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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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): 


Morningstar Category</SPAN>
7 Day Return</SPAN>
Sector Return</SPAN>
Ticker Rank</SPAN>

Aggressive Allocation</SPAN>
(2.329%)</SPAN>
(2.947%)</SPAN>
# 1 of 3</SPAN>

Aggressive Allocation</SPAN>
(2.499%)</SPAN>
(2.947%)</SPAN>
# 2 of 3</SPAN>

Aggressive Allocation</SPAN>
(4.014%)</SPAN>
(2.947%)</SPAN>
# 3 of 3</SPAN>

Bank Loan</SPAN>
(0.423%)</SPAN>
(0.620%)</SPAN>
# 1 of 4</SPAN>

Bank Loan</SPAN>
(1.092%)</SPAN>
(0.620%)</SPAN>
# 2 of 4</SPAN>

Bank Loan</SPAN>
(0.925%)</SPAN>
(0.620%)</SPAN>
# 3 of 4</SPAN>

Bank Loan</SPAN>
(0.040%)</SPAN>
(0.620%)</SPAN>
# 4 of 4</SPAN>

Bear Market</SPAN>
2.290% </SPAN>
2.290% </SPAN>
# 1 of 1</SPAN>

China Region</SPAN>
(3.841%)</SPAN>
(2.993%)</SPAN>
# 1 of 10</SPAN>

China Region</SPAN>
(2.944%)</SPAN>
(2.993%)</SPAN>
# 2 of 10</SPAN>

China Region</SPAN>
1.004% </SPAN>
(2.993%)</SPAN>
# 3 of 10</SPAN>

China Region</SPAN>
0.000% </SPAN>
(2.993%)</SPAN>
# 4 of 10</SPAN>

China Region</SPAN>
(5.564%)</SPAN>
(2.993%)</SPAN>
# 5 of 10</SPAN>

China Region</SPAN>
(6.186%)</SPAN>
(2.993%)</SPAN>
# 6 of 10</SPAN>

China Region</SPAN>
(2.006%)</SPAN>
(2.993%)</SPAN>
# 7 of 10</SPAN>

China Region</SPAN>
(3.757%)</SPAN>
(2.993%)</SPAN>
# 8 of 10</SPAN>

China Region</SPAN>
(4.120%)</SPAN>
(2.993%)</SPAN>
# 9 of 10</SPAN>

China Region</SPAN>
(2.516%)</SPAN>
(2.993%)</SPAN>
# 10 of 10</SPAN>

Commodities Precious Metals</SPAN>
(6.978%)</SPAN>
(6.978%)</SPAN>
# 1 of 1</SPAN>

Foreign Large Growth</SPAN>
(3.881%)</SPAN>
(3.881%)</SPAN>
# 1 of 1</SPAN>

Technology</SPAN>
(3.628%)</SPAN>
(3.628%)</SPAN>
# 1 of 1
</SPAN>



<TBODY>

</TBODY>

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

Similar threads

R
  • Solved
I need to find a row based on multiple criteria, with a catch.
2
Replies
13
Views
531
rcrumbliss
R
P
  • Solved
Counter in For Each loop
Replies
2
Views
207
Possum17
P
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
371
Agnarr
Agnarr
montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
R
  • Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
145
Domenic
D

Forum statistics

Threads
1,215,406
Messages
6,124,720
Members
449,184
Latest member
COrmerod

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