You can transfer timeseries or matrices to Excel by means of the SHEET statement. SHEET has the same syntax as the PRT, PLOT and CLIP statements, including the use of operators.

Guide: Excel printing

For an easier introductory guide on Excel printing, see this page.

You may also use SHEET to import data from individual cells via SHEET<import>. The sheet cells can be converted to timeseries, but can alternatively be loaded as a list, map or matrix for further processing (see examples).

Per default, SHEET uses an internal 'engine' to read and write Excel files. This engine does not depend upon Excel being installed. In order to read the older .xls format, you may use OPTION sheet engine = excel (cf. OPTION).

Excel note: if you encounter "dates" with integer numbers larger than 20000, this may be because Excel shows the dates as numbers rather than dates. You may try to change the format of the date cells: right-click, "Format cells", "Date".

For export of timeseries, SHEET uses the same internal component as PRT, so regarding operators and other details, also see the PRT help page.

Syntax

sheet < period IMPORT operator TITLE=... STAMP=... SHEET=... CELL=... DATES=... NAMES=... COLORS=... ROWS COLS APPEND=... LIST MAP MATRIX MISSING COLLAPSE=... DATEFORMAT=... DATETYPE=... BANK=... REF=... MISSING=... > variables FILE=... ;

period	(Optional). Local period, for instance 2010 2020, 2010q1 2020q4 or %per1 %per2+1.
IMPORT	(Optional). Use sheet<import> to import matrix-like data from xlsx, csv or prn files, cf. example at the end of this help topic (if you need to import data that is arranged as timeseries in labelled rows/columns, see IMPORT). You may indicate the file type like for instance sheet<import matrix csv> (default is xlsx). The prn type is essentially a file with blank-separated numbers. SERIES (default): in this case, the variables must be a comma-separated list (or a list like {#m}). With this option, you may use SHEET= (select the sheet), CELL= (point to the starting cell), ROWS/COLS (select the orientation of the data/timeseries), and FILE (the filename). After this, Gekko will read the data into the variables. If the orientation is row-wise (which is default), Gekko will use the n x k cells starting at the CELL location, where n is the number of variables, and k is the number of time periods. See example under 'Examples' below. LIST, MAP or MATRIX: You can state one collection name like for instance #m. Additionally, you may use SHEET=..., CELL=..., ROWS/COLS (select the orientation of the data, COLS is transposed), and FILE (the filename). After this, Gekko will read the data into the given collection: •List: The data is loaded as a list of rows, where each row is a sub-list of elements representing the columns. The cells can be of any type, including null (empty). For instance, #m[2][3] will represent row 2, column 3 of the sheet (that is, the cell C2). See example under 'Examples' below. •Map: The data is loaded as a map, where the keys represent the cells. The cells can be of any type, including null (empty). For instance, #m['%c2'] or #m.%c2 will represent the cell C2 (stored as the scalar %c2). •Matrix: The data is loaded as a matrix, where all the cells must be of value type. If you use the MISSING option, any empty cells will be filled with missing values (M), otherwise they are filled with 0's. See example under 'Examples' below. •List and map: note that string cells will be stripped (that is, blanks at beginning and end are removed). •List and map are [New in 3.0.6].
operator	(Optional). 'Long': abs, dif, pch, gdif, or 'short': n, d, p, dp, m, q, mp, r, rd, rp, rdp
TITLE	(Optional). A title for the sheet. You can use HEADING as alias.
STAMP	(Optional). If 'yes', a time stamp is inserted at the top.
variables	Variable(s) or expressions(s) to be printed (several variables can be printed at once using, var1, var2 ....). A matrix or matrix expression may be stated, too (only one at a time).
FILE	(Optional). SHEET will optionally create an Excel file silently without opening Excel (the filename will be [filename].xls or [filename]xlsx, and is put into the Gekko working folder) Filenames may contain an absolute path like c:\projects\gekko\bank.gbk, or a relative path \gekko\bank.gbk. Filenames containing blanks and special characters should be put inside quotes. Regarding reading of files, files in libraries can be referred to with colon (for instance lib1:bank.gbk), and "zip paths" are allowed too (for instance c:\projects\data.zip\bank.gbk). See more on filenames here.
SHEET	The name of the sheet for your data, for instance 'Data1'. Gekcel: not supported, instead select the sheet with VBA code like this: Worksheets("Data1").Activate.
CELL	The cell where data starts, for instance 'C4', default is 'A1'. Gekcel: not supported, instead use VBA to offset cells.
DATES	[yes\|no]: If yes, dates are shown (is yes per default)
NAMES	[yes\|no]: If yes, names are shown (is yes per default)
COLORS	[yes\|no]: If yes, colors are shown (is yes per default). Gekcel: not supported.
ROWS	[yes\|no]: If yes, the timeseries are printed in rows (default), use the COLS option to transpose.
COLS	[yes\|no]: If yes, the timeseries are printed in columns (transposed).
APPEND	[yes\|no\|ifexist]: If yes, the table is appended to an existing Excel workbook. If no, a new workbook is always created (default). If ifexist, the table is appended to an existing Excel Workbook if the file exists, otherwise a new workbook is created. Gekcel: not supported. [ifexist is new in Gekko 3.1.9].
MATRIX	[yes\|no]: Used with SHEET<import> to import a matrix, see example below. Gekcel: not supported.
MISSING	[yes\|no]: Used with SHEET<import matrix>. Cells with no content are set to missing instead of 0.
COLLAPSE=	(Optional). This option will collapse quarterly or monthly data into annual averages or totals. Use sheet<collapse>, sheet<collapse=avg> or sheet<collapse=total>. You may set the collapse globally, cf. option sheet collapse = [avg\|total\|none];. Beware: this only applies for option sheet freq = pretty; (which is not default).
DATEFORMAT= DATETYPE=	(Optional). These options control the date format for .xlsx and .csv files, and for the use of SHEET in Gekcel. DATEFORMAT can be either 'gekko' (default, for instance 2020q3 or 2020m11) or a format string like 'yyyy-mm-dd', and the latter may contain a first or last indicator, for instance 'yyyy-mm-dd last', which indicates for quarterly, monthly or weekly data that the last day of the quarter/month/week is used. DATETYPE can be either 'text' or 'excel'. In the former case, the dates are understood as text strings (for instance '2020q3' or '2020-09-30' for a quarterly date), and in the latter case (not relevant for .csv files), the date is understood as an Excel date, which basically counts the days since January 1, 1900. This number would correspond to 44104 for the date 2020-09-31, and can be shown in Excel in different ways depending upon date format settings, language settings, etc., but the internal number itself is unambiguous. When using SHEET in Gekcel, using DATETYPE='excel' is advised for consistency. [New in 3.0.5].
BANK	(Optional). A bankname where variables are looked up. For instance sheet <bank = b1> x; is equivalent to sheet b1:x;. See also <REF = ...>. These options can be convenient instead of opening and closing banks.
REF	(Optional). A bankname where reference variables are looked up. For instance prt <bank = b1 ref = b2 m> x;" uses banks b1 and b2 for the multiplier. See also <BANK = ...>. These options can be convenient instead of opening and losing banks.
MISSING=	(Optional). With <missing = ignore>, SHEET will deal with missing array sub-series and missing data values like GAMS, treating them as zero for sums and mathematical expressions, or skipping the printing of a sub-series if it does not exist. The following options are set locally and reverted afterwards: option series array print missing = skip; option series array calc missing = zero; option series data missing = zero. See also the appendix page on missings.

•If no period is given inside the <...> angle brackets, the global period is used (cf. TIME).

•If a variable without databank indication is not found in the first-position databank, Gekko will look for it in other open databanks if databank search is active (cf. MODE).

You may use a 'operator' to indicate which kind of data transformation you would like on your variables, for instance sheet<d>, sheet<q>, sheet<pch>. As in the PRT statement, you may also use element-specific operators (for instance sheet unemp, gdp<p>;). See the PRT statement regarding the use of operators.

Examples

An example could be:

sheet x1, x2;

Shows the two variables in Excel (if Excel is installed). Or more advanced:

sheet x1 'GDP', x2 'Unemployment' FILE = scenarioA;

This produces the file scenarioA.xlsx silently. SHEET produces a table in Excel, with variables running downwards and periods running rightwards. Missing values are converted to missing values in Excel (#N/A). SHEET should work regardless of Excel macro settings, decimal separator etc., on Excel 2003 and upwards.

To illustrate the options, consider this example:

sheet <m SHEET='Raw' CELL='d1' DATES=no NAMES=no COLORS=no COLS=yes APPEND=yes> fm/fy 'Imports', fe/fy 'Exports' FILE=adam.xlsx ;

This will print an absolute multiplier (operator m) of the two expressions (with labels), to the Excel workbook adam.xlsx (appending to the pre-existing file). The data will be put into the sheet Raw, in cell D1, without dates, labels and colors, and with the data running downwards in columns.

To illustrate how to transfer raw cell data in an out of Excel, consider this example:

reset; time 2001 2002;
xx1 = 1001, 1002;
xx3 = 3001, 3002;
sheet <2001 2002 sheet='test' cell='C5' dates=no names=no colors=no> xx1, xx3 file=testing;
reset; time 2001 2002;
sheet <2001 2002 import sheet='test' cell='C5'> xx1, xx3 file=testing.xlsx;
prt xx1, xx3;

The first SHEET statement will produce the file testing.xlsx, with the sheet test inside, where the data is starting at the cell C5. Note that you need DATES=no and NAMES=no to only get the raw data. The data looks like this (starting at cell C5):

1001 1002
3001 3002

The last SHEET statement imports data from the sheet test from testing.xlsx, and puts the cells back into the variables (timeseries) xx1 and xx3. To import the Excel data from the previous example into a matrix instead, you may use this:

sheet <import matrix sheet='test' cell='C5'> #m file=testing.xlsx;
prt #m;

You may use SHEET to export a matrix or matrix expression to Excel, for instance sheet <title='Shares' sheet='2020'> #m/1000 file=m.xlx;. Alternatively, but with less options, you can use EXPORT: export <xlsx> #m file = m.xlsx;.

If you need to perform custom transformations of an Excel spreadsheet, you may load the cells as a list or map, for further processing. Consider this spreadsheet (data.xlsx, download here).

Name	Share	jan-20	feb-20	mar-20
Total	100	100.4	100.4	100.4
Total imports	45	104.2	103.1	101.4
011 Meat	10	100.6	99.9	101.7
022 Milk	5	97.4	100.1	98.9
112 Beverages	20	100.2	100.1	100.3
121 Tobacco	10	101.0	97.6	97.2
Total exports	55	99.6	99.6	99.5
011 Meat	20	99.7	98.6	94.6
022 Milk	10	100.0	98.9	99.2
112 Beverages	15	101.0	101.1	101.1
121 Tobacco	10	100.4	101.5	101.3

The dates shown are Excel dates, representing January, February, and March 2020, respectively (Excel stores these internally as the numbers 43831, 43862, and 43891 = days since January 1, 1900). If the frequency is set to monthly, Gekko will convert these Excel dates to the Gekko dates 2020m1, 2020m2, and 2020m3. We wish to extract the rows with three-digit codes as timeseries with suitable names, for instance "011 Meat" should becomes pm011 if found under imports, else pe011, and in addition we with to extract the fixed shares as for instance s_pm011.

In the above sheet, all rows have the same number of columns, but in contrast to a matrix, this is not guaranteed. The following program loops through the rows and extracts the data (including series labels):

option freq m; //so that Excel dates are read as months
time 2020 2020;
sheet<import list> #m file=data.xlsx;
%rows = #m.length(); //number of rows
%cols = #m[1].length(); //number of cols
%t1 = date(#m[1][3]); //start date
%t2 = date(#m[1][%cols]); //end date
%ie = 1; //imports or exports
for val %i = 1 to %rows;  //loop the rows
  if(#m[%i][1].index('total exports') == 1); %ie = 2; end;  //exports type
  if(#m[%i][1].length() >= 3); //name with three chars or more
   %code = #m[%i][1][1..3]; //%code = first three characters
   if(%code.isnumeric() == 1); //if these chars are digits
   #numbers = #m[%i][3..]; //fetch the row cells into a list
   %namestart = 'pm';
   %label = 'Imports, ';
   if(%ie == 2);
   %namestart = 'pe';
   %label = 'Exports, ';
   end;
   %name = %namestart + %code; //name like 'pm011'
   {%name} <%t1 %t2 label = %label + #m[%i][1]> = #numbers; //put the data into a series pm011 = ...
   s_{%name} = timeless(#m[%i][2]); //shares, using timeless series
   end;
  end;
end;

disp pm011;

Instead of #m[%i][%j], you may alternatively use #m[%i, %j], but beware that a range like #m[%i1..%i2, %j] is not the same as #m[%i1..%i2][%j], cf. the explanations here. A map containing the cells could also have been used, but in this case, a nested list is easier. Instead of timeless series like s_pm011, values like %s_pm011 could have been used. Result:

2020m1 2020m2 2020m3

pm011 100.5700 99.9485 101.7121

pm022 97.3908 100.1147 98.9306

pm112 100.2375 100.1348 100.2966

pm121 100.9756 97.6316 97.1969

pe011 99.7357 98.5820 94.6168

pe022 99.9656 98.8769 99.1864

pe112 100.9782 101.1213 101.1087

pe121 100.4187 101.4881 101.3085

s_pm011 10.0000 10.0000 10.0000

s_pm022 5.0000 5.0000 5.0000

s_pm112 20.0000 20.0000 20.0000

s_pm121 10.0000 10.0000 10.0000

s_pe011 20.0000 20.0000 20.0000

s_pe022 10.0000 10.0000 10.0000

s_pe112 15.0000 15.0000 15.0000

s_pe121 10.0000 10.0000 10.0000

Note

The EXPORT (or WRITE) statements can also output series as an Excel workbook, but cannot append to an existing spreadsheet. SHEET, however, has more options to control the workbook. Gekko will produce a macro/vba-enabled spreadsheet, if the file extension is .xlsm.

In Excel 2007 and newer, you can click on a cell inside the table and select 'Insert' and 'Line' from the 'Charts' ribbon, and a chart will be produced (with the correct legend, labels etc.).

Related options

OPTION sheet collapse = none; [avg|total|none]; //show aggregates for quarters and months

OPTION sheet cols = no;

OPTION sheet engine = internal; //use 'excel' for the older .xls format

OPTION sheet freq = simple; [pretty|simple]; //for quarters and months

OPTION sheet mulprt abs = yes;

OPTION sheet mulprt gdif = no;

OPTION sheet mulprt lev = no;

OPTION sheet mulprt pch = no;

OPTION sheet mulprt v = no;

OPTION sheet prt abs = yes;

OPTION sheet prt dif = no;

OPTION sheet prt gdif = no;

OPTION sheet prt pch = no;

OPTION sheet rows = yes;

OPTION interface excel modernlook = yes; [yes|no]

OPTION interface excel language = danish; [danish|[empty]]

Related statements

CLIP, PRT, PLOT, EXPORT