VBA looping help

[leosmessi786]

Thanks everyone in advance for helping me out really appreciate it. This is an interesting assingment and I've tried a lot of different code but I can't get my head around it.

Basically, the table in sheet 1 is pulling in data from sheet 2 and pasting whatever is an underweight or an overweight(10% more or less relative to benchmark).So for example on the second table for Strategy 1 Sector 3 the portfolio weight is 23% greater than the benchmark weight. so that will be pasted onto the sheet 1 in the table. I do this process manually but am trying to code it. I tried a loop that goes through the data on the second table but I can't get my head around it.

If someone could help me that would be awesome thanks!

Sheet 1:

Portfolio		Port %	BM %	Active %	% Over Guideline
Underweight More Than -10% Relative Benchmark Weight
Strategy 1	Sector 3	28.00	5.00	23.00	33.00
Strategy 1	Sector 5	31.00	5.00	26.00	36.00
Strategy 5	Sector 2	13.00	0.00	13.00	23.00
Overweight 10% Plus the Benchmark Weight
Strategy 2	Sector 5	9.00	20.00	-11.00	-31.00
Strategy 4	Sector 2	0.00	12.00	-12.00	-24.00

	Sector 1	Sector 2	Sector 3	Sector 4	Sector 5
Strategy 1	7.00	8.00	28.00	1.00	31.00
Benchmark 1	5.00	5.00	5.00	5.00	5.00
Strategy 2	20.00	20.00	20.00	20.00	9.00
Benchmark 2	26.00	26.00	26.00	26.00	20.00
Strategy 3	0.00	0.00	0.00	0.00	0.00
Benchmark 3	4.00	4.00	4.00	4.00	4.00
Strategy 4	0.00	0.00	0.00	0.00	0.00
Benchmark 4	0.00	12.00	0.00	0.00	0.00
Strategy 5	0.00	13.00	0.00	0.00	0.00
Benchmark 5	0.00	0.00	0.00	0.00	0.00

 

[leosmessi786]

can someone please help me?

 

[StephenCrump]

Try this:

	A	B	C	D	E	F	G
1
2		MyData	Sector 1	Sector 2	Sector 3	Sector 4	Sector 5
3		Strategy 1	7.00	8.00	28.00	1.00	31.00
4		Benchmark 1	5.00	5.00	5.00	5.00	5.00
5		Strategy 2	20.00	20.00	20.00	20.00	9.00
6		Benchmark 2	26.00	26.00	26.00	26.00	20.00
7		Strategy 3	0.00	0.00	0.00	0.00	0.00
8		Benchmark 3	4.00	4.00	4.00	4.00	4.00
9		Strategy 4	0.00	0.00	0.00	0.00	0.00
10		Benchmark 4	0.00	12.00	0.00	0.00	0.00
11		Strategy 5	0.00	13.00	0.00	0.00	0.00
12		Benchmark 5	0.00	0.00	0.00	0.00	0.00
13
14
15		Portfolio	Sector	Port %	BM %	Active %	% Over
16		Overweight 10% +
17		Strategy 1	Sector 3	28	5	23	???
18		Strategy 1	Sector 5	31	5	26	???
19		Strategy 5	Sector 2	13	0	13	???
20		Underweight 10% +
21		Strategy 2	Sector 5	9	20	-11	???
22		Strategy 4	Sector 2	0	12	-12	???
Sheet1

MyData: =Sheet1!$B$2:$G$12
PutResultsHere: =Sheet1!$B$16:$G$22

