Module 3 - Smoothing

In module one (1), we demonstrated the data preparation phase of time series analysis. In module two (2), we described few steps to calculate numerous summary statistics and verify the significance of their values.

In this module, we will walk you through time series smoothing in Excel using NumXL functions and tools. For sample data, we’ll use the S&P 500 weekly closing prices between January 2009 and July 2012.

NumXL supports numerous smoothing functions, but each function assumes a particular characteristic about the sample data.

SPDR (S&P 500 ETF) price plot with a linear trend line between Jan 2009 and Sept 2012.

Let’s consider the S&P 500 weekly close prices time series between Jan 2009 and July 2012. The time series exhibits a trend over time.

Using equally-weighted moving average (WMA) with a window size of 4 weeks, forecasting into the next 12 weeks, we find:

SPDR (S&P 500 ETF) price plot, deterministic linear trend and 12-months equally-weighted moving average (WMA) function.

The WMA keeps pace with the original data, but it is lagging. Furthermore, the out-of-sample forecast is flat.

Assuming the trend is deterministic (non-stochastic), we can use the Holt-Winter’s double exponential smoothing functions (DESMTH).

SPDR (S&P 500 ETF) price plot, deterministic linear trend and double exponential smoothing curve.

The double exponential smoothing functionWe used optimal values for the smoothing parameters of the exponential smoothing function tracks the data pretty well and the forecast looks inline with the original curve. Is this it? Did we find a crystal ball that tells us where the price will be each week? Not quite!

Earlier, we made the assumption that the trend is deterministic (non-stochastic), but the price is more like a random-walk process, so the trend we observe is just an anomaly that can occur in the random-walk.


The Augmented Dickey-Fuller unit-root test (ADF Test) in NumXL can test for the presence of a unit-root (i.e. random-walk) in the presence of drift and/or trend.

$$(1-L)y_t=\triangledown y_t =\alpha + \gamma y_{t-1}+\beta \times t + \cdots$$

The ADF test is basically $H_o:\gamma = 0$

Augmented Dickey-Fuller (ADF) test for stationarity. The table is generated using the NumXL ADF Wizard fo r different stationary scenarios.

The unit-root existance is confirmed in all 3 different formulations: no constant ($\triangledown y_t =\gamma y_{t-1}+ \epsilon_t$), constant ($\triangledown y_t =\alpha + \gamma y_{t-1}+ \epsilon_t$) and constant+trend ($\triangledown y_t =\alpha + \gamma y_{t-1}+\beta \times t+ \epsilon_t$).

The time series is integrated (i.e. has a unit-root), so we need to take the first difference to stabilize (i.e. make stationary) the input data:

$$z_t = (1-L)y_t=\triangledown y_t$$

Have more questions? Submit a request