Author: Andrew Mason (Team OpenSolver)

As a New Zealander and a yachtie, I have been watching the Americas cup racing with great interest as Emirates Team New Zealand races against Oracle Team USA. If you want to see incredible technology and dramatic sailing, check out http://americascup.com – Oracle have done a fantastic job at bringing this racing onto our TVs and browsers. And, Kiwis, don’t forget to wear your red socks… Andrew.

PS: You might also like to read: Philpott, A.B., Mason, A.J., ‘Optimising Yacht Routes under Uncertainty,’ Proceedings of the 15th Chesapeake Sailing Yacht Symposium, Annapolis, Maryland, p89-98, January 2001 (also available here).

This says it all:

My student, Matthew, has just succeeded in getting OpenSolver running with the NOMAD non-linear solver from the Gerad group. Many thanks to Jonathon Currie & David Wilson, creators of the OPTI Matlab toolbox (which also includes NOMAD), for letting us learn from your experiences in compiling this (and the COIN-OR code) under Visual Studio.

Matthew’s code is very much beta, but it shows that we can make it all work to get a powerful non-linear solver working with Excel. The challenge has been getting our VBA code to call the NOMAD C++ code which then has to call back into a thin C++ layer which runs our VBA code which re-calculates the spreadsheet for each new set of variable values; trivial, really! We initially created an XLL Excel add-in to help with this, but it turns out any DLL can do this using the Microsoft XLL interface libraries, so we now simply create a DLL that is linked into our OpenSolver VBA add-in.

Well done, Matthew, in getting this all to work, given minimal previous experience in C (and none in C++)! It’s a real privilege to work with such excellent Engineering Science students.

This will be released in the next beta version, coming soon….

Bernhard Aeschbacher (University of Zurich, Department of Business Administration) has just finished his Masters thesis on using OpenSolver for scheduling physicians. His thesis and Excel model are available here. It was great to see Bernard using OpenSolver’s on-sheet highlighting in his appendix to show how his model was built. Bernhard also gave me most useful feedback on a problem with non-English systems; OpenSolver 2.1 fixes this.

Iain Dunning just sent me a link to Katherine Perry’s masters thesis on Call Center Scheduling Problem using Spreadsheet Optimization and VBA. (You will have to click past a broken certificate.) Katherine has been using OpenSolver to solve her call center problems. Her thesis includes an appendix giving step by step instructions on installing OpenSolver that might be useful for new users.

DEA Frontier, developed by Joe Zhu, is an Excel add-in for DEA Analysis. Joe has now released a version that works with OpenSolver, allowing larger models to be solved.

Mike Trick recently blogged about OpenSolver winning the COIN-OR 2011 Cup, and commented that OpenSolver was now available under the CPL. This was what we had on our web site as something we were doing, but I must confess the change from the GPL has not yet happened. At the moment, the GPL seems to meet everyone’s needs that we are aware of. However, to ensure this matter is clear, we hereby specifically give approval for OpenSolver to be distributed in its unaltered state as part of a larger piece of software, which is effectively saying that we do not consider calling OpenSolver routines from a third party add-in or VBA module in Excel to be linking in the sense of OpenSolver being a “subroutine library”. As long as OpenSolver continues to be a standalone Excel add-in, we are happy for it to be included and used in other applications.

Thanks Mike for your positive comments.

I was saddened to read in the INFORMS news that Paul Jensen passed away last week. I met Paul at the 2010 Informs conference where he gave a great talk about his set of OR and MS teaching add-ins for Excel. (See also these supplements.) We also corresponded about OpenSolver. You can read about Paul’s contributions at his University of Textas site; his obituary is available here. Just as PuLP has been picked up and supported by Stuart Mitchell after its creator passed away, I hope that someone will pick up the maintenance of Paul’s Excel tools; they are a great online legacy which deserve a life beyond that of their creator.

We’ve worked hard in OpenSolver 1.1 to limit the number of reads from the spreadsheet as these are slow. Testing on a user-supplied airline problem with 70,000 variables and 76,000 constraints shows that we can build this model in 164 minutes on a 2.66GHz Core 2 Duo laptop; this used to take over 12 hours in the first version of our code! If we can parse the formulae, we can make it even faster…