Disclaimer

In order to reproduce the sample below, you will need to have valid credentials for DataScope Select. The source code in this article was simplified for illustration purposes, you can find the full project here.

Overview

The compliance policy of financial institutions usually restricts purchasing or owning securities issued by companies (or their subsidiaries) which were underwritten by those financial institutions. This article demonstrates how we can use the DataScope Select .NET SDK to extract all associated securities for a list of legal entities.

Solution

We are going to use a csv file as an input, which will consist of three columns:

  • security identifier type, ISN (ISIN), CSP (CUSIP) or SED (SEDOL);
  • security identifier;
  • description.

So, in our case, the file companies.csv will look like this:

ISN,GB00BH4HKS39,VODAFONE GROUP ORD
ISN,JP3633400001,TOYOTA MOTOR ORD
CSP,459200101,INTERNATIONAL BUSINESS MACHINES ORD
CSP,254687106,WALT DISNEY ORD
CSP,097751200,BOMBARDIER CL B ORD BBD.B

This file will help us get the OrgID, a Thomson Reuters issuer identifier.

We are going to use the following DataScope Select templates:

  • Terms and Conditions;
  • EntityHierarchy.

So, we are going to establish a connection to DataScope Select, upload the companies.csv input file, get a list of OrgIDs for those instruments, then extract an entity hierarchy for each OrgID to get a list of associated OrgIDs and, finally, build a full list of all instruments associated with all entities.

Code

Prerequisites

In order to start working with the DataScope Select .NET SDK you will need to download the latest SDK, extract it, copy the following files into your solution folder and reference them in your IDE:

  • Microsoft.OData.Client.dll
  • Microsoft.OData.Core.dll
  • Microsoft.OData.Edm.dll
  • Microsoft.Spatial.dll
  • Newtonsoft.Json.dll
  • ThomsonReuters.Dss.Core.RestApi.Common.dll
  • ThomsonReuters.Dss.RestApi.Client.dll

For more information on how to install the SDK have a look at the .Net SDK tutorial 1.

Authentication

Firstly, we need to establish a connection to DataScope Select host.

private static string dssUserName = "YourDSSUserId";
private static string dssUserPassword = "YourDSSPassword";
private static Uri dssUri = new Uri("https://hosted.datascopeapi.reuters.com/RestApi/v1/");
private ExtractionsContext extractionsContext;

...

DssClient dssClient = new DssClient( dssUri );
dssClient.ConnectToServer(dssUserName, dssUserPassword);

Reading the inputs

Once the connection to the host is established, our app will read the companies.csv file, parse it and create an InstrumentIdentifier list:

int counter = 0;

char[] splitchar = { ',' };

List<InstrumentIdentifier> _entityList = new List<InstrumentIdentifier>();

try
{
    System.IO.StreamReader _inFile = new System.IO.StreamReader("companies.csv");

    while ((_rdLine = _inFile.ReadLine()) != null) 
    {
        System.Console.WriteLine("===" + _rdLine);
        string[] _tokens = _rdLine.Split(splitchar);
        if ( _tokens.Length >= 2 )
        {
            IdentifierType _idType = ParseIdentityType(_tokens[0]);
            if ( _idType == IdentifierType.NONE )
            {
                Debug.Print("Invalid identity type...");
                continue;
            }

            InstrumentIdentifier _entity = new InstrumentIdentifier {
                IdentifierType = _idType,
                Identifier = _tokens[1],
                UserDefinedIdentifier = _tokens[1]
            };
            _entityList.Add(_entity);
        }
        counter++;
    }
    _inFile.Close();
}
catch (Exception e)
{
    Debug.Print("Can't open file:" + instFilePath + " to read");
    Debug.Print("Error:" + e.Message);
}

InstrumentIdentifier[] instrumentIdentifiers =  _entityList.ToArray();

Extracting issuer OrgIDs

We are going to create a field array that is going to be used in the extraction of the Terms and Conditions template

static string[] CreateRequestedTCFieldNames()
{
    string[] requestedFieldNames = 
    {
        "Instrument ID",
        "Security Description",
        "Ticker",
        "CUSIP",
        "ISIN",
        "SEDOL",
        "CIN Code",
        "Issuer Name",
        "Issuer OrgID"
    };
}

string[] requestedTCFieldNames = CreateRequestedTCFieldNames();

