Series de tiempo estacionales en Excel

Scene 1:

Welcome to the second part of our time series data preparation tutorial. In this video I will use NumXL to examine the stationery assumption for IBM stock prices.

To start I've downloaded the IBM stock closing prices from January 3rd, 2012 to the present date now. Let's run the summary statistics for the adjusted closing prices.

First, select the NumXL tab, now click on the summary statistics icon.

Scene 2:

Select the closing prices located in column G as the input time series. Note that the current 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.

For the input time series select a range that starts and ends with G2.

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 TS sub then input the cell range to this point, then reference the cell in the descriptive stats table once again lock 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 don't have enough data so the function will return N/A. Copy the formula to the rows below it, 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, 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 walk. 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, the logarithmic closing prices as an input.

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 prices 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 is the input time series. Keep the default settings in the dialogue box then click OK, now the summary stats table is displayed select the returns the WMA and the 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:

Note that the daily log averages show a stable moving average and a 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