Month-end price of movement: the last at-the-money straddle of June

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 (
    SELECT argMaxIf(toFloat64(close), window_start, (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199)
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'MU' AND window_start >= toDateTime('2026-06-30 00:00:00') AND window_start < toDateTime('2026-07-01 00:00:00')
) AS spot
SELECT
    toString(any(expiry)) AS nearest_expiry,
    dateDiff('day', toDate('2026-06-30'), any(expiry)) AS calendar_days_to_expiry,
    round(spot, 2) AS spot_close,
    argMin(strike_usd, (dist, strike_usd)) AS atm_strike,
    round(argMin(call_last + put_last, (dist, strike_usd)), 2) AS straddle_usd,
    round(argMin(call_last + put_last, (dist, strike_usd)) / spot * 100, 2) AS straddle_pct_of_spot,
    count() AS two_sided_strikes
FROM (
    SELECT expiry, strike_usd, abs(strike_usd - spot) AS dist,
        anyIf(last_px, opt_type = 'C') AS call_last,
        anyIf(last_px, opt_type = 'P') AS put_last
    FROM (
        SELECT
            toDateOrNull(concat('20', substring(ticker, 5, 2), '-', substring(ticker, 7, 2), '-', substring(ticker, 9, 2))) AS expiry,
            substring(ticker, 11, 1) AS opt_type,
            toFloat64(toUInt32OrZero(substring(ticker, 12, 8))) / 1000 AS strike_usd,
            argMax(toFloat64(price), (sip_timestamp, toFloat64(price))) AS last_px
        FROM global_markets.options_trades
        WHERE startsWith(ticker, 'O:MU') AND length(ticker) = 19
          AND sip_timestamp >= toDateTime64('2026-06-30 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
        GROUP BY expiry, opt_type, strike_usd
    )
    WHERE expiry > toDate('2026-06-30')
    GROUP BY expiry, strike_usd
    HAVING countIf(opt_type = 'C') > 0 AND countIf(opt_type = 'P') > 0
)
WHERE expiry = (
    SELECT min(toDateOrNull(concat('20', substring(ticker, 5, 2), '-', substring(ticker, 7, 2), '-', substring(ticker, 9, 2))))
    FROM global_markets.options_trades
    WHERE startsWith(ticker, 'O:MU') AND length(ticker) = 19
      AND sip_timestamp >= toDateTime64('2026-06-30 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
      AND toDateOrNull(concat('20', substring(ticker, 5, 2), '-', substring(ticker, 7, 2), '-', substring(ticker, 9, 2))) > toDate('2026-06-30')
)