how to force solver to use only a certain number of variables

[airone12]

hi there,
i'm trying to use excel solver to optimize a portfolio.
the problem is that i am not able to model the problem correctly: it maximizes the expected return of the portfolio using all the variables, but i would like to use not all the 234 stocks i listed but 10 at max.
any idea how to force him to produce solutions that use only a subset of the variables available?
thanks in advance

 

[Eric W]

Welcome to the forum.

This depends on how you have the sheet set up. It sounds like you have 234 variables set up, and all 234 are entered in the solver as variables. And you're looking for solutions that require a maximum of 10 changes to those variables. If so, you might try duplicating those 234 cells somewhere else, then adding a formula that counts how many changed, something like =SUMPRODUCT(--(A1:A234<>B1:B234)) then adding another constraint in the Solver setup requiring that cell to be <10.

I can't swear that this will work, but I think it's worth a shot. Hope this helps.

 

[airone12]

thanks for your reply.
actually i tried something like that, but it does not work: the solver stops itself at a suboptimal solution which also breaks the constraint... i think that this kind of constraint is so not-linear that in an already not linear contest breakes the solver capacity to find a local maximum/minimum... i hope i was able to explain the mess in my head

 

[Eric W]

Hmm. After writing the first paragraph, I actually realized that what you said might happen. Which is why I went back and edited the post, adding that line that temporized a bit.

It actually seems that you might need some multi-variate analysis, and selectively remove the variables with the smallest impact. Maybe you could run the analysis with all 234 variables (I don't know how long that would take), then identify the 10 variables with the largest change, then rerun it letting only those vary.

Just throwing out some ideas, if any light bulbs go off, I'll let you know.

 

[airone12]

Hmm. After writing the first paragraph, I actually realized that what you said might happen. Which is why I went back and edited the post, adding that line that temporized a bit.

It actually seems that you might need some multi-variate analysis, and selectively remove the variables with the smallest impact. Maybe you could run the analysis with all 234 variables (I don't know how long that would take), then identify the 10 variables with the largest change, then rerun it letting only those vary.

Just throwing out some ideas, if any light bulbs go off, I'll let you know.

ok, i'll think about it, i'll let you know if something works.
thanks again