Blog

OpenSolver integration with NEOS servers

The recent alpha releases have featured solvers that interface with the cloud-based NEOS servers to solve the model. NEOS is a free service for solving optimisation problems and can run a large range of solvers. By interacting with NEOS, we gain access to these solvers from anywhere without needing to install them on the computer running OpenSolver.

In order to interact with NEOS, we create an AMPL file that describes the entire model and send this to NEOS for solving. Once NEOS solves the model, it sends back the optimal values, and these are loaded into Excel by OpenSolver.

OpenSolver already fully supports linear programs, and so when a linear solver on NEOS is requested, we can use the existing OpenSolver linear model algorithm to create the AMPL file we need. This algorithm exploits the fact that the model has linear equations, and determines the coefficients by the equations by modifying each decision variable in turn and observing the resulting change (a process that requires many spreadsheet recalculations, and so can be slow). This approach does not work for non-linear models.

Instead, what we do for non-linear models is parse the formulae on the spreadsheet to construct an AMPL model that contains all of the non-linear constraints and/or objectives that we need. We need to convert all of the non-linear Excel formulae into forms that AMPL can understand, allowing us to send the problem to NEOS.

To handle a variety of non-linear models in this way, we need to be able to convert individual Excel functions into valid AMPL code. This is currently handled for some common functions, but others like IF(), MIN() and MAX() have not been implemented yet.

Please try out the NEOS integration for both linear (CBC) and non-linear (Couenne and Bonmin) solvers and let us know how it goes. We welcome any feedback, especially about errors, that will help us improve the translation of complex OpenSolver models into AMPL files.

OpenSolver 2.5.1 alpha (25 Jun 2014)

Today we are releasing OpenSolver 2.5.1 alpha, which builds on the 2.5 alpha release from last week.

The main addition in this release is the option to use the non-linear solvers Couenne and Bonmin through the cloud-based NEOS servers.

We have also included a 64-bit version of CBC that is used automatically on 64-bit systems.

There are also stability fixes for the non-linear NOMAD solver that was included in the 2.5 release, and other bug fixes.

You can download it here. Like 2.5, this is an experimental alpha release, and we are looking for feedback on the new features, as well as any problems you might run into while using them.

OpenSolver 2.5 alpha (20 Jun 2014)

We have just released OpenSolver 2.5 alpha, which has a number of fixes and improvements. It also has some new experimental features.

You now have the ability to select from number of different solvers:

  • CBC (included and upgraded to version 2.8.8 in this release)
  • Gurobi LP/IP solver (if this is installed on your machine)
  • NOMAD non-linear solver (included, supported in 32-bit Office only for now)
  • Solvers on the cloud-based NEOS servers – CBC (linear) is supported now, and we hope to offer Couenne and Bonmin (both non-linear) in a future release.

This release also adds reporting of dual variables and sensitivity analysis, and as well as many small bug fixes and feature enhancements. Many thanks to students Matt, Kris, and Jack for their work on these enhancements.

You can download it here. All feedback is welcome, particularly regarding the new experimental features.

 

SolverStudio Speedup: 2 hours to 20 seconds

One of our OpenSolver users recently worked with modelling guru Stu Mitchell to move his model from OpenSolver to SolverStudio. The OpenSolver model originally took 4 hours to solve. After simplifying the spreadsheet, this reduced to 2 hours. Stu created a SolverStudio version that solved the same problem in just 20 seconds. To quote our user: The conversion to SolverStudio has been an excellent improvement.

If you have a complicated spreadsheet, then you may also find that SolverStudio  gives much better solve times. Furthermore, the latest SolverStudio release includes ready-to-run models for many common problem formulations, which helps make the conversion even easier. Give it a go, and let us know how you get on.

Andrew

Seeking impact testimonials

OpenSolver would not be possible without the CBC solver developed by the COIN-OR group. Working with colleagues Bob Fourer, Kevin Furman, William Hart and Michael Trick, we are preparing to nominate the initiators and founders of COIN-OR for the 2014 INFORMS Impact Prize. Thus we are soliciting testimonials to the impact that COIN-OR projects, and OpenSolver in particular, have had on diverse research and business activities.

We invite you to send us a paragraph or two describing how OpenSolver software has made a significant difference to your work and accomplishments. Please send your description by June 25 to me at email hidden; JavaScript is required Also feel free to send me any questions about what’s needed.

 

OpenSolver Application for Whirlpool

This is a nice OpenSolver application that gave savings of $1.1M annually:

https://smartech.gatech.edu/handle/1853/42172

Title: Batching Outbound Shipments for Whirlpool Corporation
Author: Bertolino, Michele ; Herbst, Ana ; Jones, Andy ; Liem, Adam ; Nemes, Travis ; O’Gorman, Melanie ; Russell, Samantha ; Sood, Eric
Abstract: The team investigated the implementation of batch-picking shipments at a regional distribution center for Whirlpool Corporation, the world’s largest appliance manufacturer. An Excel-based optimization tool was created to strategically batch shipments to more effectively utilize carrying capacity of clamping forklifts across the network. Due to the decrease in labor hours, this resulted in savings of $1.1M annually.

Informs Transactions on Education: Paul Jenson + SolverStudio

Informs Transactions on Education has just published a special issue in honour of Paul Jenson who developed some excellent Excel tools for teaching operations research and management science. This special issue includes an article on SolverStudio. Cheers, Andrew

INFORMS SolverStudio Presentation

I’ll be presenting on SolverStudio at the INFORMS conference in Minneapolis. Please come along to the Modeling Systems II session “MD08”, Monday Oct 07, 16:30 – 18:00, Level 2 – Room 200H to learn about SolverStudio, or to ask me OpenSolver questions! I look forward to seeing you there. Andrew

Americas Cup Yacht Racing

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).

Non-linear NOMAD integration

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….

Masters thesis: OpenSolver for scheduling physicians

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.

OpenSolver 2.1 (6 Sept 2012)

OpenSolver 2.1 is now released, and has a number of fixes and improvements. These include a newer version of the optimiser CBC, a bug fix for handling multiple cell blocks as decision cells on non-English systems, more robust non-linearity checking, and the ability to turn off this non-linearity checking if you know your model is linear. We now also display ≥ instead of > in our on-sheet constraints. All feedback is welcome.