In this tutorial, we will discuss 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 the functionality in NumXL 1.5X (or later).
Scene 1:
Welcome to the second part of our time series data preparation tutorial. I will use NumXL in this video to examine the stationary assumption for IBM stock prices.
To start, I've downloaded the IBM stock closing prices from January 3rd, 2012, to the present. Let's run the summary statistics for the adjusted closing prices.
First, select the NumXL tab, then click on the summary statistics icon.
Scene 2:
Select the closing prices located in column G as the input time series. Note that the currently selected cell is used as the default output range. Leave this unchanged, and then click OK.
Scene 3:
The descriptive stats table has been generated, and it is now located in your worksheet. Now, let's plot the WMA and the EWMA in the formula bar type WMA.
Scene 4:
Click the FX button to the left of the formula bar to make the function wizard appear.
Select a range that starts and ends with G2 for the input time series.
Hit F4 to lock the starting cell. Set the order equal to one for a chronicle order.
For the weights, type five for a five-day equal-weighted moving average.
Set the forecast horizon to zero to designate the last step in the input cell, then click OK.
Scene 5:
The function should return N/A since we don't have enough data.
Scene 6:
Copy the formula to the rows below it, and now for the EWMA. Type EWMA in the formula bar, then click the FX button.
Scene 7:
For the input data, the WMA assumes the time series has no mean, so let's subtract the sample mean.
Type TSSUB, input the cell range to this point, and reference the cell in the descriptive stats table once again, locking the beginning of the cell range.
Set the order equal to 1 and leave the lambda parameter blank.
Set the forecast horizon to zero to estimate the last step value.
Scene 8:
We still need more data to make the function return N/A. Copy the formula to the rows below it, and now format the EWMA numbers.
Scene 9:
Let's plot the closing pricing with the moving average and the exponential volatility.
Scene 10:
Select the three columns, switch to the Insert tab, and click Online Curve. Now, we see a graph with three curves.
Move the curve to the side and reformat it.
Scene 11:
Examine the output graph. Notice that the daily volatility looks stable, but the moving average is rather trendy or moving. The process is not stationary.
Scene 12:
Now, we can repeat the same steps but for the daily log returns. First, we need to compute the log returns. We use the diff function, which inputs the logarithmic closing prices.
Specify a time order of 1 and a lag order of 1 for differencing. Notice that the first value in the return array is missing.
As we did with the WMA function, select a range of cells, hit f2, then press ctrl shift and enter together.
Scene 13:
Now compute the WMA and the EWMA as we did with the price time series. Now compute the summary stats for the daily log returns. Switch to the NumXL tab and click the descriptive stats icon.
Scene 14:
Now select the returns cell range for the input time series. Keep the default settings in the dialogue box, then click OK. The summary stats table is displayed. Select the returns of the WMA and EWMA cell ranges.
Switch to the insert tab and select a 2d line-type graph.
Scene 15:
The graph plot is now displayed in your worksheet. Let's move the graph below the summary stats and then reformat it.
Scene 16:
The daily log averages show a stable moving average and relatively stable volatility. We can suspect the time series is stationary. Take a look at the summary stats table and notice that the sample mean is not significantly different from 0.
That's it thank you for watching, if you have any questions, suggestions, or comments, be sure to email us at support@numxl.com
Comments
Article is closed for comments.