Plot the Commodity Channel Index for any stock by entering a few parameters and clicking a button.
Donald Lambert introduced CCI in his book “Commodities Channel Index: Tools for Trading Cyclical Trends”. Pretty much as the title says, CCI helps you filter out noise, revealing cyclical trends in price action.
Explore this popular technical indicator with the Excel spreadsheet that comes with this article. The VBA can be viewed, dissected, modified and altered.
CCI balances the the current price relative to the average price over a lookback period. If CCI is high, the stock is overbought. However, if CCI is low, the stock in undersold.
CCI is simple to calculate – you only need the high, low and close price for a stock. Here are the equations
This chart gives the CCI for Caterpillar (CAT).
Identify Trends, and Oversold/Overbought Levels
Sharp moves outside the range -100 to +100 indicate the potential emergence of a new trend.
Typically, a buy signal is indicated when CCI rises above +100 (with a sell signal when the CCI falls back below +100). The reverse is true if CCI falls below -100 (with a buy signal when the CCI rises back above -100).
Effect of Lookback Period
The n-day moving average and the mean deviation (and hence the indicator) is strongly influenced by the chosen lookback period.
A smaller lookback period
- introduces more volatility into the indicator.
- encourages the indicator to move outside -100 to +100
- is used to identify long term trends
A larger lookback period
- smooths out the indicator
- discourages the indicator to move outside -100 to +100
Decision Support Tool
Spreadsheet to Generate Commodity Channel Index Charts
This Excel spreadsheets will calculate the Commodity Channel Index for any stock, using data downloaded from Yahoo Finance. The computation is automated in VBA – you can examine, modify and learn from the code.
Here’s the part of the VBA that calculates the CCI (the rest is in the spreadsheet). The code assumes that high, low and close prices are entered into columns C, D and E of sheet dataSheet.
'CCI Calculation timeWindow = parameterSheet.Range("timeWindow").Value meanDeviationFormula = "=(" For i = 0 To timeWindow - 1 meanDeviationFormula = meanDeviationFormula & "ABS(RC[-1]-R[-" & i & "]C[-2])" If i < timeWindow - 1 Then meanDeviationFormula = meanDeviationFormula & "+" End If If i = timeWindow - 1 Then meanDeviationFormula = meanDeviationFormula & ")/" & timeWindow End If Next dataSheet.Range("G1") = "Typical Price" dataSheet.Range("G2:G" & nRows).FormulaR1C1 = "=(RC[-4] + RC[-3] + RC[-2])/3" dataSheet.Range("H1") = timeWindow & "-Day SMA of TP" dataSheet.Range("H" & timeWindow + 1 & ":H" & nRows).FormulaR1C1 = "=AVERAGE(RC[-1]:R[-" & timeWindow - 1 & "]C[-1])" dataSheet.Range("I1") = timeWindow & "-Day Mean Deviation" dataSheet.Range("I" & timeWindow + 1 & ":I" & nRows).FormulaR1C1 = meanDeviationFormula dataSheet.Range("J1") = timeWindow & "-Day CCI" dataSheet.Range("J" & timeWindow + 1 & ":J" & nRows).FormulaR1C1 = "=(RC[-3]-RC[-2])/(0.015*RC[-1])"
Note that the formula for the mean deviation is
- programmatically constructed inside a For loop as a string.
- and entered into the spreadsheet using the FormulaR1C1 format
Other approaches could have been chosen, but this method was used to highlight formulas can be programmatically constructed.