Blog

250k milestone

Today the download odometer rolled past another milestone:

Thanks to all our users and our development team (and most recently Jack Dunn) for supporting OpenSolver for the last 6 years. None of this would have been possible without the solvers from COIN-OR; thanks to John Forrest and Ted Ralphs for the many years of CBC development, without which the OpenSolver project would never have started.

Andrew

OpenSolver 2.8.5 (3 Nov 2016)

Today we are releasing OpenSolver version 2.8.5 which fixes a bug brought about by the October update to Excel 2016. This bug in Excel prevented clicks on the constraint section of the model creation form in OpenSolver, and there is a workaround in today’s update so that things work as intended.

You can get the new version here.

The release also includes a new feature that a number of users have asked for in the past, which is the ability to use the NOMAD solver to optimize models where the objective and constraints depend on the output of macros. For instance, one user wanted to use the results of a Pivot Table that depended on the variable values in their model, but Pivot Tables are not updated when the sheet is recalculated so this did not previously work with OpenSolver. Another example comes from Judd Larson at the University of Wyoming. Judd’s problem involved optimizing a function that depended on the solution to a series of differential equations, which he was solving using his VBA implementation of an ODE solver. This is not a problem that can be expressed and solved in the traditional way that OpenSolver supports, but is possible to solve now with the new ability to incorporate the macro into the solution process. We are very pleased to offer a solution to a tough problem like this! For more information on how this feature works, please see the new section in the documentation describing this feature.

As always, please let us know about any issues you might have, or features you would like to see in future releases!

OpenSolver 2.8.4 (12 Oct 2016)

We have just released OpenSolver 2.8.4 which fixes a bug in the 2.8.3 release that caused it to crash on the newest versions of Excel (in most cases the Office 365 versions). This release also fixes a bug where the saved solver choice could get corrupted and force you to re-select the solver.

You can download the 2.8.4 release here.

Don’t forget to enable the update checker in OpenSolver if you want to be notified automatically of new updates like this one.

As always, please let us know if you have any feedback or problems.

OpenSolver 2.8.3 (05 Oct 2016)

We are happy today to announce the release of OpenSolver 2.8.3! This is a bugfix release for the 2.8.2 release, but we are also using the occasion to mark the 2.8.x releases as stable. This means 2.8.3 will become the default download for all users and is the recommended version for everyone.

You can download OpenSolver 2.8.3 here.

This update is primarily a bugfix release (the changelog lists around 20 significant fixes) as well as two new features.

The first new feature is extended support for using the solution on the sheet as the starting point for the solver. Passing the solution as a “warm start” to the solver in this way is now supported by all solvers except CBC. This functionality is enabled automatically – if the initial solution on the sheet is feasible, it will be sent to the solver. This can give significant reductions in solve time when solving tough integer problems.

The other big change in this release is our first partial support for Excel 2016 for Mac. This version should work if your version of Excel is at least 15.28. Unfortunately, the VBA support on this version of Excel is very limited, and so it has been very difficult to get OpenSolver working. We do not yet have the full range of functionality running, but we do have some core features working for those that need to use Excel 2016 for Mac or want to help with testing. Right now, the features that we know are working are model creation and manipulation, and solving using the linear solvers (CBC and Gurobi).

There are some major changes in the way Excel 2016 for Mac works that have made our life difficult. The most important of these is that Excel is now sandboxed for security reasons, which makes it harder for us to run the optimization solvers. Currently, the only workaround we have come up with requires you to run an installer after you download OpenSolver that installs the solvers we use on your system so that they can be used by Excel. Unfortunately this means that you will need administrative privileges on your computer to set up OpenSolver if you want to use Excel 2016 on Mac. Please see the installation page for more information about this.

It seems that Excel 2016 for Mac is significantly slower than any other Excel release, which leads to much slower OpenSolver solve times. We can only hope that Microsoft improve its speed in future releases.

We appreciate any feedback you might have on the release, especially in regards to Excel 2016 for Mac. If you do encounter any problems, please either use the “Report Issue” button from inside OpenSolver, or leave a comment here so that we can address the problem.

Fantasy Football – from OpenSolver to Cloud optimiser

This blog by Derek Nelson shows how he built an OpenSolver model for fantasy football that worked well for him. “My optimizer was created using Excel with the Open Solver add-in. This worked for me and the results were good. Namely, I had the highest projected points for my teams at the start of the season in both leagues I played in (the best you can hope for in the draft, in my opinion), and I ended the season by winning one league and placing second in the other.”

