Colorado GAMS

The GAMS-Excel Interface

Thomas F. Rutherford*

Alexander Maliyev

Department of Economics
University of Colorado

July, 1999

* This research supported by the GAMS Applied General Equilibrium Research Fund. The XLLINK program which is employed for data transfer was programmed by Alexander Maliyev. Thanks to Renger van Nieuwkoop, Franz Nelissen, and Paul van der Eijk for help with debugging. The authors remain responsible for any bugs which exist in this software. This software is not officially supported by GAMS Corporation.

New Release:

The latest version of XLLINK has been released in June, 2002. The new code is internally based on the GAMS GDX interface, but at the GAMS source level the system is unchanged.


Please try this before sending me an email.

1. Copy your license file (gamslice.txt) to a new directory.

2. Download the latest version of GAMS from here:

http://www.gams.com/download/

and install it in the new directory.  It should the license file.

3. Create a new working directory and unzipped this file into it.

4. Open a dos window in the working directory and type "run".

The results should be transferred to ramsey.xls. 



Fixed bugs in the latest release:

Problems with embedded blanks in file names have been fixed.

Outstanding issues and workarounds:

  1. Exports and dumps only work with PARAMETERS. If you want to transfer level values or marginals, you need to first move the data into a parameter

  2. There is a problem arising when data is exported to a workbook which is already opened in Excel. At present no error message is generated and the active worksheet is not updated. I hope that we will be able to terminate with an error message if data is written to an open workbook.

  3. Using xlexport to an Excel file that contains a link to another Excel file produces a message that the Excel file has links with other files every time xlexport is invoked. You have to answer each query if these links should be updates yes or no (with about 50 xlexport commands this is boring). The problem is solved by removing the link with the other Excel file, but we will try to find a solution in the next version of the code.

  4. If you stop the process of xlexport abruptly (closing the dos-window or with ctrl-break), Excel (although not opened explicitly) is open but is not displayed. If you then try to start Excel a message appears, e.g. "Excel is open and in use by ... Ignore?". Opening the file that was used for the xlexport leads to the same kind of message: "File in use by ...". This is a basic problem with OLE -- if xllink is terminated prematurely, then Excel remains open. The only solution is to open the the task manager (ctrl-alt-del) and close the "hidden" Excel application.


N.B.
The XLEXPORT and XLDUMP routines will not transfer parameter elements which are zero. If you wish to export a zero value into a spreadsheet cell, you must assign a value of EPS to the corresponding coefficient. For example, if you want to pass all values from parameter A on set domain (I,J) into a spreadsheet, you must make the assignment:


A(I,J)$(NOT A(I,J)) = EPS;

before passing A to XLEXPORT or XLDUMP.


To run these programs you will need:

This package employs the OLE (object library exchange) interface between Delphi and Excel. We have done the development work and testing with Excel version 97 SR-1, but in principal these routines should work with Excel 95 or later.


I. Distribution Information

The XLLINK system presently has two different versions. You need to choose the correct version, depending on the date of the GAMS system you are running.

Run "gams ?" to find the date of your GAMS system.

If you are running a version GAMS which was released prior to June, 2002, you need to download inclib.pck and xllink.exe.

File xlramsey.zip contains a sample application.

To install the system, copy gdxxrw.zip (or inclib.pck and xllink.exe) to the GAMS system directory and run GAMSINST. (In the IDE run File->Option->Execute->Update to install the update.)

II. An Overview

Three "libinclude" calls are provided which make it possible to transfer data between GAMS programs and Excel workbooks. (Data can be read from almost any worksheet format, but the only output format is Excel.) These utilities operate only with the PC verstion of GAMS, and they require that Excel be installed on the same machine. Briefly the interface routines are as follows:

XLIMPORT reads data from spreadsheets or workbooks at compile time. It can be used to retrieve data from any worksheet format which can be imported by Excel. The spreadsheet range must be specified explicitly. The parameter must be declared and dimensioned prior to the import. GAMS does range checking if it has explicit domains in the declaration.

XLDUMP writes data and labels into an existing Excel workbook. You can specify a worksheet within the workbook into which the data is to be written. Row and column order are defined by the GAMS program symbol table as in a GAMS DISPLAY statement.

XLEXPORT writes data into spreadsheets according to the row and column labels in the specified target range. The target range is always explicit (static), and we only transfer data for which labels are specified.

A run-time log of all XLLINK activity is written along with any error messages to a file named XLLINK.LOG in the local directory. At the start of a given GAMS job, this file is flushed, and during the job log reports from spreadsheet operations are appended.

