* Update – Trading models that were earlier written through Yahoo! Finance Stock API have all been rewritten to pull quotes from Bloomberg Markets or an Open Source API. At this time only US stocks and ETF quotes can be retrieved from this model spreadsheet. See setup instructions.
Williams R Indicator
Named after Larry Williams, %R is a technical analysis indicator that shows current closing price in relation to the high and low for a given look-back period, N. N can be a given number of days, weeks, months, or an intraday time-frame. Williams R indicator is also referred to as the inverse of the Fast Stochastic Oscillator.
In 1987 Larry Williams won the world cup of futures trading turning $100k into over 1.1 million in a span of 12 months. Then in 1997, Michelle Williams, Larry Williams’ daughter won the world cup using the same strategy as her father.
The equation for calculating %R is given by:
%R = (Highest High - Close) / (Highest High - Lowest Low) * -100
Williams %R oscillates between -100 and 0. Generally speaking, values from 0 to -20 indicate overbought condition and values from -80 to -100 means oversold condition. The default setting for Williams %R is 14 trading periods.
How to calculate Williams R Indicator in Excel
Lets say that the highest high is 36, the lowest low is 31 and the close equals 35. The high-low range is 5 (36 – 31), the highest high minus close becomes 1 (36 – 35). The ratio than become equals 0.2. Multiply 0.2 by -100 to get -20 for %R.
Low readings (values < -80) indicates the stock is trading near its low for the given look back period. High readings (values > -20) means that the stock is trading near its high for the given lookback period. The below screen capture is from the spreadsheet used in this example. Column H and Column I contains the highest high and lowest low of Yahoo for the previous 14 trading days respectively. Column J is where the %R is being calculated using the above equation. The market data was retrieved from Bloomberg Open Markets API for Yahoo stock between the period of Oct-28-2013 and Oct-27-2014.
The chart plots Williams %R vs daily closing price of Yahoo (YHOO) from Oct-27-2013 to Oct-27-2014. The trading oscillator moves between -100 and 0. Notice the chart closely. The YHOO example shows a 14 day Williams %R hitting overbought and oversold levels more than once. The stock was overbought from late Nov-2013 to Dec-2013. It dipped below -80 in January, March, April, and late June 2014 giving you a buy signal. The closing price then shot up in August 2014.
Overbought prices are not necessarily bearish. Stocks can become overbought and stay overbought during a strong uptrend. Similarly, oversold prices are not necessarily bullish. Closing levels near the bottom of %R range can signal sustained selling pressure.
The VBA behind this spreadsheet calculates Williams %R from historical stock prices and automatically imports from Yahoo Finance Web API. Just plug-in the symbol, from-to dates, and lookback period (defaulted to 14 trading days in this example), the spreadsheet does the rest.
Under Developer->Visual Basic->Tools->References, add the highlighted objects.