Fund ETF daily — fund_etf_daily_YYYYMMDD.txt.gz
One file of the SQXray data dictionary. Field list (.csv)
Fund ETF daily — fund_etf_daily_YYYYMMDD.txt.gz
One row per exchange-traded share class per trading day, on ONE listing: the close, the shares outstanding in force that day, the AUM that follows, the creation/redemption flow, and the market-price total return. The population is the ETF and ETP classes that printed a close on the date; a class with no close that day has no row rather than one carried forward. Shares outstanding are a step function — the vendor writes an observation only when the count changes — so a row carries the newest observation dated on or before its pricing_date, and staleness_days says how old that is. A flow is an observed difference and never an assumed zero: where the count is carried forward, flow_local is blank and flow_status says stale_shares.
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
fund_isin | VARCHAR(12) | never blank (key) | The share class's ISIN, always an ISIN. The fund reference file, keyed on the same fund_isin, carries the class's identity; the fund analytics file carries its trailing windows. | IE000U6US1Q0 |
pricing_date | DATE | never blank (key) | The trading day the close belongs to, on the class's primary listing. | 2026-07-16 |
currency | CHAR(3) | never blank | The primary listing's quote currency, in its major unit: a GBX tape print is published as GBP at 1/100. close_price, prev_close, aum_local, flow_local and div_amount are all in it. | USD |
primary_mic | VARCHAR(12) | may be blank | The venue the close came from. It names the listing this row describes, for a class quoted in more than one place. | XAMS |
close_price | DECIMAL(18,6) | may be blank | The market close on the primary listing, in currency. | 3.977000 |
prev_close | DECIMAL(18,6) | may be blank | The close total_return_1d is measured against, in currency. Published so a consumer can re-derive the return. | 4.050000 |
prev_close_date | DATE | may be blank | The trading day prev_close belongs to. Never assumed to be the previous calendar day. | 2026-07-15 |
shares_outstanding | BIGINT | may be blank | Shares outstanding in force on pricing_date, carried forward from the observation dated shares_asof. The vendor writes an observation only when the count changes. | 10264152 |
shares_asof | DATE | may be blank | The effective date of the shares observation this row rests on: the disclosure date behind shares_outstanding. | 2026-07-16 |
staleness_days | INT | may be blank | pricing_date minus shares_asof: the age of the shares observation, in calendar days. Read it before trusting an AUM. | 0 |
shares_delta | BIGINT | may be blank | The signed change in shares since the previous observation, split-adjusted. Blank where no new observation landed; flow_status names which case the row is. | -30198 |
aum_local | DECIMAL(24,2) | may be blank | shares_outstanding times close_price, in currency. | 40820532.50 |
aum_usd | DECIMAL(24,2) | may be blank | aum_local converted at fx_rate. Blank where no rate inside the FX window reconciles the two. | 40820532.50 |
flow_local | DECIMAL(24,2) | may be blank | shares_delta times close_price: the creation/redemption estimate, in currency. Blank, never 0, on a day whose shares observation did not move. | -120097.45 |
flow_usd | DECIMAL(24,2) | may be blank | flow_local converted at fx_rate. | -120097.45 |
flow_window_days | INT | may be blank | The calendar days the flow spans: this row's shares_asof minus the previous observation's. A flow is a one-day figure only where this is 1. | 8 |
flow_status | VARCHAR(24) | never blank | Why flow_local is what it is: observed (a new shares observation moved the count), stale_shares (the count is forward-filled, so creation is unknown and the flow is blank, not zero), no_prior_observation, no_shares. | observed |
total_return_1d | DECIMAL(18,10) | may be blank | The market-price total return over the previous close, as a decimal fraction (0.0123 = +1.23%), split- and dividend-adjusted: (close_price * split_factor + div_amount) / prev_close - 1. | -0.0013333333 |
return_status | VARCHAR(24) | never blank | Whether the return could be measured: ok, no_prior_close (no earlier close on this listing in the lookback), stale_prior_close (that close is too old to difference against), div_fx_unavailable. | ok |
div_amount | DECIMAL(20,10) | may be blank | The per-share gross dividend going ex on pricing_date, converted into currency. Blank on a day with no distribution. | 0.0676000000 |
split_factor | DECIMAL(20,10) | may be blank | New shares per old share for a split going ex on pricing_date. Blank on a day with no split. A split adjusts both the return and the shares delta. | 25.0000000000 |
fx_rate | DECIMAL(18,9) | may be blank | Units of currency per 1 USD on fx_rate_asof. Blank on a USD row, and blank where the rate feed carried none inside the window. | 1.450821000 |
fx_rate_asof | DATE | may be blank | The date the rate was struck: as-of or before pricing_date, inside the FX window. | 2026-07-01 |
data_source | VARCHAR(32) | never blank | The vendor behind the row's inputs — the close, the share count and the corporate actions that adjust them. | EDI |
etf_daily_methodology_version | VARCHAR(16) | never blank | The definitions these numbers were computed under: the flow rule and the return convention. | etf_daily-1.0 |
etf_daily_engine_version | VARCHAR(16) | never blank | The producer build that wrote the row. | etf_daily-1.0 |
Appendix: canonical values
| Field | Values |
|---|---|
flow_status | observed, stale_shares, no_prior_observation, no_shares |
return_status | ok, no_prior_close, stale_prior_close, div_fx_unavailable |