ATR

Average True Range Calculation in Google Sheets

What is the Average True Range?

The Average True Range is an indicator used to determine market volatility; it measures how much price moves on average over a given period. Welles Wilder introduced it in his book “New concepts in technical trading systems.” 

To calculate the ATR, we first need to find True Ranges (TR) for a given period. Where TR is the greatest of the following:

  • The distance from today’s high to today’s low.
  • The distance from yesterday’s close to today’s high.
  • The distance from yesterday’s close to today’s low

The Average True Range is a moving average of the True Ranges.
Learn more about the TR calculation in this post.



ATR Google Sheets Calculation

In below example we are going to calculate 14 period ATR

Cell B1 = “TSLA” ( Tesla, Inc)

Step 1 – Requesting data and creating a dataset

Request 3 price points Today’s High, Today’s Low, Yesterday’s Close.

Step 1.1.1 – Historical High & Low Request
Request “all” prices from the previous 25 days. We request more days than required in order to account for weekends and holidays, more about this here.

=GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY())
Historical Price Request

Step 1.1.2 – Historical High & Low Request

Create a new query then select the High and Low columns from step 1.1.1 , sort the values in date descending order, limit the values returned to 14.

=QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14")
Historical High and Low formula

Step 1.2.1 – Yesterday’s Close Request

Request close prices from the previous 25 days,

=QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today()))
Historical close price formula

Step 1.2.2 – Yesterday’s Close Request

Create a new query then select close column from step 1.2.1 , sort the values in date descending order, limit the values return to 14, offset by 1 and label the column as “Closeyest”.

=QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today())," SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")
Historically Yesterday’s close price forumla

1.3 – Create single dataset

We then combine the queries from 1.1.2 and 1.2.2 into a single dataset by wrapping them in brackets{} and using “,” as a separator.
1.3 = QUERY({1.1.2, 1.2.2})

=QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today())," SELECT Col2 ORDER by Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")})
Historical High, Low, Yesterday’s Close formula

Step 2 Average True Range Calculation

Step 2.1.1 True Ranges (Price Difference)
We use the dataset from 1.3 to calculate the price differences between:
(High – Low) , (High – Yesterday’s Close), (Yesterday’s Close – Low).
Learn more about the true range on this post where it is explained detail.

=QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today()),"SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")}),"SELECT Col1-Col2, Col1-Col3, Col3-Col2 ")
Historical Price differences

Step 2.1.2True Ranges (Absolute Values)

We then wrap the values returned in 2.1.1 with ABS() function to return positive values.
Since the data is an array we need to wrap the formula with the ARRAYFORMULA() function in order for it to work.

=ARRAYFORMULA(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today()),"SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")}),"SELECT Col1-Col2, Col1-Col3, Col3-Col2 ")))
Absolute value Historical Price Differences

Step 2.1.3 True Ranges (Transpose dataset)

The QUERY() function does not have a built-in method to “SELECT” values per row.
Therefore we transpose the dataset in order to have the rows as columns, this will assist us when calculating the True Range for each day.

=ARRAYFORMULA(TRANSPOSE(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today()),"SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")}),"SELECT Col1-Col2, Col1-Col3, Col3-Col2 "))))

Step 2.1.4 – True Ranges ( Max(Col) String)

We could create a new query with the below formula to find the True Range for each day.
The “Max Value” of each column is equal to the “True Range” for that day.


Query( (Step 2.1.3),"SELECT Max(Col1), Max(Col2), Max(Col3), Max(Col4), Max(Col5), Max(Col6), Max(Col7), Max(Col8), Max(Col9), Max(Col10), Max(Col1), Max(Col2), Max(Col13),Max(Col14), Max(Col15))"

However this would be time consuming and every time the ATR(Period) is changed we would need to edit the number of “Max(Col)’s”.
Therefore we need to automate the process of creating the Max(Col) “string“.
We will use the functions INDIRECT(), AMPERSAND “&”, ROW(), and JOIN() to accomplish this.

=ARRAYFORMULA((JOIN(",max(Col",row(indirect("A1:A15"))&")")))
Max(Col) string

We then combine our automated string with “SELECT Max(Col” to complete the query.
To change the number of “Max(Col)” we only need to edit “A15”.

View this post from infoinspired.com for a detailed explanation on how to find Max Values in each row.

Step 2.1.5 – True Ranges ( Per Day)

=ARRAYFORMULA(QUERY(TRANSPOSE(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, 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("A1:A15"))&")")))
Historical True Range Formula

Step 2.1.6 – Calculate Average True Range

To complete the ATR formula we wrap Step 2.15 with the AVERAGE() function.

=AVERAGE(ARRAYFORMULA(QUERY(TRANSPOSE(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, 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("A1:A15"))&")"))))
ATR formula

The below charts displays Tesla, Inc ATR on 17/06/2022 which is the same as our calculation.

17/06/2022 @ ATR(14) 46.49

Complete.

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

Leave a Reply

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