import nbappinator as nbapp
import iql

import logging
import jinja2
from functools import cache

import pandas as pd

import plotly.express as px
from plotly.subplots import make_subplots

# Setup some globals for the application. The page globals are optional.

PAGES = ["Chart", "Price", "Futures"]
myapp = nbapp.TabbedUiModel(pages=PAGES, log_footer="Messages", headers=["Config"])
chart_page = myapp.get_page(PAGES[0])
price_page = myapp.get_page(PAGES[1])
futures_page = myapp.get_page(PAGES[2])
config_page = myapp.get_page("Config")

logging.basicConfig(encoding="utf-8", level=logging.INFO)
logger = logging.getLogger(__name__)

# IQL is used fairly minimally here: to pivot the results into a more usable form than combined_df() would provide.
# The {{security}} is a Jinja2 template: it's replaced at render time with the value of security.
price_query = """
select * from bql("
    get(
      px_last, px_volume
    ) for(
      ['{{security}}']
    ) with(
      dates=range(-29d, 0d),
      fill=prev,
      currency=USD
    )
", pivot=(date, name))
    """

futures_query = """
select * from bql("
    get(
          px_last
        ) for(
          futures('{{security}}')
        ) with(
          dates=range(-29d, 0d),
          fill=prev,
          currency=USD
        )
", pivot=(date, id))
"""


@cache
def _exec_iql(query: str, **kwargs) -> pd.DataFrame:
    query_string = jinja2.Template(query).render(**kwargs)
    logger.info(f"Executing {query_string}")
    df = iql.executedf(query_string)
    return df


def generate_df(page: nbapp.UiPage, query: str, **kwargs) -> pd.DataFrame:
    df = _exec_iql(query, **kwargs)
    page.add_df(name="anon", df=df)
    return df

def draw_chart_withfutures(futures_df):
    fdf = futures_df.set_index("DATE").pct_change()

    # Show all columns, except the Date column
    fig = px.line(fdf, x=fdf.index, y=fdf.columns)

    # Set a Y Axis title and show as a Percentage
    fig.update_layout(yaxis=dict(title="%", tickformat=".2%"))

    return fig


def create_chart(price_df, futures_df):
    """Creates a single figure, comprised of three subplots.

    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.
    """

    # Create three plotly express figures
    lfig = px.line(price_df, x="DATE", y="px_last")
    bfig = px.bar(price_df, x="DATE", y="px_volume")
    fut_fig = draw_chart_withfutures(futures_df)

    fig = make_subplots(
        rows=3,
        cols=1,
        shared_xaxes=True,
        subplot_titles=("Price", "Volume", "Futures Daily px_last % Chg"),
        row_heights=[0.3, 0.2, 0.5],
    )

    # Add the chart data to each row of the figure.
    # Could also use add_trace with lfig.data[0], or iterate over each member of lfig.data
    fig.add_traces(lfig.data, rows=[1], cols=[1])
    fig.add_traces(bfig.data, rows=[2], cols=[1])

    # Since fut_fig contains multiple lines, we need to add all of them
    # 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
    # A simpler approach would be to iterate over each item and add individually
    fig.add_traces(
        fut_fig.data, rows=[3] * len(fut_fig.data), cols=[1] * len(fut_fig.data)
    )

    fig.update_layout(height=600, title="Price and Volume", xaxis=dict(title="Date"))

    return fig

def execute_click(
    component: str, action: str, args: str, app: nbapp.UiModel, caller: str
):
    app.clear_messages()
    with app.messages:
        try:
            app.update_status(caller, message="Executing", running=True)

            price_page.clear()
            futures_page.clear()
            chart_page.clear()

            security = app.get_valuestr("security")
            print(f"Querying {security}")

            price_df = generate_df(price_page, price_query, security=security)
            futures_df = generate_df(futures_page, futures_query, security=security)

            pxfig = create_chart(price_df, futures_df)
            chart_page.add_plotly_fig(name="pxfig", fig=pxfig)

            app.update_status(caller, message="Done", running=False)
        except Exception as e:
            logger.exception("Error executing")
            app.update_status(caller, message=f"Error {e}", running=False)

config_page.add_textfield(
    name="security", label="Enter Security: ", value="IBM US Equity"
)

config_page.add_button(
    name="update", label="Execute", action=execute_click, status=True
)

myapp.display()

BQuant [8] - Futures App

Futures Daily Returns

BQuant Example

Prerequisite

bqnt_futures.ipynb

Import Packages¶

Setup BQL Queries¶