Get Free Financial Data w/ Python (Fundamental Ratios-From Finviz.com)

/

A simple script to scrape fundamental ratios from Finviz.com. This basic code can be tailored to suit your application.


"""IPython 3.1, Python 3.4, Windows 8.1"""

import pandas as pd
import urllib as u
from bs4 import BeautifulSoup as bs

"""
First visit www.Finviz.com and get the base url for the quote page.
example: http://finviz.com/quote.ashx?t=aapl

Then write a simple function to retrieve the desired ratio. 
In this example I'm grabbing Price-to-Book (mrq) ratio
"""

def get_price2book( symbol ):
	try:
    	url = r'http://finviz.com/quote.ashx?t={}'\
        				.format(symbol.lower())
        html = u.request.urlopen(url).read()
        soup = bs(html, 'lxml')
        # Change the text below to get a diff metric
        pb =  soup.find(text = r'P/B')
        pb_ = pb.find_next(class_='snapshot-td2').text
        print( '{} price to book = {}'.format(symbol, pb_) )
        return pb_
    except Exception as e:
        print(e)
        
"""
Construct a pandas series whose index is the list/array
of stock symbols of interest.

Run a loop assigning the function output to the series
"""
stock_list = ['XOM','AMZN','AAPL','SWKS']
p2b_series = pd.Series( index=stock_list )

for sym in stock_list:
	p2b_series[sym] = get_price2book(sym)

The function should produce the following:


XOM price to book = 1.89
AMZN price to book = 20.74
AAPL price to book = 5.27
SWKS price to book = 5.52

Very simple adaptable code, allowing you to spend more time analyzing the data and less time aggregating it.

COMPOSITE SECTOR ETF VALUATION REPORT [7.6.2015]

/

Check out the updated IPython notebook by following the link. In this update we see the interest rate sensitive sectors like Financials, Real Estate, Utilities may be offering a good tactical buying opportunity. 

European Sovereign Debt Crisis Redux, What's the Playbook?

/

I wrote this post originally  as a guest feature on RectitudeMarket.com. Check out the website for great investment ideas and original analysis.

Now that Greece has officially rejected austerity it’s time to examine our 2011 playbook to get some clues as to what might happen. More importantly depending on your timeframe there should be plenty of strategic and tactical strategies for profit due to the increase in volatility.

Let’s compare EUR/USD, Treasury yields, EU yield spreads, and other Developed Markets from 2011 to 2015 for insights on portfolio positioning.

EUR/USD Annualized Rolling Mean Returns suggest a possible structural change

Examining the chart it is easy to see that since 2014 the rolling returns have declined dramatically. In fact it is at a level only seen twice on the chart. First, in 2001 and again in 2009. Both of those periods were tail ends of global recessions. The fact that we are not in a global recession currently(yet?)  suggests potential major trend changes are continuing. 

2011 and 2015 time periods of interest are shaded gray

EUR/USD 252 day rolling volatility is spiking

Simply observing the chart the low-to-high is unparalled since 2008 although much lower on an absolute basis. This is indicative of investor indecision and whipsaws.

2011 and 2015 time periods of interest are shaded gray

Developed Markets' Rolling Mean Returns show decoupling

During periods of global financial streess the commodity countries appear to show acute weakness. EWA and EWC are seriously underperforming their developed market, non-EU peers. QQQ appears to show the most resilience in the face of Eurozone stress in both 2011, and 2015. 

2011 and 2015 time periods of interest are shaded gray

Treasury Yields are likely to fall in a flight to safety trade then continue rising

During the 2011 crisis you can see treasury yields collapse on a lagged basis before continuining to rise. It is likely that yields will follow the same playbook by compressing until more market participants feel certainty regarding potential contagion risk. Barring a total collapse in investor faith in the EU I doubt yields stay compressed for very long given what appears to be a structural shift in rates. 

2011 and 2015 time periods of interest are shaded gray

Treasury Yield volatility is rising yet remains muted when compared to 2011

