SOLVER does not change inputs!

Good day everyone.

As part of a portfolio strategy, I need to determine the optimal weights in order to maximize the sharpe ratio.
In order to do that, I want to make use of SOLVER.
When I do this, the SOLVER acts like it's giving a solution but nothing changes to the portfolio weights.
Does this have something to do with the "complexity" of some functions on which the sharpe ratio is based?
These are partly based on IF functions.

Please find my excel file attached.
The sheet to look at is "Momentum Signals".
There you can find the weights and sharpe ratio.
What I did to maximize is:
- set objective: $H$3
- by changing variables cells: $C$3:$F$3
No matter what solving method I choose, it does not solve!!

I would greatly appreciate any help. Thanks

Originally Posted by MrShorty

Digging a little deeper, I think the main source of the "discontinuous" nature of the problem is in the Positions tab, where you have a big IF(AND(...))))) function (that looks like it should reduce down to a simple VLOOKUP() function with the 4th argument set to TRUE, if you are interested). I replaced this function with a linear interpolation function (how many times have I wished Excel had a nice built in linear interpolation function) to make this step in the algorithm "continuous". This change allowed me to use Solver to maximize the Sharpe ratio by changing the weights.

My advice would be to reformulate your algorithm (in particular, the positions tab) so that the computation is not a step function.

VLOOKUP would not be doing the job as I needed to check if values were in certain intervals.
Right? Or how would you use VLOOKUP here
Thxx for helping out!

I can get solver to "react" with a setup like this but ....

asolv1.jpg

asolv2.jpg

asolv3.jpg

.... but the max value for H3 is still 73.31% so I really don't know if this is of any help to you. Ticking the box marked "Use Multistart" and "Required Bounds on Variables" was what I needed to get solver going.

Alf

Yes, apparently adding a restriction reduces the range of possible values that SOLVER has to consider, which enables it to find a solution.
Even without using Multistart or Required Bounds on Variables.
Thx!

Originally Posted by MrShorty

VLOOKUP() with the 4th argument set to TRUE (rather than FALSE like almost all of the examples out there) does an excellent job of checking if values are in certain intervals. Steps to replacing that big IF() function with VLOOKUP()

1) VLOOKUP(value,table,column,TRUE) only works when the lookup values (left column) in the lookup table are in ascending order. Select W7:Y14 and sort the lookup table in ascending order by column X. Note that column X will be our lookup values.
2) The formula in Positions now becomes =VLOOKUP('momentum signals'!B12,'momentum signals'!$X$7:$Y$14,2,TRUE) (or something like that). Copy down and across.

But, VLOOKUP() is still a discontinuous "step" function, so this replacement is not going to help with the overall problem of getting Solver to find the maximum Sharpe ratio.

So you would advise me to reformulate my algorithm by using linear interpolation? No idea how that works

Originally Posted by MrShorty

1) VLOOKUP(value,table,column,TRUE) only works when the lookup values (left column) in the lookup table are in ascending order. Select W7:Y14 and sort the lookup table in ascending order by column X. Note that column X will be our lookup values.

When I try to sort that, it refers me to cells in other sheets (because of references).
How can I make Excel ignore those references?

Thanks for helping out!