Iterative Ranking Problem in a loop

P

parodytx

New Member
Joined
Feb 17, 2008
Messages
23
I have a spreadsheet that populates itself from individual stock data downloaded and subsequently calculated.
The data is sorted per Sector Category - each sector may have one or more than one hundred tickers within it.
With the excellent help from those on this forum I have been able to get the logic that calculates a sector average return, and I would like to rank individual values within each sector. I just can't get my head around the logic inside the routine - I would be willing to call another routine if that works.

Here is the code commented for my benefit:
Code: 
Sub AvgSectors7Day()
    Dim Count As Integer
    Dim mySum As Double
    Dim CellPosition As Range
    Dim J As Integer
    Dim K As Integer
    
    SortOnSectorField
     
    Set CellPosition = Range("D4")                                  'Sector Name
        
        Do Until CellPosition.value = ""
        Count = Count + 1
        
        mySum = mySum + CellPosition.Offset(, 9)                    '7 Day Return Column
       
        IF CellPosition.Offset(1) <> CellPosition Then              'If one row down <> this one
            CellPosition.Offset(, 25) = mySum / Count               'Calculate the Average
            K = Count - 1                                           'Decr Count by 1 due to Loop
   
            For J = 1 To K                                          'Loop to copy the Avg to all rows in Sector
                CellPosition.Offset(-J, 25) = mySum / Count         'It copies UP
            Next J
'
'Routine to Calculate Rank() in Sector here?
'
            Count = 0                                               'Out Of the IF Loop, reset the counters
            mySum = 0
        End IF
    
        Set CellPosition = CellPosition.Offset(1)                   'Next Row to Test
        
        Loop
End Sub

Any ideas? The Rank() function has me stumped with all these variable counters and offsets.

Thanks in advance.
 
Sort by date Sort by votes
Jtufplata said:
can you post sheet with data and an example of what you would like to do?
Click to expand...

Here is some pared down data illustrating what I need:

