15_StockPriceDatabase.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b99b374d",
   "metadata": {},
   "source": [
    "<div style=\"background-color:#000;\"><img src=\"pqn.png\"></img></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db4c147e",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3000251",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sys import argv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "280a44ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6223c51a",
   "metadata": {},
   "source": [
    "Establish a connection to the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b6b88ae",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"market_data.sqlite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7a5eed8",
   "metadata": {},
   "source": [
    "Fetch stock data for a given symbol and date range from Yahoo Finance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03765086",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def get_stock_data(symbol, start, end):\n",
    "    \"\"\"Fetch stock data from Yahoo Finance.\n",
    "    \n",
    "    Downloads stock data for a specified symbol and date range and processes it.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    symbol : str\n",
    "        The stock ticker symbol.\n",
    "    start : str\n",
    "        The start date (YYYY-MM-DD).\n",
    "    end : str\n",
    "        The end date (YYYY-MM-DD).\n",
    "    \n",
    "    Returns\n",
    "    -------\n",
    "    data : pd.DataFrame\n",
    "        A DataFrame containing the stock data.\n",
    "    \"\"\"\n",
    "    \n",
    "    # Download the stock data from Yahoo Finance\n",
    "    data = yf.download(symbol, start=start, end=end)\n",
    "    \n",
    "    # Reset the DataFrame index to use integer indexing\n",
    "    data.reset_index(inplace=True)\n",
    "\n",
    "    # Rename the columns to match database schema\n",
    "    data.rename(columns={\n",
    "        \"Date\": \"date\",\n",
    "        \"Open\": \"open\",\n",
    "        \"Low\": \"low\",\n",
    "        \"Close\": \"close\",\n",
    "        \"Adj Close\": \"adj_close\",\n",
    "        \"Volume\": \"volume\"\n",
    "    }, inplace=True)\n",
    "    \n",
    "    # Add a column for the stock symbol\n",
    "    data['symbol'] = symbol\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d907550",
   "metadata": {},
   "source": [
    "Save stock data for a given symbol and date range to the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7117438c",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def save_data_range(symbol, start, end):\n",
    "    \"\"\"Save stock data to database.\n",
    "    \n",
    "    Fetches and saves stock data for a specified symbol and date range.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    symbol : str\n",
    "        The stock ticker symbol.\n",
    "    start : str\n",
    "        The start date (YYYY-MM-DD).\n",
    "    end : str\n",
    "        The end date (YYYY-MM-DD).\n",
    "    \n",
    "    Returns\n",
    "    -------\n",
    "    None\n",
    "    \"\"\"\n",
    "    \n",
    "    # Get stock data for the specified symbol and date range\n",
    "    data = get_stock_data(symbol, start, end)\n",
    "    \n",
    "    # Save the data to the SQLite database\n",
    "    data.to_sql(\n",
    "        \"stock_data\", \n",
    "        con, \n",
    "        if_exists=\"append\", \n",
    "        index=False\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cd30bfc",
   "metadata": {},
   "source": [
    "Save stock data for the latest trading session to the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cb23ec7",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def save_last_trading_session(symbol):\n",
    "    \"\"\"Save the latest trading session data.\n",
    "    \n",
    "    Fetches and saves stock data for the latest trading session.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    symbol : str\n",
    "        The stock ticker symbol.\n",
    "    \n",
    "    Returns\n",
    "    -------\n",
    "    None\n",
    "    \"\"\"\n",
    "    \n",
    "    # Get today's date\n",
    "    today = pd.Timestamp.today()\n",
    "    \n",
    "    # Get stock data for the latest trading session\n",
    "    data = get_stock_data(symbol, today, today)\n",
    "    \n",
    "    # Save the data to the SQLite database\n",
    "    data.to_sql(\n",
    "        \"stock_data\", \n",
    "        con, \n",
    "        if_exists=\"append\", \n",
    "        index=False\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42704aa5",
   "metadata": {},
   "source": [
    "Fetch and save data for the symbol \"TLT\" from 2022-01-01 to 2022-10-21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d84f5d39",
   "metadata": {},
   "outputs": [],
   "source": [
    "save_data_range(\"TLT\", \"2022-01-01\", \"2022-10-21\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bb24b46",
   "metadata": {},
   "source": [
    "Query the SQLite database to fetch data for the symbol \"SPY\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a34f05ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(\"SELECT * from stock_data where symbol='SPY'\", con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97dd546f",
   "metadata": {},
   "source": [
    "Display the fetched data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "481992be",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcf58b0f",
   "metadata": {},
   "source": [
    "<a href=\"https://pyquantnews.com/\">PyQuant News</a> 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 <a href=\"https://gettingstartedwithpythonforquantfinance.com/\">get started with Python for quant finance</a>. For educational purposes. Not investment advise. Use at your own risk."
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}