All 3 maturities remain below their 2011 and subsequent 2012 peaks. They could test those highs but the chart shows a general downtrend in rolling volatility. 

2011 and 2015 time periods of interest are shaded gray

Long term lagged Eurozone interest rate spreads do not show contagion risk like 2011

Examining the long term rates pulled from the ECB’s website we can look at the yield spreads over German Bunds to get an indication of the potential contagion risk this time around. The entire year of 2011 is shaded given the bulk of the crisis happened within that time period. Compared to 2011, Italy, Spain, and Portugal are not yet seeing the same ‘stress’ in 2015. 

2011 and 2015 time periods of interest are shaded gray

Conclusions

1.       Expect increasing volatility across the board as market uncertainty heightens regarding potential contagion risk.

2.       Create your shopping list for ‘overvalued’ ETF’s and individual equities as the potential flight to safety trade will likely create reasonable longer term buying opportunities in quality names.

3.       Barring a complete debacle in the Eurozone US markets are likely to outperform other developed market peers.

4.       Pay particular attention to the EU yield spreads over ‘safe’ German Bonds. If the peripheral spreads start to blow out again as they did in 2011, all bets are off and safety of investment capital becomes critically important if it hasn’t already. 

How do Bank Stocks Perform During Periods of Rising Rates? (Python Code Version)

/

This is the Python version of a guest article that originally appeared on RectitudeMarket.com. In this version I include the Python code used to generate the anaylsis.

This subject has garnered a healthy debate among market participants in recent weeks. Conventional wisdom says that banks and the financial sector overall should benefit from a rising rate environment. The story goes that bank profitability is inextricably linked to `Net Interest Margin (NIM)`. If rates are rising, it is assumed the likely result of a strong economy, during which banks should be able to charge more for the funds they loan, while also increasing loan volume.

A popular analysis on SeekingAlpha.com written by industry veteran Donald van Deventer, makes the case that bank stock prices are negatively correlated to interest rates. While I appreciate the detail and skill of the writer I thought the analysis left some `meat on the bone` so to speak.

  1. He concludes "Bank Stock Prices are Negatively Correlated with Higher Interest Rates". I believe this is not actionable for an investor today and in fact answers the wrong question.

  2.  As an investor the most important variables are the returns from ownership of an asset. The prices themselves are of minimal importance.

  3. This analysis shows that traditional correlations between rates and financial stocks have been changing.

  4. My analysis shows the cumulative returns from ownership of financial stocks including the 'Major Banks' Industry Classification are distinctly positive over the period of study.

  5. My analysis shows that cumulative returns from ownership of bank stocks given yields are falling, are highly negative having peaked around 2002-03.

Before I describe the results of this analysis I must make several disclosures regarding the datasets used.

First and foremost all the analysis was done in Python. I exported all available symbols listed on the Nasdaq and NYSE exchanges from the Nasdaq website. I filtered the symbols first by ‘Finance’ sector. Then I used a market cap filter of greater than $1 billion. Finally I grouped the data by industry and dropped any industry symbols where the total industry was represented by less than 5 symbols. 

import pandas as pd
pd.options.display.float_format = '{:.4f}%'.format 
import numpy as np
import pandas.io.data as web
from pandas.tseries.offsets import *
import datetime as dt
import math
import matplotlib.pyplot as plt
import matplotlib as mpl
import matplotlib.dates as dates
%matplotlib inline
size=(12,7)
import seaborn as sns
sns.set_style('white')
flatui = ["#9b59b6", "#3498db", "#95a5a6", "#e74c3c", "#34495e", "#2ecc71","#f4cae4"]
sns.set_palette(sns.color_palette(flatui,7))
from pprint import pprint as pp

# ================================================================== #
# datetime management

date_today = dt.date.today()
one_year_ago = date_today - 252 * BDay()
five_years_ago = date_today - (5 * 252 * BDay())
ten_years_ago = date_today - (10 * 252 * BDay())
max_years_ago = date_today - (25 * 252 * BDay())

