---
id: financeiros/patrimonio-captacao-assessor
industry: Financial services
usage: common
effort: medium
grain: Account x day x product (custody); transaction (inflows); account x month (positivador); revenue entry x accrual month
---

# AUM and net inflows by advisor

Assets under custody, net inflows and revenue by account, advisor and office, built from the broker's reports and attributed to the right advisor on each date.

## Executive summary

**For:** Investment advisory offices (BTG, XP or multi-custodian) and financial planners who track portfolio, inflows and revenue by advisor.

**What it solves**

- Each advisor's assets and inflows match the broker's report, with no partial days or duplicate rows.
- Inflows and revenue stay with the advisor who served the account on that date, even after a book transfer.
- Advisors get a daily to-do list: maturities, negative balances, new sign-ups.

**Why it matters:** The broker delivers reports by file or API, with days that arrive half complete, versioned records and balances that get overwritten at every load. Summed carelessly, they produce assets that change advisor when a book changes hands and a commission nobody can check. With a clean base, the advisor targets, the revenue check and the sales routine all use the same number.

**Business questions it answers**

- How much does each advisor have under custody today?
- What are the month's net inflows by advisor and by office?
- How much revenue did each advisor generate this month, by product?
- Which clients have securities maturing in the next few days?
- Which accounts have no assigned advisor?
- What is the clients' real estate fund portfolio as of the last day?

**What you need**

- The broker connector (BTG Parceiros or XP) or the report files connected to Nekt
- The client base with advisor, origin and the history of book transfers
- The commission spreadsheets for sources that do not come through the API

**What you get**

- Custody, inflow and revenue tables by account and day, with the advisor as of that date
- A dashboard by advisor and office with AUC, net inflows and revenue
- Daily lists per advisor on WhatsApp or by email
- An AI agent that answers questions about portfolio and inflows with the right rules

## Detailed

### The problem

Each broker delivers the portfolio in pieces: position by market, custody by product, inflow transactions, commission by category, a monthly positivador (account activity report). Account and advisor records are versioned and the keys change with every version, the daily report sometimes arrives with only a few rows and the account balance is overwritten at every load. Summing directly attributes old inflows to today's advisor, shows assets collapsing on a partial day and loses the balance history.

### Business rules

