question

Upvotes
Accepted
5 8 12 12

Corporate Actions: How to get the closest 'Shares amount' value to given date for a symbol

Hi,

In the context of a Corporate Actions data retrieval proccess, is there a way of getting (only) the closest (lastest) value of the Shares Amount field for a given symbol (at a given date too)?

Any feedback will be appreciated.

Regards,

Ricardo

tick-history-rest-apirest-apicorporate-actions
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Accepted
13.7k 26 8 12

@ricardo.siguero,

I analyzed the differences between your 2 lists:

29 common instruments:

  • No issue for these.

11 are in the last results but not in the range results:

This is normal, because their shares amount date is out of the range (2016-05-24 to 2017-05-24) specified in the query:

  • AENA.MC: shares amount date 2015-02-11
  • ANA.MC: shares amount date 2012-06-06
  • DANO.PA: shares amount date 2017-06-12
  • DIDA.MC: shares amount date 2015-10-07
  • ENAG.MC: shares amount date 2011-12-31
  • ENEI.MI: shares amount date 2016-04-01
  • ENGIE.PA: shares amount date 2014-12-15
  • ENI.MI: shares amount date 2012-07-25
  • GAS.MC: shares amount date 2012-06-29
  • ITX.MC: shares amount date 2014-07-28
  • RENA.PA: shares amount date 2011-12-31

1 is in the range results and not in the last results: ABE.MC:

  • Range result includes 2 records with a shares amount of 990381308, on 2016-05-30 and 2016-06-24, with description Listed.
  • Last result does includes any record for a Shares Amount Type LIS (Listed) (i.e. a Corax type SHO (Shares Outstanding)) for ABE.MC, whatever the date.

I'm not a data specialist, I am unable to explain this discrepancy. Please open a content related enquiry via My Account or call the Thomson Reuters Help Desk directly.

Additional explanation to clarify:

  • A last request returns the last record for every different corax type, whatever the date.
  • A range request returns all records for every different corax type, for the selected date range.

As you observed, these are different criteria sets, that deliver different results.

PS: sorry for the belated response, I was on vacation.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
13.7k 26 8 12

@ricardo.siguero,

Here is an example to receive the last value:

Body:

{
  "ExtractionRequest": {
    "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.CorporateActionsStandardExtractionRequest",
    "ContentFieldNames": [ "Corporate Actions Type", "Shares Amount", "Shares Amount Type", "Shares Amount Type Description", "Shares Amount Date" ],
    "IdentifierList": {
      "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.InstrumentIdentifierList",
      "InstrumentIdentifiers": [
      { "Identifier": "ALVG.DE", "IdentifierType": "Ric" },
      { "Identifier": "IBM.N", "IdentifierType": "Ric"}
      ]
    },
    "Condition": {
      "ReportDateRangeType": "Last",
      "ExcludeDeletedEvents": true,
      "IncludeCapitalChangeEvents": false,
      "IncludeDividendEvents": false,
      "IncludeEarningsEvents": false,
      "IncludeMergersAndAcquisitionsEvents": false,
      "IncludeNominalValueEvents": false,
      "IncludePublicEquityOfferingsEvents": false,
      "IncludeSharesOutstandingEvents": true,
      "IncludeVotingRightsEvents": false,
      "CorporateActionsCapitalChangeType": "CapitalChangeExDate",
      "CorporateActionsDividendsType": "DividendPayDate",
      "CorporateActionsEarningsType": "PeriodEndDate",
      "ShareAmountTypes": [ "Issued", "Listed" ]
    }
  }
}

Result:

{
    "@odata.context": "https://hosted.datascopeapi.reuters.com/RestApi/v1/$metadata#Collection(ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.ExtractionRow)",
    "value": [
        {
            "IdentifierType": "Ric",
            "Identifier": "ALVG.DE",
            "Corporate Actions Type": "SHO",
            "Shares Amount": 446101300,
            "Shares Amount Type": "ISS",
            "Shares Amount Type Description": "Issued",
            "Shares Amount Date": "2017-09-19"
        },
        {
            "IdentifierType": "Ric",
            "Identifier": "IBM.N",
            "Corporate Actions Type": "SHO",
            "Shares Amount": 931940297,
            "Shares Amount Type": "OUT",
            "Shares Amount Type Description": "Outstanding",
            "Shares Amount Date": "2017-06-30"
        }
    ]
}

The data matches what is in the GUI, see the screenshot:

For a specific date (or date range) you could replace this line:

      "ReportDateRangeType": "Last",

