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:
- 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
- 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.
- 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.
- 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.
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.
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.)
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 TRUEThe 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.
$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.
$LIBINCLUDE XLDUMP A REPORT.XLS
$LIBINCLUDE XLDUMP A echoprt.xls Sheet2
$LIBINCLUDE XLDUMP E.M RESULTS.XLS
$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:
$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" );
$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
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:
(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
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 *** email@example.com, www.gams.comIf 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.)
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.xls||An empty workbook file.|
|ramsey.xls||The results workbook file.|
|run.bat||The DOS batch file which produces results for a number of different horizons.|