If there are execution errors in XLLINK, a message is written to the listing file and some additional diagnostic are written to XLLINK.LOG. The default action is to continue processing following an XLLINK error, but this can be modified if a calling program specifies:

                  $SETGLOBAL ABORTXLERR TRUE
The XLDUMP and XLEXPORT routines work inside a LOOP or IF block provided that either of these has been included first in a context where declarations are permitted. For example,


	$libinclude xlexport

	if (mymodel.objval lt 1.e-4,

	$libinclude xlexport x.l solution.xls x_l

	);

The initial invocation generates file and parameter declarations which would otherwise generate a compile error within the if block.

III. Syntax Details

1. XLDUMP: GAMS Spreadsheet Creation and Data Export Utility

Usage:

$LIBINCLUDE XLDUMP ident[.suffix] wbfile[.xls] [worksheet]
N.B.! Data transfer occurs at execution time.

N.B.! If a new file is to be created, the ".xls" extension must be specified.

N.B.! By default, zero values are not transfered. If you need to move zeros into the spreadsheet cells, assign values of EPS.

Other Comments:

Some examples of XLDUMP invocations:

$LIBINCLUDE XLDUMP A REPORT.XLS

$LIBINCLUDE XLDUMP A echoprt.xls Sheet2
$LIBINCLUDE XLDUMP E.M RESULTS.XLS 
Some operational examples using XLDUMP:

$title  Example #1: XLDUMP of a 2-dimensional array
set	i	/1*3/,
	j	/a,b,c/;

parameter a(i,j)  Randomly generated array;

a(i,j)$(uniform(0,1) gt 0.6) = uniform(0,1);

$libinclude xldump a ex1.xls

display a;

Resulting spreadsheet output:


$title  Example #2: XLDUMP of a 1-dimensional array

set j /a,b,c,d,e/;

parameter a(j)  Randomly generated vector (sparse);
a(j)$(uniform(0,1) gt 0.25) = uniform(0,1);

$libinclude xldump a ex2.xls

display a;

Resulting spreadsheet output:

$title  Example #3: XLDUMP of a 3-dimensional array


set i /i1*i3/, j /j1*j2/, k/k1*k4/;

parameter a(i,j,k)  Randomly generated sparse array;
a(i,j,k)$(uniform(0,1) gt 0.1) = uniform(0,1);

$libinclude xldump a ex3.xls a

display a;


Resulting spreadsheet a in workbook ex3.xls:

$title  Example #4: XLDUMP of an array including zeros

set i /i1*i6/;

parameter a(i)  Randomly generated array;

a(i)$(uniform(0,1) gt 0.5) = uniform(0,1);

*	Replace zeros by eps to force output of zeros:

a(i)$(not a(i)) = eps;

$libinclude xldump a ex4.xls

display a;


Resulting spreadsheet output:

$title  Example #5: Set Labels as Worksheet Names 

$ontext

This example illustrates two syntax issues.  The first concerns
invocations of xldump with a loop.  This is permitted, provided that
xldump has already been initialized in a context which permits file
and parameter declarations.  The initialization call requires no
arguments. 

The second programming issue addressed in this example concerns the
use of a set index to reference a worksheet name.  This is possible
when the sheet name set label is enclosed in two sets of quotes, with
double quotes on the outside and single quotes on the inside.
(This only works when the double quotes are outside!).

$offtext

*	Initialize xldump:

$libinclude xldump

set sc /sc1*sc3/;
set i /1*3/;

parameter	a(i)	Current solution values;

loop(sc,
	
	a(i) = uniform(0,1);

$libinclude xldump a ex5.xls "'sc.tl'" 

);
The resulting workbook contains one worksheet for each element of the set sc:


2. XLEXPORT: A GAMS to Spreadsheet Ordered Data Export Utility

Usage:
$LIBINCLUDE XLEXPORT ident[.suffix] ssfile range [/m]
N.B.! Data transfer takes place at execution time.

N.B.! Zero columns or rows are not transfered. The user must assign a value of EPS to an array element to force a zero to be written into the spreadsheet.

Some examples of XLEXPORT invocations:

$LIBINCLUDE XLEXPORT A OUTPUT.XLS Solution!A1:D20
$LIBINCLUDE XLEXPORT X.L SOLUTION XLEVELS
        
A(I,J)$(NOT A(I,J)) = EPS;

$LIBINCLUDE XLEXPORT A my_wb A1:D20 /m

Some operational examples using XLEXPORT:

$title  Example #6: XLEXPORT of a two dimensional array

set i /i1*i3/, j/j1*j3/;

parameter a(i,j)  Randomly generated array;

