**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

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)*
## 0 comments:

## Post a Comment