Normalized ATR Googles Sheets Formula

Normalized ATR Googles Sheets Formula

Last Updated on 27/05/2024 by Ndanileka

Why Formulate a Normilzed ATR

The Average True Range (ATR) is a valuable tool for gauging a single stock’s volatility. However, it becomes less helpful when comparing multiple stocks because the ATR value is influenced by the trading price.

For instance, comparing the volatility of chipmakers AMD and Intel (INTC) is difficult solely based on ATR. AMD, trading at $98 with an ATR(10) of 4.17, appears significantly more volatile than INTC at $36 with an ATR(10) of 1.45.

To address this issue and enable direct comparison of volatility across stocks, we can calculate the Normalized Average True Range (NATR). This metric expresses ATR as a percentage of the stock’s closing price.

NATR = (ATR / Closing Price) 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, despite the difference in trading prices, both AMD and INTC exhibit similar NATR values, indicating comparable volatility levels. This makes sense considering they operate in the same industry.

Normalized ATR Google Sheets Formula

To make the NATR calculation consistent with the 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

Spreadsheet

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.

Happy Trading!

Leave a Reply

Back To Top

Discover more from DEUS HARVEST

Subscribe now to keep reading and get access to the full archive.

Continue reading