Packaging Optimization Applications
After implementation of the optimization model it is often needed to
package the model into a user-friendly software package or to embed the
optimization routines into a larger system. This can mean interfacing
with enterprise databases or desktop office applications. Below we mention
some possible congifurations.
Although these examples are simple, the background machinery to run
GAMS is fairly complex. First it is needed to find out where GAMS
is installed. The path needs to be extended to be able to call
GAMS and to find the needed DLL's. Finally complexities are introduced
to support transparently both GDX file formats of GAMS version 22.5 and
before and of GAMS version 22.6.
These examples can be used as a starting point for implementing a
Using Excel as front-end
Excel is a powerful spreadsheet program with more than
enough bells-and-whistles to make it suitable as a front-end
for optimization applications. With an installed base of more
than 500 million, there is a good chance the user is already
familiar with Excel. The examples in this section
use VBA (Visual Basic for Applications) code to call GAMS
and to exchange data with GAMS.
The following algorithm has been implemented in these examples:
- Find the GAMS system directory.
- Augment the path with the GAMS system directory so that we
can call GAMS and make use of the GDX DLL's.
- Locate the Windows TEMP directory.
- Copy the GAMS model to the temp directory.
- Write GDX files with input data for the model.
- Call GAMS to solve the model.
- Read GDX files with solution data.
- Update the GUI front-end.
An Excel-based Portfolio Example
This model implements the calculation of an efficient frontier, as
graph that is used in portfolio theory to depict pareto optimal
In this application the user can change what investment instruments are
available. When the solve button is pressed a GDX input file is
created, a GAMS model is
solved containing a number of NLP models, and the solution results in
of a GDX file are read back. The graph is then updated to refect
the user specifications. This application is small enough to fit in
the student/demo version of GAMS.
Although this is a small one sheet application, it can be used as the
basis for more complex applications with multiple sheets.
Dim WorkDir As String
Dim SysDir As String
Dim DllName As String
WorkDir = TempDir()
Call AddGAMSPath(SysDir) ' needed to find gdxio.dll and gams.exe
Call ExportGDXFile(WorkDir, DllName)
Call WriteGAMSModel(WorkDir & "portfolio.gms")
Call RunGAMS(WorkDir & "portfolio.gms", WorkDir)
|This code implements the basic algorithm described above.
An Excel-based Sudoku Example
This model requires a valid GAMS and GAMS/Cplex license to run. In this
application we solve a large Sudoku problem using a MIP formulation.
Cplex can solve this (large) model quickly without doing any branch-and-bound
nodes. The presolver solves this model completely.
The spreadsheet uses VBA code to call GAMS and to read and write GDX
files to exchange data with GAMS. The model generated is too large to fit in
the Student/Demo version of GAMS.
Excel based advanced reporting with Pivot Tables
This application allows the user to perform sensitivity analysis
by varying some parameters. The Excel spreadsheet will generate and
evaluate the policy scenarios and present the results in Pivot
Tables. Pivot Tables are a powerful tool to present complex,
voluminous data, and can be easily customized to present the
model results in a meaningful way. In addition, associated Pivot
Charts can be used to create powerful Charts providing extra insight in
The application also allows to run scenario analysis from a
Memory Stick. In this case a minimal run-time GAMS system can be
placed on the USB stick, creating a portable application.
Using Access as front-end
Much of the VBA code used in the above examples can be
used when calling GAMS from MS Access. An important extension is to allow for
a progress window, with a multi-line text-box that shows progress by
displaying the log. For longer running jobs it is also important to
introduce an Interrupt (Ctrl-C) and Stop (Ctrl-Break) facility. We
have implemented this using Access as shown in the picture above. This
approach can also be used in an Excel based application.
Note: the progress window is implemented using a TextBox component from
MS Forms 2.0.
|Code to interrupt a long running GAMS job. The
low-level code to handle the code-injection and the remote-thread
management is implemented as a C DLL callable by VBA.
Private Declare Function GamsInterrupt Lib "gamsinterrupt.dll" _
(ByVal pid As Long, ByVal InterruptType As Long) As Long
Private Sub SendInterrupt(InterruptType As Long)
Dim rc As Long
Dim pid As Long
pid = getpid()
rc = GamsInterrupt(ByVal pid, ByVal InterruptType)
Private Sub InterruptButton_Click()
' try to interrupt
Private Sub KillButton_Click()
' try to kill
$if not set db $error db is not set
$onecho > mdb2gms.txt
q1=select distinct code from cities
q2=select city1,city2,distance from distances
$call =mdb2gms.exe @mdb2gms.txt
set i 'cities';
parameter d(i,j) 'distances';
|Passing data from Access to GAMS is very easy with MDB2GMS. We can use this GAMS code to handle this.
The %db% macro can be passed on to GAMS via the command line.
The complete example has a main menu with some buttons, a progress window described above and a report
window displaying the traveling salesman tour or the minimum spanning tree as found by the optimization model.
The basic architecture as demonstrated in this mini-application has been used in several commercial systems.
- Access example (the models are too large for the GAMS Student version)
Microsoft Solver Foundation Examples
The new Microsoft Solver Foundation is a .NET optimization library and Excel GUI. It includes
solver for LP, MIP and CSP models and includes a simple modeling language OML. The Excel
GUI is useable to build quick prototypes. More refined implementations can be build using the
Here are some examples:
This is the trnsport.gms model from the GAMS model library implemented in OML. All the
input data are in the spreadsheet. The OML modeling language can represent this model
The problem of finding Magic Squares can be conveniently formulated as a Constraint
Programming model. The all-different constraint can be written using the unequal condition.
Using the "Domain over Weighted Degree" heuristic we can solve problems with N=10