Financial Data Scraping and Analysis - Fundamentals and Prices

def obtain_asx200():
    """Download and parse the Wikipedia list of ASX200 
       Returns a list of tuples for to add to MySQL."""

  # Use libxml to download the list of S&P500 companies and obtain the symbol table
    now = datetime.utcnow()
    url = "http://www.asx200list.com/wp-content/uploads/csv/20170401-asx200.csv"
    s = requests.get(url).content
    df = pd.read_csv(io.StringIO(s.decode('utf-8')))
    df.columns = df.iloc[0]
    df = df.iloc[1:-1, 0:4]
    symbols = []
    for index, row in df.iterrows():
        symbols.append((row["Code"], 'stock', row["Company"], row["Sector"], 'AUD', now, now))
        
    return symbols

from pandas_datareader import data
from datetime import datetime
import pandas as pd
import requests
import io
from bs4 import BeautifulSoup
import pickle

def update_sp500_list():
    """ Downloads list of S&P500 companeis from Wikipedia.
    Returns a dictionary of sectors and tickers within each sector.
    """
    
    url = "http://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
    hdr = {"User-Agent": "Mozilla/5.0"}
    request = urllib.request.Request(url, headers = hdr)
    page = urllib.request.urlopen(request)
    soup = BeautifulSoup(page)
    
    table = soup.find("table", {"class": "wikitable sortable"})
    sector_tickers = dict()
    for row in table.findAll("tr"):
        col = row.findAll("td")
        if len(col) > 0:
            # You can extract more data here from the Wikitable if you like, it has addresses, GICS subsectors etc
            sector = str(col[3].string.strip()).lower().replace(" ", "_")
            ticker = str(col[0].string.strip())
            if sector not in sector_tickers:
                sector_tickers[sector] = list()
            sector_tickers[sector].append(ticker)
    return sector_tickers
    

def get_ohlc_data(tickers, start, end):
    """ Download ticker data from yahoo-finance.
    
    Returns a multilevel dictionary with ticker data for each sector.
    """
    sector_data = {}
    for sector, tickers in tickers.items():
        print("Downloading sector: " + sector)
        tmp_data = data.DataReader(tickers, "yahoo", start, end)
        
        for item in ["Open", "High", "Low"]:
            tmp_data[item] = tmp_data[item] * tmp_data["Adj Close"] / tmp_data["Close"]
            tmp_data.rename(items = {"Open": "open",
                                     "High": "high",
                                     "Low": "low",
                                     "Adj Close": "close",
                                     "Volume": "volume"})
            
        #Add technical indicators if you please
        #tmp_data["MA"] = MA(tmp_data, 100, "Adj Close") 
        #tmp_data["MOM"] = MOM(tmp_data, 100, "Adj Close")
        tmp_data.drop(["Close"], inplace = True)
        sector_data[sector] = tmp_data
    print("Finished.")
    return sector_data

start = datetime(1990,1,1)  
end = datetime.utcnow()    
path = "path/to/data/"
read_or_write = 0 # Flag based on whether we want to update our data or just read from disc.

if(read_or_write):
    sector_tickers = update_sp500_list() #Pull current ASX200/SP500 tickers
    sector_ohlc = get_ohlc_data(sector_tickers, start, end) #Pull yahoo fin data
    pickle.dump(sector_ohlc, open("sector_data.p", "wb"))
else: 
    sector_ohlc = pickle.load(open(path, "rb"))

print("Sectors Obtained: " ,sector_ohlc.keys())

import pandas as pd 
import numpy as np
import matplotlib.pyplot as plt 

test = pd.DataFrame([np.mean(sector_ohlc[sector]["Adj Close"],axis=1) for sector in list(sector_ohlc.keys())]).T
test.columns = list(sector_ohlc.keys())
plt.figure(figsize = (12,8))
plt.plot(test)
plt.legend(test.columns, loc="best")
plt.ylabel("Mean Adj. Close over SP500 Contained in Sector (USD)")
plt.title("Average Adjusted Close Per S&P500 Stock Sector")
plt.show()


fig = plt.figure(figsize=(20,15))
ax1 = fig.add_subplot(221, title = "Energy", ylabel = "Adj. Close (USD)")
ax2 = fig.add_subplot(222, sharex=ax1, title = "Financials")
ax3 = fig.add_subplot(223, sharex=ax1, title = "Utilities", ylabel = "Adj. Close (USD)")
ax4 = fig.add_subplot(224, sharex=ax1, sharey=ax3, title = "Industrials")

