In this video, we'll demonstrate the steps to define a minimal X13AS model to generate a seasonally adjusted time series for your data set. with the help of NumXL and the U.S. Census Bureau program "X-13ARIMA-SEATS."
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.
Hello, and welcome to the X13ARIMA-SEATS seasonal adjustment series.
The X-13ARIMA-SEATS software allows for the same seasonally adjustment methods as X-12-ARIMA and an enhanced version of the Bank of Spain's SEATS software.
In this part, we’ll demonstrate the steps to conduct seasonal adjustment using SEATS methodology in Excel using the NumXL functions and wizard.
For the dataset, we will use the monthly flow of international airline passengers between January 1949 and December 1960.
This dataset is a popular time series sample, first covered in Box-Jenkins in their time series reference manual back in 1970.
We arranged the observations of the data set in two adjacent columns: dates and values, in ascending chronological order, so the first data point corresponds to the earliest date (Jan 1949), and the last data point corresponds to the latest date (Dec 1960).
We selected an empty cell in our worksheet (D6), which has an adjacent empty cell (D5) to store the X13 model specification.
Locate and click on the ARMA icon on the NumXL Toolbar.
From the drop-down menu, select the U.S. Census X13-ARIMA-SEATS item to launch the X13 Wizard.
The X13ARIMA-SEATS Model wizard or dialog box pops up on the screen.
Select the Input data in column B (from row 9 to row 152).
The data is stored in ascending chronological order, so leave the ascending option checked.
The data is a flow-type (vs. aggregate or stock), so leave the stock Data unchecked.
The data is collected on a monthly basis, so the default monthly frequency applies to our case.
Upon program completion a dialog box pops up. Click OK.
Let’s select the start date of the earliest observation: A9.
Now, let’s examine the model specification field. This field designates a cell in our worksheet where we store a text string encoding the different options/selections we made for our model.
You can specify any empty cell to store the model specification string, but we recommend keeping it adjacent, or close to the model’s cell.
Select the Transform tab.
Initially, there is no transform function selected.
Let’s select Auto and let the X13 decide whether we should transform the data or not, prior to modeling.
Select the Outliers tab.
By default, the additive outlier and level shift are selected. For our purposes, this setting is acceptable.
Select the Seasonal Adjustment tab.
Initially, no seasonal adjustment method is selected.
Click or select SEATS.
The SEATS inner tab is enabled now and shows the different settings it can accept.
In addition to the seasonal adjustment, the X13 constructs a RegARIMA (ARIMA + Regression) model of our data set. In this tab, we specify the ARIMA component of the model.
By default, the Auto(TRAMO) option is selected. TRAMO works well with SEATS, so let’s leave it.
Let’s examine the Forecast panel on this tab. Initially, the forecast horizon is set to one year (12 data points).
TRAMO and SEATS expect at least 36 data points, so we’ll change it to 3 years.
Select the Regression tab.
Initially, no regression is selected.
Select the Missing values tab.
In this tab, we select the policy to handle intermediate data point(s) with a missing value.
Data points with missing values at either end of the time series are dropped and the start date is adjusted.
Initially, there is no policy selected for handling data points with missing values. Our dataset does not have missing values, so this will do.
Now, let’s validate the selected options and the model.
Click on the Validate button.
To validate, the X13 Wizard generates the inputs files, runs the US census seasonal adjustment software in validation mode, gathers and reports back the result.
Model validation is successful: The Validate button is grayed out (disabled), and the Run X-13AS and Apply buttons are enabled.
Let’s examine the program error file. Press the Error File button on the right.
The error file (generated by the US census X13 software) is opened in a notepad.
Let’s examine the raw input file for the US Census X13 program. Press the Spec File button on the right.
The X13 model’s input specification file is opened in a notepad window. The filename has a SPC extension and it adheres to strict syntax.
Now, we will run the model in full mode, and examine any error or warning. Press the Run X13AS button.
Upon the program completion, a status window pops up.
In our case, the run was successful with no error. Click on the OK button.
Let’s open the error file. Click the Error File button on the right.
The error file reports two (2) warnings and one (1) note. It is related to the trading day effect.
How should we handle it? We can add the trading day into the regression, but we’ll leave this to a later tutorial.
Now we are happy with our model and ready to write it into our worksheet. Press the Apply button.
The X13 Wizard closes. The two cells D6 and D5 have the X13 model identifier and the encoded string text of the specification.
Let’s trace the dependencies of cell D6.
D6 references the start date in A9, the input data set in B9:B152 and D6 (model specification text string).
Let’s look closer at the model specification text (D5).
The X13 model specification is a structured JSON text for the different options/settings we made in our model.
The model specification string does not contain reference to the input data or start date, so it is possible to share the same model specification with another (or several) X13 models.
Now, let’s examine the seasonally adjusted version of the input time series.
To do this, select the empty cell C9.
In the formula toolbar, begin typing (=X13AS…) and a drop-down of candidate functions is displayed. Select X13ASCOMP() for the component.
Now that we have the X13ASCOMP() selected, click the (fx) button (on the left of the formula toolbar) to invoke the Function Arguments Dialog.
In the X13ASCOMP(.) Function Arguments dialog, select the referenced X13 model.
Our model is in D6, so select this cell
For the desired component, type 0 (or leave blank) for the seasonally adjusted version of the time series.
For the date (or dt), leave it blank to instruct the function to return the whole time series.
Viola, the seasonally-adjust version of the input time series is shown in column C.
Let’s plot the seasonally adjusted time series with the original (un-adjusted) input time series.
That’s all for now.
NumXL takes care of all the number crunching and heavy lifting, giving you – the subject matter expert – the time to exercise your intuitions to filter through the different models, fine-tune the parameters and arrive at the one that makes sense to you.
Check with us again later for updates and enhancements to seasonal adjustment and X13ARIMA-SEATS modeling.
Thank you for watching!