Normalized ATR Googles Sheets Formula

Last Updated on 19/05/2023 by Ndanileka

WHY FORMULATE A NORMALIZED ATR

The Average True Range (ATR) is a useful tool for monitoring the volatility of a single stock. However, it cannot be easily applied when comparing the volatility of multiple stocks due to the difference in their trading prices.

For example, comparing the volatility of two stocks,
AMD and INTC is challenging since they have different prices.
AMD is trading at $98.00 with an ATR(10) of 4.17,
while INTC is trading at $36.00 with an ATR(10) of 1.45.

To make volatility measurements directly comparable across stocks with different prices, you can calculate the ATR as a percentage of the stock price. This is done using the formula expressed below:
NATR = (ATR/CLOSE) X 100

Let us continue with the AMD and INTC example and calculate their respective NATR values.

AMD NATR(10) = (4.17/98.00) x 100 = 4.26%
INTC NATR(10) = (1.45/36.00) x 100 = 4.03%

As you can see, both companies have similar NATRs (volatility) despite their different trading prices. This is not surprising, considering that they operate in the same industry.



NORMALIZED ATR GOOGLE SHEETS FORMULA

To make the NATR calculation consistent with the time period used for the ATR, the ATR value is divided by yesterday’s close price and then multiplied by 100 to get the percentage value.

B1 = “Stock Code”

NATR = ( ATR / GOOGLEFINANCE( B1, “closeyest” ) ) * 100 or format as percentage

=(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"))&")"))))/GOOGLEFINANCE(B1,"closeyest"))*100

NORMALIZED ATR PRICE ANALYSIS

Similar to the Standard Deviation, the NATR can be employed as a filter for volatility scanning to narrow down a shortlist of stocks, as a general market monitor to assess volatility levels, or to gauge the risk profile of a prospective investment.
To illustrate, the scatter chart below demonstrates the use of NATR to track the volatility of stocks in the S&P 100 index.

View and copy the spreadsheet on the link below and tailor the data to your needs.

Leave a Reply