implied_vs_realised

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
reports AS
(
    SELECT DISTINCT filing_date AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'AAPL'
      AND startsWith(form_type, '8-K')
      AND filing_date >= '2023-09-01'
      AND (positionCaseInsensitive(items_text, 'Results of Operations') > 0
           OR positionCaseInsensitive(items_text, 'Item 2.02') > 0)
),
bars AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2023-08-01'
    GROUP BY date
),
seq AS
(
    SELECT date, open_px, close_px, row_number() OVER (ORDER BY date) AS n
    FROM bars
),
per_report AS
(
    SELECT
        d0.date                                  AS report_date,
        abs(d1.close_px / d0.close_px - 1) * 100 AS abs_full_day_pct
    FROM seq AS d0
    INNER JOIN seq AS d1 ON d1.n = d0.n + 1
    INNER JOIN reports AS r ON r.report_date = d0.date
),
atm_raw AS
(
    SELECT
        date,
        days_to_expiry,
        implied_volatility
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= '2023-09-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 1 AND 10
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.025
),
nearest AS
(
    SELECT date, min(days_to_expiry) AS dte
    FROM atm_raw
    GROUP BY date
),
atm AS
(
    SELECT
        a.date                                                                   AS date,
        round(avg(a.implied_volatility * sqrt(a.days_to_expiry / 365)) * 100, 2) AS implied_move_pct
    FROM atm_raw AS a
    INNER JOIN nearest AS n ON n.date = a.date AND a.days_to_expiry = n.dte
    GROUP BY a.date
    HAVING count() > 0
)
SELECT
    toString(p.report_date)                                   AS report_date,
    a.implied_move_pct                                        AS implied_move_pct,
    round(p.abs_full_day_pct, 2)                              AS realised_move_pct,
    round(p.abs_full_day_pct - a.implied_move_pct, 2)         AS realised_minus_implied_pct
FROM per_report AS p
INNER JOIN atm AS a ON a.date = p.report_date
ORDER BY p.report_date