Commodity Channel Index (CCI)
Calculates the Commodity Channel Index, a momentum-based oscillator used to identify cyclical trends. CCI measures the current price level relative to an average price level over a given period of time.
Formula
CCI = (Typical Price - SMA of Typical Price) / (0.015 x Mean Deviation)
Where:
- Typical Price = (High + Low + Close) / 3
- SMA = Simple Moving Average over the period
- Mean Deviation = Average of absolute deviations from the mean
Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
| DataRange | Range/String | Required | OHLC data range or symbol |
| TimePeriod | Number | 14 | Lookback period for calculation |
Input Options
- Symbol string: Pass a ticker symbol to automatically fetch OHLC data
- Data range: Pass a range with columns for Open, Low, High, Close
Output
This function outputs CCI values to cells below the formula cell. Make sure you have enough empty cells below for the output.
Interpretation
| CCI Value | Signal |
|---|---|
| > +100 | Overbought condition |
| < -100 | Oversold condition |
| Crossing +100 from below | Potential sell signal |
| Crossing -100 from above | Potential buy signal |
Notes
- Returns "Commodity Channel Index" header and outputs values below
- Requires sufficient historical data for accurate calculation
- Make sure destination cells are empty to receive output
Examples
=CommodityChannelIndex("AAPL")=CommodityChannelIndex("AAPL", 20)=CommodityChannelIndex(A1:D100, 14)=CommodityChannelIndex(A1, 14)When to Use
- Identifying overbought/oversold conditions
- Spotting cyclical turning points
- Momentum-based trading strategies
- Mean reversion analysis
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Trend identification | SMA(), EMA() |
| Standard momentum | RelativeStrengthIndex() |
| Trend strength | ADX() |
| Volume-based analysis | OnBalanceVolume() |
Common Issues & FAQ
Q: Why does the output appear in multiple cells? A: CCI function outputs the array of calculated values to cells below the formula. This is by design.
Q: How do I read the CCI values? A: Values above +100 suggest overbought conditions, below -100 suggest oversold conditions.
Q: What period should I use? A: The default of 14 is standard. Shorter periods (like 9) are more sensitive, longer periods (like 20) are smoother.
Q: Why am I getting an error? A: Ensure you have enough data points (more than the TimePeriod) and sufficient empty cells below for output.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today