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

ATR=WWMA of TR

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
'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"
output(2, 1).Value = "=max(" & high0 & "," & close0 & ")-min(" & low0 & "," & close0 & ")"
output(2, 1).Copy output
output(1, 1).Clear
'Get ATR
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
output(3, 2).Value = "=(" & output0 & "+(" & n & "-1)*" & output1 & ")/" & n
End Sub

Other References