recovery_path_2007

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 monthly AS
(
    SELECT
        toStartOfMonth(date)           AS month,
        argMax(toFloat64(close), date) AS month_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2007-10-01'
      AND date <  '2014-01-01'
    GROUP BY month
),
div_cash AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        max(toFloat64(cash_amount)) AS cash
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND ex_dividend_date >= '2007-11-01'
      AND ex_dividend_date <  '2014-01-01'
      AND cash_amount > 0
    GROUP BY ex_dividend_date
),
div_log AS
(
    SELECT
        toStartOfMonth(c.ex_date)             AS month,
        sum(log(1 + c.cash / p.close_on_ex))  AS log_factor
    FROM div_cash AS c
    INNER JOIN
    (
        SELECT
            date                 AS date,
            toFloat64(close)     AS close_on_ex
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2007-11-01'
          AND date <  '2014-01-01'
    ) AS p ON p.date = c.ex_date
    GROUP BY month
),
path AS
(
    SELECT
        m.month                 AS month,
        m.month_close           AS month_close,
        ifNull(d.log_factor, 0) AS log_factor
    FROM monthly AS m
    LEFT JOIN div_log AS d ON d.month = m.month
)
SELECT
    toString(month)                AS month_start,
    formatDateTime(month, '%b %Y') AS month_label,
    round(100 * (month_close
        / first_value(month_close) OVER (ORDER BY month) - 1), 1) AS price_pct,
    round(100 * (month_close * exp(sum(log_factor) OVER (ORDER BY month))
        / first_value(month_close) OVER (ORDER BY month) - 1), 1) AS total_return_pct
FROM path
ORDER BY month