Assigning Rating History to time intervals

[wdbouk]

Hello,
below is a sample from my data

issue_id rating_date rating
1 26/05/1993 Baa3
1 23/01/1997 Baa2
2 13/10/1993 Baa3
2 23/01/1997 Baa2
4 26/07/1994 Aa3
4 26/11/1996 Aa2
4 08/07/1998 Aa3
4 08/12/1998 Aa2
4 27/08/1999 Aa3
5 20/05/1993 Aa2
5 26/11/1996 Aa2
5 08/07/1998 Aa2
5 08/12/1998 Aa3
5 27/08/1999 Aa3
6 13/10/1993 Aa2
6 26/11/1996 Aa2
6 08/07/1998 Aa2
6 08/12/1998 Aa3
6 27/08/1999 Aa3

I have the following data. However, I need the rating to be in a time interval, i.e I want to add two columns FromDate and ToDate. Consequently , for issue_id 4 for example FromDate and Todate Should be the following:

FromDate ToDate Rating
26/07/1994 26/11/1996 Aa3
26/11/1996 08/07/1998 Aa2
08/07/1998 08/12/1998 Aa3
08/12/1998 27/08/1999 Aa2
27/08/1999 today Aa3

Any help with that? It is quite difficult for my and my database is quite large )more than 100,000 observations.
I hope someone could help

 

[MichaelRed]

I assume that something else is necessary / required. The First line of your sample has a beginning date which appears to be associated w/ "issue" 4, and an Ending date associated with "issue" 5, thus removing the only coloumn which I might be tempted to use as the criteria for the series organization.

It "looks Like" the traditional bond rating system, where "Issue" would translate into a specific bond, and the rating changes with the fortunes of the issuer. In such a scheme, it would be reasonable to include the name of the issuer of the bond and a series desiginator. W/O additional information, I cannot really be of any help. Perhaps you can calrify you needs / intention or others will come to your aid.





MichaelRed
mlred@verizon.net

 

[wdbouk]

Dear micheal,

I have the rating date of each issue. A certain issue can have many rating dates if the rating of the bond was changing.
I have another data set , whcih is the transaction prices of bonds. I am trying to group the bonds by rating to do some research on that. So, if a bond was traded on a certain date and its rating was aa then this price is grouped with other aa prices. Now if the rating of tis bonds falls for "A" and the bond was traded when the rating was "A" this price is going to be grouped with other "A" bonds.
the problem is that I have the transaction dates and I have the rating dates but these dates don't coincide.A bond could have many ratings in the ratings history and I don't know which adequate rating corresponds to the transaction date. I know how to assign each rating to the transaction price using access only if I know the rating of the bond between the rating dates (using a query and puting in the criteria that the transaction date is between [FromDate] And [Todate]). Consequently the adequate rating is assigned to each transaction price.
The common field is Issue_ID between all the tables.
I hope I didn't confuse you more. I hope you could help
best wishes
wdbouk

 

[MichaelRed]

Again, the issue is that the two sets of info do not appear to have a unique (to the bond) identifier which is common to the information sets.

I made an attempt to illustrate the issue by re-generating your sample data and joining them fogether via a query:

[tt]
BondId dtStrt dtEnd Rateing
4 8/27/1999 7/23/2004 Aa3
4 8/12/1998 8/27/1999 Aa2
4 8/7/1998 8/12/1998 Aa3
4 11/26/1996 8/7/1998 Aa2
4 7/26/1994 11/26/1996 Aa3
5 8/27/1999 7/23/2004 Aa3
5 8/12/1998 8/27/1999 Aa2
5 8/7/1998 8/12/1998 Aa3
5 11/26/1996 8/7/1998 Aa2
6 8/27/1999 7/23/2004 Aa3
6 8/12/1998 8/27/1999 Aa2
6 8/7/1998 8/12/1998 Aa3
6 11/26/1996 8/7/1998 Aa2
[/tt]

AS you can see from these results, my results are nothing like what you want. It does not show and results for your items 1 & 2 while having different results than the remaining items. Since the Item Id is not carried into the second 'table' in your example is is not (cannot) be used to form the linkage. Since 'table 1' has only a single date field, I 'assumed' it to be the starting date of table 2. However this value is repeated in table one, leading to (erronous) duplication in my query.

This is not a soloution, merely an attempt to illustrate the problem.

Again, you need to have some common linkage between the two 'tables' to reasonably link them.



MichaelRed
mlred@verizon.net

 

[cmmrfrds]

Looking at your sample data it would be very difficult through sql to derive the results you suggest. If the data had a sequence number (maybe auto number) then it probably can be done through a self join. For example.

SELECT A.issueID, A.RateDate AS FromDate, B.RateDate AS ToDate, B.Rating AS LastOfRating
FROM IssueDates AS A INNER JOIN IssueDates AS B ON A.issueID=B.issueID
WHERE A.ID=B.ID+1;

In this case ID is the sequence number. In order to do this you would need to build a new temp table with the autonumber each time since the number may have gaps when maintenance is done on the table. This query is simple enough by making a table that matches your table with the addition of a autonumber then do an Insert Into Select From query.

 

[PHV]

Something like this ?
SELECT A.issue_id, A.rating_date AS FromDate, Min(B.rating_date) AS ToDate, A.rating
FROM tblRating A LEFT JOIN tblRating B
ON (A.rating_date<B.rating_date) AND (A.issue_id=B.issue_id)
GROUP BY A.issue_id, A.rating_date, A.rating
;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244