Sharing investing and trading ideas. Helping traders get started.

Monday, June 29, 2009

Average True Range

Average True Range ATR
Comes with formula, calculation steps and VBA Code

The average true range is the Welles Wilder's moving average, WWMA of the true range, TR of a period. It is calculated as follows.


TRt=max of (Hight,Closet-1) - min of (Lowt,Closet-1)

ATR=(TRt+(n-1) x (WWMA of TR)t-1) / n

VBA Code
Method A uses functions, while Method B uses sub procedures to calculate APZ. Method B is faster and more flexible.

Method A
The average true range can be calculated by first computing the true range, before applying the WWMA function to it. The WWMA function can be obtained from here. The function for true range is as follows

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.
Private Sub Workbook_Open()
End Sub

'The rest belong to any module
Sub AddUDF()
'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.
Application.MacroOptions macro:="TR", _
Description:="Returns True Range" & Chr(10) & Chr(10) & _
"Select current high, current low, last period's close.", _
Category:="Technical Indicators"
End Sub

Public Function TR(high, low, CloseYesterday)
TR = WorksheetFunction.Max(high, CloseYesterday) - WorksheetFunction.Min(low, CloseYesterday)
End Function

To compute true range, you just have to enter into any cell, "=TR([high],[low],[previous close])". The ATR can be computed by entering, "=WWMA([previous WWMA],[current TR],[n])".

Method B
Method B offers the benefit of not having to calculate true range yourself. It does everything for you. To run Method B, you have to copy the Runthis sub of Method B from the page on Accumulation/Distribution line and the WWMA_1 sub from the page on Welles Wilder's moving average. You will run the ATR sub from the Runthis sub.

'Copy the following line into the Runthis sub
'Just before the line End Sub
'Disable all other macros that Runthis will call e.g. CLV, ADL, by
'marking them out as comments with single quotes
ATR high, low, close1, output, n

Sub ATR(high As Range, low As Range, close1 As Range, output As Range, n As Long)
'Get true range
output(0, 1).Value = "True Range"
high0 = high(2, 1).Address(False, False)
low0 = low(2, 1).Address(False, False)
close0 = close1(1, 1).Address(False, False)
output(2, 1).Value = "=max(" & high0 & "," & close0 & ")-min(" & low0 & "," & close0 & ")"
output(2, 1).Copy output
output(1, 1).Clear
'Get ATR
output0 = output(4, 1).Address(False, False)
output1 = output(3, 2).Address(False, False)
output(4, 2).Value = "=(" & output0 & "+(" & n & "-1)*" & output1 & ")/" & n
output(4, 2).Copy output.Offset(0, 1)
Range(output(1, 2), output(2, 2)).Clear
output0 = output(3, 1).Address(False, False)
output1 = output(2, 1).Address(False, False)
output(3, 2).Value = "=(" & output0 & "+(" & n & "-1)*" & output1 & ")/" & n
End Sub

Other References

Like what you have just read? Digg it or Tip'd it.
The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)


Post a Comment