Scenario Analysis

I am trying to write VBA to run through all the combinations of stock weights in a portfolio to determine all the output combinations of an equation that is linked to the security weights.

The portfolio may have 1 to very large number of securities.

For instance assuming stock weight increment adjustments of 1%... and all stock weights add to 100% and none are less than 0%...

2 securities in a portfolio:
A , B
100% , 0%
99% , 1%
98% , 2%
etc.... results in 101 combinations.

3 securities in a portfolio:
A , B , C
100% , 0% , 0%
99% , 1% , 0%
99% , 0% , 1%
98% , 2% , 0%
98% , 1% , 1%
98% , 0% , 2%
etc.... results in xxx combinations.

In sum, the VBA must compute the security weight of every combination of stock weights (assuming 1% increments, 100% = total stock weights, and weights are between 100% and 0%).

Any advice?

If 'very large number of securities' means a number greater than 10, you're talking about an astronomical number of combinations ...

Edit: There are, for example, in combinations totalling 100%,

• 4,598,126 combinations for 5 stocks in 1% increments
• 10,015,005 combinations for 10 stocks in 5% increments
• 20,030,010 combinations for 20 stocks in 10% increments

Can you shed a bit of light on what you are trying to do with those combinations? Surely, a vast number of the combinations would be of no consequence. It seems plausible if you desire to arrive at a specific set of combinations that meet a defined set of criteria. In that event, it is possible to set out an optimization algorithm to perform the task.