automated_quant_db.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start by importing the libraries. I like to use yfinance to get stock data.\n",
    "# I use SQLite as a database you can store on your computer. It’s an efficient file-based database which makes it perfect for building research tools.\n",
    "# It uses standard SQL so you can easily move to a different database like MySQL or Postgres.\n",
    "\n",
    "from sys import argv\n",
    "\n",
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Download and store stock price data.\n",
    "# The script you are building will do two things:\n",
    "#     Save data within a date range\n",
    "#     Save data for the last trading day\n",
    "# \n",
    "# To make it easy, create three functions to download the data, save data within a range, and save data for the last trading day.\n",
    "\n",
    "# create a function to download the data with type hints\n",
    "# type hints are a way to annotate your code so that you can tell what type of data a function should accept and return.\n",
    "\n",
    "def get_market_data(ticker: str,start_date: str,end_date: str) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Downloads data from Yahoo Finance\n",
    "    \"\"\"\n",
    "    data = yf.download(ticker, start=start_date, end=end_date)\n",
    "    data.reset_index(inplace=True)\n",
    "    #rename columns with lower case and replace spaces with underscores\n",
    "    data.columns = [str(x).lower().replace(' ', '_') for x in data.columns]\n",
    "    #adds the ticker column\n",
    "    data['ticker'] = ticker\n",
    "    return data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a function that uses get_market_data to get time series data and saves it into a database\n",
    "# The function uses pandas to_sql method to save the data to a database.\n",
    "def save_market_data(ticker: str, start_date: str, end_date: str, conn: sqlite3.Connection) -> None:\n",
    "    \"\"\"\n",
    "    Saves stock data to database\n",
    "    \"\"\"\n",
    "    data = get_market_data(ticker, start_date, end_date)\n",
    "    data.to_sql(\n",
    "        'yf_data', \n",
    "        conn, \n",
    "        if_exists='append', \n",
    "        index=False\n",
    "    )\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a function that saves the last trading day’s data into the database.\n",
    "# The function grabs data from today and inserts it into the database.\n",
    "\n",
    "def save_market_data_for_last_trading_day(ticker: str, conn: sqlite3.Connection) -> None:\n",
    "    \"\"\"\n",
    "    Saves last trading day's data to database\n",
    "    \"\"\"\n",
    "    today = pd.Timestamp.today()\n",
    "    data = get_market_data(ticker, today, today)\n",
    "    data.to_sql(\n",
    "        'yf_data', \n",
    "        conn,\n",
    "        # if_exists='append' means that if the table already exists, the data will be appended to it.\n",
    "        if_exists='append',\n",
    "            index=False\n",
    "    )\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# create a main function that will run the script\n",
    "def main(ticker: str, start_date: str, end_date: str) -> None:\n",
    "    # usage example for bulk insert\n",
    "    #     python market_data.py bulk SPY 2022-01-01 2022-10-20\n",
    "    # usage example for last session\n",
    "    #     python market_aata.py last SPY\n",
    "\n",
    "    # create a connection to the database\n",
    "    conn = sqlite3.connect('market_data.sqlite')\n",
    "\n",
    "    # if the user selects bulk, run the save_market_data function\n",
    "    if argv[1] == 'bulk':\n",
    "        save_market_data(ticker, start_date, end_date, conn)\n",
    "        print(f\"{symbol} saved between {start} and {end}\")\n",
    "    # if the user selects last, run the save_market_data_for_last_trading_day function\n",
    "    elif argv[1] == 'last':\n",
    "        save_market_data_for_last_trading_day(ticker, conn)\n",
    "    # if the user selects anything else, print an error\n",
    "    else:\n",
    "        print('Usage: python market_data.py bulk <ticker> <start_date> <end_date>')\n",
    "        print('Usage: python market_data.py last <ticker>')\n",
    "    # close the connection to the database\n",
    "    conn.close()\n",
    "    return None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a command that runs the script from the command line\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # usage example for bulk insert\n",
    "    #     python market_data.py bulk SPY 2022-01-01 2022-10-20\n",
    "    # usage example for last session\n",
    "    #     python market_aata.py last SPY\n",
    "\n",
    "    # create a connection to the database\n",
    "    conn = sqlite3.connect('market_data.sqlite')\n",
    "\n",
    "    # if the user selects bulk, run the save_market_data function\n",
    "    if argv[1] == 'bulk':\n",
    "        save_market_data(ticker, start_date, end_date, conn)\n",
    "        print(f\"{symbol} saved between {start} and {end}\")\n",
    "    # if the user selects last, run the save_market_data_for_last_trading_day function\n",
    "    elif argv[1] == 'last':\n",
    "        save_market_data_for_last_trading_day(ticker, conn)\n",
    "    # if the user selects anything else, print an error\n",
    "    else:\n",
    "        print('Usage: python market_data.py bulk <ticker> <start_date> <end_date>')\n",
    "        print('Usage: python market_data.py last <ticker>')\n",
    "    # close the connection to the database\n",
    "    conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "coding_finance_env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}