Commodity Channel Index (CCI) calculation in Excel with Array functions

One of the difficulties in calculating the CCI (stockcharts or Gstock) is you get can get a cell with a calculation like:

=(ABS(H3-G3)+ABS(H3-G4)+ABS(H3-G5)+ABS(H3-G6)+ABS(H3-G7)+ABS(H3-G8)+ABS(H3-G9)+ABS(H3-G10)+ABS(H3-G11)+ABS(H3-G12)+ABS(H3-G13)+ABS(H3-G14)+ABS(H3-G15)+ABS(H3-G16)+ABS(H3-G17)+ABS(H3-G18)+ABS(H3-G19)+ABS(H3-G20)+ABS(H3-G21)+ABS(H3-G22))/20

because the basic CCI calculation is the formula:

(typical price – n day simple moving average of typical price) / (.015 * mean deviation)

Where mean deviation is the average of the absolute values of the difference between the last n days typical price and the current n day simple moving average (hence the sum of abs(H3-Gx)).

If you want to change the number of periods from 20, to say, 99, then the formula becomes very tedious to enter.   However, it seems there is an easier way to enter the formula in Excel (at least from a number of characters to type perspective) and that is to use array formulas (which I used in the medianifs calculation in a prior post).  The first 11 columns are from the example in Gstock. Column G is simply the typical price (pivot value)

=(C3+D3+E3)/3

The first new column – which is column L – the first row is the number of periods, and the rest of the column is simply the formula

=(“G”&ROW()&”:G”&(ROW()+$L$1-1))

I did this primarily because I was having problems with getting the array function to work properly when this was part of the formula in column N, but it has the side effect of making the formula in m3 simple:
=AVERAGE(INDIRECT(L3))

The formula in column N is displayed in column O – remember this is an array function, so rather than pressing enter, you need to press ctrl shft enter.

The CCI calculation now becomes – for cell P3 :

=(G3-M3)/(0.015*N3)

Changing the value in L1 from 20 to 99 will recalculate the CCI in column P to be based on 99 periods.

The original Gstock spreadsheet example:

Data Table Step-1 Step-2 Step-3 CCI
Date Open High Low Close Volume
09/19/1996 0.86 0.9 0.85 0.9 ### 0.883 0.823 0.033 121.425
09/18/1996 0.9 0.9 0.85 0.86 ### 0.870 0.818 0.032 107.904
09/17/1996 0.88 0.9 0.86 0.9 ### 0.887 0.815 0.030 160.638
09/16/1996 0.86 0.87 0.85 0.86 ### 0.860 0.811 0.026 125.160
09/13/1996 0.83 0.87 0.82 0.86 ### 0.850 0.809 0.024 117.092
9/12/1996 0.78 0.81 0.78 0.81 ### 0.800 0.808 0.023 -23.090
9/11/1996 0.78 0.79 0.77 0.79 ### 0.783 0.810 0.024 -74.023
9/10/1996 0.78 0.8 0.77 0.77 ### 0.780 0.811 0.024 -87.470
9/9/1996 0.76 0.79 0.76 0.78 ### 0.777 0.808 0.027 -78.407
9/6/1996 0.76 0.78 0.76 0.77 ### 0.770 0.805 0.029 -79.563
9/5/1996 0.77 0.78 0.77 0.77 ### 0.773 0.805 0.030 -69.475
9/4/1996 0.79 0.8 0.78 0.79 ### 0.790 0.803 0.032 -28.070

And additional columns (L-P) showing the alternative calculation method:

Col L Col M Col N Col O Col P
20 Step 2A Step-3A CCI
G3:G22 0.823 0.033 {=SUM(ABS(INDIRECT(L3)-M3))/$L$1} 121.425
G4:G23 0.818 0.032 {=SUM(ABS(INDIRECT(L4)-M4))/$L$1} 107.9038
G5:G24 0.815 0.030 {=SUM(ABS(INDIRECT(L5)-M5))/$L$1} 160.6381
G6:G25 0.811 0.026 {=SUM(ABS(INDIRECT(L6)-M6))/$L$1} 125.1596
G7:G26 0.809 0.024 {=SUM(ABS(INDIRECT(L7)-M7))/$L$1} 117.0916
G8:G27 0.808 0.023 {=SUM(ABS(INDIRECT(L8)-M8))/$L$1} -23.0901
G9:G28 0.810 0.024 {=SUM(ABS(INDIRECT(L9)-M9))/$L$1} -74.023
G10:G29 0.811 0.024 {=SUM(ABS(INDIRECT(L10)-M10))/$L$1} -87.4704
G11:G30 0.808 0.027 {=SUM(ABS(INDIRECT(L11)-M11))/$L$1} -78.4067
G12:G31 0.805 0.029 {=SUM(ABS(INDIRECT(L12)-M12))/$L$1} -79.5626
G13:G32 0.805 0.030 {=SUM(ABS(INDIRECT(L13)-M13))/$L$1} -69.4752
G14:G33 0.803 0.032 {=SUM(ABS(INDIRECT(L14)-M14))/$L$1} -28.0702

Here is a spreadsheet with the calculations, including an alternative method using the offset() function.
gstock CCI with array function

Advertisements

7 Responses to “Commodity Channel Index (CCI) calculation in Excel with Array functions”

  1. dj Says:

    thanks

  2. Y Starter Says:

    ={SUM(ABS(OFFSET(G3,0,0,$L$1,1)-M3))/$L$1}

  3. Alligator Shop Says:

    Hello, I’m having problems with the formula described in the M column… appears #REF error, I would like you to provide the spreadsheet to download with the calculation of CCI, you can do it? would be easier to view the formulas, thank you.

  4. Russell Says:

    This formula looks forward. IE the result in row 3 is based on the values in rows 3 to 22… thats not how CCI works

    Should be that the result in row 22 is based on values in rows 3 to 22

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: