Normalized ATR Googles Sheets Formula

Why Formulate a Normalized ATR

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

For example, let’s compare two stocks, AMD and INTC.
AMD – Trading at $98.00 with an ATR(10) of 4.17
INTC – Trading at $36.00 with an ATR(10) of 1.45
Comparing the volatility using the ATR would be challenging due to the differences in trading prices.

Normalized ATR Formula

However, If you calculate the ATR as a percentage of the stock price, you get a volatility measure that is directly comparable across stocks with different prices. We accomplish this by using the formula expressed below.


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

The completed calculation shows that both companies have similar NATRs ( volatility), which is no surprise as both stocks trade in the same industry.

Normalized ATR for Analysis

Similar to Standard Deviation, the NATR can be used as a volatility scan/filter for a shortlist, as a market monitor for general volatility, or to measure the risk profile of a potential.

In the scatter chart below, the NATR is used to monitor the of stock volatility of the S&P 100.

Normalized ATR Formula Google Sheets

To calculate the NATR we to need to divide the ATR formula by Yesterday’s Close and then multiple by 100.

B1 = “Stock Code”

NATR = (ATR/GOOGLEFINANCE(B1,”closeyest”))*100

Complete formula below;

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

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

Leave a Reply