Problem solver-function

Hello everybody!

Am trying to optimize an investment strategy by the use of the problem solver-function. Problem is that Excel seems to be unable to handle that much information.

There are two different requirements for trade: Moving average (x) > moving average (y) and barometer > z... So basically I am trying to find x,y,z so that the investment will yield the highest profit - but seeing as there are three variables and the time series go back to 1994, Excel can't manage the job (Problem solver)...

What to do?

Maybe I should simplify the question: Is there any alternative, when too many variables and/or data makes it impossible to use the problem solver-function?

Moving in scientific circles rather than financial circle, maybe I shouldn't even comment (I guess I will anyway).

What makes you think you are up against some kind of arbitrary limit built into Excel's Solver? According to Micorosoft and Frontline (the suppliers of the Solver add-in), the only limitations on the built in Solver add-in are the number of adjustable cells in the "by changing" (200) and the number of constraints (500 to unlimited) (http://support.microsoft.com/kb/75714 http://www.solver.com/standard-excel...em-size-limits)

It seems that most of the time the reason someone will have trouble getting a solution to a problem is because they have programmed the model into the spreadsheet in a less than optimum fashion. I obviously cannot know how familiar you are with Solver's algorithms (usually the Newton Raphson method unless another alternative was selected). I find that a basic understanding of the desired algorithm can help in setting up the spreadsheet to get the best shot at a solution, or to know when that algorithm is not suitable for the problem (example: NR algorithms do not like step functions). Perhaps a better understanding of your problem and Solver's algorithms would allow you to set the spreadsheet up differently and that will allow you to find a solution.

Is there any alternative, when too many variables and/or data makes it impossible to use the problem solver-function?

Almost certainly. Easiest possibility might be to upgrade to one of Frontline's Solver upgrades (not free). One of these packages may have a suitable algorithm with fewer limitations than Excel's built in Solver. This option will work if you really are up against the limitations of the built in Solver or if the built in algorithms are unsuitable for the problem.

Any other possibility is highly unlikely to be an out of the box solution, and could even require some significant programming to implement in Excel. Unless you are on the "cutting edge" of financial algorithm development, I expect someone in the field has discussed how these kind of calculations should be performed, and probably even developed software for it. Some research might be called for to determine how these kind of calculations are expected to be performed.