FORMAT=XLS/XLSX

These are the spreadsheet formats for Microsoft Excel™. An Excel file (in either format) is a Labeled Table or Unlabeled format (depending upon the file) for both input and output of series, reports and matrices, available in all versions of RATS. XLS is the standard format used by Excel through the 2003 version. XLSX is the standard format for Excel 2007 and later.

RATS Instructions

For all of these, use FORMAT=XLS for Excel 2003 and older formats (BIFF workbooks), and FORMAT=XLSX for Excel 2007 format (zipped XML format)

data(format=xlsx)	read series from XLSX into memory
store(convert=xlsx)	copy series from XLSX to RATS format
copy(format=xls)	write series to XLS file
prtdata(format=xls)	write series from RATS format file to XLS
read(format=xlsx)	read scalars or arrays from XLSX into memory
write(format=xls)	write scalars or arrays from memory to XLS file
report(format=xlsx)	(with ACTION=FORMAT) write a report to XLSX file

Interface Operations

Data Wizard

You can read series using Data/Graphics—Data (Other Formats).

Series Window and RATS Data File Window

You can import series using File—Import... or the toolbar button. You can export series using File—Export..., the toolbar button, or the Export operation on the contextual menu.

Report Windows

You can write reports using File—Save As, File—Export, Edit—Copy; the and toolbar items, and the Export and Copy contextual menu operations.

Matrix Windows

You can write the contents of a matrix window to a file using File—Save As, File—Export; the and toolbars, and the Export contextual menu.

The Copy operations will generally only allow pasting in XLS format into a spreadsheet.

SHEET option

The instructions for inputting data all take a SHEET option which allows you to take data off a specific sheet in a multiple worksheet file. (By default, data are taken only off the first worksheet). A single instruction can only process information from a single sheet, so if you need to pull data off more than one, you'll need a separate instruction for each. (You will only need one OPEN DATA, however). For instance,

open data naftadata.xlsx

cal(q) 1981:1

data(format=xlsx,org=columns,sheet="Bonds") / usabonds canbonds mexbonds

data(format=xlsx,org=columns,sheet="GDP") / usagdp cangdp mexgdp

Dates

If you want to dates to an Excel spreadsheet (or replace a poorly formatted date column), here’s an easy way to add monthly or quarterly date labels. First, insert a blank column or row and format it as one of the built-in date formats (which one doesn't really matter). Next, enter the date number corresponding to your first date in cell A2 using the appropriate date function. For example, you could enter the date June 1, 1999 with the formula:

=date(1999,6,1)

To fill the rest of the date column or row with monthly dates, enter the following formula in cell A3:

=date(year(a2+31),month(a2+31),1)

Finally, copy this formula to the rest of the cells in the date column or row. For quarterly dates, just use +92 in place of +31. For annual, use =date(year(a2+366),1,1).

Other Details

On DATA and STORE, you can, if required, use the NOLABELS option to skip automatic label processing and use the LEFT and TOP options to isolate the data.