Module 3 - Smoothing

In module one (1), we demonstrated the data preparation phase of time series analysis. In module two (2), we described a 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 of the sample data.

Price plot of SPDR (S&P 500 ETF) with a linear trend line between January 2009 to September 2012.

Let’s consider the S&P 500 weekly close price 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:

Price plot of SPDR (S&P 500 ETF) with a deterministic linear trend and a 4-week 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 Holt-Winter’s double exponential smoothing functions (DESMTH).

Price plot of SPDR (S&P 500 ETF) with a deterministic linear trend and Holt-Winters' double exponential smoothing curve.

The double exponential smoothing function used optimal values for the smoothing parameters of the exponential smoothing function tracks the data pretty well and the forecast looks in line 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$.

Table showing the results of the Augmented Dickey-Fuller (ADF) test for stationarity, generated using the NumXL ADF Wizard for different stationary scenarios.

The unit-root existence 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$).
  • 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$$

Support Files


Article is closed for comments.

Was this article helpful?
0 out of 1 found this helpful