get_data mishandle column with NULL values

[andykubid]

It seems that get_data cannot correctly handle columns with NULL/missing values.

Consider this:

ticker <- "0388.HK"
req.fields <- list("TR.EPSEstValue(Period=FY2019)", "TR.EPSEstValue(Period=FY2019).date", "TR.EPSEstValue(Period=FY2019).origdate", "TR.EPSEstValue(Period=FY2019).confirmdate")
get_data(ticker, req.fields)
   Instrument Earnings Per Share - Broker Estimate                 Date      Activation Date         Confirm Date
1     0388.HK                                 8.19 2018-03-01T15:00:00Z 2018-03-01T15:00:00Z 2018-03-02T00:00:00Z
2     0388.HK                                 6.94 2018-03-01T05:58:00Z 2018-03-01T05:58:00Z 2018-02-28T00:00:00Z
3     0388.HK                                  9.6 2018-04-12T12:00:00Z 2018-04-12T12:00:00Z 2018-04-13T00:00:00Z
4     0388.HK                               12.038 2018-01-29T16:35:00Z 2018-01-29T16:35:00Z 2018-03-01T00:00:00Z
5     0388.HK                                9.238 2018-04-12T08:27:00Z 2018-04-12T08:27:00Z 2018-03-20T00:00:00Z
6     0388.HK                                 9.41 2018-01-26T21:10:00Z 2018-01-26T21:10:00Z 2018-03-27T00:00:00Z
7     0388.HK                                 7.04 2017-10-03T14:00:00Z 2018-04-01T22:40:18Z                     
8     0388.HK                               10.684 2018-03-01T05:40:00Z 2018-03-01T05:40:00Z                     
9     0388.HK                                  9.2 2018-03-01T19:37:00Z 2018-03-01T19:37:00Z                     
10    0388.HK                                7.802 2018-03-01T14:57:00Z 2018-03-01T14:57:00Z                     
11    0388.HK                                11.99 2018-03-01T17:17:00Z 2018-03-01T17:17:00Z                     
12    0388.HK                                7.586 2018-03-05T10:30:00Z 2018-03-05T10:30:00Z                     
13    0388.HK                                 7.61 2018-02-28T12:20:00Z 2018-02-28T12:20:00Z                     
14    0388.HK                                  7.7 2018-01-11T07:47:00Z 2018-01-11T07:47:00Z                     
15    0388.HK                                 9.04 2018-03-01T09:22:00Z 2018-03-01T09:22:00Z                     
16    0388.HK                                 7.89 2018-02-28T21:54:00Z 2018-02-28T21:54:00Z                     
17    0388.HK                                7.978 2018-03-01T08:34:00Z 2018-03-01T08:34:00Z                     
18    0388.HK                                 8.75 2018-04-09T21:08:00Z 2018-04-09T21:08:00Z                     
19    0388.HK                                 7.32 2018-01-09T20:44:00Z 2018-01-09T20:44:00Z                     
20    0388.HK                                9.754 2018-02-28T14:15:00Z 2018-02-28T14:15:00Z      

Note that there are 5 confirm dates and they are all attached to the first 5 rows.

But, this is wrong.

From Eikon/Excel

in cell A1: =TR("0388.HK","TR.EPSEstValue;TR.EPSEstValue.origdate;TR.EPSEstAge;TR.EPSEstFootNote","Period=FY2019 SDate=1D CH=Fd RH=IN;date;confirmdate;analystcode;brokername;isexcluded",B2)"

Output:

Ticker	date	confirmdate	analystcode	brokername	isexcluded	broker estimate	Activation Date	Age	footnote
0388.HK	3/1/2018 15:00			Permission Denied 483808	FALSE	8.19	3/1/2018 15:00	48	NULL
0388.HK	3/1/2018 5:58		6CGC	"MORNINGSTAR	INC."	FALSE	6.94	3/1/2018 5:58	48
0388.HK	4/12/2018 12:00			Permission Denied 73704	FALSE	9.6	4/12/2018 12:00	6	NULL
0388.HK	1/29/2018 16:35	3/2/2018		Permission Denied 7896	FALSE	12.038	1/29/2018 16:35	79	NULL
0388.HK	4/12/2018 8:27		786C	NOMURA ASIAN EQUITY RESEARCH	FALSE	9.238	4/12/2018 8:27	6	NULL
0388.HK	1/26/2018 21:10	2/28/2018	4AVH	BNP PARIBAS GROUP	FALSE	9.41	1/26/2018 21:10	82	NULL
0388.HK	10/3/2017 14:00		4INH	RHB RESEARCH	TRUE	7.04	4/1/2018 22:40	197	Contributor update pending: Estimate failed freshness policy
0388.HK	3/1/2018 5:40	4/13/2018		Permission Denied 25632	FALSE	10.684	3/1/2018 5:40	48	NULL
0388.HK	3/1/2018 19:37		7A4D	CHINA EVERBRIGHT RESEARCH LTD	FALSE	9.2	3/1/2018 19:37	48	NULL
0388.HK	3/1/2018 14:57			Permission Denied 128064	FALSE	7.802	3/1/2018 14:57	48	NULL
0388.HK	3/1/2018 17:17	3/1/2018	7GOU	CHINA INTERNATIONAL CAPITAL CORPORATION (HONG KONG) LIMITED	FALSE	11.99	3/1/2018 17:17	48	NULL
0388.HK	3/5/2018 10:30			Permission Denied 22760	FALSE	7.586	3/5/2018 10:30	44	NULL
0388.HK	2/28/2018			Permission Denied 32	FALSE	7.61	2/28/2018 12:20	49	NULL
0388.HK	1/11/2018		7HRJ	KGI SECURITIES (SG) PTE LTD	FALSE	7.7	1/11/2018 7:47	97	NULL
0388.HK	3/1/2018		7IA9	CHINA GALAXY INTERNATIONAL SECURITIES	FALSE	9.04	3/1/2018 9:22	48	NULL
0388.HK	2/28/2018			Permission Denied 392	FALSE	7.89	2/28/2018 21:54	49	NULL
0388.HK	3/1/2018		772H	UOB KAY HIAN PTE LTD	FALSE	7.978	3/1/2018 8:34	48	NULL
0388.HK	4/9/2018			Permission Denied 36928	FALSE	8.75	4/9/2018 21:08	9	NULL
0388.HK	1/9/2018	3/20/2018		Permission Denied 23816	FALSE	7.32	1/9/2018 20:44	99	NULL
0388.HK	2/28/2018 14:15	3/27/2018		Permission Denied 2880	FALSE	9.754	2/28/2018 14:15	49	NULL

If we look at the first row or the R output, forecast value is 8.19 with confirmdate of 2018-3-2 but, in Excel, forecast value for the first row is also 8.19, but there is no confirm date. Confirm date of 2018-3-2 is linked with the forecast value of 12.038 (row 4).

Furthermore, I noted that the sequence of confirm date is consistent in both results but R get_data does not inject NULL into the data frame correctly.

I haven't tested it with Python API yet. though.

Thanks,

Andy