Okay, you’ve got the historical data from Yahoo Finance. You’ve imported all data in Excel. You’ve created a Stock Chart. So how do you calculate the Average True Range on Excel? How do you draw the Average True Range in Excel with a chart? Worry no more, because here’s the guide you’ve been looking for!
If you don’t know how to import data from Yahoo Finance to Excel, start with this tutorial.
Standard Average True Range
Step 1: Open your file with Open – High – Low – Close column
Open the file where you have saved the Open – High – Low – Close values. If you want, you can use this file to follow us in the tutorial.
Step 2: Create the column for the calculations of the ATR
Create 5 new columns in the table: Daily Range | High – Close[1] |Low – Close[1] | True Range | Average True Range |
Step 3: The Daily Range Formula
Firstly, we calculate the Daily Range that is High Price – Low Price. Write this formula: =[@High]-[@Low]
We use this formula because we are inside of the table. If you have a spreadsheet without a table, you can write: =C2-D2
Step 4: The High – Close[1] Formula
We subtract from the Today High Price the Close of the yesterday candle. Write or copy and paste this formula: =ABS([@High]-E2)
Step 5: The Low – Close[1] Formula
We subtract from the Today Low Price the Close of the yesterday candle. Write or copy and paste this formula: =ABS([@Low]-E2)
Step 6: True Range Formula
Now we calculate the True Range, with this formula:
=MAX([@[High – Close'[1′]]]:[@[Low – Close'[1′]]];[@[Daily Range]])
Step 6: Average True Range Formula for Excel
Finally, we obtain the Average True Range with this formula:
=AVERAGE(I3:I17)
Substantially, we are creating one Moving Average of the last 14 True Range. For this reason, we start the calculation from J17.
Average True Range Indicator Chart in Excel
Select the cell A1 “date”, press “CTRL + SHIFT” and “Down Arrow”, continue to press CTRL and click with the mouse on the cell J1 “Average True Range” then press “CTRL + SHIFT” and “Down Arrow”. You should have selected all two columns.
Now go to “Insert” menu and select the line chart.
That’s all. The Average True Range Indicator for Excel is correctly created.
You can confront this with TradeStation Average True Range Indicator with the Excel Indicator.
External link for The Average True Range (ATR)
Editors’ Recommendations: