horizons

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.

SELECT
    if(p.years = 1, '1 ปี (52 สัปดาห์)', concat(toString(p.years), ' ปี'))   AS horizon,
    p.held_from                                                              AS held_from,
    p.priced_at                                                              AS priced_at,
    round(p.start_close, 2)                                                  AS start_close,
    round(p.period_high, 2)                                                  AS period_high,
    round((p.latest_close / p.start_close - 1) * 100, 1)                     AS price_return_pct,
    round((p.latest_close + d.dividends - p.start_close) / p.start_close * 100, 1) AS total_return_pct,
    round((1 - p.latest_close / p.period_high) * 100, 1)                     AS pct_below_high,
    round(p.max_drawdown, 1)                                                 AS max_drawdown_pct
FROM
(
    SELECT
        years,
        formatDateTime(min(date), '%d %b %Y')      AS held_from,
        formatDateTime(max(date), '%d %b %Y')      AS priced_at,
        argMin(close_f, date)                      AS start_close,
        argMax(close_f, date)                      AS latest_close,
        max(high_f)                                AS period_high,
        min((close_f / running_high - 1) * 100)    AS max_drawdown
    FROM
    (
        SELECT
            years,
            date,
            toFloat64(close) AS close_f,
            toFloat64(high)  AS high_f,
            max(toFloat64(close)) OVER (PARTITION BY years ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_high
        FROM global_markets.stocks_daily_aggs
        ARRAY JOIN [1, 3, 5] AS years
        WHERE ticker = 'AAPL'
          AND date >  subtractYears(today(), years)
          AND date <= today()
    )
    GROUP BY years
) AS p
LEFT JOIN
(
    SELECT
        years,
        sum(cash_amount) AS dividends
    FROM
    (
        SELECT
            ex_dividend_date,
            max(toFloat64(cash_amount)) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'AAPL'
          AND ex_dividend_date >  subtractYears(today(), 5)
          AND ex_dividend_date <= today()
        GROUP BY ex_dividend_date
    )
    ARRAY JOIN [1, 3, 5] AS years
    WHERE ex_dividend_date > subtractYears(today(), years)
    GROUP BY years
) AS d ON d.years = p.years
ORDER BY p.years