Keltner Channel

Keltner Channel KC

Comes with formula, calculation steps and VBA code

Introduction

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)