This tutorial will outline the steps to define a minimal X13AS model to generate a seasonally adjusted time series for your data set.
For our sample data, we’ll use the monthly flow of international airline passengers between January 1949 and December 1960. This dataset is a popular time series sample, first covered by Box-Jenkins in their time-series reference manual back in 1970.
The U.S. Census X-13ARIMA-SEATS software is only available on 64-bit Windows machines. For more information, please consult this article:
Step 1 – Data Preparation
We arranged the observations of the sample data in two adjacent columns: dates and values, in ascending chronological order, so that the first data point corresponds to the earliest date (i.e., Jan 1949) and the last data point corresponds to the latest (i.e., Dec 1960).
We stored the values of the date component as an actual Excel date-type (i.e., date serial number), not text. In the figure above, we chose the display in the “M-YY” date format, but you can select any other date format.
Although the X-13ARIMA-SEATS model accepts datasets in reverse chronological order (i.e., descending), we chose the ascending order for our demonstration here.
Step 2 – Define an X-13ARIMA-SEATS Model
- Select an empty cell in your worksheet, preferably one that is close to the input data set and that has a free adjacent cell (to store the model specifications).
- From the NumXL toolbar, locate the ARMA icon, click on it, and select the U.S. Census X13-ARIMA-SEATS from the drop-down menu.
- The X-13ARIMA-SEATS Model (aka., X13AS) wizard or dialog box pops up on the screen.
- In the X13AS window, the Input tab is active and displayed. We need to select and describe the input data set.
- In the Input Data field, select the cells range in the worksheet of your data. You may include additional empty rows for future data points.
- In the Date Start field, select the cell with the earliest date value.
- The data set is collected every month, so no need to change the frequency field.
- Finally, select the empty cell above the currently selected cell to store the X13AS specification.
- Select the Transform tab and click on Auto for the transform function. Leave everything on this tab unchanged.
- Switch to the Seasonal Adjust tab and select the SEATS methodology. Leave everything else unchanged.
- Next, click on the Validate button. If there is no issue, notice that both the Run X-13AS and Apply controls are enabled.
- Click on the Apply command button.
- The X13AS wizard modifies the output cell’s formula and updates the text value of the SPC cell in your workbook.
The display of the text value of the SPC in the workbook looks truncated or incomplete, but it is only for show, so it does not clutter your worksheet. If you select the cell, you can view the complete text.
Step 3 – Query the Seasonally Adjusted Values
- Now, we are ready to generate the seasonally adjusted version of our time series. First, select a cell in the adjacent column to your input data set.
- In the cell, edit the formula to reference the X13ASCOMP(.) function as shown in the following figure.
If you leave the date argument empty in the X13ASCOMP(.) function, NumXL returns the whole series as an array. In Excel 2013+, Excel will auto-fill all rows below it. In earlier Excel versions (e.g., 2010), you must manually select the cells and use CTRL+ALT+Enter to populate the cells with array values.
- In this tutorial, I chose to enter a value for the date component. The function returns the value of the seasonally adjusted time series at the given date.
The blue arrows in the figure above outline the calculation dependency in output value.
- Copy the formula to all subsequent rows.
- Now, let’s plot the original and the seasonally adjusted time series.