Ex-Dividend Calendar from a Free SQL API

An ex-dividend calendar is a forward list of the dates on which each stock starts trading without its next declared dividend attached. You can build one with a single HTTP request: one curl call against a free SQL API returns every declared ex-date in the next two weeks, and one jq filter reshapes the JSON into four columns of ticker, ex-date, pay-date and cash amount. Everything below runs in a fresh Ubuntu container with nothing installed but those two tools.

What an ex-dividend date actually is

A board declares a dividend and names a record date. The exchange then sets the ex-dividend date, the first session on which the stock trades without that payment attached. Hold the share through the close before the ex-date and the dividend is yours. Buy on the ex-date itself and the seller keeps it, the mechanic covered in our guide to buying on the ex-dividend date. The pay date lands later, commonly two to five weeks after the ex-date, and it is carried in the same record.

The table you are about to build is a plain list of those records, filtered to a forward window and sorted by date.

Ex-dividend calendar: the next fourteen days

This panel is the finished product, computed at publish time from the same source your curl call will hit. It covers the actively traded US names going ex over the next two weeks, one row per company and ex-date.

WITH liquid AS
(
    SELECT ticker
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 45
    GROUP BY ticker
    HAVING avg(toFloat64(close) * volume) > 1e8
)
SELECT
    ticker,
    formatDateTime(ex_dividend_date, '%b %e')  AS goes_ex,
    formatDateTime(any(pay_date), '%b %e')     AS payable,
    max(cash_amount)                           AS cash_amount
FROM global_markets.stocks_dividends
WHERE ex_dividend_date >= today()
  AND ex_dividend_date <  today() + 14
  AND currency = 'USD'
  AND ticker IN (SELECT ticker FROM liquid)
  AND ticker NOT IN ('SPCX')
GROUP BY ticker, ex_dividend_date
ORDER BY ex_dividend_date, cash_amount DESC, ticker
LIMIT 15

The window holds 15 rows. The nearest one belongs to GGLL, which goes ex on Sep 22 at $0.6053 a share and pays on Sep 29. Two filters do the heavy lifting: a dollar-volume floor over the trailing 45 sessions keeps the list to names a reader recognizes, and the GROUP BY collapses any duplicate records for the same company and date.

The one curl call

Start from a bare container and install the two tools.

apt-get update && apt-get install -y curl jq

Next, store the endpoint and your key. Both are printed on the free SQL API page, along with the exact header the endpoint expects and the field name it reads the query from. Paste them in once and every command below works unchanged.

SQL_URL='<endpoint from the free SQL API page>'
SQL_KEY='<your free key>'

Now the query. It is deliberately simpler than the panel above, since a first call should be easy to read and easy to edit.

SQL="SELECT
    ticker,
    toString(ex_dividend_date) AS ex_date,
    toString(pay_date)         AS pay_date,
    cash_amount
FROM global_markets.stocks_dividends
WHERE ex_dividend_date >= today()
  AND ex_dividend_date <  today() + 14
  AND currency = 'USD'
ORDER BY ex_dividend_date, cash_amount DESC
LIMIT 200"

The request body is JSON, so the SQL has to be quoted as a JSON string. Hand that job to jq -n --arg, which escapes the newlines and quotes correctly every time, then pipe the result straight into curl.

jq -n --arg sql "$SQL" '{sql: $sql}' \
  | curl -sS "$SQL_URL" \
      -H "Authorization: Bearer $SQL_KEY" \
      -H 'Content-Type: application/json' \
      --data-binary @-

That is the whole integration. No SDK, no client library, no build step. If you would rather drive it from a script, the same request in twelve lines of standard library code is in the Python walkthrough.

Shaping the result with jq

The response is JSON. To get a table, add a second jq on the end of the pipe. This filter walks the whole document with .., keeps any object carrying an ex_date key, and prints tab-separated columns, so it keeps working whatever wrapper the payload arrives in.

jq -n --arg sql "$SQL" '{sql: $sql}' \
  | curl -sS "$SQL_URL" \
      -H "Authorization: Bearer $SQL_KEY" \
      -H 'Content-Type: application/json' \
      --data-binary @- \
  | jq -r '.. | objects | select(has("ex_date"))
           | [.ticker, .ex_date, .pay_date, (.cash_amount | tostring)]
           | @tsv'

Each line is one upcoming dividend: ticker, ex-date, pay-date, cash amount. Pipe it to sort -k2 to group by date, or redirect it to a .tsv file and open it in a spreadsheet. To narrow the calendar to names you follow, add a WHERE ticker IN ('KO', 'MSFT', 'CVX') clause to the SQL rather than filtering in jq: the work happens server side and the payload shrinks.