Derek has now gone on to create a cloud-based version of this. This work is an interesting optimisation-based project.

OpenSolver for Google Sheets 2.2.1 (25 July 2016)

Today we are releasing OpenSolver for Google Sheets 2.2.1, which is a very minor update with changes to improve our support for larger models.

Any script running in Google Apps Script has a strict 6-minute time limit while running, and is immediately cut off with no feedback if this limit is reached. It can often take longer than this to build the OpenSolver models if they are large, so we save the model-building progress as we go. If the build is stopped early, we can simply resume the solve from where we last saved the progress. Previously, this would occur the next time the “Solve” button was clicked. With this update, the solve is now automatically resumed if it is cut off, meaning that there is no need to click the “Solve” button repeatedly to build larger models piece-by-piece.

The other important change in this release is a new mechanism for saving the progress as we build the model. Previously we used the Google Apps Script CacheService to store the updates, but this limits the saved data to 100 KB which is too small for larger models. This update changes our behaviour to instead save the progress to a new hidden sheet in the workbook, allowing us to save much larger models.

Other minor changes include:

  • A bug where the costs were not loading correctly when resuming the solve has been fixed.
  • Error messages now contain the stack trace for better debugging.

If you already use OpenSolver for Google Sheets, the update will roll out automatically. If you are not yet a user, you can add it to Google Sheets using the link below:

As always, we welcome any feedback or ideas. We are coming up on a big milestone of 10,000 weekly active users for the Google Sheets addon, and are very happy to see that it is proving useful! If you find it useful, we would appreciate if you could rate us in the Chrome Webstore and/or leave a review for others.

Cool OpenSolver Applications

I recently received a nice email from Andrew Trapp, Assistant Professor, School of Business, Worcester Polytechnic Institute. Andrew has been using OpenSolver and SolverStudio in his student projects. He writes:

I’m writing to first of all thank you for the excellent software tools that you make, OpenSolver and SolverStudio, that I have been using and featuring for 4 years now in my optimization courses. They have also been used in a number of “senior design” projects that I have advised (specifically, OpenSolver in conjunction with VBA…)

Andrew’s OpenSolver projects look very interesting, and show how OpenSolver can be used in practical applications. The reports are available online, as follows:

Andrew

OpenSolver for Google Sheets v2.2.0 (18 April 2016)

Today we are releasing OpenSolver for Google Sheets 2.2.0, which is a minor update with the following changes:

  • We have added support for early stopping in GLPK. If your problem is taking a while to solve and you are satisfied with the quality of the best solution that has been found so far, you can stop the solve and OpenSolver will load this solution into the sheet. To stop a solve early, just click the “Cancel solve and use current best solution” button.
  • When solving a “Target Objective” problem, we now try to minimize the deviation from the target rather than making the target a hard constraint. Previously, if the target was hard or impossible to reach, the solver may have taken a long time to finish. Coupled with the early stopping in GLPK, you can now stop the solve early if you see that the solution is close enough to your desired target. We will be adopting this approach in OpenSolver for Excel in the very near future.

If you already use OpenSolver for Google Sheets, the update will roll out automatically. If you are not yet a user, you can add it to Google Sheets using the link below:

As always, we welcome any feedback or ideas.

OpenSolver for Google Sheets v2.1.0 (15 April 2016)

Today we are releasing version 2.1.0 of OpenSolver for Google Sheets! This is a minor update that brings the following changes:

  • We have added support for the GLPK solver. You can now solve problems in your browser using the open-source GLPK solver, via glpk.js by Henri Gourvest. This solver runs in the browser on your computer rather than on a cloud server, so is typically much faster than the alternatives and we heavily recommend choosing it in place of Cbc via NEOS. Please let us know if you find this useful!
  • The linearity check now occurs before solving, in order to catch non-linearities before spending time solving the model. We recently changed the linearity check in OpenSolver for Excel to be much more robust and better at detecting non-linearity, and we are pleased to roll these changes out to Google Sheets as well.
  • Bugfixes:
    • Fixed bug where dialogs could time out before they finished loading.
    • Fixed sheet-scoping issue when storing constraints in multi-sheet models.

There is more information about the Google Sheets addon here on the dedicated page.

