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. In this article I show how to calculate the %R using Excel.
Williams Percent Range
The %R is a fast moving oscillator 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 divergence.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
Excel is a great tool for calculating technical indicators, backtesting and developing trading strategies. On this website there are lots of resources that make it easy to test your own strategies using Excel.To calculate the %R using Excel we start with the historical data. We then need 3 new columns to contain the recent highs, recent lows and the %R.To calculate the highs we use the Excel function =MAX()To calculate the lows we use =MIN()The formula for %R is (high-close)/(high-low)*-100In Excel the formulas are:G15 =MAX(D2:D15) H15 =MIN(E2:E15) I15 =(G15-F15)/(G15-H15)*-100
If you need some more guidance, I have a video demonstrating how to set up the spreadsheet to calculate %R:
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: Long-Term Secrets to Short-Term Trading.
This video article describes how you can calculate the MACD Indicator in Microsoft Excel. I believe…
My name is Mark Ursell, and I am an individual trader and investor. I am continually working on developing new trading strategies and improving my existing strategies. I have developed a series of Excel backtest models, and you can learn more about them on this site.