SolverStudio & Pyomo – SolverStudio for Excel

We are excited to announce that SolverStudio now supports developing and solving models developed using the Pyomo Python modelling environment. To quote the Pyomo web site:

Python Optimization Modeling Objects (Pyomo) package is an open source tool for modeling optimization applications in Python. Pyomo can be used to define symbolic problems, create concrete problem instances, and solve these instances with standard solvers. Pyomo provides a capability that is commonly associated with algebraic modeling languages such as AMPL, AIMMS, and GAMS, but Pyomo’s modeling objects are embedded within a full-featured high-level programming language with a rich set of supporting libraries. Pyomo leverages the capabilities of the Coopr software library, which integrates Python packages for defining optimizers, modeling optimization applications, and managing computational experiments.

To use Pyomo under SolverStudio, you need to install both Python and then Pyomo; please see the Pyomo install information. (In the future, we hope to run Pyomo directly within SolverStudio using its internal IronPython. It is great to have support from the COOPR/Pyomo team to help make this happen.)

To install and test Pyomo with SolverStudio:

Make sure you have Python installed in your machine. If not, you can download Python from https://www.python.org/download . (Note that you need CPython, not the IronPython that is built into SolverStudio.)
Run the pip.exe installer in your Python Scripts folder, as detailed in these instructions.
Download and install SolverStudio
Launch Excel
To open the Pyomo Demo:
1. In Excel, select the Data menu tab. Under the SolverStudio menu, choose Examples then Pyomo Examples.xlsx.
2. Choose one of the model sheets, and click Solve to run your new Pyomo SolverStudio solver.

You may wish to visit the main the Pyomo site, the older COOPR/Pyomo site, seek help on the Pyomo forum, or file a Pyomo bug report.

Pyomo 4.1.10527 SolverStudio Incompatibility (Sept 2015): As detailed in this Pyomo Forum post, Pyomo 4.1.10527 removed the –json option that SolverStudio uses, leading to the following error (as reported here – thanks mbanco):

## Running: C:\Python27\Scripts\Pyomo.exe --solver=cbc  --save-results="C:\Users\amas008\AppData\Local\Temp\SolverStudio 2pdv1xei\Sheet" "C:\Users\amas008\AppData\Local\Temp\SolverStudio 2pdv1xei\model.py" "C:\Users\amas008\AppData\Local\Temp\SolverStudio 2pdv1xei\SheetData.dat" --json
WARNING: converting to the 'pyomo solve' subcommand
usage: Pyomo-script.py solve [options] <model_or_config_file> [<data_files>]
Pyomo-script.py solve: error: argument --json: expected one argument
## Error: The Pyomo output file did not contain any valid data.

The Pyomo development team has released a new version, Pyomo-4.2.10784, on 21/9/2015, which reinstates this –json option (thanks Bill). This restores compatibility with SolverStudio.

21 thoughts on “SolverStudio & Pyomo”

Manuel - September 23, 2019 at 12:02 am

Hi, could you help me on how to report the sensitivity analysis in solverstudio. Thanks

Andrew Mason - September 24, 2019 at 11:05 pm
Assuming you are using Pyomo, you need to look up their documentation to find the Python sysntax. You can then copy the sensitivity results into data items so they show on the sheet. Andrew
Reply

Augusto Ferraro - August 24, 2016 at 2:57 pm

Hi, I'm having trouble with defining the relative gap tolerance. how can I do it? I've already set the time limit writing "solver-options | timelimit=3600" in the pyomo options' table. thanks in advance

