Interactive Financial Charting
using Cufflinks
Jorge Santos | Thomson Reuters
@jorgesantos
@santosjorge
FOR PYTHON QUANTS
About
Myself
Jorge Santos
- Product Manager at Thomson Reuters - Eikon
- Passionate for Python, Data and Data Visualization
- Part of Python for Quant Finace - London
- Creator of Cufflinks
What is
Cufflinks?
Cufflinks
A productivity tool that binds the power of Plotly and Pandas.
Optimized for Financial Charting and fast manipulation of data
Interactive
Open Source
Eikon
Data Proxy
Eikon API
During this session we will be using the Thomson Reuters Eikon Data API.
About this talk.
Imports¶
In [1]:
import cufflinks as cf
import pandas as pd
import eikon as ek
Setting up Eikon Library¶
The app id can be obtained from the API Key Generator
In [4]:
ek.set_app_id('AD2833XXXXXXXX')
Symbology
Conversion
We can convert across all types of symbology
In [21]:
sedols = ['B1YW440', '0673123', 'B02J639', 'B1XZS82', '0045614', '0053673',
'0989529', '0216238', '0969703', '0263494']
In [31]:
ek.get_symbology(sedols,to_symbol_type=['ISIN','RIC'])
Out[31]:
News
Getting the top news for Apple where Trump is mentioned
In [33]:
ek.get_news_headlines('R:.SPX AND "Comey" AND Language:LEN',count=5)
Out[33]:
And then we can retrieve the actual story. The stories are delivered in HTML - which means we can display them formatted in our Notebook.
In [34]:
from IPython.display import display,HTML
In [36]:
display(HTML(ek.get_news_story('urn:newsml:reuters.com:20170510:nL4N1IC4KW')))
Analyzing 2 years
Of Microsoft Data
Using ek.get_timeseries to retrieve 2 years of Microsoft Data
In [7]:
msft=ek.get_timeseries('MSFT.O',fields='CLOSE',start_date='2015-05-04',end_date='2017-05-04')
In [8]:
msft.head()
Out[8]:
df.iplot()
Charting 2 years of Microsoft Data
In [5]:
msft.iplot()
In [6]:
msft.iplot(title='Microsoft',theme='ggplot',width=3)
Getting a best fit line
In [7]:
msft.iplot(kind='lines',color='blue',width=2,bestfit=True,title='Bestfit')
Annotations
and Markers
Any chart can be easily annotated in an easy manner¶
annotations={'Date':'First Event','Date':'Second Event'}
Adding a Quarterly Results Event to a Microsoft Chart
In [8]:
# All annotations have basic support for HTML tags
ann={'2016-01-29':'Microsoft Reports Quarterly Results Above Expectations' \
'<BR>Lowering Outlook, Price Target and Rating.'}
In [9]:
msft.iplot(kind='lines',annotations=ann,textangle=0,title='Microsoft - Estimate Revisions')
How sensitive is the price of Microsoft to Dividend Payments?¶
Using Text Labels for Annotations¶
We would like to retrieve Net (Adjusted) Dividends and overlay this information over the price chart of Microsoft.
In [10]:
# We will retreive Net and Gross Dividend for From May 2015 to April 2017
fields=['TR.DivExDate','TR.DivAdjustedGross','TR.DivAdjustedNet']
divs,err=ek.get_data('MSFT.O',fields=fields,
parameters={'SDate':'05May15','EDate':'30Apr17','DateType':'AD'})
In [11]:
# Converting to srings to datetime
divs['Dividend Ex Date']=pd.to_datetime(divs['Dividend Ex Date'])
divs=divs.set_index('Dividend Ex Date')[['Adjusted Gross Dividend Amount','Adjusted Net Dividend Amount']]
divs.head()
Out[11]:
We will generate the Text Labels (including HTML tags) from the own DataFrame columns
In [12]:
# Text Format
table_str="<b>Net: </b> {0} <b><br>Gross: </b> {1}"
divs['text']=divs.apply(lambda x:table_str.format(x['Adjusted Gross Dividend Amount'],
x['Adjusted Net Dividend Amount']),axis=1)
# Dividend Markers
msft_divs=msft.join(divs,how='right').figure(kind='scatter',y='CLOSE',text='text',
mode='markers',hoverinfo='x+text')
# Price Chart
msft_price=msft.figure(colors='blue',title='Microsoft Dividends')
We merge the figures and plot the results
In [13]:
cf.tools.merge_figures([msft_price,msft_divs]).iplot(legend=False)
Performance
Comparison
Has Microsoft outperformed the SPX index?¶
We will retrieve data from 2015 for both Microsoft and SPX
In [14]:
performance=ek.get_timeseries(['MSFT.O','.SPX'],
fields='CLOSE',
start_date='2015-05-04',
end_date='2017-05-04')
In [15]:
performance.head()
Out[15]:
Normalizing (Percent change from day 0) the values for both instruments and charting the performance.
In [16]:
performance.normalize().iplot(kind='spread',title='Microsoft Performance vs SPX',
width=2,showgrid=False)
Returns
Heatmap
We want to retrieve the 1 month returns for all the companies in STOXX and display a heathmap of the mean returns by country and sector.¶
We will get the 600 companies and the 1 month return for each of them
In [106]:
df,err=ek.get_data('0#.STOXX',['TR.HQMinorRegion','TR.HQCountryCode','TR.TRBCEconomicSector','TR.TotalReturn1Mo'])
In [107]:
df.head()
Out[107]:
Group them by Country/Sector
In [108]:
group=df.groupby(['Country ISO Code of Headquarters','TRBC Economic Sector Name'])
matrix=group.mean().unstack()['1 Month Total Return']
And plot the results
In [109]:
matrix.iplot(kind='heatmap',colorscale='rdylgn',margin={'l':200},
title='1 Month Mean Returns in Europe')
Growth
Estimate Changes
We want to retrieve the Percent Change (Growth) of Estimates across multiple Fiscal Years¶
The below function will return a DataFrame with the Percent Change Estimates on multiple Fundamental indicators for a given number of periods¶
In [18]:
def get_estimates(instrument,fields,periods):
fields=[fields] if not isinstance(fields,list) else fields
# The string required for the calculation
string='Percent_Chg(TR.{field}MeanEstimate(period=FY{period1}),' \
'TR.{field}{actOrEst}(period=FY{period0}))'
d=defaultdict(list)
for field in fields:
for period in range(periods):
_f=string.format(field=field,period0=period,
period1=period+1,
actOrEst='ActValue' if period==0 else 'MeanEstimate')
d[field].append(_f)
df,err=ek.get_data(instrument,d[field])
d[field]=[_[0] for _ in df.transpose().values[1:].tolist()]
# Renaming the columns
index=['FY{1}/FY{0}'.format(_,_+1) for _ in range(periods)]
# Returning a DataFrame
return pd.DataFrame(d,index=index)
Getting the 4 Year YoY Estimate change on EPS and Revenue for Amazon ¶
In [19]:
amz_chg=get_estimates('AMZN.O',fields=['EPS','Revenue'],periods=4)
In [20]:
amz_chg
Out[20]:
In [21]:
amz_chg.iplot(kind='bar',title='Amazon Estimates YoY Changes',colorscale='-brbg',
yTitle='Percent Change',theme='ggplot')
How good or bad are these Estimate Changes compared to Amazon's Peer Group ?¶
We first retrieve the peers of Amazon (including Amazon)
In [22]:
peers,err=ek.get_data(['PEERS(AMZN.O)','AMZN.O'],['TR.CommonName','TR.CompanyMarketCap'])
And get the top 10 by Market Cap
In [23]:
peers=peers.sort_values(by='Company Market Cap',ascending=False)[:10]
peers
Out[23]:
Getting the EPS estimates for each of them
In [24]:
peers_eps={}
for ins in peers['Instrument']:
peers_eps[ins]=get_estimates(ins,'EPS',4).to_dict()['EPS']
peers_df=pd.DataFrame(peers_eps)
In [25]:
columns=peers[['Instrument','Company Common Name']].set_index('Instrument').to_dict()['Company Common Name']
peers_df=peers_df.rename(columns=columns).transpose()
In [26]:
peers_df.head()
Out[26]:
In [27]:
peers_df.iplot(kind='bar',colorscale='-purples',title='Amazon Peers - EPS Est % Change YoY',
yTitle='Percentage')
Growth
Ideas
Screening for TECH companies in Russell 1000 Growth Index with a Market Cap between $5B and $20B and an Analyst Revision greater than 80/100.¶
First we define the Universe over which the screening will be performed
In [28]:
# From the Russell 1000 Growth Index as strarting universe
# With a Market Cap between 5B and 20B USD
# With Analyst recommendations greater than 80 and Price to Mid Term component greater than 30
# And EPS >=10 and Revenue >=5 Smart Estimates (All in USD)
universe='SCREEN(U(IN(indices(4387903/*Russell 1000 Growth*/))), ' \
'TR.CompanyMarketCap>=5000000000, TR.CompanyMarketCap<20000000000, ' \
'TR.ARM100Region>=80, TR.PriceMoMidTermComponent>=30, ' \
'TR.EPSSmartEstLastYrGrowth>=10, TR.RevenueSmartEstLastYrGrowth>=5, CURN=USD)'
fields=['TR.CommonName','TR.PricePctChg52W',
'TR.CompanyMarketCap','TR.Revenue(Period:FY0)',
'TR.ARM100Region','TR.TRBCEconomicSector']
In [29]:
df,err=ek.get_data(universe,fields=fields)
We get the top 50 companies by Market Cap
In [30]:
df=df.sort_values(by='Company Market Cap',ascending=False)[:50]
df.head()
Out[30]:
And add the Text on hover information
In [31]:
text='<b>RIC: </b>{0}<br><b>Name: </b>{1}<br><b>Market Cap: </b>{2} <br><b>ARM: </b>{2}'
df['text']=df.apply(lambda x:text.format(
x['Instrument'],
x['Company Common Name'],
round(x['Company Market Cap']/100000000,2),
x['Analyst Revisions Model Region Rank']),axis=1)
In [32]:
df.iplot(kind='bubble',x='52-week Price PCT Change',y='Revenue',size='Company Market Cap',
text='text',categories='TRBC Economic Sector Name',xTitle='52-week Price PCT Change',
yTitle='Revenue',zerolinewidth=2,zerolinecolor='darkgrey',colorscale='set3')
We can add another axis (z) and see them in a 3D form
In [33]:
df.iplot(kind='bubble3d',x='52-week Price PCT Change',y='Revenue',z='Analyst Revisions Model Region Rank',
size='Company Market Cap',text='Company Common Name',categories='TRBC Economic Sector Name',
xTitle='52-week Price PCT Change',yTitle='Revenue',zTitle='Analyst Recs',zerolinewidth=3,
zerolinecolor='darkgrey',colorscale='set3',width=0)
Analyst
Recommendations
What do Analysts think
of the FTSE these days?
We retrieve the analysts' recommendations for the FTSE
In [34]:
recs,err=ek.get_data('0#.FTSE',['TR.CompanyName','TR.NumOfBuy','TR.NumOfHold','TR.NumOfSell'])
And a bit of data wrangling
In [35]:
del recs['Instrument']
recs=recs.set_index('Company Name')
recs=recs.rename(columns=dict(zip(recs.columns.values,['Buy','Hold','Sell'])))
# We get all recommendatins as percent of the total
recs=recs.apply(lambda x:x/x.sum(),axis=1).sort_values(by='Buy')
In [36]:
recs.head()
Out[36]:
And we chart the results for all companies
In [38]:
recs.iplot(kind='barh',barmode='stack',margin={'l':300},dimensions=(1000,600),
colors=['green','blue','pink'])
Easier to digest by sectors?¶
We need to add the sector information to each company
In [39]:
sectors,err=ek.get_data('0#.FTSE',['TR.TRBCEconomicSector','TR.CompanyName'])
sectors=sectors.set_index('Company Name')
del sectors['Instrument']
recs=recs.join(sectors)
Some group by magic..
In [40]:
# Grouping by Sector (mean of percentage values)
recs=recs.groupby('TRBC Economic Sector Name').mean().sort_values(by='Buy')
And plot the sector results
In [41]:
recs.iplot(kind='barh',barmode='stack',margin={'l':200},colors=['green','blue','pink'],
title='FTSE Analyst Recommendations by Sector')
Returns
Distribution
US Presidents
The first 100 days
We will analyze the first 100 days of each of the last 5 Presidents of the United States and look at the distribution of returns for the SPX¶
Presidential Inauguration Dates
In [42]:
potus={'Barack Obama': '2009-01-20',
'Donald J. Trump': '2017-01-20',
'George Bush': '1989-01-20',
'William J. Clinton': '1993-01-20',
'George W. Bush': '2001-01-20'}
In [43]:
potus_df={}
for p in potus:
# Getting the SPX closing values for the first 100 days
df=ek.get_timeseries('.SPX','CLOSE',
start_date=potus[p],
end_date=cf.date_tools.getDatefromDate(potus[p],100,'%Y-%m-%d'))
potus_df[p]=df['CLOSE'].values
Some data wrangling..
In [44]:
min_len=min([len(potus_df[k]) for k in potus_df])
In [45]:
for k in potus_df:
potus_df[k]=potus_df[k][:min_len]
In [46]:
df=pd.DataFrame(potus_df)
In [47]:
df.pct_change(1).iplot(kind='histogram',colorscale='set1',width=0.5,
title='Potus First 100 Days<br>1d Returns Distribution')
In [48]:
df.pct_change(1).iplot(kind='histogram',colorscale='set1',width=0.5,
title='Potus First 100 Days<br>1d Returns Distribution',subplots=True,
shared_xaxes=True)
In [49]:
df.normalize().iplot(colorscale='set1',width=2,legend='bottom',title='First 100 Days POTUS Returns (SPX)')