NxCMA - Centered Moving-average Filter

Computes and return an array of the centered (two-sided) moving average.

Syntax

NxCMA(X, Order, TypeM, Weights, EndPoints)
X
is the univariate time series data (one dimensional array of cells (e.g. rows or columns)).
Order
is the time order in the data series (i.e. the first data point's corresponding date (earliest date=1 (default), latest date=0)).
Order Description
1 ascending (the first data point corresponds to the earliest date) (default)
0 descending (the first data point corresponds to the latest date)
Type
is the integer enumeration for the filter type: (0 = Simple moving-average (default), 1 = Binomial, 2 = Henderson, 3 = Spencer, 4 = user-defined weights)
Type Description
0 Simple two-sided equal-weighted moving average (default)
1 Binomial-weighted moving average
2 Henderson's weighted moving average
3 Spencer's weighted moving average
4 User-defined weighted moving average
M
is the number of terms (aka. weights) in the moving window. M must be odd-number.
Weights
is an array of the multiplying factors (i.e. weights) of the user-defined moving/rolling window. Only required when Type=4 (user-defined weighted MA).
EndPoints
is a logical value for applying asymmetric weights at the end of the series. If missing or omitted, Endpoints = False, and no treatment for end points is performed.

Remarks

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. 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 simple forecasting method.
5. The number of terms (i.e. M) in NxCMA must be an odd-number.
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 a 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 mutliplying factor.
• $-\frac{M}{2} \leq j \leq \frac{M}{2}$
8. For 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 Henderson type of moving average, the moving average weights are calculated as follow: $$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 mutliplying 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.