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

Last Updated on 12/05/2023 by Ndanileka

WHY YOU SHOULD 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 differs from what is commonly found on news outlet dashboards, where the focus is on daily price returns to identify “major movers” of the day.

The use of price volatility to measure price moves is based on the fact that daily price returns are meaningless without context, and 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%
5 Day Price Returns, Stock Comparison

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. Two useful volatility spike indicators that can be used to overcome this limitation are the Sigma Spike and Range Spike, which will be discussed in more detail below.



SIGMA SPIKE

Adam Grimes created Sigma Spike as a way to evaluate how today’s price return compares to past volatility by measuring it against the Standard Deviation of the last 20 daily price returns.

The Sigma Spike is calculated by dividing today’s price return by 20 Standard Deviations.

Suppose a stock has a current return of 20 Standard deviations of 1.53% and a current return of 4.02%

Sigma Spike = Todays Return / STDEVP(20) = 1.53%/4.02% = 2.63

According to Adam Grimes, Sigma Spike is helpful in quantifying surprise moves, and anything over 2.5σ or 3.0σ would stand out visually as a significant move on a price chart. After experimenting with various volatility-adjusted measures, Adam Grimes settled on Sigma Spike as his primary tool in daily work.

Source: Quantitative Analysis of Market Data

SIGMA SPIKE GOOGLE SHEETS FORMULA

In a previous article, we completed the formula for STDVEP(20). For further information on this metric, please refer to that article.

Sigma Spike = Today’s Return / STDEVP(20)

For the below example, cell B1 = “MSFT”

Step 1. Request today’s 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.

=  (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 PRICE ANALYSIS

Refresh page if spreadsheets is not loading.


RANGE SPIKE

The Range Spike is an indicator designed to gauge intraday volatility by comparing the True Range of the current day to the Average True Range of the past 10 to 14 days. To calculate the Range Spike, you simply divide the Daily True Range by the 10 to 14-day Average True Range.

For instance, let’s say a stock has a DTR of 5 and an ATR(14) of 7.
The Range Spike would be calculated as follows:
Range Spike = DTR / ATR(14) = 5 / 7 = 0.71

A value less than 1 indicates that the current true range is less than its 14 Day Average True range, which could indicate low volatility, depending on other factors. Conversely, a value greater than 1 suggests high volatility.

It’s important to note that statistical indicators require interpretation and should be used as part of a larger analysis. For this indicator, I use the grouping of :
Below 0.7 – Low.
0.7 to 1.3 – Normal
and above 1.3 – High
The grouping will vary depending on your preferences of sensitivity and trading timeframe.

Illustration of Range Spike from Google Sheets Gauge Chart.
Illustration of Range Spike indicator created with Google Sheets Gauge Chart.

Overall, the Range Spike indicator is a simple yet powerful tool for gaining insights into short-term price volatility.


RANGE SPIKE GOOGLE SHEETS FORMULA

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

=(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 PRICE ANALYSIS

Refresh the page if the spreadsheet is not loading.


In conclusion, Volatility Spike indicators such as Range Spike and Sigma Spike can play a crucial role in assessing the significance of price changes, filtering watchlists, and monitoring market volatility. Range Spike provides a valuable measure of the range within a price change, while Sigma Spike indicates what return we should expect.

Happy trading and good luck.

Leave a Reply