I'm getting more rows that requested on bond ratings request over DSS REST API
I'm requesting 11 rating type codes for 1 security and 1 field name (Rating Type Code) for the issuer of that security, but I get 15 rows back ! How is that possible ? And how do I find which row is for which rating code ?
Thanks for help
Bernard
@bernard.thouin,
In such cases it is best to experiment with a tool that displays all the data, without any treatment. Once you see the data you can discover many things. As a tool I use Postman, it is very easy and quick to use.
I recreated your request in Postman, and added 2 fields: "Issuer Rating Effective Date", "Issuer Rating Source Code".
Here is the request:
{
"ExtractionRequest": {
"@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.RatingsExtractionRequest",
"ContentFieldNames": [ "Issuer Rating Code", "Issuer Rating Effective Date", "Issuer Rating Source Code" ],
"IdentifierList": {
"@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.InstrumentIdentifierList",
"InstrumentIdentifiers": [ { "Identifier": "XS1591762668", "IdentifierType": "Isin" } ]
},
"Condition": {
"RatingLevel": { "IsIssue": false, "IsIssuer": true },
"ReportDateRangeType": "Last",
"DaysAgo": 7,
"IncludeInstrumentsWithNoRatings": true,
"RatingSourcesCodes": [ "FDL", "FDS", "FDT", "MCD", "MSD", "MSS", "MST", "MSU", "MTD", "SIS", "SPI" ]
}
}
}
The response follows below. There are some ratings source codes (FDT, MCD, MSD, MSU, SIS, SPI) that deliver several results, others deliver no results (MSS, MST). That explains why you receive more than expected. Some of these have different dates, others have same dates.
If I am not mistaken:
- As ReportDateRangeType is set to Last, the DaysAgo parameter is ignored.
- When there are 2 results for the same date, it is probably because a different field (not part of this extraction) changed in that day. To see all possible fields, run a GET to this endpoint: https://hosted.datascopeapi.reuters.com/RestApi/v1/Extractions/GetValidContentFieldTypes(ReportTemplateType=ThomsonReuters.Dss.Api.Extractions.ReportTemplates.ReportTemplateTypes'BondRatings')
It is not clear to me why in some cases you get 2 results for 2 different dates with ReportDateRangeType set to Last.
Hope this helps.
Added info following investigation by Gareth Teage:
Adding field "Issuer Rating Scope Code" explains the 2 results (with same or different date): 1 is for domestic currency, 1 is for foreign currency.
Here is the response for a request including field "Issuer Rating Scope Code":
{
"@odata.context": "https://hosted.datascopeapi.reuters.com/RestApi/v1/$metadata#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.ExtractionResult",
"Contents": [
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "AA-",
"Issuer Rating Effective Date": "1990-03-01",
"Issuer Rating Source Code": "FDL",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "F1+",
"Issuer Rating Effective Date": "1988-03-01",
"Issuer Rating Source Code": "FDS",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "F1+",
"Issuer Rating Effective Date": "2018-10-22",
"Issuer Rating Source Code": "FDT",
"Issuer Rating Scope Code": "D"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "F1+",
"Issuer Rating Effective Date": "2016-01-25",
"Issuer Rating Source Code": "FDT",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "Aa2",
"Issuer Rating Effective Date": "2018-07-16",
"Issuer Rating Source Code": "MCD",
"Issuer Rating Scope Code": "D"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "Aa2",
"Issuer Rating Effective Date": "2018-07-16",
"Issuer Rating Source Code": "MCD",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "P-1",
"Issuer Rating Effective Date": "1985-12-26",
"Issuer Rating Source Code": "MSD",
"Issuer Rating Scope Code": "D"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "P-1",
"Issuer Rating Effective Date": "1985-12-26",
"Issuer Rating Source Code": "MSD",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "NR",
"Issuer Rating Effective Date": "2015-09-02",
"Issuer Rating Source Code": "MSU",
"Issuer Rating Scope Code": "D"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "Aa2",
"Issuer Rating Effective Date": "2018-07-16",
"Issuer Rating Source Code": "MSU",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "P-1",
"Issuer Rating Effective Date": "2018-09-24",
"Issuer Rating Source Code": "MTD",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "A-1",
"Issuer Rating Effective Date": "2012-12-13",
"Issuer Rating Source Code": "SIS",
"Issuer Rating Scope Code": "D"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "A-1",
"Issuer Rating Effective Date": "2012-12-13",
"Issuer Rating Source Code": "SIS",
"Issuer Rating Scope Code": "F"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "A+",
"Issuer Rating Effective Date": "2012-12-13",
"Issuer Rating Source Code": "SPI",
"Issuer Rating Scope Code": "D"
},
{
"IdentifierType": "Isin",
"Identifier": "XS1591762668",
"Issuer Rating Code": "A+",
"Issuer Rating Effective Date": "2012-12-13",
"Issuer Rating Source Code": "SPI",
"Issuer Rating Scope Code": "F"
}
],
"Notes": [
"Extraction Services Version 12.2.39833 (738e4c2fbb28), Built Nov 1 2018 10:43:11\r\nProcessing started at 21112018 09:45:22 AM.\r\nUser ID: 33314\r\nExtraction ID: 333180013\r\nSchedule: _OnD_0x066a87147a801e91 (ID = 0x066a87147bc01e91)\r\nInput List (1 items): _OnD_0x066a87147a801e91 (ID = 066a87147ac01e91) Created: 21112018 09:45:20 AM Last Modified: 21112018 09:45:20 AM\r\nSchedule Time: 21112018 09:45:21 AM\r\nReport Template (10 fields): _OnD_0x066a87147a801e91 (ID = 0x066a87147aa01e91) Created: 21112018 09:45:20 AM Last Modified: 21112018 09:45:20 AM\r\nProcessing completed successfully at 21112018 09:45:22 AM, taking 0.368 Secs.\r\nExtraction finished at 21112018 08:45:22 AM UTC, with servers: x02a01, QSHC16 (0.1 secs), QSSHA1 (0.0 secs)\r\nUsage Summary for User 33314, Client 11122, Template Type Ratings\r\nBase Usage\r\n Instrument Instrument Terms Price\r\n Count Type Subtype Source Source\r\n------- ----------------------------------- ---------------------------- -------------- ----------------------------------------\r\n 1 Corporate Investment Grade N/A N/A\r\n-------\r\n 1 Total instrument charged.\r\n 0 Instruments with no reported data.\r\n=======\r\n 1 Instrument in the input list.\r\n"
]
}
@bernard.thouin
Hi Bernard,
Please would you have the identifier(s) you are using, and also the call that you are using that will help replicate what hopefully you are similarly seeing and help advise.
Best regards,
Gareth
Yes here is the code (VB.Net)
extractionRequest = New RatingsExtractionRequest With {.IdentifierList = instrumentList, .ContentFieldNames = fieldNames,
.Condition = New RatingsCondition With {
.RatingLevel = New RatingLevel With {.IsIssue = False, .IsIssuer = True},
.DaysAgo = 7,
.ReportDateRangeType = ReportDateRangeType.Last,
.IncludeInstrumentsWithNoRatings = True,
.RatingSourcesCodes = ratingCodes}}
The instrumentList has only one entry:
instrumentList.InstrumentIdentifiers(0).Identifier = "XS1591762668"
instrumentList.InstrumentIdentifiers(0).IdentifierType = Isin {23}
There is only one field:
? fieldNames.Count
1
? fieldNames(0)
"Issuer Rating Code"
The ratingCodes holds the following 11 rating codes:
FDL
FDS
FDT
MCD
MSD
MSS
MST
MSU
MTD
SIS
SPI
here the proof:
? ratingCodes.count
11
And yet I get 15 rows as a result:
? extractedRows.Count
15
with the first 4 being empty, and all other one filled like this:
Issuer Rating Code.Fitch Long-term Issuer Default Rating=
Issuer Rating Code.Fitch Short-term Issuer Default Rating=
Issuer Rating Code.Fitch Short-term Debt Rating=
Issuer Rating Code.Moody's Long-term Bank Deposit=
Issuer Rating Code.Moody's Short-term Bank Deposit=Aa2
Issuer Rating Code.Moody's Short-term Issuer Rating=Aa2
Issuer Rating Code.Moody's Short-term Issue Credit Rating=P-1
Issuer Rating Code.Moody's Senior Unsecured=P-1
Issuer Rating Code.Moody's Short-term Debt=NR
Issuer Rating Code.S&P Short-term Issuer Credit Rating=Aa2
Issuer Rating Code.S&P Long-term Issuer Rating=P-1
A-1
A-1
A+
A+
First part before equal sign is produced by me, concatenating the field name and the description of the rating code, although I assumed that the first row returned corresponded to the first rating code, FDL, because I don't know how to find out programmatically which row corresponds to which code, so it can very well be that actually the last 11 rows correspond to my 11 codes...
Hope you can make sense out of all this !
regards
Bernard
Hi Christiaan
Thanks a lot for your help, in the mean time I had found out about the effective date and the source, I still get a number of double answers with exactly the same data (same rating code, source code and effective date), very annoying, but at least it works.
Regards
Bernard
@bernard.thouin @Christiaan Meihsl
Hi,
I've done a bit of further investigation on what the backend looks like in terms of this Issuer-level Ratings reference data. It appears that in the backend the Primary Key cluster on the source would equate to DSS equivelent fields:
- Issuer ID
- Issuer Rating Effective Date
- Issuer Rating Source Code and
- Issuer Rating Scope Code
Adding the addtional DSS field of "Issuer Rating Scope Code" to the field list will help make the distinction between each record post-extraction.
Best regards,
Gareth
@gareth.teage, thank you for this investigation, it is very useful to get a better understanding of what we observe. I will amend the accepted answer to include this information.
Thanks Christiaan, I will add that to my field list and try to handle it properly :)
Regards
Bernard
@bernard.thouin, I amended the accepted answer to include the result of the investigation by Gareth. Adding the "Issuer Rating Scope Code" field explains the duplicates we observed: 1 for Domestic and 1 for Foreign currency.