123_PolarsDuckDB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bc313410",
   "metadata": {},
   "source": [
    "<div style=\"background-color:#000;\"><img src=\"pqn.png\"></img></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8b27314",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "import yfinance as yf\n",
    "import duckdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22234e42",
   "metadata": {},
   "source": [
    "### Retrieve and store financial data for further analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63e8abbf",
   "metadata": {},
   "source": [
    "This code block downloads historical stock price data for NVIDIA (NVDA) from Yahoo Finance. It retrieves data from the beginning of 2023 to the end of the year 2023. The data is then converted into a Polars DataFrame, which is a high-performance data manipulation library similar to Pandas. A new column is added to the DataFrame to label the data with the stock symbol \"NVDA\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27b3866f",
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = yf.download(\"NVDA\", start='2023-01-01', end='2024-01-01')\n",
    "\n",
    "df = (\n",
    "    pl\n",
    "    .from_pandas(\n",
    "        prices\n",
    "        .reset_index()\n",
    "    )\n",
    "    .with_columns(\n",
    "        [pl.lit(\"NVDA\").alias(\"symbol\")]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b09b76ce",
   "metadata": {},
   "source": [
    "The code uses Yahoo Finance to download stock prices for NVIDIA, covering the specified date range. The downloaded data is in Pandas format, which is then converted into a Polars DataFrame for efficient processing. After resetting the index to ensure the date is a regular column, a new column \"symbol\" is added with the value \"NVDA\" for easy identification of the stock in later analyses. This setup is particularly useful for managing and analyzing large datasets efficiently."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6523b2e6",
   "metadata": {},
   "source": [
    "### Create a database and store the stock data for querying"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15fb9708",
   "metadata": {},
   "source": [
    "This code initializes a connection to a DuckDB database called 'stocks.db' and creates a table named 'stocks'. If the table already exists, it will not create a new one. The data from our Polars DataFrame is stored in this table. This allows us to use SQL queries on the stock data, which is particularly helpful for complex data manipulations and analyses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e26c3747",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = duckdb.connect('stocks.db')\n",
    "con.execute(\"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS stocks AS SELECT * FROM df\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e501572a",
   "metadata": {},
   "source": [
    "The connection to DuckDB is established, allowing for efficient SQL operations on data stored in-memory or on disk. The code checks if a table named 'stocks' exists in the database. If not, it creates it using the data from the Polars DataFrame. This integration of Polars and DuckDB enables the user to leverage SQL queries for data analysis, combining the strengths of both SQL and DataFrame manipulations."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b04c3b67",
   "metadata": {},
   "source": [
    "### Perform SQL queries to extract insights from the data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f2bdcb7",
   "metadata": {},
   "source": [
    "Here, we execute SQL queries on the stored stock data to calculate the average closing price and find high-volume trading days. The first query calculates the average closing price for NVDA and the second query identifies the top 5 days with the highest trading volume. This approach highlights how SQL can be used to quickly summarize and explore financial data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "539f3c21",
   "metadata": {},
   "outputs": [],
   "source": [
    "pl.DataFrame(\n",
    "    con.execute(\"\"\"\n",
    "    SELECT symbol, round(avg(Close), 2) as avg_close FROM stocks GROUP BY symbol\n",
    "    \"\"\").fetchdf()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13eefbfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "pl.DataFrame(\n",
    "    con.execute(\"\"\"\n",
    "    SELECT symbol, \n",
    "           date, \n",
    "           Volume,\n",
    "           Close\n",
    "    FROM stocks \n",
    "    ORDER BY Volume DESC \n",
    "    LIMIT 5\n",
    "    \"\"\").fetchdf()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9ee1886",
   "metadata": {},
   "source": [
    "The SQL query calculates the average closing price of NVIDIA stock by grouping all data entries by the stock symbol and averaging the closing prices. The result is then rounded to two decimal places for clarity. The second query retrieves the top 5 trading days with the highest volume of trades. The results are converted into Polars DataFrames, allowing easy manipulation and visualization of the output. These queries provide a quick overview of key metrics, such as average price and trading volume peaks."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad598059",
   "metadata": {},
   "source": [
    "### Calculate rolling VWAP to track stock price trends"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f839cac9",
   "metadata": {},
   "source": [
    "We use SQL to calculate the 20-day rolling Volume Weighted Average Price (VWAP) for NVIDIA stock. The VWAP is an important metric for traders as it provides insights into the average price a stock has traded at, factoring in volume. The rolling VWAP smooths out daily fluctuations to reveal longer-term trends."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bef018a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "vwap_query = \"\"\"\n",
    "WITH daily_vwap AS (\n",
    "    SELECT \n",
    "        \"Date\",\n",
    "        symbol,\n",
    "        SUM(Volume * Close) / SUM(Volume) as vwap\n",
    "    FROM stocks\n",
    "    GROUP BY \"Date\", symbol\n",
    "),\n",
    "rolling_vwap AS (\n",
    "    SELECT \n",
    "        \"Date\",\n",
    "        symbol,\n",
    "        AVG(vwap) OVER (\n",
    "            PARTITION BY symbol \n",
    "            ORDER BY \"Date\" \n",
    "            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW\n",
    "        ) as rolling_20d_vwap\n",
    "    FROM daily_vwap\n",
    ")\n",
    "SELECT * FROM rolling_vwap\n",
    "ORDER BY symbol, \"Date\";\n",
    "\"\"\"\n",
    "pl.DataFrame(con.execute(vwap_query).fetchdf())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3dc85ef",
   "metadata": {},
   "source": [
    "The code defines a SQL query that first calculates the daily VWAP by dividing the total value traded (Volume * Close) by the total volume for each day. Next, it computes a 20-day rolling average of these daily VWAPs to smooth short-term fluctuations and reveal longer-term price trends. The use of a window function in SQL allows the rolling average to be calculated efficiently. The output is then converted into a Polars DataFrame for further analysis or visualization. This process helps traders and analysts understand how the stock's price trends over time, adjusted for trading volume."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05374e12",
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9adcfb8",
   "metadata": {},
   "source": [
    "Closing the DuckDB connection ensures that all resources are freed and the database is properly closed. This is a good practice to prevent memory leaks and ensure data integrity after completing database operations. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1b349ae",
   "metadata": {},
   "source": [
    "### Your next steps"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "083be1c6-a257-463c-bfdb-f86dcd8f78c0",
   "metadata": {},
   "source": [
    "Now try modifying the code to download data for a different stock symbol, such as AAPL or TSLA. You could also change the date range to analyze different periods. Experimenting with these parameters will help you understand how the code adapts to different data inputs and can provide new insights into other stocks' performance. This hands-on approach will deepen your understanding of data manipulation and analysis in quantitative finance."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "186171ce",
   "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"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}