Using sqlalchemy and pandas to read and write from and to a local sqlite database for Stock Market and Portfolio Anaylsis Tech Stocks and the S&P 500 in 2020
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
df = pd.read_csv('https://stocks-snp-500.herokuapp.com/stocks/stocks_table.csv?_size=max')
df
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.reset_index(level=0, inplace=True)
stocks
stocks.columns = ['Date','MSFT_stock','ZOOM_stock','SNOW_stock','FXAIX_stock']
stocks
import sqlalchemy as db
from sqlalchemy import create_engine
import sqlite3
import pandas as pd
engine = db.create_engine('sqlite:///stocks.db')
connection = engine.connect()
metadata = db.MetaData()
stocks_table = db.Table('stocks_table', metadata,
db.Column('Date',db.Integer, nullable=True, index=False),
db.Column('MSFT_stock',db.Integer, nullable=True),
db.Column('ZOOM_stock',db.Integer, nullable=True),
db.Column('SNOW_stock',db.Integer, nullable=True),
db.Column('FXAIX_stock', db.Numeric, nullable=True)
)
metadata.create_all(engine) #Creates the table
stocks_table
stocks.to_sql('stocks_table', con=engine, if_exists='append', index=False)
engine.execute("SELECT * FROM stocks_table LIMIT 10").fetchall()
sql = """
SELECT
DATE(date) AS DATE
, FXAIX_stock
, MSFT_stock
, SNOW_stock
, row_number() OVER (PARTITION BY Date, MSFT_stock ORDER BY Date) AS REC_NBR
, COUNT(*) OVER (PARTITION BY Date, MSFT_stock ORDER BY Date) AS REC_CNT
, CASE WHEN FXAIX_stock >= 120 THEN 'SNP_High' ELSE 'SNP_low' END AS SNP_HIGH_LOW
FROM stocks_table
--WHERE FXAIX_stock >= 120
"""
cnxn = connection
stocks = pd.read_sql(sql, cnxn)
stocks.tail(30)
stocks['FXAIX_stock'].plot(figsize = (12, 8))
plt.title('Total S&P 500 in 2020 Value')
