Solver Problem

**beckham-7** · 10-20-2017, 03:54 PM

Hi all

I have set up a backtesting of a trading strategy where based on two values a buy or sell signal is triggered. Those values are a simple moving average over several days of another basis value. I know would like to find the optimal number of days for the moving averages. This does not sound to difficult as a solver problem, but I set it up like: "If both moving averages are negative, then go long, if both moving averages are positive, then go short, if the are either long and short, do nothing". Please find the example attached. Is there any possibility to use the microsoft solver to maximize the value in cell I16 by changing E3 and G3?

Many thanks for your help in advance

**MrShorty** · 10-20-2017, 05:49 PM

Short but useless answer -- Yes, I am optimistic that someone with the right skill set could come up with a Solver model that would solve this problem. I am not one of those people, though

As a quick test, I found the answer by using a data table. Recognizing that the inputs for those moving averages must be integers, I set up a 2 variable data table with 1 to 6 for each moving average (Could do more, but that seemed like a good test). Limiting the inputs to 6, it looks like the max is when E3 is 3 and G3 is 6 (E3=3, G3=1 is a very close 2nd place). That approach was easier for me.

Analyzing the "shape" of your objective function using this data table, the function looks like it has some significant ups and downs that may not follow a good pattern that an algorithm like Solver can follow. The only rigorous algorithm might be a "try every possible combination", which I find is more difficult to program into Solver than using a strategy like the data table above (but that could just be me).

Are you required to use Solver?

ETA: links to pages describing the use of data tables:
http://www.excel-easy.com/examples/data-tables.html
https://support.office.com/en-us/art...2-77542a5ea50b

**Alf** · 10-21-2017, 01:59 AM

For what it's worth I've set up a solver model using the Evolutionary engine and as far as I could tell this engine is also available for Office 365.

As this is a solver engine I'm not familiar with I have no idea if the answer found by solver is the optimal one.

I've also set cells E3 and G3 to be integers in the uploaded file. Removing that constraint will still give the same max value for cell I16 but with slightly different values for E3 and G3 as shown in image.

evolutio_sol.jpg

Alf

**beckham-7** · 10-21-2017, 03:09 AM

Hi MrShorty, thanks a lot for the quick reply and your solution. I did the same but as the real model has 6 parameters with each 20 - 100 different values that could possibly be the best fitting solution, it not only takes time but my computer reaches is capacity (different story...). As i did it, it took me several thousand columns with daily data back from 2004... So, I could do that or find a nice and quick solver solution where I can additionally take in several restrictions (e.g. maximum volatility accepted, etc). If anybody has a solutions, much much appreciated

**beckham-7** · 10-21-2017, 03:25 AM

Well Alf, you are definitely my hero and saved my weekend. Wow, I can't tell how much I appreciate that!

**Alf** · 10-21-2017, 07:30 AM

You are welcome and thanks for feedback

As I said before I'm not familiar with the how and the why of this solver engine so I would recommend you playing around a bit with different settings under "Options" for the Evolutionary solver to see if you can improve the result.

I've changed the "Maximum time without improvement" from 25 seconds to 5 seconds and still got the same answer.

Alf

Ps If this solves your problem don't forget to mark your thread "Solved" as per forum rules.

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

**Alf** · 10-21-2017, 03:23 PM

There seems to be a problem I got an email about but can't see in thread.

Hi, thanks a lot for the quick reply and your solution. I did the same but as the real model has 6 parameters with each 20 - 100 different values that could possibly be the best fitting solution, it not only takes time but my computer reaches is capacity (different story...). As i did it, it took me several thousand columns with daily data back from 2004... So, I could do that or find a nice and quick solver solution where I can additionally take in several restrictions (e.g. maximum volatility accepted, etc). If anybody has a solutions, much much appreciated

My suggestion is that you go to "Frontline Systems" found at "https://www.solver.com" and registrer then you can download their most advanced model for free and test it for free during 15 days.

This advanced model should probably be powerful enough to solve your problem if your PC is up to it.

There is also an app for Office 365 where you can go on line and run run solver in the cloud but I have no idea how powerful that solver version are.

Alf

Solver Problem

LinkBack

Thread Tools

Rate This Thread

Display

Solver Problem

Re: Solver Problem

Re: Solver Problem

Re: Solver Problem

Re: Solver Problem

Re: Solver Problem

Re: Solver Problem

Thread Information

Users Browsing this Thread

Similar Threads

solver [This iterative problem doesn't always work with Solver; can it be solved with VBA]

Problem Using Solver with an optimization stock problem

Problem with the solver

Interesting Solver problem (Solver encounters an error)

solver problem

IF problem using Solver

[SOLVED] IF problem using Solver

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions