Student Project Opportunities
The Engineering Science department at the University of Auckland runs final year student projects in which our students apply their optimisation and modelling skills to real problems of practical interest. We are looking for large optimisation problems that might be of interest to our students. We are particularly interested in developing models using the new SolverStudio add-in, and so if you have a large problem, perhaps one that currently uses OpenSolver but takes too long to solve, then we would welcome an email from you to email hidden; JavaScript is required. Cheers, Andrew
Announcing SolverStudio 0.1 beta
We are pleased to announce the very first, very beta release of SolverStudio. SolverStudio lets you use Excel to edit, save and solve optimisation models built using modelling languages such as the Python-based PuLP, AMPL, GAMS, GMPL and Gurobi’s Python interface. The interface is fully Excel-based, with the model being edited and run from Excel and stored inside the Excel file. This approach provides a much better modelling solution for complex optimisation problems. Check out the screen shots to see how it works. While SolverStudio is ideal for larger problems, OpenSolver is still a great tool for simpler models, or spreadsheets that must be compatible with the built-in Solver.
OpenSolver uses the COIN-OR CBC optimization engine
The latest version, OpenSolver 1.9 is available for download, with some bug fixes, improved handling of models that cross multiple sheets, and a fix for issues that prevented the previous version working on 64 bit systems. Check out this post for the improvements made in the 1.8 version, and here for the 1.9 fixes.
Welcome to OpenSolver, the Open Source linear and integer optimizer for Microsoft Excel. OpenSolver is an Excel VBA add-in that extends Excel’s built-in Solver with a more powerful Linear Programming solver. It is developed and maintained by Andrew Mason and students at the Engineering Science department, University of Auckland, NZ. OpenSolver provides the following features:
- OpenSolver uses the excellent, Open Source, COIN-OR CBC optimization engine to quickly solve large Linear and Integer problems.
- Compatible with your existing Solver models, so there is no need to change your spreadsheets
- No artificial limits on the size of problem you can solve
- OpenSolver is free, open source software.
- Note that OpenSolver does not solve non-linear optimization problems, so your Solver model needs to have “Assume Linear Model” turned on.
As well as providing a replacement optimization engine, OpenSolver offers:
- A built-in model visualizer that highlights your model’s decision variables, objective and constraints directly on your spreadsheet
- A fast QuickSolve mode that makes it much faster to re-solve your model after making changes
- An algorithm to build and update the model only using information present on the sheet
- A modelling tool that we think improves on the built-in Solver window
OpenSolver has been developed for Excel 2003, Excel 2007 and Excel 2010 (including the 64bit version) running on Windows.
You can download OpenSolver.zip (which is hosted on our Open Solver Source Forge site).  Version details (and dates of updates) are shown on the blog page.
OpenSolver is being developed by Andrew Mason in the Department of Engineering Science at the University of Auckland, and Iain Dunning. Kat Gilbert also made valuable contributions to the code while working as a summer student. Development of OpenSolver would not have been possible without the excellent Excel Name Manager which displays all the worksheet names used by Solver to store an optimization model.
OpenSolver uses the open source COIN-OR CBC optimization engine. CBC is released as open source code under the GPLÂ (but we are happy to make it available under other licenses, such as the Common Public License (CPL) if there is a demand)Â . It is available from the COIN-OR initiative. The CBC code has been written by primarily by John J. Forrest, and is maintained by Ted Ralphs.
To build its optimization model, OpenSolver iterates through all the decision variables making small changes to each one while recording how the objective function cell and the constraint cells change. This allows the coefficients in the associated equations to be determined. Note that this assumes your model is linear; OpenSolver does some checks of this, but cannot catch every case.
The Excel Solver is a product developed by Frontline Systems for Microsoft. OpenSolver has no affiliation with, nor is recommend by, Microsoft or Frontline Systems. All trademark terms are the property of their respective owners.
I opted out of some of the software downloads that came with the shareware, and now it is not working. Do I have to agree to the spyware, or have I missed a step in the install?
OpenSolver is not shareware (it is open source), and does not include any other software downloads. Please use the download from this site to get a clean copy of the software untainted by any spyware or similar. (Your comment is most puzzling…) Andrew
Can any body help me. When I solve my model then this error occure…plzzzzzzzzzzzzz
OpenSolver could not find an optimal solution, but reported
No Solution found (unbounded)
A solution has been generated, but may be infeasible or sub-optimal or both
(CBC solver reported: Unbounded – Objective value 0.00000000)
Check your model carefully. Andrew
i’m searching about the solver basic theory and algorithm but i still not understand about how excel works manually…can someone help me ? i need for my thesis…thank you
Hi, I have built many very large LP’s, and do so for a living. I have written a generalised front end for my models, which uses Excel to define the Classses of variables in the model, the members of these classes, and the tables which contain all of the data which populates the model. This process produces a large text file in MPS format, which is the whole LP for solving. My larger models have tens of thousands of variables and constraints, but I am looking for something that can be built into the middle of a Production Scheduling System utilising much smaller models at present. Can your solver(s) use MPS format input? This me http://www.linkedin.com/pub/alan-harrington/20/135/6b2
cheers,
Alan Harrington
The Excel solver gives a report of the shadow prices (dual solution) under LP mode. Can we get the same report with OpenSolver?
You can get the shadow prices (but no range information); check out the (relatively) new “Shadow Prices” option under the OpenSolver Model dialog. Cheers, Andrew
I am not able to open and use the opensolver.It is saying a message cant find solver kind of message.
I’m sorry it is not working for you. I suggest you remove any current version, and then try downloading it again. If that does not work, reparing Excel has worked for people. It is more likely to be a problem with your setup than with OpenSolver. Hope this helps, Andrew
Great Product, much better than anything else on the market
We are using version 1.9 run via the C# equivalent of RunOpenSolver true false. This works wonderfully for runtime exceptions. The problem is that this doesn’t seem to stop the “OpenSolver: Linearity check” message box from popping up. Moreover, the warning message… “Warning: The following constraints(s) do not appear to be linear:”… is incorrect. A simple “Run a full linearity check” will verify that the problem is in fact linear.
Any ideas? We really need to rid ourselves of message boxes for the product to be useful in a serious way.
Thanks for all your hard work, you’ve produced a gem!
I have come across this issue before, which seems to be caused by numerical issues. I will email you version 2.0 to test; it has two improvements. The first is to better estimate the accuracy of numerical calculations so as to avoid false reports of non-linearity. The second is to completely skip the non-linearity check. Please let me know if it fixes your problem (preferably without manually turning off the linearity check!). Andrew
Thanks. Much appreciated. Would you consider adding in one or a few examples to speed up the self-tutorial process? Happy New Year.
Hi Andrew! Great job! Easy to install. Nice button design.
I’m looking to use solver on a model which uses VBA to perform most of the calculations, variables and results are tracked on the worksheet. Unfortunatly Excel’s built-in solver cannot help with this… can opensolver?
Neither Solver or OpenSolver care how the values appear in the spreadsheet, as long as you can specify decision variables etc on the sheet. So, having calculations performed in VBA is fine as long as you are using User Defined Functions for these. If your calculations are run by a maco, then neither Solver nor OpenSolver will work. Further, if your calculations are complicated, they are probably not linear, and so the CBC solver used by OpenSolver will not work. If you are using VBA as a modelling lanaguage, then I suggest you look at SolverStudio (http://solverstudio.org) which handles modelling languages (currently PuLP, with others coming soon). Hope this helps, Andrew
Re: Calculations run by a macro.
My workaround is to use the Change event of the worksheet that contains the cell(s) that Solver is using.
It runs every time a cell value on the worksheet (“Main_sheet”) changes. If the cell is one of Solver’s change cells, the calculations macro runs (Sub MainA)
The key part is that while the calculations macro runs, Events are turned off. After the calcs are printed in the worksheet by MainA, Events are turned back on and Solver will react for its next iteration.
Hope this is useful. I’ve only used this with built-in Frontline Solver.
Mike
Private Sub Worksheet_Change(ByVal Target As Range)
Dim adjustCells As String
On Error Resume Next
Application.EnableEvents = False
adjustCells = SolverGet(typeNum:=4, sheetname:=”Main_sheet”) ‘Solver changing cells
If InStr(1, adjustCells, Target.Address, vbTextCompare) 0 Then
Call MainA ‘MainA does the calcs. Only runs if change is in Solver change cells
End If
Application.EnableEvents = True ‘now Solver will fire
End Sub
Mike, Thanks for that tip, which is a good way to do something complicated in a (presumably non-linear) model. I would not have thought that disabling events was crucial, but I have not tried it. Another way would be a user-defined function which also allows VBA code to execute during a Solver run if the input variables to the function include the model variables. (Readers should note that OpenSolver only does linear models, for which these tricks are not so useful.)
Cheers, Andrew.
PS: Now we know that Solver is the reason why your boats are so fast…
Solver: couldn’t live without it! I’ve only just seen your project and will give it a go.
I notice there is a typo or maybe Web display problem in the above code, the correct line is:
If InStr(1, adjustCells, Target.Address, vbTextCompare) 0 Then
in case it’s a display thing:
If Instr(….) not_equal_to 0 Then
It’s about a decade since I figured this out, and I’m pretty sure turning off Events while the calc macro runs is important because Events are recursive, so the first result printed by the macro on the worksheet will fire Solver (I think); which will run before the calc macro finishes printing the full results.
At least that’s how I remember the problem.
My main use of this is to solve for geometry features by running a NURBS macro.
Mike
Mike
Need help!
I am trying out to find out the minimum possible manpower for the required work load.
I am selecting the decision variables directly using mouse but the variables are gone when I reached the 34th variable. What happened? I’m using Excel 2007.
Anyone?
There is a maximum limit set by Microsoft on the number of characters allowed in the decision variables RefEdit text box. You may be encountering this? We have increased the size of this text box to allow more decision variables to be entered and displayed. I would suggest you group your decision variables together so they can be entered in one go, or create new decsion variables in one group, and refer to these in your current decision variable cells. Hope this helps. Please let us know how you get on. Andrew
Yes, you are right with the RefEdit text box. It work! Thanks!
What a wonderful project! Hope to continue growing…
… Is there a way to use it from vb.net express?
Congratulations!!
Thanks for your positive feedback. Yes, you can run VBA macros (include OpenSolver’s) from within a .net language by (1) first loading the OpenSolver add-in, and then (2) calling a macro in the workbook, using code such as Application.Run(“OpenSolver.RunOpenSolver”). (This comes from a VSTO add-in, where Application is defined as: internal Microsoft.Office.Interop.Excel.Application Application.) Please let me know how you get on; I’m sure other users would be interested. Andrew
I have reached the limit of the standard Excel LP solver and OpenSolver seems to be very promising. One small question – Can OpenSolver be manupulated by VBA?
Yes, OpenSolver is VBA-friendly. You can edit models just as you do with Solver (because they are Solver models), and then optimise them using the VBA code at http://opensolver.org/using-opensolver. Hope this helps, Andrew.
Can your tool handle three subscripts for variables? It looks very helpful and I love that you can do large problems.
Thanks!
OpenSolver just sees a list of variables on your spreadsheet, so it doesn’t care how many subscripts there are. If you are thinking about subscripts, then it sounds like you are using a modelling language – maybe you should look at OpenSolver’s companion product, http://opensolverstudio.org, which supports modelling languages such as PuLP (and, in a coming release, AMPL). Andrew
We are using the opensolver to optimize water conservation. The problem uses the number of water using accounts at varying levels of consumption to apply water conserving best management practices, to get the most water conservation for the cost of the BMP. However, when we try to apply the model to individual accounts, we will end up with ~4M variables, including constraints. Opensolver seems to begin solving the problem, but it looks like it will take 155+ of processing time. Is there any way to speed this up. I am using a 32 bit machine and the latest version of opensolver. I think the premium solver has an option which disregards all of the obvious solutions to shorten the run-time. Any ideas?
Max: That sounds like a really interesting problem you are solving. Can you please explain it in a little more detail. Can I confirm that you have a spreadsheet with 4 million decision cells on it? Is it an LP or an IP that you are solving? When you say OpenSolver starts solving the problem, I assume it is in the building phase (when you can see it stepping thru all the decision cells, as displayed in the Excel status bar), not in the running the external optimizer phase? Perhaps if you email me the spreadsheet, I can take a look… a dot mason at auckland dot ac dot nz. Thanks, Andrew
A bit more info. OpenSolver performs a slow tedious process to determine the model. The Frontline products that include their Psi engine can by-pass this step, and so will be faster. However, if you have a problem with 4 million variables, then I’d suggest you look at OpenSolver Studio (http://opensolverstudio.org), which allows you to build a model separately from the data. This should be much faster for large problems. OpenSolver Studio will be adding more modelling languages as it matures; at the moment it comes with PuLP. By the way, can you confirm that your model is a linear one? Are there really 4 million cells selected as decision cells (ie adjustable cells) in your model? Are these binary or continuous variables? How many constraints are there? Andrew
Andrew,
I am sending a copy of our spreadsheet that contains fewer decision variables. Most of our utilities have ~20,000 accounts. Each account currently has 14 decision variables. That’s 280,000 total. Our largest utility has almost 300,000 accounts, which would result in about 4M decision variables.
Andrew,
we sent the data yesterday. We have been playing around with PuLP and ran a smaller model with ten accounts, successfully. We were trying to incorporate the Open Solver – Model Data Items tool into the PuLP set of instructions, and were unable to make it work. We will be working towards accomplishing this while waiting for your response. Great tool!
Thanks in advance!
Max
I am going to try it I will share my experince .
Hi,
thank you so much for programming the OpenSolver. It really helped me out when I had reached the limits of the standard solver.
Best Regards,
Richard
Richard: Thanks for the positive feedback; it is really appreciated by the OpenSolver team. Andrew
Thanks for your great work! OpenSolver is THE way to go if you are bounded by the restrictions of the regular Excel solver. Keep up the good work! Kind Regards, Team EasyExcelTutorial.com
Yours is a very usefull tool!
I might suggest that it is important to save models and, later, load them…
When variables are too many, they fills the space of “variable cells” field. Often, the name of sheet, inside constraint fields, could fire some exception.
These problems, however, are all surmountable.
Thanks for your comments. Your feedback on the variable cells has been most useful; the next version will have a larger space for these. Can you please give more details on the exception firing problem? We would like to fix that. You can still use Solver to save and load models if you need to do that. Cheers, Andrew
Thank you for your fast reply.
About error due to the sheet name: suppose “a b” the sheet name. So, if I use the wizard of OpenSolver to create constraints I select cells with the mouse… Inside the LHS I have, for example, ‘a b’!$C$14, and inside RHS I have ‘a b’!$C$15 (with operator <=). If I click on "Add Constraint" an error message appears: "The formula or value for RHS is not valid. Please check and try again.". So, I usually delete " 'a b'! " from both fields and the adding action work.
Sorry for my double reply (and for my hazardous English)! But, I just thought that Cbc solver, with an appropriate configuration of the logging system, writes how many nodes have been opened and their information, like the lower bound, the timestamp, ecc…
So, if I wanted to evaluate, for example, the trend of solution value, I would read log…
What do you think about?
Andrea, Sorry; I missed your post before. The log is written to the C “standard out”; you would need to capture this (or pipe it to a file) if you want to analyse or display the log information. Let me know if you succeed in making this work. Cheers, Andrew
Hi,
When I click solve button on OpenSolver 1.4, I always get this message:
“Model Solve Failed: The CBC solver did not create a solution file. No new solution is available.”
Regards,
Hi Mauricio,
Please try OpenSolver 1.5, we think it will solve the issue.
Hi Iain,
Thanks, OpenSolver 1.5 works fine!!
Congratulations!!! FANTASTIC!!! It’s very easy, faster and more precise than Premium Solver
last week i was running standard solver in Excel 2007…. 14 hours of processing… with OpenSolver…. only 1 (one) minute!!!!!
Thank you very much…!!!
Cristian
from Chile
Hi Andrew,
Thank you very much for this mammoth efforts to create this fantastic application!
It helped our organization to solve Mixed Integer Linear Programs of around 5000 variables and 6000 constraints very quickly, without buying premium solver. Also, it is very easy to integrate it with Excel’s existing solver and also to automate in VBA. I found that OpenSolver always provides as good solution as premium solver and sometimes produces even better solutions, that too with considerably less time and memory usage.
Once again, thank you very much!!!!
I am pleased that OpenSolver has been useful. Thanks for the feedback. Andrew
Hello Ashwin,
I am trying to do the same. Can you please help me in that. Is it possible to import a lp file to the opensolver? Also can you please send me the model.
Thanks!!
I’ve used this for a scheduling problem that I’ve encountered. It had tons of variables that the default add-in couldn’t handle but open solver could! Great work guys!
great work! this is an awesome project and great to see a solid open-source optimization option for excel.
just a quick friendly reminder about opensolver for office 2010. Thanks for your great job!