--- id: financeiros/patrimonio-captacao-assessor industria: Serviços financeiros uso: comum esforco: médio grao: Conta x dia x produto (custódia); movimento (captação); conta x mês (positivador); lançamento de receita x competência --- # Patrimônio e captação por assessor Patrimônio sob custódia, captação líquida e receita por conta, assessor e escritório, montados a partir dos relatórios da corretora e atribuídos ao assessor certo em cada data. ## Resumo executivo **Para quem:** Escritórios de assessoria de investimentos (BTG, XP ou multi-custódia) e planejadores financeiros que acompanham carteira, captação e receita por assessor. **O que resolve** - O patrimônio e a captação de cada assessor batem com o relatório da corretora, sem dia parcial nem linha duplicada. - Captação e receita ficam com o assessor que atendia a conta na data, mesmo depois de troca de carteira. - O assessor recebe todo dia a lista do que fazer: vencimentos, saldo negativo, cadastros novos. **Por que importa:** A corretora entrega relatórios por arquivo ou API, com dias que chegam pela metade, cadastros versionados e saldos que se sobrescrevem a cada carga. Somados sem cuidado, viram um patrimônio que muda de assessor quando a carteira troca de mãos e uma comissão que ninguém consegue conferir. Com a base tratada, a meta por assessor, a conferência de receita e a rotina comercial usam o mesmo número. **Perguntas de negócio que responde** - Quanto cada assessor tem sob custódia hoje? - Qual a captação líquida do mês por assessor e por escritório? - Quanto de receita cada assessor gerou no mês, por produto? - Quais clientes têm títulos vencendo nos próximos dias? - Quais contas estão sem assessor responsável? - Qual a carteira de fundos imobiliários dos clientes no último dia? **O que você precisa** - O conector da corretora (BTG Parceiros ou XP) ou os arquivos de relatório conectados na Nekt - A base de clientes com assessor, origem e o histórico de trocas de carteira - As planilhas de comissão das fontes que não vêm pela API **O que você recebe** - Tabelas de custódia, captação e receita por conta e dia, com o assessor da data - Painel por assessor e escritório com AUC, captação líquida e receita - Listas diárias por assessor no WhatsApp ou no e-mail - Um agente de IA que responde sobre carteira e captação com as regras certas ## Detalhado ### O problema Cada corretora entrega a carteira em pedaços: posição por mercado, custódia por produto, movimentos de captação, comissão por categoria, positivador mensal. Os cadastros de conta e assessor são versionados e as chaves mudam a cada versão, o relatório do dia às vezes chega com poucas linhas e o saldo em conta é sobrescrito a cada carga. Quem soma direto atribui captação antiga ao assessor de hoje, mostra patrimônio despencando num dia parcial e perde o histórico do saldo. ### Regras de negócio 1. **Assessor vigente na data do fato.** Captação, custódia e receita ficam com o assessor que atendia a conta na data do movimento ou da posição. Cruze com o histórico da base de clientes onde início da vigência <= data do fato e (fim da vigência nulo ou > data do fato). O assessor de hoje só vale para a foto de hoje. (Evite: Juntar a captação do ano com a base de clientes atual.) 2. **Chave de negócio estável, não a chave substituta.** Nos cadastros versionados da XP (conta, assessor, escritório, produto), dimAccountCode, dimAdvisorCode e dimOfficeChannelCode mudam a cada versão. O fato de custódia já aponta para a versão vigente na data da posição: junte por essa chave e nunca filtre por 'vigente hoje'. Para identidade ao longo do tempo, use accountCode, advisorCode e officeCode. (Evite: Filtrar o cadastro pela versão atual e juntar com posições antigas.) 3. **Deduplique cada fato pela sua chave antes de somar.** Custódia: uma linha por conta, dia e produto, a de maior lastupdate. Captação: uma por conta, dia, código do movimento, valor e quantidade. Posição por parceiro: uma por conta e dia. Positivador: primeiro some as linhas complementares que dividem o mesmo lastupdate (métricas de fluxo), depois deduplique os reprocessamentos e tire fins de semana. (Evite: Somar a tabela bruta, com versões e reprocessamentos.) 4. **Só dia íntegro entra na foto.** O relatório mais recente pode trazer o dia corrente pela metade. Dentro do último relatório, escolha a data de referência mais recente cujo volume de linhas seja pelo menos 50% do maior volume daquele relatório. O dia parcial é descartado e o último dia bom se mantém. (Evite: Usar MAX(data de referência) do relatório.) 5. **Dia útil sem carga é falha, não feriado.** Fim de semana e feriado sem posição são esperados. Um dia de pregão sem nenhuma linha de custódia para nenhuma conta é falha da fonte: sinalize, não preencha com zero e não deixe o painel mostrar patrimônio zerado. (Evite: Tratar todo dia sem dado como dia sem mercado.) 6. **Saldo que se sobrescreve precisa de histórico.** Saldo em conta de investimento e em conta digital vem em carga completa sem partição de data: cada carga apaga a anterior. A cada execução, acrescente o snapshot numa tabela de histórico carimbado com a data de processamento. A data de referência é a data da execução em America/Sao_Paulo menos um dia quando a extração roda logo depois da meia-noite. (Evite: Ler o saldo direto da tabela de carga completa para série histórica.) 7. **Legado e API unidos sem sobreposição.** Quando a fonte muda (base antiga até uma data, API a partir dela), una as duas com uma coluna fonte e uma data de corte que não se sobrepõe. Converta data e número do legado ('dd/mm/aaaa', '1.234,56') antes da união. Campos que só existem numa fonte ficam nulos na outra. (Evite: Unir legado e API sem data de corte e contar o mês da virada duas vezes.) 8. **Receita de várias fontes num schema único.** Receita do dia por conta = primeiro valor preenchido entre receita líquida, receita e comissão, ou zero. Comissões que chegam em arquivos separados (por produto, câmbio, crédito, mercado internacional) são projetadas num schema comum: cliente, assessor, receita bruta, receita líquida, comissão, produto e moeda de origem. Receita em dólar vira real pela PTAX de fechamento. Competência vem do nome do arquivo (AAAAMM_fonte) quando não há coluna. (Evite: Somar receita líquida de uma fonte com receita bruta de outra.) 9. **Histórico corrigido substitui o relatório.** Quando os assessores conferem e corrigem a comissão de anos fechados, a planilha corrigida substitui o relatório original daquele período. O relatório da corretora vale a partir do corte. Grave com chave de hash para que reprocessar não duplique. (Evite: Somar a planilha corrigida com o relatório original do mesmo ano.) 10. **Nome do assessor normalizado.** Compare e agrupe o assessor em maiúsculas, sem acento e sem espaço nas pontas. Valores nulos, vazios e '-' são conta sem assessor e aparecem como tal no painel, nunca somem. (Evite: Agrupar pelo nome como veio, com acento e caixa variando.) ### De onde vem cada número Fontes típicas de BTG e XP. Cada uma deduplicada pela sua chave. - **Posição por parceiro, por conta e dia** → Patrimônio sob custódia (AUC) (`totalammount`) - **Custódia por conta, dia e produto** → Carteira por produto e classe (`positionValue`) - **Net new money ou movimentos de captação** → Captação líquida (`aportes menos resgates`) - **Comissão por conta, categoria e produto** → Receita (`vl_receita_liquida, vl_receita, vl_comissao`) - **Indicadores mensais por conta** → Positivador (`conta x mês`) - **Carga completa sem data** → Saldo em conta (`snapshot por data de processamento`) - **Histórico de carteira por conta** → Assessor da data (`vigência início e fim`) - **Último relatório por conta** → Consentimento de Open Finance (`sem assinatura quando nulo`) ### Definições | Métrica | Definição | |---|---| | AUC da conta | valor total da posição da conta no último dia íntegro, uma linha por conta e dia | | AUC do assessor | soma do AUC das contas atribuídas ao assessor vigente na data da posição | | Captação líquida | aportes e transferências de entrada menos resgates e transferências de saída, por conta e data do movimento, atribuída ao assessor da data | | Receita líquida do dia | primeiro valor preenchido entre receita líquida, receita e comissão, somado por conta e dia; em dólar convertido pela PTAX de fechamento | | Conta sem assessor | conta sem nenhuma versão de assessor vigente na data, ou com assessor nulo, vazio ou '-' | | Vencimento próximo | título de renda fixa com vencimento nos próximos N dias corridos (por exemplo, 2 para a lista diária e 90 para planejamento), na última posição | | Saldo parado | saldo em conta corrente acima de um limite declarado (por exemplo, R$ 5 mil) por cliente e instituição, na última foto | | Saldo negativo | conta com saldo abaixo de zero na última foto, com os dias seguidos nessa situação | ### Armadilhas - **Relatório do dia pela metade.** A corretora mandou o dia corrente com poucas linhas, contra milhares num dia normal. O MAX da data puxou esse dia e o painel mostrou a carteira de renda fixa quase zerada. Como evitar: Use a data mais recente com volume de pelo menos 50% do maior volume. - **Relação conta-assessor parcial.** A tabela de relação entre conta e assessor cobria só um escritório, e cerca de um terço da custódia ficou sem assessor no cruzamento. Como evitar: Leve o assessor pelo cadastro de assessor da data da posição e use a relação só como conferência. - **Vigência quebrada.** Algumas contas e assessores não tinham nenhuma versão marcada como vigente no cadastro da corretora, mesmo com data de validade cobrindo hoje. Um filtro de vigente hoje fez essas contas sumirem. Como evitar: Junte pela chave da data e, sem versão vigente, use a de maior início de validade com flag. - **Aniversário com dia e mês trocados.** A partir de uma certa data de relatório, a data de nascimento passou a vir com dia e mês invertidos quando o dia era até 12, e a lista de aniversariantes saiu errada. Como evitar: Corrija pela data do relatório e valide com uma amostra conhecida. - **Posição negativa descartada.** Uma fração pequena das posições vinha negativa e outra parte zerada. Filtrar tudo como erro tirou posições a descoberto e histórico legítimo. Como evitar: Mantenha e marque; só exclua com regra de negócio escrita. - **Uma tabela por assessor escrita à mão.** A lista diária era gerada numa tabela por assessor, com o nome fixo no código. Assessor novo ficava sem lista até alguém editar o notebook. Como evitar: Uma tabela única particionada por assessor, com o destinatário vindo da base. ### Teste de aceitação - [ ] A custódia tratada tem o mesmo número de linhas distintas por conta, dia e produto que a fonte: os joins não perdem nem duplicam linhas. - [ ] Para um dia íntegro, o AUC total por conta bate com o relatório da corretora (diferença abaixo de 0,5%). - [ ] Toda linha de captação tem o assessor da data ou aparece como conta sem assessor; nenhuma usa o assessor atual para data passada. - [ ] Todo dia útil sem custódia aparece sinalizado como falha de carga, e nenhum dia parcial vira a foto mais recente. - [ ] A receita do mês por assessor é igual à soma das fontes de comissão daquela competência, já convertidas para real. ### Context document ```text Patrimônio, captação e receita por assessor - Corretora: . Custódia em , captação em , comissão em , positivador em . - Deduplicação: custódia por conta, dia e produto (maior lastupdate); captação por conta, dia, movimento, valor e quantidade; posição por conta e dia. - Foto do dia: dentro do último relatório, a data mais recente com volume >= 50% do maior volume. Dia útil sem dado é falha de carga, nunca patrimônio zero. - Assessor: o vigente na data do fato, pelo histórico de clientes (início <= data e fim nulo ou > data). Nunca o assessor de hoje para data passada. - Cadastros da XP são versionados: junte pela chave substituta da data; identidade por accountCode, advisorCode e officeCode. - Receita do dia = receita líquida, senão receita, senão comissão, senão zero. Dólar convertido pela PTAX de fechamento. - Até , a comissão vem da planilha corrigida pelos assessores; depois, do relatório. - Saldo em conta vem de snapshots acumulados por data de processamento, no fuso America/Sao_Paulo. - Assessor em maiúsculas e sem acento. Nulo, vazio ou '-' = sem assessor. - Nunca mostre CPF completo nem dados de contato fora da lista do próprio assessor. Exceções por cliente: - : legado até na base , API a partir de ; coluna fonte indica a origem. - : a mesa tem lista fixa de contas em . - : data de nascimento com dia e mês trocados a partir de ; corrija quando o dia for até 12. ``` ### Variações - **Comissionamento e rebate:** Corretoras de seguros: percentual de repasse por ramo sobre a comissão de cada apólice, por franqueado, cruzando a produção por CNPJ e apólice. - **Planejamento com Open Finance:** Investimentos cadastrados à mão e via Open Finance, deduplicados pelos ids externos e conferidos contra o saldo agregado da instituição. - **Carteira por classe de ativo:** Recorte da custódia do último dia por classe (fundos imobiliários, renda fixa, renda variável, previdência), com produto, conta e assessor.