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.

Excel Stock Chart

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 |

Excel Average True Range create column

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

Excel Average True Range Daily Range Formula

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)

Excel Average True Range High - Close Formula

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)

Excel Average True Range Low - Close Formula

Step 6: True Range Formula

Now we calculate the True Range, with this formula:

=MAX([@[High – Close'[1′]]]:[@[Low – Close'[1′]]];[@[Daily Range]])

Excel ATR indicator True Range Formula

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.

Select two colums in excel for average true range indicator chart

Now go to “Insert” menu and select the line chart.

Creating a chart of average true range indicator in excel

That’s all. The Average True Range Indicator for Excel is correctly created.

Average True Range Indicator for Excel

You can confront this with TradeStation Average True Range Indicator with the Excel Indicator.

Average True Range in TradeStation compare to ATR in Excel

 

External link for The Average True Range (ATR)

 

Editors’ Recommendations:

 

 

 

 

 

 

Leave a Reply