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 os,  requests

file_links = [("data/api_key.txt", "")]

# 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):
    if not os.path.isfile(filepath):
        with open(filepath, 'wb') as f:
            response = requests.get(fileurl)

8.1 Obtaining Historical Stock Data

Keywords: stock price data

The purpose of this notebook is to download historical trading data for a selected group of the stocks from Alpha Vantage for use with other notebooks. Use of this notebook requires you so enter your personal Alpha Vantage api key into a file data/api_key.txt. The trading data is stored as individual .csv files in a designated directory. Subsequent notebooks read and consolidate that data into a singe file.

You only need to run this notebook if you wish to analyze a different set of stocks, if you wish to update data for the existing set.

8.1.1 Imports

In [1]:
import os
import pandas as pd
import requests
import time

8.1.2 Select Stocks to Download

In [7]:
djia = ['AXP','BA','CAT','CSCO','CVX','DD','DIS','GE', \
        'GS','HD','IBM','INTC','JNJ','JPM','KO','MCD', \
        'MMM','MRK','MSFT','NKE','PFE','PG','T','TRV', \

favs = ['AAPL']

stocks = favs + djia

data_dir = os.path.join('data', 'stocks')
os.makedirs(data_dir, exist_ok=True)

8.1.3 Alpha Vantage

The following cells retrieve a history of daily trading data for a specified set of stock ticker symbols. These functions use the free Alpha Vantage data service. The free service tier provides up to 5 queries per minute.

The service requires an personal api key which can be claimed here in just a few seconds. Place the key as a string in a file data/api_key.txt in the data directory as this notebook (note: api_key.txt is not distributed with the github repository). The function api_key() returns the key stored in api_key.txt.

In [3]:
def api_key():
    "Read api_key.txt and return api_key"
        with open('data/api_key.txt') as fp:
            line = fp.readline()
        raise RuntimeError('Error while attempting to read data/api_key.txt')
    return line.strip()

The function alphavantage(s) returns a pandas dataframe holding historical trading data for a stocker ticker symbol specified by s.

In [4]:
import os
import requests
import pandas as pd

def alphavantage(symbol=None):
    if symbol is None: 
        raise ValueError("No symbol has been provided")
    payload = {
        "function": "TIME_SERIES_DAILY_ADJUSTED",
        "symbol": symbol,
        "outputsize": "full",
        "datatype": "json",
        "apikey": api_key(), 
    api_url = ""
        response = requests.get(api_url, params=payload)
        raise ValueError("No response using api key: " + api_key)
    data = response.json()
    k = list(data.keys())
    metadata = data[k[0]]
    timeseries = data[k[1]]
    S = pd.DataFrame.from_dict(timeseries).T
    S = S.apply(pd.to_numeric)
    S.columns = [h.lstrip('12345678. ') for h in S.columns]
    return S

open high low close adjusted close volume dividend amount split coefficient
2019-05-31 176.23 177.99 174.99 175.07 175.07 27001002 0.0 1.0
2019-05-30 177.95 179.23 176.67 178.30 178.30 21218412 0.0 1.0
2019-05-29 176.42 179.35 176.00 177.38 177.38 28481165 0.0 1.0
2019-05-28 178.92 180.59 177.91 178.23 178.23 27948160 0.0 1.0
2019-05-24 180.20 182.14 178.62 178.97 178.97 23714686 0.0 1.0

get_stock_data(symbols) retrieves trading data for a list of symbols and stores each in seperate file in the data directory. The file name is the ticker symbol with a .csv suffix.

In [5]:
def get_stock_data(symbols, service=alphavantage):
    if isinstance(symbols, str):
        symbols = [symbols]
    assert all(isinstance(s, str) for s in symbols)
    for s in symbols:
        print('downloading', s, end='')
        k = 5
        while k > 0:
                k -= 1
                S = service(s)
                S.to_csv(os.path.join(data_dir, s + '.csv'))
                print(' success')
                print('.', end='')
        if k < 0: print('fail')
downloading AAPL success

8.1.4 Download selected ticker Symbols

In [6]:
downloading AAPL success
downloading AXP success
downloading BA success
downloading CAT success
downloading CSCO success
downloading CVX success
downloading DD success
downloading DIS.... success
downloading GE success
downloading GS success
downloading HD success
downloading IBM success
downloading INTC.... success
downloading JNJ success
downloading JPM success
downloading KO success
downloading MCD success
downloading MMM.... success
downloading MRK success
downloading MSFT success
downloading NKE success
downloading PFE success
downloading PG.... success
downloading T success
downloading TRV success
downloading UNH success
downloading UTX success
downloading V.... success
downloading VZ success
downloading WMT success
downloading XOM success
In [ ]: