How to Calculate the Stochastic Indicator using Excel

The Stochastic Oscillator is a hugely popular trading indicator.  The indicator was developed by George Lane.

Tracking Momentum

The indicator is designed to track price momentum.  It does this by comparing the current close with the highest and lowest price over a set number of periods.

Stochastic Oscillator

How to use the Stochastic Oscillator

The stochastic oscillator is designed to signal a change in the market direction.  However, beginner traders can become unstuck when they use the stochastic to trade against a strong trend.  During a strong uptrend the stochastic will often be in the over-bought area, however this does not mean that it is a good time to go short.

George Lane said that the best way to trade the stochastic is to look for divergence between the price and the oscillator.  During an uptrend both the price and stochastic will be hitting new highs at the same time.  At some stage the price will hit a new high and the oscillator will not.  This divergence indicates that the momentum of the move is weakening and that the price might be ready to pull back.

The stochastic oscillator can also be used to time entries in the direction of the trend.  Swing trading relies on entering trades when the price has retraced against the main trend.  To swing trade using the stochastic a trader needs to identify the main trend and then wait until the stochastic has moved into the oversold area.  A long trade can then be entered when the price moves out of the oversold area.

YouTube Video

Formulas Used

Close-lowest low H17 =F17-MIN(E4:E17)
Highest high-lowest low I17 =MAX(D4:D17)-MIN(E4:E17)
%K J17 =(H17/I17)*100
Slow Stochastic K19 =(SUM(H17:H19)/SUM(I17:I19))*100
Signal Line L21 =AVERAGE(K19:K21)

Related Links

If you are interested in using Excel to backtest trading strategies my new Ebook course: How to Backtest a Trading Strategy using Excel is now available in the Amazon Kindle Bookstore.

If you are interested in using Excel for trading purposes you may be interested in how to calculate the following indicators:

Calculating the SuperTrend Indicator 
Optimising Trading Strategies using Excel
Calculating the Elder Ray Indicator