# ================================================================== #
# import stock lists 

path = r"C:\Users\Owner\Documents\_Trading_Education\data_sets_for_practice\\"
NYSE = pd.read_csv(path + 'NYSE_All_companylist.csv')
Nasdaq = pd.read_csv(path + 'Nasdaq_All_companylist.csv')

# print('{}\n{}'.format( Nasdaq.head(), Nasdaq.info() ))
# ================================================================== #
# select financial firms 

nyse_fin = NYSE.loc[(NYSE['Sector'] == 'Finance') & (NYSE['MarketCap'] >= 1e9)]
nsdq_fin = Nasdaq.loc[(Nasdaq['Sector'] == 'Finance') & (Nasdaq['MarketCap'] >= 1e9)]
# print('{}\n{}'.format( nyse_fin.head(), nsdq_fin.head() ))

# ================================================================== #
# combine both dataframes

all_sym = pd.concat([nyse_fin,nsdq_fin])
all_sym.info()
# ================================================================== #
# groupby 'Industry'; check summary statistics

all_grp = all_sym.groupby('Industry')
all_size = all_grp.size()
all_ind_wts = ((all_size / all_size.sum()) * 100).round(2)
all_mktcap_avg = all_grp['MarketCap'].mean().order(ascending=False)
# print('> {}\n>> {}\n {}'.format(all_size, all_ind_wts, all_mktcap_avg ))
print('> {}'.format(all_size))


> Industry
Accident &Health Insurance             7
Banks                                  2
Commercial Banks                      27
Diversified Commercial Services        2
Diversified Financial Services         2
Finance Companies                      1
Finance: Consumer Services            20
Investment Bankers/Brokers/Service    29
Investment Managers                   27
Life Insurance                        20
Major Banks                           96
Property-Casualty Insurers            48
Real Estate                           18
Savings Institutions                  16
Specialty Insurers                    11
dtype: int64

# ================================================================== #
# filter symbols if Industry group size is less than 5
filtered_symbols = all_grp.filter(lambda x: len(x) > 5)
filtered_grp = filtered_symbols.groupby('Industry')

filtered_size = filtered_grp.size()
filtered_ind_wts = ((filtered_size / filtered_size.sum()) * 100).round(2)
filtered_mktcap_avg = filtered_grp['MarketCap'].mean().order(ascending=False)
print('>> {}\n>> {}\n {}'.format(filtered_size, filtered_ind_wts, filtered_mktcap_avg))

>> Industry
Accident &Health Insurance             7
Commercial Banks                      27
Finance: Consumer Services            20
Investment Bankers/Brokers/Service    29
Investment Managers                   27
Life Insurance                        20
Major Banks                           96
Property-Casualty Insurers            48
Real Estate                           18
Savings Institutions                  16
Specialty Insurers                    11
dtype: int64
>> Industry
Accident &Health Insurance            2.1900%
Commercial Banks                      8.4600%
Finance: Consumer Services            6.2700%
Investment Bankers/Brokers/Service    9.0900%
Investment Managers                   8.4600%
Life Insurance                        6.2700%
Major Banks                          30.0900%
Property-Casualty Insurers           15.0500%
Real Estate                           5.6400%
Savings Institutions                  5.0200%
Specialty Insurers                    3.4500%
dtype: float64
 Industry
Commercial Banks                     36040759083.6163
Life Insurance                       21216713129.3125
Major Banks                          19336610403.7998
Investment Bankers/Brokers/Service   18135804631.3441
Finance: Consumer Services           12974551702.9260
Specialty Insurers                   10956345056.7109
Accident &Health Insurance            9773432756.0971
Investment Managers                   8789388295.6570
Property-Casualty Insurers            8393947526.6806
Real Estate                           3410973631.1011
Savings Institutions                  2817572654.6600
Name: MarketCap, dtype: float64

