Average True Range Spreadsheet & Tutorial

Discover how traders use average true range as a stop-loss indicator in buying & selling strategies, and learn how it is calculated in Excel.

A stock’s range is the difference between the maximum and minimum price on any single day, and is often used as an indicator of volatility. However, trading is often halted if prices increases or decrease by a large amount on any single day. This is sometimes observed in commodities trading, and can lead to a gap between opening and closing prices between two consecutive days. A daily range would not necessarily capture this information.

J. Welles Wilder introduced true range and average true range in 1978 to better describe this behavior.

The true range captures the difference between closing and opening prices between two consecutive days. True range is the largest of

  • the difference between yesterday’s close and today’s low
  • the difference between yesterday’s close and today’s high
  • the difference between today’s high and today’s low
The initial value of true range is simply the daily high minus the daily low.
The average true range (ATR) is an exponential n-day average , and can be approximated by this equation.

Average True Range Equation

where n is the window of the moving average (usually 14 days) and TR is the true range. ATR is usually initialized (at t = 0) with a n-day trailing average of TR.

Average true range does not indicate the direction of the market, but simply the volatility. The equation gives the most recent price movement greater significance; hence, it is used to measure market sentiment.

It is usually used to analyze the risk of taking a specific position in the market. One way of doing this is to predict daily movements based on historic values of ATR, and enter or exit the market accordingly. For example, a daily stop-loss may be set at 1.5 or 2 times the average true range. This gives an asset price freedom to vary naturally during a trading day, but still sets a reasonable exit position.

Moreover, if the historic average true range contracts while prices are trending upwards, then this might indicate that market sentiment may turn.

Combined with Bollinger Bands, average true range is an effective tool for volatility-based trading strategies.

 Calculate Average True Range in Excel

This Excel spreadsheet uses daily stock prices for BP for the five years from 2007 (downloaded with this spreadsheet).

Average True Range in Excel

The spreadsheet is fully annotated with equations and comments to aid your understanding.

The following spreadsheet, however, has a lot more smarts. It automatically, plots the average true range, the relative strength index and the historical volatility from data it automatically downloads from Yahoo Finance.

Relative Strength Index and Average True Range in Excel

You enter the following information

  • a stock ticker
  • a start and end date
  • calculation periods for the ATR, RSI and historical volatility

After clicking a button, the spreadsheet download stock quotes from Yahoo Finance (specifically, the daily open, close, high and low prices between the two dates). It then plots the average true range and the historical volatility.

It’s very simple to use! I’d love to hear what you think or if you have any improvements you’d like.

Download Excel Spreadsheet to Calculate Average True Range

Download Excel Spreadsheet To Automatically Plot RSI, ATR and Historical Volatility from Stock Ticker (Yahoo Finance)


11 thoughts on “Average True Range Spreadsheet & Tutorial

  1. If you can explain, how to use above information in trading decision.

    If you can give exel formula for calculating supertrend indicator ? As now every one is talking about supertrend . i only know that its calculation is based on average true range.

    Thanks in advance

  2. File had a compile error in hidden module: mdlWin32API

    I use a Mac and have Excel for Mac 2011 Version 14.3.2 Guessing it is a 64 bit vs. 32 bit error

  3. I believe there is an error in the spreadsheet because you are not taking the absolute value for (current high – previous close) OR (current low – previous close) even though that’s what your comments say to do.

  4. There is an error in the spreadsheet RSI-ATR-MACD-EMA-HV.xlsm
    The true range considers MAX(abs(Low(today)-High(today)), abs(high(today)-low(yesterday)), abs(low(today)-close(yesterday))).

    According to the correct comments above, the second abs() term shoud be abs(high(today)-close(yesterday))

    1. You have to provide a bit more information than just blankly saying “this macro does not work!”

      OS? Excel version? etc?

      The spreadsheet works fine for me on Excel 2010 and Excel 2013 on Windows 7 64-bit

  5. Having same problem as Doug (April 30, 2013). Have the compile error in hidden module: mdlWin32API popping up. Is there a work-around for this?

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 15 + 13 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)