a(i,j)$(uniform(0,1) gt 0.5) = uniform(0,1);

$libinclude xlexport a ex5.xls b2:e5

display a;

List file output (column ordering according to the GAMS symbol table):

----    304 PARAMETER A             Randomly generated array

            j1          j2          j3

i1                   0.550
i3       0.067       0.998       0.991

Spreadsheet ex5.xls before (arbitrary column order):

Spreadsheet ex5.xls after:

$title  Example #7: XLEXPORT using the Merge option

set i /i1*i3/, j/j1*j3/;

parameter a(i,j)  Randomly generated array;

a(i,j)$(uniform(0,1) gt 0.5) = uniform(0,1);

$libinclude xlexport a ex6.xls b2:e5 /m

display a;

List file output:

----    306 PARAMETER A             Randomly generated array

            j1          j2          j3

i1                   0.550
i3       0.067       0.998       0.991

Spreadsheet ex6.xls before (arbitrary column order):

Spreadsheet ex6.xls after:

$title  Example #8: Use set labels to reference ranges with XLEXPORT

*	Make an initialization call:

$libinclude xlexport

set sc /sc1*sc3/;
set i /1*3/;

parameter	a(i)	Current solution values;

loop(sc,
	
	a(i) = uniform(0,1);

*	Specify the range with an embedded text label defining
*	the worksheet.  Double quotes on the outside, single
*	quotes inside around the label reference:

$libinclude xlexport a ex8.xls "'sc.tl'!a1:c2" 

);


3. XLIMPORT: A Spreadsheet Data Import Utility for GAMS

Usage:

$LIBINCLUDE XLIMPORT parameter wbfile range

N.B.! Data import occurs at compile time. Parameter must be declared prior to import with explicit dimension.

Some examples of XLIMPORT invocations:

$LIBINCLUDE XLIMPORT A A.WK1 A1:D20

$LIBINCLUDE XLIMPORT A A.XLS RANGEA

Some operational examples using XLIMPORT:

$title  Example #9: XLIMPORT of a two-dimensional array

set i /i1*i3/, j/j1*j3/;

parameter a(i,j)  Array imported from worksheet;

$libinclude xlimport a ex7.xls b2:e5

display a;

Spreadsheet to be imported:

Listing file output:


----     79 PARAMETER A             Array imported from worksheet

            j1          j2          j3

i1       2.000       3.000      12.000
i2       2.000       4.000      12.000
i3       2.000       2.000       1.000

$title  Example #10: XLIMPORT of a three-dimensional array


set i /i1*i3/, j/j1*j3/, k/k1*k2/;

parameter a(i,k,j)  Array imported from worksheet;

$libinclude xlimport a ex8.wk1 b2:e5

display a;

Spreadsheet to be imported:

Listing file output:


----     76 PARAMETER A             Array imported from worksheet

               j1          j2

i1.k1       2.000       3.000
i2.k2       2.000       4.000
i3.k1       2.000       2.000

IV. A Detailed Example Using Library Model Ramsey

In order to illustrate the XLEXPORT spreadsheet interface can be used to produce spreadsheet output from a GAMS program, I used library model RAMSEY to study how the results of an intertemporal equilibrium model depend on the model horizon. The steps in this analysis are as follows:

(i) Extract the model from the GAMS library:

gamslib	ramsey

(i) Rewrite the set declarations in RAMSEY.GMS to be:

$INCLUDE HORIZON.GMS 

SET       TFIRST(T) FIRST PERIOD
          TLAST(T)  LAST PERIOD;

(ii) Set up a batch file, RUN.BAT, which solves three models for different horizons, writing HORIZON.GMS with the DOS command ECHO:

ECHO SET     T         TIME PERIODS      /1990*2000/	>HORIZON.GMS
CALL GAMS RAMSEY
ECHO SET     T         TIME PERIODS      /1990*2010/	>HORIZON.GMS
CALL GAMS RAMSEY
ECHO SET     T         TIME PERIODS      /1990*2020/	>HORIZON.GMS
CALL GAMS RAMSEY

(iii) Use Excel to generate a workbook, ramsey.xls. This will hold the results. The first spreadsheet in this book holds two named ranges, C_ and I. (Use "C_" rather than "C" because Excel does not permit "C" as a range names -- Excel continues to amaze me.) The ranges contain row labels for each year from 1990 to 2030. The column labels specify three alternative model horizons of 2000, 2010 and 2020. When this file is initially generated, only the row and column labels are provided -- no data appears within the tables. Column E is in neither range C_ nor I. It contains entries at five year intervals (1990, 1995, etc.) which are used only to label the output graph.

