vxx_monthly

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 splits AS
(
    SELECT
        groupArray(execution_date) AS split_dates,
        groupArray(price_factor)   AS price_factors
    FROM
    (
        SELECT
            execution_date,
            toFloat64(any(split_from)) / toFloat64(any(split_to)) AS price_factor
        FROM global_markets.stocks_splits
        WHERE ticker = 'VXX'
        GROUP BY execution_date
    )
),
daily AS
(
    SELECT
        a.date AS date,
        toFloat64(a.close) * arrayProduct(arrayMap((d, f) -> if(d > a.date, f, 1.0), s.split_dates, s.price_factors)) AS adj_close
    FROM global_markets.stocks_daily_aggs AS a
    CROSS JOIN splits AS s
    WHERE a.ticker = 'VXX'
      AND a.date >= toDate('2019-01-01')
),
month_end AS
(
    SELECT
        toStartOfMonth(date)    AS month_start,
        argMax(adj_close, date) AS adj_close
    FROM daily
    GROUP BY month_start
)
SELECT
    toString(month_start)                AS month,
    formatDateTime(month_start, '%b %Y') AS month_label,
    round(adj_close, 2)                  AS vxx_close_adj,
    round(100 * (adj_close / first_value(adj_close) OVER (ORDER BY month_start) - 1), 1) AS change_pct
FROM month_end
ORDER BY month_start