Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Bond Rating Query

    I am trying to develop a query for average Bond ratings in a portfolio. I believe the best way to do this this would be to assign each bond value a number value, then calculate the aggregate to then have it spit out an average value. I would want the average not to include "nr". Also there will be some reconrds that will have null (blank) records and I would like the query to ignore these in both of these queiries. the field name is called "S&P Rating" The bond rating would be numerically assigned as follows:

    AAA 1
    AA+ 2
    AA 3
    AA- 4
    A+ 5
    A 6
    A- 7
    BBB+ 8
    BBB 9
    BBB- 10
    BB+ 11
    BB 12
    BB- 13
    B+ 14
    B 15
    B- 16
    CCC+ 17
    CCC 18
    CCC- 19
    nr 20

    Also, in addition to an average value I would like to it produce data similar to the below. The "A" will aggregate all of the types of A bonds, The "BBB" will include the BBB. BBB+, and BBB- and so on.

    A 31.60%
    BBB 8.98%
    BB 27.94%
    B 20.68%
    CCC 7.71%
    NR 3.09%

    Any help here would be greatly appreciate! Please let me know if you need additional details

    Thanks,
    Mike

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think the clue is in the "aggregate all of the types of A bonds" bit. You have three distinct types of bonds that could be represented as BondTypes:

    BondType
    ------------
    bond_type_id
    description

    Then you have several possibilities for "sub" bond types, which could be represented as another table:


    BondTypeRating
    ----------------
    bond_type_rating_id
    bond_type_id
    description
    rank


    You can then assign your individual bonds a "bond_type_rating_id" which would give you the bond type by way of the bond_type_id, the plain-english representation (AAA, AA+, etc) by way of the description field in the BondTypeRating table, as well as the relative weight by way of the rank field.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    I'm thinking the first step for me would be to assign values to the bonds as I have listed above. I have a basic understanding of Access functions. How would I set that up (in design view or SQL)?

    After i have that I believe I can make some more progress on my own.

    Thanks,

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why not use Excel if that's your preferred route?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2009
    Posts
    6
    This is currently part of a much larger project that we are trying to convert from Excel to Access, so ideally we would like to convert this part to Access as well. Also because the data is pretty expansive.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, if you have good business reason to use Access then I STRONGLY suggest boning up a bit on relational fundamentals.

    Have gander here:
    Fundamentals of Relational Database Design -- r937.com

    The idea here is to break up the logical chunks of data in to their own tables, then use relationships to hook everything together. This is what Access is really good at. Excel is a good weapon of choice if you're dealing with a "flat" table like you're describing above. However, approaching Access with the same mindset almost invariably results in unstable, brittle, poorly performing and excruciatingly-painful-to-maintain-and/or-extend solutions.

    The values you're trying to assign have a place (hooked up to Rank in BondTypeRating), but I can't caution you strongly enough against trying to put all of this in the same table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2009
    Posts
    6
    So you wouldn't reccomend doing some kind of update query with multiple if statements to have this data changed over?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think so. Granted I know very little about exactly what you're working with...

    Assuming you have some spreadsheets or text files or some other kind of flat datasource that you're trying to import, you can key off of the "description" field in "BondTypeRating" to properly tag each bond you need to import with the correct rating.

    I don't have the time right at this very second to whip up an example, but the rough idea is you create a staging table that mirrors all the fields you want to pull in from your raw datasource. You then look at this table to see if it has any new bonds/what-have-you that need to be imported based on the best identifying information you have available. In this case I'd imagine you get legal bond names or symbols that are fairly reliable.

    After you've imported any new bonds, you can then look at the "S&P Rating" field and match it up to the description out of BondTypeRating. That will give you a bond_type_rating_id which you could then use to hook up the rating to a given bond. If you only want to store the current rating for each bond, you could put a bond_type_rating_id on your main Bond table. If you want to store a history of ratings, then you can create a many-to-many table between Bond and BondTypeRating that would track what rating a given bond had at what point in time.

    Once you've done this, then you get all the reporting and querying benefits of properly normalized bond rating info.
    Last edited by Teddy; 03-31-10 at 18:22.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •