Article

How to extract equity ownership data with DataScope Select .NET SDK

December 22,2016

Steven Peng

Last Updated: 20 Oct 2021

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 Refinitiv 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
DataScope.Select.Core.RestApi.Common.dll
DataScope.Select.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://selectapi.datascope.refinitiv.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.

Articles

Developer Tools

Use Cases

Upcoming Events

Past Events

About Us