gross_vs_net

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
        ticker,
        ex_dividend_date,
        max(cash_amount) AS amount
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= addMonths(toStartOfMonth(today()), -12)
      AND ex_dividend_date <  toStartOfMonth(today())
      AND currency = 'USD'
      AND cash_amount > 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, ex_dividend_date
),
payers AS (
    SELECT
        ticker,
        round(toFloat64(sum(amount)), 4) AS annual_usd
    FROM monthly
    GROUP BY ticker
    HAVING count() = 12
       AND countDistinct(toStartOfMonth(ex_dividend_date)) = 12
),
last_price AS (
    SELECT
        ticker,
        toFloat64(argMax(close, date)) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 30
      AND ticker IN (SELECT ticker FROM payers)
    GROUP BY ticker
)
SELECT
    p.ticker                                                AS ticker,
    round(100 * p.annual_usd / x.last_close, 2)             AS gross_yield_pct,
    round(100 * p.annual_usd * 0.70 / x.last_close, 2)      AS net_yield_pct
FROM payers AS p
INNER JOIN last_price AS x ON x.ticker = p.ticker
WHERE x.last_close >= 5
  AND 100 * p.annual_usd / x.last_close BETWEEN 2 AND 25
ORDER BY 100 * p.annual_usd / x.last_close DESC
LIMIT 12