Как рассчитать atr в excel
Обновлено: 03.07.2024
This page explains the calculation of Average True Range (ATR). We will look at all three commonly used calculation methods – simple, exponential, and the original Wilder’s smoothing method. We will also compare the results of these methods on some examples, pointing out their different characteristics.
Calculating True Range
Average True Range, as its name suggests, is the average of true range. If you want to understand and calculate ATR, you first need to understand and calculate true range, which is the greatest of these three:
- High minus low (like traditional range)
- High minus previous close
- Previous close minus low
So the formula for true range would be:
TR = max ( H – L, H – C.1, C.1 – L )
For more detailed explanation of true range and some graphical examples, see True Range and How It Differs from Range.
Calculating ATR from True Range
Once you have calculated true range for each bar, the next step is to calculate the average of these, which is the ATR that we want. There are several different methods for that and the three most common are the following:
- Simple Moving Average
- Exponential Moving Average
- J. Welles Wilder’s Smoothing Method
1. Simple Moving Average (“Simple MA” in the calculator) is mathematically arithmetic average – the sum of last n bars divided by n:
n is the ATR period length
TRi is true range i bars ago
2. Exponential Moving Average (“Exponential MA”) puts greater weight on the most recent bars and smaller weight on older bars:
TR0 is true range for the current bar
ATR1 is ATR calculated for the previous bar
a is the smoothing factor, which is a function of the period length n:
3. Wilder’s Smoothing Method (“Wilder”) is the method originally used for ATR calculation by ATR inventor J. Welles Wilder (explained in his book New Concepts in Technical Trading Systems, page 23). It has the same logic as exponential moving average (puts greater weight on the most recent bars), from which it only differs in the exact calculation of the smoothing factor:
For detailed guide to calculating all the three methods in Excel, see the ATR Excel tutorial.
Which of the Methods is the Correct One?
In various resources you will find that one of the methods is the “right one” and the others are “incorrect”. If you are looking for the “original” ATR, as presented by J. Welles Wilder, use Wilder’s smoothing method.
However, there is no such thing as “correct” or “incorrect” calculation method and none of the methods is universally more profitable than the others when using ATR as part of the investment decision making process.
Of course, different methods lead to slightly different behaviour of ATR and may have strengths and weaknesses, depending on the particular purpose, trading style, and market conditions. But personally I find the selection of particular method less important than simply knowing what you are using and using it consistently. If you are familiar with trading strategies based on moving averages, it is very similar to the SMA vs. EMA (vs. another MA’s) discussion. It is also similar to the discussion about the “best” period length.
Comparing EMA/Wilder’s vs. SMA ATR Calculation
Note: The EMA and Wilder methods are actually quite similar (you could already see that in the formulas above). There is in fact a direct relationship between period lengths of the two (2x-1), although it is not 100% accurate due to the data start problem explained below.
Disadvantages of EMA/Wilder’s ATR calculation method
One disadvantage is that both EMA and Wilder’s method are more complicated to calculate, though this does not play any role if you have Excel or common technical analysis software. For some people it may also be less comfortable to think about and interpret.
A more serious issue is that the resulting ATR at a particular point of time can show different values for the same set of data depending on when you started to calculate ATR in the past. This is because EMA and Wilder’s ATR calculation includes all data from start to the particular point of time (the data in distant past have very little weight in the final ATR value, but still do have some).
Disadvantage of SMA ATR calculation method
One drawback of the simplified ATR calculation is that ATR sometimes changes due to a sharp True Range change N bars back, as the simple moving average window rolls, even when current True Range remains stable.
ATR Calculation Methods Compared in One Chart
Below you can see the three ATR calculation methods in one chart. I have used S&P500 index daily data in 4Q2012, but I have changed the value on one day (to 1,000 on 14 November 2012) to create an extreme True Range value. You can see how ATR reacts differently to the spike in True Range under the three methods (blue = SMA, orange = EMA, red = Wilder, the period is 14 on all):
As a result of the logic of individual calculation methods, the SMA ATR is elevated for 14 days (= ATR period) following the extreme price day, while the EMA and Wilder ATR start to gradually decline immediately after the True Range spike. However, EMA and Wilder ATR take longer time to return to the original level in the end. The difference is the same as the way simple and exponential moving averages react differently to price spikes, because the calculations are mathematically the same (you just use True Range instead of price on ATR).
Notice also the difference between EMA and Wilder ATR, which is a result of the difference in the smoothing factor. The numerator in the formula for a is 2 under EMA, but 1 under Wilder’s method. Therefore, Wilder ATR with a particular period (n) is approximately the same as EMA ATR with double the period (2n, or 2n – 1 to be precise). The chart below shows the same as the one above, only the EMA ATR period was doubled to 28. You can see the EMA and Wilder ATR are almost the same now:
This is a detailed guide to calculating Average True Range (ATR) in Excel. We will first calculate true range and then ATR as moving average of true range. We will cover all three popular ATR calculation methods – simple, exponential, and the original Wilder’s smoothing method.
You don’t need advanced Excel skills for these calculations – they only use relatively simple Excel functions, such as MAX or AVERAGE.
Historical Data
For calculating Average True Range you need the history of high, low and close for each day or bar. If you have your data in OHLC format, paste it into a new Excel spreadsheet into columns A (date/time), B (open – not really needed), C (high), D (low), E (close). Put the column labels in row 3 and the actual data below, starting from row 4.
I will use daily prices for the GLD ETF from 1 June to 31 August 2017. If you want to follow this tutorial with the exact same data and check that your results are correct, you can download the data from Yahoo Finance (the symbol is GLD). Note that I have rounded the prices to two decimal places to deal will Yahoo data imprecision.
You can also calculate ATR for other frequencies, such as weekly, monthly or intraday – the formulas are the same. I will use “day” and “bar” interchangeably to refer to one row throughout this tutorial.
Calculating True Range
Before ATR itself we must first calculate true range for each day, because ATR is a moving average of that. The formula is quite simple – true range is the greatest of the following three price differences:
- High minus low (the traditional range)
- High minus previous close
- Previous close minus low
For detailed explanation and graphical examples, see True Range and How It Differs from Range.
Because the inputs include previous close, we can’t calculate true range for the first row of our data, so we will start with the second (row 5 in our spreadsheet). Let’s put true range in column F. The formula in cell F5 is:
… where C5 is current bar high, D5 is current bar low, and E4 is previous bar close. MAX is the Excel function which returns maximum of given arguments.
Make sure you use relative references (no dollar signs) and copy the formula to the other rows from row 6 down.
We have calculated true range for each bar. Now we can calculate ATR as moving average of true range.
Calculating ATR – the Three Methods
There are three popular smoothing methods for calculating ATR from true range. The simple moving average method is the simplest of the three, so we will start with that one. Then we will also calculate the other two methods – exponential moving average and Wilder’s smoothing method, which is the original method used by J. Welles Wilder, the inventor of ATR.
Simple Moving Average ATR
The SMA method is very straightforward, which is why I personally often prefer this one. In Excel you calculate ATR simply as arithmetic average of true range over a certain number of latest bars, using the Excel function AVERAGE. “Certain number” of bars is called the ATR period and it is the only parameter that this indicator takes.
In this tutorial I will use 21 as the ATR period. I am working with daily data and 21 trading days corresponds to approximately one month. There is of course no “correct” period and different settings suit different purposes, different markets, and different trading styles.
Let’s put the period setting in cell G2.
In column G we will calculate the AVERAGE of column F (true range). We will make our calculations dynamic, which will allow as to change the ATR period easily just by changing the value in cell G2, without the need to rewrite any formulas.
We can do this by combining AVERAGE with another Excel function – OFFSET. Let’s start in the last row of our data. The formula in cell G68 is:
AVERAGE and OFFSET Combination Explained
The entire OFFSET function is inside the AVERAGE function as its argument. OFFSET returns a range of cells and AVERAGE then calculates arithmetic average of those cells.
OFFSET itself has five arguments, which specify the range of cells to return. Let’s explain each.
“Reference” is where you want to start looking for the range. In our example it’s cell F68, which is the current bar’s true range.
The next two arguments – “rows” and “cols” – decide how many rows up or down and how many columns left or right to shift to find the starting point of the range. In our case, we want the range to always include n last rows in column F, ending with F68, where n is the ATR period in cell G2. With the current period setting of 21, we need the “rows” argument of OFFSET to be -20 to shift 20 rows back from cell F68 to cell F48. ATR will be calculated as average of 21 cells from F48 to F68. The “cols” argument is zero, because we want to stay in column F.
The last two arguments – “height” and “width” – decide the size of the range. In our case we want “height” to be equal to the ATR period and “width” equal to 1.
You can test the formula by setting a small ATR period like 2 or 3 and checking that it returns arithmetic average of the cells you expect. The result for GLD daily data at 31 August 2017 with ATR period 21 is 1.111905. This is the Average True Range of GLD in the 21 trading days ending 31 August.
You can copy the formula to the other rows to get a time series of ATR.
If you don’t want to see these errors and prefer empty cells, adjust the formula in cell G5 as follows:
This is an IF function checking that the row number is high enough for ATR to be calculated. If it is, the formula calculates ATR. If it isn’t, the formula returns “”. The condition is:
If the row number is greater than or equal to 4 plus the ATR period, ATR can be calculated. For example, if you set the ATR period to 2, the first row where ATR calculation is possible is row 6, because the first row of data is 4, the first row with true range is 5, and you need at least two rows with true range, which is 5 and 6.
Note that this formula only works when the first row of data is row 4. If you insert or delete rows and your data starts in another row, you will need to adjust this formula. There are different ways to make this dynamic too – I leave that up to you.
The first rows of our spreadsheet now look like this:
Exponential and Wilder’s Method
We have successfully calculated Average True Range using the simple moving average method. For the other two methods, continue to the second part.
This is the second part of the Average True Range (ATR) Excel tutorial. In the first part we have calculated ATR using the simple moving average method. Now we will calculate ATR using two other popular methods – exponential moving average and Wilder’s smoothing method.
First Part Recap
From the first part we have a spreadsheet with historical data in columns A-E, true range in column F and SMA ATR in column G.
Now we will calculate exponential moving average ATR in column H.
Exponential Moving Average Calculation Method
This method calculates ATR as exponential moving average of true range, in the same way exponential moving average of closing price works.
Each bar’s ATR is calculated as weighted average of two inputs:
- The current bar’s true range
- The previous bar’s ATR
ATR = a * TR + ( 1 – a ) * ATR.1
… where TR is current bar’s true range, ATR.1 is previous bar’s ATR and a is the smoothing factor, which is calculated from the ATR period input (n):
a = 2 / ( n +1 )
For example, when you set ATR period to 14, the smoothing factor will be:
a = 2 / ( 14 + 1 ) = 2 / 15 = 0.1333
… and each bar’s ATR will be:
ATR = 0.1333 * TR + 0.8667 * ATR.1
In other words, ATR will be composed of roughly 13% of current bar’s true range and 87% of previous bar’s ATR. These weights will change with ATR period – the shorter the period, the greater the weight of current bar’s true range and the faster ATR reacts to changing market conditions.
There is one problem though. When each bar’s ATR is calculated using the previous bar’s ATR, how do we calculate ATR for the first bar? The answer is arithmetic average – exactly the same we did in the SMA method calculation (this is why the tutorial does the SMA method first).
Excel Implementation of EMA ATR
Let’s place ATR period input in cell H2 in our spreadsheet.
The formulas in column H will use IF Excel functions and perform three different calculations, based on which row it is:
IF Conditions
Let’s first create the IFs and get the conditions right. The formula in cell H5 is:
Instead of actual calculations, I’m using the placeholders “SMA” and “EMA” for now.
There are two IF functions. The first condition checks whether the current row is smaller than 4 + ATR period. The number 4 corresponds to the first row of our data. For example, if we set ATR period to 5, this condition becomes current row < 4 + 5 and the formula will return “” in all rows above row 9. Starting from row 9, the condition will no longer be true and the next part of the formula will be executed.
The next part is another IF function. This time the condition checks whether the current row is equal to 4 + ATR period. If it is, we will calculate simple moving average (arithmetic average) of true range. If it isn’t (therefore it must be greater), we will calculate the EMA ATR formula.
With the placeholders “SMA” and “EMA” and ATR period set to 5, the results look like this:
ATR Formulas
Now we only need to replace “SMA” and “EMA” with the formulas to get the correct results.
The SMA formula is very simple – we can use the formula from the SMA ATR method in the first part of this tutorial. In the formula in cell H5, we will replace “SMA” with this:
Note: Unlike the SMA ATR method, in this case we know the range will always start in row 5, so we could actually use a simpler formula:
… and get the same result. Both the formulas above return the same thing when used with the IF condition ROW(F5)=4+H$2.
If you have chosen the first version, the entire formula in cell H5 becomes:
You can copy this formula to the other rows to check the results. If you have the IF conditions right and ATR period set to 5, you should see empty cells in rows 5-8, then the number 1.114 (the 5-period SMA ATR) in cell H9, and “EMA” in the rows below.
Let’s now replace “EMA” with a new EMA ATR formula:
ATR = a * TR + ( 1 – a ) * ATR.1
ATR = ( 2 / ( n +1 ) ) * TR + ( 1 – 2 / ( n + 1 ) ) * ATR.1
In the formula in cell H5, we will replace “EMA” with:
The entire formula in cell H5 becomes:
Two things to pay particular attention to are brackets and absolute vs. relative references.
If you copy the formula from H5 to the other rows, you should get these results for ATR period 5 and the GLD daily data we are using:
Wilder’s Smoothing Method ATR
The last of the three methods that remains is Wilder’s smoothing method. This is the one presented in New Concepts in Technical Trading Systems, the book by J. Welles Wilder that first introduced ATR to the world.
It is in fact almost the same as the EMA ATR method explained above, with just one difference.
Under the EMA method the smoothing factor a is calculated as 2 / ( n + 1). Under Wilder’s method it is simply 1 / n. The ATR formula becomes:
ATR = ( 1 / n ) * TR + ( 1 – 1 / n ) * ATR.1
Therefore, we can use the formula from the EMA method and only change the smoothing factor to get Wilder’s ATR. The formula in cell I5 is:
… where I$2 is the ATR period for this column. The part that has changed from the EMA method is the last part:
The results with our data look like this:
The Best Method and Period
You can see that the three methods give quite different results even with the same period setting. This is not a surprise when you look at the formulas.
You may be wondering which method and which period are the “correct” ones.
Regarding ATR period, popular ones appear to be 14 and 20, but there is no magic number. Consistency and suitability to your particular purpose and conditions are more important than the exact value you choose. Furthermore, discussing the best period doesn’t make any sense without also specifying which method we are using, because the period’s effect is different across methods. More here.
Regarding the calculation methods, obviously the “correct” method is the one presented by the original author of ATR, J. Welles Wilder. That said, my experience from using various financial or charting software is that all the three methods are commonly used. One problem is that many trading platforms and charting packages lack proper documentation and you don’t know which of the methods the particular software is using. This is why it’s useful to be able to calculate ATR yourself and compare other party results with your own. This is also the reason that first led me to developing the ATR Calculator.
В данной статье хотелось бы обратить внимание на один из наиболее простых методов измерения волатильности — вычислении волатильности с помощьюиндикатора ATR.
Хотелось бы отметить, что волатильность позволяет нам всегда работать в ритме рынка, помогает грамотно устанавливать размер стоп-лосса и тейк-профита.
Средний Истинный Диапазон (Average True Range, сокращенно ATR)– биржевой технический индикатор, отражающий волатильность движения актива. Автором данного индикатора стал Уэллс Уайлдер и описал его в книге «Новые концепции технических торговых систем». На данный момент ATR активно применяется трейдерами и используется во многих торговых стратегиях.
Основной смысл индикатора ATR заключается в определении среднего диапазона изменения цены за определенный период времени.
Особенности индикатора:
Первоначально Уайлдер определяет Истинный диапазон (True Range — TR), который определяется как максимальное из трех значений.
- Разница между текущим максимумом и текущим минимумом;
- абсолютное значение разницы текущего максимума и предыдущего закрытия;
- абсолютное значение разницы текущего минимума и предыдущего закрытия.
Средний истинный диапазон Average True Range (ATR) выводится из TR путем усреднения по какому-либо из методов — простому среднему, экспоненциальному или другому.
Использование индикатора ATR
Отражая свое предназначение, индикатор Average True Range, как правило, достигает высоких значений в момент сильного роста или падения биржевого инструмента, когда происходят либо панические прадажи, либо активные его покупки. В это время на бирже волатильность максимальна.
Низкие значения индикатора ATR соотносятся с продолжительными периодами бокового, нейтрального движения, которые характерны для рынка в моменты ожидания важных новостей или отсутствия большого капитала.
ATR можно использовать также, как и другие индикаторы волатильности. Принцип прогнозирования следующий: чем выше значение ATR, тем выше волатильность, а значит и вероятность изменения трендового движения; чем ниже значение индикатора, тем слабее направленность тренда.
Обычно используют 14-периодный ATR, который может быть рассчитан как на внутридневных, так и на дневных или недельных и даже месячных данных.
Экстремальные значения индикатора часто указывают на разворотные точки и или начало нового движения. Как и другие индикаторы показывающие волатильность, как, например полосы Боллинджера (Bollinger Bands), Average True Range не может предсказать направление или продолжительность движения, он указывает только на уровень активности.
Расчетная формула ATR:
ATR = Moving Average(TRj, n),
где
TRj = максимальному из модулей трех значений
|High — Low|, |High — Closej-1|, |Low — Closej-1|.
Истинный диапазон – это наибольшая из следующих величин:
— разность между текущим максимумом и минимумом;
— разность между предыдущей ценой закрытия и текущим максимумом;
— разность между предыдущей ценой закрытия и текущим минимумом.
ATR — это скользящее среднее значений истинного диапазона.
Основные недостатки:
В качестве недостатков обычно указывается один — при большом периоде ATR может запаздывать, указывая не текущую а прошлую волатильность.
Рассмотрим это на примере. Валютные пары EUR/USD и GBP/JPY. Вопрос: поставите ли вы стопы для обеих валютных пар на одном и том же расстоянии? Ответ: конечно нет.
Если вы можете рисковать 2% своего капитала и в том, и в другом случае, было бы неправильно ставить стопы для каждой из пар на одном и том же расстоянии. Почему? Да потому что пара EUR/USD двигается в среднем на 120 пунктов в день, в то время как пара GBP/JPY – на 250-300. Следовательно, нет никакого смысла в том, чтобы ставить для этих по сути разных пар стоп-приказы на одном и том же расстоянии.
Как размещать стоп-ордера, используя индикатор ATR
Посмотрите на значение ATR и устанавливайте стопы в двух или трех ATR’ах. К примеру, если на тот момент, когда вы вошли в рынок, значение ATR было 100, и вы решите разместить стоп-приказ в 2 ATR, то вам нужно умножить 100 на 2. Следовательно, вам нужно разместить стоп-приказ на расстоянии в 200 пунктов от точки входа (разместить стоп в 2 ATR).
Читайте также: