Monthly payers yielding above 12% - the price move and the payment move behind the number

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,
           argMax(cash_amount, ex_dividend_date) AS latest_payment,
           argMin(cash_amount, ex_dividend_date) AS oldest_payment
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date > today() - 400
      AND ex_dividend_date <= today()
      AND cash_amount > 0
      AND distribution_type = 'recurring'
      AND ticker NOT IN ('SPCX')
      AND ticker NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 400 AND today())
    GROUP BY ticker
    HAVING countIf(ex_dividend_date > today() - 365) BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
       AND oldest_payment > 0
),
filers AS (
    SELECT ticker
    FROM global_markets.stocks_ratios
    GROUP BY ticker
    HAVING argMax(market_cap, date) > 0
),
tape AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(close) * volume) AS dollar_vol,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM monthly)
      AND (window_start >= toDateTime(today() - 32)
           OR (window_start >= toDateTime(today() - 378) AND window_start < toDateTime(today() - 358)))
      AND toDate(toTimeZone(window_start, 'America/New_York')) < today()
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    GROUP BY ticker, d
),
px AS (
    SELECT ticker,
           avgIf(dollar_vol, d > today() - 32) AS adv_usd,
           argMaxIf(close_px, d, d > today() - 32) AS last_close,
           argMaxIf(close_px, d, d <= today() - 358) AS close_year_ago,
           countIf(d > today() - 32) AS recent_sessions,
           countIf(d <= today() - 358) AS old_sessions
    FROM tape
    GROUP BY ticker
    HAVING recent_sessions >= 15
       AND old_sessions > 0
       AND adv_usd >= 1000000
       AND last_close > 0
       AND close_year_ago > 0
)
SELECT m.ticker AS ticker,
       round(100 * m.latest_payment * 12 / p.last_close, 2) AS annual_yield_pct,
       round(100 * (p.last_close - p.close_year_ago) / p.close_year_ago, 1) AS price_change_12m_pct,
       round(100 * (m.latest_payment - m.oldest_payment) / m.oldest_payment, 1) AS payment_change_12m_pct,
       round(p.adv_usd / 1e6, 1) AS avg_daily_traded_musd
FROM monthly m
INNER JOIN px p ON p.ticker = m.ticker
INNER JOIN filers f ON f.ticker = m.ticker
WHERE 100 * m.latest_payment * 12 / p.last_close >= 12
ORDER BY annual_yield_pct DESC
LIMIT 25