Sub Test()

    Dim vIn As Variant, vOut(1 To 2) As Variant, vTemp() As Variant
    Dim Count(1 To 2) As Long, i As Long, j As Long, k As Long
    Dim diff As Double
    Const N = 2, T = 10, cols = 6
    
    vIn = Range("MyData").Value2
    ReDim vTemp(1 To (UBound(vIn) - 1) * (UBound(vIn, 2) - 1) / N, 1 To cols)
    vOut(1) = vTemp
    vOut(2) = vTemp
        
    For i = 2 To UBound(vIn) - 1 Step N
        For j = 2 To UBound(vIn, 2)
            If Abs(vIn(i, j) - vIn(i + 1, j)) >= T Then
                k = IIf(vIn(i, j) > vIn(i + 1, j), 1, 2)
                Count(k) = Count(k) + 1
                vOut(k)(Count(k), 1) = vIn(i, 1)
                vOut(k)(Count(k), 2) = vIn(1, j)
                vOut(k)(Count(k), 3) = vIn(i, j)
                vOut(k)(Count(k), 4) = vIn(i + 1, j)
                vOut(k)(Count(k), 5) = vIn(i, j) - vIn(i + 1, j)
                vOut(k)(Count(k), 6) = "???"  'I don't know what this number is
            End If
        Next j
    Next i

    With Range("PutResultsHere")
        .ClearContents
        .Cells(1, 1).Value = "Overweight 10% +"
        .Offset(1).Resize(Count(1), cols).Value = vOut(1)
        With .Offset(Count(1) + 1)
            .Cells(1, 1).Value = "Underweight 10% +"
            .Offset(1).Resize(Count(2), cols).Value = vOut(2)
        End With
        .Resize(Count(1) + Count(2) + 2, cols).Name = "PutResultsHere"
    End With
    
End Sub

 

[leosmessi786]

Try this:

	A	B	C	D	E	F	G
1
2		MyData	Sector 1	Sector 2	Sector 3	Sector 4	Sector 5
3		Strategy 1	7.00	8.00	28.00	1.00	31.00
4		Benchmark 1	5.00	5.00	5.00	5.00	5.00
5		Strategy 2	20.00	20.00	20.00	20.00	9.00
6		Benchmark 2	26.00	26.00	26.00	26.00	20.00
7		Strategy 3	0.00	0.00	0.00	0.00	0.00
8		Benchmark 3	4.00	4.00	4.00	4.00	4.00
9		Strategy 4	0.00	0.00	0.00	0.00	0.00
10		Benchmark 4	0.00	12.00	0.00	0.00	0.00
11		Strategy 5	0.00	13.00	0.00	0.00	0.00
12		Benchmark 5	0.00	0.00	0.00	0.00	0.00
13
14
15		Portfolio	Sector	Port %	BM %	Active %	% Over
16		Overweight 10% +
17		Strategy 1	Sector 3	28	5	23	???
18		Strategy 1	Sector 5	31	5	26	???
19		Strategy 5	Sector 2	13	0	13	???
20		Underweight 10% +
21		Strategy 2	Sector 5	9	20	-11	???
22		Strategy 4	Sector 2	0	12	-12	???
Sheet1

MyData: =Sheet1!$B$2:$G$12
PutResultsHere: =Sheet1!$B$16:$G$22

Sub Test()

    Dim vIn As Variant, vOut(1 To 2) As Variant, vTemp() As Variant
    Dim Count(1 To 2) As Long, i As Long, j As Long, k As Long
    Dim diff As Double
    Const N = 2, T = 10, cols = 6
   
    vIn = Range("MyData").Value2
    ReDim vTemp(1 To (UBound(vIn) - 1) * (UBound(vIn, 2) - 1) / N, 1 To cols)
    vOut(1) = vTemp
    vOut(2) = vTemp
       
    For i = 2 To UBound(vIn) - 1 Step N
        For j = 2 To UBound(vIn, 2)
            If Abs(vIn(i, j) - vIn(i + 1, j)) >= T Then
                k = IIf(vIn(i, j) > vIn(i + 1, j), 1, 2)
                Count(k) = Count(k) + 1
                vOut(k)(Count(k), 1) = vIn(i, 1)
                vOut(k)(Count(k), 2) = vIn(1, j)
                vOut(k)(Count(k), 3) = vIn(i, j)
                vOut(k)(Count(k), 4) = vIn(i + 1, j)
                vOut(k)(Count(k), 5) = vIn(i, j) - vIn(i + 1, j)
                vOut(k)(Count(k), 6) = "???"  'I don't know what this number is
            End If
        Next j
    Next i

    With Range("PutResultsHere")
        .ClearContents
        .Cells(1, 1).Value = "Overweight 10% +"
        .Offset(1).Resize(Count(1), cols).Value = vOut(1)
        With .Offset(Count(1) + 1)
            .Cells(1, 1).Value = "Underweight 10% +"
            .Offset(1).Resize(Count(2), cols).Value = vOut(2)
        End With
        .Resize(Count(1) + Count(2) + 2, cols).Name = "PutResultsHere"
    End With
   
