This notebook contains material from ND-Pyomo-Cookbook; content is available on Github. The text is released under the CC-BY-NC-ND-4.0 license, and code is released under the MIT license.

In [ ]:
# IMPORT DATA FILES USED BY THIS NOTEBOOK
import os,  requests

file_links = [("data/Historical_Adjusted_Close.csv", "https://jckantor.github.io/ND-Pyomo-Cookbook/data/Historical_Adjusted_Close.csv")]

# This cell has been added by nbpages. Run this cell to download data files required for this notebook.

for filepath, fileurl in file_links:
    stem, filename = os.path.split(filepath)
    if stem:
        if not os.path.exists(stem):
            os.mkdir(stem)
    if not os.path.isfile(filepath):
        with open(filepath, 'wb') as f:
            response = requests.get(fileurl)
            f.write(response.content)

8.2 Consolidating and Charting Stock Data

Keywords: stock price data

The notebook reads the data downloaded in the previous notebook, extracts the 'adjusted close', and stores the consolidated in a new data file data/Historical_Adjusted_Close.csv.

8.2.1 Imports

In [110]:
%matplotlib inline

import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import os
import pandas as pd

8.2.2 Loading stock data from data directory

In [111]:
data_dir = os.path.join('data', 'stocks')

stocks = {}
for file in sorted(os.listdir(data_dir)):
    if file.endswith(".csv"):
        s = file.split('.')[0]
        fname = os.path.join(data_dir, file)
        stocks[s] = pd.read_csv(fname, index_col=0)
        stocks[s].set_index(pd.DatetimeIndex(stocks[s].index), inplace=True)

stocks.keys()
Out[111]:
dict_keys(['AAPL', 'AXP', 'BA', 'CAT', 'CSCO', 'CVX', 'DD', 'DIS', 'F', 'GE', 'GS', 'HD', 'IBM', 'INTC', 'JNJ', 'JPM', 'KO', 'MCD', 'MMM', 'MRK', 'MSFT', 'NKE', 'PFE', 'PG', 'T', 'TRV', 'UNH', 'UTX', 'V', 'VZ', 'WMT', 'XOM'])

8.2.3 Charting

In [143]:
def stock_chart(symbol):
    S = pd.DataFrame.from_dict(stocks[symbol])
    fig = plt.figure(figsize=(12,8))
    ax = [plt.subplot(5,1,(1,2)), plt.subplot(5,1,3), plt.subplot(5,1,4), plt.subplot(5,1,5)]
    for a, s in zip(ax, [['adjusted close', 'close'], 'volume', 'dividend amount', 'split coefficient']):
        S[s].plot(ax=a, grid=True)
        a.set_ylabel(s) 
    ax[0].set_title(symbol)
    ax[-1].xaxis.set_major_locator(mdates.YearLocator())
    ax[-1].xaxis.set_major_formatter(mdates.DateFormatter('%m-%Y'))  
    
stock_chart('AAPL')

8.2.4 Consolidating adjusted close data

In [114]:
S = pd.concat([stocks[s]['adjusted close'] for s in stocks.keys()], axis=1, keys=stocks.keys())

fig, ax = plt.subplots(figsize=(14,9))
S.plot(ax=ax, lw=0.7, logy=True)
ax.xaxis.set_major_locator(mdates.YearLocator())
ax.xaxis.set_major_formatter(mdates.DateFormatter('%m-%Y'))
ax.set_ylabel('adjusted close')
ax.set_title('Adjusted Closing Price of Selected Stocks')
ax.legend(loc='center left', bbox_to_anchor=(1.0, 0.5))
ax.grid(True)

S.to_csv(os.path.join('data','Historical_Adjusted_Close.csv'))