With the following:

      "ReportDateRangeType": "Range",
      "QueryStartDate": "2017-09-19T00:00:00.000Z",
      "QueryEndDate": "2017-09-19T00:00:00.000Z",

For the preceding example it would only return the record for ALVG.DE. The IBM.N record would not be included because the shares amount date (June 30 2017) is outside of the range specified in the query. If you do not know the Shares Amount date then you will need to make a query with a large range, and then look at the dates in the results to keep the result(s) you require.


icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
5 8 12 12

Thank you, @Christiaan Meihsl for your previous response...

We are using the Stored & scheduled approach in our TRTHv2 data accesses, so the endpoint and report template used, for this case, are shown below:

/Extractions/CorporateActionsStandardReportTemplates
{
    "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ReportTemplates.CorporateActionsStandardReportTemplate",
    "ShowColumnHeaders": true,
    "Name": "catemplate01",
    "Headers": [
        
    ],
    "Trailers": [
        
    ],
    "ContentFields": [
        {
            "FieldName": "RIC",
            "Format": null
        },
        {
            "FieldName": "Shares Amount",
             "Format": {
                        "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ReportTemplates.ContentFieldNumberFormat",
                        "DecimalSeparator": "Period",
                        "UseThousandSeparator": false,
                        "UseTrailingZero": false
                        }
            
        },
        {
            "FieldName": "Shares Amount Type",
            "Format": null
        },
        {
            "FieldName": "Shares Amount Type Description",
            "Format": null
        },
        {
            "FieldName": "Shares Amount Date",
            "Format": null
        },
        {
            "FieldName": "Corporate Actions Type",
            "Format": null
        }
    ],
    "Condition": {
        "ReportDateRangeType": "Last",
        "ExcludeDeletedEvents": true,
        "IncludeCapitalChangeEvents": false,
        "IncludeDividendEvents": false,
        "IncludeEarningsEvents": false,
        "IncludeMergersAndAcquisitionsEvents": false,
        "IncludeNominalValueEvents": false,
        "IncludePublicEquityOfferingsEvents": false,
        "IncludeSharesOutstandingEvents": true,
        "IncludeVotingRightsEvents": false,
        "CorporateActionsCapitalChangeType": "CapitalChangeExDate",
        "CorporateActionsDividendsType": "DividendPayDate",
        "CorporateActionsEarningsType": "PeriodEndDate"

    }
}

The instrument is populated with more than two-hundred items, including the following:

    {
        "IdentifierType": "Ric",
        "Identifier": "POP.MC"
    }, {
        "IdentifierType": "Ric",
        "Identifier": "SABE.MC"
    }, {
        "IdentifierType": "Ric",
        "Identifier": "SAN.MC"
    },

An excerpt of the CSV file got from the response is shown below:

...
SABE.MC,,,,,DIV
SABE.MC,5616151196,LIS,Listed,17/11/2016,SHO
SABE.MC,,,,,MNA
SABE.MC,5318076577,,,,MNA
SABE.MC,,,,,EAR
SABE.MC,,,,,CAP
SAN.MC,4078297870,,,,MNA
SAN.MC,,,,,MNA
SAN.MC,,,,,MNA
SAN.MC,,,,,DIV
SAN.MC,,,,,VOT
SAN.MC,,,,,PEO
SAN.MC,,,,,NOM
SAN.MC,,,,,CAP
SAN.MC,TRN,LIS,Listed,31/07/2017,SHO
SAN.MC,,,,,EAR
...

where the SAN.MC symbol shows, for the Shares Amount Type = 'LIS' (the only Shares Amount Type we are focusing on), that the Shares Amount value is set to 'TRN'.

How can we get the related numeric value for such amount in that case?

Thank you very much for your attention and help,

Ricardo

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ricardo.siguero

The "TRN" likely indicate “Truncated” meaning that the data has be truncated due to restriction of the field width available in the report template for the respective field.

The default integer places is only 10, but the share amount of SAN.MC likely is more than that.

You may increase the integer places to '50' (maximum) for the share amount field.