Code: 
[TABLE="width: 337"]
<TBODY>[TR]
[TD="class: xl66, width: 50, bgcolor: transparent"][B][SIZE=3][COLOR=#000000]Ticker[/COLOR][/SIZE][/B]
[/TD]
[TD="class: xl66, width: 213, bgcolor: transparent"][B][SIZE=3][COLOR=#000000]Morningstar Category[/COLOR][/SIZE][/B]
[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"][B][SIZE=3][COLOR=#000000]7 Day Return[/COLOR][/SIZE][/B]
[/TD]
[TD="class: xl66, width: 60, bgcolor: transparent"][B][SIZE=3][COLOR=#000000]Sector Return[/COLOR][/SIZE][/B]
[/TD]
[TD="class: xl68, width: 59, bgcolor: transparent"][B][SIZE=3][COLOR=#000000]Ticker Rank in Sector[/COLOR][/SIZE][/B]
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]AOA[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Aggressive Allocation[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.421% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.604% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]RLY[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Aggressive Allocation[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.723% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.604% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]VIXH[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Aggressive Allocation[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.331%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.604% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]BKLN[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Bank Loan[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.121% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.136%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]FTSL[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Bank Loan[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.504%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.136%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]SNLN[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Bank Loan[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.000% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.136%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]SRLN[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Bank Loan[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.161%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](0.136%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]HDGE[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]Bear Market[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](2.420%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](2.420%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]CHIE[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]China Region[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]3.831% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.614% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]CHII[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]China Region[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](2.463%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.614% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]CHIM[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]China Region[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#ff0000](2.840%)[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.614% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]CHIQ[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]China Region[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]0.599% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.614% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]CHIX[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]China Region[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]5.433% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.614% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]CHXF[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent"][SIZE=3][COLOR=#000000]China Region[/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.324% [/COLOR][/SIZE]
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"][SIZE=3][COLOR=#000000]1.614% [/COLOR][/SIZE]
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

As you can see, the Sector Return is the average of the individual components. The actual Sectors may contain one, several or dozens of individual tickers. (the China Region has about a hundred - left out for clarity.) I would like to Rank the individual tickers WITHIN the sector, not the whole sheet. Thus, the top 3 tickers world rank out of 3, the next group out of 4, etc.

Here is the code again without the comments for readability.

Rich (BB code): 
Sub AvgSectors7Day()
    Dim Count As Integer
    Dim mySum As Double
    Dim CellPosition As Range
    Dim J As Integer
    Dim K As Integer
    
    SortOnSectorField
     
    Set CellPosition = Range("D4")
        
        Do Until CellPosition.value = ""
        Count = Count + 1
        
        mySum = mySum + CellPosition.Offset(, 9)
        If CellPosition.Offset(1) <> CellPosition Then
            CellPosition.Offset(, 25) = mySum / Count
            K = Count - 1
   
            For J = 1 To K
                CellPosition.Offset(-J, 25) = mySum / Count
            Next J
'
'Rountine to Calculate rank in Sector here?
'
            Count = 0
            mySum = 0
        End If
    
        Set CellPosition = CellPosition.Offset(1)
        
        Loop
End Sub


Thanks for any help.
 
Upvote 0
hello again,

i had a similar problem while ago. some values in red in inside () , is this for any particular reason, and should be in included for ranking?
 
Upvote 0
Jtufplata said:
hello again,

i had a similar problem while ago. some values in red in inside () , is this for any particular reason, and should be in included for ranking?
Click to expand...

The red negative values should be irrelevant to the rankings: if say, the values in a group of 4 are 1, 0, -1, and -2, then the ranking should be 1, 2, 3 & 4 out of 4 respectively.

The data is already sorted from highest value (most positive) to lowest (least positive or most negative) within each sector group.
 
Upvote 0
Hi,

the following code should help, just change the offsets as needed for your sheet, i tested it on your data, using columns A,B,C,D and E for rank.

Code: 
Sub test()
Dim MornStarCategory, sector As Range
Dim j As Integer
Dim w_sector As String
'On Error Resume Next
j = 1
Set MornStarCategory = Range("B:B").Cells.SpecialCells(xlCellTypeConstants) ' use morningstar range here or the range where you can group by region
    For Each sector In MornStarCategory  ' each region in all of regions
        If sector <> w_sector Then   ' compares the range value with the one stored w_sector variable
                w_sector = sector.Value  (because it isn't then rank is 1)
                j = 1
                sector.Offset(0, 3).Value = j
            Else                        'because they are the same region, than the rank is incremented by 1
                j = j + 1
                sector.Offset(0, 3) = j
        End If
    Next sector
End Sub

let me know if it worked.

regards
 
Last edited:
Upvote 0
parodytx said:
The red negative values should be irrelevant to the rankings: if say, the values in a group of 4 are 1, 0, -1, and -2, then the ranking should be 1, 2, 3 & 4 out of 4 respectively.

The data is already sorted from highest value (most positive) to lowest (least positive or most negative) within each sector group.
Click to expand...

Doh!

Writing about it caused me to figure it out - as the data is sorted already I just needed to add a line under the reverse CellPosition.Offset to the column needed using the loop counter as the rank. The Initial average goes in the LAST one of the series so it is automatically the lowest ranking.

If interested:
Rich (BB code): 
K = Count - 1                                      
L = Count - 1
                For J = 1 To K                                     
                    CellPosition.Offset(-J, 25) = mySum / Count                  'Copy the Average to prior row    
                    CellPosition.Offset(-J, 36) = "# " & L & " of " & Count      'Copy the rank to the same row
                    L = L - 1
                Next J

And here is the result:

Rich (BB code): 
[TABLE="width: 408"]
<TBODY>[TR]
[TD]Morningstar Category</SPAN>
[/TD]
[TD]7 Day Return</SPAN>
[/TD]
[TD]Sector Return</SPAN>
[/TD]
[TD]Ticker Rank</SPAN>
[/TD]
[/TR]
[TR]
[TD]Aggressive Allocation</SPAN>
[/TD]
[TD="align: right"](2.329%)</SPAN>
[/TD]
[TD="align: right"](2.947%)</SPAN>
[/TD]
[TD]# 1 of 3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Aggressive Allocation</SPAN>
[/TD]
[TD="align: right"](2.499%)</SPAN>
[/TD]
[TD="align: right"](2.947%)</SPAN>
[/TD]
[TD]# 2 of 3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Aggressive Allocation</SPAN>
[/TD]
[TD="align: right"](4.014%)</SPAN>
[/TD]
[TD="align: right"](2.947%)</SPAN>
[/TD]
[TD]# 3 of 3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bank Loan</SPAN>
[/TD]
[TD="align: right"](0.423%)</SPAN>
[/TD]
[TD="align: right"](0.620%)</SPAN>
[/TD]
[TD]# 1 of 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bank Loan</SPAN>
[/TD]
[TD="align: right"](1.092%)</SPAN>
[/TD]
[TD="align: right"](0.620%)</SPAN>
[/TD]
[TD]# 2 of 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bank Loan</SPAN>
[/TD]
[TD="align: right"](0.925%)</SPAN>
[/TD]
[TD="align: right"](0.620%)</SPAN>
[/TD]
[TD]# 3 of 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bank Loan</SPAN>
[/TD]
[TD="align: right"](0.040%)</SPAN>
[/TD]
[TD="align: right"](0.620%)</SPAN>
[/TD]
[TD]# 4 of 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bear Market</SPAN>
[/TD]
[TD="align: right"]2.290% </SPAN>
[/TD]
[TD="align: right"]2.290% </SPAN>
[/TD]
[TD]# 1 of 1</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](3.841%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 1 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](2.944%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 2 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"]1.004% </SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 3 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"]0.000% </SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 4 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](5.564%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 5 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](6.186%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 6 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](2.006%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 7 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](3.757%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 8 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](4.120%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 9 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]China Region</SPAN>
[/TD]
[TD="align: right"](2.516%)</SPAN>
[/TD]
[TD="align: right"](2.993%)</SPAN>
[/TD]
[TD]# 10 of 10</SPAN>
[/TD]
[/TR]
[TR]
[TD]Commodities Precious Metals</SPAN>
[/TD]
[TD="align: right"](6.978%)</SPAN>
[/TD]
[TD="align: right"](6.978%)</SPAN>
[/TD]
[TD]# 1 of 1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Foreign Large Growth</SPAN>
[/TD]
[TD="align: right"](3.881%)</SPAN>
[/TD]
[TD="align: right"](3.881%)</SPAN>
[/TD]
[TD]# 1 of 1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Technology</SPAN>
[/TD]
[TD="align: right"](3.628%)</SPAN>
[/TD]
[TD="align: right"](3.628%)</SPAN>
[/TD]
[TD]# 1 of 1
</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks for helping.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
398
user29383902
U
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
216
Mazurofgod
M
J
  • Solved
Excel Selenium VBA - Help with loop when class appears more than once
Replies
3
Views
287
Anthony47
A
S
  • Question Question
EMAIL REMINDERS FROM EXCEL VBA
Replies
2
Views
372
Logit
Logit
R
  • Question Question
VBA to automate calculation
Replies
1
Views
216
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top