Summary of revisions of sell side analysts' and economists' forecasts

iwasaki

I am instructed to post a following question here by Helpdesk. Hope someone can take care of it.

I am looking for an efficient way of checking the latest revisions of (1) sell side analysts’ earnings estimates of individual company and (2) economists’ forecasts of macroeconomic variables (such as GDP QoQ). For (1), ideally, when I run some code specifying a universe (e.g., SP500), then it gives me back a list of sell sides’ revised earnings estimates of constituents of the index within a day or a week. (not aggregate, but individual firm level) Similarly, for (2), when I throw an inquiry, hopefully it returns a list of revised economists’ forecasts. To clarify, I am not looking for consensus figures, but individual analysts’ or economists’ forecasts. I would like to do this on Excel API or Python API. If you have any idea how to implement it, could you share it with me? Thank you!

jason.ramchandani01

@iwasaki @ArmaAngela.Morillo So regarding the second part I have the following:

In order to get the poll contributor RICs for an economic indicator say US Non-Farm Payrolls - go to the ECOP app and select the indicator you want and download the Excel file - which contains a list of contributor rics. Once you have these:

eco_list =['USNFAR=ECI','pUSNFAR=M','pUSNFAR=F','pUSNFAR=P','pUSNFAR=E','pUSNFAR=O','pUSNFAR=L','pUSNFAR=H','pUSNFAR=T','pUSNFAR=C','pUSNFAR=34414809183','pUSNFAR=4295955914','pUSNFAR=8589934341','pUSNFAR=4295856596','pUSNFAR=4295856605','pUSNFAR=4296543164','pUSNFAR=5001207163','pUSNFAR=4297339132','pUSNFAR=5000004165','pUSNFAR=4295870355','pUSNFAR=4295903341','pUSNFAR=4295899980','pUSNFAR=5000665333','pUSNFAR=5000035922','pUSNFAR=5001446019','pUSNFAR=4295889577','pUSNFAR=5000661873','pUSNFAR=4298156389','pUSNFAR=4295860596','pUSNFAR=5001111230','pUSNFAR=4296225616','pUSNFAR=5000001034','pUSNFAR=4296840932','pUSNFAR=4296717602','pUSNFAR=4295859689','pUSNFAR=5000068620','pUSNFAR=4295863670','pUSNFAR=8589934337','pUSNFAR=4295884395','pUSNFAR=4296266900','pUSNFAR=5000643716','pUSNFAR=8589934333','pUSNFAR=4295869477','pUSNFAR=5036745041','pUSNFAR=8589934328','pUSNFAR=4296926154','pUSNFAR=5000039807','pUSNFAR=5000659676','pUSNFAR=4296433158','pUSNFAR=5001111202','pUSNFAR=8589934339','pUSNFAR=5000322672','pUSNFAR=5031543701','pUSNFAR=5033962561','pUSNFAR=4297220581','pUSNFAR=5046437422','pUSNFAR=5000817614','pUSNFAR=5001232158','pUSNFAR=5036775681','pUSNFAR=5000059185','pUSNFAR=4295860600','pUSNFAR=5000026163','pUSNFAR=4296103568','pUSNFAR=5000723161','pUSNFAR=5001221150','pUSNFAR=5000002683','pUSNFAR=5000264442','pUSNFAR=4295898641','pUSNFAR=4297121541','pUSNFAR=8589934316','pUSNFAR=5003301014','pUSNFAR=4296531649','pUSNFAR=4296540339','pUSNFAR=4297227413','pUSNFAR=5038053435','pUSNFAR=5031059686','pUSNFAR=5035155339','pUSNFAR=5000715222','pUSNFAR=4295890645','pUSNFAR=4295885329','pUSNFAR=4296823812','pUSNFAR=4296420951','pUSNFAR=4296921663','pUSNFAR=5000059309','pUSNFAR=5000022647','pUSNFAR=5000029538','pUSNFAR=8589934305','pUSNFAR=4295926331','pUSNFAR=4296248645','pUSNFAR=4297634065','pUSNFAR=4295869482','pUSNFAR=5050917316','pUSNFAR=34413802883','pUSNFAR=5001231000','pUSNFAR=4295903903','pUSNFAR=5062906536','pUSNFAR=5000497219','pUSNFAR=4298462503','pUSNFAR=5022926798','pUSNFAR=4296447819','pUSNFAR=5000075281','pUSNFAR=5036026248','pUSNFAR=4295903973','pUSNFAR=5000699111','pUSNFAR=4297027268','pUSNFAR=5044022689','pUSNFAR=5037621355','pUSNFAR=5000002007','pUSNFAR=5001207809','pUSNFAR=4295911963','pUSNFAR=5037851020','pUSNFAR=4296078247','pUSNFAR=5001154187','pUSNFAR=5001203928','pUSNFAR=8589934275','pUSNFAR=5001170406','pUSNFAR=8589934258','pUSNFAR=5001139367','pUSNFAR=4298379035','pUSNFAR=5040200683','pUSNFAR=4296540416','pUSNFAR=4296141236','pUSNFAR=5000074849','pUSNFAR=5042370971','pUSNFAR=5000056057','pUSNFAR=5001447162','pUSNFAR=5024457089','pUSNFAR=5000750851','pUSNFAR=5004082757','pUSNFAR=4295875735','pUSNFAR=8589934271','pUSNFAR=3441403774','pUSNFAR=5040200092','pUSNFAR=8589934336','pUSNFAR=5000021791','pUSNFAR=4296482545','pUSNFAR=8589934269','pUSNFAR=4298048339','pUSNFAR=5000409258','pUSNFAR=4296618369','pUSNFAR=8589934264','pUSNFAR=4297695497','pUSNFAR=5000044496','pUSNFAR=5001426437','pUSNFAR=5052152936','pUSNFAR=4295926398','pUSNFAR=4295860617','pUSNFAR=5000058989','pUSNFAR=8589934254','pUSNFAR=5000026637','pUSNFAR=5040200816','pUSNFAR=5000046321','pUSNFAR=4295387767','pUSNFAR=4298321962','pUSNFAR=5000000933','pUSNFAR=4297607708','pUSNFAR=5000055834','pUSNFAR=5000725229','pUSNFAR=5003634083','pUSNFAR=5001232358','pUSNFAR=5000046620','pUSNFAR=4298279577','pUSNFAR=4297570990','pUSNFAR=5000709068','pUSNFAR=5000014937','pUSNFAR=4296797779','pUSNFAR=4295877814','pUSNFAR=4297077149','pUSNFAR=4298030964','pUSNFAR=5035576964','pUSNFAR=5032082744','pUSNFAR=5000051464','pUSNFAR=4295904557','pUSNFAR=4298214655','pUSNFAR=4295857405','pUSNFAR=4296390404','pUSNFAR=5001196667','pUSNFAR=4297206206','pUSNFAR=4295870369','pUSNFAR=5001178194',
'pUSNFAR=4296555134','pUSNFAR=5003192346','pUSNFAR=4296013052','pUSNFAR=4296783547','pUSNFAR=5000016911','pUSNFAR=4295862904','pUSNFAR=4295925946','pUSNFAR=4298364510','pUSNFAR=4295876827','pUSNFAR=4296703552','pUSNFAR=5000062056','pUSNFAR=4296544521','pUSNFAR=4295907415','pUSNFAR=5007516915','pUSNFAR=4295868925','pUSNFAR=4297926925','pUSNFAR=5000766115','pUSNFAR=5001134789','pUSNFAR=5001231268','pUSNFAR=4295904676','pUSNFAR=5038911272','pUSNFAR=4296841886','pUSNFAR=4296879512','pUSNFAR=157','pUSNFAR=5000050478','pUSNFAR=8589934173','pUSNFAR=4295870015','pUSNFAR=5036684607','pUSNFAR=5000060749','pUSNFAR=8589934213','pUSNFAR=4296216132','pUSNFAR=5014804390','pUSNFAR=5040200694','pUSNFAR=5001224535','pUSNFAR=4296691557','pUSNFAR=4298009762','pUSNFAR=4295859134','pUSNFAR=4296396736','pUSNFAR=4295921907','pUSNFAR=4295926800','pUSNFAR=5034848936','pUSNFAR=5000067007','pUSNFAR=5000768769','pUSNFAR=4297878248','pUSNFAR=4296087855','pUSNFAR=5000203173','pUSNFAR=4295876880','pUSNFAR=5040196251','pUSNFAR=5000002617','pUSNFAR=5000436924','pUSNFAR=5000818350','pUSNFAR=4297269573','pUSNFAR=4296010337','pUSNFAR=8589934200','pUSNFAR=5000039357','pUSNFAR=4298492064','pUSNFAR=4295895205','pUSNFAR=5039189474','pUSNFAR=4297432571','pUSNFAR=4295861241','pUSNFAR=4296846463','pUSNFAR=5001738799','pUSNFAR=4295865860','pUSNFAR=4295862902','pUSNFAR=5000001334','pUSNFAR=4298007875','pUSNFAR=5001445475','pUSNFAR=5001425253','pUSNFAR=5000832689','pUSNFAR=4295890710','pUSNFAR=5035889006','pUSNFAR=4295908213','pUSNFAR=5000074278','pUSNFAR=8589934183']

