Times series stationarity in Excel

In this tutorial, we will touch on the second most important assumption in time series analysis: Stationarity, or the assumption that a time series sample is drawn from a stationary process using functionality in NumXL 1.5X (or later).

Welcome to the second part of our time series Data preparation mini tutorial In this video, I'll use NumXL to examine the stationary assumption for IBM stock prices. To start, I downloaded the daily closing prices for IBM sock between January 3rd, 2012 to date.

Let's run summary statistics on the adjusted closing prices.

The summary statistics table is generated and displayed in your worksheet.

In the formula bar, type WMA

Click on the fx button on the left of the formular bar. The Function wizard pops up.

For input time series, select the cell range that start and ends with G2

Lock the starting cell, hit F4

Set order to 1 (chronicle order)

For the weights, we assume a 5-day equally weighted moving average, so type 5

Forecast horizin, set it to zero to designative the last step in the inpput cell. Click OK

The function returns N/A as we don't have enough data

copy the formula to the rows below it.

Next, for EWMA.

Type EWMA in the formula bar. click on the fx button

For input data, the EWMA assumes the time series has zero mean, so let's subtract the sample mean. Type TSSUB, input cell range to this point, and reference the cell in the descriptve statistics table.

Again, Lock the bginning of the cell range.

Order - 1

Leave Lambda blank

Forecast horizon, set it to zero to estimate the last step value.

The function returns N?A as we don't have enough data yet.

copy the formula to the rows below it.

format the EWMA numbrs.

A graph with 3 curves is shown next

Examine the output graph: (1) the daily volatility looks stable, but the moving average is rather trendy or a random walk.The process is not stationary

Next, we'll repeat the same steps, but for the daily log returns.

First, Let's compute the daily log returns.

Specify time order of 1, and lag order of 1 for the differencing

The first value in the return array is missing.

Similar to what we did in the WMA function, select a range of cells

Now, compute the WMA and EWMA in a similar fashion to what we did earlier with the prices time series.

Now, Let's compute the summary statistics of the daily log returns

Keep the default settings in the dialog

Now, select the returns, WMA and the EWMA cell range

The graph plot is displayed in your worksheet.

Move the graph below the summary stats, and Let's re-format.

Note that daily log returns show stable moving average and reltively stable volatility. We suspect the time series to be stationary.

Finally, examining the summary statistics table, the sample mean is not significantly different from zero. That's it. Thank you for watching. If you have any question, suggestion or comments, please send them to us at support@spiderfinancial.com

Have more questions? Submit a request