CUSIP querying failures

mjg
mjg Explorer
in Eikon Data APIs

hello, because querying using TickerSymbol trying to get RIC isn't bulletproof, i thought i might try relying on CUSIP.

it turns out that is not bulletproof either.

here is a list of CUSIPs that fail using this code:

'G96629103', 'G9892K100', 'N94209108', '81807M205', 'M98068105', 'M9T951109', 'N3144W105', 'M97628107', 'G98239109', 'G9525W109', 'Y95308105', 'N30577105', 'G48833118'

    response = symbol_conversion.Definition(symbols=cusips,
                                            from_symbol_type=symbol_conversion.SymbolTypes.CUSIP
                                            ).get_data()

some of these CUSIPs do come up in Data Item Browser if you punch the associated Ticker Symbol in, example:

G96629103 ticker WTW


some of these CUSIPS don't come up in Data Item Browser, but shuold. example:

81807M205 ticker YQ

----


i am looking for some way, any way, to reliably be able to take EITHER a U.S. Ticker Symbol (still struggling with that despite some good help from @marcin.bunkowski here):

https://community.developers.refinitiv.com/questions/112362/some-nyse-rics-returning-without-suffix-why.html


OR, i can use a CUSIP instead if that makes this a more bulletproof join/query method. but i am shocked that CUSIP seems to be just as difficult as Ticker Symbol.

---


In the end I want to make queries for attributes like Market Cap, Dividend Record Date, Fund Inception Date, and so on, but to get the data back I Expect to get back for the security I am entering. If Ticker dodesn't work, and CUSIP doesn't work, what will?

Tagged:

Best Answer

  • m.bunkowski
    m.bunkowski LSEG
    Answer ✓

    Hi @mjg

    I have tried to run your example several times and indeed, intermittently I was getting the same error message that was coming from the backend service that can be overloaded. I have observed that it was less frequent, when you request one field 'TR.RIC'. That can be later on successfully used in your further calls.

    Alternatively, you can reduce the number of instruments in a single call.

    You can also do a try-except block to catch errors and retry like below

    max_steps = 3
    step = 0
    while step < max_steps:
        try:
            df = rd.get_data(cusips, ['TR.TickerSymbol', 'TR.RIC', 'TR.CusipExtended','TR.OrganizationID'])
            print(df)
            break

        except rd.errors.RDError as e: 
            print("RDError code :", e.code) 
            print("RDError message:", e.message)
            step +=1 
            continue

