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

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)