Spawning GAMS from Excel

Calling GAMS out of Excel requires some more work than just exchanging data with Excel. The application has to:

1. Locate the GAMS system directory and adjust the system path accordingly.
2. Copy the GAMS model into a temporary directory (by default the temporary directory of Windows)
3. Extract the model data from the spreadsheet into a GAMS readable format (gdx)
4. Execute GAMS (solve the model, write the slution back to gdx file)
5. Import the model results back from the gdx files into the spread sheet
6. Update the spreadsheet (graphics, tables)

Using VisualBasic for Applications (VBA) this can be implemented with a few lines of code:

Sub solve()
   Dim WorkDir As String
   WorkDir = TempDir()
   Call ClearSolution
   If (Not AddGAMSPath()) Then ' needed to find gdxio.dll and gams.exe
      Exit Sub
   End If
   Call ExportGDXFile(WorkDir)
   Call WriteGAMSModel(WorkDir & "portfolio.gms")
   Call RunGAMS(WorkDir & "portfolio.gms", WorkDir)
   Call ReadListing(WorkDir & "portfolio.lst")
   Call ImportGDXFile(WorkDir)
End Sub

For further details, please inspecte the VBA part of the examples below.

This very simple example shows how GAMS can be invoked from an Excel spreadsheet. The example spreadsheet has a button, which will cause GAMS to run the trnsport.gms model stored in c:/tmp. There is no data exchange.

A more complete application will write an include file for a GAMS model, and will import a comma delimited file with results when the run is finished. An example of such a complete application is described in http://www.gams.com/mccarl/excelgams.pdf.

This spreadsheet is a complete example that uses GDX files to exchange information solves a 25×25 Sudoku problem using CPLEX. You will need a GAMS/CPLEX license to be able to run the spreadsheet. The MIP model solves very easily: the solution is found in the presolve phase.

Note: This spreadsheet requires distribution 22.6 or younger to work properly. If you are using an older distribution, please download this one.

This example (screen shot below) solves a series of NLPs to create an efficient frontier of a portfolio optimization problem.

Note: This spreadsheet requires distribution 22.6 or younger to work properly. If you are using an older distribution, please download this one.