Volatility Spike Indicators Google Sheets Formulas – Sigma Spike & Range Spike

Why should you use Volatility Spike Indicators?

Adam Grimes introduced the method of using volatility as an indicator for “major moves” in his blog post on How to calculate Sigma Spikes?.

The method takes a different approach from what you would find on most news outlet dashboards, where their focus is on daily price returns to determine “major movers” of the day.

Using price volatility to measure price moves is based on the fact that daily price returns are meaningless without context. Therefore, short-term price history is required to understand the significance of a price change.
A better approach would be to monitor the natural movement of a stock and then base the importance of a price change on that information.

For example, let us consider two stocks, A & B, with the below price returns.

Day12345
Stock A-2.81%-2.61%3%1.96%-2.31%
Stock B0.56%-0.77%-0.44%0.61%-2.31%

Without considering the previous days, the significance of Day 5 would be deemed equal for both stocks. However, when we view the price history, we see that stock B’s price change is more significant.



Volatility Spike Indicators

Below I will discuss and share two volatility spike indicators and formula’s for Google sheets.
Namely; Sigma Spike and Range Spike.

Sigma Spike

Sigma Spike, formulated by Adam Grimes, attempts to put today’s price return in the context of the Standard Deviation of 20 daily price returns.

Calculation In Google Sheets

Sigma Spike = Today’s Return / STDEVP(20)
The formula for the STDVEP(20) is on the highlighted link.

For example below cell B1 = “MSFT”

Step 1
Request today return as a percentage.

=GOOGLEFINANCE(B1,"CHANGEPCT")/100   
Percentage Change Formula
Percentage Change Formula

Step 2
Divide Step 1 by STDVEP(20) to complete the formula.

Sigma Spike Formula
=  (GOOGLEFINANCE(B1,"CHANGEPCT")/100)/STDEVP(QUERY({QUERY(GOOGLEFINANCE(B1,"close",today()-40 ,today()),"SELECT Col2 ORDER by Col1 DESC LIMIT 20 LABEL Col2''"),QUERY(GOOGLEFINANCE(B1,"close",TODAY()-(40) ,TODAY()),"SELECT Col2 ORDER by Col1 DESC LIMIT 20 OFFSET 1 LABEL Col2''",)},"Select Col1/Col2-1 LIMIT 20"))(GOOGLEFINANCE(B1,"CHANGEPCT")/100)/STDEVP(QUERY({QUERY(GOOGLEFINANCE(B1,"close",today()-40 ,today()),"SELECT Col2 ORDER by Col1 DESC LIMIT 20 LABEL Col2''"),QUERY(GOOGLEFINANCE(B1,"close",TODAY()-(40) ,TODAY()),"SELECT Col2 ORDER by Col1 DESC LIMIT 20 OFFSET 1 LABEL Col2''",)},"Select Col1/Col2-1 LIMIT 20"))
Sigma |Spike Formula
Sigma Spike Formula
Sigma Spike Analysis Example

Refresh page if spreadsheets is not loading.

Range Spike

Range Spike attempts to put today’s True Range in the context of the Average True of (10 – 14 ) Days.

Calculation in google Sheets

Range Spike = Today’s True Range / Yesterday’s ATR(14)

The True Range and ATR formulas are on the highlighted links. Therefore I will only share the complete Range Spike formula on this post as it is a quotient of TR/ATR.

Range Spike Formula
=(MAX(GOOGLEFINANCE(B1,"HIGH"),GOOGLEFINANCE(B1,"CLOSEYEST"))-MIN(GOOGLEFINANCE(B1,"LOW"),GOOGLEFINANCE(B1,"CLOSEYEST")))/AVERAGE(ARRAYFORMULA(QUERY(TRANSPOSE(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-30, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-30, today()),"SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")}),"SELECT Col1-Col2, Col1-Col3, Col3-Col2 "))),"SELECT max(Col"&JOIN(",max(Col",row(indirect("A2:A15"))&")"))))
Range Spike Formula
Range Spike Formula
Range Spike Analysis Example

Refresh page if spreadsheets is not loading.


Conclusion

Volatility Spike indicators can be excellent tools for identifying the significance of a price change, they can also function as a filter on a watchlist or as a market volatility monitor.
Range Spike provides insight into the range within a price change, ( narrow, normal, wide) .
Sigma Spike provides insight into what return we should expect, ( below, normal, greater).

Happy trading and good luck!

Leave a Reply

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