איך מודדים תשואה חודשית של מניה

SELECT ticker,
       round(argMaxIf(cl, day, day <= '2026-05-31'), 2) AS may_final_close,
       round(argMinIf(op, day, day >= '2026-06-01'), 2) AS june_first_open,
       round(argMaxIf(cl, day, day <= '2026-06-30'), 2) AS june_final_close,
       round(100 * (argMaxIf(cl, day, day <= '2026-06-30') - argMaxIf(cl, day, day <= '2026-05-31'))
             / argMaxIf(cl, day, day <= '2026-05-31'), 2) AS close_to_close_pct,
       round(100 * (argMaxIf(cl, day, day <= '2026-06-30') - argMinIf(op, day, day >= '2026-06-01'))
             / argMinIf(op, day, day >= '2026-06-01'), 2) AS open_to_close_pct,
       round(abs(100 * (argMaxIf(cl, day, day <= '2026-06-30') - argMinIf(op, day, day >= '2026-06-01'))
                 / argMinIf(op, day, day >= '2026-06-01')
             - 100 * (argMaxIf(cl, day, day <= '2026-06-30') - argMaxIf(cl, day, day <= '2026-05-31'))
                 / argMaxIf(cl, day, day <= '2026-05-31')), 2) AS convention_gap_points
FROM (
    SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               argMinIf(toFloat64(open), window_start, rth) AS op,
               argMaxIf(toFloat64(close), window_start, rth) AS cl
        FROM (
            SELECT ticker, window_start, open, close,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker IN ('NVDA', 'SPY')
              AND window_start >= '2026-05-28 04:00:00'
              AND window_start < '2026-07-01 08:00:00'
        )
        GROUP BY ticker, day
)
GROUP BY ticker
ORDER BY ticker

WITH px AS (
    SELECT argMaxIf(cl, day, day <= '2026-05-31') AS may_close,
           argMaxIf(cl, day, day <= '2026-06-30') AS jun_close
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               argMinIf(toFloat64(open), window_start, rth) AS op,
               argMaxIf(toFloat64(close), window_start, rth) AS cl
        FROM (
            SELECT ticker, window_start, open, close,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker IN ('NVDA', 'SPY')
              AND window_start >= '2026-05-28 04:00:00'
              AND window_start < '2026-07-01 08:00:00'
        )
        GROUP BY ticker, day
    )
    WHERE ticker = 'SPY'
),
dv AS (
    SELECT sum(cash_amount) AS div_per_share
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND ex_dividend_date >= '2026-06-01'
      AND ex_dividend_date <= '2026-06-30'
)
SELECT round(may_close, 2) AS may_final_close,
       round(jun_close, 2) AS june_final_close,
       round(div_per_share, 4) AS june_dividend_per_share,
       round(100 * (jun_close - may_close) / may_close, 2) AS price_return_pct,
       round(100 * div_per_share / may_close, 2) AS dividend_contribution_pct,
       round(100 * ((jun_close + div_per_share) - may_close) / may_close, 2) AS total_return_pct
FROM px CROSS JOIN dv

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           argMaxIf(toFloat64(close), window_start,
               toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
               AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York')) AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND ((window_start >= '2021-06-28 04:00:00' AND window_start < '2021-07-01 08:00:00')
        OR (window_start >= '2026-06-26 04:00:00' AND window_start < '2026-07-01 08:00:00'))
    GROUP BY day
),
px AS (
    SELECT argMaxIf(cl, day, day <= '2021-06-30') AS start_close,
           argMaxIf(cl, day, day <= '2026-06-30') AS end_close
    FROM daily
),
dv AS (
    SELECT round(sum(cash_amount), 2) AS divs_per_share,
           count() AS payments
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND ex_dividend_date >= '2021-07-01'
      AND ex_dividend_date <= '2026-06-30'
)
SELECT round(start_close, 2) AS june_2021_close,
       round(end_close, 2) AS june_2026_close,
       divs_per_share AS dividends_paid_per_share,
       payments AS dividend_payments,
       round(100 * (end_close - start_close) / start_close, 2) AS five_year_price_return_pct,
       round(100 * ((end_close + divs_per_share) - start_close) / start_close, 2) AS five_year_return_with_divs_pct,
       round(100 * divs_per_share / start_close, 2) AS dividend_points_added
