Skip Main Navigation Skip To Navigation Content

X-13ARIMA-SEATS Seasonal Adjustment Program

You are here: Census.govSubjects A to ZX-13ARIMA-SEATSWin X-13 & X-13-Data › X-13-Data Documentation
Skip top of page navigation

X-13-Data Version 1.1

Nikki Czaplicki
U.S. Census Bureau
March 19, 2014

Table of Contents


1. Introduction

X-13ARIMA-SEATS cannot read data from a Microsoft Excel file. Nor does X-13ARIMA-SEATS produce Excel output.

X-13-Data is a tool that can:

  • Convert data from Microsoft Excel to a text file in datevalue format.
  • Convert X-13ARIMA-SEATS output files in x12save format to a Microsoft Excel file.

Licensing information for this software can be found at http://www.census.gov/srd/www/disclaimer.html.

Return to contents.


2. Requirements

X-13-Data has been tested on Windows XP and Windows 7, and Windows 8. To run the program, Microsoft.NET Framework 3.5 must be installed on your computer. It can be downloaded at http://www.microsoft.com/en-us/download/details.aspx?id=21. Link to a non-federal Web site

X-13-Data also requires Microsoft Excel installed on your computer and has been tested with Microsoft Excel 2007 and Microsoft Excel 2010.

Return to contents.


3. Getting Started

  1. Download X13Data.zip.
  2. Unzip X13Data.zip.
  3. Double click on X13Data.exe to launch. The X-13-Data main screen will display.

    X-13-Data Main Screen

    Keyboard Shortcuts:
    Browse=Alt+b
    Go=Alt+g
    Cancel=Alt+c

  4. From here, you can choose to convert your Excel data into a file that can be read by X-13ARIMA-SEATS (see Section 4), or convert your X-13 output files to Excel files (see Section 5).

Return to contents.


4. Convert from Excel to X-13.

X-13-Data will read data in a Microsoft Excel file in a variety of formats (all described in Section 6) and convert it to an X-13-Data file in datevalue format. (See the X-13ARIMA-SEATS Reference Manual for more information about datevalue and other accepted formats.)

  1. Select Excel to X-13 file from the X-13-Data main page and press Go. The X-13-Data Excel Reader window will appear.
  2. Select a Microsoft Excel file by either typing the complete file name in the Excel File text box or navigating to the file using the Browse button. Once an existing Excel file has been selected, the other controls will become available; use them to select the output file, define the data format, and locate the data within the spreadsheet, as described below.

    X-13-Data: Excel Reader

    Keyboard Shortcuts:
    Browse=Alt+b
    Go=Alt+g
    Cancel=Alt+c

  3. Select the Excel worksheet that contains the data using the Worksheet Number box.
  4. Select the format that your data are in using the Data Format box. See Data Formats below for more information on supported formats.
  5. If you are reading a single series with Vertical or Horizontal format, use the Save File box to select a name for the data file that will be created.
    1. The file must have a .dat extension. X-13-Data will change or add the .dat extension, if necessary.
    2. If a file with the selected name already exists, a box will display asking if you would like to overwrite or rename the file.
  6. If you are reading a file containing multiple series in Vertical-Multiple, Horizontal-Multiple or MRTS format, each series will be saved to its own data file. These data files will be saved in the same directory as the input Excel file.
    1. For Vertical-Multiple and Horizontal-Multiple formats, the data file names will be the series names whose locations are specified using the Series Names Row/Column box.
    2. For MRTS format, the location of the series names does not need to be specified.
    3. For all of these formats, if a file with the same name already exists, the new file name will have a numeric suffix. For example, if books.dat already exists in the same directory as the input file, the new data file will be named books(2).dat.
  7. Check the Date in more than one column box if the dates are organized such that the year is in the first date column and the period (months or quarters) is in the next column.
  8. Use the following controls to describe the location of the data within Excel file:
    1. Data Start Column: The column in which the dates begin.
    2. Data Start Column: The column in which the data begin.
    3. Data Row: The row in which the data begin.
    4. Series Names Row: The row containing the series names. Only available for Vertical-Multiple format.
    5. Series Names Column: The column containing the series names. Only available for Horizontal-Multiple format.
    6. Data End Column: The column where the user would like the data to end. Only available for Regression Matrix format.
    7. Data End Row: The row where the user would like the data to end. Only available for Regression Matrix format.
  9. Alternatively, you can specify the starting date manually by checking the Manually Enter Dates box and then selecting the starting year using the Start Year box and the starting period using the Start Period box. NOTE: Manually Enter Dates is not available for MRTS format.
  10. Use the Period box to select the frequency of the data: 12 for monthly data, 4 for quarterly data, or 1 for annual data.
  11. X-13-Data will stop reading an Excel file when:
    1. Two consecutive non-numeric values in a series, e.g. two consecutive missing values. (Note that in X-13ARIMA-SEATS, by default the value -99999 in a series is considered a missing value; if your data has missing values, it is best to change them to this value before attempting to convert the file.)
    2. An empty row or column is encountered

