Last Updated on 03/09/2024 by Ndanileka

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 then calculated as the moving average of the True Ranges.

ATR Google Sheets Formula (Tutorial)

In the 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 to account for weekends and holidays; more about this here.

=GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY())

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, and limit the values returned to 14.

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

Step 1.2.2 – Yesterday’s Close Request

Create a new query, then select the 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'")

1.3 – Create a 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'")})

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 in this post, where it is explained in 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 ")

Step 2.1.2 – True Ranges (Absolute Values)

We then wrap the values returned in 2.1.1 with the ABS() function to return positive values.
Since the data is an array, we need to wrap the formula with the ARRAYFORMULA() function 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 ")))

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 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 formula below to find the True Range for each day.
Each column’s “Max Value” equals 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"))&")")))

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 of 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"))&")")))

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"))&")"))))

The charts below show Tesla, Inc.’s ATR on June 17, 2022, which matches our calculation.

Tutorial Spreadsheet

ATR Custom Function

= ATR_GF()

/**
 * Calculates the Average True Range (ATR) for a given range and period.
 * 
 * @param {array} range - An array where each row contains a date and its corresponding values for  High, Low, and Close
 * @param {number} period - The number of days over which the ATR is calculated.
 * @return {array} - An array where each row contains a date and its corresponding ATR value.
 * @customfunction
 */

function ATR_GF(range, period) {

   if (period > range.length) {
    throw "Error: The specified period exceeds the length of the range.";
  }

  // Get the True Range values using the TR_GF function
  const trueRanges = TR_GF(range);
  
  // Initialize an array to hold the ATR values
  let atrValues = [];
  
  // Calculate the ATR for each period
  for (let i = 0; i <= trueRanges.length - period; i++) {
    let sum = 0;
    
    // Sum up the True Range values over the last 'n' periods
    for (let j = 0; j < period; j++) {
      sum += trueRanges[i + j][1]; // Accessing the True Range value from the array
    }
    
    // Calculate the ATR and push it to the atrValues array with the corresponding date
    atrValues.push([trueRanges[i + period - 1][0], sum / period]);
  }
  
  return atrValues;
}

Parameters

range:

An array where each row contains a date and its corresponding values for High, Low, and Close. Ensure that the array does not include the header row.

Example: To prepare your data, you can use a Google Sheets query function to remove the header row from data requested from GOOGLEFINANCE().
For instance, in this example, Col1 is Date, Col3 is High, Col4 is Low, Col5 is Close.

=QUERY(QUERY(GOOGLEFINANCE("AAPL", "all", TODAY()-200, TODAY()), "SELECT Col1, Col3, Col4, Col5", 1), "SELECT * OFFSET 1", 0)

period:

The number of days over which the Average True Range (ATR) is calculated. This period determines how many values are used to compute the ATR.

Dependencies:

TR_GF()
Calculates the True Range (TR) for a given range and period.

How to Use

1. In Google Sheets:
   • Open the “Extenstions” menu and select “Apps Script”.
   • Paste the “TR_GF” and “ATR_GF” code into the script editor.
   • Save the script
2. Return to Your Google Sheets Document:
   • Enter “ATR_GF() in a cell to use the function.

Spreadsheet

Happy Trading!