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.

Hi Pierre,

Do you have news form the development team?

Best Regards

Richard

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

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

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.

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

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'})