quant_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# How to build an automated quant database\n",
    "\n",
    "In today’s issue I’m going to show you how to build your own automated stock price database.\n",
    "\n",
    "All quants work with data to analyze the markets. Most of them have internal databases. Hedge funds and banks like Jane Street, Man Group, and Goldman Sachs have teams of people managing data.\n",
    "\n",
    "Building your own stock price database is important if you want to combine data to analyze the markets. Putting stock prices, economic trends, and custom analytics in a database helps you research the markets faster.\n",
    "\n",
    "## Data drives the markets\n",
    "\n",
    "With the explosion of free market data, there’s never been a better time to start storing it. Once you have it, combine it, transform it, and analyze it in any way you want.\n",
    "\n",
    "By the end of this issue you will know how to:\n",
    "\n",
    "- Use SQLite to build a database\n",
    "- Download stock data for free\n",
    "- Store the data in a database\n",
    "- Run it all automatically\n",
    "I’m going to show you how to do it with Python.\n",
    "\n",
    "Let’s get started"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "# import libraries\n",
    "from sys import argv\n",
    "\n",
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "import sqlite3"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-23T10:04:00.655929Z",
     "end_time": "2023-04-23T10:04:01.083892Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Step 2: Download and store stock price data\n",
    "\n",
    "The script you are building will do two things:\n",
    "\n",
    "Save data within a date range\n",
    "Save data for the last trading day\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",
    "First, create a function that downloads data."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "def get_stock_data(symbol, start, end):\n",
    "    data = yf.download(symbol, start=start, end=end)\n",
    "    data.reset_index(inplace=True)\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",
    "    data['symbol'] = symbol\n",
    "    return data\n",
    "# The function uses yfinance to download data. Then it converts the date to a column, renames the columns, adds the symbol, and returns a DataFrame. Add the symbol so you can save more than one stock in the same table.\n",
    "\n",
    "# Next, create a function that uses get_stock_data to get stock data and saves it into the database.\n",
    "\n",
    "def save_data_range(symbol, start, end, con):\n",
    "    data = get_stock_data(symbol, start, end)\n",
    "    data.to_sql(\n",
    "        \"stock_data\",\n",
    "        con,\n",
    "        if_exists=\"append\",\n",
    "        index=False\n",
    "    )"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-23T10:04:04.000009Z",
     "end_time": "2023-04-23T10:04:04.003692Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "def save_last_trading_session(symbol, con):\n",
    "    today = pd.Timestamp.today()\n",
    "    data = get_stock_data(symbol, today, today)\n",
    "    data.to_sql(\n",
    "        \"stock_data\",\n",
    "        con,\n",
    "        if_exists=\"append\",\n",
    "        index=False\n",
    "    )\n",
    "# This function grabs data from today and inserts it into the database."
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-23T10:04:05.421583Z",
     "end_time": "2023-04-23T10:04:05.426461Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter bulk or last\n"
     ]
    }
   ],
   "source": [
    "# I want to run this script automatically. Before I do, I need to add some code to run it 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",
    "    con = sqlite3.connect(\"market_data.sqlite\")\n",
    "\n",
    "    if argv[1] == \"bulk\":\n",
    "        symbol = argv[2]\n",
    "        start = argv[3]\n",
    "        end = argv[4]\n",
    "        save_data_range(symbol, start, end, con)\n",
    "        print(f\"{symbol} saved between {start} and {end}\")\n",
    "    elif argv[1] == \"last\":\n",
    "        symbol = argv[2]\n",
    "        save_last_trading_session(symbol, con)\n",
    "        print(f\"{symbol} saved\")\n",
    "    else:\n",
    "        print(\"Enter bulk or last\")\n",
    "\n",
    "# Remember, to use this code, you need to run it in the terminal.\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-04-23T10:04:49.449352Z",
     "end_time": "2023-04-23T10:04:49.456642Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# Query the SQLite database with pandas in a Jupyter Notebook\n",
    "\n",
    "# To get data out of the database, create a Jupyter Notebook in the same directory as the database file. Then you can write SQL as you would normally.\n",
    "\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "# connect to the database\n",
    "con = sqlite3.connect(\"market_data.sqlite\")\n",
    "\n",
    "# simple select statement\n",
    "df_1 = pd.read_sql_query(\"SELECT * from stock_data where symbol='SPY'\", con)\n",
    "\n",
    "# where statement\n",
    "df_2 = pd.read_sql_query(\"SELECT * from stock_data where symbol='SPY' and volume > 100000\", con)"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}