FROM px CROSS JOIN dv

WITH px AS (
    SELECT argMaxIf(cl, day, day <= '2024-05-31') AS may_close,
           argMaxIf(cl, day, day <= '2024-06-30') AS jun_close
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
               argMaxIf(toFloat64(close), window_start,
                   toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
                   AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York')) AS cl
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'NVDA'
          AND window_start >= '2024-05-29 04:00:00'
          AND window_start < '2024-07-01 08:00:00'
        GROUP BY day
    )
),
sp AS (
    SELECT split_to / split_from AS ratio
    FROM global_markets.stocks_splits
    WHERE ticker = 'NVDA'
      AND execution_date >= '2024-06-01'
      AND execution_date <= '2024-06-30'
)
SELECT round(may_close, 2) AS may_final_close_raw,
       round(jun_close, 2) AS june_final_close_raw,
       toInt32(ratio) AS shares_after_split_per_old_share,
       round(100 * (jun_close - may_close) / may_close, 2) AS raw_price_change_pct,
       round(may_close / ratio, 2) AS may_close_split_adjusted,
       round(100 * (jun_close - may_close / ratio) / (may_close / ratio), 2) AS adjusted_return_pct
FROM px CROSS JOIN sp

SELECT round(argMaxIf(toFloat64(close), window_start, rth), 2) AS regular_session_close,
       round(argMax(toFloat64(close), window_start), 2) AS last_extended_print,
       round(100 * (argMax(toFloat64(close), window_start) - argMaxIf(toFloat64(close), window_start, rth))
             / argMaxIf(toFloat64(close), window_start, rth), 3) AS after_hours_drift_pct
FROM (
    SELECT window_start, close,
           toTimeZone(window_start, 'America/New_York') >= toDateTime('2026-06-30 09:30:00', 'America/New_York')
           AND toTimeZone(window_start, 'America/New_York') < toDateTime('2026-06-30 16:00:00', 'America/New_York') AS rth
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2026-06-30 04:00:00'
      AND window_start < '2026-07-01 08:00:00'
)

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           argMaxIf(toFloat64(close), window_start,
               toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
               AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York')) AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2025-06-24 04:00:00'
      AND window_start < '2026-07-01 08:00:00'
    GROUP BY day
),
monthly AS (
    SELECT toStartOfMonth(day) AS month_start,
           argMax(cl, day) AS month_close
    FROM daily
    GROUP BY month_start
),
rets AS (
    SELECT month_start,
           formatDateTime(month_start, '%Y-%m') AS month,
           round(month_close, 2) AS month_final_close,
           100 * (month_close / lagInFrame(month_close) OVER (ORDER BY month_start) - 1) AS r
    FROM monthly
)
SELECT month,
       month_final_close,
       round(r, 2) AS simple_return_pct,
       round(100 * log(1 + r / 100), 2) AS log_return_pct
FROM rets
WHERE isFinite(r)
ORDER BY month_start

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           argMaxIf(toFloat64(close), window_start,
               toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
               AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York')) AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2025-06-24 04:00:00'
      AND window_start < '2026-07-01 08:00:00'
    GROUP BY day
),
monthly AS (
    SELECT toStartOfMonth(day) AS month_start,
           argMax(cl, day) AS month_close
    FROM daily
    GROUP BY month_start
),
rets AS (
    SELECT month_start,
           100 * (month_close / lagInFrame(month_close) OVER (ORDER BY month_start) - 1) AS r
    FROM monthly
),
ends AS (
    SELECT argMin(month_close, month_start) AS base_close,
           argMax(month_close, month_start) AS final_close
    FROM monthly
)
SELECT count() AS months_linked,
       round(sum(r), 2) AS simple_sum_pct,
       round(100 * (exp(sum(log(1 + r / 100))) - 1), 2) AS geometric_compound_pct,
       round(100 * (any(final_close) / any(base_close) - 1), 2) AS direct_twelve_month_pct,
       round(100 * (exp(sum(log(1 + r / 100))) - 1) - sum(r), 2) AS compounding_gap_points,
       round(sum(100 * log(1 + r / 100)), 2) AS sum_of_monthly_log_returns_pct,
       round(100 * log(any(final_close) / any(base_close)), 2) AS twelve_month_log_return_pct
FROM rets CROSS JOIN ends
WHERE isFinite(r)