This text provides documentation for SeeResults, an Excel-based spreadsheet application that has been designed to aid the viewing of results from GAMS CGE models. The program is designed to work with the GDX data file format, which is easy to use for output in GAMS. The workbook acts as a GDX file viewer, but with the option of using a special data definition file to describe and categorise results. Additionally, it is possible to store results in a special format which would allow users without GAMS/GDX installed on their computers to view them.
The latest version of the program is available at http://www.elsenburg.com/provide/seeresults.
Copyright © The PROVIDE Project. The program, this text and accompanying files are outputs of the Project.
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. A copy of the GNU GPL, gpl.txt, is included with the programme files. Do NOT remove this notice if you redistribute this programme If you use this programme you should acknowledge the source and not claim authorship. Altered versions should be clearly distinguished from the original.
If you wish to make the software available for wider distribution, for example on a website or packaged with other software, we request that you contact us first. If you wish to acknowledge the software in your research outputs, please reference "PROVIDE (2004). 'SeeResults: A Spreadsheet Application for CGE Model Result Analysis'. PROVIDE Technical Paper 2004:1. Department of Agricultural Economics: Elsenburg. Available online at http://www.elsenburg.com/provide/seeresults."
Programming on this version and compilation of this text and the SeeResults package was done by Melt van Schoor, with substantial inputs by Dr Scott McDonald, Lindsay Chant and Cecilia Punt. We also wish to thank Anju Keetharuth for essential testing and useful comments. Please contact Melt van Schoor (meltvs@elsenburg.com) with bug reports, suggestions and questions, but keep in mind that the program is unsupported.
This text was last edited on 26 November 2004 (the program itself may have been updated since then).
It is recommended that you read at least sections B and C of this text before using the program.
At a minimum, you need need a recent version of Windows and Excel and a data file that you wish to view. If you wish to view GDX files (which is likely if you want to view results from your own models) you will also need to have the GDX utilities installed. To utilise the data description features of SeeResults, you will also need a data definition file.
This section briefly explains the process of viewing the results in a GDX file. It only covers the most important concepts, while the remainder of the documentation provides a detailed treatment. We assume that you meet the prerequisites listed in the previous section (note especially the directive about the Excel macro security option) and that you are testing the program using the example files "Sample_Results.gdx" and "Sample_DataDef.xls", which are included in the package.
Before you begin, you should check that SeeResults is able to use the GDX utilities. To do this, open SeeResults.xls and click on [Options]. Then click on the [Test] buttons. If you can see the utilities running, it means that SeeResults is able to use the utilities. If you see a message such as "The system cannot find the path specified", then first try to locate the programs by using [Auto]. Failing this, you may need to locate these manually and enter the locations. If you do not have GAMS installed, you can get the necessary files here.
You can then add a data source. To do this, you need to click on [Add] and then select the data file. In the dialog, the first time you double-click on a file, it will be selected as a data source and the second time you double-click it will be selected as a data definition file. Selecting a data definition file is optional. After you click on [Accept] you should see your data source and data definition file (if any) in the data source box. If not, it could mean either that you chose illegal options (e.g. files with the wrong extensions) or that the files you selected could not be found or read for some reason.
Data definition files are Excel files that contain information about sets and variables that make part of a result set. The easiest way to create a data definition file is to look at the example and adjust it to your own data set. It is acceptable to define only some of the data in a result set; the principle is that whatever is available will be used as far as it fits the data.
Data sources are stored as pairs of filenames (source and definition) in the Data Sources box. You can define multiple data sources at a time for easy navigation between them. When you save the workbook (i.e. SeeResults.xls) you effectively save the contents of the data sources box, the options and persistent filters if you use them. No model result data are ever saved in the viewer.
After defining a data source, you should be able to see one or more categories in the Categories box just below the Data Source box. If you did not select a data definition file, there will be no categorisation, and all parameters will fall in the "(uncategorised)" category. Clicking on a category will show all the variables in the GDX file under that category.
Clicking (or double-clicking) on a variable name will lead you to the pivot table viewer (assuming the parameter has at least one dimension and at least one data point). The data for that variable should then be displayed according to a default layout. From here you can change orientation options for each set (index), and select to view charts or export a table. Experiment with these options to see how they work.
You can always return to the Navigate worksheet by pressing Ctrl-Shift-R and to the pivot table viewer by pressing Ctrl-Shift-P.
This section describes the various options that you can change by clicking on [Options] from the main Navigate worksheet. All options are global, meaning they apply to all data that you view with the workbook (SeeResults.xls) in which you set the options. If you change any options, be sure to save them by saving the SeeResults.xls workbook. By default, the workbook is not automatically saved when you close it (and the usual "Do you want to save.." window does not appear!).
The Options are:
Data definitions describe the data that you view. For SeeResults, data definitions are entirely optional. Furthermore, even if you do use data definitions, you may describe the data only partially. The principle is that any data definitions available will be used by the viewer (as far as it fits the data). Depending on which components of the data definition are available, the following changes to SeeResults's behaviour occur when data definitions are used:
Data definitions are expressed in a data definition file. A data definition file is an Excel workbook (.xls file) that contains two special worksheets, one of which is named "Sets" and another "Variables". As can be expected, these sheets define sets and variables respectively. To use a data definition file, reference it when you define data sources, or add a data definition file reference to an existing data source by selecting it and clicking [Change]. If your data source is an Excel file, you may use the same file as the data source, as long as it contains the two mentioned worksheets.
An example data definition file is available.
Sets are defined on a worksheet named "Sets" in the data definition file. The first set name must appear in cell A2 on the worksheet. The format is as follows:
D. Options
E. Data Definitions
Set Definitions
| A | B | C | D | E | ... | |
| 1 | Set 1 Description | Set 2 Description | ... | |||
| 2 | [Set 1 Name] | ShortDescr | LongDescr | [Set 2 Name] | ShortDescr | ... |
| 3 | [Set 1 Elem 1 Name] | [Set 1 Elem 1 Short Descr] | [Set 1 Elem 1 Long Descr] | [Set 2 Elem 1 Name] | [Set 2 Elem 1 Short Descr] | ... |
| 4 | [Set 1 Elem 2 Name] | [Set 1 Elem 2 Short Descr] | [Set 1 Elem 2 Long Descr] | [Set 2 Elem 2 Name] | [Set 2 Elem 2 Short Descr] | ... |
| ... | ... | ... | ... | ... | ... | ... |
In the above schema, each cell with bracketed text is read by SeeResults. Each set has a name and a number of elements and each element has a name and two descriptions. It is vital to preserve the cell layout exactly, for example each set must be exactly three columns away from the previous one. You may leave out long (or short) element descriptions, but then you still need to preserve open columns.
Set names should correspond to the set names used in the GAMS model and element names must correspond to the element names in the GAMS model. For element descriptions, you can use anything, but avoid the use of special (non-alphanumeric) characters or overly long descriptions.
Variable blocks are defined on a worksheet named "Variables" in the data definition file. The first variable block name must appear in cell A3 on the worksheet. Note that each of the rows in this table represent a variable block with potentially numerous individual variables. The format is as follows:
Variable Definitions
| A | B | C | D | |
| 1 | ||||
| 2 | Variable | Indices | Category | Description |
| 3 | [Variable 1 Name] | [ind1,ind2,ind3,...] | [Variable 1 Category] | [Variable 1 description] |
| 4 | [Variable 2 Name] | [ind1,ind2,ind3,...] | [Variable 2 Category] | [Variable 2 Description] |
| ... | ... | ... | ... | ... |
Again, only bracketed cells are read by SeeResults. Each variable block has one or more indexes, which are comma-seperated without any other characters (e.g. a space after each comma). Variable names should correspond to your GAMS model variable declarations exactly, while categories and descriptions can be anything (again avoid the use of non-alphanumberic characters). To ensure that variables in the same categories are grouped together on the Navigate worksheet, be sure to specify exactly the same categories for the relevant variables.
The Navigate worksheet is the main interface in SeeResults. It should open automatically whenever you open the SeeResults program. From here, you can change options, define data sources and navigate variables.
To change options, click on the [Options] button. A special options window will pop up allowing you to change global options. See the relevant section for details.
Defining data sources is neccessary before you can view any data. A data source is essentially a reference to a data file (a GDX or a suitable XLS file) and optionally also a data definition file. You can use the [Add], [Change] and [Remove] buttons to manage your data sources. Both [Add] and [Change] will open a special file browser window which allows you to select the data and optional data definition file. In the file browser window, you can generally double-click on relevant items to activate them - directories to change them and files to select them. The first time you double-click on a file, it will be selected as the data source and the second time you double-click on a file it will be selected as the data definition file. However, this will only work if the boxes are empty. Alternatively, you can use the [Use for Data Source] and [Use for Data Defs] buttons.
A number of conditions will trigger an error condition which will result in your selections being rejected. For example, you will not be able to select files with invalid extensions or files which are inaccessible.
Once data sources have been defined, you should see a number of categories in the Categories box on the Navigate worksheet. If this fails, check the GDXDUMP.EXE setting and ensure that your data file is valid. If you have not specified a data definition file, or if your data definition file does not contain categories for the variables in the data file, there will only be a single category, named "(uncategorised)".
Select a category by clicking on it. This should load a list of variables in that category into the Variables box. Click (or double-click) on a variable to view it. If you select a variable block containing valid data (at least one dimension and at least one data point), you will be taken to the pivot table viewer (see below). If you see an error message, you may have a problem with GDXDUMP.EXE.
You can return to the Navigate worksheet from any location in the same workbook by activating a set macro with the key combination Ctrl-Shift-R.
This worksheet enables you to extract, manipulate and chart data. When you select a variable from the Navigate worksheet, the variable block will be "loaded" into the pivot table viewer. A default layout is used to display variables and a table is drawn initally.
To change the layout, you need to change the settings per field. Fields (or indices) for the current variable are listed in the first list box. Once you select a field, you can then select Orientation (Row or Column) and filters for it using the other two list boxes. Once you are satisfied, click on the [Table] button to redraw the table. Whenever a table is created, you are seeing a static formatted copy of a temporary pivot table that has been configured using the options you specify. You can see the actual pivot table on the "PivTemp" worksheet if you wish.
The other options available are [Chart], [PChart] and [Export]. [Chart] will produce a chart based on the table currently displayed, while [PChart] will produce a pivot chart based on the underlying pivot table. [Export] will create a static copy of the data on a new worksheet in a new workbook. The worksheets that are created when you use [Export] will never be automatically deleted, because they are not in the SeeResults.xls workbook. Note that these options ([Chart], [PChart] and [Export]) will always produce results based on the data table currently displayed on the PTableView worksheet. Therefore, if you wish to change (e.g.) a filter and then view the chart again, you first need to click on [Table] to update the table with your new settings.
All pivot tables, charts, etc. are deleted automatically when the workbook is saved (except for exported tables). Also, they can be overwritten if you view another variable or the same variable with different settings. Orientation and filter settings for a particular variable are lost whenever you select another variable, except when the persistent filters option is turned on, in which case filters (but not orientations) will be maintained for a given set (even across different variables or data sources). If you are using persistent filters option, you can save the current set of filters by saving the SeeResults.xls workbook.
You can return to the pivot table viewer from any location in the same workbook by activating a set macro with the key combination Ctrl-Shift-P.
SeeResults can use either normal GDX files or special-format XLS files as data sources. You can create the special XLS files by defining a GDX data source (with or without a data defintion file) and then selecting the [Convert] button from the Navigate worksheet. This will prompt you for a filename, and then create a XLS file containing all of the valid variables in the GDX file. Scalar or empty variables are not included in the new file. Additionally, if your data source references a data definition file, the data definitions will automatically be copied to the new XLS file, so that the new file can be used simultaneously as a data source and a data definition file.
During the conversion process, it is strongly recommended that you wait for it to complete without using any other application on the computer. This is because the conversion process uses the windows clipboard, and also creates multiple processes.
After a successful conversion (which can take some time) the new file will automatically appear as a new data source.
There are a number of advantages of XLS format files:
The main disadvantage of using XLS files are:
F. The Navigate Worksheet
G. The Pivot Table Viewer
H. XLS Data Files
-