Using spreadsheet to produce CCI indicators (Commodity Channel Index)

Stock Exchange and Market Analysis

Technical Analysis (14)

CCI (Commodity Channel Index) was setup by the US stock market analyst Donald Lambert (Donald R. Lambert) in the 1980 year of the proposed oscillators , used to observe its cyclical changes and measures the deviation of stock price and its average statistical price. Let us base on CCI to import mathematical formulas’ spreadsheet :
TP _t : first t typical day's price , using the following formula :
TP_t = ( C_t + H_t + L_t ) / 3
   C _t = closing price on the day of t
   H _t = the highest price on the day of t
   L _t = the lowest price on the day of t
TMAn _t : the n-day moving average of the t_th day typical price, the following formula :
TMAn _t
  = (TP _t + TP _t-1 + ... + TP _{t -n + 1} ) / n
MDn _T : Typical prices TP _t therewith n daily averages TMAn _t n days summed absolute deviation , the following equation :
MD _t
  = ( | TP _t - TMAn _t | + | TP _t-1 - TMAn _t-1 | + ... + | TP _{t-n + 1} - TMAn _{t-n + 1} | ) / n
CCIn _t : The n- day commodity channel indicator on the day of t, the formula is as follows :
CCIn _t
  = (TP _t - TMAn _t ) / (0.015 * MDn _t )
In order to allow the CCI indicator to be limited to a certain range of fluctuations , Donald has specially added 0.015 to achieve the above purpose . In addition, the value of n usually takes two weeks (10 days ) or the whole month (24 days ). In the following spreadsheet , we then take the 24- day example . in general , we will be based on property stocks , and observing at least one year of candlestick graph , observe the CCI index higher than 100 and less than -100 behavior , as well as its high points and low in reverse Point to the timing of the rebound , define the overbought and oversold area as a reference for entering and exiting stocks . Finally, in order to facilitate the readers to use the CCI indicator , the author still uses the model of Juyang .Continue to produce analysis samples in Excel format for reader's reference :

template14.xlsx

This sample in addition to the " Historical Prices " provide spreadsheet CCI reference index outside , and in the second spreadsheet " stock chart " to provide the candlestick graph , the typical price line , the typical price 24- day moving average , trading volume and CCI Trend line for readers' reference . If you find any mistakes, please correct me . Thank you !
In addition , the author made spreadsheet for various indicators (moving average , RSI, BIAS, stochastic KD line, William indicator , MACD, CDP and Bollinger band) for stock analysis , and further produced candlestick charts and other technical indicators on the same chart , interested readers can refer to Ref.1.
Reference (Ref):
1. Investment Financial Notes - Stock Technical Analysis