> ## Documentation Index
> Fetch the complete documentation index at: https://octo-98895abd.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Investor flow

> Daily buy/sell/net flow by investor type, differenced from B3's month-to-date participation snapshots.

<Note>
  **Parameters, response columns and types are generated** from the SQL catalog and
  render under **Endpoints → Short interest & flows** in the sidebar. This page carries what a
  schema cannot: the conventions, the nulls that are deliberate, and the traps.
</Note>

### Overview

`investor_flow` answers one question: **what did each class of investor do on a given session?** Foreign investors, institutionals, individuals and the rest, bought and sold, in R\$ thousands.

| Endpoint | Grain | What it holds |
| - | - | - |
| `GET /investor_flow` | `(investor_type, reference_date)` | Daily buy / sell / net flow, plus the month-to-date figures it was derived from |

A **view**: `GET`, PostgREST filters, `limit`/`offset` paging. The securities-lending views are on [Securities lending](/api-docs/lending).

<Warning>
  **These numbers are a first difference that SILO computes. B3 does not publish a daily flow series.**

  What B3 publishes is a **month-to-date cumulative snapshot** — "so far this month, investor type X has bought R\$ N" — released with a **T+2 lag**. A day's flow is the difference between two consecutive snapshots, and that arithmetic has two ways of going badly wrong. Both are handled here rather than left to you:

  * **The difference never reaches across a month boundary.** Subtracting January's last snapshot from February's first would report a whole month of buying as one day's flow. The window is partitioned by month, so it cannot.
  * **The first snapshot held for a month is not always that month's first session.** Treating that month-to-date total as one day's flow would invent a spike on an arbitrary date, so it counts as a daily flow **only** when it really is the month's first session; otherwise the flow is `null` and `flow_basis` says why.

  Combined with the ratchet below, the series has genuine holes near the start of any month SILO began capturing mid-way through. They are marked, not filled.
</Warning>

<Warning>
  **History starts at first capture and cannot be extended backwards.** `b3_investor_participation` is part of B3's BDI group, retained for roughly **21 business days** with no archive. A missed session is lost at any price. Do not read the first date as a market event, and do not compute a long-run average or a year-over-year comparison until the warehouse has held that much.

  The T+2 publication lag also means `as_of` trails the calendar by two sessions **even when ingest is perfectly healthy**. That is the source's cadence, not staleness on our side — `landed_at` is the column that answers "did the pipeline run?".
</Warning>

### Read `flow_basis` before anything else

<Warning>
  **Rows the view cannot interpret carry `null` flows — never zeros — and summing them as zeros silently understates the month.**

  | `flow_basis` | Means | `*_value_thousands` |
  | - | - | - |
  | `delta` | A real one-session difference between two consecutive snapshots | A flow |
  | `month_open` | The month's first session, where the month-to-date total *is* the day | A flow |
  | `unknown_opening_snapshot` | No earlier snapshot is held for that month, so the day's flow is unknowable | **`null`, by construction** |

  `null` here is a statement: *we do not know what happened on this day*. It is not zero flow, not a quiet session, and not missing data to be filled. A `sum()` in SQL or pandas skips nulls silently and hands you a month total missing an unknown number of days — which looks exactly like a real, smaller number.

  **Filter explicitly.** Either restrict to `flow_basis=in.(delta,month_open)` and know your window is partial, or keep every row and treat the nulls as the gaps they are. What you must not do is aggregate across the three bases without looking.
</Warning>

### Field notes

| Field | What to know |
| - | - |
| `reference_date` | The session the flow is **for**, not the date B3 published it — the T+2 lag is already accounted for. |
| `investor_type` | The investor class exactly as B3 publishes it. The distinct values are few and come straight from the source; read them off the data rather than hard-coding a translation. |
| `buy_` / `sell_` / `net_value_thousands` | The session's purchases, sales and `buy - sell`, in **R\$ thousands** (the source's own `brl_mil` — *mil* is Portuguese for thousand). Multiply by 1,000 for reais; do not assume millions. Null on `unknown_opening_snapshot` rows. |
| `mtd_buy_` / `mtd_sell_value_thousands` | B3's **cumulative** month-to-date figures as published — what the difference was taken from. Never null. |