ax1.plot(sector_ohlc["energy"]["Adj Close"])
ax2.plot(sector_ohlc["financials"]["Adj Close"])
ax3.plot(sector_ohlc["utilities"]["Adj Close"])
ax4.plot(sector_ohlc["industrials"]["Adj Close"])
plt.show()

import pymysql
from sqlalchemy import create_engine
import pandas as pd
import matplotlib.pyplot as plt

# Start connection to mysql database
conn = pymysql.connect(host = 'localhost', user = 'username', password ='password', db = 'mstar', port = 3306)

# Initialise sql engine
engine = create_engine('mysql+pymysql://username:password@localhost:3306/mstar')

# Execute SQL to retrieve from a table
with engine.connect() as conn, conn.begin():
    data = pd.read_sql('select * from morningstar_key_financials_aud;', conn)

with engine.connect() as conn, conn.begin():
    df = pd.read_sql('select * from morningstar_key_profitability;', conn)

df["period"] = pd.to_datetime(df["period"])
data.info()

dividends = data.groupby(["period"]).mean()["dividends_aud"]

div_rm = dividends.rolling(min_periods=1, center = True, window = 6).mean().dropna()
plt.plot(div_rm)
plt.xlim([min(div_rm.index), max(div_rm.index)])
plt.show()

eps = data.groupby(["period"]).mean()["earnings_per_share_aud"]

eps_rm = eps.rolling(min_periods=1, center = True, window = 3).mean().dropna()
plt.plot(eps_rm)
plt.xlim([min(eps_rm.index), max(eps_rm.index)])
plt.show()

bv = data.groupby(["period"]).mean()["book_value_per_share_aud"]

bv_rm = bv.rolling(min_periods=1, center = True, window = 3).mean().dropna()
plt.plot(bv_rm)
plt.xlim([min(bv_rm.index), max(bv_rm.index)])
plt.show()

eps = data.groupby(["period"]).mean()
eps.describe()

df.head()
mean_profits = df.groupby(["period"]).mean()
mp_rm = mean_profits["return_on_invested_capital_percent"].rolling(min_periods=1, center = True, window = 6).mean().dropna()
mp_rm.plot()
plt.show()

basic_screener = df.groupby(["ticker"])
basic_screener["return_on_assets_percent"].mean().sort_values(ascending = False)[0:15].plot(kind="barh")
plt.show()

period_screener = df[df["period"] > "2014-01-01"]
period_screener = period_screener.groupby(["ticker"])

screen_average = period_screener.mean()
filtered_average = screen_average[(screen_average["return_on_equity_percent"].values > 15) & (screen_average["return_on_equity_percent"].values <= 100)]
filtered_average.sort_values(by = "return_on_equity_percent",ascending = False)[0:15]#.plot(kind="barh")

def get_stats(group):
    return {'min': group.min(), 'max': group.max(), 'count': group.count(), 'mean': group.mean()}

bins = [-500, -100, 0, 100, 500]
group_names = ['Very Poor', 'Poor', 'Nil', 'Okay']
data['categories'] = pd.cut(data['earnings_per_share_aud'], bins, labels=group_names)

data['earnings_per_share_aud'].groupby(data['categories']).apply(get_stats).unstack()

import pymysql
from sqlalchemy import create_engine
import pandas as pd
import matplotlib.pyplot as plt
import pandas.io.sql as psql

db_host = 'localhost'
db_user = 'username'
db_pass = 'password'
db_name = 'pricing'

con = pymysql.connect(db_host, db_user, db_pass, db_name)

# Select all of the historic Google adjusted close data for symbol MQG

sql = """SELECT *
         FROM symbol AS sym
         INNER JOIN daily_price AS dp
         ON dp.symbol_id = sym.id
         WHERE sym.ticker = 'MQG'
         ORDER BY dp.price_date ASC;"""
         
engine = create_engine('mysql+pymysql://username:password@localhost:3306/pricing')

# Create a pandas dataframe from the SQL query
with engine.connect() as conn, conn.begin():
    p_data = pd.read_sql(sql, con)
    

p_data["price_date"] = pd.to_datetime(p_data["price_date"])
df = p_data.groupby(['price_date', 'ticker']).close_price.mean().unstack()    
plt.figure(figsize=(10,5))

plt.plot(df)
plt.plot(df.rolling(180, center=True).std())
plt.plot(df.rolling(360, center=True).std())
plt.plot(df.rolling(360, center=True).mean())
plt.plot(df.diff(30))

plt.legend(["Price", "6m Rolling Std.", "12m Rolling Std.", "12m Rolling Mean", "1m MOM"], loc = "best")
plt.yaxis("Adj. Price ($)")
plt.show()

