For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
CodeWave avatar image
3 ●0 ●0 ●0

Historical company guidance data

Hi,

I have a few questions:

1) Does Eikon have historical earnings guidance data before 2003 available. I am asking because for many companies, the earliest guidance data you get is from 2003. If it does, since when is the data available and what is the best way to access it? No I have done TR searches for 'Company Guidance' -data points under I/B/E/S-Estimates.

2) Why does this Python-query display NaN values on all other fields than Guidance Low Value, Guidance High Value and Guidance Text for Walmart:

fields = ['TR.GuidanceDate',
'TR.GuidanceMeasure',
'TR.GuidanceMeasureBasis',
'TR.GuidancePeriodYear',
'TR.GuidancePeriodMonth',
'TR.GuidanceAcctType',
'TR.GuidanceUnitType',
'TR.GuidanceSegmentType',
'TR.GuidanceSegmentName',
'TR.GuidanceDataForm',
'TR.GuidanceDataBasis',
'TR.GuidanceValueDescriptor',
'TR.GuidanceCurrency',
'TR.GuidanceLowValue',
'TR.GuidanceHighValue',
'TR.GuidanceText',
'TR.GuidanceSpeaker',
'TR.GuidanceDocType']


data, err = ek.get_data('WMT',
fields,
{'Period':'FY1',
'FRQ':'FQ',
'SDate':'1990-01-01',
'EDate':'2004-12-31',
'GuidMeasure':'EPS'
}
)

Part of the output (last 4 rows):


However, when I execute the query with the Excel Formula Builder tool, the same rows look like this:


How can I fix the API queary so that it outputs all information - not just the last 4 columns?


3) From what I've understood. it is not possible to skip NaN rows with the API, but is it possible to skip NaN rows in Excel? I would like to include only rows with information to the output and exclude the ones with NaN in every field.

In addition, I am wondering if and how it is possible to filter only the rows that are not 'NULL' with respect to a specific column?


4) What is the correct query to output all company guidance to Earning per Share (EPS) from 1990-01-01 to say 2019-12-31? I would like to include earnings guidance for fiscal quarters and fiscal years. The query that I have now been using is the one presented in question 2 but I am not sure if its the right one. Particularly, I am unsure what I should choose as the 'Frequency' and/or 'Financial Period' in the parameters-section in the Formula builder.

Currently, I have used Fiscal Quarter (FQ) as the Frequency and FY1 as the Financial Period.

Please do not get confused about the fact that I am referring to code (Data API) and Excel interchangeably. The reason is that I firstly create the queries in Excel and when they seem to work, I transfer them to code in Spyder.


5) Is there a way to create a list for all U.S companies and use the list in a TR query? I tried the screener but I got an error stating that the universe is too big (over 5000 rows).


I would highly appreciate some advice!

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpythonformula-builder
capt1.png (17.1 KiB)
capt2.png (17.2 KiB)
icon clock
10 |1500

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

2 Answers

Upvotes
Accepted
wasin.w avatar image
11.5k ●16 ●7 ●10

Hello @CodeWave

Do you still encounter the issue? The Eikon support team confirms that "Issue resolved with the new release of Analytic Data Cloud"

icon clock
10 |1500

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

Upvotes
chavalit-jintamalit avatar image
18k ●21 ●12 ●20

Hi @CodeWave

1. and 4.

Please contact Refinitiv Content Helpdesk to clarify the availability of the data and define the parameters using =TR function on Eikon Excel to retrieve the data according to your requirement.

Then you can replicate it to Eikon Data API.


2. I tested your code and I got 64 rows result.

I randomly select 2 fields and compare the result from API to Eikon Excel.

They are identical. I am not so sure what the problem is?


3. You can use drop, dropna function from dataframe.

Sample:


5. You can to define more filter so it does not exceed 5000 instruments.

Please be aware that the get_data limit number of returning data points to 10000 per API calls.

If you have 5000 instruments, that mean you can only get 2 fields (5000 x 2 = 10000 data points)

ahs1.png (112.4 KiB)
ahs2.png (65.1 KiB)
icon clock
10 |1500

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

CodeWave avatar image CodeWave

Hi,

Thank you for the response! Yeah, I also got a 63-row result. It seems I took the screenshots from another query. However, the problems is the same.

2) My problem is that I don't get the values for any other column than 'Instrument', 'Guidance Low Value', 'Guidance High Value' and 'Guidance Text' with the identical code that I previously posted:


Do you happen to know why I get the NULL values to all the other columns. It seems you got all the data with the same code based on your answer to question 3. Your dataframe includes the values for all data points - at least in row 58 and 63 that you displayed.

To further demonstrate the issue, I will show how the rows 58-63 looks like:

As you can see, I am missing all the data between the instrument and the low value.

5) Ok. So I guess the only way is to do the queries in parts. So for example, If I were to fetch 17 fields for 10,000 firms, I would have to loop over 17 times?

Thanks again for the advice and I am looking forward to your follow-up answer!

capt5.png (22.1 KiB)
1591773858537.png (19.5 KiB)
chavalit-jintamalit avatar image chavalit-jintamalit CodeWave

Hi @CodeWave

2) Are you using latest API version (1.1.2)?

Can you print out the "error" variable to see if you receive any error?

Note that I also checked and assume that you are using "Eikon for Student" variant.


5) You can refer to API limitation at this URL. (https://developers.refinitiv.com/eikon-apis/eikon-data-api/docs?content=49692&type=documentation_item)

You should not exceed the limit on each API call.

So if you are requesting 10000 firms, each firm 17 fields data.

You can split the firm list into multiple smaller lists.


CodeWave avatar image CodeWave chavalit-jintamalit

Hi,

2) I checked, and yes, I am using the 1.1.2 version.

When printing the variable 'err', it returns None. If you meant that I should try to print one of the variables that has no values, it just returns an array with all elements having value NaN - just like in the dataframe.

Yes, I suppose I am using the Eikon for Student version since I am using the Eikon available for all the students in my university.

What could be the problem? I am having a hard time to figuring it out.

5) Yeah, already tried that too, but I seem to continuously face the error 408... I tried with lists with 5, 10, 20, 100 and 500 firms/RICS. Any hints on what could be good size for the smaller firm lists to fetch these 17 fields for the firms?

Anyway, the issue to number 2) is more important and I would appreciate if we could fix it.

Thanks for the help so far!

Show more comments
Click below to post an Idea Post Idea