Williams R Indicator

      2 Comments on Williams R Indicator

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.

Formula for %R:

%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. From the below screengrab, column H and column I contain 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 %R formula. The market data was retrieved from Bloomberg Open Markets API for Yahoo stock 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 prices. It automatically imports quotes from Bloomberg Open Markets. Just plug-in the symbol, from-to dates, and lookback period (defaulted to 14 trading days), the spreadsheet does the rest.

Usage Instructions

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.

The spreadsheet model retrieves quote information from Bloomberg Markets API through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain COM+ objects to start utilizing Bloomberg Markets Web Service through VBA.

Under Developer->Visual Basic->Tools->References, add the highlighted objects.

VBA-JSON-References

2 thoughts on “Williams R Indicator

  1. Jay Gonzalez

    I must commend you for taking the time switch all models from yahoo finance Web api to open source. That will go the long way. I have downloaded all technical indicator models. But it is asking for the password for viewing the code. Anyway can i donate $50 through a PayPal link? I am in need of the source.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.