strategy-lab/to_explore/pyquantnews/15_StockPriceDatabase.ipynb

This notebook fetches financial data for specified stock symbols from Yahoo Finance and stores it in a SQLite database. It provides functions to download stock data over a given date range and save the data into the database. Additionally, it can save data for the latest trading session. This is useful for maintaining a local database of historical stock prices for analysis and backtesting. The code demonstrates usage with example stock symbols and queries the stored data.

In [ ]:

from sys import argv

In [ ]:

import pandas as pd
import yfinance as yf
import sqlite3

Establish a connection to the SQLite database

In [ ]:

con = sqlite3.connect("market_data.sqlite")

Fetch stock data for a given symbol and date range from Yahoo Finance

In [ ]:

def get_stock_data(symbol, start, end):
    """Fetch stock data from Yahoo Finance.
    
    Downloads stock data for a specified symbol and date range and processes it.
    
    Parameters
    ----------
    symbol : str
        The stock ticker symbol.
    start : str
        The start date (YYYY-MM-DD).
    end : str
        The end date (YYYY-MM-DD).
    
    Returns
    -------
    data : pd.DataFrame
        A DataFrame containing the stock data.
    """
    
    # Download the stock data from Yahoo Finance
    data = yf.download(symbol, start=start, end=end)
    
    # Reset the DataFrame index to use integer indexing
    data.reset_index(inplace=True)

    # Rename the columns to match database schema
    data.rename(columns={
        "Date": "date",
        "Open": "open",
        "Low": "low",
        "Close": "close",
        "Adj Close": "adj_close",
        "Volume": "volume"
    }, inplace=True)
    
    # Add a column for the stock symbol
    data['symbol'] = symbol
    
    return data

Save stock data for a given symbol and date range to the SQLite database

In [ ]:

def save_data_range(symbol, start, end):
    """Save stock data to database.
    
    Fetches and saves stock data for a specified symbol and date range.
    
    Parameters
    ----------
    symbol : str
        The stock ticker symbol.
    start : str
        The start date (YYYY-MM-DD).
    end : str
        The end date (YYYY-MM-DD).
    
    Returns
    -------
    None
    """
    
    # Get stock data for the specified symbol and date range
    data = get_stock_data(symbol, start, end)
    
    # Save the data to the SQLite database
    data.to_sql(
        "stock_data", 
        con, 
        if_exists="append", 
        index=False
    )

Save stock data for the latest trading session to the SQLite database

In [ ]:

def save_last_trading_session(symbol):
    """Save the latest trading session data.
    
    Fetches and saves stock data for the latest trading session.
    
    Parameters
    ----------
    symbol : str
        The stock ticker symbol.
    
    Returns
    -------
    None
    """
    
    # Get today's date
    today = pd.Timestamp.today()
    
    # Get stock data for the latest trading session
    data = get_stock_data(symbol, today, today)
    
    # Save the data to the SQLite database
    data.to_sql(
        "stock_data", 
        con, 
        if_exists="append", 
        index=False
    )

Fetch and save data for the symbol "TLT" from 2022-01-01 to 2022-10-21

In [ ]:

save_data_range("TLT", "2022-01-01", "2022-10-21")

Query the SQLite database to fetch data for the symbol "SPY"

In [ ]:

df = pd.read_sql_query("SELECT * from stock_data where symbol='SPY'", con)

Display the fetched data

In [ ]:

df

PyQuant News is where finance practitioners level up with Python for quant finance, algorithmic trading, and market data analysis. Looking to get started? Check out the fastest growing, top-selling course to get started with Python for quant finance. For educational purposes. Not investment advise. Use at your own risk.