• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the mutual fund and Rank them best of thier consistency

B

BK LAAL

Member
Please find the attached xls.


sheet : top20_list This sheet contains top 20 Elss mutualfunds .

1) I have to highlight those mutual funds which are present in all 5 coloums.

2)These mutual funds are sorted based on their rank on that particular month/Year.
I want to make a sorted Rank list consirdering its consistency for all the months and year


Please guide me in this
 

Attachments

Hi ,

Can you explain what is meant by consistency ?

1. There are 7 columns of figures , from column C through column I.

2. I assume that in these figures , bigger is better i.e. a value such as 23.4 is better than 2.34

3. Since the periods for which these figures have been calculated are different , I assume that when taking some kind of a weighted average , the figures for the longer periods should carry more weight ; for instance a value of 11.1 in column I ( 5 yrs ) should carry more weight than 11.1 in column E ( 6 mnths ). Is this correct ? If so , can you specify these weights ?

4. Lastly , consistency alone cannot be a criterion , thus a fund which has consistently been around 5 need not be better than a fund which has fluctuated between 5 and 20.

5. You have calculated various sums in columns K , L and M ; are the figures such that we can just sum up the figures in columns C through I ? What meaning does this sum have ?

Narayan
 
Hi Narayan

Request you to check the tab top20_list and not the mutualfunds_return tab.

for calculation for the data based on mutualfunds_return tab I ll ask you question later :)

The data in tab top20_list list is arraged based on the Ranking.

1) I have to highlight those mutual funds which are present in all 5 coloums.
2)These mutual funds are sorted based on their rank on that particular month/Year.
I want to make a sorted Rank list consirdering its consistency for all the months and year

For Eg

Following is the cricket test ranking for that test match

Rank TEST 1 TEST 2 TEST 3 TEST 4 TEST 5 Overal Rating
Rank 1 Sachin S Ganguly Sachin Dravid Sachin Sachin
Rank 2 Dravid Dravid S Ganguly S Ganguly Dravid Dravid
Rank 3 S Ganguly Sachin Dravid Sachin S Ganguly S Ganguly

And I want the result as Overall Rating

This is because out of 5 Test matches 3/5 sachin is one
Now out of 2 other player, we shall check who has most no of Rank 2 its Dravid
So and and so forth

Rank sachin Ganguly Dravid
Rank 1 3 1 1
Rank 2 0 2 3
Rank 3 2 2 1


On Similiar lines i want for those mutual fund of tab top20_list

Thanks in advance
 
Hi ,

Sorry I overlooked that , but the points I made still stand ; how do we compare a ranking over a 2 month period with a ranking over a 2 year period ?

The analogy you have given of Sachin , Ganguly and Dravid does not hold ; it is like comparing a century made in a T20 match with a century made in a Test match ; they should not be compared , since the circumstances in which the centuries might have been made , the opposition against which they might have been made , could be totally different.

In fact , the ranking itself should decide which is better ; for example , if you have given a ranking of 1 to ICICI Pru RIGHT Fund (G)over a period of 3 years , this itself means that ICICI Pru RIGHT Fund (G) is the most consistent ; the fact that Axis Long Term Equity Fund (G) is the top ranked fund over a one-year period is immaterial.

It can also happen that a fund is not ranked high over a 3-year period since it might not have been in operation for that long. How do you factor in this ?

Narayan
 
Hi Narayan

Thanks for your Reply

I agree with you partially. For simplicity I have done it this way.

MoreOver in MF 3 year if ICICI PRu Right FUnd is No1 it means,till that year (2014-3 = 2011) it is NO1

For Example if we take run scored for past 3 year sachin must be No 1
and if we take run socred for past 1 year S Ganguly must be No1
and if we take run socred for past 6 month Dravid can be No1

Regaring your last point

It can also happen that a fund is not ranked high over a 3-year period since it might not have been in operation for that long. How do you factor in this ?

[bk laal] : I have selected only those fund which are existing for past 3 - year period
 
Hi ,

The issue is still not clear !

Consistency refers to continued good performance over a period of time.

If fund A has been ranked first over a 1-year period , but fund B has been ranked first over a 3-year period , does that not mean that fund B is more consistent than fund A ?

