bqnt_futures.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import nbappinator as nbapp\n",
    "import iql\n",
    "\n",
    "import logging\n",
    "import jinja2\n",
    "from functools import cache\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "import plotly.express as px\n",
    "from plotly.subplots import make_subplots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup some globals for the application. The page globals are optional.\n",
    "\n",
    "PAGES = [\"Chart\", \"Price\", \"Futures\"]\n",
    "myapp = nbapp.TabbedUiModel(pages=PAGES, log_footer=\"Messages\", headers=[\"Config\"])\n",
    "chart_page = myapp.get_page(PAGES[0])\n",
    "price_page = myapp.get_page(PAGES[1])\n",
    "futures_page = myapp.get_page(PAGES[2])\n",
    "config_page = myapp.get_page(\"Config\")\n",
    "\n",
    "logging.basicConfig(encoding=\"utf-8\", level=logging.INFO)\n",
    "logger = logging.getLogger(__name__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setup BQL Queries\n",
    "\n",
    "IQL queries allow bql(...) queries to be embedded within them, and contain a few advanced features like more intelligent pivoting of the results. \n",
    "\n",
    "The \"group\" statements are needed because larger securities will return too many series. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IQL is used fairly minimally here: to pivot the results into a more usable form than combined_df() would provide.\n",
    "# The {{security}} is a Jinja2 template: it's replaced at render time with the value of security.\n",
    "price_query = \"\"\"\n",
    "select * from bql(\"\n",
    "    get(\n",
    "      px_last, px_volume\n",
    "    ) for(\n",
    "      ['{{security}}']\n",
    "    ) with(\n",
    "      dates=range(-29d, 0d),\n",
    "      fill=prev,\n",
    "      currency=USD\n",
    "    )\n",
    "\", pivot=(date, name))\n",
    "    \"\"\"\n",
    "\n",
    "futures_query = \"\"\"\n",
    "select * from bql(\"\n",
    "    get(\n",
    "          px_last\n",
    "        ) for(\n",
    "          futures('{{security}}')\n",
    "        ) with(\n",
    "          dates=range(-29d, 0d),\n",
    "          fill=prev,\n",
    "          currency=USD\n",
    "        )\n",
    "\", pivot=(date, id))\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "@cache\n",
    "def _exec_iql(query: str, **kwargs) -> pd.DataFrame:\n",
    "    query_string = jinja2.Template(query).render(**kwargs)\n",
    "    logger.info(f\"Executing {query_string}\")\n",
    "    df = iql.executedf(query_string)\n",
    "    return df\n",
    "\n",
    "\n",
    "def generate_df(page: nbapp.UiPage, query: str, **kwargs) -> pd.DataFrame:\n",
    "    df = _exec_iql(query, **kwargs)\n",
    "    page.add_df(name=\"anon\", df=df)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def draw_chart_withfutures(futures_df):\n",
    "    fdf = futures_df.set_index(\"DATE\").pct_change()\n",
    "\n",
    "    # Show all columns, except the Date column\n",
    "    fig = px.line(fdf, x=fdf.index, y=fdf.columns)\n",
    "\n",
    "    # Set a Y Axis title and show as a Percentage\n",
    "    fig.update_layout(yaxis=dict(title=\"%\", tickformat=\".2%\"))\n",
    "\n",
    "    return fig\n",
    "\n",
    "\n",
    "def create_chart(price_df, futures_df):\n",
    "    \"\"\"Creates a single figure, comprised of three subplots.\n",
    "\n",
    "    Demonstrates how to create individual plots in plotly.express, and then assemble them into a single figure. This provides the benefit of the simpler plotly.express API, without having to resort directly to the graph_objects API.\n",
    "    \"\"\"\n",
    "\n",
    "    # Create three plotly express figures\n",
    "    lfig = px.line(price_df, x=\"DATE\", y=\"px_last\")\n",
    "    bfig = px.bar(price_df, x=\"DATE\", y=\"px_volume\")\n",
    "    fut_fig = draw_chart_withfutures(futures_df)\n",
    "\n",
    "    fig = make_subplots(\n",
    "        rows=3,\n",
    "        cols=1,\n",
    "        shared_xaxes=True,\n",
    "        subplot_titles=(\"Price\", \"Volume\", \"Futures Daily px_last % Chg\"),\n",
    "        row_heights=[0.3, 0.2, 0.5],\n",
    "    )\n",
    "\n",
    "    # Add the chart data to each row of the figure.\n",
    "    # Could also use add_trace with lfig.data[0], or iterate over each member of lfig.data\n",
    "    fig.add_traces(lfig.data, rows=[1], cols=[1])\n",
    "    fig.add_traces(bfig.data, rows=[2], cols=[1])\n",
    "\n",
    "    # Since fut_fig contains multiple lines, we need to add all of them\n",
    "    # The [3]*len(...) is just a trick to create a series of [3,3,3,3,3] for the length of the number of elements\n",
    "    # A simpler approach would be to iterate over each item and add individually\n",
    "    fig.add_traces(\n",
    "        fut_fig.data, rows=[3] * len(fut_fig.data), cols=[1] * len(fut_fig.data)\n",
    "    )\n",
    "\n",
    "    fig.update_layout(height=600, title=\"Price and Volume\", xaxis=dict(title=\"Date\"))\n",
    "\n",
    "    return fig"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_click(\n",
    "    component: str, action: str, args: str, app: nbapp.UiModel, caller: str\n",
    "):\n",
    "    app.clear_messages()\n",
    "    with app.messages:\n",
    "        try:\n",
    "            app.update_status(caller, message=\"Executing\", running=True)\n",
    "\n",
    "            price_page.clear()\n",
    "            futures_page.clear()\n",
    "            chart_page.clear()\n",
    "\n",
    "            security = app.get_valuestr(\"security\")\n",
    "            print(f\"Querying {security}\")\n",
    "\n",
    "            price_df = generate_df(price_page, price_query, security=security)\n",
    "            futures_df = generate_df(futures_page, futures_query, security=security)\n",
    "\n",
    "            pxfig = create_chart(price_df, futures_df)\n",
    "            chart_page.add_plotly_fig(name=\"pxfig\", fig=pxfig)\n",
    "\n",
    "            app.update_status(caller, message=\"Done\", running=False)\n",
    "        except Exception as e:\n",
    "            logger.exception(\"Error executing\")\n",
    "            app.update_status(caller, message=f\"Error {e}\", running=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "                    if (window.location.href.indexOf('voila') >= 0){\n",
       "                        const l=document.createElement('link');\n",
       "                        l.setAttribute('rel','stylesheet');  \n",
       "                        l.setAttribute('type','text/css');\n",
       "                        l.setAttribute('href',`${window.location.href.split('/').slice(0,7).join('/')}/static/theme-light.css`);\n",
       "                        document.body.appendChild(l);\n",
       "                        document.body.classList.remove('theme-dark')\n",
       "                        document.body.classList.add('theme-light')\n",
       "                    }\n",
       "                </script>\n",
       "                "
      ],
      "text/plain": [
       "<nbappinator.appinator.ThemeFixer at 0x2a217099790>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cc218a9a5f964865b786a5f116736cd9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Html(children=[Html(children=['\\n    .vuetify-styles code, .vuetify-styles kbd, .vuetify-styles samp{\\n       …"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config_page.add_textfield(\n",
    "    name=\"security\", label=\"Enter Security: \", value=\"IBM US Equity\"\n",
    ")\n",
    "\n",
    "config_page.add_button(\n",
    "    name=\"update\", label=\"Execute\", action=execute_click, status=True\n",
    ")\n",
    "\n",
    "myapp.display()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}