you can now chunk them into groups of RICs that will keep you from falling foul of per API call limits - 3000 interday datapoints for ek.get_timeseries:

def chunks(l, n):
    for i in range(0,len(l),n):
        yield l[i:i+n]
        
rics = list(chunks(list(eco_list), 20))
rics

Once you have your array of 20 RIC chunks you can simply:

data = pd.DataFrame()
for i, r in enumerate(rics):
    df = ek.get_timeseries(r, interval='monthly', start_date='2018-01-01')
    if len(data):
        data = pd.concat([data, df], axis=1)
    else:
        data = df
        
data

For the moment I can offer this workflow - there might be a possibility of getting the contributor RICs programatically from our RDP Search API - but I have not been able to find the content. I will speak to the search team and get back to you here. I hope this can help.

jason.ramchandani01

@iwasaki Hi I have written two articles with jupyter notebooks which should be able to assist you - the first article will help you with individual analyst estimates and the second article will help you with economic data and forecasts. Let me know if you have any follow up questions. I hope this can help.

iwasaki

Thank you, Jason, for comprehensive notes. While I got an overview of it, I am still not convinced how to implement what I would like to do. So, let me be more specific. Here are two tasks of interest as follows:

(1) For constituents of S&P500, get all the new earnings estimates by sell side analysts (not consensus, but at individual analyst level) published on 2021-09-13, conditional on differences between the new estimates and the old estimates (i.e., skip reiterations or no-changes, and focus on revisions).

