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

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).

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.

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)