fx_vs_equity

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
px AS (
    SELECT
        date,
        ticker,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'FXE')
      AND date >= today() - 1130
      AND date <  today()
      AND close > 0
),
rets AS (
    SELECT
        ticker,
        date,
        log(c / lagInFrame(c) OVER (PARTITION BY ticker ORDER BY date
                                    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS ret
    FROM px
),
paired AS (
    SELECT
        date,
        anyIf(ret, ticker = 'SPY') AS r_spy,
        anyIf(ret, ticker = 'FXE') AS r_fx
    FROM rets
    WHERE isFinite(ret)
    GROUP BY date
    HAVING countIf(ticker = 'SPY') = 1
       AND countIf(ticker = 'FXE') = 1
)
SELECT
    formatDateTime(toStartOfMonth(date), '%Y-%m')          AS month,
    toUInt32(round(stddevSamp(r_spy) * sqrt(252) * 100))   AS vol_spy_pct,
    toUInt32(round(stddevSamp(r_fx)  * sqrt(252) * 100))   AS vol_eur_usd_pct,
    toInt32(round(corr(r_spy, r_fx) * 100))                AS correlation_pct
FROM paired
GROUP BY month
HAVING count() >= 10
ORDER BY month