Commodity Channel Index CCI

Commodity Channel Index, CCI

Comes with formula, calculation steps and VBA code

CCI = (Current Typical Price – Simple Moving Average of Typical Price) / (k x Mean Deviation)

, where

Current Typical Price = (High + Low + Close) / 3

Simple Moving Average of Typical Price = Sum of Typical Price over the last n periods / n

Mean Deviation =

Sum of absolute of (each typical price in the last n days – Current Simple Moving Average of Typical Price) / n

, k is value specified by the user.

The CCI measures how the current typical price exceeds its simple moving average as a percentage of historical deviation. The constant k determines how often the CCI exceeds +/- 100.

VBA Code

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 output = Range("H2:H11955")

n = 5 'number of historical periods to look at

'needed for certain indicators

band_factor = 1.5

CCI_1 high, low, close1, output, n, band_factor

End Sub

Sub CCI_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 = "Simple Moving Average"

SMARange = Range(output(1, 1), output(n, 1)).Address(False, False)

output(n, 2).Value = "=average(" & SMARange & ")"

output(n, 2).Copy output.Offset(0, 1)

output(0, 3).Value = "Deviation"

TP = output(n, 1).Address(False, False)

SMARange = output(n, 2).Address(False, False)

output(n, 3) = "=abs(" & TP & "-" & SMARange & ")"

output(n, 3).Copy output.Offset(0, 2)

output(0, 4).Value = "Mean Deviation"

DevRng = Range(output(n, 3), output(n * 2 - 1, 3)).Address(False, False)

output(n * 2 - 1, 4).Value = "=average(" & DevRng & ")"

output(0, 5).Value = "CCI"

TP = output(n * 2 - 1, 1).Address(False, False)

SMA = output(n * 2 - 1, 2).Address(False, False)

MeanDev = output(n * 2 - 1, 4).Address(False, False)

output(n * 2 - 1, 5).Value = "=(" & TP & "-" & SMA & ")/(" & band_factor & "*" & MeanDev & ")"

Range(output(n * 2 - 1, 4), output(n * 2 - 1, 5)).Copy output.Offset(0, 3)

Range(output(1, 2), output(n - 1, 5)).Clear

Range(output(n, 4), output(n * 2 - 2, 5)).Clear

End Sub

Other References

http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:commodity_channel_index_cci