End Sub

thanks a lot for you rreply. wait do u want me to declare the mydata and putresultshere as variables?

 

[StephenCrump]

Yes, based on the layout in Post #3, my code expects to find:

MyData: =Sheet1!$B$2:$G$12

(Or you could use VBA to determine this dynamically)

The code also expects to find a range called PutResultsHere, which as the name implies, is where you want the output table. A single cell will suffice - the code works out the required size dynamically.

 

[leosmessi786]

I'm getting an error with this line which is why im asking lol. Where do i put these variable above the sub test () or within the macro or where else

 

[StephenCrump]

They are Excel range names, i.e. you create them in Excel.

Here's the workbook: UnderOver.xlsm

 

[leosmessi786]

thank you so so much. this really helps a lot. you really are a great man. Just one more question sorry about that. But how would I make it so that strategies above 30% on an absolute basis have their own category

I put a picture

 

[StephenCrump]

You could generalise the code like this:

Sub Test()

    Dim vIn As Variant, vOut() As Variant, vTemp() As Variant
    Dim Count() As Long, i As Long, j As Long, k As Long, counter As Long
    Dim diff As Double
    Dim header() As String
    Const N = 2, cols = 6, M = 3
    ReDim vOut(1 To M)
    ReDim Count(1 To M)
    ReDim header(1 To M)
    header(1) = "Overweight 20% +"
    header(2) = "Overweight 10% +"
    header(3) = "Underweight 10% +"
        
    vIn = Range("MyData").Value2
    ReDim vTemp(1 To (UBound(vIn) - 1) * (UBound(vIn, 2) - 1) / N, 1 To cols)
    For i = 1 To M
        vOut(i) = vTemp
    Next i
        
    For i = 2 To UBound(vIn) - 1 Step N
        For j = 2 To UBound(vIn, 2)
            k = 0
            Select Case vIn(i, j) - vIn(i + 1, j)
            Case Is > 20
                k = 1
            Case Is > 10
                k = 2
            Case Is < -10
                k = 3
            End Select
            If k <> 0 Then
                Count(k) = Count(k) + 1
                vOut(k)(Count(k), 1) = vIn(i, 1)
                vOut(k)(Count(k), 2) = vIn(1, j)
                vOut(k)(Count(k), 3) = vIn(i, j)
                vOut(k)(Count(k), 4) = vIn(i + 1, j)
                vOut(k)(Count(k), 5) = vIn(i, j) - vIn(i + 1, j)
                vOut(k)(Count(k), 6) = "???"  'I don't know what this number is
            End If
        Next j
    Next i

    With Range("PutResultsHere")
        .ClearContents
        .Font.Bold = False
        For i = 1 To M
            With .Offset(counter)
                .Cells(1, 1).Value = header(i)
                .Cells(1, 1).Font.Bold = True
                If Count(i) > 0 Then
                    .Offset(1).Resize(Count(i), cols).Value = vOut(i)
                Else
                    .Offset(1).Cells(1, 1).Value = "n/a"
                    counter = counter + 1
                End If
                counter = counter + Count(i) + 1
            End With
        Next i
        .Resize(counter, cols).Name = "PutResultsHere"
    End With
    
End Sub

 

[leosmessi786]

hey, thanks for your reply. I said 30% as an absolute value not a relative one. so if a value for a strategy was 30% and lets say the benchmark was 25% it would still show.