Answers

  • mjg
    mjg Explorer

    could i do something like, where the CUSIP query fails, I try again using "CINS" or "Cusip Extended" or something? would that cover both cases here?

  • mjg
    mjg Explorer

    again, all i am trying to accomplish in the end is, if i can lock down a US Ticker Symbol on my side OR a CUSIP/CINS/CUSIP Extended on my side, can i reliably query eikon for various data (example: market cap, dividend ex date, fund inception date, price to earnings ratio... typical equity or ETF field data)... and be confident the security i am getting results for is the security i think i am getting results for, and of course, that if i do indeed have a valid security, that i get data back and not nothing.

    i am trying to be a good user and figure out the different ways of doing this.


    it seems possibly from a different post this is a potential path- but i hope to not have to 'filter through' the results in the big payload this delivers... hoping for something reliable/straightforward

    symbologylookup_url = 'https://api.refinitiv.com/discovery/symbology/v1/lookup';

    request_body = {"from": [{"identifierTypes": ["CinsNumber"],
                              "values": ["G96629103", "81807M205"]}],
        "to": [{"identifierTypes": ["RIC"]}],
        "reference": ["name", "status", "classification"],
        "filter": {"status": "active"},
        "type": "auto"}

    request_definition = rd.delivery.endpoint_request.Definition(
        method=rd.delivery.endpoint_request.RequestMethod.POST,
        url=symbologylookup_url,
        body_parameters=request_body
    )
    response = request_definition.get_data()
    mydata = response.data.raw['data']
    print (mydata)
  • m.bunkowski
    m.bunkowski LSEG

    Hi @mjg

    It looks that using the rd library, the CUSIPs from your list are recognized and sample data items are retrieved.

    import refinitiv.data as rd
    rd.open_session()

    rd.get_data(
        ['G96629103', 'G9892K100', 'N94209108', '81807M205', 'M98068105', 'M9T951109', 'N3144W105', 'M97628107', 'G98239109', 'G9525W109', 'Y95308105', 'N30577105', 'G48833118'],
        ['TR.CompanyMarketCapitalization','TR.DivRecordDate','TR.CommonName','TR.RIC'])

    1702888594489.png

  • mjg
    mjg Explorer

    thank you very much @m.bunkowski for your continued assistance. that looks promising. if i wanted to isolate this call to just serve symbol conversion purposes, what fields might i query? i am guessing:


    'TR.TickerSymbol', 'TR.RIC', 'TR.CusipExtended',

    'TR.OrganizationID'


    assuming TickerSymbol and RIC are straightforward, to 'query back' the CUSIP, as sort of a doublecheck, would i use 'TR.CusipExtended'? i recall a year or so ago - perhaps in the dealscreener table - i made use of 'Perm ID'. it looks like maybe

    'TR.OrganizationID' is the field to try for that one? it would be amazing if, as we are now hoping, by just providing a bulletproof list of CUSIPs, i can generate back a table with a few of the relevant conversions. those are the ones i can think of but it's not easy to tell in data item library which fields to pass.

    thank you.

  • mjg
    mjg Explorer
    https://community.developers.refinitiv.com/discussion/comment/112423#Comment_112423

    in my (limited) initial test, the CUSIP I pass in does always equal the TR.CusipExtended being returned. a good sign.

  • m.bunkowski
    m.bunkowski LSEG
    https://community.developers.refinitiv.com/discussion/comment/112423#Comment_112423

    Hi @mjg

    Of course it may depend on your workflow but as a rule of a thumb the most universal approach would be to convert the symbol to a RIC code (TR.RIC). That is a symbol, widely used in different type of calls and enables you to precisely identify a security at a quote level.

  • mjg
    mjg Explorer

    ok so now i have a question about the structure get_data returns

    if i am querying for these fields with a list of 1600 CUSIPs, i get back a dataframe with 30,000 + rows, and multiple rows per cusip ... why would the get_data call return multiple rows per instrument? ... 778 even, for the max count case?

    result['Instrument'].value_counts()
    78462F103    778
    464287200    595
    46090E103    492
    81369Y506    488
    464287432    486
                ... 
    316092386      1
    316092238      1
    00214Q203      1
    00214Q401      1
    35473P470      1
        field_list = [
            'TR.CommonName',
            'TR.FundInceptionDate',
            'TR.FundCompany',
            'TR.DivDate',
            'TR.DivRecordDate',
            'TR.DivAnnouncementDate',
            'TR.DivExDate'
        ]
        result = rd.get_data(cusips, field_list)
  • mjg
    mjg Explorer

    i experimented with passing RICs to that same call, and i get the same result - 36k rows ... hopefully this is an easy thing to solve?

  • mjg
    mjg Explorer

    i searched and foudn that some fields take arguments that specify how many / what kind of results to give. that makes sense. for example, EndNum=1 here 'fixes' the problem - except that i probably want the _first_ fund inception date, not the "last" fund inception date. can i pass arguments to that field that provide 1 result - the earliest valid result?

            'TR.FundInceptionDate(EndNum=1)',
  • mjg
    mjg Explorer

    related question, how would i reduce the output from this field to one row? i don't see any parameter equivalent to EndNum for that one. thanks

    'TR.FundCGPayment'
  • bob.lee
    bob.lee LSEG
    https://community.developers.refinitiv.com/discussion/comment/112428#Comment_112428

    Hi @mjg ,Do you have the sample instrument code and the complete query (qet_data call) you are using for this TR.FundCGPayment? If you do not specify any dates/periods, it should only return the latest data. I tried using a US funds as an example to get its captial gain data and I only get 1 result:

    df = rd.get_data(universe=['LP40181943'], fields=['TR.FundCGPayment', 'TR.FundCGPaymentDate'])

    Then, I got:

       Instrument  Payment Value Payment Date
    0  LP40181943        3.83992   2023-12-11


    It seems you are mixing funds and non-funds fields for your calls. I think it is better to separate funds from other asset types. For funds data sourced from fund companies (i.e. Lipper data), your field names should all starts with "TR.Fund".

  • m.bunkowski
    m.bunkowski LSEG
    https://community.developers.refinitiv.com/discussion/comment/112418#Comment_112418
    Hi @mjg

    There is no additional parameter to sort the results using the rd.get_data() call however you can still use the functionality of the dataframe to obtain the desired format like using sorting and filtering.
    What is more similar to comment by @bob.lee you are using a fund fields with equity fields and that may create an unexpectedly rich outputs.

    Your follow up questions are more data related therefore I do recommend to open a case with the content support that can assist you. Please open a ticket with MyRefinitiv.

  • mjg
    mjg Explorer

    hi @m.bunkowski how about one more technical question

    in a _non deterministic_ way, sometimes i get these errors. know why? i would say i get the errors one out of every 10 queries, but when they c ome, they come in bunches. i tried changing the size of my cusip lists from 100, to 300, etc, but the size of the list or the constituents of the list doesn't seem to change whether or not i get the error. thanks.

    code ...

    cusips = [] # [really its whatever cusip list, one example is below in error]        
    df = rd.get_data(cusips, [
                'TR.TickerSymbol', 'TR.RIC', 'TR.CusipExtended',
                'TR.OrganizationID'
            ])
    refinitiv.data._errors.RDError: Error code -1 | Backend error. 400 Bad Request Requested universes: ['09073M104', '46436E502', '049468101', '879360105', '88642R109', '74347G606', '88556E102', '30151E715', '879433829', '87918A105', '33738R118', '893641100', '87190U100', '88076W103', '891160509', '898697206', '210751103', '98973P101', '09259E108', '89237H209', '89677Q107', '89612W102', '87357P100', '46436E429', '896095106', '88224Q107', '74347B201', 'G8807B106', '88145X108', 'M8744T106', '192003101', '74933W452', '89785X101', '74347X849', '05969A105', '14574X104', '87652V109', '87650L103', 'M8737E108', '46144X487', '74365U107', '60871R209', '46138G706', '87484T108', '87427V103', '874080104', '874060205', '132061862', '88166A706', '892918103', '89346D107', '00206R102', '74347G630', '871829107', '87184Q206', '871655106', '87157D109', '87151X101', '132061201', '863667101', '87165B103', '861025104', '83356Q108', '81725T100', '854231107', '86722A103', '844895102', '85205L107', '845467109', '83088M102', '854502101', '51819L107', '83417Q204', '831754106', '82489T104', '032108888', '74347W130', '80517M109', '805111101', '82889N863', '82835P103', '92891H101', '81761L102', '46436E536', '86959K105', '46435U218', '46435G243', '464288802', '86882L204', '40054A108', '868459108', '74347G317', '868168105', '86803X204', '86765K109', '86614U100', '866674104', '867224107', '21036P108', '85916J409']. Requested fields: ['TR.TICKERSYMBOL', 'TR.RIC', 'TR.CUSIPEXTENDED', 'TR.ORGANIZATIONID']

Categories