AUM and net inflows by advisor

Source

Broker position, custody, inflows and commission

Rule

Deduplication, complete day, advisor in effect on the date

Outcome

AUC, net inflows and revenue by advisor

Activation

Dashboard, advisor's daily list and AI agent

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.

Business questions it answers

Ask your agent these questions once the case is live.

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

Where to activate

Office dashboard

AUC, net inflows and revenue by advisor and office, with the reference day and the source of each number in view.

Advisor's daily list

On WhatsApp or by email: securities maturing in 2 days, negative balances, idle balances, sign-ups from the last 7 days and clients without Open Finance.

AI agent via MCP

Answers about portfolio, inflows and revenue by advisor with the effective-date rule and the complete-day rule already applied.

Ready to build?

The connectors bring the data ready to use. The work is in deduplication, guarding against partial reports and the advisor history. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

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.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Joining the year's inflows with the current client base.

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.

Filtering the record to the current version and joining it with old positions.

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.

Summing the raw table, with versions and reprocessing runs.

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.

Using MAX(reference date) from the report.

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.

Treating every day without data as a day without trading.

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.

Reading the balance directly from the full-load table for a time series.

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.

Combining legacy and API without a cutoff date and counting the switchover month twice.

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.

Summing net revenue from one source with gross revenue from another.

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.

Summing the corrected spreadsheet with the original report for the same year.

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.

Grouping by the name as it came, with varying accents and case.

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.

Where each number comes from

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

Position by partner, by account and dayAssets under custody (AUC)totalammount

Custody by account, day and productPortfolio by product and asset classpositionValue

Net new money or inflow transactionsNet inflowscontributions minus redemptions

Commission by account, category and productRevenuevl_receita_liquida, vl_receita, vl_comissao

Monthly indicators by accountPositivadoraccount x month

Full load without a dateAccount balancesnapshot by processing date

Book history by accountAdvisor as of the datevalidity start and end

Latest report by accountOpen Finance consentunsigned when null

Definitions

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 that already happened

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.

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.

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.

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.

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.

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.

A single table partitioned by advisor, with the recipient coming from the base.

Acceptance test

The case is done when every item passes.

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.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

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.

Build the "AUM and net inflows by advisor" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/financeiros/patrimonio-captacao-assessor.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.

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.