SHEET

<< Click to Display Table of Contents >>

Navigation:  Gekko commands >

SHEET

Previous pageReturn to chapter overviewNext page

You can transfer variables to Excel by means of the SHEET command. SHEET has the same syntax as the PRT, PLOT and CLIP commands, including the use of operators. 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  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 obtain data from Excel. (If you need to obtain data arranged in rows/columns with labels, see IMPORT<xlsx>).

 

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 (blanks at beginning and end are removed).

List and map: [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

Name of the variable(s) printed. Several variables can be printed at once using, var1, var2 .... You may also use lists or expressions.

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 be contain an absolute path like c:\projects\gekko\myfile, a relative path like \gekko\myfile.gbk, or be stated without a path. Filenames containing blanks and special characters should be put inside quotes. See more on filenames here.

SHEET

The name of the sheet for your data, for instance 'Data1'

CELL

The cell where data starts, for instance 'C4', default is 'A1'.

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)

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]: If 'yes', the table is appended to an existing Excel workbook (is 'no' per default)

MATRIX

[yes|no]: Used with SHEET<import> to import a matrix, see example below.

MISSING

[yes|no]: Used with SHEET<import matrix>. Cells with no content are set to missing instead of 0.

DATEFORMAT=

DATETYPE=

(Optional). These options control the date format for .xlsx and .csv files. DATEFORMAT can be either 'gekko' (default) 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 or monthly data that the last day of the quarter or month 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. [New in 3.0.5].

BANK

(Optional). A bankname where variables are looked up. For instance PRT <bank = b1> x; is equivalent to PRT 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 subseries and missing data values like GAMS, treating them as zero for sums and mathematical expressions, or skipping the printing of a subseries 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 command, you may also use element-specific operators (for instance SHEET unemp, gdp<p>;). See the PRT command 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 command 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 command 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 EXPORT to export a matrix to Excel, 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 %= 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) commands 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 commands

 

CLIP, PRT, PLOT, EXPORT