NxCMA - Centered Moving-average Filter

1. The time series is homogeneous or equally spaced.
2. The time series may include missing values (e.g. #N/A) at either end.
3. The NxCMA returns an array-type value back to Excel. The user must use CTRL+ALT+ENTER to show returned values.
4. The centered or two-sided moving averages are used to smooth a time series and be able to estimate or see the trend, one-sided moving averages can be used as a simple forecasting method.
5. The number of terms (i.e., M) in NxCMA must be odd.
6. A moving average leaves any linear trend in the original data untouched, but with Spencer's or Henderson's filters, it is possible to leave higher-order polynomials (e.g., quadratic) untouched as well.
7. The smoothed time series using the centered moving average is expressed as follow: $$ \hat{x}_t = \sum_{j=-\frac{M}{2}}^\frac{M}{2} w_j\times x_{t-j}$$ Where:
   • $\hat{x}_t$ is the smoothed data set at time t
   • $M$ is the number of terms in the moving average: $M$ must be an odd positive number.
   • $w_j$ is the j-th weight or the multiplying factor.
   • $ -\frac{M}{2} \leq j \leq \frac{M}{2} $
8. For the Binomial-weighted moving average, the weights are calculated as follow: $$ w_j=\binom{M}{\frac{M}{2}+j} \times 2^{-M} $$ Where:
   • $M$ must be greater or equal to 3.
9. For the Henderson moving average, the moving average weights are calculated as follows: $$w_j = 315\times\frac{(k-1)^2 - j^2)(k^2-j^2)((k+1)^2-j^2)((3k^2-16)-11j^2)}{8k(k^2-1)(4k^2-1)(4k^2-9)(4k^2-25)}$$ where:
   • $ k=\frac{M+3}{2} $
   • $m$ is the number of terms in moving average: $m$ is odd integer greater or equal to 3 ($m \geq 3$).
   • $w_j$ is the j-th weight or the multiplying factor.
10. For Spencer type of filters, the number of terms can be either 15 (quarterly data) or 21 (monthly data).
11. For 15-terms spencer moving average, the weights are: $$w_j = \frac{a_j}{320}$$ Where: $$a = [ -3, -6,-5, 3, 21,46,67,74,67,46,21,3,-5,-6,-3 ] $$
12. For 21-terms spencer moving average, the weights are: $$w_j = \frac{a_j}{1750} $$ Where: $$a=[-5, -15, -25,-25,-10, 30, 90,165, 235, 285, 300, 285, 235, 165, 90, 90, 30, -10, -25, -25, -15, -5 ] $$
13. If the keepNA argument is set to True(1), cells with missing value are preserved and are sampled equally with other observations and represented by #N/A in the returned array.
14. The NxCMA function is available starting with version 1.66 PARSON.

Files Examples

Related Links

• Wikipedia - Moving average

References

• D. S.G. Pollock; Handbook of Time Series Analysis, Signal Processing, and Dynamics; Academic Press; Har/Cdr edition(Nov 17, 1999), ISBN: 125609906
• James Douglas Hamilton; Time Series Analysis; Princeton University Press; 1st edition(Jan 11, 1994), ISBN: 691042896
• Tsay, Ruey S.; Analysis of Financial Time Series; John Wiley & SONS; 2nd edition(Aug 30, 2005), ISBN: 0-471-690740
• Box, Jenkins, and Reisel; Time Series Analysis: Forecasting and Control; John Wiley & SONS.; 4th edition(Jun 30, 2008), ISBN: 470272848
• Walter Enders; Applied Econometric Time Series; Wiley; 4th edition(Nov 03, 2014), ISBN: 1118808568