Symbology (ISIN/CUSIP/SEDOL) conversion to RIC using DataScope

Gurpreet Bal
Platform Application Developer Platform Application Developer

This is a part of series of article which describes how to convert instrument symbology from one identifier to another. This particular article shows the use of DataScope Select (DSS) REST API for conversion. DSS is an internet-hosted product that offer unparalleled access to global pricing, validated terms and conditions, historical data content, corporate actions, cross-reference data and entity data.

Instrument identifiers can be referred to using several coding systems. LSEG products use RIC (Instrument Codes), which uniquely identify financial instruments, including where they are traded. As an example, IBM.N identifies the IBM stock traded on NYSE, and IBM.L identifies the IBM stock traded on LSE. The RIC syntax here is <ticker>.<exchange>, which is the most common syntax in use, but there are cases where the syntax is different (options, futures, FX, etc.).

Several other codes are in use in the financial world (ISINSedolCusip, etc.). One of the popular ones is ISIN (International Securities Identification Number) which also uniquely identifies securities, but does not identify the venue (or exchange) where they are traded. For example, US4592001014 identifies the IBM stock (whatever venue it is traded on).

Often times, there is a need to convert one type of identifier to another one. For e.g. The Real-Time Marketdata Distribution System (RTMDS) system uses RIC codes as an instrument identifier. However, many other applications may use other identifiers types and there is a need to map those identifiers to RIC, before requesting streaming market data. This article will explain how we can retrieve the RIC of an instrument from other instrument types using the DSS REST API with Python script and JSON HTTP requests.

Input and Output files

In this article we will build a sample application, which can read a CSV file with financial identifiers, and produce another CSV file in the output, enriching it with additional details like RIC code, Company Name and Currency Code etc. For the ease of use the sample application is developed in Python, but any programming language which supports REST API calls can be used. We will use DataScope Select for this conversion, so the user will need DSS credentials to test this sample.

The input CSV file should be of the format (Identifier Type, Identifier), as shown:

    	
            

Sedol,2126067

Cusip,459200101

Isin,JP3633400001

Isin,US0378331005

Cusip,172967424

.

.

.

Once the process sample reads the input list, it parses it into a valid DSS JSON message, sends out the request for the TermsAndConditionsExtractionRequest. This type of extraction request can be used to request various types of fundamental data for an instrument. We will use TermsAndConditionsExtractionRequest to get the primary RIC, company name and currency etc. The response JSON message is parsed out and written as an output CSV file. The output file will have the format as shown:

    	
            

IdentifierType,Identifier,RIC,CUSIP,ISIN,SEDOL,Company Name,Currency Code

Sedol,2126067,TRI.N,884903105,CA8849031056,2126067,ThomsonReuters,USD

Cusip,459200101,IBM.N,459200101,US4592001014,2005973,IBM,USD

Isin,JP3633400001,7203.T,None,JP3633400001,6900643,Toyota,JPY

Isin,US0378331005,AAPL.OQ,037833100,US0378331005,2046251,Apple,USD

.

.

.

Note that we have chosen a few extra fields like Currency etc, but there are many more like listing primary exchange etc, which can also be included.

Step by step process

To convert the symbols, the first step is to read the input CSV file and load it into a list of dictionaries, which associates the symbol with the identifier type (Sedol, ISIN, CUSIP etc). The Python csv module is used to parse the input file.

Next, the script logs into the DSS servers and receives an OAuth access token. This token is needed for all subsequent API calls, or else the server will reject the TermsAndConditionsExtractionRequest api call. The login mechanism and the asynchronous HTTP GET and POST methods are implemented in a helper file named DSSDefines. This helps in clean separation of business logic from the API code. The helper function DSS_login takes in username and password and exchanges it for a token.

    	
            # Login to DSS and get a get an OAuth access token
DSS_login(uName, password)

Once the OAuth token has been successfully received, we can perform the symbology conversion. The key API call for conversion is the On Demand Extraction - which is a HTTP POST method and takes in a JSON request message in a certain format. The JSON message identifies the extraction type - TermsAndConditionsExtractionRequest, passes in the list of identifiers, passes in the list of fields to be extracted and also specifies what type of validation should be performed. Here is the message format:

    	
            

{

  "ExtractionRequest": {

    "@odata.type": "#DataScope.Select.Api.Extractions.ExtractionRequests.TermsAndConditionsExtractionRequest",

    "ContentFieldNames": [ --- list of all the fields, like BID, ASK, CUSIP, RIC separated by comma --- ],

    "IdentifierList": {

      "@odata.type": "#DataScope.Select.Api.Extractions.ExtractionRequests.InstrumentIdentifierList",

      "InstrumentIdentifiers": [ --- list of all the instruments for which above fields should be extracted --- ],

      "ValidationOptions": {

          --- set various validation switches on/off --- 

      }

    }

  }

}