I used the filtered set of symbols and collected <= 25 years of data from Yahoo Finance using ‘adjusted close’ prices. Unfortunately there are obvious gaps in the data. I tried to minimize the effects by resampling the daily data into weekly data and using rolling means, returns, correlations etc. where appropriate. I am unsure of the exact issue behind the data gaps, but I don’t believe it invalidates the general interpretation of the analysis.

I then collected <= 25 years of Treasury yield data for 5, 10, and 30 year maturities using the symbols ‘^FVX’, ‘^TNX’, ‘^TYX’, respectively. 

Note: The following code block shows how I downloaded the data and created the indices for both dataframes so that I could merge the data together for easier analysis.


%%time
# ================================================================== #
# define function to get prices from yahoo finance
def get_px(stock, start, end):  
    try:
        return web.DataReader(stock, 'yahoo', start, end)['Adj Close']
    except:
        print( 'something is f_cking up' )

# ================================================================== #
# get adj close prices 

stocks = [filtered_symbols['Symbol']]
px = pd.DataFrame()
for i, stock in enumerate(stocks):
    # print('{}...[done]\n__percent complete: >>> {}'.format(stock, (i/len(stocks))))
    px[stock] = get_px( stock, max_years_ago, date_today )
# print('>>{}  \n>> {}'.format(px.tail(), px.info()))

px.to_excel(path + '_blog_financial px_{}.xlsx'.format(date_today))

# ================================================================== #
# grab yield data
yields = ['^TYX','^TNX','^FVX']

rates = pd.DataFrame()
for i in yields:
    rates[i] = get_px( i, max_years_ago, date_today )
rates.to_excel(path + '_blog_treasury rates_{}.xlsx'.format(date_today))

After collecting all the data Yahoo Finance had to offer I created financial industry composites using an equal weighted average of the returns of each stock within each industry. I narrowed the focus to the following industries: Major Banks, Investment Bankers/Brokers/Service, Investment Managers, and Commercial Banks.


%%time
# ================================================================== #
# import price data

px = pd.read_excel(path + '_blog_financial px_{}.xlsx'.format(date_today))
rets = np.log(px / px.shift(1)) # calculate log returns
#rets.info()
# ================================================================== #
# construct proper indices for px data to include industry

rets_tpose = rets.T.copy() # transpose df to get symbols as index
r = rets_tpose.reset_index() # reset index to get symbols as column
r = r.sort('index').reset_index(drop=True) # sort the symbol column 'index'; reset numerical index and drop it as col
#r.head()
# ~~~~~~~~~~~ setup industry/columns by sorting symbols using all_sym df; reset numerical index and drop it as col
new_index = filtered_symbols[['Symbol','Industry']].sort('Symbol').reset_index(drop=True) # output dataframe

# ================================================================== #
# create proper multiindex for groupby operations
syms = new_index['Symbol']
industry = new_index['Industry']
idx = list(zip(*(industry, syms)))
idx = pd.MultiIndex.from_tuples(idx, names=['Industry_', 'Symbols_'])
#idx
# ================================================================== #
# construct new log return dataframe using idx

lrets = r.set_index(idx).drop(['index'], axis=1).sortlevel('Industry_').dropna(axis=1,how='all')
lrets_grp = lrets.T.groupby(axis=1, level='Industry_').mean() # equal weighted means of each stock in group
dt_idx = pd.to_datetime(lrets_grp.index) # convert index to datetime
lrets_grp = lrets_grp.set_index(dt_idx, drop=True) # update index 
# lrets_grp.head()

%%time
# ================================================================== #
# import treasury rate data
rates = pd.read_excel(path + '_blog_treasury rates_{}.xlsx'.format(date_today), index_col=0, parse_dates=True).dropna()
rates = rates.set_index(pd.to_datetime(rates.index), drop=True)
# rates.info()

I grouped all the calculations into one code block for ease of reference.


# ================================================================== #
# block of calculations

# ================================================================== #
# resample log returns weekly starting monday
lrets_resampled = lrets_grp.resample('W-MON')

