Interactive Financial Charting

using Cufflinks

Jorge Santos | Thomson Reuters

@jorgesantos

@santosjorge

FOR PYTHON QUANTS

About

Myself

Jorge Santos

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]:
ISIN RIC primaryRIC
0045614 GB0000456144 ANTO.L ANTO.L
0053673 GB0000536739 AHT.L AHT.L
0216238 GB0002162385 AV.L AV.L
0263494 GB0002634946 BAES.L BAES.L
0673123 GB0006731235 ABF.L ABF.L
0969703 GB0009697037 BAB.L BAB.L
0989529 GB0009895292 AZN.L AZN.L
B02J639 GB00B02J6398 ADML.L ADML.L
B1XZS82 GB00B1XZS820 AAL.L AAL.L
B1YW440 GB00B1YW4409 III.L III.L

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]:
versionCreated text storyId sourceCode
2017-05-10 15:25:27 2017-05-10 15:25:27 US STOCKS-Wall St weighed down by weak earning... urn:newsml:reuters.com:20170510:nL4N1IC4KW:1 NS:RTRS
2017-05-10 13:56:42 2017-05-10 13:56:42 US STOCKS-Disney results, Comey sacking weigh ... urn:newsml:reuters.com:20170510:nL4N1IC4E9:1 NS:RTRS
2017-05-10 13:32:37 2017-05-10 13:32:37 US STOCKS SNAPSHOT-Wall St opens slightly lowe... urn:newsml:reuters.com:20170510:nL4N1IC4FL:1 NS:RTRS
2017-05-10 12:44:16 2017-05-10 12:44:16 US STOCKS-Wall St to open slightly lower after... urn:newsml:reuters.com:20170510:nL4N1IC3XM:1 NS:RTRS
2017-05-10 11:32:06 2017-05-10 11:32:06 US STOCKS-Futures fall after FBI Chief Comey s... urn:newsml:reuters.com:20170510:nL4N1IC3GN:1 NS:RTRS

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')))
  • Walt Disney drops after results, weighs on Dow and S&P
  • Allergan drops after reporting quarterly loss
  • Indexes down: Dow 0.16 pct, S&P 0.07 pct, Nasdaq 0.23 pct

Adds details, comments, updates prices

By Yashaswini Swamynathan

- U.S. stocks slipped on Wednesday, weighed down by a string of weak corporate earnings, while investors turned cautious after President Donald Trump fired his FBI chief.

Trump said he fired FBI Director James Comey, who had been leading an investigation into his 2016 presidential campaign's possible collusion with Russia, over his handling of an email scandal involving then-Democratic presidential nominee Hillary Clinton. (Full Story)

Analysts say the development could distract Trump from pursuing his promises, including the highly anticipated reform of the U.S. tax code.

"I think the market is probably going to work past the Comey news unless it spirals into a bigger issue where it starts to threaten the Trump agenda," Robert Pavlik, chief market strategist at Boston Private Wealth.

Demand for safe-haven assets like gold, bonds and the Japanese yen rose, while the dollar fell.

At 10:59 a.m. ET (1459 GMT), the Dow Jones Industrial Average .DJI was down 34.4 points, or 0.16 percent, at 20,941.38, the S&P 500 .SPX was down 1.67 points, or 0.07 percent, at 2,395.25 and the Nasdaq Composite .IXIC was down 14.35 points, or 0.23 percent, at 6,106.23.

Six of the 11 major S&P 500 sectors were lower, with healthcare .SPXHC and consumer discretionary .SPLRCD sectors dropping the most. Energy .SPNY was up 1.4 percent on the back of higher oil prices.

Disney DIS.N fell 2.5 percent and was the top drag on the Dow and the S&P 500 after the media company reported lower-than-expected quarterly revenue and a decline in the number of ESPN subscribers. (Full Story)

Allergan AGN.N dropped 3.2 percent to $230.75 after the Botox-maker posted a quarterly loss as it took a write down on the value of its stake in Teva Pharmaceutical TEVA.TA. (Full Story)

Priceline PCLN.O tumbled 5 percent to $1,815 after the travel website operator forecast current-quarter earnings below analysts' expectations.

One bright spot was Nvidia NVDA.O, which surged 14 percent after the chipmaker reported a better-than-expected jump in quarterly revenue. Shares of rival AMD AMD.O also rose. (Full Story)

Advancing issues outnumbered decliners on the NYSE by 1,599 to 1,177. On the Nasdaq, 1,483 issues fell and 1,130 advanced.

The S&P 500 index showed 19 52-week highs and one low, while the Nasdaq recorded 85 highs and 46 lows.


