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.
Fixed some display issues in the model dialog to give more compact displays, and force formulae RHS to be in absolute terms
Fixed a bug that saw models being wrongly built and then reported as non-linear (incorrectly) for some complex models if Excel calculation mode was set to manual.
Fixed passing of multiple parameters to CBC if the user defines a parameter table on the sheet; previously only the last parameter was passed.
Deleted the DLL’s downloaded in the .zip file which don’t seem to be needed now that we statically link everything in CBC.
Version 1.8 fixes a problem that prevented OpenSolver 1.7 loading on 64 bit systems, and improves handling of models that have decision variables and constraints on multiple sheets. Note that Excel does not allow ranges to contain arbitrary cells from many sheets, and so all the decision variables must be on the same sheet.
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.
OpenSolver 1.7 has been released. (OpenSolver 1.6 was an internal release only.) Changes include:
Version 1.7 beta
Added controls in the About box to allow easy installation and uninstallation
Added code to interact nicely with the forthcoming OpenSolver Studio
Improved OpenSolver for use from VBA:
– Build and Solve operations now throw errors (instead of popping up dialogs), allowing dialog-free usage from VBA
– Return codes are better handled (and Solver compatible)
– A new optional parameter has been added to RunOpenSolver to avoid dialogs even if infeasible/unbounded solutions are generated
Version 1.6 beta
Fixed display and editting of an objective target value in the Model dialog.
Modified the Open Last Model in CBC functionality so that it passes any Solver options and any CBC solve parameters to CBC if they are available in any current worksheet
Fixed a minor issue in Model dialog where a RHS could be entered for a new constraint if the user had previously had a Bin or Int constraint selected
Fixed a redim bug in the quick non-linearity checker for models with no constraints (which can happen if there is only a target objective value)
Added “Show optimisation progress while solving” (being Solver’s “Show Iteration Results”) to the OpenSolver options dialog
Improved operation of Options dialog, including proper sycnronisation of values when opened from the Model dialog
Better handling of the Excel 2010 “Simplex engine” option as used in parallel with “Assume linear model”
Fixed an error in the full non-linearity checker
Added output of dual prices onto the sheet; this is set using the Model dialog
Rearranged Model dialog to better fit new Duals option, and better use space around constraint listing
Better handling of the Excel solver options – OpenSolver now sets all these to sensible defaults
Better handling of users entering formulae in the Model dialog for a constraint RHS in terms of non-English localisation issues, but this still needs work
Fixed a size limitation in Quick Solve, and converted Quick Solve to sparse matrix handling for better memory usage.
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 1.2 beta now treats blank cells in constraints as zero instead of reporting a model error. This makes it more compatible with standard Excel behaviour.
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…
We use a wide variety of spreadsheet models and associated spreadsheet layouts to test OpenSolver; these are available here, so you too can see exactly how Solver and OpenSolver handle merged cells in your decision variables…
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…
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 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.
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 is moving from the GPL to the Common Public License (CPL), the license favoured by the COIN-OR community. We’ll be releasing a new version shortly (with a few minor improvements) under this license.