Rolling 12-month correlation: daily GLD returns vs daily changes in the 10-year real yield

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
    daily AS
    (
        SELECT
            t.date                                                   AS d,
            toFloat64(t.yield_10_year) - toFloat64(e.market_10_year) AS real_10y,
            toFloat64(g.close)                                       AS gld_close
        FROM global_markets.treasury_yields AS t
        INNER JOIN global_markets.inflation_expectations AS e ON e.date = t.date
        INNER JOIN
        (
            SELECT
                date,
                max(close) AS close
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'GLD'
              AND date >= '2005-01-01'
            GROUP BY date
        ) AS g ON g.date = t.date
        WHERE t.date >= '2005-01-01'
          AND t.yield_10_year > 0
          AND e.market_10_year > 0
    ),
    changes AS
    (
        SELECT
            d,
            real_10y - prev_real       AS real_chg,
            gld_close / prev_close - 1 AS gld_ret
        FROM
        (
            SELECT
                d,
                real_10y,
                gld_close,
                lagInFrame(real_10y)  OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_real,
                lagInFrame(gld_close) OVER (ORDER BY d ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_close
            FROM daily
        )
        WHERE prev_close > 0
    ),
    quarters AS
    (
        SELECT DISTINCT toStartOfQuarter(d) AS q
        FROM changes
        WHERE d >= '2006-01-01'
          AND addMonths(toStartOfQuarter(d), 3) <= today()
    )
SELECT
    toString(qs.q)                                                     AS quarter,
    concat('Q', toString(toQuarter(qs.q)), ' ', toString(toYear(qs.q))) AS quarter_label,
    round(corr(c.real_chg, c.gld_ret), 2)                              AS corr_12m,
    count()                                                            AS sessions
FROM quarters AS qs
CROSS JOIN changes AS c
WHERE c.d > addMonths(qs.q, -9)
  AND c.d < addMonths(qs.q, 3)
GROUP BY qs.q
ORDER BY qs.q