If you already use the addon, the update will automatically roll out. If you haven’t used it before, you can try it out here:

Update on Google’s Linear Solver

Google’s LinearOptimizationService has been fixed as of earlier today, so the Google Linear Solver should be working again. We are happy to now offer two additional solvers in case this happens again. It took over two weeks for Google to fix this bug, which is very frustrating for all of us, and hopefully is a problem we will be able to avoid in future!

Feedback and Issues

OpenSolver for Google Sheets is younger and less mature than the Excel version, so does not yet have the full range of features as in the Excel version. There are also a lot of possibilities that are created by the online aspect of Google Sheets (such as sharing sheets and multiple users on a sheet at once). This means there are many new features to consider and add to the addon as it evolves over time.

If you have any feature requests or experience any problems while using it, please let us know either here or by using the “Report an Issue” button inside Google Sheets (you can find this in “Add-ons > OpenSolver > Help”). If you use “Report an Issue”, please make sure that you include your contact details, otherwise there is no way for us to follow up!

We are always interested to hear how you are using OpenSolver, and in particular if you find the Google Sheets version useful (because we can”t count downloads, we have much less user information on the number of Google Sheets users). This allows us to balance the time we spend developing the Excel and Google Sheets versions according to user demand.

If you like the Google Sheets addon, please consider rating us in the store and/or leaving us a review, which will help other users find out about OpenSolver!

OpenSolver for Google Sheets v2.0 (7 April 2016)

We are very excited to announce the release of OpenSolver for Google Sheets 2.0! This is a major update that brings many improvements to the Google Sheets addon:

  • Support for one more than one model per workbook! Now, each sheet can have its own model. Use the sheet selector at the top of the sidebar to switch to the model for a sheet.
  • Models can reference cells on different sheets! You can define models using ranges on any sheet in the workbook.
  • Added a new solver: Cbc on NEOS! You can choose to either use the solver provided by Google, or the Cbc solver on the NEOS optimization cloud. Note that all problems submitted to NEOS become publicly available, please see the terms of use.
    , and use the new Cbc solver to solve models on the NEOS cloud server.
  • Support for resuming incomplete solves! If the solve takes more than 6 minutes, it will be stopped before a solution is found. Now you can resume the solve from where you left off, allowing you to solve larger models than before.

There is more information about the Google Sheets addon here on the dedicated page.

If you already use the addon, the update will automatically roll out. If you haven’t used it before, you can try it out here:

Update on Google’s Linear Solver

Last week, Google made breaking changes to their LinearOptimizationService inside Google Sheets, which means that it currently is not functional at all, and so we can’t use it in OpenSolver to solve optimisation problems. We reported the issue to Google immediately, but it has not been fixed at the time of writing this post. In the meantime, you should use the Cbc solver via the NEOS server.

If you would like to see Google fix their solver faster, you can click the star on the issue page linked before. The more stars the issue gets, the faster they will fix the problem.

Migrating models from previous versions of OpenSolver

Due to supporting one model per sheet, we have had to make changes to how we store the OpenSolver models, and so your current models are not automatically loaded into the new version. To load in your old model, you can either redefine it manually, or use the “Import previous model” item in the menu. This will load in your previous model as the model for the currently selected sheet.

Please get in touch if you have problems with this feature, we want to make the transition as smooth as possible for everyone. You should also use the “Show previous model data” menu item to open a box with the model information for your sheet. Please include this when you get in touch so that we can diagnose the problem.

Feedback and Issues

OpenSolver for Google Sheets is younger and less mature than the Excel version, so does not yet have the full range of features as in the Excel version. There are also a lot of possibilities that are created by the online aspect of Google Sheets (such as sharing sheets and multiple users on a sheet at once). This means there are many new features to consider and add to the addon as it evolves over time.

If you have any feature requests or experience any problems while using it, please let us know either here or by using the “Report an Issue” button inside Google Sheets (you can find this in “Add-ons > OpenSolver > Help”). If you use “Report an Issue”, please make sure that you include your contact details, otherwise there is no way for us to follow up!

We are always interested to hear how you are using OpenSolver, and in particular if you find the Google Sheets version useful (because we don’t control the downloads, we have much less user information on the number of Google Sheets users). This allows us to balance the time we spend developing the Excel and Google Sheets versions according to user demand.

If you like the Google Sheets addon, please consider rating us in the store and/or leaving us a review, which will help other users find out about OpenSolver!