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
Best Answer
-
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 NaNThe defect was raised to the development team.
0
Answers
-
As the Excel results are not readable in my previous post I re-attach them here.
Dividend Payment Type Dividend Type Dividend Ex Date Date Dividend Market Adjustment factor Dividend Market Adjustment factor type Dividend Market Record Date Stock Dividend Special 12.09.2017 11.10.2017 0.25 Capital Change Type Stock Dividend Final 12.09.2017 01.10.2017 0.956938 Capital Change Type Stock Dividend Final 14.09.2017 01.10.2017 0.917431 Capital Change Type Stock Dividend Final 14.09.2017 01.10.2017 0.920138 Capital Change Type Stock Dividend Final 15.09.2017 01.10.2017 0.966184 Capital Change Type Stock Dividend Final 18.09.2017 25.10.2017 0.966184 Capital Change Type0 -
Looks like that is a formatting issue and null values are not handled correctly. I will contact product managers and let you know.
0 -
Hi Pierre,
Do you have news form the development team?
Best Regards
Richard
0 -
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
0 -
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
0 -
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
0 -
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.0 -
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
0 -
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'})0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