1. **Advisor in effect on the date of the event.** Inflows, custody and revenue stay with the advisor who served the account on the transaction or position date. Join against the client base history where effective start <= event date and (effective end is null or > event date). Today's advisor only applies to today's snapshot. (Avoid: Joining the year's inflows with the current client base.)
2. **Stable business key, not the surrogate key.** In XP's versioned records (account, advisor, office, product), dimAccountCode, dimAdvisorCode and dimOfficeChannelCode change with every version. The custody fact already points to the version in effect on the position date: join by that key and never filter by 'current today'. For identity over time, use accountCode, advisorCode and officeCode. (Avoid: Filtering the record to the current version and joining it with old positions.)
3. **Deduplicate each fact by its key before summing.** Custody: one row per account, day and product, the one with the latest lastupdate. Inflows: one per account, day, transaction code, amount and quantity. Position by partner: one per account and day. Positivador: first sum the complementary rows that share the same lastupdate (flow metrics), then deduplicate reprocessing runs and remove weekends. (Avoid: Summing the raw table, with versions and reprocessing runs.)
4. **Only complete days go into the snapshot.** The latest report may bring the current day half complete. Within the latest report, choose the most recent reference date whose row volume is at least 50% of the largest volume in that report. The partial day is discarded and the last good day stays. (Avoid: Using MAX(reference date) from the report.)
5. **A business day with no load is a failure, not a holiday.** Weekends and holidays without positions are expected. A trading day with no custody rows for any account is a source failure: flag it, do not fill it with zero and do not let the dashboard show zero assets. (Avoid: Treating every day without data as a day without trading.)
6. **A balance that gets overwritten needs history.** Investment account and digital account balances come as full loads with no date partition: each load erases the previous one. On every run, append the snapshot to a history table stamped with the processing date. The reference date is the run date in America/Sao_Paulo minus one day when the extraction runs right after midnight. (Avoid: Reading the balance directly from the full-load table for a time series.)
7. **Legacy and API combined without overlap.** When the source changes (old base up to a date, API from then on), combine the two with a source column and a cutoff date that does not overlap. Convert legacy dates and numbers ('dd/mm/yyyy', '1.234,56') before the union. Fields that exist in only one source stay null in the other. (Avoid: Combining legacy and API without a cutoff date and counting the switchover month twice.)
8. **Revenue from several sources in a single schema.** Daily revenue per account = first filled value among net revenue, revenue and commission, or zero. Commissions that arrive in separate files (by product, FX, credit, international markets) are projected into a common schema: client, advisor, gross revenue, net revenue, commission, product and source currency. Dollar revenue is converted to reais at the closing PTAX (official BRL/USD rate). The accrual month comes from the file name (YYYYMM_source) when there is no column. (Avoid: Summing net revenue from one source with gross revenue from another.)
9. **Corrected history replaces the report.** When advisors check and correct commissions for closed years, the corrected spreadsheet replaces the original report for that period. The broker's report applies from the cutoff onward. Write with a hash key so reprocessing does not duplicate. (Avoid: Summing the corrected spreadsheet with the original report for the same year.)
10. **Normalized advisor name.** Compare and group advisors in uppercase, without accents and with no leading or trailing spaces. Null, empty and '-' values are accounts without an advisor and appear as such in the dashboard, never disappearing. (Avoid: Grouping by the name as it came, with varying accents and case.)

### Where each number comes from

Typical BTG and XP sources. Each one deduplicated by its own key.

- **Position by partner, by account and day** → Assets under custody (AUC) (`totalammount`)
- **Custody by account, day and product** → Portfolio by product and asset class (`positionValue`)
- **Net new money or inflow transactions** → Net inflows (`contributions minus redemptions`)
- **Commission by account, category and product** → Revenue (`vl_receita_liquida, vl_receita, vl_comissao`)
- **Monthly indicators by account** → Positivador (`account x month`)
- **Full load without a date** → Account balance (`snapshot by processing date`)
- **Book history by account** → Advisor as of the date (`validity start and end`)
- **Latest report by account** → Open Finance consent (`unsigned when null`)

### Definitions

| Metric | Definition |
|---|---|
| Account AUC | total position value of the account on the last complete day, one row per account and day |
| Advisor AUC | sum of the AUC of the accounts assigned to the advisor in effect on the position date |
| Net inflows | deposits and incoming transfers minus redemptions and outgoing transfers, by account and transaction date, attributed to the advisor as of that date |
| Daily net revenue | first filled value among net revenue, revenue and commission, summed by account and day; dollar amounts converted at the closing PTAX |
| Account without an advisor | an account with no advisor version in effect on the date, or with a null, empty or '-' advisor |
| Upcoming maturity | fixed income security maturing in the next N calendar days (for example, 2 for the daily list and 90 for planning), in the latest position |
| Idle balance | checking account balance above a stated threshold (for example, R$ 5 thousand) by client and institution, in the latest snapshot |
| Negative balance | an account with a balance below zero in the latest snapshot, with the consecutive days in that state |

### Pitfalls

- **Half-complete daily report.** The broker sent the current day with only a few rows, against thousands on a normal day. The MAX of the date picked that day and the dashboard showed the fixed income portfolio near zero. How to avoid: Use the most recent date with at least 50% of the largest volume.
- **Partial account-advisor relationship.** The table relating accounts to advisors covered only one office, and about a third of custody ended up without an advisor in the join. How to avoid: Take the advisor from the advisor record as of the position date and use the relationship only as a check.
- **Broken effective dates.** Some accounts and advisors had no version flagged as current in the broker's records, even with a validity date covering today. A 'current today' filter made those accounts disappear. How to avoid: Join by the dated key and, with no current version, use the one with the latest validity start, flagged.
- **Birthdays with day and month swapped.** From a certain report date on, the date of birth started arriving with day and month swapped whenever the day was 12 or less, and the birthday list came out wrong. How to avoid: Correct by report date and validate against a known sample.
- **Negative positions discarded.** A small fraction of positions came negative and another part came at zero. Filtering all of them as errors removed short positions and legitimate history. How to avoid: Keep and flag them; exclude only with a written business rule.
- **A hand-written table per advisor.** The daily list was generated in one table per advisor, with the name hardcoded. A new advisor had no list until someone edited the notebook. How to avoid: A single table partitioned by advisor, with the recipient coming from the base.

### Acceptance test

- [ ] The cleaned custody table has the same number of distinct rows per account, day and product as the source: the joins neither lose nor duplicate rows.
- [ ] For a complete day, total AUC by account matches the broker's report (difference below 0.5%).
- [ ] Every inflow row has the advisor as of its date or appears as an account without an advisor; none uses the current advisor for a past date.
- [ ] Every business day without custody is flagged as a load failure, and no partial day becomes the latest snapshot.
- [ ] Monthly revenue by advisor equals the sum of the commission sources for that accrual month, already converted to reais.

### Context document

```text
AUM, net inflows and revenue by advisor
- Broker: <BTG/XP>. Custody in <table>, inflows in <table>, commission in <table>, positivador (monthly account activity report) in <table>.
- Deduplication: custody by account, day and product (latest lastupdate); inflows by account, day, transaction, amount and quantity; position by account and day.
- Daily snapshot: within the latest report, the most recent date with volume >= 50% of the largest volume. A business day without data is a load failure, never zero assets.
- Advisor: the one in effect on the event date, from the client history (start <= date and end null or > date). Never today's advisor for a past date.
- XP records are versioned: join by the dated surrogate key; identity by accountCode, advisorCode and officeCode.
- Daily revenue = net revenue, else revenue, else commission, else zero. Dollars converted at the closing PTAX (official BRL/USD rate).
- Up to <cutoff date>, commission comes from the spreadsheet corrected by the advisors; after that, from the report.
- Account balance comes from snapshots accumulated by processing date, in the America/Sao_Paulo time zone.
- Advisor in uppercase and without accents. Null, empty or '-' = no advisor.
- Never show a full CPF (individual tax ID) or contact details outside the advisor's own list.
Per-client exceptions:
- <client>: legacy data up to <date> in base <table>, API from <date>; the source column shows the origin.
- <client>: desk <desk> has a fixed list of accounts in <table>.
- <client>: date of birth with day and month swapped from <date>; correct it when the day is 12 or less.
```

### Variations

- **Commissions and rebates:** Insurance brokers: pass-through percentage by line of business on each policy's commission, by franchisee, matching production by CNPJ (company tax ID) and policy.
- **Planning with Open Finance:** Investments entered by hand and via Open Finance, deduplicated by external ids and checked against the institution's aggregate balance.
- **Portfolio by asset class:** A cut of the last day's custody by class (real estate funds, fixed income, equities, pension plans), with product, account and advisor.