# ================================================================== #
# rolling mean returns
n = 52
roll_mean = pd.rolling_mean( lrets_resampled, window=n, min_periods=n ).dropna(axis=0,how='all')

# ================================================================== #
# rolling sigmas
roll_sigs = pd.rolling_std( lrets_resampled, window=n, min_periods=n ).dropna(axis=0,how='all') * math.sqrt(n)

# ================================================================== #
# rolling risk adjusted returns 
roll_risk_rets = roll_mean/roll_sigs

# ================================================================== #
# calculate log returns of treasury rates
rate_rets = np.log( rates / rates.shift(1) ).dropna()
rate_rets_resampled = rate_rets.resample('W-MON')

# ================================================================== #
# cumulative log returns of resampled rates
lrates_cumsum = rate_rets_resampled.cumsum()

# ================================================================== #
# rolling mean returns of rates
lrates_roll_mean = pd.rolling_mean(rate_rets_resampled, n, n).dropna(axis=0, how='all')

# ================================================================== #
# join yield and stock ret df

# ~~~~ raw resampled log returns
mrg = lrets_resampled.join(rate_rets_resampled, how='outer')

# ~~~~ z-scored raw resampled log returns
zrets = (lrets_resampled - lrets_resampled.mean()) / lrets_resampled.std()
zrates = (rate_rets_resampled - rate_rets_resampled.mean()) / rate_rets_resampled.std()
zmrg = zrets.join(zrates, how='outer')

# ~~~~ rolling means log returns
roll = roll_mean
rates_roll = lrates_roll_mean
mrg_roll = roll.join(rates_roll, how='outer')

# ~~~~ z-scored rolling means
z_roll = (roll_mean - roll_mean.mean()) / roll_mean.std()
zrates_roll = (lrates_roll_mean - lrates_roll_mean.mean()) / lrates_roll_mean.std()
mrg_roll_z = z_roll.join(zrates_roll, how='outer')

# ================================================================== #
# study focus 

# ~~~~ raw resampled log returns
focus = mrg[['Major Banks','Investment Bankers/Brokers/Service','Investment Managers','Commercial Banks','^TYX','^TNX','^FVX']]
# ~~~~ z-scored raw resampled log returns
focus_z = zmrg[['Major Banks','Investment Bankers/Brokers/Service','Investment Managers','Commercial Banks','^TYX','^TNX','^FVX']]
# ~~~~ z-scored rolling means
focus_roll = mrg_roll[['Major Banks','Investment Bankers/Brokers/Service','Investment Managers','Commercial Banks','^TYX','^TNX','^FVX']]
# ~~~~ z-scored rolling means
focus_roll_z = mrg_roll_z[['Major Banks','Investment Bankers/Brokers/Service','Investment Managers','Commercial Banks','^TYX','^TNX','^FVX']]

# ================================================================== #
# select time periods of rising rates

focus_rising = focus
rates_gt_zero_tyx = focus_rising[focus_rising['^TYX'] > 0] 
rates_gt_zero_tnx = focus_rising[focus_rising['^TNX'] > 0] 
rates_gt_zero_fvx = focus_rising[focus_rising['^FVX'] > 0] 

cols_tyx = [col for col in rates_gt_zero_tyx.columns if col not in ['^TYX','^TNX','^FVX']]
cols_tnx = [col for col in rates_gt_zero_tnx.columns if col not in ['^TYX','^TNX','^FVX']]
cols_fvx = [col for col in rates_gt_zero_fvx.columns if col not in ['^TYX','^TNX','^FVX']]

rates_gt_zero_tyx_x = rates_gt_zero_tyx[cols_tyx]
rates_gt_zero_tnx_x = rates_gt_zero_tnx[cols_tnx]
rates_gt_zero_fvx_x = rates_gt_zero_fvx[cols_fvx]

Note: I did not show the plot code I used b/c I did not want to distract too much from the actual analysis. If anyone is interested in how I generated the following charts, contact me. 