What an empty week looks like

A forward window can come back empty, and a script that assumes rows will mislead you. jq has a clean signal for this: jq -e exits with status 4 when the filter produced no output at all.

curl -sS ... | jq -er '.. | objects | select(has("ex_date")) | .ticker'
echo "exit $?"

An exit of 4 with no printed lines is an empty week. It does not mean nothing pays that week; it means nothing with an ex-date in that window has been declared yet. The next panel makes that distinction visible by counting how many distinct companies carry a declared ex-date in each of the next twelve weeks.

SELECT
    toString(cal.week)                AS week,
    formatDateTime(cal.week, '%b %e') AS week_label,
    toUInt32(ifNull(d.payers, 0))     AS payers
FROM
(
    SELECT toMonday(today()) + 7 * arrayJoin(range(12)) AS week
) AS cal
LEFT JOIN
(
    SELECT
        toMonday(ex_dividend_date) AS w,
        countDistinct(ticker)      AS payers
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= toMonday(today())
      AND ex_dividend_date <  toMonday(today()) + 84
      AND currency = 'USD'
      AND ticker NOT IN ('SPCX')
    GROUP BY w
) AS d ON d.w = cal.week
ORDER BY cal.week

The week of Sep 21 carries 805 names with a declared ex-date. The week of Dec 7, twelve weeks out, carries 59. The shape of that line is the single most useful thing to understand about any dividend calendar, whoever serves it.

A forward calendar is always part estimate

An ex-date exists in data only once a board has declared the dividend. Before that, a payment most investors consider routine is simply absent from the table. Sites that show a date anyway are projecting it from last year's schedule, and that projection moves whenever a board meets a week late or changes the amount.

The practical question is how much warning you actually get. This panel takes every US dividend whose ex-date fell in the trailing year and buckets it by the gap between the declaration date and the ex-date.

WITH declared AS
(
    SELECT
        ticker,
        ex_dividend_date,
        max(declaration_date) AS declared_on
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= today() - 365
      AND ex_dividend_date <  today()
      AND currency = 'USD'
      AND declaration_date > toDate('2000-01-01')
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, ex_dividend_date
),
lead_times AS
(
    SELECT dateDiff('day', declared_on, ex_dividend_date) AS lead_days
    FROM declared
    WHERE declared_on <= ex_dividend_date
)
SELECT
    multiIf(
        lead_days <=  7, '7 days or less',
        lead_days <= 14, '8 to 14 days',
        lead_days <= 30, '15 to 30 days',
        lead_days <= 60, '31 to 60 days',
                         'more than 60 days') AS lead_time_bucket,
    count()                                   AS dividend_count
FROM lead_times
GROUP BY lead_time_bucket
ORDER BY min(lead_days)

The first bucket holds 10142 dividends declared 7 days or less before their ex-date. The last holds 18476, declared more than 60 days ahead. A calendar built on declared records is exact for the near weeks and thins out past them, which is the honest behaviour. Anyone running a timing strategy around these dates, such as the one described in our dividend capture breakdown, is working inside that declaration lag rather than around it.

If you want the same data without writing anything, the hosted upcoming ex-dividend dates page refreshes the identical forward list on a schedule, and the free market data API overview covers the rate limits that apply to the call above.

FAQ

How do I get upcoming ex-dividend dates from an API?

Query a dividends table for rows whose ex-date falls after today. One curl call carrying a SELECT against global_markets.stocks_dividends with WHERE ex_dividend_date >= today() returns ticker, ex-date, pay-date and cash amount for the forward window you ask for.

Why is a stock missing from my ex-dividend calendar?

The record appears once the board has declared the dividend. A company that pays every quarter but has not yet announced its next payment has no forward ex-date to return, so it is absent from the window rather than skipping the dividend.

Can an ex-dividend date change after it is published?

Yes. Until the declaration is filed, any forward date is a projection from the prior schedule. Even after declaration, amounts and dates are occasionally revised, which is why regenerating the calendar beats caching it.

Do I need a library to call a SQL market data API?

No. curl sends the request and jq builds the JSON body and formats the response, both available in any base Linux image. A language SDK adds convenience, not capability.

What is the difference between the ex-date, the record date and the pay date?

The ex-date is the first session the stock trades without the dividend attached. The record date is when the company reads its shareholder list. The pay date is when cash reaches accounts, usually a few weeks later.

Every panel on this page ships with the SQL that produced it, so you can copy any of them into your own curl call and change the window. To edit and run these queries interactively, ask the question in plain English on the Strasmore terminal.