Tuesday, March 7, 2017

Running GAMS from Excel

Small demo: Run a GAMS model from Excel.

Some VBA (Visual Basic for Applications) code is used to locate the GAMS system, launch GAMS and provide a log window.

Using Excel to implement “mini-GUIs” has certain benefits: users are familiar with Excel and IT departments typically don’t mind (they may object to any programming language/GUI platform you have in mind). The advantage of using VBA is that spreadsheets with VBA code are self-contained. No installation or separate files: just send the spreadsheet. Another big advantage is there is a lot of Excel/VBA knowledge out there: I can give this to a client for further adaptation (to handle future needs).  

When visiting clients, whether commercial companies or government agencies and other institutions, I am always impressed with the critical role Excel plays in running their business. Not very often I see optimization applications where Excel is not in some way involved e.g. for data entry, reporting, ad-hoc data, etc. Having said this, I prefer to do more complicated analysis outside Excel, e.g. in GAMS or R. Developing models in Excel is usually not very easy and quite error prone. On the other hand I have seen users do quite complex things in Excel (but I have also seen many cases where I could not reproduce Excel results, usually because it is so easy to make copy-paste errors in Excel formulas).