Tech Blog

Blog About

Go to Iqmo's GitHub repo

Mar 4, 2024

BQuant [7] - OMON App

---

OMON in BQuant

OMON shows options near the current strike price for a given security. The Calls/Puts puts this view side by side, a common way to visualize the option chain.

In this demo application, we’ll show how to reproduce this view and how to build interactive charts that show the historical price movement for selected options.

BQuant Example

Prerequisite

To run this example, follow the steps in BQuant - Opening Blog Examples to create a Custom Environment that includes nbappinator and the notebook code below.

bqnt_omon_app.ipynb

Install IQL

IQL is a query engine (developed by the Iqmo team) that allows, among other things, BQL queries to be executed within a SQL framework.

This allows seamless interplay between a sophisticated SQL database engine and the breadth of BQL data.

import iql

import nbappinator as nbapp
import jinja2
import logging
import pandas as pd
import plotly.express as px

from functools import cache, partial

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

Setup BQL Queries

IQL queries allow bql(...) queries to be embedded within them, and contain a few advanced features like more intelligent pivoting of the results.

The "group" statements are needed because larger securities will return too many series.

options_query = """
select * from bql("
        get (
            group(ID_SECURITY_DES) as #ID_SECURITY_DES, 
            group(put_call) as #put_call, 
            group(open_int) as #open_int, 
            group(strike_px) as #strike_px, 
            group(px_last) as #px_last, 
            group(px_ask) as #px_ask, 
            group(ivol(mid)) as #ivol_mid, 
            group(px_mid) as #px_mid,
            group(px_bid) as #px_bid, 
            group(expire_dt) as #expire_dt, 
            group(px_volume) as #px_volume, 
            group(delta) as #delta, 
            group(PCT_MONEYNESS) as #PCT_MONEYNESS, 
            group(div_yield) as #div_yield, 
            group(exer_typ) as #exer_typ, 
            group(expiration_periodicity) as #expiration_periodicity
        ) 
        for (
            options('{{security}}')
        )
        ", pivot=(ORIG_IDS,name))
"""

strike_query = """
select * from bql("
    get(
        px_last
    ) 
    for(
        '{{security}}'
    )
    ", pivot=(ID,name))
"""


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

    return iql.execute(query_string)

OMON Query

This is not as complicated as it looks. A lot of the code here is used to flatten the calls and puts so they appear side by side.

omon_query = """WITH 
    --- split into calls and puts
    calls as (SELECT * from options_df where put_call = 'Call'),
    puts as (SELECT * from options_df where put_call = 'Put'),
    --- full outer join calls and puts - side by side
    callputs as (SELECT ifnull(calls.expire_dt, puts.expire_dt) as expire_dt, 
        ifnull(calls.strike_px, puts.strike_px) as strike_px, 
        ifnull(calls.expiration_periodicity, puts.expiration_periodicity) expiration_periodicity, 
        calls.open_int as oint_calls, puts.open_int as oint_puts, calls.ivol_mid as IVM_calls, puts.ivol_mid as IVM_puts, calls.ID_SECURITY_DES as id_calls, puts.ID_SECURITY_DES as id_puts,
        calls.px_volume as vol_calls, puts.px_volume as vol_puts, calls.px_mid as mid_calls, puts.px_mid as mid_puts, calls.px_bid as bid_calls, puts.px_bid as bid_puts, calls.px_last as last_calls, puts.px_last as last_puts,
        calls.px_ask as ask_calls, puts.px_ask as ask_puts
        FROM calls 
        FULL OUTER JOIN puts 
            on calls.expire_dt = puts.expire_dt and calls.expiration_periodicity = puts.expiration_periodicity and calls.strike_px = puts.strike_px
        ),
    --- calculate the strike price distance from current_strikes_df spot price. This is used to take the top 5 closest strike prices.
    callputs_with_distance as (SELECT callputs.*, abs(callputs.strike_px - current_strikes_df.px_last) distance FROM callputs JOIN current_strikes_df on true)   

    SELECT id_calls, strike_px, bid_calls, ask_calls, last_calls, IVM_calls, vol_calls, oint_calls, id_puts, strike_px, bid_puts, ask_puts, last_puts, IVM_puts, vol_puts, oint_puts
    from 
        --- Filter to the Monthly Top 5 for each expiration
        (SELECT *, rank() OVER (PARTITION BY expire_dt ORDER BY distance asc) ranking FROM callputs_with_distance)
    WHERE ranking <= 5 and expiration_periodicity = 'Monthly'
    ORDER BY expire_dt, strike_px
    """


def get_omon_view(security):
    try:
        options_df = _exec_iql(options_query, security=security)  # noqa
        current_strikes_df = _exec_iql(strike_query, security=security)  # noqa

        omon_view = iql.execute(omon_query)
    except Exception:
        logger.exception("Error running query, generating fake data for demo purposes")
        omon_view = pd.DataFrame({"Dummy Data": range(100), "Value": range(100)})
    return omon_view

historical_px = """get(px_last)
for({{idents}})
with(dates=range(-1M, 0d), currency='USD', fill=prev)
"""


def click_action(info: dict, app: nbapp.UiModel):
    with app.messages:
        try:
            page = app.get_page(PAGES[0])

            page.add_textstatic(f"You clicked {info}")

            ids = []
            for row in info["currentSelection"]:
                ids.append(row["data"]["id_calls"])
                ids.append(row["data"]["id_puts"])

            df = _exec_iql(historical_px, idents=str(ids))
            fig = px.line(df, x="DATE", y="value", color="id")

            app.clear_container("chartbox")
            chartbox = app.get_container("chartbox")
            chartbox.add_plotly_fig(name="f1", fig=fig)
        except Exception:
            logger.exception("Error in click_action")

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)

            page = app.get_page(PAGES[0])
            page.clear()

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

            omon_df = get_omon_view(security)

            grid_options = {
                "getRowStyle": """
                    function(params) {
                        if ((params.node.rowIndex + 1) % 5 === 0) {
                            return {'borderBottom': '2px solid black'};
                        }
                    }
                """,
            }
            click_action_p = partial(click_action, app=app)
            page.add_df(
                name="df1",
                df=omon_df,
                action=click_action_p,
                grid_options=grid_options,
                multiselect=True,
            )
            app.update_status(
                caller, message="Executing historical query", running=True
            )

            page.add_box(name="chartbox", horiz=False)

            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)

myapp = nbapp.TabbedUiModel(pages=PAGES, log_footer="Messages", headers=["Config"])

config_page = myapp.get_page("Config")
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()

© 2024 Iqmo. All rights reserved.

Go to Iqmo's GitHub repo