**How to make Stochastic Oscillator – technical analysis indicator in Excel sheet**

Stochastic Oscillator (also called Stoch indicator) is a technical indicator that compares
a security's closing price to its price range over a given time period.

Stochastic Oscillator follows the speed or the momentum of
price. As a rule, the momentum changes direction before price.

The Stochastic Oscillator indicator’s sensitivity to market
movements can be reduced by adjusting the time period or by taking a moving
average of the result.

So, bullish and bearish divergences in the Stochastic
Indicator’s can be used to look for reversals. Because the Stochastic
Oscillator is range bound from 0 to 100, is also useful for identifying
overbought and oversold levels.

**This indicator is calculated with the following formula:**

**%K = (Current Close - Lowest Low)/(Highest High - Lowest Low) * 100**

**%D = 3-day SMA of %K**

` `

Lowest Low = lowest low for the previous period

Highest High = highest high for the previous period

%K is multiplied by 100 to move the decimal point two places

**The default setting for the Stochastic Oscillator indicator is 14 periods**, which can be days, weeks, months or an intraday timeframe.

A
14-period %K would use the most recent close, the highest high over the last 14
periods and the lowest low over the last 14 periods. %D is a 3-day simple
moving average of %K. This line is plotted alongside %K to act as a signal or
trigger line

The theory behind this indicator is that in an
upward-trending market, prices tend to close near their high, and during a
downward-trending market, prices tend to close near their low. Transaction
signals occur when the %K crosses through a three-period moving average called
the "%D".

As it’s a range bound oscillator from 0 to 100, the Stochastic Oscillator makes it easy to identify overbought and oversold levels. The oscillator ranges from zero (0) to one hundred (100).

No matter how fast a security advances or declines, the Stochastic Oscillator will always fluctuate within this range. Traditional settings use 80 as the overbought threshold and 20 as the oversold threshold. These levels can be adjusted to suit analytical needs and security characteristics. Readings above 80 for the 20-day Stochastic Oscillator would indicate that the underlying security was trading near the top of its 20-day high-low range. Readings below 20 occur when a security is trading at the low end of its high-low range.

Before looking at chart, it’s important to note that overbought readings( above 80) are not necessarily bearish.

**Securities can be overbought and can remain overbought during a strong uptrend.**

Closing levels that are consistently near the top of the range indicate sustained buying pressure. In a similar vein, oversold readings(below 20) are not necessarily bullish.

**Securities can also oversold and can remain oversold during a strong downtrend.**

Closing levels consistently near the bottom of the range indicate sustained selling pressure. It is, therefore, important to identify the bigger trend and trade in the direction of this trend.

Look for occasional oversold readings in an uptrend and ignore frequent overbought readings. Similarly, look for occasional overbought readings in a strong downtrend and ignore frequent oversold readings.

The Stochastic Oscillator is based on George Lane's original
formulas for %K and %D. %K in the fast
version that appears rather choppy. %D is the 3-day SMA of %K.

So for calculation of Stochastic Oscillator Indicator in
excel, formula is

**%K = 100[(C - L14)/(H14 - L14)]**C = the most recent closing price

L14 = the low of the 14 previous trading sessions

H14 = the highest price traded during the same 14-day period.

%D = 3-period moving average of %K

There are three versions of the Stochastic Oscillator, which
was developed by George C. Lane in the late 1950s as a momentum indicator that
shows the location of the close relative to the high-low range over a set
number of periods.

The Fast Stochastic
Oscillator is based on George Lane's original formulas for %K and %D. %K in the fast version that appears rather
choppy. %D is the 3-day SMA of %K.

In fact, Lane used %D to generate buy or sell signals based
on bullish and bearish divergences. Lane asserts that a %D divergence is the
"only signal which will cause you to buy or sell."

Because %D in the Fast Stochastic Oscillator is used for
signals, the Slow Stochastic Oscillator was introduced to smooth %K with a 3-day SMA, which is exactly what
%D is in the Fast Stochastic Oscillator.

%K in the Slow Stochastic Oscillator equals %D
in the Fast Stochastic Oscillator

Fast Stochastic Oscillator:

Fast %K = %K basic calculation

Fast %D = 3-period SMA of Fast %K

Slow Stochastic Oscillator:

Slow %K = Fast %K smoothed with 3-period SMA

Slow %D = 3-period SMA of Slow %K

The Full Stochastic Oscillator is a fully customizable
version of the Slow Stochastic Oscillator. Users can set the look-back period,
the number of periods to slow %K and the number of periods for the %D moving
average.

The default parameters were used in these examples:

The default parameters were used in these examples:

Fast Stochastic Oscillator (14,3),

Slow Stochastic Oscillator (14,3) and Full Stochastic
Oscillator (14,3,3).

Full Stochastic Oscillator:

Full %K = Fast %K smoothed with X-period SMA

Full %D = X-period SMA of Full %K

So formula in excel sheet is

after having input data like date (Column A) , open (Column B), High (Column C), Low (Column D), Close (Column E) for 14 days.

In Column F15 for %K = (Current Close - Lowest Low)/(Highest High -
Lowest Low) * 100

=ROUND(((E15-MIN(E2:E15))/(MAX(E2:E15)-MIN(E2:E15)))*100,0)

In Column G17 for Full %K (Fast %K smoothed with X-period SMA) (3 days)

=ROUND(AVERAGE(F15:F17),0)

In Column H19 for Full %D (X-period SMA of Full %K) (3 days)

=ROUND(AVERAGE(G17:G19),0)

* Round is meant for rounding to zero decimals.

In excel sheet, the indicator will look this after having typed the formulas in cell:

Stochastic Oscillator technical indicator |

Stochastic Oscillator technical indicator in Nifty chart |

Stochastic Oscillator nifty excel chart |