Andrew Mason - August 24, 2016 at 9:24 pm
You need to find the Pyomo option to set the gap (which I don't remember, sorry), and then add it to the PyomoOptions defined range (as shown for another option in the "Transportation-Pyomo" example sheet). Andrew
Reply
- Augusto Ferraro - August 26, 2016 at 12:41 pm
  Thank you Andrew. I've found that I should write "options.parameter2009=0.05" somewhere to get a 5% relative gap, but I dont know in which column I should write it and what should I write in the other one. I tried with "solver-options" but it didnt work. I get an error executing the model code. Any help will be truthfully appreciated. Augusto.
  Reply
  - Andrew Mason - August 29, 2016 at 6:24 pm
    You can use Python commands to set your Pyomo solver options; see Section 18.12 of the Pyomo documentation. Hope this help. Andrew
    Reply

Sam - October 27, 2015 at 10:21 am

I am testing pyomo with gurobi. But it fails with error C:\Python27\Scripts\Pyomo.exe --solver=Gurobi --save-results="C:\Users\sam.walker\AppData\Local\Temp\SolverStudio knxky2fg\Sheet" "C:\Users\sam.walker\AppData\Local\Temp\SolverStudio knxky2fg\model.py" "C:\Users\sam.walker\AppData\Local\Temp\SolverStudio knxky2fg\SheetData.dat" --json The SolverFactory was unable to create the solver "Gurobi" and returned an UnknownSolver object. This error is raised at the point where the UnknownSolver object was used as if it were valid (by calling method "config_block"). The original solver was created with the following parameters: type: Gurobi _args: () options: {} ## Error: The Pyomo output file did not contain any valid data. ## Pyomo did not complete; no solution is available. The sheet has not been changed. ## Done When I take the command line and change it to lower case --solver=gurobi , it runs ok. So, is it just a small bug?

amas008 - October 28, 2015 at 9:10 pm
Thanks for the feedback. It looks like Pyomo requires solver names to be given in lower case. You can manually set the solver (with the name "gurobi") by following the example in "Pyomo Examples.xlsx : Transportation-Pyomo" where we define a named range "PyomoOptions" with an entry of "solver" and "gurobi". We will fix this capitalisation issue in the next SolverStudio release. Sorry for the hassle, but I hope this gets you going. Andrew
Reply

Alvi - May 25, 2015 at 1:12 pm

I am getting an error TypeError:expected index value,got str when I am running pyomo optimization problem from Solverstudio. I am a novice in using this and anyhelp will be greatly appreciated from coopr.pyomo import * model = AbstractModel() #defining Sets model.t=set() model.budget=Param(initialize=30000) #Defining Variables model.Newspaper=Var(model.t,bounds=(0,1000),within=NonNegativeReals) model.BSCS=Var(model.t,within=NonNegativeReals) model.DDTB=Var(model.t,within=NonNegativeReals) model.SEM=Var(model.t,within=NonNegativeReals) model.DBIMP=Var(model.t,within=NonNegativeReals) #Constraint def totalConstraint(model): return sum(model.Newspaper[k]+model.BSCS[k]+model.DDTB[k]+model.SEM[k]+model.DBIMP[k] for k in model.t)==model.budget model.totconstraint=Constraint(rule=totalConstraint) #Objective Function def maximizeconversion(model): ans=0 for i in model.t: ans=ans+model.Newspaper[i]+model.BSCS[i]+model.DDTB[i]+model.SEM[i]+model.DBIMP[i] return ans model.SolverResult=Objective(rule=maximizeconversion,sense=maximize) have already defined t in edit data option as a data range with values from 1 to 30.I am just experimenting with solverstudio and the constraints as such might not make any practical sense now.

amas008 - May 25, 2015 at 8:07 pm
Thanks for your interest in SolverStudio. Is this an error reported by SolverStudio in a pop-up dialog, or a Pyomo-reported error shown in the text output window? Andrew
Reply
- Maya - January 3, 2016 at 11:05 pm
  I'm getting the same TypeError, and it's in a pop-up dialog. Any help greatly appreciated!
  Reply
  - amas008 - January 14, 2016 at 5:20 am
    Can you please tell us what versions of SolverStudio, Pyomo and Python you are using? Perhaps you could also email the workbook to [email protected] Sorry for this hassle; we try to work across a wide range of scenarios whenever possible. Andrew
    Reply

Moataz Eweda - September 27, 2014 at 11:18 am

I have installed Python 2.78 & Coopr_3.4.7842 & while trying solving coopr example I receive following [ 0.00] Setting up Pyomo environment [ 0.00] Applying Pyomo preprocessing actions [ 0.00] Creating model [ 0.03] Applying solver [ 0.09] Processing results Number of solutions: 1 Solution Information Gap: 0.0 Status: optimal Function Value: 15.58 [ 0.17] Applying Pyomo postprocessing actions [ 0.17] Pyomo Finished ## COOPR did not complete; no solution is available. The sheet has not been changed. ## Done

amas008 - September 28, 2014 at 9:21 pm
Sorry to hear it is not working for you. I have checked, and all the COOPR examples provided with SolverStudio work fine for me using Coopr 3.4.7842 (CPython 2.7.5 on Windows 7). I doubt that the slightly different Python would have any impact. What example are you trying to solve? Note that if you choose File... View Working Files, and then open the Sheet file (using a text editor), you will see what was produced by COOPR when solving your model. Andrew
Reply

Sara - April 16, 2014 at 11:29 pm

Hello Andrew, Im trying to use SolverStudio and COOPR ( or other languages) for a linear optimization model. Before trying to learn how to use COOPR and SolverStudio I wanted to know if it would have the capability of solving 2,000,000 variables and about 1,000,000 constraints. Do you have any suggestions regarding what to use for my problem? Thank you

amas008 - April 17, 2014 at 1:09 am
1 million constraints is a large problem; I don't know how any of the modelling languages would handle that sized problem. What are the 2 million variables - what do they represent? Is this an integer programme?
Reply
- Sara - April 17, 2014 at 5:48 pm
  It is a maximization linear model. the variables are binary variables, which refers to having and not adding an item to a portfolio. The reason for the large number of variables is that my binary variables have 6 dimensions and the total number of variables is the total permutations that they can take. Any suggestion would be extremely helpful. I can't find useful info on the limitations of each of these solvers yet.
  Reply
  - amas008 - April 18, 2014 at 6:23 pm
    There are few limitations on these solvers other than memory and time. Predicting how much of these you will need is very hard, which is why you will not find any clear problem size specifications given. I suggest you test your problem using a smaller sized experiment, perhaps trying out AMPL on NEOS or PuLP with CBC. I look forward to hearing how you get on. Andrew
    Reply
    - Sara - April 18, 2014 at 10:02 pm
      I have attempted to solve my problem with OpenSolver( Which I think uses CBC solver, please correct me if I'm wrong) and after a certain expansion I made to the model. The cbc ran for a 4 days and didn't stop when I limit the time to make sure it ends it returns fractions instead of binary variables. So if I use PULP and then CBC would it be the same story? The same solver power?
      Reply
      - amas008 - April 18, 2014 at 11:22 pm
        Yes, OpenSolver uses CBC. PuLP can use CBC and other commercial solvers. Using PuLP with CBC should give the same answer as OpenSolver, so it won't really help you. The commercial solvers can be much faster, so worth trying if you can find someone with one. You can also use Gurobi on NEOS with AMPL; see http://www.neos-server.org/neos/solvers/milp:Gurobi/AMPL.html Hope this helps, Andrew
        Reply

Pingback: INFORMS SolverStudio Presentation MD08 « SolverStudio for Excel

21 thoughts on “SolverStudio & Pyomo”

Leave a Reply Cancel reply