毎月配当株の全リスト

SELECT m.ticker AS ticker,
       round(m.latest_payment, 4) AS monthly_payment_usd,
       round(100 * m.latest_payment * 12 / l.last_close, 2) AS annual_yield_pct,
       round(dateDiff('day', s.streak_start, s.last_ex) / 365.25, 1) AS years_paying_monthly,
       round(l.adv_usd / 1e6, 1) AS avg_daily_traded_musd,
       formatDateTime(l.last_session, '%b %e') AS priced_through
FROM (
    SELECT ticker, argMax(cash_amount, ex_dividend_date) AS latest_payment
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date > today() - 365
      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 count() BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
) AS m
INNER JOIN (
    SELECT ticker,
           maxIf(ed, gap_days > 70) AS streak_start,
           max(ed) AS last_ex
    FROM (
        SELECT ticker, ex_dividend_date AS ed,
               dateDiff('day',
                        lagInFrame(ex_dividend_date) OVER (PARTITION BY ticker ORDER BY ex_dividend_date),
                        ex_dividend_date) AS gap_days
        FROM (
            SELECT ticker, ex_dividend_date
            FROM global_markets.stocks_dividends
            WHERE distribution_type = 'recurring' AND cash_amount > 0 AND ex_dividend_date <= today()
            GROUP BY ticker, ex_dividend_date
        )
    )
    GROUP BY ticker
) AS s ON s.ticker = m.ticker
INNER JOIN (
    SELECT ticker,
           avg(dollar_vol) AS adv_usd,
           argMax(close_px, d) AS last_close,
           max(d) AS last_session,
           count() AS sessions
    FROM (
        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 window_start >= toDateTime(today() - 32)
          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
    )
    GROUP BY ticker
    HAVING sessions >= 15 AND adv_usd >= 1000000 AND last_close > 0
) AS l ON l.ticker = m.ticker
INNER JOIN (
    SELECT ticker
    FROM global_markets.stocks_ratios
    GROUP BY ticker
    HAVING argMax(market_cap, date) > 0
) AS f ON f.ticker = m.ticker
ORDER BY l.adv_usd DESC
LIMIT 60

WITH payers AS (
    SELECT ticker,
           count() AS payments_12m,
           argMax(frequency, ex_dividend_date) AS freq_flag
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date > today() - 365
      AND ex_dividend_date <= today()
      AND cash_amount > 0
      AND distribution_type = 'recurring'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
),
filers AS (
    SELECT ticker
    FROM global_markets.stocks_ratios
    GROUP BY ticker
    HAVING argMax(market_cap, date) > 0
)
SELECT multiIf(payments_12m = 1, 'One a year',
               payments_12m = 2, 'Two a year',
               payments_12m BETWEEN 3 AND 6, 'Three to six a year',
               payments_12m BETWEEN 7 AND 9, 'Seven to nine a year',
               payments_12m BETWEEN 10 AND 14, 'Ten to fourteen a year',
               payments_12m BETWEEN 15 AND 39, 'Fifteen to thirty-nine a year',
               'Forty or more a year') AS bucket,
       count() AS tickers,
       countIf(freq_flag = 12) AS labelled_monthly,
       countIf(ticker IN (SELECT ticker FROM filers)) AS company_filings
FROM payers
GROUP BY bucket
ORDER BY min(payments_12m)

WITH monthly AS (
    SELECT ticker, argMax(cash_amount, ex_dividend_date) AS latest_payment
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date > today() - 365
      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 count() BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
),
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
),
named AS (
    SELECT m.ticker AS ticker,
           100 * m.latest_payment * 12 / p.last_close AS yld,
           100 * (p.last_close - p.close_year_ago) / p.close_year_ago AS price_change,
           m.ticker IN (SELECT ticker FROM filers) AS is_filer
    FROM monthly m
    INNER JOIN px p ON p.ticker = m.ticker
)
SELECT multiIf(yld < 3, 'Under 3%',
               yld < 5, 'From 3% to 5%',
               yld < 8, 'From 5% to 8%',
               yld < 12, 'From 8% to 12%',
               'Above 12%') AS bucket,
       count() AS payers,
       round(quantileDeterministic(0.5)(yld, cityHash64(ticker)), 2) AS median_yield_pct,
       round(quantileDeterministic(0.5)(price_change, cityHash64(ticker)), 1) AS median_price_change_12m_pct,
       countIf(is_filer) AS company_filings
FROM named
GROUP BY bucket
ORDER BY median_yield_pct

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

WITH steps AS (
    SELECT ed, amt,
           lagInFrame(amt) OVER (ORDER BY ed ASC) AS prior_amt
    FROM (
        SELECT ex_dividend_date AS ed, max(cash_amount) AS amt
        FROM global_markets.stocks_dividends
        WHERE ticker = 'O'
          AND distribution_type = 'recurring'
          AND cash_amount > 0
          AND ex_dividend_date >= '2013-01-01'
          AND ex_dividend_date < toStartOfYear(today())
        GROUP BY ed
    )
),
yearly AS (
    SELECT toYear(ed) AS year,
           max(amt) AS monthly_rate,
           countIf(prior_amt > 0 AND amt > prior_amt) AS raise_count
    FROM steps
    GROUP BY year
),
seq AS (
    SELECT year, monthly_rate, raise_count,
           lagInFrame(monthly_rate) OVER (ORDER BY year ASC) AS prior_rate
    FROM yearly
),
panel AS (
    SELECT year, monthly_rate, raise_count,
           100 * (monthly_rate - prior_rate) / prior_rate AS raise_pct
    FROM seq
    WHERE prior_rate > 0 AND year >= 2014
)
SELECT year,
       round(monthly_rate, 4) AS monthly_payment_usd,
       round(raise_pct, 2) AS annual_raise_pct,
       raise_count,
       round(min(raise_pct) OVER (), 2) AS smallest_annual_raise_pct
FROM panel
ORDER BY year

WITH per_year AS (
    SELECT ticker,
           toYear(ex_dividend_date) AS year,
           count(DISTINCT ex_dividend_date) AS payments
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date >= '2005-01-01'
      AND ex_dividend_date < toStartOfYear(today())
    GROUP BY ticker, year
)
SELECT year,
       countIf(payments BETWEEN 10 AND 14) AS monthly_payers,
       round(100 * countIf(payments BETWEEN 10 AND 14) / count(), 2) AS monthly_share_pct
FROM per_year
GROUP BY year
ORDER BY year