DIscrepancy between Eikon in Excel and in Python

Hi,

I'm using the below Formula in Excel and get the also below mentioned results (with filter Dividend Ex Date for September 2017).

=TR(Sheet2!D2:D8;"TR.DivPaymentType;TR.DivType;TR.DivExDate;TR.DivDate;TR.DivAdjustmentFactor;TR.DivAdjustmentType;TR.DivMktRecordDate";"SDate=1997-01-01 EDate=2017-12-31 DivPayType=SDI:SCA:CSA CH=Fd RH=IN";C3)

Sheet2!D2:D8 = SCC.PS, HAR.HM, 2107.TW, 2823.TW, 2867.TW, 1714.TW, 2890.TW

Dividend Ex Date Date Dividend Market Adjustment factor

12.09.2017 11.10.2017 0.25

12.09.2017 01.10.2017 0.956938

14.09.2017 01.10.2017 0.917431

14.09.2017 01.10.2017 0.920138

15.09.2017 01.10.2017 0.966184

18.09.2017 25.10.2017 0.966184

Using the same Formula in Python as below.

split_rq1 = ek.get_data(
    instruments=['SCC.PS',
                'HAR.HM',
                '2823.TW',
                '2107.TW',
                '2867.TW',
                '1714.TW',
                '2890.TW'
            ],
    fields = [
        'TR.ISINCode',
        'TR.DivPaymentType',
        'TR.DivType',
        'TR.DivExDate',
        'TR.DivDate',
        'TR.DivAdjustmentFactor',
        'TR.DivAdjustmentType',
        'TR.DivMktRecordDate'
    ],
    parameters = {
        'CH' : 'Fd',
        'SDate'  :   '1997-01-01',
        'EDate' : '2018-12-31',
        'DivPayType' :' SDI:SCA:CSA'
        # 'SDate'  :   yesterday_tr,
        # 'EDate' : enddate_tr
    }
    )

I get following results:

Date Dividend Market Adjustment factor

01.08.2017 0.25

01.03.2016 0.970874

01.10.2017 NaN

01.10.2017 0.920138

01.10.2017 NaN

25.10.2017 NaN

Could you please help me to understand why I get three NaN values in Python that are populated in Excel?

Many thanks in advance,

Best regards

RIchard Sponda

Find more posts tagged with

python

eikon

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

It's normal to retrieve NaN values,but it should match with NULL values in EXCEL.

Your issue seems to be the same than this.

Example: limit the instrument list to 1714.TW

