Solved

VBA EXCEL assign and categorize data

Posted on 2009-05-13
7
296 Views
Last Modified: 2013-11-25
Hi, another question: I have a sheet (is attached) where I need to categorize data according to the Rating. On sheet "Info" we can find the rating (column B) for the particular bond - on sheet "Risk Criteria" we can find the the Riskrating (column E) which indicates that A+ to A- will get the rating 3 (this inculdes also A as it is in between A+ and A-). Everything BB+ and below will get a 5 - if there is no rating it should also get a 5. The result should be displayed in sheet "Calculation". The CUSIP/ISIN is the ID. If you have any help how to solve this I would be very grateful.
Moreover, I would like to classify the country by assinig them another rating. This rating still has to be calculated according to the data i am downloading on sheet "Country". Here we can see that there are many bonds with a number (yield) - this data actually has to be sorted by the numer (yield) so that we can perform the assignment of the Risk Criteria. On sheet "Risk Criteria" we can the 20% having the highest number (yield) should get the number 5 (80.01% - 100%) this should include all the countries on the bottim of the table on sheet "Country". If there is not country found the Bond should get a 5 (as with the Rating). The result should be displayed on sheet "Calculation". Again, I am grateful for any help.
RatingCountry.xls
0
Comment
Question by:kbrasco
  • 5
  • 2
7 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 24377156
What about AAA rating?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24377168
And other similar ratings?
AAA, AA, BBB, BB, BB+ and so on...
0
 

Author Comment

by:kbrasco
ID: 24377655
AAA = 1
AA+, AA, AA- = 2
A+, A, A- = 3
BBB+, BBB, BBB- = 4
all the rest = 5

Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 45

Expert Comment

by:patrickab
ID: 24388479
kbrasco,
Hopefully the macro below takes care of the requirements of your paragraph 1. It's in the attached file. I'll be back later for the 2nd para. Let me know how you get on with the first macro.
Press the button on the Calculation sheet to get the results.
Patrick

Sub specialmacro()

Dim rng1 As Range

Dim celle1 As Range

Dim rng2 As Range

Dim celle2 As Range
 

With Sheets("Calculation")

    Set rng1 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "B").End(xlUp))

End With

With Sheets("Info")

    Set rng2 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "D").End(xlUp))

End With
 

For Each celle1 In rng1

    celle1.Offset(0, 1) = WorksheetFunction.VLookup(celle1, rng2, 2, False)

    celle1.Offset(0, 2) = WorksheetFunction.VLookup(celle1, rng2, 3, False)

    Select Case UCase(celle1.Offset(0, 1))

        Case Is = "AAA"

            celle1.Offset(0, 3) = 1

        Case Is = "AA", "-AA"

            celle1.Offset(0, 3) = 2

        Case Is = "A", "-A"

            celle1.Offset(0, 3) = 3

        Case Is = "BBB", "-BBB"

            celle1.Offset(0, 3) = 4

        Case Is = "D", "BB"

            celle1.Offset(0, 3) = 5

        Case Else

            celle1.Offset(0, 3) = 5

    End Select

Next celle1
 

End Sub

Open in new window

RatingCountry-01.xls
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 24390034
kbrasco,
I have corrected the ratings part. I'm not sure I've got it right yet - please check it out. It's in the attached file and in the 'Select Case' section of the VBA code below.
I will await your response before taking any further steps.
Patrick

Sub specialmacro()

Dim rng1 As Range

Dim celle1 As Range

Dim rng2 As Range

Dim celle2 As Range
 

With Sheets("Calculation")

    Set rng1 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "B").End(xlUp))

End With

With Sheets("Info")

    Set rng2 = Range(.Cells(2, "B"), .Cells(.Cells.Rows.Count, "D").End(xlUp))

End With
 

For Each celle1 In rng1

    celle1.Offset(0, 1) = WorksheetFunction.VLookup(celle1, rng2, 2, False)

    celle1.Offset(0, 2) = WorksheetFunction.VLookup(celle1, rng2, 3, False)

    Select Case UCase(celle1.Offset(0, 1))

        Case Is = "AAA"

            celle1.Offset(0, 3) = 1

        Case Is = "AA", "AA-", "AA+"

            celle1.Offset(0, 3) = 2

        Case Is = "A", "A-", "A+"

            celle1.Offset(0, 3) = 3

        Case Is = "BBB", "BBB-", "BBB+"

            celle1.Offset(0, 3) = 4

        Case Is = "D", "BB", "BB-", "BB+"

            celle1.Offset(0, 3) = 5

        Case Else

            celle1.Offset(0, 3) = 5

    End Select

Next celle1
 

End Sub

Open in new window

RatingCountry-02.xls
0
 

Author Closing Comment

by:kbrasco
ID: 31581064
Excellent!
0
 
LVL 45

Expert Comment

by:patrickab
ID: 24426129
kbrasco - Pleased it did what you wanted. Thanks for the grade - Patrick
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now