Stock Market and Portfolio Anaylsis Tech Stocks and the S&P 500 in 2020 with pandas_datareader and writing to at sqlite database
This post includes code adapted from python for finance and trading algorithms udemy course and python for finance and trading algorithms udemy course notebooks and the documentation here
import pandas as pd
import numpy as np
import pandas_datareader.data as web
import datetime
import matplotlib.pyplot as plt
%matplotlib inline
# # start = datetime.datetime(2016, 1, 1)
# # end = datetime.datetime(2017, 5, 17)
# start = datetime.datetime(2010, 1, 1)
# end = datetime.datetime(2020, 1, 1)
start = pd.to_datetime('2020-01-01')
end = pd.to_datetime('today')
FXAIX_stock = web.DataReader('FXAIX', 'yahoo', start, end)
FXAIX_stock.head()
MSFT_stock = web.DataReader('MSFT', 'yahoo', start, end)
MSFT_stock.head()
ZOOM_stock = web.DataReader('ZM', 'yahoo', start, end)
ZOOM_stock.head()
SNOW_stock = web.DataReader('SNOW', 'yahoo', start, end)
SNOW_stock.head()
fig = plt.figure(figsize=(12, 6))
plt.title('Open')
MSFT_stock['Open'].plot(label='Microsoft')
ZOOM_stock['Open'].plot(label='Zoom')
SNOW_stock['Open'].plot(label='Snowflake')
FXAIX_stock['Open'].plot(label='SNP_500')
plt.legend()
fig = plt.figure(figsize=(12, 6))
plt.title('Volume')
MSFT_stock['Volume'].plot(label='Microsoft')
ZOOM_stock['Volume'].plot(label='Zoom')
SNOW_stock['Volume'].plot(label='Snowflake')
FXAIX_stock['Volume'].plot(label='SNP_500')
plt.legend()
FXAIX_stock = web.DataReader('FXAIX', 'yahoo', start, end)
FXAIX_stock.head()
MSFT_stock = web.DataReader('MSFT', 'yahoo', start, end)
MSFT_stock.head()
ZOOM_stock = web.DataReader('ZM', 'yahoo', start, end)
ZOOM_stock.head()
SNOW_stock = web.DataReader('SNOW', 'yahoo', start, end)
SNOW_stock.head()
stocks = pd.concat([MSFT_stock['Open'], ZOOM_stock['Open'], SNOW_stock['Open'], FXAIX_stock['Open']],
axis = 1)
stocks
stocks.columns = ['MSFT_stock','ZOOM_stock','SNOW_stock','FXAIX_stock']
stocks
mean_daily_ret = stocks.pct_change(1).mean()
mean_daily_ret
stocks.pct_change(1).corr()
stock_normed = stocks/stocks.iloc[0]
stock_normed.plot()
stock_daily_ret = stocks.pct_change(1)
stock_daily_ret.head()
log_ret = np.log(stocks / stocks.shift(1))
log_ret.head()
log_ret.hist(bins = 100,
figsize = (12, 6));
plt.tight_layout()
log_ret.describe().transpose()
log_ret.mean() * 252
log_ret.cov()
# Set seed (optional)
np.random.seed(101)
# Stock Columns
print('Stocks')
print(stocks.columns)
print('\n')
# Create Random Weights
print('Creating Random Weights')
weights = np.array(np.random.random(4))
print(weights)
print('\n')
# Rebalance Weights
print('Rebalance to sum to 1.0')
weights = weights / np.sum(weights)
print(weights)
print('\n')
# Expected Return
print('Expected Portfolio Return')
exp_ret = np.sum(log_ret.mean() * weights) *252
print(exp_ret)
print('\n')
# Expected Variance
print('Expected Volatility')
exp_vol = np.sqrt(np.dot(weights.T, np.dot(log_ret.cov() * 252, weights)))
print(exp_vol)
print('\n')
# Sharpe Ratio
SR = exp_ret/exp_vol
print('Sharpe Ratio')
print(SR)
num_ports = 15000
all_weights = np.zeros((num_ports, len(stocks.columns)))
ret_arr = np.zeros(num_ports)
vol_arr = np.zeros(num_ports)
sharpe_arr = np.zeros(num_ports)
for ind in range(num_ports):
# Create Random Weights
weights = np.array(np.random.random(4))
# Rebalance Weights
weights = weights / np.sum(weights)
# Save Weights
all_weights[ind,:] = weights
# Expected Return
ret_arr[ind] = np.sum((log_ret.mean() * weights) *252)
# Expected Variance
vol_arr[ind] = np.sqrt(np.dot(weights.T, np.dot(log_ret.cov() * 252, weights)))
# Sharpe Ratio
sharpe_arr[ind] = ret_arr[ind] / vol_arr[ind]
sharpe_arr.max()
sharpe_arr.argmax()
all_weights[10619,:]
max_sr_ret = ret_arr[1419]
max_sr_vol = vol_arr[1419]
plt.figure(figsize = (12, 8))
plt.scatter(vol_arr,
ret_arr,
c = sharpe_arr,
cmap = 'plasma')
plt.colorbar(label = 'Sharpe Ratio')
plt.xlabel('Volatility')
plt.ylabel('Return')
# Add red dot for max SR
plt.scatter(max_sr_vol,
max_sr_ret,
c = 'red',
s = 50,
edgecolors = 'black')
def get_ret_vol_sr(weights):
"""
Takes in weights, returns array or return,volatility, sharpe ratio
"""
weights = np.array(weights)
ret = np.sum(log_ret.mean() * weights) * 252
vol = np.sqrt(np.dot(weights.T, np.dot(log_ret.cov() * 252, weights)))
sr = ret/vol
return np.array([ret, vol, sr])
from scipy.optimize import minimize
import numpy as np
def neg_sharpe(weights):
return get_ret_vol_sr(weights)[2] * -1
# Contraints
def check_sum(weights):
'''
Returns 0 if sum of weights is 1.0
'''
return np.sum(weights) - 1
# By convention of minimize function it should be a function that returns zero for conditions
cons = ({'type' : 'eq', 'fun': check_sum})
# 0-1 bounds for each weight
bounds = ((0, 1), (0, 1), (0, 1), (0, 1))
# Initial Guess (equal distribution)
init_guess = [0.25, 0.25, 0.25, 0.25]
# Sequential Least Squares
opt_results = minimize(neg_sharpe,
init_guess,
method = 'SLSQP',
bounds = bounds,
constraints = cons)
opt_results
opt_results.x
get_ret_vol_sr(opt_results.x)
frontier_y = np.linspace(0, 0.3, 100)
def minimize_volatility(weights):
return get_ret_vol_sr(weights)[1]
frontier_volatility = []
for possible_return in frontier_y:
# function for return
cons = ({'type':'eq','fun': check_sum},
{'type':'eq','fun': lambda w: get_ret_vol_sr(w)[0] - possible_return})
result = minimize(minimize_volatility,
init_guess,
method = 'SLSQP',
bounds = bounds,
constraints = cons)
frontier_volatility.append(result['fun'])
plt.figure(figsize = (12, 8))
plt.scatter(vol_arr,
ret_arr,
c = sharpe_arr,
cmap = 'plasma')
plt.colorbar(label = 'Sharpe Ratio')
plt.xlabel('Volatility')
plt.ylabel('Return')
# Add frontier line
plt.plot(frontier_volatility,
frontier_y,
'g--',
linewidth = 3)
stocks['FXAIX_stock'].plot(figsize = (12, 8))
plt.title('Total S&P 500 in 2020 Value')
import sqlalchemy as db
from sqlalchemy import create_engine
import sqlite3
import pandas as pd
stocks
engine = db.create_engine('sqlite:///stocks.sqlite')
connection = engine.connect()
metadata = db.MetaData()
stocks.to_sql('stocks', con=engine, if_exists='append', index=True)
engine.execute("SELECT * FROM stocks LIMIT 10").fetchall()
engine.execute("SELECT FXAIX_stock FROM stocks LIMIT 10").fetchall()
# df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
# df
# df.to_sql('users', con=engine)
# engine.execute("SELECT * FROM users").fetchall()