tuesday_settlements

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
    traded AS
    (
        SELECT groupArray(session_day) AS session_days
        FROM
        (
            SELECT date AS session_day
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'SPY'
              AND date >= toDate('2014-11-01')
            GROUP BY session_day
        )
    ),
    closures_ahead AS
    (
        SELECT groupArray(date) AS closed_days
        FROM global_markets.stocks_market_holidays
        WHERE status = 'closed'
    )
SELECT
    toString(settlement)                                    AS settlement_date,
    formatDateTime(settlement, '%W')                        AS settlement_weekday,
    concat('VX ', formatDateTime(contract_month, '%b %Y'))  AS vix_contract,
    formatDateTime(if(friday_closed, ref_friday, wednesday_target), '%a %b %e, %Y') AS closed_day_in_the_way
FROM
(
    SELECT
        a.contract_month AS contract_month,
        a.ref_friday     AS ref_friday,
        ((a.ref_friday <= toDate(arrayMax(t.session_days))) AND (NOT has(t.session_days, a.ref_friday)))
            OR has(h.closed_days, a.ref_friday)             AS friday_closed,
        if(friday_closed, addDays(a.ref_friday, -1), a.ref_friday) AS spx_anchor,
        addDays(spx_anchor, -30)                            AS wednesday_target,
        ((wednesday_target <= toDate(arrayMax(t.session_days))) AND (NOT has(t.session_days, wednesday_target)))
            OR has(h.closed_days, wednesday_target)         AS target_closed,
        if(target_closed, addDays(wednesday_target, -1), wednesday_target) AS settlement
    FROM
    (
        SELECT
            contract_month,
            addMonths(contract_month, 1)                                             AS ref_month,
            addDays(ref_month, ((5 - toInt32(toDayOfWeek(ref_month)) + 7) % 7) + 14) AS ref_friday
        FROM
        (
            SELECT addMonths(toDate('2015-01-01'), toInt32(arrayJoin(range(144)))) AS contract_month
        )
    ) AS a
    CROSS JOIN traded AS t
    CROSS JOIN closures_ahead AS h
)
WHERE toDayOfWeek(settlement) <> 3
ORDER BY settlement ASC