Now we are going to use the DataScope Select object TermsAndConditionsExtractionRequest with the above field list to extract the issuer OrgIDs into issuerOrgIdAry:

//Create an on demand extraction, of type TermsAndConditionsExtractionRequest:
TermsAndConditionsExtractionRequest extractionIssues =    new TermsAndConditionsExtractionRequest {
    IdentifierList = EntityIdentifierList.Create(instrumentIdentifiers),
    ContentFieldNames = requestedTCFieldNames
};

extractionCompanyRows = extractionsContext.Extract(extractionIssues); 

//"Issuer OrgID" is the 8th index element of requestedFieldNames object.
int _IssuerOrgIdfldIndex =  8; 
string[] issuerOrgIdAry = RetrieveIssuerOrgId(extractionCompanyRows, _IssuerOrgIdfldIndex);

foreach (ExtractionRow row in extractionCompanyRows)
{
    IEnumerable<object> rowValues = row.DynamicProperties.Select(dp => dp.Value);
    object[] fieldAry = rowValues.ToArray();
    if (fieldAry[fieldIndex] != null)
    {
        _relatedOrgIds = fieldAry[_IssuerOrgIdfldIndex].ToString();
        _issuerOrgIdList.Add( _relatedOrgIds );
    }
}
string[] issuerOrgIdAry = _issuerOrgIdList.ToArray();

Extracting entity hierarchy

We are going to use issuerOrgIdAry from the previous step in otder to create a company entity array:

List<EntityIdentifier> entityIdentifierList = new List<EntityIdentifier>();
EntityIdentifier[] entityIdentifiers = new EntityIdentifier[issuerOrgIdAry.Length];

for (int i = 0; i < issuerOrgIdAry.Length; i++)
{
    EntityIdentifier _entityIdentifier = new EntityIdentifier
    {
        IdentifierType = IdentifierType.OrgId,
        Identifier = issuerOrgIdAry[i],
        UserDefinedIdentifier = issuerOrgIdAry[i]
    };
    entityIdentifierList.Add(_entityIdentifier);
}
EntityIdentifier[] companyEntities = entityIdentifierList.ToArray();

Now, let's create a field array for this extraction:

static string[] CreateRequestedAssociatedOrgIds() {
    string[] requestedFieldNames = 
    {
        "ID",
        "ID Type",
        "Related OrgID",
        "Related Official Name",
        "Relationship Type"
    };
    return requestedFieldNames;
}

string[] requestedAssociatedOrgIds = CreateRequestedAssociatedOrgIds();

So, all the preparation at this point is done. Let us use the DataScope Select object LegalEntityHierarchyExtractionRequest to create the associated identifier extraction:

LegalEntityHierarchyExtractionRequest extractionIssues = new LegalEntityHierarchyExtractionRequest 
{
    IdentifierList = EntityIdentifierList.Create(entityIdentifiers),
    ContentFieldNames = requestedFieldNames,
    Condition = new LegalEntityHierarchyCondition()
    {
        DeltaDays = -1,
    }
};

IDssEnumerable<ExtractionRow> extractionEntityRows = extractionsContext.Extract(extractionIssues);

Compiling a list of identifiers

Let us create one list for all related identifiers:

List<string> _relatedOrgIdsList = new List<string>();

//"Related OrgID" is the 3rd index element of requestedFieldNames object.
int _RelatedOrgIdFldIndex = 3

foreach (ExtractionRow row in extractionDataRows) 
{
    IEnumerable<object> rowValues = row.DynamicProperties.Select(dp => dp.Value);

    object[] fieldAry = rowValues.ToArray();

    if (fieldAry[_RelatedOrgIdFldIndex] != null)
    {
        string _fldValue = fieldAry[fieldIndex].ToString();
        if (!_relatedOrgIdsList.Exists(e => e == _fldValue)) 
        {
            _relatedOrgIdsList.Add(_fldValue);
        }
    }
}
string[] relatedOrgIdAry = _relatedOrgIdsList.ToArray();

Extracting associated instruments

Next, let us use the TermsAndConditionsExtractionRequest object again extract to all instruments:

EntityIdentifier[] allRelatedOrgIdEntities =  new EntityIdentifier[relatedOrgIdAry.Length];