(Reporting by Yashaswini Swamynathan in Bengaluru; Editing by Anil D'Silva)

(( yashaswini.swamynathan@thomsonreuters.com ; within U.S. +1 646 223 8780, outside U.S. +91 80 6749 2405; Reuters Messaging: yashaswini.swamynathan.thomsonreuters.com@reuters.net ))


Keywords: USA-STOCKS/ (UPDATE 3)

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]:
MSFT.O CLOSE
Date
2015-05-04 48.24
2015-05-05 47.60
2015-05-06 46.28
2015-05-07 46.70
2015-05-08 47.75

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]:
Adjusted Gross Dividend Amount Adjusted Net Dividend Amount
Dividend Ex Date
2015-08-18 0.31 0.31
2015-11-17 0.36 0.36
2016-02-16 0.36 0.36
2016-05-17 0.36 0.36
2016-08-16 0.36 0.36

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]:
CLOSE MSFT.O .SPX
Date
2015-05-04 48.24 2114.49
2015-05-05 47.60 2089.46
2015-05-06 46.28 2080.15
2015-05-07 46.70 2088.00
2015-05-08 47.75 2116.10

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]:
Instrument Minor Region of Headquarters Country ISO Code of Headquarters TRBC Economic Sector Name 1 Month Total Return
0 III.L Northern Europe GB Financials 8.735936
1 A2.MI Southern Europe IT Utilities 2.832244
2 AAAA.L Northern Europe GB Consumer Non-Cyclicals -3.789004
3 AALB.AS Western Europe NL Industrials 5.254784
4 ARLG.DE Western Europe DE Financials 9.681602

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

This is how a backend analytic looks like

Year 1: Percent_Chg(TR.EPSMeanEstimate(period=FY1),TR.EPSActValue(period=FY0))

Year 2: Percent_Chg(TR.EPSMeanEstimate(period=FY1),TR.EPSMeanEstimate(period=FY0))

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]:
EPS Revenue
FY1/FY0 36.851837 22.197527
FY2/FY1 71.432683 20.910831
FY3/FY2 62.131399 18.671825
FY4/FY3 43.932644 16.330261
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]:
Instrument Company Common Name Company Market Cap
29 AAPL.OQ Apple Inc 7.715562e+11
10 GOOGL.OQ Alphabet Inc 6.486604e+11
50 AMZN.O Amazon.com Inc 4.497893e+11
11 FB.OQ Facebook Inc 4.398198e+11
1 BABA.N Alibaba Group Holding Ltd 2.883858e+11
2 WMT.N Wal-Mart Stores Inc 2.296707e+11
15 PCLN.OQ Priceline Group Inc 9.230081e+10
17 NFLX.OQ Netflix Inc 6.705998e+10
30 PYPL.OQ PayPal Holdings Inc 5.888569e+10
19 YHOO.OQ Yahoo! Inc 4.650770e+10

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]:
FY1/FY0 FY2/FY1 FY3/FY2 FY4/FY3
Apple Inc 7.497834 16.079019 6.666622 1.440143
Amazon.com Inc 36.851837 71.432683 62.131399 43.932644
Alibaba Group Holding Ltd 42.290507 23.503356 27.087412 25.123601
Facebook Inc 28.854374 18.251770 26.584551 25.350571
Alphabet Inc -1.189196 18.595541 18.491230 15.634594
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]:
Instrument Company Common Name 52-week Price PCT Change Company Market Cap Revenue Analyst Revisions Model Region Rank TRBC Economic Sector Name
0 ROK.N Rockwell Automation Inc 37.540050 1.987381e+10 5.879500e+09 88 Industrials
36 CFG.N Citizens Financial Group Inc 65.304309 1.895192e+10 NaN 99 Financials
1 SWKS.OQ Skyworks Solutions Inc 51.342883 1.871298e+10 3.289000e+09 96 Technology
4 WDAY.N Workday Inc 23.301105 1.837550e+10 1.569407e+09 93 Technology
16 COL.N Rockwell Collins Inc 15.661032 1.677746e+10 5.259000e+09 99 Industrials

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]:
Buy Hold Sell
Company Name
Intu Properties PLC 0.062500 0.437500 0.500000
Pearson PLC 0.062500 0.625000 0.312500
WM Morrison Supermarkets PLC 0.066667 0.533333 0.400000
Next PLC 0.090909 0.681818 0.227273
InterContinental Hotels Group PLC 0.105263 0.631579 0.263158

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)')

Quant Figure

Time to go into live mode

Emergency Link

thank you

questions?