Blog

OpenSolver 1.5 (9 August 2011)

We are pleased to announce the release of OpenSolver 1.5!

This is a bug-fix release:

  • Re-compiled CBC so that it does not have any external dependencies.
  • Fixed an error with cells formatted as currency or date.
  • Fixed some localisation-related issues with tolerance/ratio gap.

Thank you for submitting bug reports – they are very handy in solving problems. With so many different system configurations out there, it can be tricky to test even a fraction!

OpenSolver 1.4 (31 July 2011)

We are pleased to announce the release of OpenSolver 1.4!

For a full changelog, please see the document in the download.

  • Fixed 2003 menus – version 1.3 had some issues remaining.
  • Fixed some issues with and streamlined AutoModel
  • Fixed a ref-edit related focus bug in Model tool that was causing some strange behaviour.
  • Added partial locale support to Model. Entering number like 180,2 will work, but will display as 180.2
  • Added an Options window, available under Model in menu, and from a button on the Model form.
  • Added error catching in int/bin constraints on non-decision variable cells.
  • Updated CBC to version 2.7
  • Added custom icons for toolbar!

As always, please report any bugs via email or as comments.

OpenSolver 1.3 beta (7 July 2011)

We are pleased to announce the release of OpenSolver 1.3 beta!

Big features in this release are:

  • Excel 2003 menu support, thanks to Paul at Eclipse Engineering. Eclipse Engineering provide high-end CFD and FEA analysis along with a host of highly specialized CAE-based automation and integration solutions.
  • Support for 64-bit Excel 2010 has been added.
  • Removed last dependence on the built-in Solver: you can now build a model in the OpenSolver Model window!
A screenshot of the new Model tool
The new Model tool in OpenSolver 1.3

Bug reports are very welcome.

OpenSolver 1.2 is now the official stable version of OpenSolver.

Paul Jensen – Rest In Peace

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.

OpenSolver speedups

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…

Tinker Tailor Solver Spy

Have you ever wondered what calculations Excel Solver does to discover your model? Be curious no longer; this spreadsheet (ObserveCalculations.xlsm) will show you exactly what goes on. Be sure to bring up the VBA Immediate window (Alt-F11) to watch the calculations in real time. To make it easier, we’ve also copied some of this output into the sheets. You can, of course, also use this to watch OpenSolver doing its work. Enjoy. PS: You’ll see that things have changed quite a bit between Solver 2007 and Solver 2010…

Ranges and ‘Assume Non-Negative’ Quirks

I learnt a lot working on OpenSolver 1.1beta. For example, did you know that Excel’s union operator can produce ranges with duplicate cells!? Pearson describes the problem here. (We haven’t implemented this yet in OpenSolver as it may slow things down, but will do if it causes problems.) I also realised that Excel allows ranges (such as for the decision variables) like “A1:A5,A2:A6” that internally overlap; OpenSolver now internally fixes ranges like this when they occur.

I’ve always told my students that Solver’s “Assume Non-Negative” applies zero lower bounds to all the variables. I was wrong, as demonstrated by Solver’s new wording “Make Unconstrained Variables Non-Negative”. Lower bounds are only applied to variables that don’t have ‘explicit’ lower bounds set for them in the constraints. ‘Explicit’ here means the variable appears in the left hand side of a constraint which has a right hand side specified by a range or a constant. However, my preliminary tests suggest that Excel 2010 also requires that the left hand side of the constraint not include any non-decision variables. This can lead to different solutions when you move to Excel 2010, which seems rather strange. OpenSolver used to apply zero lower bounds to all the variables. The new version now implements the 2007 approach; you can see this in the new Bounds section in the .lp files.

Finally, did you know the right hand side of a constraint can contain a formula? This is a direct consequence of Excel allowing formulae to be entered as a “named range” (not that it is actually a range!). OpenSolver now handles these (not that I’d suggest you use this obscure feature).

OpenSolver 1.1beta released (4 March 2011)

OpenSolver 1.1beta is now available for download. OpenSolver can now run in Excel 2010 as well as 2007, and solves much larger problems and does so more quickly. (We tested on a problem with 70,000 variables and 70,000 constraints sent in by a user!) There are lots of little bug fixes and big improvements, including an AutoModel feature that provides an optional alternative to the Solver dialog; many thanks to my student Iain Dunning for coding this up. We now also check that the model is linear, and highlight any problems. Thanks to Kathleen Gilbert for working over her holidays to make these improvements. You can download the beta here. We look forward to your feedback.

Cited in Interfaces

OpenSolver has been mentioned in the latest INFORMS Interfaces journal in a COIN-OR article by Kipp Martin, who writes: 

Users of Excel should also be aware of OpenSolver. This is an Excel VBA add-in that extends the Excel built-in solver from Frontline Systems Inc. This add-in allows the user to formulate a model using the builtin Excel solver. However, rather than optimize the model with the built-in Excel solver engine, with the OpenSolver add-in, you can solve the problem using the COIN-OR Cbc mixed-integer linear programming solver. Thus, there are no size limitations based on license restrictions. Students can build and experiment with large realistic-sized models. Another feature of OpenSolver is that it has a command, View LP Model, which shows the algebraic statement of the model. This is a nice feature for debugging and actually seeing the underlying constraints and objective function in an easily readable format. Although built on COIN-OR software, OpenSolver is not available at the COIN-OR website; it must be obtained at http://opensolver.org/. 

Martin, Kipp: Tutorial: COIN-OR: Software for the OR Community, Interfaces 40(6), pp. 465–476, INFORMS 2011
http://interfaces.journal.informs.org/cgi/reprint/40/6/465 (subscription required) 

OpenSolver 0.98 beta (Jul 16, 2010)

OpenSolver has been upgraded to version 0.98 (still beta). This version includes bug fixes associated with quick solves (one GUI related, one that fixes the handling of multi-area ranges, and checks that the user is on the same sheet and workbook as that used to initialise the quick solve), and also improvements so that OpenSolver dynamically resizes its arrays to handle large problems (assuming everything fits in memory). All feedback appreciated… Andrew

Blogged by Mike Trick

Mike Trick, who visited New Zealand a few years ago, has blogged about OpenSolver. This has generated an interesting discussion about the GPL license, and whether this will limit what people want to do with OpenSolver. I’ve been having similar discussions with Ted Ralphs, the CBC maintainer, and Stu Mitchell of PULP fame. If anyone wants OpenSolver to be available under another license, then please let me know.

As well as all the contributions Mike made to OR in New Zealand during his stay, I also have to thank him for his Travelling Umpire Problem (TUP) which kept my Heuristics class very busy.

I hadn’t come across Larry’s IEOR Tools site before following the link on his OpenSolver comment. He too has written about OpenSolver. He’s created an interesting site with a good open source emphasis that I’ll be visiting more often.

Andrew