Many thanks for this fantastic add-in! I use it for tuning/optimising algo trading systems I develop for my personal trading activities. Given the non-linearity of my models I’m pretty much limited to NOMAD, but it performs fantastically. My only wish is that I can do sensitivity and robustness analyses.
I am new to OpenSolver and I am wondering, if there is a way to use OpenSolver without Excel. As a risk controller of a bank I have the task to integrate an optimization function (minimize the value at risk of a given set of interestrate sensitivities within a set of boundaries). Since this should be done on a database instead of an Excel spreadsheet I need to call the OpenSolver API directly via code.
You could move the VBA code out of Excel into Access, but it would be a big job! You do better using a proper modelling language, such as Python/PuLP, and access the database from the Python code. We may be able to provide consultancy assistance if needed. Andrew
thanks for the quick reply. That is indeed not much of a problem, since the code is already implemented within an access database (prototype). How would i call an optimizer for this non linear problem and how would I set the constraints? The function to minimize is implemnted in VBA code.
You don’t want to implement the function in VBA; it should be an expression. Go and look at a modelling language. PuLP won’t work for non-linear; I suggest Julia/JuMP or Pyomo. Good luck. Andrew
Dear Andrew,
I appreciate the fact that the opensolver offers various solver engines. However, i just ran into some problems. My model is non-linear, so Couene, Bonmin, NOMAD and NEOS solver engines are the available choices for the opensolver verion 2.9 (advanced).
1) Of all the above, NOMAD appears to give competitive results as Excel Solver. However, I have to run NOMAD again and again to get the right results. I am attempting to use the opensolver for my thesis, but it is obvious that NOMAD appears frustrating.
2) I even download and installed the latest Gurobi optimiser solver engine. But, when opensolver runs it a subscript error is generated. I suppose this arises from the fact that the opensolver uses only the linear aspect of gurobi. Pls confirm.
3) Couene and Bonmin also generated errors. NEOS was too slow over the network and did not perform as expected.
Thanks for your email. Nomad finds local optima and so can require lots of restarts. We only have a linear interface for Gurobi. You need to use non-Excel-specific formulae to use Bonmin or Couenne. Andrew
Thank you for the feedback.
1) I have discovered that the initial values of the decision variables have some impact on the output of the solver engine. Generally, i initiate them at the max values and NOMAD and Excel solver have comeup with the expected results. However, in the course of iteration, NOMAD begins to misbehave. Keeping it at the right track is an issue to be able to locate the global optimum – thus, running it severally. I have one idea : run NOMAD couple of times with different initial values of the decision variables — i will try this now.
2) Moreover, is there anyway i can increase the number of iterations of NOMAD? Maybe this can help to locate in one run the global optima.
3) Furthermore, could you clarify the “non-excel-specific formulae to use Bonmin or Couenne”?
Thanking you greatly for sparing your time to answer the various questions.
David
David,
1) Yes, they do. Running with random starting points is good.
2) More iterations will not help unless it re-starts from a new starting solution.
3) See https://opensolver.org/guide-to-solvers/
Andrew
I’m trying out opensolver for google sheets; thank you! Unfortunately it has the following problem. I manually enter values for the input variables that I know are good. Opensolver reports that it has found an optimum using the GLPK solver but the optimum is worse than the objective value at the input values I manually entered! It seems like at a minimum opensolver should check the initial value of the objective cell and report that the solver failed if the allegedly optimum value is worse than the initial value.
Also it would be nice to have the option for a crude brute force search in parameter space in case you have a nonlinear optimization problem. Even something so simple as choose the step size and range for each variable would be useful.
Assuming your problem is linear, and the numbers are all of similar size, then we guarantee that the answer will be optimal. If it is not, then either the GLPK solver has a bug, or we have a bug. I would double check your answer, please; if you still believe you have a better answer, then please contact us again.
We have a brute force search under development for Excel, as part of a wider tool for exploration. That might meet your need for an enumerator.
Thanks for your feedback; it is always appreciated. Andrew
I’m sure my answer is better because it provides a lower objective value 🙂 My equation may be nonlinear, but that’s not relevant here because GLPK reports that it has a solution which is clearly not the optimal solution. As you know, nearly all programs have bugs. I am suggesting a simple programming check that opensolver can use to help validate the correctness of the solver. I’d be glad to share the spreadsheet with you, just send me an email.
If your model is non-linear, then our solvers are solving the wrong problem, and so the answer is wrong. (We cannot always identify non-linearities.) We’ll add your suggestion to our ToDo list; is another useful check for non-linearity that we don’t do now. Thanks for your feedback, Andrew
Dear OpenSolver Team,
I am really surprised by the great results of your work. I have been using OpenSolver for some time (large Stock Cutting problems) it works with good results.
I also noticed that a large part of the CBC.exe calculation is consumed by process of finding a possible solution. However, in my case, knowing the specificity of my problem, I can easily generate a feasible solution – being a good starting point. The question is whether and how to define the initial solution that will be used by OpenSolver (and later by CBC.exe -mips)?
OpenSolver is able to look at the current solution in the spreadsheet, and if it is feasible, pass it to the solver as a starting solution. However, I don’t think we have this coded up for CBC (but it might be worth double checking this). We do this for Gurobi, so switching to Gurobi is an option. You can check the code for this by searching for InitialSolutionIsValid. We’d welcome any code changes you make to get this working with CBC. Cheers, Andrew
Dear Andrew, many thanks for your valuable hint and for fast response.
The other quick workaround which just came into my mind is to generate the txt file with initial feasible solution, and to use “Extra solver parameters range” option with “mips” switch to CBC and filepath as a value.
However, I will try to implement “mips” function directly in OpenSolver, as you sugested (and to share it).
With BR, /Tomasz
Hi Andrew,
please, I have model in excel, which is linear programming (no MIP or IP). Can I use OpenSolver CBC solver for this? Do I get right result? I ask You, because in definitions it writes that CBC is for (MIP) and I need solver from LP. When I write easy example for CBC solver, it give me a result with decimal numbers in variables.
Hello, can I ask how to still have the solution even though it didnt satisfy the constraint?
for example, I have matrix with supply and demand as a constraint. But the demand is greater than supply so it will never satisfy the model still I want to saw the distribution of this problem so I can make decision. I use google open solver because on excel didnt open at all even though its loaded to the excel.
I’m looking to see if OpenSolver or SolverStudio might be able to solve my investment problem.
I’ve set up an Excel spreadsheet with (6) investment categories, those being: Fixed Rate Mortgage, Variable Rate Mortgage, Kid 1 529 Plan, Kids 2&3 529 Plan, Dad’s 401K, & Mom’s 401K. In another column is the investment income. Each month’s investment income can be divvied up to each investment.
As a large-scale problem I’ve been able to use a (30) day trial of a very expensive commercial Solver to determine the monthly distribution. Takes a couple of hours to optimize but ultimately tells me where to put our monthly investment money.
Nothing against the commercial Solver but I would like to see if OpenSolver or SolverStudio could do the same or better optimize the problem.
Before I invest a bunch of time changing my Excel problem over to be OpenSolver-friendly does it sound like it is within the realm of OpenSolver or perhaps SolverStudio?
It’s been over a year since the (30) day trial expired but if memory serves it was non-linear. I’m using several Excel formulas such as IMPT, PPMT, DAYS, NPER, and FV. I’m assuming I need to get away from those first?
These formulae are not something we can convert (‘parse’) into a modelling language/optimisation form. These are non-linear. NOMAD can handle these Excel-specific formulae, but not the other non-linear solvers. But, there is probably a linear formulation with variables such as x_ij=1 if item is is sold in period j, etc. Hope this helps, Andrew
On google sheets, I open OpenSolver but it won’t let me enter any information. It worked great in the past but I haven’t used it in a month or so and this morning when I went to use it everything was shaded gray and not allowing any info to be entered.
I have to say, I am truly admiring you guys.
I do not use the OpenSolver functions but your tool is to me like a real bible when it comes to understand how to optimise VBA code for excel. I’m currently working on a VBA project for my parents as they use Excel on a daily basis and could use automation in their work. I have to say, the ribbon gives me a headache trying to make it fool proof. I’ve been using your code as a example to help me go through the code wondering how you made it so responsive and fast to load. Having a well working add-in makes a big difference and I thank you.! It is so much better than digging into forums to find what I’m looking for. If you had a advice for a beginner in excel add-ins to achieve the same kind of work you do, what would it be?
Thanks 🙂
Hi.
I need to install 64Bit Excel (Office 2016). Will OpenSolver (2.7.1 or above) work with the 64Bit version? Or do I need to download alternative version of OpenSolver?
The installation directions for opensolver on windows 10 excel 2010 are missing a lot of steps. For example when you copy the files to the addins folder, should you copy the folder created by the unzip program, or the individual files. Once you’ve copied the files it would be helpful to have instructions as to how to install opensolver permanently (i.e. by going to files, options, . . .) Please make it step by step.
Phil, We’d welcome user input to make this process easier to follow. If you had the time to write something up, we’d love to add it to our instructions. By the way, you do not need to install OpenSolver but can just run it from your documents or temp folder. Installation so that Excel always shows OpenSolver is optional, and is one click away using the About OpenSolver box (without copying any files; it will continue to run from its current location). Hope this helps, Andrew. PS: Please never copy just the files; the OpenSolver folders and files need to stay together.
Simple question – how do I stop the solver once it has been running for some time? Where’s the stop button? (MacOS version)
Press Escape (on Windows and hopefully on Mac). It works most of the time… the best Excel offers us.
Hi Andrew and Team,
Many thanks for this fantastic add-in! I use it for tuning/optimising algo trading systems I develop for my personal trading activities. Given the non-linearity of my models I’m pretty much limited to NOMAD, but it performs fantastically. My only wish is that I can do sensitivity and robustness analyses.
Thanks again!
Gary
Hello,
I am new to OpenSolver and I am wondering, if there is a way to use OpenSolver without Excel. As a risk controller of a bank I have the task to integrate an optimization function (minimize the value at risk of a given set of interestrate sensitivities within a set of boundaries). Since this should be done on a database instead of an Excel spreadsheet I need to call the OpenSolver API directly via code.
How can I achieve this?
Cheers Matt
You could move the VBA code out of Excel into Access, but it would be a big job! You do better using a proper modelling language, such as Python/PuLP, and access the database from the Python code. We may be able to provide consultancy assistance if needed. Andrew
Hi Andrew,
thanks for the quick reply. That is indeed not much of a problem, since the code is already implemented within an access database (prototype). How would i call an optimizer for this non linear problem and how would I set the constraints? The function to minimize is implemnted in VBA code.
Thanks,
Matt
You don’t want to implement the function in VBA; it should be an expression. Go and look at a modelling language. PuLP won’t work for non-linear; I suggest Julia/JuMP or Pyomo. Good luck. Andrew
Dear Andrew,
I appreciate the fact that the opensolver offers various solver engines. However, i just ran into some problems. My model is non-linear, so Couene, Bonmin, NOMAD and NEOS solver engines are the available choices for the opensolver verion 2.9 (advanced).
1) Of all the above, NOMAD appears to give competitive results as Excel Solver. However, I have to run NOMAD again and again to get the right results. I am attempting to use the opensolver for my thesis, but it is obvious that NOMAD appears frustrating.
2) I even download and installed the latest Gurobi optimiser solver engine. But, when opensolver runs it a subscript error is generated. I suppose this arises from the fact that the opensolver uses only the linear aspect of gurobi. Pls confirm.
3) Couene and Bonmin also generated errors. NEOS was too slow over the network and did not perform as expected.
Kindly revert,
Thanks.
David
Thanks for your email. Nomad finds local optima and so can require lots of restarts. We only have a linear interface for Gurobi. You need to use non-Excel-specific formulae to use Bonmin or Couenne. Andrew
Dear Andrew,
Thank you for the feedback.
1) I have discovered that the initial values of the decision variables have some impact on the output of the solver engine. Generally, i initiate them at the max values and NOMAD and Excel solver have comeup with the expected results. However, in the course of iteration, NOMAD begins to misbehave. Keeping it at the right track is an issue to be able to locate the global optimum – thus, running it severally. I have one idea : run NOMAD couple of times with different initial values of the decision variables — i will try this now.
2) Moreover, is there anyway i can increase the number of iterations of NOMAD? Maybe this can help to locate in one run the global optima.
3) Furthermore, could you clarify the “non-excel-specific formulae to use Bonmin or Couenne”?
Thanking you greatly for sparing your time to answer the various questions.
David
David,
1) Yes, they do. Running with random starting points is good.
2) More iterations will not help unless it re-starts from a new starting solution.
3) See https://opensolver.org/guide-to-solvers/
Andrew
Is there OpenSolver for Linux?
No sorry. But we would like there to be… Andrew
I’m trying out opensolver for google sheets; thank you! Unfortunately it has the following problem. I manually enter values for the input variables that I know are good. Opensolver reports that it has found an optimum using the GLPK solver but the optimum is worse than the objective value at the input values I manually entered! It seems like at a minimum opensolver should check the initial value of the objective cell and report that the solver failed if the allegedly optimum value is worse than the initial value.
Also it would be nice to have the option for a crude brute force search in parameter space in case you have a nonlinear optimization problem. Even something so simple as choose the step size and range for each variable would be useful.
Assuming your problem is linear, and the numbers are all of similar size, then we guarantee that the answer will be optimal. If it is not, then either the GLPK solver has a bug, or we have a bug. I would double check your answer, please; if you still believe you have a better answer, then please contact us again.
We have a brute force search under development for Excel, as part of a wider tool for exploration. That might meet your need for an enumerator.
Thanks for your feedback; it is always appreciated. Andrew
I’m sure my answer is better because it provides a lower objective value 🙂 My equation may be nonlinear, but that’s not relevant here because GLPK reports that it has a solution which is clearly not the optimal solution. As you know, nearly all programs have bugs. I am suggesting a simple programming check that opensolver can use to help validate the correctness of the solver. I’d be glad to share the spreadsheet with you, just send me an email.
If your model is non-linear, then our solvers are solving the wrong problem, and so the answer is wrong. (We cannot always identify non-linearities.) We’ll add your suggestion to our ToDo list; is another useful check for non-linearity that we don’t do now. Thanks for your feedback, Andrew
Dear OpenSolver Team,
I am really surprised by the great results of your work. I have been using OpenSolver for some time (large Stock Cutting problems) it works with good results.
I also noticed that a large part of the CBC.exe calculation is consumed by process of finding a possible solution. However, in my case, knowing the specificity of my problem, I can easily generate a feasible solution – being a good starting point. The question is whether and how to define the initial solution that will be used by OpenSolver (and later by CBC.exe -mips)?
Thank you in advance for your respose.
OpenSolver is able to look at the current solution in the spreadsheet, and if it is feasible, pass it to the solver as a starting solution. However, I don’t think we have this coded up for CBC (but it might be worth double checking this). We do this for Gurobi, so switching to Gurobi is an option. You can check the code for this by searching for InitialSolutionIsValid. We’d welcome any code changes you make to get this working with CBC. Cheers, Andrew
Dear Andrew, many thanks for your valuable hint and for fast response.
The other quick workaround which just came into my mind is to generate the txt file with initial feasible solution, and to use “Extra solver parameters range” option with “mips” switch to CBC and filepath as a value.
However, I will try to implement “mips” function directly in OpenSolver, as you sugested (and to share it).
With BR, /Tomasz
Thanks; we look forward to seeing your code. Andrew
Hi Andrew,
please, I have model in excel, which is linear programming (no MIP or IP). Can I use OpenSolver CBC solver for this? Do I get right result? I ask You, because in definitions it writes that CBC is for (MIP) and I need solver from LP. When I write easy example for CBC solver, it give me a result with decimal numbers in variables.
CBC also solves linear models with no integer/binary requirements. Andrew
Hello, can I ask how to still have the solution even though it didnt satisfy the constraint?
for example, I have matrix with supply and demand as a constraint. But the demand is greater than supply so it will never satisfy the model still I want to saw the distribution of this problem so I can make decision. I use google open solver because on excel didnt open at all even though its loaded to the excel.
Thank you!
I’m looking to see if OpenSolver or SolverStudio might be able to solve my investment problem.
I’ve set up an Excel spreadsheet with (6) investment categories, those being: Fixed Rate Mortgage, Variable Rate Mortgage, Kid 1 529 Plan, Kids 2&3 529 Plan, Dad’s 401K, & Mom’s 401K. In another column is the investment income. Each month’s investment income can be divvied up to each investment.
As a large-scale problem I’ve been able to use a (30) day trial of a very expensive commercial Solver to determine the monthly distribution. Takes a couple of hours to optimize but ultimately tells me where to put our monthly investment money.
Nothing against the commercial Solver but I would like to see if OpenSolver or SolverStudio could do the same or better optimize the problem.
Before I invest a bunch of time changing my Excel problem over to be OpenSolver-friendly does it sound like it is within the realm of OpenSolver or perhaps SolverStudio?
thanks for your feedback!
I’d have thought this was a linear (possibly binary) model. It should solve using CBC or via NEOS. Hope your get it going. Andrew
It’s been over a year since the (30) day trial expired but if memory serves it was non-linear. I’m using several Excel formulas such as IMPT, PPMT, DAYS, NPER, and FV. I’m assuming I need to get away from those first?
These formulae are not something we can convert (‘parse’) into a modelling language/optimisation form. These are non-linear. NOMAD can handle these Excel-specific formulae, but not the other non-linear solvers. But, there is probably a linear formulation with variables such as x_ij=1 if item is is sold in period j, etc. Hope this helps, Andrew
On google sheets, I open OpenSolver but it won’t let me enter any information. It worked great in the past but I haven’t used it in a month or so and this morning when I went to use it everything was shaded gray and not allowing any info to be entered.
I have not seen this, sorry. Have you tried removing and then adding back OpenSolver? Andrew
I have to say, I am truly admiring you guys.
I do not use the OpenSolver functions but your tool is to me like a real bible when it comes to understand how to optimise VBA code for excel. I’m currently working on a VBA project for my parents as they use Excel on a daily basis and could use automation in their work. I have to say, the ribbon gives me a headache trying to make it fool proof. I’ve been using your code as a example to help me go through the code wondering how you made it so responsive and fast to load. Having a well working add-in makes a big difference and I thank you.! It is so much better than digging into forums to find what I’m looking for. If you had a advice for a beginner in excel add-ins to achieve the same kind of work you do, what would it be?
Thanks 🙂
Hi.
I need to install 64Bit Excel (Office 2016). Will OpenSolver (2.7.1 or above) work with the 64Bit version? Or do I need to download alternative version of OpenSolver?
Br,
TMZ
Same version does 32bit and 64bit Excel on Windows.
Hi!
The installation directions for opensolver on windows 10 excel 2010 are missing a lot of steps. For example when you copy the files to the addins folder, should you copy the folder created by the unzip program, or the individual files. Once you’ve copied the files it would be helpful to have instructions as to how to install opensolver permanently (i.e. by going to files, options, . . .) Please make it step by step.
Thanks!
Phil Troy
Phil, We’d welcome user input to make this process easier to follow. If you had the time to write something up, we’d love to add it to our instructions. By the way, you do not need to install OpenSolver but can just run it from your documents or temp folder. Installation so that Excel always shows OpenSolver is optional, and is one click away using the About OpenSolver box (without copying any files; it will continue to run from its current location). Hope this helps, Andrew. PS: Please never copy just the files; the OpenSolver folders and files need to stay together.