How to Calculate the Stochastic Indicator using Excel

Last Updated on November 28, 2022 by Mark Ursell

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

Tracking Momentum

The stochastic oscillator is comprised of two lines, %K and %D. The main line is called %K and it tracks price momentum. Price momentum is calculated by comparing the current price with the highest and lowest prices over the period of the oscillator. A simple moving average is used to slow %K to make it smoother. A further average is used to create the signal line, %D.

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