(2) For US real GDP QoQ growth on seasonally adjusted basis for 3Q 2021, get all the new economists’ estimates (again, at an individual economist level) released within one week, conditional on changes to their forecasts.

Do you have any idea how to implement these queries by using ek.get_data() or any other commands? Thank you again, in advance!

ArmaAngela.Morillo

Hi Jason,

Good day.

Hope to follow up answers for the question raised by client.

Received an email today from client regarding his query.

Thank you in advance.

Best Regards,

Arma Morillo

Customer Support Executive

Eikon AIM (Advisory and Investment Management)

raksina.samasiri

Hi @ArmaAngela.Morillo and @Jam.Santillan.Refinitiv,

Do you have the sample TR function on Excel for the tasks mentioned? If you could provide the Excel's TR function, I'll be able to replicate it using Eikon Data API

jason.ramchandani01

@iwasaki @ArmaAngela.Morillo So on the 1st point about retrieving analyst summaries - if you didnt want to store estimates in a database locally you could try something like the following:

1) pulling down all S&P500 current broker estimates:

df4, err =ek.get_data(['0#.SPX'],['TR.RevenueEstValue(Period=FY1).date','TR.RevenueEstValue(Period=FY1).origdate','TR.RevenueEstValue(Period=FY1).origtimezone','TR.RevenueEstValue(Period=FY1).confirmdate','TR.RevenueEstValue(Period=FY1).confirmtimezone','TR.RevenueEstValue(Period=FY1).brokername','TR.RevenueEstValue(Period=FY1).analystname','TR.RevenueEstValue(Period=FY1).analystcode','TR.RevenueEstValue(Period=FY1)'])
                      
df4

2) isolate the estimate activation dates for each day - say for after 15-Sept-2021:

df4['Activation Date'] = pd.to_datetime(df4['Activation Date'])
df5 = df4[df4['Activation Date'] >= '2021-09-15']
df5

3) Then for this subset (which yielded 99 records - so not massive compute - however this might change in earnings peaks etc be careful here) check the individual broker forecast -vs- the previous forecast, where possible:

df5['PCTCHG'] = np.nan
df5['Prev Est Date'] = np.nan


for idx, row in df5.iterrows():
    df6,err = ek.get_data(row['Instrument'],['TR.RevenueEstValue(SDate=0,EDate=-1,Period=FY1,Frq=Y,AnalystCode=' + row['Analyst Code'] + ').origdate','TR.RevenueEstValue(SDate=0,EDate=-1,Period=FY1,Frq=Y,AnalystCode=' + row['Analyst Code'] + ')'])
    if len(df6):
        try:
            df5['PCTCHG'][idx] = df6['Revenue - Broker Estimate'][0] / df6['Revenue - Broker Estimate'][1] -1
            df5['Prev Est Date'][idx] = df6['Activation Date'][1]
            del df6
        except: 
            df5['PCTCHG'][idx] = 'est issue'
            del df6
            pass
    else:
        df5['PCTCHG'][idx] = 'no prev est'
        del df6

4) then check df5

df5

This should give you a list you ca then filter in any way you want ie exclude PCTCHG=0, or NA or <10% etc. I hope this can help guide you. On the individual economist estimates I am still investigating - please bear with me and I will post here when I have something concrete for you.

iwasaki

Dear Jason,

Thank you for very detailed step-by-step instruction. It's very clear and I am able to retrieve what I would like to see on my end. I am grateful for that.

For the second question that I asked, do you know if there exists an economist version (not sell-side analyst) of TR.RevenueEstValue() or something with which I can get his or her estimation? Of course, economists' target of forecast is a macroeconomic variable, not an accounting figure, so I presume the syntax should be somewhat different.

Best,

Hitoshi

iwasaki

Thank you for detailed explanation! It worked out on my end. I really appreciate your help. With that, I have no further questions. Thank you again.