Article

Eikon Excel Company Tearsheet in Python (Part 2)

March 31,2020

Zoya Farberov

Introduction

This is the second part of Eikon Excel Company Tearsheet in Python article. It picks up the discussion right where Eikon Excel Company Tearsheet in Python Part 1 left off, and continues with going over Charting. For the best understanding of the material being presented, we suggest reading Part 1 first, by following this link.

Simple Charting with Eikon Data

Many of Eikon Data API charting requirements can be met by retrieving the content via API calls into Dataframes and charting it. An example of this use case is

Stock Performance Chart that uses Price Actions content.

1. We retrieve the content via Eikon Data API

    	
            dfPriceActions, err = ek.get_data(RIC, 'TR.PricePctChg1M/100;TR.PricePctChg3M/100;TR.PricePctChg6M/100;TR.PricePctChg1Y/100')

2. Next we populate the results into lists so that the first list contains names and the second contains values, in this case, price actions, removing the head of the list, RIC

    	
            #create lists out of results 
listNames = dfPriceActions.columns.tolist()
listPA = dfPriceActions.iloc[0, :].tolist()
 
# remove the first element, it's header
listNames.pop(0)
listPA.pop(0)

3. Multi-Row Dataframe in convinently structured via Dictionary:

    	
            dataDictPA = {'Names': listNames, 'Periods': listPA } 
dfPA = pd.DataFrame(dataDictPA, index = listNames)

4. Next we chart it using Plotly library:

    	
            fig = px.bar(dfPA, x='Names', y='Periods')
fig.show()

Find more info on Plotly charting in References section.

One feature that we find particularly useful for charting in Jupyter notebook, is offline charting:

    	
            import plotly.offline as pyof
pyof.init_notebook_mode(connected=True)

Advanced Charting with Eikon Data

The above approach is single-pass: all of the required data points are obtained via Eikon Data API and fed directly into a chart such as plotly chart, with minimal transformations required, and simple massaging can be done in-place.

Some Eikon data charting requirements cannot be met via this approach. One or more of the intermediate data sets are required to derive the complete data set that is required for charting. These requirements can be met via multi-pass approach.

Price Chart, at the bottom of Company Tearsheet Template, is a good example of this type of requirement. In Eikon Excel this requirement is also implemented as a multi-pass, and we follow the same approach.

We review how this is done with Eikon Excel to better understand the approach, and then we translate it into Python. The approach selected for Price Chart implementation may not be the only possible, we aim to keep the approach in Python consistent with the approach selected for Price Chart in Excel template, so the two are in synch.

Let us get started.

1. Tab Data of Company Tearsheet Template is where Data transformations are performed. First, prices are retrieved into columns R and S

    	
            =TR('Company Tearsheet'!$C$3,"TR.PriceClose(sdate=-5AY edate=0d).calcdate;TR.PriceClose(sdate=-5AY edate=0d)","frq=D",R7:S1817)

Similarly, in py's section Pricing Data 1 - Closing Prices we retreive the same closing prices

    	
            dfPricingIntermediate1, err = ek.get_data(RIC, 'TR.PriceClose.calcdate;TR.PriceClose',{'sdate':'-5AY','edate':'0d','frq':'D'})

2. Next in columns O and P, a set of MSFT closing dates is retreived (based on the prices previously retreived into columns R and S)

    	
            =TR("MSFT.O","TR.PriceClose(sdate=0d).date")
=TR("MSFT.O","TR.PriceClose(sdate=-24AM).date")
=IF(OR(INDIRECT(ADDRESS(($S$6+$S$5),R$6))=0,ISERROR(INDIRECT(ADDRESS(($S$6+$S$5),R$6)))),"",INDIRECT(ADDRESS(($S$6+$S$5),R$6)))
=TR("MSFT.O","TR.PriceClose(sdate=#1).date",,,"-"&$Q$7&"D")

And the two dates, that constitute the edges of the interval, are derived from them:

    	
            =IF(P6>=P5,P6,P5)
=IF(P6>=P7,P6,P7)

The sa is done in py, in section Pricing Data - 2 - Dates, we do the same (defining a function to determine the number of workdays between dates):

    	
            def workdays(d, end, excluded=(6, 7)):
    days = []
    while d.date() <= end.date():
        if d.isoweekday() not in excluded:
            days.append(d)
        d += timedelta(days=1)
    return days
 
RIC2 = 'MSFT.O'
 
dfDatesMSFTIntermediate1, err = ek.get_data(RIC2, 'TR.PriceClose.date',{'sdate':'0d'})
dfDatesMSFTIntermediate2, err = ek.get_data(RIC2, 'TR.PriceClose.date',{'sdate':'-24AM'})
 
 
# number of work days between the two dates + 250
daysBetween = len(workdays((datetime.strptime(dfDatesMSFTIntermediate2.at[0,'Date'],'%Y-%m-%dT%H:%M:%SZ')),
               (datetime.strptime(dfDatesMSFTIntermediate1.at[0,'Date'],'%Y-%m-%dT%H:%M:%SZ')))) + 250
daysBetween
dfDatesMSFTIntermediate4, err = ek.get_data(RIC2, 'TR.PriceClose.date',{'sdate':'-'+str(daysBetween)+'D'})
 