=TR("1714.TW;"TR.DivMktRecordDate;TR.DivDate;TR.DivAdjustmentFactor";"SDate=1997-01-01 EDate=2017-12-31 DivPayType=SDI:SCA:CSA CH=Fd RH=IN";B3)

Result:

Same request with ek.get_data :

>>> split_rq1,err = ek.get_data('1714.TW', ['TR.DivMktRecordDate','TR.DivDate','TR.DivAdjustmentFactor'], parameters)

Result:

>>> split_rq1

Instrument Dividend Market Record Date Date Dividend Market Adjustment factor
0 1714.TW 1997-08-05 1997-11-06 0.947867

1 1714.TW 1998-06-30 1998-09-02 0.892857

2 1714.TW 1999-06-17 1999-08-24 0.892857

3 1714.TW 2000-09-20 2000-11-28 0.892857

4 1714.TW 2001-07-27 2001-10-16 0.952381

5 1714.TW 2002-08-09 2002-10-04 0.952381

6 1714.TW 2003-10-14 2003-12-02 0.970874

7 1714.TW 2004-10-07 2004-12-15 0.966184

8 1714.TW 2009-09-17 2009-10-28 NaN

9 1714.TW 2010-09-29 2010-11-17 NaN
10 1714.TW 2011-09-14 2011-11-15 NaN
11 1714.TW 2012-09-18 2012-11-09 NaN
12 1714.TW 2013-09-18 2013-11-11 NaN
13 1714.TW 2014-09-18 2014-10-29 NaN
14 1714.TW 2016-09-05 2016-11-03 NaN
15 1714.TW 2017-09-18 2017-10-01 NaN

The defect was raised to the development team.

All comments

It's normal to retrieve NaN values,but it should match with NULL values in EXCEL.

Your issue seems to be the same than this.

Example: limit the instrument list to 1714.TW

=TR("1714.TW;"TR.DivMktRecordDate;TR.DivDate;TR.DivAdjustmentFactor";"SDate=1997-01-01 EDate=2017-12-31 DivPayType=SDI:SCA:CSA CH=Fd RH=IN";B3)

Result:

Same request with ek.get_data :

>>> split_rq1,err = ek.get_data('1714.TW', ['TR.DivMktRecordDate','TR.DivDate','TR.DivAdjustmentFactor'], parameters)

Result:

>>> split_rq1

The defect was raised to the development team.

richard.sponda

Hi Pierre,

Do you have news form the development team?

Best Regards

Richard

richard.sponda

Hi all,

In Excel I get with following Forumula below result.

=TR("BIOX.PA";"TR.ISINCode;TR.CACorpActEventType;TR.CACorpActDesc;TR.CAAnnouncementDate;TR.CAAdjustmentFactor;TR.CARecordDate;TR.CAEffectiveDate;TR.CAExDate";"Sdate=1997-01-01 Edate=2018-12-31 CH=Fd RH=IN";C2)

Adjustment Factor Capital Change Ex Date

1 NULL

0.333333 20.09.2017

1 NULL

In python I receive with following Code below result.

split_rq1 = ek.get_data(
    instruments=['BIOX.PA'],
    fields = [
        'TR.ISINCode',
        'TR.CACorpActEventType',
        'TR.CACorpActDesc',
        'TR.CAAnnouncementDate',
        'TR.CAAdjustmentFactor',
        'TR.CARecordDate',
        'TR.CAEffectiveDate',
        'TR.CAExDate',
    ],
    parameters = {
        'CH' : 'Fd',
        'SDate'  :   '1997-01-01',
        'EDate' : '2018-12-31'}
    )

  Capital Change Announcement Date  Adjustment Factor  \
0                       2016-05-26           1.000000   
1                       2017-04-24           0.333333   
2                       2017-05-30           1.000000   


  Capital Change Record Date Capital Change Effective Date  \
0                 2017-09-19                    2016-06-09   
1                                               2017-09-20   
2                                               2017-09-01   


  Capital Change Ex Date  
0             2017-09-20  
1                         
2                         

Could you please confirm that this is due to the same issue mentioned above?

Many thanks,

Kind regards

Richard

Alex Putkov.1

Yes, this is the same issue that's been raised and acknowledged on multiple threads on this forum, e.g. https://community.developers.refinitiv.com/questions/19014/period-end-date-not-pulling-through-properly.html

richard.sponda

Hi Alex,

The workaround with switching SDate and EDate worked for the above,

however I came along another issue.

I’m interested in fetching the ‘Old
50 / New 1’ ratio for the Stock Dividend (Ex. Date 09/11/2017) of company
LEN.N.

However with the following formula in
Python I get below result.

split_rq1=
ek.get_data(instruments=['LEN.N',

],fields = ['TR.ISINCode','TR.DivPaymentType','TR.DivType','TR.DivExDate','TR.DivDate','TR.DivAdjustmentFactor','TR.DivAdjustmentType','TR.DivMktRecordDate','TR.CATermsOldShares','TR.CATermsNewShares'],parameters = {'CH'
: 'Fd','SDate'
: '2018-12-31','EDate'
: '1997-01-01','DivPayType'
:'
SDI:SCA:CSA'}

)

Is there any workaround for this?

Many thanks in advance,

Best regards

Richard Sponda

Alex Putkov.1

Hi Richard,
I must admit I'm rather lost in what you're trying to get here. Where would ‘Old 50 / New 1’ ratio come from in this example. I see no capital change event for Lennar Corp that would result in 50/1 old stock to new stock ratio.
On a totally separate note, in Eikon data model fields TR.Div* and TR.CA* belong to separate categories (Dividend History and Capital Changes), as you can see in the Formula Builder dialog in Eikon Excel or in Data Item Browser. The fields listed under different categories have no relation to one another. Dividend History returns dividend events and Capital Changes returns capital change events. These two types of events are separate and not cross-referenced. In your call you're effectively asking for dividend and capital change events that occurred between 1997 and 2018 to be returned as a single table. But just because events appear on the same row does not mean they're in any way related. In the top row you'll simply have the most recent dividend and the most recent capital change event. The 1/2 old to new shares ratio you see returned in the top row refers to the stock split that occurred in 2004 and has absolutely no relation to any dividend events.

richard.sponda

Hi Alex,

Please find below screenshots of the event, that clearly indicates a share change with the ratio of 50:1 on the Ex. Date 09/11/2017.

Since TR.Div and TR,CA are different categories, could you please advise how I could get the 50 to 1 ratio out of the TR.Div formula?

Please let me remark that I tried to fetch this event with the below TR.CA Formula as well, with results as on the screenshot.

split_rq1= ek.get_data(instruments=['LEN.N',

],fields = ['TR.ISINCode','TR.CACorpActEventType','TR.CACorpActDesc','TR.CAAnnouncementDate','TR.CAAdjustmentFactor','TR.CARecordDate','TR.CAEffectiveDate','TR.CAExDate','TR.CAMarketExDateDate',

],parameters = {'CH' : 'Fd','SDate' : '2018-12-31','EDate' : '1997-01-01'}

)

Many thanks in advance for your help,

Best regards

Richard

Alex Putkov.1

Hi Richard. First of all my apologies for the late response. If you have further questions, please start a new thread. It's very easy to miss a new post when you ask a new question on an existing thread.
Now regarding your question, I must admit I got confused. Contrary to what I thought the event you're referring to is not a 50:1 stock consolidation, but a stock dividend payable in a different stock. Holders of Class A shares of Lennar Corp receive one Class B share as dividend for each 50 Class A shares held. Since your post there's been a note added to the Event Detail page you took a screenshot of to the above effect. You can also get the details from Lennar Cort investor relations site
To retrieve the 50:1 ratio you're looking for you need to use Dividend History category, fields TR.DivExistingSec and TR.DivAdditionalSec, e.g.
ek.get_data('LEN.N',['TR.DivPaymentType','TR.DivPayDate','TR.DivAnnouncementDate','TR.DivExistingSec','TR.DivAdditionalSec','TR.DivNotes'],{'SDate' : '2017-12-31','EDate' : '2017-01-01'})

EXPLORE OUR SITES