Let us keep cricket out of it and stick to your working example ; can you manually work it out and say which fund is the most consistent and how you would work it out ? If you want , take any two funds as examples , and explain which of the two would be more consistent.

Once the logic is thoroughly worked out , coding it is that much easier.

Narayan
 
Thanks Narayan for your reply

Its exactly same as the cricket example I gave .

anyways illustrating the example with 3 funds

Rank 3 Month perf 6 Month Perf 1 Year Perf 2 Year Perf 3 Year Perf Overal Rating
Rank 1 ICICI Pru RIGHT Fund (G) Reliance Tax Saver (ELSS) (G) ICICI Pru RIGHT Fund (G) Axis Long Term Equity Fund (G) ICICI Pru RIGHT Fund (G) ICICI Pru RIGHT Fund (G)
Rank 2 Axis Long Term Equity Fund (G) Axis Long Term Equity Fund (G) Reliance Tax Saver (ELSS) (G) Reliance Tax Saver (ELSS) (G) Axis Long Term Equity Fund (G) Axis Long Term Equity Fund (G)
Rank 3 Reliance Tax Saver (ELSS) (G) ICICI Pru RIGHT Fund (G) Axis Long Term Equity Fund (G) ICICI Pru RIGHT Fund (G) Reliance Tax Saver (ELSS) (G) Reliance Tax Saver (ELSS) (G)
 
I have given
rank 1 1wt
rank 2 .9wt
rank 3 .8wt

and then did the SUM PRODUCT
Rank ICICI Pru RIGHT Fund (G) Reliance Tax Saver (ELSS) (G) Axis Long Term Equity Fund (G)
1 3 1 1
0.9 0 2 3
0.8 2 2 1
Total 4.6 4.4 4.5

Suggestion are welcome .

without weightage if we can do then it will be better

because I cannot say Rank 2 = .9 of rank 1

MoreOver i want a simple overall ranking . It can be cricket ranking or Students ranking for thier monthy test
 
Any help here Please

atleast for the 1st part of my requirement ie

I need to highlight ( condition format ) only those cell values which are present in all the columns ( in my example 5 columns)
 
Hi ,

Would the same colour do ? Or would you want each fund coloured a different colour ?

Would you need the funds coloured in all the columns ?

Narayan
 
@NARAYANK991
Excellent solution sir. Inspired by your code. I had use below formula in CF. The only limitation it has, it will use only one colour. But it is dynamic.
=SUMPRODUCT(IF(B2=Ranking_Data,1,0))=5

I want to make one suggestion for you, everytime an user run your code, it should clear the formatting first and reapply new one as per data change. Now it is keeping the old one, if you change data, the highlighted cell are still highlighted.

Regards,
 
Hi Misra ,

Thanks for pointing that out ; the revised code is :

Code: 
Public Sub Colour_Funds()
          Dim counter As Integer
          Dim color_array(1 To 9) As Long
          color_array(1) = RGB(200, 150, 150)
          color_array(2) = RGB(200, 200, 150)
          color_array(3) = RGB(200, 250, 150)
          color_array(4) = RGB(150, 150, 200)
          color_array(5) = RGB(150, 200, 200)
          color_array(6) = RGB(150, 250, 200)
          color_array(7) = RGB(150, 200, 150)
          color_array(8) = RGB(150, 200, 200)
          color_array(9) = RGB(150, 200, 250)
         
          Worksheets("top20_list").Activate
          Range("Ranking_Data").Interior.Color = xlNone
          counter = 1
         
          For Each cell In Range("First_Column")
              If Application.WorksheetFunction.CountIf(Range("Ranking_Data"), cell) = 5 Then
                  cell.Interior.Color = color_array(counter)
                  For Each cell1 In Range("First_Column").Offset(, 1).Resize(, 4)
                      If cell1 = cell Then cell1.Interior.Color = color_array(counter)
                  Next
                  counter = counter + 1
              End If
          Next
End Sub
where the additional line of code is :

Range("Ranking_Data").Interior.Color = xlNone

Narayan
 
You must log in or register to reply here.
Back
Top