Rolling Mean Returns appear to show regime shift in correlations

Looking at the following chart there appears to be a distinct change in the behavior of 52 week rolling mean returns. I z-scored the data for easier interpretation but the raw data shows the same relationships. In the period before ~2004 it appears that Treasury rates and rolling average returns are indeed negatively correlated as they clearly oscillate in opposition. However at some point approximately between Q4 2002 and Q1 2004 this relationship changed as the rolling mean returns appear to move in sync with rates afterwards in a loosely positive correlation.

Recessions shaded in gray. Theorized regime change shaded in blue. 

Rolling Correlations support theory of regime shift in correlations

This next plot shows the 52 week correlations of the composite industries compared to each of the Treasury yield maturities. There is a clear gap in the data, however we can see that prior to my theorized regime shift there were multiple long periods where correlations between rates and the composites were negative (< 0.0). Since then, the correlations have oscillated between highly positive (~>0.5) and 0, with short duration of actual negative correlations.

Recessions shaded in gray.

Cumulative Returns during periods of rising rates are highly positive since 2002-2003

Next I analyzed the data filtered to include only financial industry composite returns during periods where the changes in rates were positive (> 0.0). I did this for each of the three maturities and calculated the cumulative sum. All three charts show negative or zero returns prior to the 2002. Afterwards beginning around 2003, composite returns begin rising together until present day! This result is a clear indicator of two concepts.

  1. There is a high probability of a regime change in the data-set
  2. More importantly, this chart shows that investors had more opportunity to gain from being long financial stocks during periods of rising rates than the alternative.

Recessions shaded in gray

Cumulative Returns during periods of falling rates peaked around 2002-03 and are highly negative since

For comparison I filtered the composite returns to periods where the changes in rates were negative (< 0.0). I did this for each of the three yield maturities. This chart also supports the theory of a regime change in the data set. More importantly, it shows that every composite industry except ‘Investment Managers’ peaked during the 2002-2003 time period and all have been in steep decline since ~2007. Currently all composites show negative cumulative returns.

Recessions shaded in gray

Conclusions

This analysis has some areas worth further investigation and it certainly has some weak points. However, we can make some strong informed conclusions.

  1. Analysis of financial composite prices and yield changes are not enough for an investor to make an informed portfolio decision.

  2. There appears to be a clear regime change in the data-set. Therefore, investment decisions today based on analysis prior to the regime change can give conflicting results, and lead to sub-optimal investment allocations and unnecessary losses.

  3. When analyzing the conditional financial composite returns during the most recent regime, this research shows investors had significantly more gains given periods of rising rates than periods of falling rates!

Feel free to contact me with questions, comments, or feedback: BCR@BlackArbs.com @blackarbsCEO

How Volatile Are US Treasury Bond Yields?

/

The volatility in global bond markets have generated quite the discussion among market participants. So much so that BlackRock has indicated publicly that their old risk models need to be redone. 

BlackRock was speaking specifically about the European sovereign bond market but in light of the recent volatility in US treasuries I wanted to contextualize what's happening domestically. Especially considering I went out on a limb regarding the relative-value offered by the US long bond I thought this topic would warrant further study. 

The shaded region represents the 'Great Recession'

This chart represents annualized rolling volatility of the 30, 10, 5 year treasury bonds. Examining the chart there has been a clear spike in volatility across all three maturities since late 2014. 

Taking this line of inquiry a step further I wanted to examine yield volatility as if I was an active fixed income portfolio manager. To do this I plotted the weekly log yield changes of each maturity. For reference I added horizontal lines representing the +/- 2 standard deviation threshold for each maturity. 

On first glance it's clear the yield changes have been ulcer inducing since the beginning of the year, especially in the 30, and 10 year maturities. However the yield changes in the 5-year are more subdued in comparison. This fits the narrative that fixed income PM's are moving to shorter durations in anticipation of increasing inflation risks.