## Saturday, June 20, 2009

### 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"
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"
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

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)