Permutations/combinations (VBA)

[Imperial_1]

Hello all,

I’m looking to create a macro that will output to excel “all weight” permutations for a portfolio of 2 assets.

Now I’m actually looking to do this with quite a few assets (5 initially) but starting with 2 at this stage. To avoid too large a number of permutations, I will go up in steps of 0.1 (10%). Also note the combination of 2 numbers needs to equal 1 (100%).

For example, I have asset A and asset B. I see this as having 11 possible combinations; A – 100% and B – 0%, A-90% and B-10%....

I have written the macro below which seems to miss the first 2 combinations and the last 2 combinations and I’m not sure why.

Can someone please help me?

Regards
Dipesh

Sub every_Combo()

Dim i        As Double
Dim j        As Double
Dim x        As Integer

x = 1

Sheets("perm").Cells.ClearContents

    For i = 0 To 1 Step 0.1
        For j = 0 To 1 Step 0.1

    
        If i + j = 1 Or i = 1 Or j = 1 Then
            Sheets("perm").Cells(x, 1).Value = i
            Sheets("perm").Cells(x, 2).Value = j
            x = x + 1
        Else
            GoTo ABC
        End If

ABC:
        
        Next j
    Next i

End Sub

 

[pgc01]

Hi Dipesh
Welcome to the board

You only need one variable, for the percent value of the first asset.
The second value is not variable, it's fixed and equal to 100 minus the first one.

For ex., working with integer percent values:

Sub every_Combo()

Dim j As Long
Dim lRow As Long

    Sheets("perm").Cells.ClearContents

    For j = 0 To 100 Step 10
        lRow = lRow + 1
        Sheets("perm").Cells(lRow, 1).Value = j
        Sheets("perm").Cells(lRow, 2).Value = 100 - j
    Next j

End Sub

 

[Imperial_1]

Hello PGC,
Thanks for the reply.
I agree with your solution, make sense when there are only 2 assets. But once I go beyond two assets there is a need for more than one variable. (However, I do agree there will always be one asset which can be derived from the other variables.)
But, in any case I’m still interested to know why my code is not working as I’m expecting. Think I am missing something basic….
Below I have the code for a 5 asset version, in which I experience the same problem. The initial and final combinations that I would expect to be outputted are missed.
For example I would expect the first output to be:

• 0
• 0
• 0
• 0
• 1

And then the next output after than to be:

• 0
• 0
• 0
• 0.1
• 0.9

Instead the first output is:

• 0
• 0
• 0
• 0.2
• 0.8

Not sure what the issue is here.
Thanks again and regards
Dipesh

Option Explicit


Sub Every_combo()

Dim A           As Double
Dim B           As Double
Dim C           As Double
Dim D           As Double
Dim E           As Double
Dim x           As Integer

x = 1

Sheets("perm").Cells.ClearContents

    For A = 0 To 1 Step 0.1
        For B = 0 To 1 Step 0.1
            For C = 0 To 1 Step 0.1
                For D = 0 To 1 Step 0.1
                    For E = 0 To 1 Step 0.1
    

                        If A + B + C + D + E = 1 Then
                            Sheets("perm").Cells(x, 1).Value = A
                            Sheets("perm").Cells(x, 2).Value = B
                            Sheets("perm").Cells(x, 3).Value = C
                            Sheets("perm").Cells(x, 4).Value = D
                            Sheets("perm").Cells(x, 5).Value = E
                            
                            x = x + 1
                            
                        Else
                            GoTo ABC
                        End If
                        
ABC:

                    Next E
                Next D
            Next C
        Next B
    Next A
    

        
        

End Sub

 

[pgc01]

But, in any case I’m still interested to know why my code is not working as I’m expecting. Think I am missing something basic….

I see. Sorry, I agree, first of all it's important to know what is wrong.

You are using Doubles and then making exact comparisons, which is always a risk due to to possible floating point approximation errors.

Try rounding the values, in this case for ex. with 2 decimals and then compare.

This is the code you posted first, just rounding before comparing:

Sub every_Combo()

Dim i        As Double
Dim j        As Double
Dim x        As Integer

x = 1

Sheets("perm").Cells.ClearContents

    For i = 0 To 1 Step 0.1
        For j = 0 To 1 Step 0.1

    
        If Round(i + j, 2) = 1 Then
            Sheets("perm").Cells(x, 1).Value = i
            Sheets("perm").Cells(x, 2).Value = j
            x = x + 1
        Else
            GoTo ABC
        End If

ABC:
        
        Next j
    Next i

End Sub