for (int i = 0; i < orgIds.Length; i++)
{
    allRelatedOrgIdEntities[i] = new EntityIdentifier
    {
        IdentifierType = IdentifierType.OrgId,
        Identifier = orgIds[i],
        UserDefinedIdentifier = ""
    };
}

TermsAndConditionsExtractionRequest extractionIssues = new TermsAndConditionsExtractionRequest 
{
    IdentifierList = EntityIdentifierList.Create(allRelatedOrgIdEntities),
    ContentFieldNames = requestedTCFieldNames
};

IDssEnumerable<ExtractionRow> extractionAllIssueRows = extractionsContext.Extract(extractionIssues);

Results

Finally, display the results in the console:

foreach (ExtractionRow row in extractionDataRows)
{
    IEnumerable<object> rowValues = row.DynamicProperties.Select(dp => dp.Value);
    rowValuesInString = String.Join("|", rowValues);

    if (rowValuesInString != string.Empty)
    {
        Console.WriteLine(rowValuesInString);
    }
}

Here is what our output will look like:

=== Request All Issues data
Returned list of field names:
Instrument ID|Security Description|Ticker|CUSIP|ISIN|SEDOL|CIN Code|Issuer Name|Issuer OrgID|
Returned field values:
13625|VOD    8.125 02/15/30|VOD|92857TAC1|US92857TAC18|B0P03D8||VODAFONE GROUP PLC|13625
13625|VOD    7.875 02/15/30|VOD|92857TAH0|US92857TAH05|0460187||VODAFONE GROUP PLC|13625
13625|VOD    8.125 02/15/30|VOD||USG9387SAN56|B019ZS8|G9387SAN5|VODAFONE GROUP PLC|13625
13625|VOD    6.250 11/30/32|VOD|92857WAB6|US92857WAB63|3235605||VODAFONE GROUP PLC|13625
13625|VOD    5.900 11/26/32 MTN|VOD||XS0158715713|3230097|G93882AH4|VODAFONE GROUP PLC|13625
13625|VOD    5.000 06/04/18 MTN|VOD||XS0169888558|3312489|G93882AQ4|VODAFONE GROUP PLC|13625
13625|VOD    4.625 07/15/18|VOD|92857WAE0|US92857WAE03|3322585||VODAFONE GROUP PLC|13625
13625|VOD    5.625 12/04/25 MTN|VOD||XS0181816652|3388361|G93882AT8|VODAFONE GROUP PLC|13625
13625|VODAFONE GROUP ORD|VOD||GB00BH4HKS39|BH4HKS3|G93882192|Vodafone Group PLC|13625
13625|VODAFONE GROUP ADR REP 10 ORD|VOD|92857W308|US92857W3088|BK019T4||Vodafone Group PLC|13625
13625|VODAFONE GROUP CEDEAR|VOD||ARDEUT110590|2496566|P3R87L339|Vodafone Group PLC|13625
13625|VOD    5.625 02/27/17|VOD|92857WAP5|US92857WAP59|B1S9JR5||VODAFONE GROUP PLC|13625
13625|VOD    6.150 02/27/37|VOD|92857WAQ3|US92857WAQ33|B1SBFX9||VODAFONE GROUP PLC|13625
13625|VOD    5.375 06/06/22 MTN|VOD||XS0304458051|B1YCFV4|G93882BR1|VODAFONE GROUP PLC|13625
13625|VOD    8.125 11/26/18 MTN|VOD||XS0400780960|B3FMYG5|G93882EC1|VODAFONE GROUP PLC|13625
13625|VOD          12/01/28 MTN|VOD||XS0401837280|B3K7WL6|G93882EL1|VODAFONE GROUP PLC|13625
13625|VOD    5.375 12/05/17 MTN|VOD||XS0432619913|B54NLL7|G93882GC9|VODAFONE GROUP PLC|13625
13625|VOD    5.450 06/10/19|VOD|92857WAS9|US92857WAS98|B5461L6||VODAFONE GROUP PLC|13625
13625|VOD    4.650 01/20/22 MTN|VOD||XS0479869744|B60XLT6|G93882GQ8|VODAFONE GROUP PLC|13625

...

Extraction completed.
Number of data rows: xxxx
Number of valid (non empty) data rows: xxxx

Conclusion

Automating similar routines with DataScope Select is quick and simple, offering you greater flexibility and is more efficient than the manual use of DataScope Select via the browser.