display(dfDatesMSFTIntermediate1.at[0,'Date'],
dfDatesMSFTIntermediate2.at[0,'Date'],
dfPricingIntermediate1.at[0,'Calc Date']+'T00:00:00Z',
dfDatesMSFTIntermediate4.at[0,'Date'])
 
if dfPricingIntermediate1.at[0,'Calc Date']+'T00:00:00Z' > dfDatesMSFTIntermediate2.at[0,'Date']:
    laterDate1 = dfPricingIntermediate1.at[0,'Calc Date']+'T00:00:00Z'
else:
    laterDate1 = dfDatesMSFTIntermediate2.at[0,'Date']
    
 
if dfPricingIntermediate1.at[0,'Calc Date']+'T00:00:00Z' > dfDatesMSFTIntermediate4.at[0,'Date']:
    laterDate2 = dfPricingIntermediate1.at[0,'Calc Date']+'T00:00:00Z'
else:
    laterDate2 = dfDatesMSFTIntermediate4.at[0,'Date']
    
display(laterDate1,laterDate2)

3. Now we use the dates to retrieve the data sets that we require for charting.

In Excel it is done this way:

    	
            =TR('Company Tearsheet'!$C$3, "TR.PriceClose(Sdate=0D Edate=#1 Frq=D).CalcDate",,C$6:C$800,$O$6)
 
=TR('Company Tearsheet'!$C$3,"TR.Volume(Sdate=0D Edate=#1 Frq=D Scale=3)",,D$6:D$800,$O$6)
 
=TR('Company Tearsheet'!$C$3, "TR.PriceClose(Sdate=0D Edate=#2 Frq=D curn=#1)",,E$6:E$800,$P$3,$O$6)

While calculating 50 and 200 day moving average

    	
            =IF($E6="","",IF(COUNT($E6:$E55)=50,AVERAGE($E6:$E55),NA()))
 
=IF($E6="","",IF(COUNT($E6:$E205)=200,AVERAGE($E6:$E205),NA()))

Note the references to columns O and P where the border dates were calculated.

And in Python Pricing Data - 3 - Volumes, Prices, 50 days, 200 days section:

    	
            dfDtVolPrice, err = ek.get_data(df_2.at[0,'Primary Quote RIC'], 'TR.PriceClose.CalcDate,TR.PriceClose',{'Sdate':'0D','Edate':laterDate2[:10],'Frq':'D'})
 
dfDtVolPrice2, err = ek.get_data(df_2.at[0,'Primary Quote RIC'], 'TR.Volume',{'Sdate':'0D','Edate':laterDate2[:10],'Frq':'D','Scale':3})
dfDtVolPrice['Volume'] = dfDtVolPrice2['Volume']
 
#calculate moving averages
convolve50 = np.convolve(dfDtVolPrice['Price Close'], np.ones((50,))/50, mode='valid')
convolve200 = np.convolve(dfDtVolPrice['Price Close'], np.ones((200,))/200, mode='valid')

and note the use of numpy.convolve described in more detail in References to conveniently obtain the required running averages .

The third step completes the chart data requirement and we are ready to pass the data into plotly chart:

    	
            fig = make_subplots(specs=[[{"secondary_y": True}]])
 
fig.add_trace(
    go.Scatter(
#       x=dfDtVolPrice['Calc Date'],  #we can optionally chart the complete set
        x = dfDtVolPrice.loc[:525,['Calc Date']]['Calc Date'],    # we are cutting off
        y=dfDtVolPrice['Price Close'],
        name = 'Price Close'), 
    secondary_y=False,
)
fig.add_trace(go.Scatter(
#    x=dfDtVolPrice['Calc Date'],
    x = dfDtVolPrice.loc[:525,['Calc Date']]['Calc Date'],
    y=dfDtVolPrice['Volume'],
    name = 'Volume'), 
    secondary_y=True,
)
fig.add_trace(go.Scatter(
#    x=dfDtVolPrice['Calc Date'],
    x = dfDtVolPrice.loc[:525,['Calc Date']]['Calc Date'],
    y=convolve50,
    name='50 Day MA',),
    secondary_y=False,
)
fig.add_trace(go.Scatter(
    x = dfDtVolPrice.loc[:525,['Calc Date']]['Calc Date'],
#    x=dfDtVolPrice['Calc Date'],
    y=convolve200,
    name='200 Day MA',),
    secondary_y=False,
)
 
fig.update_yaxes(title_text="Price", secondary_y=False)
fig.update_yaxes(title_text="Volume", secondary_y=True)
 
fig.show()

There are two features of the resulting chart that may be worth noting:

It is made more meaningful by the fact that it carries two Y axes, one for Prices and one for Volumes
We cut off the data in all the charted Dataframes at desired point, prior to passing it into go.Scatter, allowing us to present only the most meaning portion of the data set.

We hope that this article helps our reader to reproduce Eikon Excel Company Tearsheet example in Python, and, Jupyter notebook: that accompanying downloadable code on GitHub contains ready-to-use examples that may be relevant, and that at least some of the approaches discussed can be re-used for the next Eikon Data API tasks and projects.