ETF unit creation and redemption flows are a clean read on primary-market demand: when investors put money to work, authorised participants create new units (shares outstanding rises); when they pull money out, units are redeemed (shares outstanding falls). Multiplying the change in units by the price gives the dollar value created or redeemed that day. Run across 54 ETFs spanning US, international, sector, thematic and fixed-income exposures, this is a daily map of where money is rotating between markets — not a single aggregate.
The monitor appends one row per ETF each US trading day, computes a trailing 20-session dollar flow and its historical percentile per ETF, the 14-day RSI, the 1-day price change and AUM, then publishes a dark-theme report to the web plus a Discord summary.
| Market | ETFs |
|---|---|
| US (5) | SPY, QQQ, VTV, VUG, VB |
| International (16) | VGK, EWJ, EWA, EEM, MCHI, EWY, EWT, INDA, EWS, EWM, THD, EIDO, EPHE, ILF, EWZ, EWW |
| US & Global Sectors (17) | SMH, XLK, XLC, XLF, IXG, XLI, EXI, XLV, IXJ, IBB, XLY, XLP, XLE, IXC, XLB, XLU, XLRE |
| Thematics (12) | DRAM, SHLD, KWEB, BOTZ, ROBO, NASA, LIT, ICLN, GLD, GDX, COPX, URA |
| Fixed Income (4) | SGOV, TLH, BND, BNDX |
Every line is the US-listed ticker, priced and reported in US$, so flows are directly comparable across markets. There is no aggregate / TOTAL row: summing flows across unrelated markets is not meaningful, so each market is shown and ranked on its own. Creation/redemption works identically for bond funds, so the Fixed Income block reads the same way — though bond-fund flows often move opposite to equity risk appetite.
acc n/21 instead of a flow.Units are in millions and price is in US$, so the flow lands in US$ millions. Using the two-day average price avoids attributing a price move to a flow. On holiday carry-forward sessions, units are unchanged so the daily flow is zero by construction.
This mirrors the Korea ETF monitor's cumulative-flow approach, set to a 20-session window to match the Bloomberg Flows (US$m) 20-day field.
Each ETF's current 20-day flow is ranked against its own history using the Excel PERCENTRANK.INC convention (0–100). The history window runs from 31 Dec 2020 (the seed start) — or the fund's inception if it listed later (e.g. SHLD Sep-2023, NASA Mar-2026) — and grows by one observation each trading day. A reading near 100 means the current 20-day creation is near the most extreme inflow in the ETF's sample; near 0 means the most extreme outflow. The percentile is always per-ETF — never pooled across the universe.
RSI is computed with Wilder's smoothing (the original RSI definition: a simple average seeds the first value, then each subsequent average gain/loss is (prev×13 + today)/14). Validated against the Bloomberg RSI_14D field on the seed history, this reproduces Bloomberg to within ~0.3 RSI points mean error on converged data (the only larger gaps are in the first few weeks of any series, before Wilder smoothing settles — irrelevant once 5+ years of history is in place).
Prices come from yfinance daily closes (Stooq .us as fallback). Shares outstanding — the input that drives flows — is drawn from a validated multi-source chain rather than a single feed, because no one free source is accurate for every fund. yfinance's sharesOutstanding in particular reports whole-fund figures for multi-share-class funds (e.g. Vanguard ETFs, which are one share class of a larger fund) and stale values elsewhere. Each source below is checked against the Bloomberg seed and matches to ~1%:
| Source | Used for | Why |
|---|---|---|
Polygon share_class_shares_outstanding | VTV, VUG, VB, VGK, XLI, IXC | Share-class-aware — the only source that isolates the ETF class for multi-class Vanguard funds |
| stockanalysis.com API | the other 48 ETFs | Reports true shares outstanding for standalone ETFs |
| SSGA fund page (AUM ÷ NAV) | the 11 SPDR sectors (backup) | Issuer-direct safety net beneath the primaries |
yfinance totalAssets ÷ price | last resort | Approximate; guarded by the flow clamp below |
On any source outage a fund carries its last shares count forward (zero flow that day) rather than recording a phantom. As a final guard, any single-day change in shares greater than 25% is treated as a data artefact — the new level is kept but that day's flow is set to zero. No Bloomberg feed is needed for routine daily runs.
| Field | Source |
|---|---|
| Seed history | Bloomberg export (EQY_SH_OUT, PX_LAST) → bootstrap_history.py → etf_history.csv |
History is seeded once from a Bloomberg export (the ground-truth basis), then the script appends a row per ticker per US trading day from the live sources above and recomputes everything from the stored series — so RSI and flow windows always have full lookback. Re-running the seed from a fresh Bloomberg export is the manual reset if a number ever looks off, but is not part of the routine.
Because a stale feed shows up as an unchanged value repeating across sessions, the report flags it: a † next to a price or shares figure means that value has been unchanged for more than three consecutive sessions — either a stale feed or a genuinely low-turnover fund. Treat a flagged name's daily flow with care.
| Column | Meaning |
|---|---|
| ETF | Short name and ticker |
| AUM ($bn) | Shares outstanding × price, US$ billions |
| Price ($) | Latest US$ close |
| 1d Chg | Price change vs prior session |
| % 52w Hi | Price vs its highest close in the trailing 52 weeks (≤ 0; 0 = at a new high) |
| RSI 14d | Wilder RSI; ≥70 overbought, ≤30 oversold |
| Flow 20d ($m) | Trailing 20-session creation (＋) / redemption (−) |
| Percentile | Where the 20d flow sits vs the ETF's own history |
| Sh out (m) | Units outstanding, millions |
Every column is click-sortable; each market table defaults to AUM descending. Green is net creation / price up / oversold RSI; red is net redemption / price down / overbought RSI. There is no aggregate row by design.
python3 bootstrap_history.py ETF_Discord.csv → builds etf_history.csv.python3 etf_monitor.py — fetches, appends, computes, renders, posts./etf — runs the full pipeline.chris-etf-flow.pages.dev.