Williams R Indicator Excel

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.

Williams R Indicator Excel

Take for example that the highest high is 36, the lowest low equals 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 data was imported from Yahoo Finance Web Service for symbol YHOO between the period of Oct-28-2013 and Oct-27-2014.

Williams-R-Indicator-Excel

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.

Williams-R-Indicator-Excel

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.

Download Williams %R Spreadsheet

Leave a Comment

Your email address will not be published.