(iv) Add GAMS code at the bottom of RAMSEY.GMS which uses XLEXPORT to write the solution into ramsey.xls:

*	SAVE THE RESULTS IN A PARAMETER WHICH HAS A COLUMN LABEL
*	EQUAL TO THE HORIZON, INCLUDING BASE YEAR VALUES:

PARAMETER	CL(*,T)		CONSUMPTION PATH
		IL(*,T)		INVESTMENT PATH;

*	ADD EPS TO BE SURE THAT WE OVERWRITE THE ENTIRE COLUMN:

CL(T,TLAST)      = C.L(T) + EPS;
IL(T,TLAST)      = I.L(T) + EPS;

*	EXPORT THE DATA TO SPREADSHEET RANGES C AND I 
*	WITH A MERGE SO THAT WE DON'T LOSE DATA WHICH HAS BEEN
*	WRITTEN FOR OTHER CASES (ONE COLUMN OF EACH RANGE IS WRITTEN
*	FOR THE CURRENT HORIZON):

$LIBINCLUDE XLEXPORT CL   ramsey.xls C_ /m
$LIBINCLUDE XLEXPORT IL   ramsey.xls I  /m

(v) After running the batch file RUN.BAT, use a spreadsheet to view the results.

(vi) Of course, once a graph has been defined within a spreadsheet, it may easily be imported into a wordprocessing document, such as WordPerfect or Microsoft Word, both of which provide functions which to format the diagrams on a single page with figure captions.

(vii) It is also possible to use GAMS and GNUPLOT to plot data from a spreadsheet document, as illustrated in the following example:


$TITLE	Read some data from an existing spreadsheet file and plot it useing GNUPLOT

*	Begin by seeing if GNUPLOT is installed -- if not, 
*	then exit the program

$IF NOT EXIST %gams.sysdir%gnuplot.gih $EXIT


SET 
        T       TIME PERIODS            /1990*2020/
        TL      TIME PERIODS TO LABEL   /1990, 1995, 2000, 2005, 2010, 2015, 2020/
        H(T)    ALTERNATIVE HORIZONS    /2000,2010,2020/;

parameter 
        C(T,H)  Consumption paths for alternative horizons 
        I(T,H)  Investment path for alternative horizons;

$LIBINCLUDE XLIMPORT C ramsey.xls C_
$LIBINCLUDE XLIMPORT I ramsey.xls I

DISPLAY C, I;

*       The following code generates a plot using GNUPLOT:

*       Label missing data:

C(T,H)$(NOT C(T,H)) = NA;
I(T,H)$(NOT I(T,H)) = NA;

$SETGLOBAL GP_ROW  T
$SETGLOBAL GP_XL   TL

$LIBINCLUDE GNUPLOT C

$setglobal gp_term gif
$LIBINCLUDE GNUPLOT I

V. Installation

(1) Get the Right Files

These programs only run on the PC. Do not attempt to run these programs on Unix workstations.

The XLLINK system presently has two different versions. You need to choose the correct version, depending on the date of the GAMS system you are running.

Run "gams ?" to find the date of your GAMS system, e.g.:

*** GAMS Rev 133  Jun 14, 2002 WIN.00.NA 20.7 133.000.040.VIS P3 translator
***
*** GAMS Development Corporation
*** 1217 Potomac Street, NW
*** Washington, DC 20007, USA
*** 202-342-0180, 202-342-0181 fax
*** support@gams.com, www.gams.com
If you are running a version GAMS which was released prior to June, 2002, you need to download inclib.pck and xllink.exe.

If you are running a very early version of GAMS, you may need to obtain a system update here. (There may be a fee,depending on the age of your current GAMS system.)

(2) Install the Files

Begin with a version of GAMS which is installed and operational. To install XLLINK, copy gdxxrw.zip (or inclib.pck and xllink.exe) to the GAMS system directory and run GAMSINST. (In the IDE run File->Option->Execute->Update to install the update.)

(3) Test Your Installation

Download and unzip xlramsey.zip into a working directory, then execute run.bat.

The following files are provide in the xlramsey.zip archive:

ramsey.gms The modified GAMS library model. This file runs only with GAMS version 2.50 or later.
empty.xlsAn empty workbook file.
ramsey.xlsThe results workbook file.
run.batThe DOS batch file which produces results for a number of different horizons.


Economics Department, University of Colorado, Boulder CO 80309-0256
Phone: (303) 492-5169, Fax: (303) 492-8969
email:rutherford@colorado.edu
Last modified August, 2002 by TFR