NxSMA - Seasonal Moving-average Filter

Computes Seasonal (aka. double) moving average (aka. KxM MA).

Syntax

NxSMA(X, Order, K, N, EndPoints)

X

is the univariate time series data (a 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).

K

is the number of terms (aka points) of the second moving average filter. If missing, K is assumed 1.

M

is the number of terms (aka points) in the first moving average filter (e.g., seasonal length). If missing or omitted, N is assumed 1.

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 Endpoints 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 NxSMA returns an array-type value back to Excel. The user must use CTRL+ALT+ENTER to show returned values.
4. The number of terms (i.e., K+N-1) in NxSMA must be an odd number. In effect, K and N can be either both odd numbers or both even numbers.
5. In practice, the NxSMA is known as the double moving average filter: which combines two MA to form a symmetric two-sided moving average filter.
6. Why use NxSMA? When working with time series you often smooth to remove seasonality effects, which requires a period (aka moving window) to be equal to a seasonal length, which is often an even number, for example, 12 months or 4 quarters. In this case, we use the double moving average (e.g., 2x4 for quarterly data, or 2x12 for monthly data, etc.).
7. Mathematically, the order in which the MA filters are applied does not matter, and they will return the same results. For our application,
8. The weights or the multiplying factors of the NxSMA filter are derived by taking the convolution of the two moving average filters (K-MA and N-MA).
9. The weights or the multiplying factors of the NxSMA are calculated as follows: $$w_j=\left\{\begin{matrix} \frac{j}{K\times M} & j < K\\ \frac{1}{M}& K \leq j \leq M\\ \frac{M+K-j}{K\times M} & M+K > j > M \end{matrix}\right.$$
10. By definition, the sum of all weights in the SMA is equal to one(1).
11. The NxSMA function is available starting with version 1.66 PARSON.

Files Examples

Related Links

• Wikipedia - Autocorrelation function (ACF).

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.