Need Macro to repeat Solver for several scenarios

[CharlotteFI]

Hello there-

As the title explains, I am trying to come up with a macro that will run solver for me multple times across several columns. As you can see in the attachment, I am running solver for 99 different portfolio possibilities. I have solved for Column B, C and D by running solver repeatedly by hand but would like to make this process automated since I'll need to do this 100+ times for just this spreadsheet (many more!). Everything in here is fake so if some formulae doesn't seem all that logical, it's ok - just a test sheet!

Starting in column C, going to CW, I am running solver as such:

Set Objective: $D$23
By Changing Variable Cells: $D$17:$D$19
Subject to the Contraints:
$D$17:$D$19 >=0
$D$20 = 1
$D$22 = $D$21

Can anyone help with the VBA to make solver re-run itself from columns C all the way to CW - effectively changing the portfolio weights in rows 17-79 for each column? Unfortunately I'm new to the VBA arena. Much appreciated!

CharlotteFI

[CharlotteFI]

EDIT: Solver criteria obviously changes with each column. I realized that I used criteria for column D when I notated "Starting in column C" - it should have said "starting with column D" Thanks!

[Alf]

Try this macro ans see if it works the way you wish.

Please Login or Register  to view this content.

Alf

[CharlotteFI]

Try this macro ans see if it works the way you wish.

Please Login or Register  to view this content.

Alf

Alf - this is fantastic! Thank you so much. The only issue (which I may be able to solve on my own) is that of of the constraints is that row 20 must sum to 100% for each column (no leverage). Any thoughts? Thank you again!

[CharlotteFI]

Try this macro ans see if it works the way you wish.

Please Login or Register  to view this content.

Alf

Fixed. I just changed:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks again for your help! Very much appreciated!

[Alf]

QUOTE] is that of the constraints is that row 20 must sum to 100%[/QUOTE]

Strange as the solver command is set so that the value of row 20 should be 1 (format must of course be set as %)

Please Login or Register  to view this content.

Where "Cells(20, i)" is row 20 and i is column number going from 3 (C column) to 101 (CW column)

Will have a look at it to morrow.

Alf

[Alf]

Glad you fixed it. Since this seems to solve your problem could you please mark the thread "Solved" and if you like click on the small star in my post (bottom lift) and give a rating to my "efforts"

Alf

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

[CharlotteFI]

I agree that is odd. "100%" or "1" should yield the same result. Thanks again.

[Alf]

Thanks for rep and feed back. Analyzing the result a see a problem starting with portfolio 23. As soon as there is only one weight (row 17 to 19) the constraint that
row 22 = row 21 don't "work" any more.

Perhaps this needs an extra constraint that ensures that at least 2 weights must be chosen? Or do you have any explanation?

Almost midnight here in Sweden so I'm off to bed!

Alf

[CharlotteFI]

That's funny you mention that. I actually just made an edit to my master version just before reading your comment. The example I gave you contained several portfolios that were not theoretically possible due to the asset characteristics. I've since fixed it in the master so the issue no longers exists. Because this is an efficient frontier, the 1st asset (Row 17) was never actually used due to its relative inefficiency. I'm thinking I'll change the constraint to ensure at least two (or 3) weights are chosen for some different scenario analyses I'll be doing. End result should be a portfio of the "feasible set" in addition to the efficient frontier.