import talib as ta

inputs = {
    'open': np.array(p_data["open_price"]),
    'high': np.array(p_data["high_price"]),
    'low': np.array(p_data["low_price"]),
    'close': np.array(p_data["close_price"]),
    'volume': np.array(p_data["volume"])
}

fig = plt.figure(figsize = (20,15))
plt.suptitle("Technical Indicators", fontsize = 25)
plt.subplot(321)
plt.plot(ta.RSI(np.array(p_data["close_price"])))
plt.title("RSI")

plt.subplot(322)
upper, middle, lower = ta.BBANDS(np.array(p_data["close_price"]), 30,2,2)                                
plt.plot(p_data["price_date"],upper)
plt.plot(p_data["price_date"],middle)
plt.plot(p_data["price_date"],lower)
plt.title("Bollinger Bands")


plt.subplot(323)
output = ta.SMA(inputs["close"], 50)
plt.plot(p_data["price_date"],output)
plt.title("Simple Moving Average")

plt.subplot(324)
slowk, slowd = ta.STOCH(inputs["high"], inputs["low"], inputs["close"], 5, 3, 0, 3, 0) # uses high, low, close by default
plt.plot(p_data["price_date"],slowk)
plt.plot(p_data["price_date"],slowd)
plt.title("Stochastic Momentum")

plt.subplot(325)
macd, macdsignal, macdhist = ta.MACD(inputs["close"], fastperiod=12, slowperiod=26, signalperiod=9)
plt.plot(p_data["price_date"],macd)
plt.plot(p_data["price_date"],macdsignal)
plt.plot(p_data["price_date"],macdhist)
plt.title("Moving Average-Convergence Divergence")

plt.subplot(326)
inphase, quadrature = ta.HT_PHASOR(inputs["close"])
plt.plot(p_data["price_date"],inphase)
plt.plot(p_data["price_date"],quadrature)
plt.title("Hilbert Transform - Phasor")

fig.tight_layout()
fig.subplots_adjust(top=0.92)
plt.show()

con = pymysql.connect(db_host, db_user, db_pass, db_name)

sql = """SELECT *
         FROM symbol AS sym
         INNER JOIN daily_price AS dp
         ON dp.symbol_id = sym.id
         where price_date > '2015-01-01';"""
         
engine = create_engine('mysql+pymysql://username:password@localhost:3306/pricing')

# Create a pandas dataframe from the SQL query
with engine.connect() as conn, conn.begin():
    p_data_full = pd.read_sql(sql, con)
    

p_data_full.head()

symlist = pd.DataFrame(np.unique(p_data_full["ticker"]), columns=["ticker"])

def filter_combined(prices):
    mean_12m = prices["close_price"].rolling(360, center=True).mean().mean()
    mean_1m = prices["close_price"].rolling(30, center=True).mean().mean()
    
    std_12m = prices["close_price"].rolling(360, center=True).std().mean()
    std_1m = prices["close_price"].rolling(30, center=True).std().mean()

    vol_12m = prices["volume"].pct_change(periods=12).mean()
    vol_1m = prices["volume"].pct_change(periods=1).mean()
    
    
    return mean_1m / mean_12m, std_1m / std_12m, vol_1m / vol_12m
        
    
vals = [filter_combined(p_data_full[p_data_full["ticker"] == sym]) for sym in symlist["ticker"]]
vals = pd.DataFrame(vals)
symlist[["mean_filter", "std_dev_filter", "vol_filter"]] = vals

symlist.sort_values(by=["std_dev_filter"], ascending = False)[0:15]

np.unique(p_data_full["sector"])

def replace_all_keys(s, dict):
    for i, j in dict.items():
        s = s.replace(i, j)
    return s

replacement_key = {"consumer_discretionary": "Consumer Discretionary",
                  "health_care":"Health Care",
                  "real_estate":"Real Estate",
                  "telecommunication_services":"Telecommunication Services",
                  "information_technology":"Information Technology",
                  "consumer_staples":"Consumer Staples",
                  "utilities":"Utilities",
                  "financials":"Financials",
                  "energy":"Energy",
                  "materials":"Materials",
                  "industrials":"Industrials"}

p_data_full = p_data_full.apply(lambda x: replace_all_keys(x, replacement_key))

p_data_sector = p_data_full.groupby(["sector", "price_date"])

plt.figure(figsize = (15,10))
vals = p_data_sector["volume"].mean().unstack(level=0)
vals.ewm(span=20).mean().plot()
plt.legend(bbox_to_anchor=(1.7,1))
plt.ylabel("Volume")
plt.show()