Sharing investing and trading ideas. Helping traders get started.

Tuesday, June 23, 2009

Keltner Channel

Keltner Channel KC
Comes with formula, calculation steps and VBA code

To compute the Keltner channel, you need to specify 2 values

a) n, the number of historical periods the channel is based on and
b) k, the width multiplier of the channel

Step 1: Compute the typical price of each period

Typical Price, TP = (High + Low + Close)/3

Step 2: Calculate the simple moving average representing the middle of the Keltner channel, SMA Middle

SMA Middle, SMAM=Average of past n typical prices

Step 3: Calculate the Range of each period

Range, R = High – Low

Step 4: Calculate the Width of the channel

Width, W = SMA of R for the past n days x k

Step 5 Compute upper and lower bands of the channel

SMA Lower = SMAM – Width
SMA Upper = SMAM + Width

Breakouts above or below the upper and lower bands are considered to be bullish or bearish signals respectively.

VBA Code
As usual, two methods are provided. Method A creates an array function called KC to output the upper, middle and lower bands of the Keltner channel using the last n days of high, low and close, and a user specified channel width multiplier as the inputs.

Method B consists of a sub KC_1 called from a Runthis sub to calculate the outputs. The inputs are specified i nthe Runthis sub.

Method A

Sub AddUDF()
Application.MacroOptions macro:="KC", _
Description:="Returns Upper, Middle and Lower bands of the Kelter Channel" & Chr(10) & Chr(10) & _
"Select last n periods of High, Low and Close. Input width multiplier", _
Category:="Technical Indicators"
End Sub

Public Function KC(high, low, close_, widthmult)
TypicalPrice = 0
Range1 = 0
n = WorksheetFunction.Count(close_)
For a = 1 To n
TypicalPrice = TypicalPrice + ((high(a, 1) + low(a, 1) + close_(a, 1)) / 3)
Range1 = Range1 + (high(a, 1) - low(a, 1))
Next a
SMARange = Range1 / n
bandwidth = SMARange * widthmult
Dim result(0, 1 To 3)
result(0, 2) = TypicalPrice / n 'Middle Band
result(0, 1) = result(0, 2) + bandwidth
result(0, 3) = result(0, 2) - bandwidth
KC = result
End Function

To use Method A, input into any cell "=KC([high],[low],[close],[width multiplier]

Method B

Sub Runthis()
Dim high As Range, low As Range, close1 As Range
Dim output As Range, n As Long, band_factor As Double

Set high = Range("C2:C11955")
Set low = Range("D2:D11955")
Set close1 = Range("E2:E11955")
Set volume = Range("F2:F11955")
Set output = Range("H2:H11955")

n = 5 'number of historical periods to look at
'needed for certain indicators
band_factor = 1.5

KC_1 high, low, close1, output, n, band_factor
End Sub

Sub KC_1(high As Range, low As Range, close1 As Range, output As Range, n As Long, band_factor As Double)
output(0, 1).Value = "Typical Price"
high0 = high(1, 1).Address(False, False)
low0 = low(1, 1).Address(False, False)
close0 = close1(1, 1).Address(False, False)
output(1, 1).Value = "=(" & high0 & "+" & low0 & "+" & close0 & ")/3"
output(1, 1).Copy output
output(0, 2).Value = "Range"
output(1, 2).Value = "=" & high0 & "-" & low0
output(1, 2).Copy output.Offset(0, 1)
output(0, 3).Value = "SMA(Range)"
Rrange = Range(output(1, 2), output(n, 2)).Address(False, False)
output(n, 3).Value = "=average(" & Rrange & ")"
output(0, 4).Value = "Upper KC"
output(0, 5).Value = "Middle KC"
output(0, 6).Value = "Lower KC"
RangeM = Range(output(1, 1), output(n, 1)).Address(False, False)
output(n, 5).Value = "=average(" & RangeM & ")"
Rrange = output(n, 3).Address(fale, False)
MiddleK = output(n, 5).Address(False, False)
output(n, 4).Value = "=" & MiddleK & "+" & Rrange & "*" & band_factor
output(n, 6).Value = "=" & MiddleK & "-" & Rrange & "*" & band_factor
Range(output(n, 3), output(n, 6)).Copy output.Offset(0, 2)
Range(output(1, 3), output(n - 1, 6)).Clear
End Sub

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)


Anonymous said...

great b.shit

Post a Comment