Last Updated on November 28, 2022 by Mark Ursell
In this article and video, I show how to calculate the Williams %R using Excel.
The Williams %R is a technical indicator developed by Larry Williams. It is an oscillator that is used by traders to identify underlying market strength.
Video: How to Calculate Williams %R
Williams Percent Range
The %R is a fast-moving oscillator that is similar to the stochastic oscillator. It calculates how the current price compares to the recent highs and lows.
It does not have any smoothing which means that it responds quickly to a change in the price action. The indicator moves in a range from 0 to -100.
One of the best ways to trade using the %R is to identify divergences between price and the oscillator. If the price is setting new highs and the oscillator is not making new highs this is a warning that a divergence is happening.
In the chart above the FTSE 100 index was making new highs during April 2015. However, the %R was getting weaker. By the end of April, the price was following the %R lower.
How to Calculate the Williams %R in Excel
Excel is a tremendous tool for all your trading analysis. You can use it to calculate technical indicators, backtest trading strategies and develop new trading strategies. On this website, there are lots of resources that make it easy to test your own strategies using Excel.
Calculate the Williams %R Using Excel
Total Time: 20 hours and 10 minutes
Start with Your Historical Price Data in an Excel Spreadsheet
Create three new column headings:
Column G: Recent Highs
Column H: Recent Lows
Column I: %R
Enter the Formulas for Recent Highs and Recent Lows
Recent Highs use the formula =MAX()
Recent Lows use the formula =MIN()
Cell G15 =MAX(D2:D15)
Cell H15 =MIN(E2:E15)
Enter the Formula for Williams %R
The formula for %R is: (high-close)/(recent highs-recent lows)*-100
Cell I15 =(G15-F15)/(G15-H15)*-100
If you need some more guidance, watch the video above showing me demonstrating how to set up the spreadsheet to calculate %R:
“Always think that the next trade will be a loser… a big loser”Larry Williams
Larry Williams has been a trader and teacher of traders for many years. One of his market philosophies is to “always think that the next trade will be a loser … a big loser”. It that sounds a bit strange to you then check out his book on Amazon: Long-Term Secrets to Short-Term Trading.
Discover the secrets of the markets you trade by backtesting. Find hidden opportunities and develop a solid reliable trading strategy. Tradinformed Backtest Models use Excel, and make it easy for you to develop and optimize any trading strategy.