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