Companies expected to raise their dividend this month, declared names first

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 changes AS
(
    SELECT
        ticker,
        ex_dividend_date,
        declaration_date,
        amount,
        prev_amount,
        (prev_amount > 0 AND amount > prev_amount
             AND declaration_date > toDate('2000-01-01'))            AS is_raise,
        (prev_amount > 0 AND amount < prev_amount)                   AS is_cut,
        round((amount / prev_amount - 1) * 100, 1)                   AS raise_pct
    FROM
    (
        SELECT
            ticker,
            ex_dividend_date,
            declaration_date,
            amount,
            lagInFrame(amount, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date
                                        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_amount
        FROM
        (
            SELECT
                ticker,
                ex_dividend_date,
                ifNull(max(declaration_date), toDate('1970-01-01')) AS declaration_date,
                ifNull(max(toFloat64(cash_amount)), 0)              AS amount
            FROM global_markets.stocks_dividends
            WHERE toString(frequency) = '4'
              AND ex_dividend_date >= toDate('2020-01-01')
              AND ticker NOT IN ('SPCX')
            GROUP BY ticker, ex_dividend_date
        )
    )
)
SELECT
    ticker,
    if(declared_this_month > 0, 'declared', 'expected, not yet declared')  AS status,
    concat(multiIf(median_day <= 7,  'first week of ',
                   median_day <= 14, 'second week of ',
                   median_day <= 21, 'third week of ',
                                     'last week of '),
           monthName(today()))                                             AS usual_window,
    concat(formatDateTime(today(), '%b'), ' ', toString(earliest_day),
           ' to ', toString(latest_day))                                   AS past_declaration_days,
    concat(toString(years_in_month), ' of 5')                              AS years_declared_in_month,
    last_raise_pct,
    concat(formatDateTime(last_declared, '%b'), ' ',
           toString(toDayOfMonth(last_declared)), ', ',
           toString(toYear(last_declared)))                                AS last_raise_declared
FROM
(
    SELECT
        ticker,
        uniqExactIf(toYear(declaration_date),
                    is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1))
                                                                                AS years_with_raise,
        uniqExactIf(toYear(declaration_date),
                    is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1)
                             AND toMonth(declaration_date) = toMonth(today()))  AS years_in_month,
        countIf(is_cut AND ex_dividend_date >= addYears(toStartOfYear(today()), -5))
                                                                                AS cuts,
        countIf(is_raise AND toStartOfMonth(declaration_date) = toStartOfMonth(today()))
                                                                                AS declared_this_month,
        quantileExactIf(0.5)(toDayOfMonth(declaration_date),
                    is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1)
                             AND toMonth(declaration_date) = toMonth(today()))  AS median_day,
        minIf(toDayOfMonth(declaration_date),
                    is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1)
                             AND toMonth(declaration_date) = toMonth(today()))  AS earliest_day,
        maxIf(toDayOfMonth(declaration_date),
                    is_raise AND (toYear(declaration_date) BETWEEN toYear(today()) - 5 AND toYear(today()) - 1)
                             AND toMonth(declaration_date) = toMonth(today()))  AS latest_day,
        argMaxIf(raise_pct, declaration_date, is_raise)                         AS last_raise_pct,
        maxIf(declaration_date, is_raise)                                       AS last_declared
    FROM changes
    GROUP BY ticker
    HAVING years_with_raise = 5
       AND years_in_month >= 4
       AND cuts = 0
)
ORDER BY status ASC, ticker ASC