Solver using VBA for portfolio optimisation

Perhaps you should start by upoading a model if you wish somebody to help you?

Alf

Hi FDibbins,

Thank you and I wasnt aware and I apologise for the cross post.

regards,
Natalie

Not sure I understand your problem properly but to just set up a macro that runs the solver model you have in sheet "MeanVar_MP" this should do

Please Login or Register  to view this content.

But I think you need to run solver in a loop chewing on all the colected data on this sheet i.e. columns C, D and E perhaps if so I need more information.

Before you can use Solver with a VBA macro you must set a reference to solver in Visual Basic. Click on the "Developer" tab -> click "visual basic" in the new windows that opens click "Insert" and then "Module" Paste "SolvRun" macro in this "window". Then go to "Tools" - "References" and find "Solver" tick box in front of solver now you can run solver using a macro.

I would also recomend you search the Forum with Solver as the search string perhaps you may find a thread that may be of help.

Alf

Hi Alf,

I have followed according to your instructions. however, when i run the VBA code in a new window, it seems to give me a different answer from the answer i obtained when i click on Solver manually.

As for the looping, for example, in order to obtain the weights for Jan-98 ('sheet MeanVar_MP; cell 04:Q4), i used the figures from the ('inputs' sheet, array E5:G52') Jan -94 to Dec-97 brought forward into ('sheet MeanVar_MP;array C2:E49) and then I use solver to maximise the sharpe ratio (cell I18) with constraints of weights(sum=H5:J5) =1.

Subsequently, to obtain the weights for Feb-98 ('sheet MeanVar_MP; cell 05:Q5), i used the figures from the 'inputs' sheet, array E5:G52' (Feb -94 to Jan-98) and repeat by maximising using solver.

I will continue to obtain weights up to Dec-13 ('sheet MeanVar_MP; O195:Q195) using figures from the 'inputs' sheet, array E196:G244 ' (Dec-09 to Nov-13).

Appreciate your help.

Regards
Natalie

it seems to give me a different answer from the answer i obtained when i click on Solver manually.

If I assume that the values in sheet “MeanVar_MP” cells H5:J5 and I18 are the result of a solver run in your uploaded file so I would expect these values to stay the same if I did a manually rerun of solver.

But a rerun of solver changes range H5:J5 from “0.28862, 0.3577494, 0.353634”
to “0.361779, 0.638221, 0”

And I18 goes from “0.1340433” to “0.207819” and these results are the same as I get when I’m running the macro.

A solver solution may differ depending on the solver “starting point” and looking at you model I see that you have not ticked the box “Use Multistart”. Don’t know if this something you should test I’ll add a link explaining the workings of this then you can decide.

http://www.solver.com/content/basic-...al-solution-17

As for looping solver I assume you wish to update range “O4:V??” with solver results but I can’t see how you change solver settings to get different results.

As an example of setting up a solver loop I'll add this link.

http://www.excelforum.com/excel-prog...ml#post3205531

Alf