The On Demand Extraction can be sent in two forms - Raw Extraction and Extract with Notes. The URI /Extractions/ExtractWithNotes will also includes the summary section in the resultset, which contains information about the server version, instrument usage quota and other pertinent information about the data. Complete request message for the above list of instruments would be like this:

    	
            

{

  "ExtractionRequest": {

    "@odata.type": "#DataScope.Select.Api.Extractions.ExtractionRequests.TermsAndConditionsExtractionRequest",

    "ContentFieldNames": ["RIC", "CUSIP", "ISIN", "SEDOL", "Company Name", "Currency Code"],

    "IdentifierList": {

      "@odata.type": "#DataScope.Select.Api.Extractions.ExtractionRequests.InstrumentIdentifierList",

      "InstrumentIdentifiers": [{

          "IdentifierType": "Sedol",

          "Identifier": "2126067"

        }, {

          "IdentifierType": "Cusip",

          "Identifier": "459200101"

        }, {

          "IdentifierType": "Isin",

          "Identifier": "JP3633400001"

        }, {

          "IdentifierType": "Isin",

          "Identifier": "US0378331005"

        }, {

          "IdentifierType": "Cusip",

          "Identifier": "172967424"

        }

      ],

      "ValidationOptions": {

        "AllowHistoricalInstruments": true,

        "AllowInactiveInstruments": true,

        "AllowOpenAccessInstruments": false

      },

      "UseUserPreferencesForValidationOptions": false

    }

  }

}

Asynchronous mechanism

The asynchronous mechanism provides the support for long running requests. Since long running requests like extractions, require the connection to remain open for the duration of the request, firewalls and routers can drop the idle connections causing the user code to loose the context and data. For long running requests periodic status updates are provided along with and the ability to cancel the request. In addition the asynchronous protocol supports polling, status and progress updates, along with the ability to cancel a request.

An asynchronous request may function like a synchronous request, if the response is available immediately. For requests which take a long time to complete, the server responds with a HTTP code 202, indicating that the request was accepted. The 202 response also includes a monitor URL in the "Location" header. A GET request against the monitor URL will return the response payload, or if the result is not yet available, another 202 will be returned with a new location url. To use this asynchronous functionality, the request message to the server must include a header prefer: respond-async.

The code to handle async http post is implemented in the DSS_postAsync helper function.

DSS server provides the following response to our request:

    	
            

{

  '@odata.context': 'https://selectapi.datascope.refinitiv.com/RestApi/v1/$metadata#DataScope.Select.Api.Extractions.ExtractionRequests.ExtractionResult',

  'Contents': [{

      'IdentifierType': 'Sedol',

      'Identifier': '2126067',

      'RIC': None,

      'CUSIP': None,

      'ISIN': None,

      'SEDOL': None,

      'Company Name': None,

      'Currency Code': None

    }, {

      'IdentifierType': 'Cusip',

      'Identifier': '459200101',

      'RIC': 'IBM.N',

      'CUSIP': '459200101',

      'ISIN': 'US4592001014',

      'SEDOL': '2005973',

      'Company Name': 'IBM',

      'Currency Code': 'USD'

    }, {

      'IdentifierType': 'Isin',

      'Identifier': 'JP3633400001',

      'RIC': '7203.T',

      'CUSIP': None,

      'ISIN': 'JP3633400001',

      'SEDOL': '6900643',

      'Company Name': 'Toyota',

      'Currency Code': 'JPY'

    }, {

      'IdentifierType': 'Isin',

      'Identifier': 'US0378331005',

      'RIC': 'AAPL.OQ',

      'CUSIP': '037833100',

      'ISIN': 'US0378331005',

      'SEDOL': '2046251',

      'Company Name': 'Apple',

      'Currency Code': 'USD'

    }, {

      'IdentifierType': 'Cusip',

      'Identifier': '172967424',

      'RIC': 'C.N',

      'CUSIP': '172967424',

      'ISIN': 'US1729674242',

      'SEDOL': '2297907',

      'Company Name': 'Citigroup',

      'Currency Code': 'USD'

    }

  ],

  'Notes': ['Extraction Services Version 18.1.1.47398 .....']

}

Now, the symbology conversion application has to loop through the Contents array to split out the instruments and parse it into a CSV format for writing.

Data limits

The Datascope product enforces limits on the amount of data that can be extracted, and also how frequently it can be extracted, to ensure an even quality of service to all the users. Please refer to the Best Practices and Limits for LSEG DataScope Select document to understand these limits.

Testing

To test the provided Symbology conversion example, load the iPython notebook in the Jupyter of VSCode editor. Enter your DSS credentials in the notebook and step through all the cells. The notebook will create an output CSV file with the result if no errors are encountered.

Summary

This article shows how to use DSS to perform symbology conversion. The provided sample will only retrieve the Primary RIC of an instrument. For users requiring RIC's for other venues, can enhance it by providing the desired trading venue as an input parameter. Future articles in this series will explore the use of other products like Data Platform to perform the symbology conversion.

DSS Symbology conversion notebook