Return to contents.


5. Convert X-13 Output to an Excel file.

The Excel file will have the following format:

  • The first row will contain the title of the X-13 output file.
  • The second row will contain labels for each column.
  • The data begins in the third row. The data will be organized vertically with the dates displayed in the first column as an Excel date in mmm-yyyy format and data in the following columns with two decimal places displayed.

To convert X-13 output files to Excel files.

  1. Select X-13 Output to Excel file from the X-13-Data main page and press Go. The X-13-Data Excel Writer window will appear.

    X-13-Data: Excel Writer

    Keyboard Shortcuts:
    Browse=Alt+b
    Go=Alt+g
    Cancel=Alt+c

  2. Select an existing X-13 output file by either typing the complete file name in the X-13 File text box or navigating to the file using the Browse button. If the X-13 file exists, the other controls will become available. See Appendix B from the X-13ARIMA-SEATS Reference Manual for a list of file extensions created with X-13ARIMA-SEATS. Currently, only files in x12save format can be converted. See the X-13ARIMA-SEATS Reference Manual for more information about x12save format.
  3. Enter the name of the Excel file to which the data should be saved and select Go. The output file's extension must be either .xls or .xlsx. If a file with the same name already exists, a message box will pop up to ask if you would like to overwrite the file. NOTE: You cannot overwrite an Excel file if the Excel file is open.

Return to contents.


6. Data Formats

Vertical

Vertical Format Example

The dates are in one or two columns and the data is in another column

Horizontal

Horizontal Format Example

Data is in a table, with each row containing one year of data. Each column contains data for one period.

Vertical-Multiple

Vertical-Multiple Format Example

Similar to Vertical for single series, each series is in its own column. There is a row containing the names of each series. These are used to name the data files created by X-13-Data.

Horizontal-Multiple

Horizontal-Multiple Format Example

One column contains the series names. (Here it is column A.) Each series is presented in its entirety before the next series begins. Each row contains one year of data. Each month (quarter) is in its own column.

MRTS

MRTS Format Example

MRTS refers to the U.S. Census Bureau's Monthly Retail Trade and Food Services report. It is available at http://www.census.gov/retail/mrts/www/data/excel/mrtssales92-present.xls

X-13-Data will create a data file for each unadjusted series from the MRTS report. You do not need to specify any parameters when the MRTS format has been selected.

Note that a viewer for Excel filesLink to a non-federal Web site is available from the Microsoft Office website.


Return to contents.


Regression Matrix

Regression Matrix Format Example

Similar to Vertical Multiple format except that entire matrix will be saved into one data file. This type of file can be read into X-13ARIMA-SEATS as a user-defined regressor.
Use the controls to specify the start and end rows and columns of the regression matrix. Data Start Column and Data Start Row are required. Leave Data End Column and Data End Row at their default values to read the entire matrix. Otherwise, use Data End Column and Data End Row to read a subset of the data in the Excel file.

7. Contact Information

For more information or to report bugs, please contact Nikki Czaplicki.
Email: nicole.czaplicki@census.gov
Phone: 301-763-6206

Return to contents.


8. References

U.S. Census Bureau (2013). X-13ARIMA-SEATS Reference Manual,Version 1.1PDF

Return to contents.



[PDF] or PDF denotes a file in Adobe’s Portable Document Format. To view the file, you will need the Adobe® Reader® Off Site available free from Adobe.

This symbol Off Site indicates a link to a non-government web site. Our linking to these sites does not constitute an endorsement of any products, services or the information found on them. Once you link to another site you are subject to the policies of the new site.

Source: U.S. Census Bureau | OSMREP and CSRM | (301) 763-1649 (or x12@census.gov) |  Last Revised: April 02, 2014