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
There is an VBA error in this file. Has there been an update to it?
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
Bhushan
I am also looking for Super Trend excel calculator. Based on your ATR I further tried to added Super Trend Uptrend and downtrend but values are not accurate. Request yout help in this……I am reachable at ravishah885@gmail.com
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
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.
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))
this macro does not work!
How can we expect to get something that work correctly from you, Invest Excel?
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
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?
WILL THE RELATIVE STRENGTH INDEX, AVERAGE TRUE RANGE AND HISTORICAL VOLATILITY WORK IN OPEN OFFICE SPREADSHEET?
No it won’t