{
"FieldName": "Shares Amount",
"Format": {
...
     "DecimalSeparator": "Period",
     "UseThousandSeparator": false,
     "UseTrailingZero": false,
     "IntegerPlaces": 50
}

Upvotes
5 8 12 12

We've been working in the development of the data access mentioned above, and I'd like to share our experience about it:

I. Approaches

We've tried two different approaches:

(1) Based on the 'Last' qualifier

{
    "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ReportTemplates.CorporateActionsStandardReportTemplate",
    "ShowColumnHeaders": true,
    "Name": "E88846_REQUESTCORPORATEACTIONS_207872_TEMPL_93592",
    "Headers": [],
    "Trailers": [],
    "ContentFields": [{
        "Format": null,
        "FieldName": "RIC"
    }, {
        "Format": {
            "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ReportTemplates.ContentFieldNumberFormat",
            "DecimalSeparator": "Period",
            "IntegerPlaces": "30",
            "UseThousandSeparator": false
        },
        "FieldName": "Shares Amount"
    }, {
        "Format": null,
        "FieldName": "Shares Amount Type"
    }, {
        "Format": null,
        "FieldName": "Shares Amount Type Description"
    }, {
        "Format": null,
        "FieldName": "Shares Amount Date"
    }, {
        "Format": null,
        "FieldName": "Corporate Actions Type"
    }],
    "Condition": {
        "ReportDateRangeType": "Last",
        "ExcludeDeletedEvents": true,
        "IncludeCapitalChangeEvents": true,
        "IncludeDividendEvents": true,
        "IncludeEarningsEvents": true,
        "IncludeMergersAndAcquisitionsEvents": true,
        "IncludeNominalValueEvents": true,
        "IncludePublicEquityOfferingsEvents": true,
        "IncludeSharesOutstandingEvents": true,
        "IncludeVotingRightsEvents": true,
        "CorporateActionsCapitalChangeType": "CapitalChangeExDate",
        "CorporateActionsDividendsType": "DividendPayDate",
        "CorporateActionsEarningsType": "PeriodEndDate"
    }
}

(2) Based on a given date range

Under it, the range's end-date is set to yesterday's


{
    "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ReportTemplates.CorporateActionsStandardReportTemplate",
    "ShowColumnHeaders": true,
    "Name": "E39182_REQUESTCORPORATEACTIONS_207872_TEMPL_45516",
    "Headers": [],
    "Trailers": [],
    "ContentFields": [{
        "Format": null,
        "FieldName": "RIC"
    }, {
        "Format": {
            "@odata.type": "#ThomsonReuters.Dss.Api.Extractions.ReportTemplates.ContentFieldNumberFormat",
            "DecimalSeparator": "Period",
            "IntegerPlaces": "30",
            "UseThousandSeparator": false
        },
        "FieldName": "Shares Amount"
    }, {
        "Format": null,
        "FieldName": "Shares Amount Type"
    }, {
        "Format": null,
        "FieldName": "Shares Amount Type Description"
    }, {
        "Format": null,
        "FieldName": "Shares Amount Date"
    }, {
        "Format": null,
        "FieldName": "Corporate Actions Type"
    }],
    "Condition": {
        "ReportDateRangeType": "Range",
        "QueryStartDate": "2016-05-24",
        "QueryEndDate": "2017-05-24",
        "ExcludeDeletedEvents": true,
        "IncludeCapitalChangeEvents": true,
        "IncludeDividendEvents": true,
        "IncludeEarningsEvents": true,
        "IncludeMergersAndAcquisitionsEvents": true,
        "IncludeNominalValueEvents": true,
        "IncludePublicEquityOfferingsEvents": true,
        "IncludeSharesOutstandingEvents": true,
        "IncludeVotingRightsEvents": true,
        "CorporateActionsCapitalChangeType": "CapitalChangeExDate",
        "CorporateActionsDividendsType": "DividendPayDate",
        "CorporateActionsEarningsType": "PeriodEndDate"
    }
}

II. Results

The lists of instruments we got from the data accesses below differs considerably (attached the two lists, where only instruments with entries as 'Listed' have been included)

Number of instruments (Date Range): 30

Number of instruments (Last): 40

It's important to say that the instrument list for the 'Date Range' approach is not a subset of the 'Last' list from the the other one (some instruments appears in one that do not in another, and vice-versa)


So, our question is: shouln't those queries be equivalent? shouldn't I get the same list of instruments and same values?

Thanks, in advance, for your attention and help.

Ricardo
range.txt last.txt


range.txt (258 B)
last.txt (346 B)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
1 1 1 0

Due to an outage of the Thomson Reuters Developer Community email notification system from 2:30 PM CDT on November 29th until 9:00 AM CDT on November 30th we are posting to all questions updated during this timeframe in order to ensure all customers receive all relevant email updates. Please review this question, and its associated answers, in order to ensure you are aware of any possible updates that may have occurred during this outage. We apologize for the delay and any inconvenience this has caused.

Matthew Logterman, J.D.

Sr. Product Manager, Thomson Reuters Developer Community

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.