X-12-ARIMA demostración en Excel

Scene 1:

Hello and welcome to the NumXL seasonal adjustment tutorial. In this video we'll demonstrate how to construct a seasonal adjusted time series, build a regARIMA model and project a forecast in Excel with the help of NumXL and the US Census Bureau program called X12 ARIMA.

For this tutorial we'll use a sample of the monthly International Airlines passengers data. Let's start!

First select an empty cell in your worksheet then in the numbered cell toolbar locate the X12 icon and click on it.

Scene 2:

The X12 dialog box pops up. For input data, select the cell range and column C In the date start field, select the date of the earliest observation. Under prior adjustment let's change the transformation to none then uncheck the calendar adjustment for the intercept/constant, the Easter holiday effect and the trading day effect. Click on apply to generate the native script for the X12 program. Once done the open X12 SBC file button will be enabled. Click on this button to examine the generated script.

Scene 3:

The notepad is launched with a loaded generated script. Note that this file was generated by NumXL using the options that we have selected in the dialog. This syntax and structure of the file is compliant with those of X12A program specifications.

Scene 4:

Now let's examine the X11 filter options. The type is mult or multiplicative. The X11 options are set to defaults as specified by the X12A program. Close this file and let's go back to our dialog.

Scene 5:

Let's explicitly run the program, but note that this step is not really needed because NumXL runs the program as soon as an output values requested by worksheet functions. For now click on Run X12A button.

Scene 6:

Upon program completion a dialog box pops up. Click OK.

Scene 7:

Now the output files are created and different dialog buttons are enabled. Let's open the error file to see if the program encountered any issues.

Scene 8:

A warning of a trading day effect is present. Remember that we unchecked the prior adjustment for trading days.

Scene 9:

Now open the output file of the X12A program.

Scene 10:

This is a long and detailed text file. If you're not familiar with this format you can easily close it and use the NumXL functions.

Scene 11:

There is an enormous amount of information here. The output is best explained in the X12 ARIMA documentation found on the US Census Bureau website.

Scene 12:

Close the file. Click OK to exit the X12 wizard.

Scene 13:

Now we have a unique identifier in the D1 cell. The ID starts with X12A underscore for easy differentiation. The part on the right side can change when we change the selected options, input data, or location of the cell in your worksheet. We are ready to retrieve the seasonal adjusted trend time series. In D5 type X12ACOMP in the formula bar then click on the FX button on the left.

Scene 14:

The function argument dialog pops up.

In the first field let's reference the cell D1, then lock it for row and column movements.

In step, type the offset from the beginning of the sample data, for this tutorial we have a separate column for the index aka column A. Select the A5 cell and also lock this for column movement.

The component designates which output series we are targeting. For the final seasonal adjusted time series, type 1.

Scene 15:

The corresponding value is now in D5. Copy the formula to the rows below it. Now we can plot the seasonal adjusted time series. Copy the cells formula in D5 to the neighboring cell on the right. Then select it and click on the FX button on the left of the formula bar.

Scene 16:

Change the component from seasonal adjusted time series to seasonal factors. In order to do that let's launch the help file to get the full list of the components.

Scene 17:

The function reference page is loaded. Now we see that the seasonal factor component type is four. Close the help file.

Scene 18:

Type four under component then click OK.

Scene 19:

Copy the formula to the rows below it. Now let's go back and change the options in the X12 dialog. Select the D1 cell where the unique identifier is shown and click on the X12 ARIMA icon in the NumXL toolbar.

Scene 20:

The dialog box pops up preset with our latest selection. Let's now change the transformation to logarithmic. Then add all the regression variables. Hit apply to update the X12 script, now The unique identifier of the model is changed and the old files are deleted.

Scene 21:

Let's examine the new model specification file. Note that under transform, the function log is present. Close the file.

Scene 22:

Now let's run the program. Examine the error file for any warnings.

Scene 23:

We have a warning of seasonal and trading day effect. Close the file, hit OK to exit the wizard.

Scene 24:

The cell value is updated to reflect the new models options. The models output values in our worksheet are also automatically updated.

Scene 25:

Move the graph to the right.

Scene 26:

Now let's copy the model to a different cell.

First select the D1 cell. Then copy the cell value and formula to the clipboard. Select an empty cell on the right then paste the value in formula by pressing ctrl V. Note that the copied models identifier is not equal to the one in the D1 cell. This is to designate that those are two separate models.

Now copy the formulas in D5 and D6 into the empty cells on the right. The copied cell references the model in D1. To change that, update the first argument to reference the cell in G1. Copy the formula in G5 to H5 and update the component number to four. Now copy the formulas and G and H columns to all the rows below.

Scene 27:

By now we have a second seasonal adjusted time series. Let's plot it along with the earlier trend. To demonstrate how the two models are independent we'll change the setting in the second model. Select G1 and click on the X12 icon in the tool bar.

Scene 28:

Change the X11 filter mode to additive. Change the filter option to 3x9, click apply to generate a new script. Next examine the specification file.

Scene 29:

The X11 section shows the new mode and option.

Scene 30:

Run the program and examine the error log.

Scene 31:

The X12A reported a problem. The time series is not long enough to war an adjustment for leap year. Let's remove the calendar adjustment, close the file.

Scene 32:

Uncheck all the regression variables then click apply again to generate a new script. Now run the program. Examine the error log file.

Scene 33:

And it looks like the program ran successfully with one warning about training days.

Scene 34:

Examine the output file, quickly glance at different tests and analysis and close it.

Scene 35:

Click OK and close the X12 dialog.

Scene 36:

The two seasonally adjusted trends are not exactly on top of each other.

Scene 37:

We are now ready to display the forecast of the regARIMA model. In the L27 cell type X12 A4 function then click on the FX button.

Scene 38:

In the function arguments dialog, reference the model cell in G1. Lock it for row and column movements.

Scene 39:

Under step, select the offset from the end of the sample data, also lock for column movement. In the return type, choose one for mean forecast, click OK.

Scene 40:

Copy the formula to the right, select the copied cell and click on the FX button.

Scene 41:

Change the return type to the upper limit or three.

Scene 42:

Again copy the formula and change the return type to lower limit or two. Select the cells and copy the formulas to the rows below.

Scene 43:

Now let's plot the confidence interval and the forecast mean.

Scene 44:

That is it for now, thank you for watching!