Skip to content

Open source replacement for Solver in Excel

Andrew Mason, who I got to know very well during my year in Auckland, has put together an open source Excel add-in that extends Excel’s built-in Solver (a product from Frontline Systems) and replaces the underlying optimization engine with the optimization code CBC from COIN-OR.  OpenSolver allows the solving of models without limits to the number of variables or constraints.  Andrew has also provided some enhancements including a model visualizer and the capability for fast reoptimization.

OpenSolver works with linear and (mixed-)integer programming models, but not with nonlinear models.  So keep those @IF and @POWER functions away from it!

This add-in a great illustration of both the strengths and weaknesses of open source.  On the plus side, all the work that people put into CBC can now find a much wider audience through the efforts of someone else.  No coordination was needed:  people like Andrew are able to use their own creativity and drive to put together something useful.

However, it is important to note that there are different open source licenses, and that Andrew has licensed this under GPL, while most of COIN-OR is licensed under CPL/EPL.  For most, this licensing difference is not of importance, but any work that is derivative of Andrew’s work can only be distributed under GPL.  Trying to figure out what is a derivative work is beyond me, but is important to those who would like to further develop systems based on Andrew’s work.  I note this fully respecting Andrew’s right to pick whatever license works best for him!

For me, this will be great to point to in class:  MBA student’s love Solver, but get frustrated with size limits.  I can now point to an easy to use add-in that removes those limits without requiring any changes in models.

{ 8 } Comments

  1. Joao | July 7, 2010 at 7:41 am | Permalink

    The GPL basically states that derivative work, if distributed, must be free and open source. “Free”, in this context, stands for the liberties granted to other developers to modify, distribute and even sell your software, potentially without any modifications.

    Not only don’t I see a problem with choosing this license, but I believe it’s Mason’s implicit duty to do so for a project originated in the academia, where educational purposes should permeate all endeavors.

  2. Larry (IEOR Tools) | July 7, 2010 at 8:53 am | Permalink

    OpenSolver is really a good idea. I’m excited to give it a try. There should be alternatives for optimization with Excel.

    As far as the licensing I agree with Joao. GPL has four axioms that must be true for any GPL software. This is from FSF.org which publishes the GPL.

    Freedom 0: Freedom to run the program
    Freedom 1: Freedom to study how the program works, and change it to make it do what you wish
    Freedom 2: Freedom to redistribute copies so you can help your neighbor
    Freedom 3: Freedom to distribute copies of your modified versions to others

  3. Tallys | July 7, 2010 at 9:15 am | Permalink

    This is great! I’ll definitely give it a try and hopefully use it next time I teach my MBA Optimization class. Thanks for posting this!

  4. Paul Rubin | July 7, 2010 at 11:38 am | Permalink

    I’m no expert on open source licenses (and in fact I get headaches every time I think about them), but I’m pretty sure that a consequence of OpenSolver being GPLed is that if someone builds an application using it — say a prefab spreadsheet template for some specific problem — and then tries to distribute the application, the application has to be open-sourced under a license no more restrictive than GPL. IIRC the COIN-OR licenses are explicitly chosen to allow COIN-OR software to be used in proprietary products.

  5. Larry (IEOR Tools) | July 7, 2010 at 1:16 pm | Permalink

    Paul, I think you are right. CPL/EPL was specifically created with the business enterprise community in mind. GPL is more of an ideal or philosophy.

    I’m not really sold on one license or the other. I think Michael said it best with the advantages of open source is that a wider community will get to use this software.

  6. Paul Rubin | July 7, 2010 at 2:04 pm | Permalink

    Larry, I agree, at least to point that any open source license is better than none (and I certainly applaud the effort here). Here’s one possible issue, though. Suppose someone decides to write an OR or optimization text book (perhaps targeted at business students) and wants to bundle Excel spreadsheets and OpenSolver. I’m fairly certain that the spreadsheets are considered derivative works under GPL, which would mean that the author has no copyright protection against authors of other books using them. That’s possibly not a big deal — I’m pretty sure textbook authors “borrow” problems from other books all the time. A better question is whether, by bundling the spreadsheets with the book, the book itself becomes a derivative work (which I think would eliminate any copyright protection).

    Which is why OSS licenses give me headaches.

  7. Bo Jensen | July 8, 2010 at 4:40 am | Permalink

    Paul, I agree OSS licenses also gives me a big headache. For some people it is hard to grasp why OSS for optimization software is not used on a wider scale in industry. But from my experience, companies are SO scared to get a law suit. Until some one makes a good optimization package under a license like boost, then I mostly see OSS optimization benefit academia. Which in some sense is a bit sad, but good for us who live on developing commercial optimization software :-)

  8. Andrew Mason | July 12, 2010 at 10:16 pm | Permalink

    Thanks for the comments; it’s great to have all this interest in OpenSolver. (And thank you, Mike, for blogging about it.) I’ve had a few discussions with Ted Ralphs (COIN-OR) and Stu Mitchell (maintainer of the COIN-OR PULP project) about the choice of license. If anyone finds that the GPL is stopping them using OpenSolver, then please let me know. Other choices are definitely feasible.

    I would welcome feedback on how you are using OpenSolver, any bugs you find and any suggestions you have for improvements.

{ 3 } Trackbacks

  1. Blogged by Mike Trick « OpenSolver | November 13, 2010 at 12:45 pm | Permalink

    [...] Mike Trick By admin, on July 13th, 2010 Mike Trick, who visited New Zealand a few years ago, has blogged about OpenSolver. This has generated an interesting discussion about the GPL license, and whether this will limit [...]

  2. [...] codes from COIN-OR into an easy-to-use interface in Excel.  It is a fantastic piece of work (that I blogged about previously) and one that I believe does a tremendous amount of good for the world of operations research.  If [...]

  3. COIN-OR Cup | Charlotte 2011 | November 13, 2011 at 6:00 pm | Permalink

    [...] codes from COIN-OR into an easy-to-use interface in Excel.  It is a fantastic piece of work (that I blogged about previously) and one that I believe does a tremendous amount of good for the world of operations research.  If [...]