Does anyone know a better alternative to MS Excel's Solver?

Question

My company has to crunch a lot of data and part of the process involves running the solver and plotting a graph through resulting data points. Obviously there is a lot of copy and paste involved and the whole process is shaky, error prone and all round cluster-fudge. I was wondering if there was an alternative to the solver that can be used so that even if we have to use excel to plot the final graph, there will be a lot less data that needs to be copied and pasted back and forth. It would be great especially if the tool could be easily integrated into a .NET application but I am open to suggestions that may require a little bit of code-fu to get this to work.

Thanks!

Answer 1

gretl, the Gnu Regression Econometrics and Time-series Library, does the sort of things you need, and is highly efficient and well documented. I doubt there is a .NET API, but you can control it via scripting if necessary.

Answer 2

The solver is essentially an iterative technique used to find roots of functions. Depending on the particular form of the function that you are trying to find a root of, you can roll your own or use existing implementations of the bisection method or Newton's method (or many other iterative root-finding techniques). If you post more about the specific problem that you are trying to solve, I might be able to point you to a more specific technique. Alternatively, you can read about root-finding and look at the Extreme Optimization library.

Answer 3

Netlib offers a comprehensive list of open-source solvers for various purposes. You may find some that you like.

Answer 4

I've automated the solver interface for different projects. Have you considered some-sort of VBA macro? This support article from MS goes through the whole VBA interface: http://support.microsoft.com/kb/843304. I'd assume it's accessible through .NET as well.

Answer 5

Check out Microsoft Solver Foundation they provide .Net libraries to solve different kind of optimization problems http://code.msdn.microsoft.com/solverfoundation

You can integrate it into your code very easily

Answer 6

OpenOffice has a new nonlinear solver plugin which uses completely different methods of finding solutions to optimization problems than the steepest-descent methods used in Excel's solver.

From your description it sounds like your data has a lot of outliers. For that you need some sort of robust fitting.

Matlab is the best program to do all these things, and it can be linked from .NET

Answer 7

One of the MAJOR BUGS in the Solver/Excel VBA interface is not visible until you take your Excel Spreadsheet (which references the Solver Interface) to another machine.

There you will get an error that basically says that the Solver add-in is not loaded. You must go in to VBA and into Tools, References and re-establish the reference to the Solver library.

VERY ANNOYING.

Answer 8

You might like to take a look at this, I haven't looked too closely.

Eureqa is a software tool for detecting equations and hidden mathematical relationships in your data. Its primary goal is to identify the simplest mathematical formulas which could describe the underlying mechanisms that produced the data. Eureqa is free to download and use.

Answer 9

You might be interested in the open source OpenSolver we have developed for Excel; it's available from http://www.opensolver.org. This uses the free COIN-OR CBC solver, and can solve linear and integer problems (but not non-linear models). It installs as an Excel add-in. It can solve much larger problems, and typically does so faster than Solver. Hope you find it useful.