The `mtd_*` columns are the check on everything above them: within one month, `mtd_buy_value_thousands` on consecutive sessions should differ by exactly `buy_value_thousands` on the later one. That subtraction is the audit, and it is why the cumulative columns are published beside the differenced ones instead of being discarded.

### Example

One investor type across a month, with the uninterpretable rows kept visible:

<CodeGroup>
  ```bash curl theme={null}
  curl -s -G "$SILO_URL/investor_flow" \
    -H "apikey: $SILO_ANON_KEY" \
    --data-urlencode "select=reference_date,investor_type,flow_basis,net_value_thousands,mtd_buy_value_thousands" \
    --data-urlencode "investor_type=eq.Investidor Estrangeiro" \
    --data-urlencode "reference_date=gte.2026-09-01" \
    --data-urlencode "order=reference_date.asc"
  ```

  ```python Python theme={null}
  import pandas as pd

  params = {
      "investor_type": "eq.Investidor Estrangeiro",
      "reference_date": "gte.2026-09-01",
      "order": "reference_date.asc",
  }
  df = pd.DataFrame(requests.get(f"{URL}/investor_flow", headers=H, params=params).json())

  # Look at the bases BEFORE aggregating. A silent sum over all three would drop
  # the unknown days and report a smaller month as if it were complete.
  print(df["flow_basis"].value_counts())

  usable = df[df["flow_basis"].isin(["delta", "month_open"])]
  print(usable["net_value_thousands"].sum(),
        "R$ thousands over", len(usable), "of", len(df), "sessions")
  ```

  ```javascript JavaScript theme={null}
  const { data } = await supabase
    .from('investor_flow')
    .select('reference_date,investor_type,flow_basis,net_value_thousands')
    .eq('investor_type', 'Investidor Estrangeiro')
    .gte('reference_date', '2026-09-01')
    .order('reference_date', { ascending: true })
  ```
</CodeGroup>

```json theme={null}
[
  { "reference_date": "2026-09-01", "investor_type": "Investidor Estrangeiro",
    "flow_basis": "month_open", "net_value_thousands": -412300.0,
    "mtd_buy_value_thousands": 8241000.0 },
  { "reference_date": "2026-09-02", "investor_type": "Investidor Estrangeiro",
    "flow_basis": "delta", "net_value_thousands": 138900.0,
    "mtd_buy_value_thousands": 12903000.0 },
  { "reference_date": "2026-09-03", "investor_type": "Investidor Estrangeiro",
    "flow_basis": "unknown_opening_snapshot", "net_value_thousands": null,
    "mtd_buy_value_thousands": 15110000.0 }
]
```

Illustrative values. Check `coverage` for the newest `reference_date` before pinning a window.

### What this is not

* **Not a flow by security.** The grain is investor *type*, market-wide. There is no per-ticker breakdown in the source, so no join to `quotes` or [`short_interest`](/api-docs/lending) exists at this grain. Aligning a market-wide flow series against one ticker's return is your inference to make and own, not an edge this warehouse publishes.
* **Not a position.** These are gross purchases and sales over a session. A net figure is the session's direction, not anyone's holdings.
* **Not a panel metric.** These rows carry no id in the panel's sense, so there is no [`panel`](/api-docs/panel) arm — the same reason [`anbima_classes`](/api-docs/anbima) has none.

### Paging and empty results

The 1,000-row page is PostgREST's `db-max-rows`, and on a view it **cuts silently with a `200`**, keeping the oldest rows. Send `Prefer: count=exact` and read `Content-Range` (`0-999/*` means cut), or page with `limit`/`offset` and an explicit `order`. The [SDK](/api-docs/sdk) does both: `silo.investor_flow(...)` for one page, `silo.view_all("investor_flow", order="reference_date.asc")` to walk them all.

Given the retention window and a handful of investor types, a whole capture window fits inside one page today — but do not build on that; it is a property of the ratchet, not a guarantee.

* **Unknown investor type → `200 []`.** Spelling must match B3's.
* **Empty window → `200 []`.** Common, given \~21 business days of retention and the T+2 lag. Check [`coverage`](/api-docs/coverage)'s `investor_flow` row first.
* The landing tables are **not** readable by client roles; this view is owner-privileged and the columns it selects are the whole boundary.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.