Generate Commodity Channel Index Charts for Any Ticker

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

The 0.015 in the formula attempts to restrict the majority of the movement of CCI to the range -100 to +100.

This chart gives the CCI for Caterpillar (CAT).

This chart was automatically by the free Excel spreadsheet accompanying this article.

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

Chartists typically use Commodity Channel Index with other indicators, such On Balance Volume or MACD.

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.

You can also change the time frame over which the moving average and mean deviation is calculated.

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.

Excel Spreadsheet to Generate Commodity Channel Index