Stochastic Oscillator is a powerful tool for technical analysis that compares a stock’s closing price to its price range over a period. Developed by George C. Lane in the 1950s, the Stochastic Oscillator is a momentum indicator that shows the location of the closing price relative to the high-low range over a given number of trading periods. I will walk you through build a simple stochastic oscillator model in Excel.
How to Read the Stochastic Oscillator in Your Trading?
Stochastic Oscillators does banded oscillations that move above and below two bands signifying extreme price levels. They are helpful for traders in identifying overbought and oversold conditions as the range varies between 0 and 100. It’s sensitivity can be minimized by adjusting the time period or by taking a moving average. To determine if the stock is overbought or oversold, the stochastic is scaled from 0 to 100.
The best way to trade a stochastic oscillator is in a ranging market rather than a trending market. Trending market indicators such as MACD and EMA will keep you in a trend as long as the trend remains intact. That is why the use of stochastic is best in a ranging market.
Build a Simple Stochastic Oscillator in Excel
The VBA behind the model does all heavy lifting for you. Worksheet formulas can be used, however they are less flexible than VBA. The spreadsheet is very simple to use. You simply need to key in the following:
1. Symbol or a Major Index – In this example, I ran the oscillator chart for Russel 2000 Index
2. Time Period. It defaults to 14 day period.
3. %K or Stochastic Oscillator period – The default setting is 14 trading periods, which can be days, weeks, months or intra-day ranges. This is often referred as “lookback” period.
4. SMA period or %D – The default is for 3 days. %D or SMA is simply a 3-day moving average of %K
The indicators are calculated using these methods:
%K =(Closing Price – Lowest Low) / (Highest High – Lowest Low) * 100
%D = Arithmetic mean of 3-day of %K often called as smoothing period
%K for 14 day trading period would use the most recent close. %D is a 3-day simple moving average of %K and it is plotted next to %K to identify as a signal line. Since stochastic oscillator is range based, you can easily spot overbought and oversold levels. Typically, 70 is considered as overbought threshold and 30 as oversold threshold. Readings above 70 for 14-day Stochastic Oscillator means that the underlying stock was trading near the top of its 14-day high-low range. Readings below 30 means that a stock is trading at the low end of its high-low range.
VBA does everything for you
You can start by importing open-high-low-close data for a stock or an ETF. The accompanied VBA in the model downloads historical stock prices through a Web Service. The worksheet will appear as below. Note that few columns have been hidden to keep it concise.
1. Column H or Highest High is MAX(C2:C15). We are doing this for 14 trading periods.
2. Column I or Lowest Low is MIN(D2:15). We are doing this for 14 trading periods.
3. Column J or %K = (E15 – I15) / (H15 – I15) * 100, since we are applying 14-trading days lookback period
4. Column K or %D = AVERAGE(J15:J17) which is simple last 3-day moving average of %K
Snippets from line 97 to 118 does the VBA initialization and calculates Simple Moving Average or SMA.
Lines 126 through 152 defines primary, secondary and data axes of the chart.
Lines 153 through 169 does labeling of axes, sets the scaling sizes, and title of the chart.
After running the model, the chart will appear as below.
I have covered Relative Strength Index (RSI) in one of my earlier posts. Download build a simple stochastic oscillator excel model here.