How to show estimates and actuals in the same dataframe?

jess.xu

I'm creating a dataframe with tickers and revs in FY18 and 19. I copied the same field I used in Excel, but the results only gave me FY18 and FY20 data. FY19 is missing. Curious if there's something I'm missing here? Thanks!

Revenue = 'AVAIL(TR.RevenueActValue,TR.RevenueSmartEst,TR.RevenueMean,TR.TotalRevenue)'

Year = 'TR.Revenue.Date'

df, err = ek.get_data(['GOOG.O', 'MSFT.O', 'FB.O', 'AMZN.O', 'TWTR.K'],
[Year, Revenue],
{'Scale': 6, 'SDate': 0, 'EDate': 1, 'FRQ': 'FY', 'Curn': 'USD'})

Alex Putkov.1

@jess.xu
Your request is based on incorrect premise that fields returned from different categories would be aligned, which is not the case. Your request contains fields from the following categories: Reuters Fundamentals - Standardized Income Statement, I/B/E/S Estimates - Actuals, I/B/E/S Estimates - Starmine Smart Estimates. It may be possible to meaningfully align the results in this specific case, but it's not feasible to come up with a logic that would meaningfully align the results retrieved from multiple categories of fields in a generic use case. Instead of trying to align the data the system simply returns the requested results from each category and merges them into a table without interpreting the data and without applying any logic. This means that the values on the same row in the table returned may have no relation to one another. In your example the date returned by TR.Revenue.Date has absolutely no relation to the rest of the fields you retrieve. Or rather any relation you may find there is purely accidental. When you request TR.Revenue.date, TR.RevenueActValue and TR.RevenueSmartEst the system retrieves these 3 fields separately, then simply combines the columns and returns the table. The request level parameters are applied separately to each of the above fields and any omitted parameters assume default values, which may be different for different fields. In this example the default financial period for TR.Revenue.date and TR.RevenueActValue is FY0 whereas for TR.RevenueSmartEst it is FY1. Hence with {'SDate': 0, 'EDate': 1, 'FRQ': 'FY'} TR.Revenue.date and TR.RevenueActValue return two rows: for FY2018 and FY2019, whereas TR.RevenueSmartEst also returns two rows, but for FY2019 and FY2020.
Then you apply AVAIL function, which returns the first available of it's arguments, which on the first row in your example will be the actual revenue for 2018 and on the second row it will be Starmine Smart Estimate for FY2020. To illustrate try the following request:

ek.get_data('GOOGL.O',['TR.Revenue.date','TR.Revenue',
                       'TR.RevenueActValue.fpa','TR.RevenueActValue',
                       'TR.RevenueSmartEst.fpa','TR.RevenueSmartEst'],
           {'SDate': 0, 'EDate': 1, 'FRQ': 'FY'})

To see the fields categories and parameters applicable to any field including the default values use Formula Builder wizard in Eikon Excel.

chavalit-jintamalit

Hi @jess.xu

From your provided code, here is the result.

I do not understand the problem but the data seems to be 2 consecutive years.

So it should not be FY18 then FY20.

jess.xu

Hi @chavalit-jintamalit,

If you compare the output w/ the data on Eikon terminal, you will see the result shows FY18 and FY20. Thought SDate=0, EDate=1 should give me FY19 and FY20, right?

E.g. GOOGL

E.g. MSFT

jess.xu

I see. I will break out actuals and estimates into two dataframes, and append tgt. Thanks Alex!