iv_vs_rv

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
universe AS (
    SELECT ticker
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 150
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING argMax(market_cap, date) > 20000000000
       AND argMax(average_volume, date) > 5000000
       AND argMax(price, date) > 10
),
series AS (
    SELECT
        ticker,
        arrayMap(t -> t.2, arraySort(t -> t.1, groupArray((date, toFloat64(close))))) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN (SELECT ticker FROM universe)
      AND date >= today() - 120
      AND date <  today()
      AND close > 0
    GROUP BY ticker
),
realized AS (
    SELECT
        ticker,
        toUInt32(round(arrayReduce('stddevSamp', arraySlice(r, -30)) * sqrt(252) * 100)) AS rv_30d_pct
    FROM
    (
        SELECT
            ticker,
            arrayMap((x, y) -> log(x / y),
                     arraySlice(px, 2),
                     arraySlice(px, 1, length(px) - 1)) AS r
        FROM series
    )
    WHERE length(r) >= 30
    ORDER BY rv_30d_pct DESC
    LIMIT 10
),
implied AS (
    SELECT
        underlying_symbol                                      AS ticker,
        toUInt32(round(avg(implied_volatility) * 100))         AS iv_30d_pct,
        count()                                                AS contract_days
    FROM global_markets.options_greeks
    WHERE date >= today() - 30
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 20 AND 45
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
      AND underlying_symbol IN (SELECT ticker FROM realized)
    GROUP BY underlying_symbol
    HAVING contract_days >= 20
)
SELECT
    r.ticker                                      AS ticker,
    r.rv_30d_pct                                  AS rv_30d_pct,
    i.iv_30d_pct                                  AS iv_30d_pct,
    toInt32(i.iv_30d_pct) - toInt32(r.rv_30d_pct) AS spread_pp
FROM realized AS r
INNER JOIN implied AS i ON i.ticker = r.ticker
ORDER BY r.rv_30d_pct DESC