Nesta aula vamos te mostrar a planilha para Controle de Ações e IR (Imposto de Renda) que vamos construir ao longo dessa série!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Na aula de hoje, será disponibilizada uma para vocês uma Planilha para Controle de Ações e IR no VBA Grátis!
Se você realiza investimentos na bolsa de valores, essa planilha de ações vai te ajudar muito a organizar seus investimentos em um só lugar.
Além de te mostrar de forma automática todos os seus investimentos e suas posições atuais, ela irá permitir fazer o cálculo de imposto de renda para essas ações também.
Isto é, incluindo as movimentações da forma correta, os cálculos de quanto você deve de imposto de renda serão feitos automaticamente por ela.
Essa planilha de controle de ações também funciona perfeitamente para outros ativos da bolsa de valores, como os ETFs e Fundos Imobiliários.
Ela tem seis abas: notas, movimentações, resumo, imposto de renda, imposto de renda (FIIs) e cotações.
NOTAS
Nessa aba você fará o cadastro de todas as movimentações de compra e venda de ações feitas nas suas corretoras.
Você deverá preencher as informações necessárias para que essa ação possa ser computada na planilha. Ou seja, existem diversas colunas nessa aba, tente preencher o máximo de informações.
Botão de calcular taxas
Informações a fornecer:
O botão vai calcular o valor das taxas proporcionalmente aos valores fornecidos de cada ação, por cada número de nota cadastrada.
OBS: Você vai preencher o seu número da nota na última coluna antes de calcular as taxas
OBS2: Imposto de Renda Retido na Fonte (I.R.R.F.): você só retém imposto se você vendeu algo. Numa movimentação de compra, não teremos esse imposto.
Botão de incluir notas
Esse botão será responsável por incluir as novas movimentações/transações de compra e venda de ações para as outras abas, cálculos espalhados pela planilha.
Assim que você clicar no botão, ele irá preencher as duas primeiras colunas com um “X” em incluída e um ID, de acordo com a data de realização daquela movimentação.
MOVIMENTAÇÕES
As compras e vendas cadastradas na primeira aba, ou seja, as informações das notas, com datas do pregão e da liquidação daquela transação, movimentação (compra ou venda), ID, ação, e os valores negativos ou positivos respeitando respectivamente compra e venda.
OBS: Nessa aba deverão/poderão ser registrados na tabela: valores de aluguéis de ações, aporte ou retirada de uma conta específica, proventos etc.
ABA RESUMO
Depois que tudo foi cadastrado corretamente nas abas de inclusão de notas e movimentações, na aba de resumo tem-se o resumo dos ativos/ações listadas nessas duas primeiras abas.
Nela, há uma tabela com ativo, quantidade, custo médio, valor investido, preço atual, valor atual, variação monetária e variação percentual.
Além dos valores quantitativos, temos dois gráficos muito legais: um que representará a variação monetária dos ativos de forma positiva (em azul, abaixo), e negativo (em laranja); e um gráfico em pizza, demonstrando a distribuição do seu portfólio de investimento percentual nas ações.
IMPOSTO DE RENDA
A aba de imposto de renda vai ficar preenchida automaticamente conforme o preenchimento das outras abas (as duas primeiras).
Basicamente ela é estruturada em meses, tem as alienações (vendas), lucro ação, lucro ETF, lucro isento, lucro total, prejuízo a compensar, base de cálculo, imposto devido, I.R.R.F no mês, imposto a pagar (total), data de pagamento e imposto pago.
IMPOSTO DE RENDA (FIIs)
Ela é estruturada como a aba de imposto de renda anterior, mas apenas para fundos de investimentos imobiliários. Contém os dados dos meses, lucro com fundos imobiliários, prejuízo a compensar, base de cálculo para o imposto, imposto devido, I.R.R.F. no mês, imposto a pagar, data de pagamento e imposto pago.
COTAÇÕES
A aba de cotações contém as cotações último dia (dia anterior ao atual) dos ativos que você investiu, discriminadamente, para auxiliar a fazer as contas nas outras abas.
Nesta aula vamos te mostrar os conceitos de investimentos e como efetuar o primeiro registro dentro da nossa planilha para termos esse controle!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Na aula de hoje vamos te mostrar como criar uma planilha para controle de ações e IR do zero!
O que o primeiro post da série vai te ensinar?
Decidimos iniciar a série falando sobre os conceitos que vamos utilizar ao longo da série para que todos consigam acompanhar os conteúdos.
Nem todo mundo sabe o que é ação ou ETF o que é, então vale esclarecer esses primeiros pontos iniciais para depois entrarmos no controle propriamente dito.
Vale ressaltar aqui que, como comentado no vídeo, o conteúdo não é produzido por especialistas em nenhuma das áreas que envolvam finanças, contabilidade ou da parte tributária.
Então, tudo que vamos abordar na série sobre investimentos é fruto de conhecimento adquirido ao longo do tempo, com estudos e experiências pessoais com alguns investimentos.
Renda fixa X Renda variável
Os investimentos de renda fixa funcionam como um empréstimo do seu dinheiro ao emissor, como bancos, empresas ou o governo, que depois de um tempo retornam para você com um índice atrelado que rendem algum dinheiro a mais.
A renda variável consiste na compra de partes de um negócio, como uma empresa ou empreendimento imobiliário. Dentro dessas partes citadas vamos tratar na nossa série aqui de ações, ETFs e FIIs: elas são exemplos de renda variável.
O que são Ações?
Uma ação nada mais é do que a parte de uma empresa. Exemplo: imagine uma empresa como sendo uma pizza. Os donos e sócios da empresa possuem algumas fatias dessa pizza.
Além disso, se essa empresa for de capital aberto (possuir ações listadas na bolsa de valores abertas para venda ao mercado), as outras fatias são divididas em Ações, que podem ser compradas por qualquer pessoa.
Dessa forma, toda empresa que possui capital aberto possui vários sócios minoritários, que são aquelas pessoas que possuem suas ações.
O que é ETF?
“ETF” vem da sigla de uma expressão em inglês – “Exchange Traded Funds” – que, resumidamente, diz respeito a fundos que investem em uma variedade de ações. É como se fosse uma composição de ações num único local.
Por exemplo, temos o ETF chamado de BOVA11 que investe em todas as ações do Ibovespa, isto é, se você comprar uma cota de BOVA11, está, na verdade, comprando todas as ações do Ibovespa de uma única vez.
O que são Fundos Imobiliários?
São fundos que investem no ramo imobiliário. Então, é uma empresa que investe em casas, apartamentos, condomínios, shoppings, lajes corporativas e abre a oportunidade de pessoas investirem com elas.
Assim como nos ETFs, os FIIs são comprados por cotas, então toda vez que você investe em uma cota de um FII, você está investindo em alguma parte do mercado imobiliário através da empresa que você escolheu (a gestora do FII).
O que você precisa para investir em ações?
Qualquer pessoa com um CPF pode comprar ações, ETFs, FIIs. Você precisa ter uma conta em uma corretora (as ações ficam disponíveis lá) para acessar esses investimentos.
Ah! Vale ressaltar que não precisa ter muito dinheiro, visto que algumas ações custam muito pouco (exemplo: 2 reais). Inclusive existem lotes, e quantidades de ações reduzidas.
E para vender? Quase todos os ativos de renda variável são rapidamente/facilmente líquidos, porque rapidamente se transformam em dinheiro, ao serem vendidos.
Primeira compra de uma ação
Aqui vão alguns nomes e informações que serão úteis para você comprar sua primeira ação/investimento em renda variável.
Essas mesmas informações vão ser fundamentais para preenchimento da nossa planilha mais para frente da série, então é legal que você se familiarize com elas.
Nesta aula vamos estar planilhando a primeira venda de ações no Excel. Vamos fazer o primeiro registro de uma venda de ativos na nossa tabela!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na segunda aula da nossa série, nesta aula vou te mostrar como criar uma planilha para controle de ações e IR do zero!
Nesse post você vai aprender:
Nessa aula vamos te mostrar como continuar sua planilha para controle de ações, para isso vamos registrar uma venda de ações, visto que na última aula fizemos um registro de uma compra.
Então para melhorar nossa planilha de ações Excel e deixá-la mais completa, precisamos, além do registro de uma venda, fazer o cálculo médio de ações.
Registrar uma venda de ações
Para registrar uma venda de ações, iremos, na aba “Compra” da nossa planilha, registrar a operação do dia 04/02/2021 observado abaixo:
Registramos, então, uma venda de metade das ações compradas (152 da primeira aula) no dia 29/01 para exemplificar o cálculo de lucro ou prejuízo na operação com essa ação.
Cálculo de custo médio
Vendemos 76 ações ITUB4 em 04/02. Com os valores fictícios colocados, tivemos valor líquido de R$ 1952,62.
Então, temos que o valor de venda gerado para essas 76 ações vai ser comparado com o valor para comprar as mesmas 76 ações no outro dia, que foram compradas as mesmas 76 ou mais ações, sendo o caso da operação cadastrada na planilha de compra de 152 ações.
Assim, deveremos calcular o custo médio da ação, para ver qual o valor de compra de 76 das 152 ações compradas anteriormente gerou. Nesse caso, o custo médio foi de R$ 39,55, pela divisão do valor líquido de R$ 6012,32 pela quantidade de 152.
O valor de compra de 76 das 152 ações vai ser a multiplicação do custo médio (R$ 39,55) pela quantidade de ações vendidas (abatidas), 76. Com isso, teremos o valor de compra de R$ 3006,16.
Análise de compra x venda
Assim, conseguimos calcular o lucro ou prejuízo na operação a partir da subtração do valor de venda menos o valor de compra.
Como o valor de compra é maior do que o de venda, temos um prejuízo (resultado negativo).
OBS: Se tivéssemos vendido todas as 152 ações, o valor de compra teria o mesmo valor líquido de compra, de R$ 6012,32, como citado no vídeo.
Nesta aula vamos te mostrar como fazer a compra e venda de ações na nossa tabela, como calcular o resultado dessas operações e o custo médio!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na terceira aula da nossa série, vou te mostrar como criar uma planilha para controle de ações e IR do zero!
Nesse post você vai aprender:
Hoje vamos lidar com a compra de ações, venda de ações, e resultado das operações de venda, começando a fazer então um controle de ações no Excel.
Vamos registrar então mais uma operação de compra do mesmo ticker (ITUB4, de exemplo) para exemplificar o que queremos demonstrar nessa aula. Observe abaixo que compramos a quantidade de 35 ações ITUB4.
Calcularemos um novo custo médio para essa compra de 05/02/2021. Lembrando que esse é o novo custo médio DESSA OPERAÇÃO.
Vale lembrar aqui que uma operação de venda não altera o custo médio de uma compra anterior, porque você não desembolsou nenhum valor para vendê-las. Mas, é nas operações de venda que registramos algum valor para o resultado.
OBS1: só temos resultado com venda, porque antes veio uma compra de ações, daí é registrado um resultado total ou parcial de uma operação com ações.
Repare abaixo que o nosso resultado da operação é igual ao valor de venda (que é o valor líquido da linha correspondente à venda de 04/02), subtraído do valor principal de R$ 3006,16 (que é o valor usado para comprar 76 ações ITUB4 a R$39,52), resultando no prejuízo de R$ – 1053,54.
OBS2: No caso da operação de 05/02/2021, teremos um novo custo médio, diferente do registrado na data 29/01/2021.
Observe abaixo o resultado do cálculo do novo custo médio GLOBAL (que envolve todas as operações) para a operação de compra de ITUB4 no dia 05/02:
OBS3. Lembrando que esse custo médio total/global de R$ 36,87 vai ser o nosso “último” custo médio para levar adiante nas novas operações, e tomado como base de cálculo para novas operações de compra ou de venda.
Exemplificando uma operação de venda:
Repare acima que, o valor investido em 04/02/2021 é de R$ 3006,16, que é a multiplicação da quantidade de ações vendidas (76) pelo custo médio de compra dessas mesmas 76 ações, que foram compradas dia 29/01/2021 (e por isso que ela tem o mesmo custo médio daquela data).
Ou seja: Valor investido (04/02) = R$ 3006,16 = R$ 39,55 x 76
Exemplificando uma operação de compra:
Repare aqui que o novo valor investido (de 05/02/2021) vai ser o somatório de R$ 3006,16 com R$1086,56, que respectivamente significam quanto foi gasto nas ações “restantes” da primeira operação compra-venda (em 04/02/2021, explicado acima) e o que foi gasto na nova compra (05/02/2021).
Ou seja: Valor investido (05/02) = R$ 4092,72 = R$ 3006,16 + R$ 1086,56
Nesta aula vamos te mostrar como fazer a criação da carteira de ativos no Excel para complementar nossa planilha de controle da bolsa de valores!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na quarta aula da nossa série, vou te mostrar nesta aula como monitorar carteira de ativos.
Nesse post você vai aprender:
Hoje vamos lidar com a criação da carteira de ativos. Para isso, precisaremos saber como utilizar a função SOMASES, e também a função HISTÓRICODEAÇÕES no Excel.
Partindo da planilha montada na aula anterior, vamos começar a montar nossa carteira de ativos. Vamos também inserir uma compra de ações PETR4, para mostrar mais dois exemplos de cálculos.
Observe abaixo a nossa tabela exemplo de compra e venda de ações:
Cálculo da quantidade da carteira
Para fazer a soma de quantidade de um determinado ticker, devemos fazer duas fórmulas SOMASES: uma para as compras e outra para as vendas.
A quantidade final será o resultado da soma das compras subtraído pela soma das vendas de um mesmo ticker.
Exemplificando esse fato, para ITUB4:
Quantidade = =SOMASES(C2:C5;B2:B5;B13;F2:F5;”C”)-SOMASES(C2:C5;B2:B5;B13;F2:F5;”V”)
Quantidade = Soma de Compras de ITUB4 – Soma de Vendas de ITUB4
Quantidade = 187 – 76
Quantidade = 111
Cálculo do valor investido da carteira
O valor investido é o quanto foi desembolsado pela pessoa para comprar ou ter aquela quantidade de ações (tickers) que ela possui em sua carteira. Ele é calculado pela soma dos valores investidos em compras nas ações diminuído do valor investido com as vendas desse mesmo ticker.
Valor Investido = =SOMASES(I2:I5;B2:B5;B13;F2:F5;”C”)-SOMASES(I2:I5;B2:B5;B13;F2:F5;”V”)
Valor Investido = Soma de Valor Investido em Compras de ITUB4 – Soma de Valor Investido em Vendas ITUB4
Valor Investido = R$ 4092,72
Cálculo do custo médio da carteira
O custo médio é calculado de forma similar ao apresentado nas últimas aulas da série, dividindo o valor investido pela quantidade de tickers comprados. Observe abaixo o cálculo:
Custo Médio = Valor Investido / Quantidade
Custo Médio ITUB4 = 4092,72 / 111 = R$ 36,87
Data e cotação
Usar a fórmula HISTÓRICODEAÇÕES (Office 365), conseguiremos pegar a cotação do ticker em questão (ITUB4) do dia anterior (fechamento). Observe a fórmula abaixo:
Data e Cotação = =HISTÓRICODEAÇÕES(B13;HOJE()-1;;;0)
Data e Cotação = 11/02/2021 R$27,69
A fórmula é matricial, então ela pede o preenchimento de duas células adjacentes, preenchendo a data da cotação e a própria cotação (quanto vale aquele ticker naquela data). Com essas informações, poderemos calcular a variação daquele ticker.
Cálculo da variação
A variação nada mais é do que o valor correspondente se vendêssemos essas quantidades naquele dia cotado, ou seja, mostra o resultado de fato em prejuízo ou lucro se você vendesse aquela quantidade de ticker restante na sua carteira de ativos.
Variação (ITUB4)= =(G13-E13)*C13
Variação (ITUB4)= (Cotação ITUB4 – Custo Médio ITUB4) * (Quantidade)
Variação (ITUB4)= R$ – 1019,13
Com isso, teremos todos os cálculos feitos: quantidade, valor investido, custo médio, data, cotação e variação. Observe abaixo para o caso de ITUB4:
Nesta aula vamos te mostrar fazer a criação da Tabela de Notas de Corretagem para que possamos fazer todos os registros de forma organizada!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na quinta aula da nossa série, nesta aula vou te mostrar como monitorar carteira de ativos.
Nesse post você vai aprender:
Campos da tabela e alguns comentários citados no vídeo:
Nesta aula vamos estar planilhando uma Nota de Corretagem! Isso mesmo, nós vamos te mostrar como fazer esse registro na nossa planilha.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na sexta aula da nossa série, nesta aula vou te mostrar como registrar notas de corretagem, baseada no exemplo mostrado no vídeo.
Nesse post você vai aprender:
Vamos pegar uma nota fictícia de exemplo, e preencher a nossa tabela de notas. O resultado ficou resumido abaixo:
Alguns campos que não são apenas de preenchimento da nota de corretagem planilhada, e devem ser calculados:
OBS: Coluna de impostos é considerada taxa porque é um valor em cima da taxa operacional.
Lembrando que para vendas, a taxa vai ser subtraída do valor da venda, porque elas vão sempre sair do seu bolso (já que o valor da venda é positivo para o seu bolso, o valor da taxa é negativo, não te permitindo ganhar mais).
Já, para compras, a taxa vai ser paga por você para a corretora (pelas ações), que vai penalizar você e te fazer perder mais dinheiro. Por isso, precisamos usar uma fórmula de soma atrelada a uma condição (SE) de Compra (C) Ou Venda (V). Observe abaixo:
OBS2: IRRF só incide se for em Vendas. Em compras, o valor dele é 0 (zero). Por isso a primeira operação da nossa tabela acima não tem IRRF, e a 2ª e a 3ª sim.
OBS3: Das taxas, apenas mostramos acima as taxas de liquidação e emolumentos. As outras ficaram zeradas, de acordo com essa nota de corretagem específica, que registra compras e vendas na corretora ABCD.
OBS4: Os valores de IRRF, Taxa Liquidação e Emolumentos são calculadas conforme a média ponderada do valor total de cada um desses valores.
Descobriremos os percentuais dos valores individuais em relação ao total (soma) das operações. Assim, conseguiremos colocar pesos (ponderar) para diferenciar qual operação vai receber uma taxa maior, quando representar maior valor do total.
OBS5: Emolumentos e Taxa de Liquidação não dependem do tipo da operação
Lembrando que, ao final das contas, não precisaremos mais manter as fórmulas de cálculo para as taxas, e copiaremos e colaremos valores, para deixar a planilha mais leve e eficiente.
Apenas manteremos as fórmulas para as colunas em que essas contas não irão variar, nem necessitarão de uma conferência ou uma conta com valores de taxas específicos.
Ou seja, Valor da Operação, Taxas e Valor Líquido sempre serão fórmulas. O resto, serão sempre valores.
Nesta aula vamos te mostrar como será feita a criação da aba de movimentações para que possamos fazer todos os nossos registros!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na sétima aula da série, nesta aula vou te mostrar como lidar com notas de corretagem, baseadas nos exemplos mostrados no vídeo, assim como inseri-las na aba de movimentações.
O objetivo dessas aulas iniciais é cada vez mais complementar a nossa planilha de controle da bolsa de valores para que ela fique mais robusta, e nas próximas aulas começaremos a pensar na parte de programação para potencializá-la.
Nesse post você vai aprender:
Observe abaixo a nossa aba de movimentações com exemplos prontos, já cadastrados, e vamos posteriormente às explicações de seus campos.
ID
Representa o identificador da movimentação presente na tabela, que serve para ser um valor correspondente e de referência para uma aba de notas. Repare que incluímos também essa coluna na tabela de notas, de onde paramos na última aula da série.
Conta
Conta da corretora usada para fazer as movimentações.
Data Pregão
Data em que ocorreu o pregão referente àquela movimentação (ou quando ocorreu aquela movimentação, no caso de aportes ou retiradas, por exemplo)
Data Liquidação
Data na qual a movimentação se tornou líquida, ou seja, teve saída ou entrada de dinheiro da sua conta.
Descrição
É algo que vai descrever ou caracterizar aquela movimentação de ID específico, para que sirva como um campo esclarecedor. É um campo descritivo para você lembrar de detalhes daquela operação.
Tipo (Categorias)
Quantidade
Quantidade de ações movimentadas
Valor
Valor monetário da movimentação daquele dia, daquela corretora, daquele tipo, referente aquele ID.
Saldo de conta (da corretora)
Para calcular o saldo da corretora desejada (a digitar), devemos usar uma fórmula SOMASES, para termos uma forma de distinguir quais valores foram referentes a uma compra (valores que saíram da sua conta, ou seja, são negativos) e quais foram referentes a uma venda (valores positivos, ou seja, que entraram na sua conta).
Usaremos a fórmula abaixo:
=SOMASES(Tabela2[Valor];Tabela2[Conta];B1)
Nesta aula vamos te mostrar como será feito o registro das movimentações de venda na nossa Série Controles para Bolsa de Valores!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na oitava aula da série, nesta aula vou te mostrar como fazer movimentações de venda, baseadas nos exemplos mostrados no vídeo e como inseri-las na aba de movimentações.
Além disso, vamos introduzir a aba de opções.
O objetivo dessas aulas iniciais é cada vez mais complementar a nossa planilha de controle da bolsa de valores para que ela fique mais robusta, e nas próximas aulas começaremos a pensar na parte de programação para potencializá-la.
Nesse post você vai aprender:
Na última aula, paramos de cadastrar as movimentações, na aba de movimentações da nossa planilha, como mostra a tabela abaixo:
Vamos nessa aula cadastrar essas duas movimentações de vendas que ficaram pendentes (sem o X) de cadastro na aula passada da série, como mostra a tabela abaixo que está na aba de notas da nossa planilha.
Vamos cadastrar duas vendas: SAPR11 e YDUQ3. Observe abaixo como ficará a nossa aba de Movimentações após o cadastro dessas duas vendas (representadas pelos números de ID 6 e 7):
Ação
Preencher a quantidade de ações (ou tipo específico) vendidas (nesse caso), em valor negativo, porque estamos “perdendo” ou tirando essas quantidades da nossa posse. Por consequência disso, entra dinheiro na nossa conta. Então, o valor registrado para aquela venda é positivo.
Resultado Ação
O “resultado ação” é a resposta de lucro ou prejuízo gerado a partir daquela venda. Não importa se preenchemos quantidade ou não nessa “linha” da tabela porque não fará diferença.
O seu valor é resultado entre a subtração do valor de venda daquelas ações e o valor de compra dessas mesmas ações. Se positivo, teremos um resultado de lucro. Se negativo, resultado de prejuízo.
I.R.R.F Ação
É o valor destinado àquela linha de imposto na aba de notas, que é sempre negativo. Se é um imposto pago, temos um valor que é pago e sai da sua conta para pagar o imposto.
Porém, algumas instituições acabam não cobrando esse imposto por não alcançar determinado valor de venda em ações por mês. Por isso é bom verificar esse valor, e, caso não seja cobrado, não informar.
Extra
Vamos também fazer algumas modificações necessárias para facilitar o nosso desenvolvimento da aba de resumo:
1) Nomear a tabela presente na aba de Notas de TabelaNotas:
2) Nomear a tabela presente na aba de Movimentações de TabelaMovimentacoes,
3) Criar uma aba de Opções, que vai conter a lista de opções que vão servir de auxílio para a modificação 4
4) Criar uma validação de dados para a coluna de Tipo na TabelaMovimentacoes, baseada nas opções de tipo listadas na aba Opções, que serão: Ação, FII, ETF, BDR e Opção. Para criar a validação, iremos:
5) Criar uma validação de dados para a coluna de C/V na TabelaMovimentacoes, que vai conter qual foi a movimentação, de compra ou venda. Para isso, seguiremos os passos destacados acima, e em Fonte colocaremos apenas “C;V”.
Nesta aula vamos te mostrar como criar a tabela auxiliar da aba resumo para que possamos construir nosso resumo e ter a visualização gráfica dele!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na nona aula da nossa série, nesta aula vou te mostrar como começar a construir a aba de resumo dos nossos ativos.
Nesse post você vai aprender:
Observe abaixo a nossa aba de movimentações com exemplos prontos, já cadastrados, e vamos posteriormente às explicações de seus campos.
Precisamos ter em uma coluna todos os ativos que aparecem na aba Notas (coluna de Ticker da TabelaNotas), de forma única, ou seja, precisamos de todos os ativos, sem repetição, para fazermos um quadro resumo sobre eles.
Para isso, iremos utilizar a fórmula do Office 365 chamada Único. Observe abaixo:
Precisamos ter na coluna de Tipo da nossa tabela de interesse os tipos dos ativos encontrados na coluna A.
Então, teremos que fazer uma procura de tipo dos valores correspondentes presentes na coluna A da nossa tabela, mas lá na tabela de Notas.
Para isso, iremos utilizar a fórmula do Office 365 chamada PROCX. Observe abaixo:
Na terceira coluna da nossa tabela de interesse, queremos ter as quantidades do ativo em questão, localizado na nossa coluna A.
Para isso, vamos utilizar uma fórmula SOMASES, para somar as quantidades, com a condição de ser daquele Ativo em questão, daquele Tipo.
Ou seja, duas condições para realizar a soma de quantidades dessas ações na TabelaMovimentacoes. Observe abaixo:
Na última coluna da nossa tabela de interesse, a coluna D, teremos o valor investido em cada ativo, daquele tipo específico. Para isso, usaremos também a fórmula SOMASES.
Ou seja, duas condições para realizar a soma dos valores investidos dessas ações na TabelaMovimentacoes. Observe abaixo:
Nesta aula vamos dar continuidade a construção da nossa aba resumo criando a carteira com as posições abertas nesse resumo!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima aula da série, nesta aula vou te mostrar como continuar a construir a aba de resumo dos nossos ativos.
Vamos cada vez mais complementar a nossa planilha de controle da bolsa de valores para que ela fique mais robusta, e nas próximas aulas começaremos a pensar na parte de programação para potencializá-la.
Nesse post você vai aprender:
Ativo
Para listar os ativos da nossa aba de resumo, precisaremos dos que possuem quantidades maiores do que 0, na nossa tabela auxiliar criada na última aula.
Para isso devemos usar mão da fórmula filtro, do Office 365. Observe como ela fica:
=FILTRO(A2#;C2#>0)
Quantidade
Para buscar a quantidade referente aos ativos encontrados pela fórmula filtro feita na etapa anterior, precisamos usar uma fórmula PROCV.
Precisaremos buscar o ativo respectivo da coluna F na coluna A e retornar o que estiver na sua quantidade na coluna C. Com isso, nossa fórmula PROCV ficará:
=PROCV(F2#;A:C;3;FALSO)
Valor Investido
O valor investido desses ativos listados também vai ser puxado por meio de uma fórmula PROCV, como citado acima, só que dessa vez buscaremos o valor da quarta coluna da nossa tabela auxiliar (coluna D). Observe:
=PROCV(F2#;A:D;4;0)
Custo Médio
O custo médio desses ativos já foi explicado nas aulas anteriores, mas relembrando: é o valor resultante da divisão do valor investido e as quantidades daquele ativo listado na nossa tabela. Observe a seguir:
=I2#/G2#
Preço Atual
Vamos explicar melhor essa parte na próxima aula, onde ensinaremos uma nova fórmula do Office 365 para buscar esses valores da cotação dos ativos em uma certa data.
Valor Atual
O valor atual nada mais é do que a multiplicação entre as quantidades daquele determinado ativo por seu valor de cotação (preço atual), que vamos aprender como completar na próxima aula.
Vamos de qualquer forma deixar a fórmula pronta. Observe:
=G2#*J2
Variação
A variação é o nosso resultado: é a subtração entre o valor atual e o valor investido.
Interpretação: se for maior do que zero, você teria lucro ao vender aquelas quantidades de ativos naquela data analisada; e se menor que zero, você teria prejuízo se vendesse as ações naquela data.
A fórmula abaixo representa essa subtração:
=K2#-I2#
Variação %
A variação é simplesmente o quanto o valor absoluto de variação (calculado na etapa anterior) representa no valor investido. É a variação em forma percentual, apenas.
Observe abaixo como é feito o seu cálculo:
=L2#/I2#
Após fazer essas etapas, teremos as fórmulas realizadas para resumir as linhas em que temos quantidades maiores que zero de ativos, que no caso (exemplo) se resume a CASH3.
OBS: Como explicado no vídeo, as fórmulas que carregam “#” representam uma forma automática que as fórmulas do Office 365 entendem que precisam estender as fórmulas para as células abaixo para continuar os cálculos.
Caso outras células relacionadas apareçam nas linhas abaixo, elas automaticamente se estendem e realizam novos cálculos para suprir aquela nova entrada de células adjacentes.
Nesta aula vamos te mostrar como extrair a cotação de um ativo de duas formas diferentes, para os que não possuem a versão atual do Excel!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima primeira aula da série, nesta aula vou te mostrar como começar a construir a aba de resumo dos nossos ativos.
O objetivo dessas aulas iniciais é cada vez mais complementar a nossa planilha de controle da bolsa de valores para que ela fique mais robusta, e nas próximas aulas começaremos a pensar na parte de programação para potencializá-la.
Nesse post você vai aprender:
Na última aula, paramos com a tabela de resumo da seguinte maneira:
Agora vamos formatar e complementar a tabela. Acompanhe a seguir:
Formatação
Na Página Inicial, aplicar cor de fundo azul e letra branca em todo cabeçalho, conforme as imagens a seguir:
Também na guia Página Inicial vamos aplicar texto centralizado em toda a tabela: informações do cabeçalho e das ações. Observe como:
Aba de cotações dos ativos
Criaremos uma aba, e chamaremos de “Cotações”. Ela vai conter uma lista das nossas ações, sem repetição, e o preço atual dessas ações (ativos). Observe abaixo as fórmulas e a tabela resultante.
A fórmula ÚNICO vai nos dar a lista de ativos, sem repetição, que aparecem na nossa aba de notas. Assim, podemos ter nessa aba as informações de preço de todas as ações envolvidas pela nossa carteira de ações. Observe a fórmula abaixo:
=ÚNICO(TabelaNotas[Ticker])
A partir da fórmula acima, precisamos buscar os preços dos ativos listados. Para isso, iremos utilizar uma fórmula/função específica do Office 365 chamada de função HISTÓRICODEAÇÕES.
Essa fórmula, combinada com a função SEERRO e a função HOJE, e juntas, nos darão a cotação dos ativos para o dia anterior, ou de 2 dias atrás, ou de 3, ou de 4, caso essa fórmula de histórico de ações não consiga buscar o valor de um dia específico (final de semana, feriado, por exemplo). Observe abaixo a nossa fórmula completa:
=SEERRO(HISTÓRICODEAÇÕES(A2;HOJE();;;0);
SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-1;;;0);
SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-2;;;0);
SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-3;;;0);HISTÓRICODEAÇÕES(A2;HOJE()-4;;;0)))))
Alternativa de trazer os preços pela integração com Google Sheets
Vamos apresentar aqui também uma alternativa à fórmula histórico de ações. Ela envolve e vai requerer:
– Ter uma conta Google
– Acessar o Google Drive
– Criar uma planilha indo em Novo > Planilhas Google
– Colocar a lista de ações de interesse
– Usar a função GOOGLEFINANCE conforme abaixo:
– Iremos em Arquivo > Publicar na web > Publicar
– Copiaremos o link
– No Excel, iremos na guia Dados > Da Web > Colar o link > OK > Selecionar o último nível, conforme mostrado no vídeo
– Duas tabelas serão carregadas a partir desse link, e iremos escolher a segunda, de nome “Table”
– Clicaremos em transformar dados
– Ao abrir o Power Query (editor), vamos fazer as edições necessárias para trazer nossa tabela
– Iremos clicar com CTRL nas duas primeiras colunas da esquerda > Botão direito do mouse > Remover Colunas
– Fechar e carregar
Trazer preço atual do ativo da aba de cotações
Agora que já temos as cotações/preços atuais dos ativos desejados, precisamos trazer eles para a nossa tabela da aba de resumo.
Abaixo mostramos a fórmula necessária para fazer esse processo:
Faremos um PROCV para trazer o valor da terceira coluna da tabela de cotações, que vai da coluna de A até C. Observe a fórmula:
=PROCV(F2#;Cotações!A:C;3;0)
Nesta aula vamos te mostrar como criar um gráfico de distribuição da carteira para que você tenha um resumo visual dos seus ativos!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima segunda aula da série, nesta aula vou te mostrar como construir o gráfico de distribuição da carteira, para que se possa analisar o percentual de cada ativo em nossa carteira.
Nesse post você vai aprender:
Vamos aumentar nossa tabela com uma tabela auxiliar da construída na última aula, para que possamos criar esse gráfico de forma correta.
Relembrando: na última aula, paramos com a tabela de resumo da seguinte maneira.
Essa tabela auxiliar que irá aumentar nossa tabela da última aula será composta por 4 colunas: Ativos prejuízo, Intervalo prejuízo, Ativos lucro e Intervalo lucro.
Para a construção dessas colunas novas vamos utilizar da função SE para podermos definir um resultado conforme a variação do ativo. Desta forma vamos separar o que foi positivo e negativo.
Observe abaixo as fórmulas e explicações:
Ativos Prejuízo
Fórmula: =SE(L2#<0;F2#;"")
Explicação: Se o valor da coluna respectiva de variação do ativo for negativo (menor do que zero), iremos pegar o nome do ativo que tem essa variação negativa. Se não, ficará escrito vazio (“”) de propósito, ou seja, nada.
Queremos fazer isso para que no gráfico de pizza não fique aparecendo nada para esse valor.
Intervalo Prejuízo
Fórmula: =SE(L2#<0;L2#;0)
Explicação: Se o valor da coluna respectiva de variação for negativo (menor do que zero), iremos pegar o próprio valor negativo. Se não, ficará escrito zero (0) de propósito.
Queremos fazer isso para que no gráfico de pizza não fique aparecendo nada para esse valor.
Ativos Lucro
Fórmula: =SE(L2#>0;F2#;"")
Explicação: Se o valor da coluna respectiva de variação for positivo (maior do que zero), iremos pegar o nome do ativo que tem essa variação positiva. Se não, ficará escrito vazio (“”) de propósito, ou seja, nada.
Queremos fazer isso para que no gráfico de pizza não fique aparecendo nada para esse valor.
Intervalo Lucro
Fórmula: =SE(L2#>0;L2#;0)
Explicação: Se o valor da coluna respectiva de variação for positivo (maior do que zero), iremos pegar o próprio valor positivo. Se não, ficará escrito zero (0) de propósito.
Queremos fazer isso para que no gráfico de pizza não fique aparecendo nada para esse valor.
Gráfico de Pizza
Nesse post aqui a gente te ensina como fazer e lidar com gráficos de Pizza dentro do Excel.
Para criar o nosso gráfico, como explicado no vídeo, vamos:
Para personalizar gráfico, como na imagem acima, teremos que fazer os passos a seguir:
Nesta aula vamos criar o gráfico de Lucro x Prejuízo para complementar nossa visualização dos ativos que temos na carteira!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima terceira aula da série, nesta aula vou te mostrar como construir o gráfico de distribuição da carteira, para que se possa analisar o percentual de cada ativo em nossa carteira.
Nesse post você vai aprender:
Nesse post aqui nós ensinamos como fazer alguns gráficos, inclusive como criar gráfico de coluna, que será ensinado a seguir.
Caso queira conferir, na última aula ensinamos como criar um gráfico de pizza para observar o percentual de lucro e prejuízo com os ativos, algo que você também pode aprender aqui nesse post.
Vamos inserir uma linha na aba de movimentações e na aba de notas que nos permita ter um outro cenário do que já tínhamos na tabela mostrada no final da aula anterior (LINK), onde poderemos ter um cenário de lucro e um cenário de prejuízo.
Para criar o nosso gráfico, como explicado no vídeo, vamos:
Para personalizar gráfico, como na imagem acima, teremos que fazer os passos a seguir:
Teremos, então, após esse processo de criação e como fazer a formatação gráfica, o resultado a seguir:
Nesta aula vamos finalizar o design da aba resumo para que possamos dar continuidade a nossa série com a planilha organizada para a macro!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima quarta da nossa série, nesta aula vou te mostrar como completar o design da aba de resumo criada nas aulas anteriores.
Nesse post você vai aprender:
Na última aula nossa aba de resumo da planilha ficou da seguinte forma:
1) Design da aba
Para finalizar o design da nossa aba de resumo, vamos modificar o gráfico de pizza para contemplar as ações de lucro também. Acompanhe:
Para modificar o nosso gráfico de pizza, como explicado no vídeo, vamos:
Também iremos trocar a cor das colunas do gráfico de colunas, laranja em negativo (para baixo), e de azul os positivos (para cima), que indicam prejuízo e lucro, respectivamente. Acompanhe:
2) Formatação condicional da tabela
Bordas da tabela:
Valores negativos e positivos:
OBS: Lembrando que, como citamos no vídeo, temos outras aulas aqui no blog que mostram como fazer a formatação condicional. Aproveite essas aulas para ficar craque nessa ferramenta do Excel!
3) Configurar gráfico para células ocultas
Para configurar o gráfico para células ocultas, precisamos ativar isso no gráfico de colunas, porque vamos ocultar todas as colunas auxiliares da aba, criadas nas aulas anteriores, e deixar somente os valores da tabela de interesse. Acompanhe:
Ocultaremos todas as colunas, exceto às expostas abaixo, que traduzem como ficará a nossa aba de resumo:
Nesta aula vamos criar gráficos automáticos para que você possa ter essa atualização sempre que inserir uma nova informação na planilha!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima quinta aula da série, onde estaremos criando gráficos automáticos a partir daqueles gráficos de colunas e de pizza criados nas aulas anteriores.
Nesse post você vai aprender:
Na última aula da série (LINK), a nossa aba de resumo da planilha ficou da seguinte forma:
Incluir movimentação fictícia (ID 9) no começo, em notas e movimentações, e ao final do processo mais uma (ID 10), da seguinte forma:
Elas vão gerar um reflexo na aba de movimentações mostrado na tabela abaixo, além de um aporte necessário (representado pelo ID 11):
Para criar os gráficos dinâmicos, vamos utilizar de um artifício das referências das fórmulas do Office 365, como mencionado no vídeo.
Lembrando que para quem não tem essa versão, também disponibilizamos a planilha feita de outras maneiras.
Acompanhe a seguir os passos para criar nomes que vão representar as nossas referências automáticas:
OBS: Escopo: Resumo em todos
Agora, precisamos introduzir esses nomes criados nos nossos gráficos. Lembrando esses nomes criados são os que fazem referência aos nossos intervalos automáticos.
Eles foram criados para adaptar o que o gráfico precisa que seja escrito em sua fórmula para receber valores, pois ele não reconhece/aceita as fórmulas com # (pelo menos não ainda).
Portanto, a seguir vamos fazer as modificações nos gráficos.
Lembrando que, no vídeo introduzimos outras notas na aba de notas e movimentações fictícias para testar esse reflexo nos gráficos já modificados.
Ao final do nosso processo dessa aula, nós teremos o que podemos observar abaixo:
Nesta aula vamos atender à pedidos e inserir notas de corretagem de day trade na nossa planilha para deixá-la ainda mais completa!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima sexta aula, onde estaremos lidando com notas de corretagem de day trade, algo pedido por vocês!
Nesse post você vai aprender:
Na última aula a nossa aba de resumo da planilha ficou da seguinte forma:
Day trade (transação do dia, em inglês) nada mais é do que a estratégia de comprar e vender ações no mesmo dia. Ou seja, fazer operações de compra e venda de ações no prazo máximo de um dia, ou de um mesmo pregão.
Para o nosso controle de ativos você tem a possibilidade de criar uma aba para as operações de Day Trade, ou pode colocar na aba de notas.
Optando por esse último caso, terá que criar um identificador, para que futuramente essa operação possa ser identificada com a macro. Ou seja, assim saberíamos o que é day trade e o que não é, para deixar tudo organizado.
Nós aqui vamos optar por fazer em uma aba separada, por questão de escolha e didática.
Vamos então duplicar a aba de notas, pressionando Ctrl com o teclado, clicando com botão esquerdo do mouse e arrastando para o lado. Ao soltar, teremos nossa aba duplicada. Vamos dar um duplo clique com botão esquerdo do mouse e renomeá-la.
Iremos apagar as linhas desnecessárias, e deixar apenas uma informação exemplo para Day Trade. Vamos excluir os valores e deixar apenas as fórmulas, como mostrado no vídeo.
A seguir podemos ver os exemplos usados na aula, para a aba de Day Trade:
OBS: repare aqui que o I.R.R.F. para operações Day Trade é de 1% em cima do lucro da operação, como mostrado no vídeo. Mas fique tranquilo que esse valor vai ser passado na nota de day trade, e você provavelmente não precisará fazer essa conta.
Vamos também incluir essas operações na aba de movimentações, para ver os efeitos dessas notas acontecerem, tanto em resultado, quanto em imposto, e como isso vai refletir no valor do nosso saldo de corretora (levemente, mas vai). Observe abaixo:
E o reflexo disso na aba de resumo?
Observe que não há! Concorda que tudo (quantidade) que compramos daquele ativo em day trade nós vendemos? Nada dessas quantidades ficou pendente, correto?
Então não teremos nada desse ativo em day trade na aba de resumo, a não ser que tenha algo sobrando dele vindo das nossas notas. Em day trade, tudo que é comprado, é vendido no mesmo dia.
O resultado da ação day trade se deve apenas ao seu lucro entre o que foi vendido e o que foi comprado, se positivo, e prejuízo, em cenário contrário. Mas nada será mostrado na aba de resumo porque as quantidades serão anuladas sempre (o que é comprado é vendido).
Portanto, nada foi modificado na nossa aba resumo. Observe:
Vamos então ajeitar as nossas abas, retirando as linhas de grade, para que elas fiquem mais apresentáveis e mais “limpas” visualmente. Iremos em Exibir > Linhas de grade (desmarcar) para cada aba.
Nesta aula vamos te mostrar alguns eventos da bolsa de valores como os agrupamentos e desdobramentos (Splits e Inplits)!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Estamos na décima sétima aula da série, onde estaremos lidando com os eventos de agrupamentos e desdobramentos (splits e inplits)!
Nesse post você vai aprender:
Na última aula da série (LINK), a nossa aba de resumo da planilha ficou da seguinte forma:
Os agrupamentos e desdobramentos são eventos da bolsa de valores que vão alterar quantidade e preço dos ativos na mesma proporção.
Desdobramentos
Por exemplo, os desdobramentos na bolsa de valores (splits na bolsa de valores) acontecem quando o valor de uma ação de uma determinada empresa está relativamente alto e ocorre um aumento na quantidade que você tem.
No entanto, observe abaixo que você não vai ganhar mais por isso:
Então, se a proporção foi de 1 para 4, então de 100 ativos passamos a ter 400, o preço da ação também vai diminuir na mesma proporção (vai ser dividida por 4).
Repare acima que a quantidade foi de 100 para 400, e o preço foi de 25 para 6,25 reais, e que o valor total se mantém.
Isso quer dizer que teremos o preço dividido por 4, tendo exatamente o mesmo valor final, então não vamos ganhar nem perder dinheiro nesse evento.
Agrupamentos
Os agrupamentos na bolsa de valores (inplits na bolsa de valores) já funcionam de forma oposta ao mostrado para os desdobramentos. Observe:
Ou seja, ocorre um aumento do preço do ativo, mas com isso temos uma diminuição na quantidade na mesma proporção.
Repare que o valor total se mantém.
Da mesma maneira não temos perdas nem ganhos de dinheiro nesses eventos. O principal motivo é facilitar que novos compradores consigam adquirir esses ativos, seja de forma a ficar mais acessível, ou criar um grupo de ações concentrado (maior) para ser comprado.
Exemplo de desdobramento na planilha
Para fazermos esses eventos refletirem na nossa planilha, vamos usar um exemplo ode MGLU3, que foi desdobrada em 14 de outubro de 2020, de 1 para 4.
Ou seja, as ações dela que existiam anteriormente a essa data serão multiplicadas por quatro e seu valor será dividida por quatro.
Para isso devemos registrando um ativo de MGLU3 anterior a essa data, para que possamos simular e programar um código que faça essa leitura e os cálculos necessários para esse evento. Observe o novo registro de ID 14 na aba de notas:
Observe também o reflexo desse registro do ID 14 na aba de movimentações, incluindo um aporte:
Também, o reflexo na aba de resumo:
Até aqui vamos apenas incluir esses ativos na aba de notas e movimentações.
A partir da próxima aula vamos começar a construir os códigos, inclusive esse que vai fazer a operacionalização desse desdobramento exemplo registrado aqui nessa aula.
Nesta aula vamos te mostrar como fazer a automação de splits e inplits que foram os eventos da bolsa de valores vistos na última aula!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a décima oitava aula da série! Nela estaremos lidando com os eventos de agrupamentos e desdobramentos (splits e inplits), assim como na aula anterior, mas dessa vez vamos atuar na automação de splits e inplits.
Nesse post você vai aprender:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos em Inserir > Módulo, e poderemos começar a escrever nossos códigos:
Sub split_inplit()
ativo = InputBox("Digite o Ticker do ativo a ser ajustado.")
If ativo = "" Then Exit Sub
proporcao = InputBox("Digite a proporção do evento. Ex: se for um split de 1 para 4, digite 1/4. Se for um inplit de 4 para 1, digite 4/1.")
If proporcao = "" Then Exit Sub
data_corte = CDate(InputBox("Digite a data de corte do evento. Ex: os acionistas só terão direito a esse evento se tiverem adquirido os ativos até a data 13/10/2020, então a data de corte é 13/10/2020"))
If data_corte = 0 Then Exit Sub
pos_barra = InStr(proporcao, "/")
de = CInt(Left(proporcao, pos_barra - 1))
para = CInt(Mid(proporcao, pos_barra + 1, 2))
linha = Sheets("Notas").Range("E1048576").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 9) = ativo And Sheets("Notas").Cells(i, 5) <= data_corte Then
Sheets("Notas").Cells(i, 10) = WorksheetFunction.RoundDown(Sheets("Notas").Cells(i, 10) * para / de, 0)
Sheets("Notas").Cells(i, 11) = Sheets("Notas").Cells(i, 11) * de / para
End If
Next
linha = Sheets("Movimentações").Range("A1048576").End(xlUp).Row
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 5) = ativo And Sheets("Movimentações").Cells(i, 3) <= data_corte Then
Sheets("Movimentações").Cells(i, 7) = WorksheetFunction.RoundDown(Sheets("Movimentações").Cells(i, 7) * para / de, 0)
End If
Next
End Sub
Comentários e explicação do código:
Vamos começar nosso código atribuindo a uma variável chamada ativo o que o usuário inserir na 1ª caixa de texto que vai aparecer para o usuário digitar, com a função InputBox.
Se essa variável for igual a vazio, ou seja, o usuário não digitar nada, vamos sair do nosso código, com a função IF. Se for diferente de vazio, a macro continuará.
OBS: Nessa publicação aqui nós falamos com detalhes sobre a função IF no VBA. Vale conferir a aula se você não está acostumada (o) com ela!
A 2ª InputBox vai pegar a proporção do inplit ou do split, dependendo do que você preencha, como exemplificado no vídeo (1/4 ou 4/1, dependendo do movimento) e armazenar na variável “proporcao”.
Novamente vamos conferir se uma variável é vazia ou não, e sairemos do código ou não, respectivamente. Dessa vez, olhando apenas para o caso da variável “proporcao”.
A 3ª InputBox vai pegar a data de corte do evento (inplit ou split). Dependendo do que você preencha, irá ficar armazenada uma data como texto, porque a inputbox é uma caixa de texto e ela retorna um texto.
Por isso precisamos usar de uma função por fora, no caso a função CDate, para fazer a conversão de texto em data, para termos efetivamente uma data atrelada à variável “data_corte”.
Novamente vamos conferir se uma variável é vazia ou não, e sairemos do código ou não, respectivamente. Dessa vez, olhando apenas para o caso da variável “data_corte”.
Após isso vamos pegar os valores de “de” e “para”, onde o “de” é o que temos antes do caractere “/”, e o para é o que vem depois do caractere “/”.
Para pegar a posição da barra, vamos usar a função InStr, para procurar em um texto no VBA, no caso da nossa variável “proporcao”, o caractere “/”, e armazenar essa posição na variável “pos_barra”.
A partir dessa posição, vamos utilizar a função Left para pegar o que está antes da barra. Precisaremos converter em inteiro com a função CInt porque a função Left nos retorna um texto, e armazenaremos esse número na variável “de”.
Faremos uma lógica parecida para achar o que vem depois da “/”, com a função Mid, e precisaremos converter também o texto para inteiro com a função CInt. Armazenaremos esse número na variável “para”.
Para apoiar a explicação das fórmulas de texto usadas aqui, temos outras aulas também que mostram essa explicação: para a função InStr especificamente, e para as outras fórmulas de texto no VBA.
Depois disso tudo feito, vamos buscar a última linha preenchida na tabela, e armazenar na variável “linha”. Mostramos em outras aulas como achar a última linha de uma tabela no Excel, como nessa.
Fique atento ao detalhe de citar em qual aba nós acharemos a última linha da tabela, que nesse caso é a última linha da aba de Notas.
Após isso, vamos criar uma estrutura de repetição, nesse caso a estrutura de repetição For, para fazer um looping e percorrer as linhas da linha 2 até a última linha da nossa planilha, fazendo determinadas ações:
Se a célula que está na linha analisada, na coluna dos tickers (9), for igual ao ativo armazenado na variável “ativo”, e se a data que estiver nessa mesma linha, na coluna de datas de pregão (5) for menor ou igual à variável “data_corte”, queremos na coluna 10 (coluna J, de quantidade), o que estiver escrito na célula multiplicado pela variável “para”, dividir pela variável “de”.
Além disso, precisamos arredondar esse valor utilizando a função arredondar para baixo do Excel, invocada no VBA pelo método WorksheetFunction.
Além dessa ação, precisaremos multiplicar por “de” e dividir por “para” (contrário do apresentado acima) na célula da mesma linha, na coluna 11.
Precisamos fazer outra estrutura de repetição, agora para a aba de Movimentações, da linha 4 até a última linha dela. Reutilizaremos a variável linha, mas agora ela pegará a informação na aba de Movimentações.
Em seguida faremos outra verificação com a função If.
Se a célula que está na linha analisada, na coluna dos tickers (5, nesse caso), for igual ao ativo armazenado na variável “ativo”, e se a data que estiver nessa mesma linha, na coluna de datas de pregão (3, nesse caso) for menor ou igual à variável “data_corte”, queremos na coluna 7 (de quantidade), o que estiver escrito na célula multiplicado pela variável “para”, dividir pela variável “de”.
Além disso, precisamos arredondar esse valor utilizando a função arredondar para baixo do Excel, invocada no VBA pelo método WorksheetFunction.
OBS: Para salvar a planilha com o formato .xlsm, você deve ir em Arquivo > Salvar como > Modificar o formato do arquivo para Pasta de Trabalho Habilitada para Macro do Excel (*.xlsm). A outra forma é mostrada no vídeo, equivalente a essa apresentada aqui.
Botão para rodar o nosso código criado
Precisamos de um botão que será nosso ativador ou abridor do nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos). A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos que esteja escrito nele.
No nosso exemplo, escrevemos “Registrar Venda”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (split_inplit). Em seguida, clicaremos nela e em OK. Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Resumo
Nessa aula aprendemos como fazer a Automação de Splits e Inplits (agrupamentos e desdobramentos) para que o Excel faça isso de forma automática apenas com as informações inseridas pelo usuário.
Assim você não precisará fazer todo esse processamento de forma manual, onde poderia acabar cometendo algum erro durante esse registro de dados.
Para as automações, vamos utilizar algumas funções muito importantes, como:
Essas funções serão utilizadas para facilitar a utilização dos dados, buscar informações e formatar os dados da forma correta.
Por fim, vimos também como utilizar a estrutura de repetição For para poder repetir algumas partes do código, e evitar escrevê-las diversas vezes.
Nesta aula vamos trazer uma novidade para o gráfico de pizza, vamos utilizar o gráfico pizza de pizza para melhorar a visualização dos dados!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a décima nona aula da série! Nela estaremos trazendo uma novidade para o nosso gráfico de pizza, criado na aula 12 da série, que mostra a nossa distribuição da carteira.
Nesse post você vai aprender:
Vamos hoje complementar nosso gráfico de pizza, mostrando uma outra visualização/personalização de um segundo gráfico de pizza.
Esse segundo gráfico de pizza vai representar um zoom/separação de uma das categorias da pizza maior.
Pegaremos a categoria outros, e faremos uma outra pizza só dela, porque elas ficariam espremidas no gráfico maior, por terem um percentual muito pequeno. Dessa forma, fica uma visualização legal e mais fácil de enxergar como é essa distribuição das fatias menores.
Para criar esse gráfico, precisaremos:
Para fazer a formatação do gráfico de pizza, deveremos:
Configurar o gráfico para células ocultas
Como mostrado no vídeo, precisamos ativar isso no gráfico de pizza, porque vamos ocultar todas as colunas auxiliares da aba, criadas nas aulas anteriores, e deixar somente os valores da tabela de interesse. Acompanhe:
Como dizer quantos ativos ficam em cada pizza?
Como mudar o nome da categoria que vai para o segundo gráfico de pizza (menor)?
Após essas modificações, a nossa planilha de Resumo estará da seguinte forma:
Nesta aula vamos estar automatizando a aba de cotações para que você não tenha mais que fazer tudo de forma manual e demorada!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a vigésima aula da série! Nela vamos utilizar eventos no VBA para poder automatizar a aba de cotações da nossa planilha da série para que você não precise toda vez ajustar as fórmulas que buscam as cotações diárias daqueles ativos.
Nesse post você vai aprender a lidar com:
Para poder automatizar a aba de cotações da nossa planilha, mais especificamente falando das fórmulas das cotações dos ativos, vamos construir e explicar um código para esse fim.
Ele vai ser implementado com a ajuda dos eventos no VBA: nesse caso, o de ativação da aba “Resumo”.
Para visualizarmos seu funcionamento, teremos que inserir um novo ativo (ID 16) na aba de notas, e nas movimentações, como a seguir:
Mas, como mostrado no vídeo, repare que esse ativo de exemplo adicionado não refletirá exatamente o que precisamos para deixar essa adição automática nas tabelas das outras abas, a de Resumo e a de Cotações. Observe:
Vamos, então, inserir o código abaixo dentro da pasta de objetos, na planilha (aba) que corresponde ao nome “Resumo” (Planilha 4 (Resumo), dentro do VBA).
No vídeo mostramos com detalhes como chegar a ela. Observe o código final:
Private Sub Worksheet_Activate()
linha_a = Sheets("Cotações").Range("A1048576").End(xlUp).Row
linha_b = Sheets("Cotações").Range("B1048576").End(xlUp).Row
If linha_a <> linha_b Then
Sheets("Cotações").Range("B2").Formula2Local = "=SEERRO(HISTÓRICODEAÇÕES(A2;HOJE();;;0);SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-1;;;0);SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-2;;;0);SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-3;;;0);HISTÓRICODEAÇÕES(A2;HOJE()-4;;;0)))))"
If linha_a > 2 Then
Sheets("Cotações").Range("B2:B" & linha_a).FillDown
End If
End If
End Sub
Explicação do código:
OBS: Mostramos em outras aulas como achar a última linha de uma tabela no Excel, como nessa
OBS2: Nessa outra aula mostramos como lidar com a função IF
Pós código, nossas abas de Resumo e Cotações ficarão da forma correta, como desejávamos, e de forma automática. Observe:
Nesta aula vamos iniciar o preenchimento das taxas com VBA 1 de 3, pois serão 3 aulas para fazer essa automação dentro do VBA!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a vigésima primeira aula da nossa série! Nela vamos utilizar eventos no VBA para poder automatizar a aba de Notas da nossa planilha da série, e como fazer o preenchimento das taxas com VBA 1 de 3 de forma automática.
Essa aula vai ser a primeira de três aulas, para fazer esse código.
Nesse post você vai aprender a lidar com:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, e começar a escrever uma nova sub, como mostrado no vídeo. Escreveremos “sub preenchendo_taxas” e daremos enter. Observe abaixo o nosso código pronto:
Sub preenchendo_taxas()
Dim m_valor_taxas(1 To 11)
m_taxas = Array("I.R.R.F.", "Taxa Liquidação", "Taxa Registro", "Taxa Termo/Opções", "Taxa A.N.A.", "Emolumentos", "Taxa Operacional", "Taxa Execução", "Taxa Custódia", "Impostos", "Taxa Outros")
cont = 1
For Each taxa In m_taxas
m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa)
cont = cont + 1
Next
End Sub
Explicação do código:
------> m_valor_taxas(1) = InputBox("Digite o valor da taxa: " & “I.R.R.F”)
OBS2: Nesse post aqui você pode aprender mais sobre a estrutura de repetição For Each no VBA
Botão para rodar o nosso código
Precisamos de um botão que será nosso ativador ou abridor do nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos). A partir dessa escolha, redimensione a ilustração segundo o seu gosto, e pronto.
Vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos que esteja escrito nele. No nosso exemplo, escrevemos “Registrar Venda”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (preenchendo_taxas).
Em seguida, clicaremos nela e em OK. Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nesta aula vamos continuar o preenchimento das taxas com VBA 2 de 3. Essa é a segunda aula dessa parte de taxas para organizá-las!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a vigésima segunda aula da série. Vamos ver como fazer o preenchimento das taxas de forma automática. Essa aula é a segunda de três aulas, para fazer esse código.
Nesse post você vai aprender a lidar com:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo. Daremos seguimento ao preenchimento das taxas para que na próxima aula possamos finalizar essa parte das taxas. Observe a sub abaixo:
Sub preenchendo_taxas()
Dim m_valor_taxas(1 To 11)
m_taxas = Array("I.R.R.F.", "Taxa Liquidação", "Taxa Registro", "Taxa Termo/Opções", "Taxa A.N.A.", "Emolumentos", "Taxa Operacional", "Taxa Execução", "Taxa Custódia", "Impostos", "Taxa Outros")
cont = 1
For Each taxa In m_taxas
m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa)
cont = cont + 1
Next
nota = InputBox("Digite o número da Nota de Corretagem") + 0
n_ativos = WorksheetFunction.CountIf(Sheets("Notas").Range("C:C"), nota)
n_ativos_v = WorksheetFunction.CountIfs(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")
total_operacao = WorksheetFunction.SumIf(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("L:L"))
total_operacao_v = WorksheetFunction.SumIfs(Sheets("Notas").Range("L:L"), Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")
linha = Sheets("Notas").Range("A1048576").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 3) = nota Then
For j = 1 To 11
If j = 1 Then 'Se o j for 1, significa que é o I.R.R.F.
If Sheets("Notas").Cells(i, 7) = "V" Then
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao_v, 2)
Else
Sheets("Notas").Cells(i, 4 + j) = 0
End If
Else 'Significa que estamos nas outras taxas
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
End If
Next
End If
Next
End Sub
Explicação do código destacado acima (continuação do anterior):
(VALOR DA OPERAÇÃO * VALOR DA TAXA(j) ) / (TOTAL DE OPERAÇÃO DE VENDA), arredondada para duas casas decimais
Sheets("Notas").Cells(i, 14 + j) = Round(VALOR DA OPERAÇÃO * VALOR DA TAXA (j) / TOTAL DE OPERAÇÃO DESSA NOTA), arredondada para duas casas decimais.
Aqui estão alguns links de apoio para esse post Preenchimento das Taxas com VBA 2:
Nesta aula vamos finalizar o preenchimento das taxas com VBA 3 de 3. Essa é a útlima aula para finalizarmos o preenchimento das taxas!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a vigésima terceira aula da nossa série. Nela vamos ver como fazer o preenchimento das taxas automaticamente que começamos na aula anterior (22). Essa aula vai ser a terceira parte, de três aulas, para fechar esse código de automação para inclusão de notas.
Nesse post você vai aprender a lidar com:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo. Daremos seguimento ao preenchimento das taxas para que hoje possamos finalizar essa parte das taxas. Observe a sub abaixo:
Sub preenchendo_taxas()
Dim m_valor_taxas(1 To 12)
Dim m_valor_opcoes(1 To 7)
Dim m_valor_acum(1 To 12)
m_taxas = Array("I.R.R.F.", "Taxa Liquidação", "Taxa Registro", "Taxa Termo/Opções", "Taxa A.N.A.", "Emolumentos", "Taxa Operacional", "Taxa Execução", "Taxa Custódia", "Impostos", "Taxa Outros", "I.R.R.F. Day Trade")
m_opcoes = Array("Ação", "ETF", "FII", "BDR", "Opção", "Futuro", "Termo")
tipo_corretagem = InputBox("Cada tipo de ativo tem uma corretagem diferente? [Sim/Não]")
If tipo_corretagem <> "Sim" And tipo_corretagem <> "Não" Then Exit Sub
If tipo_corretagem = "Sim" Then
cont = 1
For Each opcao In m_opcoes
m_valor_opcoes(cont) = InputBox("Digite a corretagem para o tipo de ativo: " & opcao) + 0
cont = cont + 1
Next
End If
cont = 1
For Each taxa In m_taxas
m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa)
cont = cont + 1
Next
nota = InputBox("Digite o número da Nota de Corretagem") + 0
n_ativos = WorksheetFunction.CountIf(Sheets("Notas").Range("C:C"), nota) + WorksheetFunction.CountIf(Sheets("Notas Day Trade").Range("C:C"), nota)
n_ativos_v = WorksheetFunction.CountIfs(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")
n_ativos_v_dt = WorksheetFunction.CountIfs(Sheets("Notas Day Trade").Range("C:C"), nota, Sheets("Notas Day Trade").Range("G:G"), "V")
total_operacao = WorksheetFunction.SumIf(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("L:L")) + WorksheetFunction.SumIf(Sheets("Notas Day Trade").Range("C:C"), nota, Sheets("Notas Day Trade").Range("L:L"))
total_operacao_v = WorksheetFunction.SumIfs(Sheets("Notas").Range("L:L"), Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")
total_operacao_v_dt = WorksheetFunction.SumIfs(Sheets("Notas Day Trade").Range("L:L"), Sheets("Notas Day Trade").Range("C:C"), nota, Sheets("Notas Day Trade").Range("G:G"), "V")
'Movimentação das taxas na aba Notas
linha = Sheets("Notas").Range("A1048576").End(xlUp).Row
c = 1
c_v = 1
For i = 2 To linha
If Sheets("Notas").Cells(i, 3) = nota Then
If c = n_ativos Then
For j = 1 To 11
Sheets("Notas").Cells(i, 14 + j) = m_valor_taxas(j) - m_valor_acum(j)
Next
ElseIf c_v = n_ativos_v And Sheets("Notas").Cells(i, 7) = "V" Then
Sheets("Notas").Cells(i, 14 + 1) = m_valor_taxas(1) - m_valor_acum(1)
For j = 2 To 11
If j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas").Cells(i, 8) Then
Sheets("Notas").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas").Cells(i, 21) / m_valor_taxas(7), 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
Next
Else
For j = 1 To 11
If j = 1 Then 'Se o j for 1, significa que é o I.R.R.F.
If Sheets("Notas").Cells(i, 7) = "V" Then
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao_v, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Else
Sheets("Notas").Cells(i, 14 + j) = 0
End If
ElseIf j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas").Cells(i, 8) Then
Sheets("Notas").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas").Cells(i, 21) / m_valor_taxas(7), 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
Next
End If
c = c + 1
If Sheets("Notas").Cells(i, 7) = "V" Then c_v = c_v + 1
End If
Next
'Movimentação das taxas na aba Notas Day Trade
linha = Sheets("Notas Day Trade").Range("A1048576").End(xlUp).Row
c_v_dt = 1
For i = 2 To linha
If Sheets("Notas Day Trade").Cells(i, 3) = nota Then
If c = n_ativos Then
For j = 2 To 12
If j = 12 Then
Sheets("Notas Day Trade").Cells(i, 15) = m_valor_taxas(j) - m_valor_acum(j)
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = m_valor_taxas(j) - m_valor_acum(j)
End If
Next
ElseIf c_v_dt = n_ativos_v_dt And Sheets("Notas Day Trade").Cells(i, 7) = "V" Then
Sheets("Notas Day Trade").Cells(i, 15) = m_valor_taxas(12) - m_valor_acum(12)
For j = 2 To 11
If j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas Day Trade").Cells(i, 8) Then
Sheets("Notas Day Trade").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas Day Trade").Cells(i, 21) / m_valor_taxas(7), 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(Sheets("Notas Day Trade").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
Next
Else
For j = 2 To 12
If j = 12 Then 'Se o j for 12, significa que é o I.R.R.F. Day Trade
If Sheets("Notas Day Trade").Cells(i, 7) = "V" Then
Sheets("Notas Day Trade").Cells(i, 15) = Round(Sheets("Notas Day Trade").Cells(i, 12) * m_valor_taxas(j) / total_operacao_v_dt, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 15)
Else
Sheets("Notas Day Trade").Cells(i, 15) = 0
End If
ElseIf j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas Day Trade").Cells(i, 8) Then
Sheets("Notas Day Trade").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas Day Trade").Cells(i, 21) / m_valor_taxas(7), 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(Sheets("Notas Day Trade").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
Next
End If
c = c + 1
If Sheets("Notas Day Trade").Cells(i, 7) = "V" Then c_v_dt = c_v_dt + 1
End If
Next
End Sub
Explicação do código destacado acima (continuação do da aula anterior):
Quando colocarmos o valor de corretagem, daquele tipo específico de opção de ativo, iremos sair desse For e seguiremos.
Agora começaremos a tratar do “day trade”, no mesmo código:
Aqui estão alguns links de apoio para esse post de Preenchimento das Taxas com VBA 3:
Nesta aula vamos fazer alguns ajustes na macro de splits e inplits, pois temos um caso em que a macro pode dar um problema e vamos corrigir!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a vigésima quarta aula da série! Nela vamos dar prosseguimento a série, corrigindo o nosso código de como fazer o preenchimento das taxas automaticamente, que fizemos na última aula.
Por algumas sugestões que fizeram, depois de alguns testes, vimos que havia um caso em que essa macro poderia dar um problema.
Para lidar com a situação apontada acima, quando tivermos uma quantidade decimal em questão, vamos optar por utilizar o WorkSheetfunction.RoundDown, que é a função de arredondar para baixo.
Com isso, vamos poder corrigir a quantidade e ajustar o preço para que não tenhamos mais esse problema.
Nesse post, vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo. Daremos seguimento ao preenchimento das taxas para que hoje possamos finalizar essa parte das taxas. Observe a sub abaixo:
Sub split_inplit()
ativo = InputBox("Digite o Ticker do ativo a ser ajustado.")
If ativo = "" Then Exit Sub
proporcao = InputBox("Digite a proporção do evento. Ex: se for um split de 1 para 4, digite 1/4. Se for um inplit de 4 para 1, digite 4/1.")
If proporcao = "" Then Exit Sub
data_corte = CDate(InputBox("Digite a data de corte do evento. Ex: os acionistas só terão direito a esse evento se tiverem adquirido os ativos até a data 13/10/2020, então a data de corte é 13/10/2020"))
If data_corte = 0 Then Exit Sub
pos_barra = InStr(proporcao, "/")
de = CInt(Left(proporcao, pos_barra - 1))
para = CInt(Mid(proporcao, pos_barra + 1, 2))
linha = Sheets("Notas").Range("E1048576").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 9) = ativo And Sheets("Notas").Cells(i, 5) <= data_corte Then
valor_operacao = Sheets("Notas").Cells(i, 12)
Sheets("Notas").Cells(i, 10) = WorksheetFunction.RoundDown(Sheets("Notas").Cells(i, 10) * para / de, 0)
Sheets("Notas").Cells(i, 11) = valor_operacao / Sheets("Notas").Cells(i, 10)
End If
Next
linha = Sheets("Movimentações").Range("A1048576").End(xlUp).Row
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 5) = ativo And Sheets("Movimentações").Cells(i, 3) <= data_corte Then
Sheets("Movimentações").Cells(i, 7) = WorksheetFunction.RoundDown(Sheets("Movimentações").Cells(i, 7) * para / de, 0)
End If
Next
End Sub
Explicando as alterações no código acima (em destaque):
Aqui estão alguns links de apoio para esse post:
Nesta aula vamos te mostrar como criar o código para mudança de ticker de algum ativo, e claro, automatizar essa mudança para o usuário!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Essa é a vigésima quinta aula da série. Nela vamos criar um código a parte para fazer mudança de ticker! Isso ocorre quando temos algum ativo que mudou de nome. Portanto, nesse post, vamos lidar com:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo. Daremos seguimento ao preenchimento das taxas para que hoje possamos finalizar essa parte das taxas. Observe a sub abaixo:
Sub mudanca_ticker()
ativo = InputBox("Qual é o ticker a ser alterado?")
If ativo = "" Then Exit Sub
novo_ativo = InputBox("Qual é o ticker que vai substituir o antigo?")
If novo_ativo = "" Then Exit Sub
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 9) = ativo Then
Sheets("Notas").Cells(i, 9) = novo_ativo
End If
Next
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 5) = ativo Then
Sheets("Movimentações").Cells(i, 5) = novo_ativo
End If
Next
MsgBox "Terminou!"
End Sub
Explicação do código acima:
Botão para rodar o nosso código criado
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos, no nosso exemplo, escrevemos “Mudança de Ticker”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (mudanca_ticker). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nesta aula vamos te mostrar como fazer a automação da bonificação de ações dentro da nossa planilha para facilitar esse procedimento!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a vigésima sexta aula da série. Nas últimas aulas nós vimos como automatizar eventos de inplits e splits, e mudança de ticker, lembram?
Hoje veremos como criar automação da bonificação de ações! Isso ocorre quando temos algum ativo que é dado pela empresa para os clientes (provento dos ativos). Portanto, nesse post, vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Daremos seguimento ao preenchimento das taxas para que hoje possamos finalizar essa parte das taxas. Observe a sub abaixo para Automação da Bonificação de Ações:
Sub bonificacao()
ativo = InputBox("Qual é o ativo que está recebendo uma bonificação?")
If ativo = "" Then Exit Sub
proporcao = CDbl(InputBox("Qual é a proporção da bonificação? Ex: se você vai ganhar 1 ação a cada 10 que tiver, a proporção é de 0,10"))
If proporcao = 0 Then Exit Sub
custo_atribuido = InputBox("Qual é o custo atribuído para cada ação bonificada?") + 0
If custo_atribuido = 0 Then Exit Sub
data_ex = CDate(InputBox("Qual é a data ex da bonificação? Ex: se você só puder participar desse evento se possuir os ativos até o dia 13/04/2020, a data ex é 14/04/2020"))
If data_ex = 0 Then Exit Sub
data_disp = CDate(InputBox("Qual é a data que o ativo vai ficar disponível em sua conta? Pode deixar em branco se não souber"))
If data_disp = 0 Then data_disp = data_ex + 2
corretora = InputBox("Em qual corretora você possui esse ativo?")
If corretora = "" Then Exit Sub
compras = WorksheetFunction.SumIfs(Sheets("Notas").Range("J:J"), Sheets("Notas").Range("I:I"), ativo, Sheets("Notas").Range("G:G"), "C", Sheets("Notas").Range("E:E"), "<" & CLng(data_ex))
vendas = WorksheetFunction.SumIfs(Sheets("Notas").Range("J:J"), Sheets("Notas").Range("I:I"), ativo, Sheets("Notas").Range("G:G"), "V", Sheets("Notas").Range("E:E"), "<" & CLng(data_ex))
quantidade = compras - vendas
qtd_bonificada = WorksheetFunction.RoundDown(quantidade * proporcao, 0)
valor_provento = WorksheetFunction.RoundDown(custo_atribuido * qtd_bonificada, 2)
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
Sheets("Movimentações").Cells(linha + 1, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
Sheets("Movimentações").Cells(linha + 1, 2) = corretora
Sheets("Movimentações").Cells(linha + 1, 3) = data_ex
Sheets("Movimentações").Cells(linha + 1, 4) = data_disp
Sheets("Movimentações").Cells(linha + 1, 5) = ativo
Sheets("Movimentações").Cells(linha + 1, 6) = "Ação"
Sheets("Movimentações").Cells(linha + 1, 7) = qtd_bonificada
Sheets("Movimentações").Cells(linha + 1, 8) = -valor_provento
Sheets("Movimentações").Cells(linha + 2, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
Sheets("Movimentações").Cells(linha + 2, 2) = corretora
Sheets("Movimentações").Cells(linha + 2, 3) = data_ex
Sheets("Movimentações").Cells(linha + 2, 4) = data_disp
Sheets("Movimentações").Cells(linha + 2, 5) = ativo
Sheets("Movimentações").Cells(linha + 2, 6) = "Provento" 'Pode colocar Bonificação se preferir
'Sheets("Movimentações").Cells(linha + 2, 7) = qtd_bonificada, você pode tirar de comentário se quiser colocar a quantidade
Sheets("Movimentações").Cells(linha + 2, 8) = valor_provento
MsgBox "Terminou"
End Sub
Explicação do código acima (Automação da Bonificação de Ações):
Botão para rodar o nosso código criado
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos que esteja escrito nele.
No nosso exemplo, escrevemos “Bonificação”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (bonificacao). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nesta aula vamos te mostrar como fazer a automação para IPO e Subscrição para continuar com as automações da nossa planilha!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a vigésima sétima aula da série. Nas últimas aulas nós vimos como automatizar eventos de inplits e splits, e mudança de ticker, e bonificação, lembram?
Hoje veremos como criar automação para IPO e Subscrição! Isso ocorre quando uma empresa qualquer lista suas ações na bolsa de valores (Initial Public Offering – IPO). Portanto, nesse post, vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Vamos:
Daremos seguimento ao preenchimento da série com o código de Automação para IPO e Subscrição, para que possamos continuar as automatizações. Observe a sub abaixo:
Sub ipo_subscricao()
ativo = InputBox("Qual é o ativo em questão?")
If ativo = "" Then Exit Sub
tipo = InputBox("Qual é tipo do ativo?")
If tipo = "" Then Exit Sub
corretora = InputBox("Em qual corretora você vai receber esse ativo?")
If corretora = "" Then Exit Sub
data_mov = CDate(InputBox("Quando você vai receber esse ativo?"))
If data_mov = 0 Then Exit Sub
qtd = InputBox("Qual a quantidade desse ativo que você vai receber?") + 0
If qtd = 0 Then Exit Sub
custo_unitario = InputBox("Qual o custo unitário pago por esse ativo?") + 0
If custo_unitario = 0 Then Exit Sub
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
Sheets("Movimentações").Cells(linha + 1, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
Sheets("Movimentações").Cells(linha + 1, 2) = corretora
Sheets("Movimentações").Cells(linha + 1, 3) = data_mov
Sheets("Movimentações").Cells(linha + 1, 4) = data_mov
Sheets("Movimentações").Cells(linha + 1, 5) = ativo
Sheets("Movimentações").Cells(linha + 1, 6) = tipo
Sheets("Movimentações").Cells(linha + 1, 7) = qtd
Sheets("Movimentações").Cells(linha + 1, 8) = - WorksheetFunction.RoundDown(qtd * custo_unitario, 2)
Sheets("Movimentações").Cells(linha + 1, 9) = "IPO/Subscrição"
MsgBox "Terminou!"
End Sub
Explicação do código acima:
Botão para rodar o nosso código criado (Automação para IPO e Subscrição)
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos que esteja escrito nele. No nosso exemplo, escrevemos “IPO e Subscrição”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (ipo_subscricao). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
OBS: Ajeitando a Fórmula ÚNICO da nossa aba “Resumo”
Lembram que a nossa aba de resumo carrega todas as informações de Ticker da aba Notas?
Nesse caso de IPO/Subscrição temos o registro do ativo só na aba de Movimentações, e isso seria um problema para a nossa aba de Resumo. Precisamos, então, atualizar essa fórmula.
Como fazer?
Agora nós teremos uma nova fórmula chamada FILTRO, que é uma fórmula nova do Office 365, que vai literalmente fazer um filtro na coluna, via condições de filtro que queremos.
Ela é uma fórmula matricial, então temos que visualizar ela como tendo resultado de matriz para entendê-la melhor.
Além delas, vamos usar outra função do Office 365, a CLASSIFICAR, que vai classificar os resultados dessa matriz retornada pela função FILTRO, e a função ÚNICO, que vai retornar para a gente uma matriz com os valores únicos daquela matriz maior de valores.
Na aba Resumo, as fórmulas ficarão:
Célula A2:
=CLASSIFICAR(ÚNICO(FILTRO(TabelaMovimentacoes[Descrição];(TabelaMovimentacoes[Tipo]<>"Aporte")*(TabelaMovimentacoes[Tipo]<>"Retirada"))))
A fórmula acima vai classificar em ordem alfabética as ações únicas (sem duplicadas), filtradas da aba de Movimentações, mais especificamente da Tabela de Movimentações, onde na coluna Descrição for diferente de “Aporte” e na coluna Tipo for diferente de “Retirada”.
OBS: copiaremos essa fórmula para a célula A2 da aba de Cotações
Célula B2:
=PROCX(A2#;Opções!D:D;Opções!E:E)
A fórmula acima vai fazer uma procura na coluna D da aba de Opções, onde criaremos uma lista auxiliar dos tickers (ativos), e vai nos retornar na coluna E dessa mesma aba qual o correspondente para o encontrado na célula A2 da Aba Resumo (com a presença da #, se torna matricial)
Essa tabela auxiliar será criada na Aba de Opções, e terá a seguinte fórmula:
Opções!D2 (essa fórmula vai se estender para a célula E2):
=FILTRO(TabelaMovimentacoes[[Descrição]:[Tipo]];(TabelaMovimentacoes[Tipo]<>"Aporte")*(TabelaMovimentacoes[Tipo]<>"Provento")*(TabelaMovimentacoes[Tipo]<>"Retirada")*(TabelaMovimentacoes[Tipo]<>"Resultado Ação")*(TabelaMovimentacoes[Tipo]<>"Resultado Ação Day Trade")*(TabelaMovimentacoes[Tipo]<>"I.R.R.F. Ação")*(TabelaMovimentacoes[Tipo]<>"I.R.R.F. Ação Day Trade"))
A fórmula acima vai fazer um filtro nas colunas Descrição e Tipo da TabelaMovimentacoes, onde vai considerar somente os Tipos diferentes de “Aporte”, “Retirada”, “Resultado Ação”, “Resultado Ação Day Trade”, “I.R.R.F. Ação” e “I.R.R.F. Ação Day Trade”
Nesta aula vamos organizar a aba movimentações para automatizá-la e deixar sempre o usuário com o mínimo de trabalho manual!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a vigésima oitava aula da série. Hoje veremos como fazer mais uma automação com macros no VBA para automatizar a aba movimentações, mais especificamente falando de como organizá-la! Vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaMovimentações, nos módulos, como mostrado no vídeo.
Daremos seguimento ao preenchimento da série com a criação do código de organização da tabela de movimentações, para que possamos continuar as automatizações.
Observe a sub abaixo:
Sub organiza_movimentacoes()
With Worksheets("Movimentações").ListObjects("TabelaMovimentacoes").Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("TabelaMovimentacoes[[#All],[Data Pregão]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 1) = "" Then
Sheets("Movimentações").Cells(i, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
End If
Next
Sheets("Movimentações").Columns.AutoFit
End Sub
Explicação do código acima:
Botão para rodar o nosso código criado (Organizar a Aba Movimentações)
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos. No nosso exemplo, escrevemos “Organizar Tab.”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (organiza_movimentacoes). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nessa aula nós vamos estar incluindo aportes retiradas e proventos com uma nova macro para automatizar mais essa parte da nossa tabela!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a vigésima nona aula da série. Hoje veremos como fazer mais uma automação com macros no VBA para automatizar a aba movimentações, mais especificamente falando de como incluir aportes, retiradas e proventos na nossa tabela de Movimentações!
Nela, vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaMovimentações, nos módulos, como mostrado no vídeo.
Daremos seguimento ao preenchimento da série com a inclusão de aportes, retiradas e proventos, para que possamos continuar as automatizações da nossa aba de Movimentações.
Observe a sub abaixo:
Sub aporte_retirada_provento()
tipo = InputBox("Qual é o tipo da movimentação?" & Chr(10) & Chr(10) & "Aporte" & Chr(10) & "Retirada" & Chr(10) & "Provento")
If tipo = "" Then Exit Sub
tipo = WorksheetFunction.Proper(tipo)
If tipo <> "Aporte" And tipo <> "Retirada" And tipo <> "Provento" Then Exit Sub
corretora = InputBox("Qual a corretora utilizada?")
If corretora = "" Then Exit Sub
data_mov = CDate(InputBox("Qual a data da movimentação?"))
If data_mov = 0 Then Exit Sub
descricao = InputBox("Qual é a descrição da movimentação (se for provento é o nome do ativo e se for aporte/retirada é aporte/retirada nome da corretora)?")
If descricao = "" Then Exit Sub
valor = CDbl(InputBox("Qual o valor da movimentação?"))
If valor = 0 Then Exit Sub
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
Sheets("Movimentações").Cells(linha, 2) = corretora
Sheets("Movimentações").Cells(linha, 3) = data_mov
Sheets("Movimentações").Cells(linha, 4) = data_mov
Sheets("Movimentações").Cells(linha, 5) = descricao
Sheets("Movimentações").Cells(linha, 6) = tipo
Sheets("Movimentações").Cells(linha, 8) = valor
Call organiza_movimentacoes
End Sub
Explicação do código acima:
Botão para rodar o nosso código criado (Incluindo Aportes Retiradas e Proventos)
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos que esteja escrito nele. No nosso exemplo, escrevemos “Aporte, Retirada ou Provento”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (aporte_retirada_provento). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nessa aula nós criar uma macro para excluir IDs e Notas de forma automática, assim o usuário só precisa informar o que deseja excluir!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima aula da série. Hoje veremos como fazer mais uma automação com macros no VBA para excluir IDs e Notas automaticamente, se tratando das abas de Notas, Movimentações e Notas Day Trade!
Nela, vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Daremos seguimento à série com a exclusão de notas ou IDs das nossas abas de Notas, Notas Day Trade e Movimentações, como citado anteriormente.
Observe a sub abaixo para excluir IDs e Notas:
Sub excluir_nota_id()
Dim m_id() As Variant
tipo = LCase(InputBox("Você quer excluir uma Nota ou um ID? [Nota/ID]"))
If tipo = "" Then Exit Sub
numero = InputBox("Qual é o número da Nota/ID a ser excluída(o)?")
If numero = "" Then Exit Sub
numero = numero + 0
If tipo = "nota" Then
'Aba Notas
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
linha_dt = Sheets("Notas Day Trade").Range("A1000000").End(xlUp).Row
ReDim m_id(1 To linha + linha_dt - 2)
c = 1
For i = 2 To linha
If Sheets("Notas").Cells(i, 3) = numero Then
m_id(c) = Sheets("Notas").Cells(i, 2)
c = c + 1
Sheets("Notas").Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
End If
Next
'Aba Notas Day Trade
For i = 2 To linha_dt
If Sheets("Notas Day Trade").Cells(i, 3) = numero Then
m_id(c) = Sheets("Notas Day Trade").Cells(i, 2)
c = c + 1
Sheets("Notas Day Trade").Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
End If
Next
c = c - 1 'Número de linhas preenchidas na minha m_id
'Aba Movimentações
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
For i = 4 To linha
For j = 1 To c
If Sheets("Movimentações").Cells(i, 1) = m_id(j) Then
Sheets("Movimentações").Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
Exit For
End If
Next j
Next i
ElseIf tipo = "id" Then
'Aba Notas
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 2) = numero Then
Sheets("Notas").Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
End If
Next
'Aba Notas Day Trade
linha = Sheets("Notas Day Trade").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas Day Trade").Cells(i, 2) = numero Then
Sheets("Notas Day Trade").Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
End If
Next
'Aba Movimentações
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 1) = numero Then
Sheets("Movimentações").Range(i & ":" & i).Delete shift:=xlUp
i = i - 1
End If
Next
End If
MsgBox ("Finalizado!")
End Sub
Explicação do código acima:
Botão para rodar o nosso código criado
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos. No nosso exemplo, escrevemos “Excluir Nota/ID”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (excluir_nota_id). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nessa aula nós vamos estar incluindo notas de compra nas movimentações para continuar nossa automação da planilha de bolsa de valores!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima primeira aula da série. Hoje veremos como fazer mais uma automação no Excel. No caso incluindo notas de compra nas movimentações, se tratando das abas de Notas, Movimentações e Notas Day Trade!
Nela, vamos:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Daremos seguimento à série com a exclusão de notas ou IDs das nossas abas de Notas, Notas Day Trade e Movimentações, como citado anteriormente.
Observe a sub abaixo que vamos estar incluindo notas de compra:
Sub incluir_nota()
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 1) = "" Then
If Sheets("Notas").Cells(i, 7) = "C" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -Sheets("Notas").Cells(i, 14)
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
ElseIf Sheets("Notas").Cells(i, 7) = "V" Then
End If
End If
Next i
Sheets("Movimentações").Activate
Call organiza_movimentacoes
Sheets("Notas").Activate
End Sub
Explicação do código acima:
Botão para rodar o nosso código criado
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos. No nosso exemplo, escrevemos “Incluir Nota”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (incluir_nota). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Nessa aula nós vamos estar incluindo notas de venda nas movimentações para continuar nossa automação da planilha de bolsa de valores!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima segunda aula da série. Hoje veremos como fazer mais uma automação no Excel. No caso incluindo notas de venda nas Movimentações!
Nela, vamos ver:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Daremos seguimento à série com a inclusão de notas na aba Movimentações, como citado anteriormente.
Incluindo notas de venda na sub abaixo:
Sub incluir_nota()
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 1) = "" Then
If Sheets("Notas").Cells(i, 7) = "C" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -Sheets("Notas").Cells(i, 14)
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
ElseIf Sheets("Notas").Cells(i, 7) = "V" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
'Preenchendo a linha do Valor Principal
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = -Sheets("Notas").Cells(i, 10) 'Quantidade
ativo = Sheets("Notas").Cells(i, 9)
tipo = Sheets("Notas").Cells(i, 8)
quantidade = Sheets("Notas").Cells(i, 10)
custo_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("H:H"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo)
quantidade_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("G:G"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo) + Sheets("Notas").Cells(i, 10)
custo_medio = -custo_total / quantidade_total
valor_principal = Round(custo_medio * quantidade, 2)
Sheets("Movimentações").Cells(linha_mov, 8) = valor_principal
'Preenchendo a linha do Resultado da Operação
Sheets("Movimentações").Cells(linha_mov + 1, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 1, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 1, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 1, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 1, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 1, 6) = "Resultado " & Sheets("Notas").Cells(i, 8) 'Tipo
valor_venda = Sheets("Notas").Cells(i, 14)
resultado = valor_venda - valor_principal
Sheets("Movimentações").Cells(linha_mov + 1, 8) = resultado
'Preenchendo a linha do I.R.R.F.
Sheets("Movimentações").Cells(linha_mov + 2, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 2, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 2, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 2, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 2, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 2, 6) = "I.R.R.F. " & Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov + 2, 8) = -Sheets("Notas").Cells(i, 15)
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
End If
End If
Next i
Sheets("Movimentações").Activate
Call organiza_movimentacoes
Sheets("Notas").Activate
End Sub
Explicação do código acima (Incluindo Notas de Venda):
Nessa aula nós vamos te mostrar como incluir notas day trade nas movimentações para deixar nossa planilha ainda mais completa!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima terceira aula da série. Hoje veremos como fazer mais uma automação no Excel. No caso de como incluir notas day trade nas Movimentações! Vamos continuar o código elaborado na aula anterior (32), lidando com automação Excel. (LINK)
Nela, vamos ver:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Daremos continuidade à série incluindo Notas Day Trade na aba Movimentações.
Observe a sub abaixo:
Sub incluir_nota()
Set aba_inicial = ThisWorkbook.ActiveSheet
'Incluindo as operações da aba Notas
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 1) = "" Then
If Sheets("Notas").Cells(i, 7) = "C" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -Sheets("Notas").Cells(i, 14)
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
ElseIf Sheets("Notas").Cells(i, 7) = "V" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
'Preenchendo a linha do Valor Principal
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = -Sheets("Notas").Cells(i, 10) 'Quantidade
ativo = Sheets("Notas").Cells(i, 9)
tipo = Sheets("Notas").Cells(i, 8)
quantidade = Sheets("Notas").Cells(i, 10)
custo_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("H:H"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo)
quantidade_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("G:G"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo) + Sheets("Notas").Cells(i, 10)
custo_medio = -custo_total / quantidade_total
valor_principal = Round(custo_medio * quantidade, 2)
Sheets("Movimentações").Cells(linha_mov, 8) = valor_principal
'Preenchendo a linha do Resultado da Operação
Sheets("Movimentações").Cells(linha_mov + 1, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 1, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 1, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 1, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 1, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 1, 6) = "Resultado " & Sheets("Notas").Cells(i, 8) 'Tipo
valor_venda = Sheets("Notas").Cells(i, 14)
resultado = valor_venda - valor_principal
Sheets("Movimentações").Cells(linha_mov + 1, 8) = resultado
'Preencendo a linha do I.R.R.F.
Sheets("Movimentações").Cells(linha_mov + 2, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 2, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 2, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 2, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 2, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 2, 6) = "I.R.R.F. " & Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov + 2, 8) = -Sheets("Notas").Cells(i, 15)
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
End If
End If
Next i
'Incluindo as operações da aba Notas Day Trade
linha = Sheets("Notas Day Trade").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas Day Trade").Cells(i, 1) = "" Then
If Sheets("Notas Day Trade").Cells(i, 7) = "C" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas Day Trade").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas Day Trade").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas Day Trade").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -Sheets("Notas Day Trade").Cells(i, 14)
Sheets("Notas Day Trade").Cells(i, 1) = "x"
Sheets("Notas Day Trade").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
ElseIf Sheets("Notas Day Trade").Cells(i, 7) = "V" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
'Preenchendo a linha do Valor Principal
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas Day Trade").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas Day Trade").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = -Sheets("Notas Day Trade").Cells(i, 10) 'Quantidade
ativo = Sheets("Notas Day Trade").Cells(i, 9)
quantidade = Sheets("Notas Day Trade").Cells(i, 10)
valor_principal = WorksheetFunction.SumIfs(Sheets("Notas Day Trade").Range("N:N"), Sheets("Notas Day Trade").Range("G:G"), "C", Sheets("Notas Day Trade").Range("I:I"), ativo, Sheets("Notas Day Trade").Range("J:J"), quantidade)
Sheets("Movimentações").Cells(linha_mov, 8) = valor_principal
'Preenchendo a linha do Resultado da Operação
Sheets("Movimentações").Cells(linha_mov + 1, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 1, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 1, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 1, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 1, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 1, 6) = "Resultado " & Sheets("Notas Day Trade").Cells(i, 8) & " Day Trade" 'Tipo
valor_venda = Sheets("Notas Day Trade").Cells(i, 14)
resultado = valor_venda - valor_principal
Sheets("Movimentações").Cells(linha_mov + 1, 8) = resultado
'Preenchendo a linha do I.R.R.F.
Sheets("Movimentações").Cells(linha_mov + 2, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 2, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 2, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 2, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 2, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 2, 6) = "I.R.R.F. " & Sheets("Notas Day Trade").Cells(i, 8) & " Day Trade" 'Tipo
Sheets("Movimentações").Cells(linha_mov + 2, 8) = -Sheets("Notas Day Trade").Cells(i, 15)
Sheets("Notas Day Trade").Cells(i, 1) = "x"
Sheets("Notas Day Trade").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
End If
End If
Next i
Sheets("Movimentações").Activate
Call organiza_movimentacoes
aba_inicial.Activate
End Sub
Explicação do código em negrito acima:
Nessa aula nós vamos te mostrar algumas novidades na macro de taxas e mudança de ticker para poder melhorá-las e facilitar o uso delas!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Seguimos aqui na maior série de aulas da Hashtag Treinamentos, hoje estamos na trigésima quarta aula, que também está no nosso curso completo de VBA!
Nessa aula veremos como fazer alguns ajustes em dois códigos que finalizamos em aulas anteriores. Sendo mais preciso, mas macros de taxa e mudança de ticker.
Nela, vamos ver:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
A seguir teremos ajuste de macros de taxa e mudança de ticker, conforme mencionado no começo.
Observe as subs abaixo (Novidades na Macro de Taxas):
Sub preenchendo_taxas()
Dim m_valor_taxas(1 To 12)
Dim m_valor_opcoes(1 To 7)
Dim m_valor_acum(1 To 12)
m_taxas = Array("I.R.R.F.", "Taxa Liquidação", "Taxa Registro", "Taxa Termo/Opções", "Taxa A.N.A.", "Emolumentos", "Taxa Operacional", "Taxa Execução", "Taxa Custódia", "Impostos", "Taxa Outros", "I.R.R.F. Day Trade")
m_opcoes = Array("Ação", "ETF", "FII", "BDR", "Opção", "Futuro", "Termo")
nota = InputBox("Digite o número da Nota de Corretagem")
If nota = "" Then Exit Sub
nota = nota + 0
tipo_corretagem = MsgBox("Cada tipo de ativo tem uma corretagem diferente?", vbYesNo)
If tipo_corretagem <> 6 And tipo_corretagem <> 7 Then Exit Sub
'tipo_corretagem = LCase(InputBox("Cada tipo de ativo tem uma corretagem diferente? [Sim/Não]"))
'If tipo_corretagem <> "sim" And tipo_corretagem <> "não" Then Exit Sub
If tipo_corretagem = 6 Then
cont = 1
For Each opcao In m_opcoes
m_valor_opcoes(cont) = InputBox("Digite a corretagem para o tipo de ativo: " & opcao) + 0
cont = cont + 1
Next
End If
cont = 1
For Each taxa In m_taxas
m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa)
cont = cont + 1
Next
n_ativos = WorksheetFunction.CountIf(Sheets("Notas").Range("C:C"), nota) + WorksheetFunction.CountIf(Sheets("Notas Day Trade").Range("C:C"), nota)
n_ativos_v = WorksheetFunction.CountIfs(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")
n_ativos_v_dt = WorksheetFunction.CountIfs(Sheets("Notas Day Trade").Range("C:C"), nota, Sheets("Notas Day Trade").Range("G:G"), "V")
total_operacao = WorksheetFunction.SumIf(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("L:L")) + WorksheetFunction.SumIf(Sheets("Notas Day Trade").Range("C:C"), nota, Sheets("Notas Day Trade").Range("L:L"))
total_operacao_v = WorksheetFunction.SumIfs(Sheets("Notas").Range("L:L"), Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")
total_operacao_v_dt = WorksheetFunction.SumIfs(Sheets("Notas Day Trade").Range("L:L"), Sheets("Notas Day Trade").Range("C:C"), nota, Sheets("Notas Day Trade").Range("G:G"), "V")
'Movimentação das taxas na aba Notas
linha = Sheets("Notas").Range("A1048576").End(xlUp).Row
c = 1
c_v = 1
For i = 2 To linha
If Sheets("Notas").Cells(i, 3) = nota Then
If c = n_ativos Then
For j = 1 To 11
Sheets("Notas").Cells(i, 14 + j) = m_valor_taxas(j) - m_valor_acum(j)
Next
ElseIf c_v = n_ativos_v And Sheets("Notas").Cells(i, 7) = "V" Then
Sheets("Notas").Cells(i, 14 + 1) = m_valor_taxas(1) - m_valor_acum(1)
For j = 2 To 11
If j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas").Cells(i, 8) Then
Sheets("Notas").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
If CDbl(m_valor_taxas(7)) = 0 Then
Sheets("Notas").Cells(i, 14 + j) = 0
Else
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas").Cells(i, 21) / m_valor_taxas(7), 2)
End If
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
Next
Else
For j = 1 To 11
If j = 1 Then 'Se o j for 1, significa que é o I.R.R.F.
If Sheets("Notas").Cells(i, 7) = "V" Then
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao_v, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Else
Sheets("Notas").Cells(i, 14 + j) = 0
End If
ElseIf j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas").Cells(i, 8) Then
Sheets("Notas").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
If CDbl(m_valor_taxas(7)) = 0 Then
Sheets("Notas").Cells(i, 14 + j) = 0
Else
Sheets("Notas").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas").Cells(i, 21) / m_valor_taxas(7), 2)
End If
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas").Cells(i, 14 + j)
End If
Next
End If
c = c + 1
If Sheets("Notas").Cells(i, 7) = "V" Then c_v = c_v + 1
End If
Next
'Movimentação das taxas na aba Notas Day Trade
linha = Sheets("Notas Day Trade").Range("A1048576").End(xlUp).Row
c_v_dt = 1
For i = 2 To linha
If Sheets("Notas Day Trade").Cells(i, 3) = nota Then
If c = n_ativos Then
For j = 2 To 12
If j = 12 Then
Sheets("Notas Day Trade").Cells(i, 15) = m_valor_taxas(j) - m_valor_acum(j)
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = m_valor_taxas(j) - m_valor_acum(j)
End If
Next
ElseIf c_v_dt = n_ativos_v_dt And Sheets("Notas Day Trade").Cells(i, 7) = "V" Then
Sheets("Notas Day Trade").Cells(i, 15) = m_valor_taxas(12) - m_valor_acum(12)
For j = 2 To 11
If j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas Day Trade").Cells(i, 8) Then
Sheets("Notas Day Trade").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
If CDbl(m_valor_taxas(7)) = 0 Then
Sheets("Notas Day Trade").Cells(i, 14 + j) = 0
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas Day Trade").Cells(i, 21) / m_valor_taxas(7), 2)
End If
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(Sheets("Notas Day Trade").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
Next
Else
For j = 2 To 12
If j = 12 Then 'Se o j for 12, significa que é o I.R.R.F. Day Trade
If Sheets("Notas Day Trade").Cells(i, 7) = "V" Then
Sheets("Notas Day Trade").Cells(i, 15) = Round(Sheets("Notas Day Trade").Cells(i, 12) * m_valor_taxas(j) / total_operacao_v_dt, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 15)
Else
Sheets("Notas Day Trade").Cells(i, 15) = 0
End If
ElseIf j = 7 Then 'Se o j for 7, significa que é a taxa operacional/corretagem
If tipo_corretagem = "Sim" Then
For k = 0 To 6 'Percorrendo a minha matriz de opções
If m_opcoes(k) = Sheets("Notas Day Trade").Cells(i, 8) Then
Sheets("Notas Day Trade").Cells(i, 14 + j) = m_valor_opcoes(k + 1)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Exit For
End If
Next
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) / n_ativos, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
ElseIf j = 10 Then 'Se o j for 10, significa que é o ISS
If CDbl(m_valor_taxas(7)) = 0 Then
Sheets("Notas Day Trade").Cells(i, 14 + j) = 0
Else
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(m_valor_taxas(j) * Sheets("Notas Day Trade").Cells(i, 21) / m_valor_taxas(7), 2)
End If
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
Else 'Significa que estamos nas outras taxas
Sheets("Notas Day Trade").Cells(i, 14 + j) = Round(Sheets("Notas Day Trade").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
m_valor_acum(j) = m_valor_acum(j) + Sheets("Notas Day Trade").Cells(i, 14 + j)
End If
Next
End If
c = c + 1
If Sheets("Notas Day Trade").Cells(i, 7) = "V" Then c_v_dt = c_v_dt + 1
End If
Next
End Sub
Explicações sobre a macro “preenchendo_taxas”:
Sub mudanca_ticker()
ativo = InputBox("Qual é o ticker a ser alterado?")
If ativo = "" Then Exit Sub
novo_ativo = InputBox("Qual é o ticker que vai substituir o antigo?")
If novo_ativo = "" Then Exit Sub
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 9) = ativo Then
Sheets("Notas").Cells(i, 9) = novo_ativo
End If
Next
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 5) = ativo Then
Sheets("Movimentações").Cells(i, 5) = novo_ativo
If Sheets("Movimentações").Cells(i, 9) = "" Then
Sheets("Movimentações").Cells(i, 9) = "De: " & ativo & " Para: " & novo_ativo & " Data: " & Date
Else
Sheets("Movimentações").Cells(i, 9) = Sheets("Movimentações").Cells(i, 9) & " - De: " & ativo & " Para: " & novo_ativo & " Data: " & Date
End If
End If
Next
MsgBox "Terminou!"
End Sub
Comentários sobre a macro “mudança_ticker”:
Nessa aula nós vamos te mostrar o que é e como incluir as notas de venda a descoberto nas movimentações que já temos na tabela!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima quinta aula da série. Hoje veremos como fazer mais ajustes de macros VBA. No caso de como incluir notas de vendas a descoberto nas movimentações!
Nela, vamos ver:
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Daremos continuidade à série incluindo notas com situação de vendas a descoberto na aba Movimentações.
Observe a sub abaixo:
Sub incluir_nota()
Set aba_inicial = ThisWorkbook.ActiveSheet
'Incluindo as operações da aba Notas
linha = Sheets("Notas").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas").Cells(i, 1) = "" Then
ativo = Sheets("Notas").Cells(i, 9)
tipo = Sheets("Notas").Cells(i, 8)
qtd_ativo = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("G:G"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo)
If (Sheets("Notas").Cells(i, 7) = "C" And qtd_ativo >= 0) Or (Sheets("Notas").Cells(i, 7) = "V" And qtd_ativo <= 0) Then 'Iniciando/Reforçando uma operação
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
If Sheets("Notas").Cells(i, 7) = "C" Then
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -Sheets("Notas").Cells(i, 14)
Else
Sheets("Movimentações").Cells(linha_mov, 7) = -Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = Sheets("Notas").Cells(i, 14)
'Preenchendo a linha do I.R.R.F.
Sheets("Movimentações").Cells(linha_mov + 1, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 1, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 1, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 1, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 1, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 1, 6) = "I.R.R.F. " & Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov + 1, 8) = -Sheets("Notas").Cells(i, 15)
End If
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
ElseIf (Sheets("Notas").Cells(i, 7) = "C" And qtd_ativo < 0) Or (Sheets("Notas").Cells(i, 7) = "V" And qtd_ativo > 0) Then 'Finalizando uma operação
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
'Preenchendo a linha do Valor Principal
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas").Cells(i, 8) 'Tipo
quantidade = Sheets("Notas").Cells(i, 10)
custo_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("H:H"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo)
'se for uma compra custo_total é negativo
'se for uma venda custo_total é positivo
quantidade_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("G:G"), Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("F:F"), tipo)
'se for uma compra quantidade_total é positivo
'se for uma venda quantidade_total é negativo
custo_medio = -custo_total / quantidade_total
'se for uma compra custo_medio positivo
'se for uma venda custo_medio positivo
valor_principal = Round(custo_medio * quantidade, 2)
If Sheets("Notas").Cells(i, 7) = "C" Then
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -valor_principal
Else
Sheets("Movimentações").Cells(linha_mov, 7) = -Sheets("Notas").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = valor_principal
End If
'Preenchendo a linha do Resultado da Operação
Sheets("Movimentações").Cells(linha_mov + 1, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 1, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 1, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 1, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 1, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 1, 6) = "Resultado " & Sheets("Notas").Cells(i, 8) 'Tipo
valor_liquido = Sheets("Notas").Cells(i, 14)
resultado = valor_liquido - valor_principal
If Sheets("Notas").Cells(i, 7) = "C" Then
Sheets("Movimentações").Cells(linha_mov + 1, 8) = -resultado
Else
Sheets("Movimentações").Cells(linha_mov + 1, 8) = resultado
'Preenchendo a linha do I.R.R.F.
Sheets("Movimentações").Cells(linha_mov + 2, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 2, 2) = Sheets("Notas").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 2, 3) = Sheets("Notas").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 2, 4) = Sheets("Notas").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 2, 5) = Sheets("Notas").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 2, 6) = "I.R.R.F. " & Sheets("Notas").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov + 2, 8) = -Sheets("Notas").Cells(i, 15)
End If
Sheets("Notas").Cells(i, 1) = "x"
Sheets("Notas").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
End If
End If
Next i
'Incluindo as operações da aba Notas Day Trade
linha = Sheets("Notas Day Trade").Range("A1000000").End(xlUp).Row
For i = 2 To linha
If Sheets("Notas Day Trade").Cells(i, 1) = "" Then
If Sheets("Notas Day Trade").Cells(i, 7) = "C" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas Day Trade").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas Day Trade").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = Sheets("Notas Day Trade").Cells(i, 10) 'Quantidade
Sheets("Movimentações").Cells(linha_mov, 8) = -Sheets("Notas Day Trade").Cells(i, 14)
Sheets("Notas Day Trade").Cells(i, 1) = "x"
Sheets("Notas Day Trade").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
ElseIf Sheets("Notas Day Trade").Cells(i, 7) = "V" Then
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
'Preenchendo a linha do Valor Principal
Sheets("Movimentações").Cells(linha_mov, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas Day Trade").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha_mov, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov, 6) = Sheets("Notas Day Trade").Cells(i, 8) 'Tipo
Sheets("Movimentações").Cells(linha_mov, 7) = -Sheets("Notas Day Trade").Cells(i, 10) 'Quantidade
ativo = Sheets("Notas Day Trade").Cells(i, 9)
quantidade = Sheets("Notas Day Trade").Cells(i, 10)
valor_principal = WorksheetFunction.SumIfs(Sheets("Notas Day Trade").Range("N:N"), Sheets("Notas Day Trade").Range("G:G"), "C", Sheets("Notas Day Trade").Range("I:I"), ativo, Sheets("Notas Day Trade").Range("J:J"), quantidade)
Sheets("Movimentações").Cells(linha_mov, 8) = valor_principal
'Preenchendo a linha do Resultado da Operação
Sheets("Movimentações").Cells(linha_mov + 1, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 1, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 1, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 1, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 1, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 1, 6) = "Resultado " & Sheets("Notas Day Trade").Cells(i, 8) & " Day Trade" 'Tipo
valor_venda = Sheets("Notas Day Trade").Cells(i, 14)
resultado = valor_venda - valor_principal
Sheets("Movimentações").Cells(linha_mov + 1, 8) = resultado
'Preenchendo a linha do I.R.R.F.
Sheets("Movimentações").Cells(linha_mov + 2, 1) = Sheets("Movimentações").Cells(linha_mov, 1) 'ID
Sheets("Movimentações").Cells(linha_mov + 2, 2) = Sheets("Notas Day Trade").Cells(i, 4) 'Corretora
Sheets("Movimentações").Cells(linha_mov + 2, 3) = Sheets("Notas Day Trade").Cells(i, 5) 'Data pregão/movimentação
Sheets("Movimentações").Cells(linha_mov + 2, 4) = Sheets("Notas Day Trade").Cells(i, 6) 'Data liquidação
Sheets("Movimentações").Cells(linha_mov + 2, 5) = Sheets("Notas Day Trade").Cells(i, 9) 'Ativo/descrição
Sheets("Movimentações").Cells(linha_mov + 2, 6) = "I.R.R.F. " & Sheets("Notas Day Trade").Cells(i, 8) & " Day Trade" 'Tipo
Sheets("Movimentações").Cells(linha_mov + 2, 8) = -Sheets("Notas Day Trade").Cells(i, 15)
Sheets("Notas Day Trade").Cells(i, 1) = "x"
Sheets("Notas Day Trade").Cells(i, 2) = Sheets("Movimentações").Cells(linha_mov, 1)
End If
End If
Next i
Sheets("Movimentações").Activate
Call organiza_movimentacoes
aba_inicial.Activate
End Sub
Explicação sobre os destaques feitos acima:
Nessa aula nós vamos iniciar o controle de imposto de renda parte 1 para criar mais essa aba na nossa planilha de controles!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima sexta aula da série. E nessa aula nós vamos dar início ao controle de imposto de renda na nossa planilha!
Para isso vamos utilizar algumas funções que você já deve conhecer dentro do Excel para ajustar nossa tabela como:
Vamos utilizar essas funções e a formatação de células para preencher as colunas de:
Assim, vamos dar início a construção da nossa planilha/aba de imposto de renda para ter todas as informações necessárias!
Criando a aba de Imposto de Renda
Inserindo fórmulas na primeira linha de cada coluna
Na segunda linha, que de fato vamos colocar a fórmula, nesse caso:
=DATAM(A2;1)
Essa fórmula vai simplesmente adicionar 1 mês à data presente na célula A2, que nesse caso é 01/01/2020 e resultará em 01/02/2020.
=
SOMASES(TabelaNotas[Valor Operação];TabelaNotas[C/V];"V";TabelaNotas[Tipo];"<>FII";TabelaNotas[Data Pregão];">="&[@Mês];TabelaNotas[Data Pregão];"<="&FIMMÊS([@Mês];0))
+
SOMASES(TabelaNotasDayTrade[Valor Operação];TabelaNotasDayTrade[C/V];"V";TabelaNotasDayTrade[Tipo];"<>FII";TabelaNotasDayTrade[Data Pregão];">="&[@Mês];TabelaNotasDayTrade[Data Pregão];"<="&FIMMÊS([@Mês];0))
Essa fórmula vai fazer a soma do valor de operação (coluna da tabela Notas, da aba Notas), se for uma Venda, se o tipo for diferente de “FII”, se a data do pregão for maior ou igual à data da mesma linha da tabela (Imposto de Renda), se a data for menor ou igual ao final do mês da data da linha em questão (dada pela fórmula FIMMÊS).
Essa soma vai ser depois somada (sim) com outra soma, que vai fazer a mesma lógica, mas agora olhando para a aba de Notas Day Trade.
Somando os valores da aba Notas e Notas Day Trade teremos as nossas alienações.
Formatar a coluna como Moeda.
OBS: Se o resultado da alienação for menor que R$ 20.000,00, teremos isenção de imposto de renda.
=SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Ação";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0))
Essa fórmula vai fazer a soma dos valores da coluna de Valor da Tabela de Movimentações (aba Movimentações), se o tipo preenchido na Tabela de Movimentações for “Resultado Ação”, e se a data do pregão da Tabela de Movimentações for maior ou igual à data da linha em questão da aba de Imposto de Renda, e se a data do pregão também for menor ou igual à data da linha em questão da aba de Imposto de Renda, através da fórmula FIMMÊS.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado ETF";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0))
Essa fórmula vai fazer a soma com a lógica de forma parecida com a fórmula anterior, mas dessa vez somaremos os tipos de “Resultado ETF”.
=SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado BDR";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0))
Essa fórmula vai fazer a soma com a lógica de forma parecida com a fórmula anterior, mas dessa vez somaremos os tipos de “Resultado BDR”.
=SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];{"Resultado Futuro";"Resultado Termo";"Resultado Opções"};TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0)))
Essa fórmula vai fazer a soma com a lógica de forma parecida com a fórmula anterior, mas dessa vez somaremos os tipos de “Resultado Futuro”, “Resultado Termo” e “Resultado Opções”.
Aqui repare que fizemos uma soma no começo, porque a nossa fórmula resulta numa matriz de três números individuais. Com a fórmula de soma, acabamos por somar esses 3 números (que antes seriam distribuídos em 3 células diferentes) e colocar em uma única célula.=SE([@Alienações]<20000;SE([@[Resultado Ação]]>0;[@[Resultado Ação]];0);0)
Essa fórmula vai fazer a verificação do valor presenta na coluna de Alienações e na linha em questão, da tabela de Imposto de Renda.
Se o valor presente nessa coluna e linha específica for menor do que 20000, vamos avaliar a coluna de Resultado Ação.
Se a coluna de Resultado Ação, naquela mesma linha em questão, tiver valor positivo, aquela quantia será um lucro isento.
Caso contrário (não for positivo), o lucro isento será 0 (não haverá lucro isento).
Se o valor de Alienações for maior ou igual a 20000, o lucro isento também será igual a 0 (não haverá lucro isento).
=SOMA(Tabela5[@[Resultado Ação]:[Resultado Futuro, Termo e Opções]])-[@[Lucro Isento]]
Esse valor representa a soma de todos os resultados, da coluna B até a coluna F, ou seja, de Resultado Ação até Resultado Futuro, Termo e Opções, e subtrai o lucro isento, caso haja. Assim, teremos o valor do nosso resultado total.
Ao final de todos os procedimentos vamos selecionar todas as colunas da tabela, daremos duplo clique para autoajustar as colunas, e formatar como moeda.
Nessa aula nós vamos dar continuidade com o controle de imposto de renda parte 2 para complementar as informações dessa aba!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima sétima aula da série. Nessa aula nós vamos dar continuidade ao controle de imposto de renda na nossa planilha, iniciada na aula anterior!
Para isso vamos utilizar algumas funções que você já deve conhecer dentro do Excel para ajustar nossa tabela como:
Vamos utilizar essas funções e a formatação de células para preencher as colunas de:
Assim, vamos dar continuação da construção da nossa planilha/aba de imposto de renda para ter todas as informações necessárias!
Inserindo fórmulas na primeira linha de cada coluna
=SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Futuro";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0)))
Essa fórmula vai fazer a soma da coluna de Valores da Tabela Movimentações, com a coluna de Tipo da mesma tabela sendo igual a “Resultado Futuro”, e a data pregão correspondente a esse valor e esse tipo na Tabela de Movimentações, sendo maior ou igual à data da linha em questão na nossa tabela de Imposto de Renda, e que essa mesma data pregão seja menor ou igual ao final do mês (dada pela função FIMMÊS) apontado pela data da Tabela de Imposto de Renda.
=SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Termo";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0)))
Essa fórmula vai fazer a soma com a lógica de forma parecida com a fórmula anterior, mas dessa vez somaremos os tipos de “Resultado Termo”.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Opções";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0)))
Essa fórmula vai fazer a soma com a lógica de forma parecida com a fórmula anterior, mas dessa vez somaremos os tipos de “Resultado Opções”.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SOMA(Tabela5[@[Resultado Ação]:[Resultado Opção]])-[@[Lucro Isento]]
A fórmula acima vai fazer a soma das colunas entre Resultado Ação e Opção, tirando (subtraindo) o possível lucro isento dessa linha.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SE(LIN()=2;0;SE(K1+J1>=0;0;K1+J1))
A fórmula acima vai ser elaborada para ser arrastada, mas para a segunda linha da tabela ela vai ser forçada a retornar o resultado 0.
Olhando para a terceira linha da nossa planilha, vamos analisar a fórmula:
=SE(LIN()=2;0;SE(K2+J2>=0;0;K2+J2))
Vamos utilizar uma fórmula SE para fazer a seguinte verificação: olharemos qual a linha da atual célula através da Função LIN. Se essa linha for igual a 2 (caso citado anterior), ou seja, a primeira linha da tabela, o resultado da fórmula vai ser 0.
Caso a linha que estivermos seja diferente de 2, ou seja, a partir da 3ª linha, vamos para o segundo teste: se a soma dos valores que estão nas colunas K e J da linha anterior (imediatamente acima) somados for maior ou igual a zero, retornaremos zero. Caso contrário, ou seja, essa soma menor do que zero, somaremos os valores das colunas K e J da linha anterior à linha em questão da tabela de Imposto de Renda.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SE([@[Resultado Total]]+[@[Prejuízo a Compensar]]>0;[@[Resultado Total]]+[@[Prejuízo a Compensar]];0)
A fórmula acima vai fazer também uma verificação: na mesma linha, se o somatório das colunas de Resultado Total e Prejuízo a Compensar for maior do que zero (0), manteremos essa soma. Caso essa soma seja menor ou igual a zero, retornaremos 0.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=[@[Base de Cálculo]]*15%
A fórmula acima vai fazer a simples multiplicação da linha que tiver na coluna da Base de Cálculo por 15%.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];{"I.R.R.F. Ação";"I.R.R.F. ETF";"I.R.R.F. BDR";"I.R.R.F. Futuro";"I.R.R.F. Termo";"I.R.R.F. Opção"};TabelaMovimentacoes[Data Liquidação];">="&[@Mês];TabelaMovimentacoes[Data Liquidação];"<="&FIMMÊS([@Mês];0)))
Essa fórmula vai fazer a soma com a lógica de forma parecida com a fórmula anterior, mas dessa vez somaremos os tipos de “I.R.R.F. Ação”, “I.R.R.F. ETF”, “I.R.R.F. BDR”, “I.R.R.F. Futuro”, “I.R.R.F. Termo”, “I.R.R.F. Opção”.
Aqui repare que fizemos uma soma no começo, porque a nossa fórmula resulta numa matriz de três números individuais. Com a fórmula de soma, acabamos por somar esses 6 números (que antes seriam distribuídos em 6 células diferentes) e colocar em uma única célula.
Ao final de todos os procedimentos vamos selecionar todas as colunas da tabela, daremos duplo clique para auto ajustar as colunas.
Nessa aula nós vamos dar continuidade com o controle de imposto de renda parte 3 para complementar as informações dessa aba!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima oitava aula da série. Nessa aula nós vamos dar continuidade ao controle de imposto de renda na nossa planilha! Estamos na parte 3!
Para isso vamos utilizar algumas funções que você já deve conhecer dentro do Excel para ajustar nossa tabela como:
Vamos utilizar essas funções e a formatação de células para preencher as colunas de:
Assim, vamos dar continuação da construção da nossa planilha/aba de cálculo do imposto de renda para ter todas as informações necessárias!
Inserindo fórmulas na primeira linha de cada coluna
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SE(
[@[Imposto Devido]]<=0;0;
SE([@[Imposto Devido]]<
ABS(SOMASES([I.R.R.F. no mês];[Mês];">="&DATA(ANO([@Mês]);1;1);[Mês];"<="&FIMMÊS([@Mês];0))
-
SOMASES([I.R.R.F Compensado];[Mês];">="&DATA(ANO([@Mês]);1;1);[Mês];"<"&[@Mês]));-[@[Imposto Devido]];
SOMASES([I.R.R.F. no mês];[Mês];">="&DATA(ANO([@Mês]);1;1);[Mês];"<="&FIMMÊS([@Mês];0))
-
SOMASES([I.R.R.F Compensado];[Mês];">="&DATA(ANO([@Mês]);1;1);[Mês];"<"&[@Mês])
)
)
A fórmula acima vai fazer uma verificação principal: se o valor da linha correspondente do imposto devido for menor ou igual a que zero, zero será retornado como resposta.
Caso contrário (ou seja, maior ou igual a zero), vamos fazer uma comparação entre os valores absolutos do valor do IRRF do mês em questão (considerando as datas inferiores até ele, ou seja, acumulado das datas daquele ano) e o valor do IRRF Compensado (também considerando as datas inferiores até ele).
Caso o valor absoluto do Imposto Devido seja maior do que o IRRF Compensado até aquele mês, vou apenas compensar o valor de Imposto Devido até aquele mês (minuto 31 de vídeo)
Caso contrário, ou seja, o valor do IRRF Compensado seja maior do que o Imposto Devido, vamos fazer o cálculo do IRRF Compensado como sendo o sinal contrário do que temos para o valor do Imposto Devido (minuto 32 de vídeo).
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SE([@[Imposto Devido]]+[@[I.R.R.F Compensado]]<0;0;[@[Imposto Devido]]+[@[I.R.R.F Compensado]])
Essa fórmula vai fazer uma verificação: se o valor da soma do imposto devido com o IRRF Compensado for menor do que zero, zero será retornado.
Caso contrário, ou seja, se essa soma for maior ou igual a zero, será retornada a própria soma do imposto devido com o IRRF Compensado.
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SE(SOMASES([Imposto a pagar];[Mês];"<="&FIMMÊS([@Mês];0))-SOMASES([DARF a pagar];[Mês];"<"&[@Mês])>10;SOMASES([Imposto a pagar];[Mês];"<="&FIMMÊS([@Mês];0))-SOMASES([DARF a pagar];[Mês];"<"&[@Mês]);0)
Se a soma de impostos a pagar, do mês até o mês em questão, no mesmo ano, do imposto a pagar for maior do que 10, colocaremos o que for exatamente o valor da nossa soma de impostos a pagar diminuída do que foi o resultado dos DARFs anteriores até aquele mês (menor).
A partir daí, se ficar verificado que essa diferença entre a soma dos impostos devidos e a soma do que foi calculado dos DARFs até o mês em questão for menor do que 10, o valor retornado será 0.
Ou seja, só aparecerá valor se tivermos o resultado maior do que 10.
(Mais detalhes de 36 a 39 min de vídeo)
Vamos inserir na primeira linha da coluna a fórmula abaixo:
=SE([@[DARF a pagar]]>0;PROCV(DATAM([@Mês];1);Feriados!E:N;10;0);"")
Nesse caso vamos utilizar uma tabela auxiliar inserida, chamada “Feriados”, onde teremos listados o primeiro dia e o último dia útil de um mês específico, já levando em consideração os feriados. Disponibilizamos essas informações do ano de 2002 até 2040.
Essa fórmula vai fazer uma verificação: se o valor do DARF a pagar for maior do que 0 (positivo), faremos basicamente uma busca da data um mês posterior (conseguido através da fórmula DATAM, deslocando 1 mês pra frente), na coluna E e retornando o que tiver na coluna N da planilha de Feriados, para buscar o último dia útil daquele mês posterior.
Assim teremos como resultado dessa fórmula o último dia útil do mês (coluna N) posterior a data de interesse, que é quando o imposto tem que ser pago.
Ao final de todos os procedimentos vamos selecionar todas as colunas da tabela, daremos duplo clique para auto ajustar as colunas.
Nessa aula nós vamos dar continuidade com o nosso controle, mas agora vamos para imposto de renda day trade e FIIs para complementar a planilha!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a trigésima nona aula da série. E nessa aula nós vamos dar continuidade ao controle de imposto de renda na nossa planilha! Agora vamos criar as abas de Imposto de Renda Day Trade e de FIIS.
Para isso vamos utilizar algumas funções que você já deve conhecer dentro do Excel para ajustar nossa tabela utilizando de:
Vamos utilizar essas funções para construir as abas e tabelas:
Assim, vamos dar continuação da construção da nossa planilha/aba de cálculo do imposto de renda para ter todas as informações necessárias!
Pequena correção na Aba Imposto de Renda
Vamos fazer uma pequena correção na fórmula da coluna de Imposto Devido, para:
=ARREDONDAR.PARA.BAIXO([@[Base de Cálculo]]*15%;2)
Precisamos arredondar para baixo aquele valor apresentado porque não pagamos em centavos a partir da 3ª casa decimal. Por isso, vamos arredondar o resultado para duas casas decimais, para nos apontar até os centavos que devemos pagar, nesse caso.
Essa fórmula vai ser replicada para as outras abas da forma correta (já atualizada).
Aba/Tabela de Imposto de Renda Day Trade
Para todas as colunas vamos inserir o texto “ Day Trade” ao final de cada texto, que então serão analisados pelas fórmulas de todas as colunas, com exceção de:
=ARREDONDAR.PARA.BAIXO([@[Base de Cálculo]]*20%;2)
Nesse caso (Day Trade) vamos considerar o imposto de 20% sobre a Base de Cálculo.
Aba/Tabela de Imposto F.I.I.
Para todas as colunas vamos inserir o texto “ FII” ao final de cada texto, que então serão analisados pelas fórmulas de todas as colunas, com a exceção de:
=SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];{"Resultado FII";"Resultado FII Day Trade"};TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0)))
Onde precisamos incluir aqui, entre chaves, “Resultado FII e “Resultado FII Day Trade”, porque há a possibilidade de ter FII em operações normais, e FII em operações Day Trade.
=SE([@[Resultado FII]]+[@[Prejuízo a Compensar]]>0;[@[Resultado FII]]+[@[Prejuízo a Compensar]];0)
Vamos usar apenas a coluna de Resultado FII aqui, porque ela é a única coluna de Resultado nessa tabela/aba. As outras não são necessárias e foram excluídas.
=ARREDONDAR.PARA.BAIXO([@[Base de Cálculo]]*20%;2)
Nesse caso (de FII) vamos considerar o imposto de 20% sobre a Base de Cálculo.
OBS: Lembrando que estamos excluindo, ao final do processo as três últimas colunas das tabelas de Imposto de Renda (Normal, Day Trade e FII), que são: DARF a pagar, Data de pagamento e Imposto pago.
Essas colunas irão compor justamente a nova aba de DARF a Pagar.
Aba/Tabela de DARF a Pagar
Vamos segurar a tecla Ctrl (teclado), clicara com botão esquerdo (mouse) e arrastar para o lado qualquer aba de Imposto de Renda, das citadas acima.
Assim, vamos copiar aba no Excel, para fazer a nossa aba de DARF a Pagar, que vai ter as seguintes colunas:
=TabelaIRDFII[@[Imposto a pagar]]+TabelaIRDT[@[Imposto a pagar]]+TabelaIR[@[Imposto a pagar]]
É simplesmente a soma das colunas “Imposto a pagar” das tabelas das abas Imposto de Renda, Imposto de Renda Day Trade e Imposto de Renda F.I.I..
=SE(SOMASES([Imposto a pagar];[Mês];"<="&FIMMÊS([@Mês];0))-SOMASES([DARF a pagar];[Mês];"<"&[@Mês])>10;SOMASES([Imposto a pagar];[Mês];"<="&FIMMÊS([@Mês];0))-SOMASES([DARF a pagar];[Mês];"<"&[@Mês]);0)
Se a soma do ano é menor do que 10, o valor vai se acumular e nada será retornado como resposta. Se o valor da soma for maior do que 10, vai ser retornado o valor da soma.
=SE([@[DARF a pagar]]>0;PROCV(DATAM([@Mês];1);Feriados!E:N;10;0);"")
Tem a mesma lógica da fórmula para as abas de imposto de renda individual, apontando o dia útil que deve ser feito o pagamento do imposto.
Sem fórmulas, para justamente você preencher quando realizar o pagamento do imposto
Nessa aula nós vamos te mostrar a nova macro para split inplit. Vamos fazer alguns ajustes e melhorar a macro de agrupamentos e desdobramentos.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a quadragésima aula da série. Nessa aula nós fazer ajustes na nossa macro que envolve agrupamentos e desdobramentos (macro para split/inplit)!
Vamos lidar com:
Assim, vamos dar continuação da construção da nossa planilha da série fazendo ajustes na macro para split/inplit, fazendo uma nova versão dela, mais simples dessa vez. Acompanhe.
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Observe a sub abaixo (macro para split/inplit):
Sub split_inplit_v2()
ativo = InputBox("Digite o Ticker do ativo a ser ajustado.")
If ativo = "" Then Exit Sub
corretora = InputBox("Em qual corretora você vai receber esse ativo?")
If corretora = "" Then Exit Sub
proporcao = InputBox("Digite a proporção do evento. Ex: se for um split de 1 para 4, digite 1/4. Se for um inplit de 4 para 1, digite 4/1.")
If proporcao = "" Then Exit Sub
data_corte = CDate(InputBox("Digite a data de corte do evento. Ex: os acionistas só terão direito a esse evento se tiverem adquirido os ativos até a data 13/10/2020, então a data de corte é 13/10/2020"))
If data_corte = 0 Then Exit Sub
pos_barra = InStr(proporcao, "/")
de = CInt(Left(proporcao, pos_barra - 1))
para = CInt(Mid(proporcao, pos_barra + 1, 2))
qtd_total = WorksheetFunction.SumIfs(Sheets("Movimentações").Range("G:G"), Sheets("Movimentações").Range("F:F"), "Ação", Sheets("Movimentações").Range("E:E"), ativo, Sheets("Movimentações").Range("C:C"), "<=" & CLng(data_corte))
qtd_final = WorksheetFunction.RoundDown(qtd_total * para / de, 0)
quantidade = qtd_final - qtd_total
linha = Sheets("Movimentações").Range("A1048576").End(xlUp).Row + 1
Sheets("Movimentações").Cells(linha, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1 'ID
Sheets("Movimentações").Cells(linha, 2) = corretora
Sheets("Movimentações").Cells(linha, 3) = data_corte
Sheets("Movimentações").Cells(linha, 4) = data_corte
Sheets("Movimentações").Cells(linha, 5) = ativo
Sheets("Movimentações").Cells(linha, 6) = "Ação"
Sheets("Movimentações").Cells(linha, 7) = quantidade
Sheets("Movimentações").Cells(linha, 8) = 0
Sheets("Movimentações").Cells(linha, 9) = "Ajuste na proporção - De: " & de & " Para: " & para
End Sub
Comentários e explicação do código:
Botão para rodar o nosso código criado
Precisamos de um botão que será nosso ativador ou abridor do nosso código.
Nessa aula nós vamos fazer alguns ajustes na macro para Subscrição de Ações e FIIs para separar as macros de IPO e Subscrição para deixá-la melhor!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Essa é a aula de número 41 da série. E nessa aula nós fazer ajustes na nossa nova macro que envolve Subscrição de ações e FIIs!
Para lidar com Subscrição de Ações e FIIs vamos utilizar:
Assim, vamos dar continuação da construção da nossa planilha da série fazendo ajustes na macro de IPO e Subscrição (Subscrição de Ações e FIIs), fazendo uma nova versão dela, mais simples dessa vez. Acompanhe.
Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.
Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.
Observe a sub abaixo:
Sub subscricao()
tipo_mov = MsgBox("Você quer incluir a data de liquidação?", vbYesNo)
corretora = InputBox("Em qual corretora você vai receber esse ativo?")
If corretora = "" Then Exit Sub
ativo = InputBox("Qual é o ativo em questão?")
If ativo = "" Then Exit Sub
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
If tipo_mov = 6 Then 'Estamos incluindo a parte de Liquidação
data_liq = CDate(InputBox("Quando você vai pagar por esse ativo?"))
If data_liq = 0 Then Exit Sub
valor_liq = InputBox("Qual o valor que será debitado na data de liquidação?") + 0
If valor_liq = 0 Then Exit Sub
Sheets("Movimentações").Cells(linha + 1, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
Sheets("Movimentações").Cells(linha + 1, 2) = corretora
Sheets("Movimentações").Cells(linha + 1, 3) = data_liq
Sheets("Movimentações").Cells(linha + 1, 4) = data_liq
Sheets("Movimentações").Cells(linha + 1, 5) = ativo
Sheets("Movimentações").Cells(linha + 1, 6) = "Subscrição"
Sheets("Movimentações").Cells(linha + 1, 8) = -valor_liq
Sheets("Movimentações").Cells(linha + 1, 9) = "Subscrição: Ajuste para a Data de Liquidação"
Else 'Estamos ajustando as movimentações para incluir a Integralização
tipo = InputBox("Qual é tipo do ativo?")
If tipo = "" Then Exit Sub
data_int = CDate(InputBox("Quando você vai receber esses ativos?"))
If data_int = 0 Then Exit Sub
qtd = InputBox("Qual a quantidade desse ativo que você vai receber?") + 0
If qtd = 0 Then Exit Sub
For i = 4 To linha
If Sheets("Movimentações").Cells(linha, 6) = "Subscrição" And Sheets("Movimentações").Cells(linha, 5) = ativo Then
id_mov = Sheets("Movimentações").Cells(linha, 1)
Sheets("Movimentações").Cells(linha, 6) = tipo
End If
Next
Sheets("Movimentações").Cells(linha + 1, 1) = id_mov
Sheets("Movimentações").Cells(linha + 1, 2) = corretora
Sheets("Movimentações").Cells(linha + 1, 3) = data_int
Sheets("Movimentações").Cells(linha + 1, 4) = data_int
Sheets("Movimentações").Cells(linha + 1, 5) = ativo
Sheets("Movimentações").Cells(linha + 1, 6) = tipo
Sheets("Movimentações").Cells(linha + 1, 7) = qtd
Sheets("Movimentações").Cells(linha + 1, 8) = 0
Sheets("Movimentações").Cells(linha + 1, 9) = "Subscrição: Ajuste para a Data de Integralização"
End If
MsgBox "Terminou!"
End Sub
Explicação do código acima (subscricao):
Observe a sub abaixo:
Sub ipo()
ativo = InputBox("Qual é o ativo em questão?")
If ativo = "" Then Exit Sub
tipo = InputBox("Qual é tipo do ativo?")
If tipo = "" Then Exit Sub
corretora = InputBox("Em qual corretora você vai receber esse ativo?")
If corretora = "" Then Exit Sub
data_mov = CDate(InputBox("Quando você vai receber esse ativo?"))
If data_mov = 0 Then Exit Sub
qtd = InputBox("Qual a quantidade desse ativo que você vai receber?") + 0
If qtd = 0 Then Exit Sub
custo_unitario = InputBox("Qual o custo unitário pago por esse ativo?") + 0
If custo_unitario = 0 Then Exit Sub
linha = Sheets("Movimentações").Range("A1000000").End(xlUp).Row
Sheets("Movimentações").Cells(linha + 1, 1) = WorksheetFunction.Max(Sheets("Movimentações").Range("A:A"), Sheets("Notas Day Trade").Range("B:B"), Sheets("Notas").Range("B:B")) + 1
Sheets("Movimentações").Cells(linha + 1, 2) = corretora
Sheets("Movimentações").Cells(linha + 1, 3) = data_mov
Sheets("Movimentações").Cells(linha + 1, 4) = data_mov
Sheets("Movimentações").Cells(linha + 1, 5) = ativo
Sheets("Movimentações").Cells(linha + 1, 6) = tipo
Sheets("Movimentações").Cells(linha + 1, 7) = qtd
Sheets("Movimentações").Cells(linha + 1, 8) = -WorksheetFunction.RoundDown(qtd * custo_unitario, 2)
Sheets("Movimentações").Cells(linha + 1, 9) = "IPO"
MsgBox "Terminou!"
End Sub
Explicação do código acima (ipo):
Botão para rodar o nosso código criado
Precisamos de um botão que será o ativador/responsável por disparar nosso código.
Para isso, iremos na nossa planilha na guia Inserir > Ilustrações > Formas > Retângulo: Cantos Arredondados (2º ícone da categoria de Retângulos).
A partir dessa escolha, redimensione a ilustração de acordo com o seu gosto, e pronto.
Após isso, vamos personalizar o botão. Para isso, iremos dar um clique com o botão esquerdo do mouse nele, e escreveremos o que desejamos que esteja escrito nele. No nosso exemplo, escrevemos “IPO”, fomos na guia Página Inicial, e botamos o alinhamento no meio e centralizamos o texto, além de colocar em negrito.
Para finalizar, iremos colocar um sombreamento, indo em Formato de Forma > Efeitos de Forma > Predefinição > Predefinições > Predefinição 2.
Para atrelar o código ao nosso botão, no Excel, clicaremos com o botão direito do mouse no botão, iremos em atribuir macro… e procuraremos pela nossa macro (ipo). Em seguida, clicaremos nela e em OK.
Pronto. Agora basta clicar com o botão esquerdo do mouse no botão, que o código irá rodar.
Vamos repetir o processo para a macro de subscricao, colocando o texto “Subscrição” no botão que vai ser criado copiando a forma do de IPO.
Nessa aula nós vamos iniciar a construção de um modelo para os Bens e Direitos da DIRPF Pt.1 para facilitar o preenchimento dos dados!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala Galera! Essa é a aula de número 42 da nossa Série Controles para Bolsa de Valores!
E nessa aula nós vamos criar um modelo para os Bens e Direitos da DIRPF Pt.1 na nossa planilha de controle para bolsa de valores!
Para isso vamos utilizar algumas funções dentro do Excel:
Vamos, em primeiro lugar, criar a nossa aba de Bens e Direitos para ter todas as informações necessárias!
A seguir listamos as colunas que ela deve conter para representar o nosso modelo de Declaração de Imposto de Renda (DIRPF).
Aba Bens e Direitos (Bens e Direitos da DIRPF Pt.1)
OBS: As fórmulas informadas abaixo apresentadas estarão nas respectivas colunas, sempre na linha 2.
Ano
Vamos deixar um campo em aberto para selecionarmos/digitarmos o ano em questão que queremos ver nessa aba.
Corretora
Vamos listar aqui as corretoras que fazem parte do nosso controle de ações, para facilitar a nossa aba.
Razão Social
Vamos listar aqui as razões sociais das nossas corretoras, que fazem parte do nosso controle de ações, para facilitar a nossa aba.
CNPJ
Vamos listar aqui os CNPJs das corretoras citadas, que fazem parte do nosso controle de ações, para facilitar a nossa aba.
Ativo e Tipo
Vamos listar os ativos e tipos derivados da matriz apresentada na aba opções mais especificamente na célula D2.
=ÚNICO(Opções!D2#)
Através da fórmula ÚNICO são retornados apenas os valores sem repetição daquela lista.
Repare que aqui temos como resultado os valores únicos considerando cada ativo e tipo (matriz de duas colunas).
Corretora 1, 2 e 3
Vamos listar as três corretoras em que temos movimentações de ativos.
=SE(E2="";"";TRANSPOR(ÚNICO(FILTRO(TabelaMovimentacoes[Conta];TabelaMovimentacoes[Descrição]=E2))))
Vamos filtrar (função FILTRO) a coluna de Conta na Tabela Movimentações (aba Movimentações), apenas para os valores em que temos o ativo igual ao apresentado naquela linha, na coluna E (Ativo), da aba de Bens e Direitos.
Por fora dela vamos utilizar a função ÚNICO, já explicada anteriormente, em que vai retornar apenas as corretoras, sem repetição, para quele tipo de ativo.
Como essa fórmula apenas nos daria uma matriz vertical (1 coluna e várias linhas), e queremos que essa lista seja na horizontal (1 linha e várias colunas), usaremos a fórmula TRANSPOR para fazer essa operação de transposição de matrizes.
Por último vamos aplicar uma fórmula SE para comparar se temos algo preenchido na coluna E. Se tivermos algo, preencheremos com o resultado da fórmula. Caso não haja, será retornado em branco (vazio).
Quantidade em 31/12/2020 (ano anterior)
É aqui que vamos listar a quantidade de ativos que temos no ano anterior ao analisado/digitado na célula B1.
=ARRED(SOMASES(TabelaMovimentacoes[Quantidade];TabelaMovimentacoes[Descrição];ÍNDICE(E2#;0;1);TabelaMovimentacoes[Tipo];ÍNDICE(E2#;0;2);TabelaMovimentacoes[Data Liquidação];"<="&DATA($B$1-1;12;31));0)
Essa fórmula vai fazer a soma das quantidades da coluna de Quantidade na Tabela Movimentações (aba Movimentações), apenas para os valores em que temos a descrição na tabela conforme o ativo em questão.
Assim como para os valores em que temos o tipo na tabela conforme o tipo de ativo igual ao apresentado naquela linha, na coluna E (Ativo), da aba de Bens e Direitos.
Além dessas condições, a soma das quantidades vai levar em consideração a data listada na Tabela Movimentações, que deve ser menor ou igual à última data do ano anterior (B1-1).
Repare que usamos a fórmula ARRED para arredondar esse valor para zero casas decimais, também.
Repare que o título da coluna está vinculado ao valor da célula B1. Ele sempre vai pegar o ano anterior ao da célula B1 (=B1-1).
Situação em 31/12/2020 (ano anterior)
A situação vai nos dizer qual é o valor monetário referente àquela quantidade de ações que ainda temos no ano anterior ao vigente.
=-ARRED(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Descrição];ÍNDICE(E2#;0;1);TabelaMovimentacoes[Tipo];ÍNDICE(E2#;0;2);TabelaMovimentacoes[Data Liquidação];"<="&DATA($B$1-1;12;31));2)
A fórmula é similar à apresentada anteriormente para quantidades, mas nesse caso vamos somar os valores da coluna de Valor da Tabela Movimentações.
Repare também que usamos a fórmula ARRED para arredondar esse valor para duas casas decimais, porque se trata de um valor monetário.
Também colocamos um sinal negativo no começo da fórmula para inverter/consertar o valor exibido na Tabela Movimentações (compra representa valor negativo, e venda valor positivo).
Repare que o título da coluna está vinculado ao valor da célula B1. Ele sempre vai pegar o ano anterior ao da célula B1 (=B1-1).
Quantidade em 31/12/2021 (ano vigente)
É aqui que vamos listar a quantidade de ativos que temos no ano vigente, que foi digitado na célula B1.
=ARRED(SOMASES(TabelaMovimentacoes[Quantidade];TabelaMovimentacoes[Descrição];ÍNDICE(E2#;0;1);TabelaMovimentacoes[Tipo];ÍNDICE(E2#;0;2);TabelaMovimentacoes[Data Liquidação];"<="&DATA($B$1;12;31));0)
Essa fórmula vai fazer a soma das quantidades da coluna de Quantidade na Tabela Movimentações (aba Movimentações), apenas para os valores em que temos a descrição na tabela conforme o ativo em questão.
Assim como para os valores em que temos o tipo na tabela conforme o tipo de ativo igual ao apresentado naquela linha, na coluna E (Ativo), da aba de Bens e Direitos.
Além dessas condições, a soma das quantidades vai levar em consideração a data listada na Tabela Movimentações, que deve ser menor ou igual à última data do ano vigente (B1).
Repare que usamos a fórmula ARRED para arredondar esse valor para zero casas decimais, também.
Repare que o título da coluna está vinculado ao valor da célula B1. Ele sempre vai pegar o ano vigente (igual ao da célula B1).
Situação em 31/12/2021 (ano vigente)
A situação vai nos dizer qual é o valor monetário referente àquela quantidade de ações que ainda temos nesse ano.
=-ARRED(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Descrição];ÍNDICE(E2#;0;1);TabelaMovimentacoes[Tipo];ÍNDICE(E2#;0;2);TabelaMovimentacoes[Data Liquidação];”<=”&DATA($B$1;12;31));2)
A fórmula é similar à apresentada anteriormente para quantidades, mas nesse caso vamos somar os valores da coluna de Valor da Tabela Movimentações.
Repare também que usamos a fórmula ARRED para arredondar esse valor para duas casas decimais, porque se trata de um valor monetário.
Também colocamos um sinal negativo no começo da fórmula para inverter/consertar o valor exibido na Tabela Movimentações (compra representa valor negativo, e venda valor positivo).
Repare que o título da coluna está vinculado ao valor da célula B1. Ele sempre vai pegar o ano vigente (igual ao da célula B1).
Terminou 2020 ou 2021 posicionado?
Vamos expor uma fórmula resultado que irá apontar pra gente se tivermos quantidades do ativo da coluna E no ano vigente ou anterior maiores do que 0.
=SE(J2#+L2#>0;"Sim";"Não")
Caso o resultado da soma de J2 e L2 seja positivo, estaremos posicionados no ano vigente ou no anterior. Caso contrário, não estaremos posicionados.
Repare que o título da coluna está vinculado ao valor da célula B1. Ele sempre vai pegar o ano vigente (igual ao preenchido na célula B1) e o anterior ao da célula B1 (=B1-1).
Nessa aula nós vamos continuar com o modelo de Bens e Direitos da DIRPF Pt.2 para facilitar o preenchimento da declaração!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala Galera! Essa é a aula de número 43 da nossa Série Controles para Bolsa de Valores!
E nessa aula nós vamos terminar o nosso modelo para os Bens e Direitos da DIRPF Pt.2 na nossa planilha de controle para bolsa de valores!
Para isso vamos utilizar algumas funções dentro do Excel:
A seguir daremos continuidade ao nosso modelo para Bens e Direitos da DIRPF Pt.2 para finalizar as informações que precisamos para a Declaração de Imposto de Renda para Pessoa Física (DIRPF).
Aba Bens e Direitos (Bens e Direitos da DIRPF Pt.2)
OBS: As fórmulas informadas abaixo apresentadas estarão nas respectivas colunas, sempre na linha 2.
Ativo
Vamos deixar um campo em aberto para selecionarmos/digitarmos o ano em questão que queremos ver nessa aba.
=FILTRO(ÍNDICE(E2#;0;1);N2#="Sim";"Sem nenhum ativo")
Vamos filtrar (função FILTRO) a 1ª coluna da matriz que está nas colunas E (Ativo) e F (Tipo), através da função ÍNDICE, onde pegaremos apenas os valores em que temos “Sim” escrito na coluna N (Posicionado?).
Caso não seja “Sim” na coluna N, será retornado “Sem nenhum ativo” nessa fórmula.
Corretora 1
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:I;3;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo nas colunas E:I (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna E (coluna de procura) e o seu correspondente na coluna 3 (Corretora 1).
Corretora 2
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:I;4;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo nas colunas E:I (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna E (coluna de procura) e o seu correspondente na coluna 4 (Corretora 2).
Corretora 3
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:I;5;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo nas colunas E:I (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna E (coluna de procura) e o seu correspondente na coluna 5 (Corretora 3).
Tipo
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:F;2;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo nas colunas E:F (colunas Ativo, Tipo), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna E (coluna de procura) e o seu correspondente na coluna 2 (Tipo).
Quantidade em 31/12/2021
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:M;8;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo nas colunas E:M (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3, Quantidade em 31/12/2020, Situação em 31/12/2020, Quantidade em 31/12/2021, Situação em 31/12/2021), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna E (coluna de procura) e o seu correspondente na coluna 8 (Quantidade no ano vigente).
Preço Médio em 31/12/2021
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:M;9;0)/U2#)
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo nas colunas E:M (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3, Quantidade em 31/12/2020, Situação em 31/12/2020, Quantidade em 31/12/2021, Situação em 31/12/2021), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna E (coluna de procura) e o seu correspondente na coluna 8 (Situação no ano vigente).
Essa situação vai ser dividida pela quantidade de ações no ano vigente (coluna U), assim resultando no preço médio daqueles ativos.
Código
Fórmula:
=SE(P2="Sem nenhum ativo";"";SE(T2#="Ação";31;SE(T2#="BDR";49;SE(T2#="FII";73;74))))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, vamos chamar funções SE aninhadas:
CNPJ
Fórmula:
=SE(P2="Sem nenhum ativo";"";SE(PROCV(P2#;'Empresas Listadas'!A:B;2;0)="";"";PROCV(P2#;'Empresas Listadas'!A:B;2;0)))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo/ticker nas colunas A:B da aba de Empresas Listadas (colunas Ticker, CNPJ), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 2 (CNPJ).
Quantidade
Fórmula:
=SE(P2="Sem nenhum ativo";"";U2#&SE(T2#="Ação";" Ações de ";" Cotas de "))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, vamos juntar o texto que tiver na coluna U com o que tiver na coluna T. Ou seja, a quantidade do ativo no ano vigente com o seu tipo.
Assim teremos o descritivo da quantidade.
Razão Social
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;'Empresas Listadas'!A:C;3;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito, vamos fazer uma procura vertical (PROCV) desse ativo/ticker nas colunas A:C da aba de Empresas Listadas (colunas Ticker, CNPJ e Razão Social), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 3 (Razão Social).
Custo Médio
Fórmula:
=SE(P2="Sem nenhum ativo";"";" ao custo de R$ "&TEXTO(V2#;"0,0000")&" cada - ")
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, vamos juntar o texto ” ao custo de R$ ” com o que for resultante do que estiver na coluna V formatado para 4 casas decimais.
Então, teríamos como resultado o que seria seu preço médio no ano vigente.
Assim teremos o descritivo do custo médio.
Ticker
Fórmula:
=SE(P2="Sem nenhum ativo";"";"Ticker: "&P2#&" - ")
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, vamos juntar o texto “Ticker” com o que estiver na coluna P (Ativo) e com o texto “ – “.
Então, teríamos o descritivo do Ticker em questão.
Corretora
Fórmula:
=SE(P2="Sem nenhum ativo";"";SE(R2#=0;"Custodiadas na corretora: ";"Custodiadas nas corretoras: ")&PROCV(Q2#;A:B;2;0)&" - CNPJ: "&PROCV(Q2#;A:C;3;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, vamos fazer uma comparação:
Caso o que estiver na coluna R não seja igual a 0 (teremos alguma corretora), vamos juntar o texto “Custodiadas na corretora: “, e se o que tiver na coluna R for igual a 0, vamos juntar o texto “Custodiadas nas corretoras: “ com o que for resultante de uma procura do nome da corretora 1 (coluna Q) nas colunas A:B (colunas Corretora, Razão Social), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 2 (Razão Social).
Esse resultado será também concatenado (unido) com o texto “ – CNPJ” e com o resultado da procura do nome da corretora 1 (coluna Q) nas colunas A:C (colunas Corretora, Razão Social, CNPJ), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 3 (CNPJ).
Corretora 2
Fórmula:
=SE(P2="Sem nenhum ativo";"";SE(R2#<>0;" e "&PROCV(R2#;A:B;2;0)&" - CNPJ: "&PROCV(R2#;A:C;3;0);""))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, vamos fazer uma comparação:
Caso o que estiver na coluna R seja diferente de 0 (teremos mais de uma corretora), vamos juntar o texto ” e ” com o que for resultante de uma procura do nome da corretora 2 (coluna R) nas colunas A:B (colunas Corretora, Razão Social), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 2 (Razão Social).
Esse resultado será também concatenado (unido) com o texto “ – CNPJ” e com o resultado da procura do nome da corretora 2 (coluna R) nas colunas A:C (colunas Corretora, Razão Social, CNPJ), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 3 (CNPJ).
Lembrando que e se o que tiver na coluna R for igual a 0, nada será retornado.
Corretora 3
Fórmula:
=SE(P2="Sem nenhum ativo";"";SE(S2#<>0;" e "&PROCV(S2#;A:B;2;0)&" - CNPJ: "&PROCV(S2#;A:C;3;0);""))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, vamos fazer uma comparação:
Caso o que estiver na coluna R seja diferente de 0 (teremos mais de uma corretora), vamos juntar o texto ” e ” com o que for resultante de uma procura do nome da corretora 3 (coluna S) nas colunas A:B (colunas Corretora, Razão Social), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 2 (Razão Social).
Esse resultado será também concatenado (unido) com o texto “ – CNPJ” e com o resultado da procura do nome da corretora 2 (coluna S) nas colunas A:C (colunas Corretora, Razão Social, CNPJ), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 3 (CNPJ).
Lembrando que e se o que tiver na coluna R for igual a 0, nada será retornado.
Discriminação
Fórmula:
=SE(P2="Sem nenhum ativo";"";Y2#&Z2#&AA2#&AB2#&AC2#&AD2#&AE2#)
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso haja algo escrito na coluna P diferente disso, vamos concatenar (unir os textos) o que tiver nas colunas Y, Z, AA, AB, AC, AD e AE (D. Quantidade, D. Razão Social, D. Custo Médio, D. Ticker, D. Corretora, D. Corretora 2 e D. Corretora 3).
Assim teremos o nosso campo descritivo final com a junção dos campos descritivos criados parciais.
Situação em 31/12/2020
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:M;7;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito diferente disso, vamos fazer uma procura vertical (PROCV) desse ativo/ticker nas colunas E:M (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3, Quantidade em 31/12/2020, Situação em 31/12/2020), e retornar o que exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 7 (Situação no ano anterior).
Situação em 31/12/2021
Fórmula:
=SE(P2="Sem nenhum ativo";"";PROCV(P2#;E:M;9;0))
Se na coluna de Ativo (coluna P) estiver escrito “Sem nenhum ativo”, nada será retornado.
Caso contrário, ou seja, algo esteja escrito diferente disso, vamos fazer uma procura vertical (PROCV) desse ativo/ticker nas colunas E:M (colunas Ativo, Tipo, Corretora 1, Corretora 2, Corretora 3, Quantidade em 31/12/2020, Situação em 31/12/2020, Quantidade em 31/12/2021 e Situação em 31/12/2021), e retornar exatamente o que for achado (argumento 0 do PROCV) na coluna A (coluna de procura) e o seu correspondente na coluna 9 (Situação no ano vigente).
Nessa aula nós vamos dar início ao modelo para a Renda Variável da DIRPF Pt.1, para complementar o modelo de Bens e Direitos que já fizemos.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Vamos para a quadragésima quinta aula da Série Controles para Bolsa de Valores!
Nessa aula nós vamos dar continuidade ao nosso modelo, mas agora vamos fazer para Renda Variável da DIRPF, onde vamos criar duas abas para inserir essas informações.
Vamos criar a aba de operações comuns/day trade e operações fundos investimentos imobiliários para ajustar todas as informações necessárias.
Nessa aba de operações comuns/day trade vamos te mostrar como utilizar a função DATAM, formatação de data, formatação de planilha e a ferramenta mesclar e centralizar.
Criar aba Operações Comuns – Day Trade
Criaremos uma aba nova e daremos o nome de “Operações Comuns – Day Trade”. Assim
Formatar modelo de tabela
Vamos agora começar a parte de formatação da tabela que vai receber os dados futuramente.
No título teremos ano, lucro isento e imposto retido, com as devidas fórmulas.
A tabela pronta terá esse estilo a seguir para o primeiro mês (janeiro):
Repare que a coluna A está oculta. É com essa coluna que criaremos a coluna de Mês (coluna B). Observe:
Para o primeiro mês vamos usar a fórmula DATA, que vai pegar o ano que está preenchido em C1 (ano de análise), no primeiro dia e primeiro mês.
As próximas linhas vão ser iguais a essa fórmula feita na célula A4 (de A5 até A20)
Para os demais meses (fevereiro até dezembro) teremos o seguinte cenário:
Para os demais meses vamos usar a fórmula DATAM, que vai pegar a data que estiver na célula A4 (ou imediatamente da linha de mesmo tipo correspondente deslocada 1 mês para trás), A21, A38 e deslocar 1 mês para frente.
As próximas “estruturas” de meses vão repetir as fórmulas estabelecidas aqui para o mês de fevereiro, que é o primeiro que usa a fórmula DATAM.
A partir da colocação das fórmulas, vamos, na coluna B, mesclar e centralizar as células que correspondem do primeiro (“Mercado à vista – ações”) até o último tipo (“Imposto Pago”) de cada mês.
Após, como mostrado também na videoaula, vamos centralizar e alinhar ao centro o texto, para que a posição do texto fique no centro dessa célula “grande” mesclada.
Lembrando que devemos formatar a data da coluna B para um formato especial, “Personalizado” do tipo “mmm/aa”.
A formatação da tabela sem borda é acompanhada de borda externa para representar células que não terão preenchimento ou serão mescladas e apresentadas em um único resultado para mais de uma linha, por exemplo.
Iremos copiar as células e fórmulas da primeira linha de tipo até a última linha de tipo para os outros meses, como mostrado no vídeo.
OBS1: A diferença aqui se dá pelo fato de que a linha do tipo de “Resultado negativo até o mês anterior” só é preenchida para o mês de janeiro.
OBS2: Se alternarão as cores de preenchimento entre azul e “sem preenchimento” para diferenciarmos os meses facilmente, ao bater o olho.
Nessa aula nós vamos continuar com o modelo para a Renda Variável da DIRPF Pt.2 para facilitar o preenchimento da declaração de imposto de renda.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal! Vamos para a quadragésima quinta aula (Renda Variável da DIRPF Pt.2) da Série Controles para Bolsa de Valores!
Nessa aula nós vamos dar continuidade ao nosso modelo para Renda Variável da DIRPF Pt.2, onde vamos trabalhar com: Função PROCV
1) Lucro Isento
A fórmula abaixo vai representar a soma da coluna de Lucro Isento na tabela de imposto de renda, se a coluna de mês for maior do que o primeiro dia (01) do primeiro mês (01) da célula C1 (ano analisado) e ao mesmo tempo menor do que o último dia (31) do último mês (12) da célula C1 (ano analisado)
=SOMASES(TabelaIR[Lucro Isento];TabelaIR[Mês];">="&DATA(C1;1;1);TabelaIR[Mês];"<="&DATA(C1;12;31))
2) Imposto Retido
A fórmula abaixo vai fazer uma lógica de soma parecida com a de cima, mas vai retirar a parcela de imposto (I.R.R.F.) já compensado (pago) até aquele ano.
Ou seja, vai desconsiderar o que já foi pago no passado para fazer a conta.
A soma vai se dar tanto na tabela IR quanto na tabela FII
=ABS(SOMASES(TabelaIR[I.R.R.F. no mês];TabelaIR[Mês];">="&DATA(C1;1;1);TabelaIR[Mês];"<="&DATA(C1;12;31))
-
SOMASES(TabelaIR[I.R.R.F Compensado];TabelaIR[Mês];">="&DATA(C1;1;1);TabelaIR[Mês];"<="&DATA(C1;12;31)))
+
ABS(SOMASES(TabelaIRFII[I.R.R.F. no mês];TabelaIRFII[Mês];">="&DATA(C1;1;1);TabelaIRFII[Mês];"<="&DATA(C1;12;31))
-
SOMASES(TabelaIRFII[I.R.R.F Compensado];TabelaIRFII[Mês];">="&DATA(C1;1;1);TabelaIRFII[Mês];"<="&DATA(C1;12;31)))
3) Mercado à vista – Operações comuns
A fórmula abaixo vai fazer a soma dos valores de resultado ação, ETF e BDR da aba de Imposto de Renda, subtraindo o que for lucro isento em cima desses tipos de resultados.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivos encontrados nas colunas 3, 4, 5 e 9.
Lembrando que, para as fórmulas PROCV abaixo, a 3ª coluna é a de Resultado Ação, a 4ª é de Resultado ETF, a 5ª é de Resultado BDR e a 9ª coluna é de Lucro Isento.
=PROCV(A4;'Imposto de Renda'!A:I;3;0)
+PROCV(A4;'Imposto de Renda'!A:I;4;0)
+PROCV(A4;'Imposto de Renda'!A:I;5;0)
-PROCV(A4;'Imposto de Renda'!A:I;9;0)
4) Mercado à vista – Day Trade
A fórmula abaixo vai fazer a soma dos valores de resultado ação, ETF e BDR da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda Day Trade e vai pegar os valores respectivos encontrados nas colunas 2, 3, 4.
Lembrando que, para as fórmulas PROCV abaixo, a 2ª coluna é a de Resultado Ação, a 3ª é de Resultado ETF, a 4ª é de Resultado BDR.
=PROCV(A4;'Imposto de Renda Day Trade'!A:H;2;0)
+PROCV(A4;'Imposto de Renda Day Trade'!A:H;3;0)
+PROCV(A4;'Imposto de Renda Day Trade'!A:H;4;0)
5) Mercado opções – Operações comuns
A fórmula abaixo vai trazer o valor de resultado opção da aba de Imposto de Renda.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivo encontrado na coluna 8.
Lembrando que, para as fórmulas PROCV abaixo, a 8ª coluna é a de Resultado Opção.
=PROCV(A7;'Imposto de Renda'!A:I;8;0)
6) Mercado opções – Day Trade
A fórmula abaixo vai trazer o valor de resultado opção da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda Day Trade e vai pegar os valores respectivo encontrado na coluna 7.
Lembrando que, para as fórmulas PROCV abaixo, a 7ª coluna é a de Resultado Opção.
=PROCV(A4;'Imposto de Renda Day Trade'!A:H;7;0)
7) Mercado futuro – Operações comuns
A fórmula abaixo vai trazer o valor de resultado futuro da aba de Imposto de Renda.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivo encontrado na coluna 6.
Lembrando que, para as fórmulas PROCV abaixo, a 6ª coluna é a de Resultado Futuro.
=PROCV(A7;'Imposto de Renda'!A:I;6;0)
8) Mercado futuro – Day Trade
A fórmula abaixo vai trazer o valor de resultado futuro da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda Day Trade e vai pegar os valores respectivo encontrado na coluna 5.
Lembrando que, para as fórmulas PROCV abaixo, a 5ª coluna é a de Resultado Futuro.
=PROCV(A4;'Imposto de Renda Day Trade'!A:H;5;0)
9) Mercado a termo – Operações comuns
A fórmula abaixo vai trazer o valor de Resultado termo da aba de Imposto de Renda.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivo encontrado na coluna 7.
Lembrando que, para as fórmulas PROCV abaixo, a 7ª coluna é a de Resultado Termo.
=PROCV(A7;'Imposto de Renda'!A:I;7;0)
10) Mercado a termo – Day Trade
A fórmula abaixo vai trazer o valor de resultado termo da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda Day Trade e vai pegar os valores respectivo encontrado na coluna 6.
Lembrando que, para as fórmulas PROCV abaixo, a 6ª coluna é a de Resultado Termo.
=PROCV(A4;'Imposto de Renda Day Trade'!A:H;6;0)
11) Resultado até o mês anterior – Operações comuns
A fórmula abaixo vai trazer o valor de prejuízo a compensar da aba de Imposto de Renda.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivo encontrado na coluna 11.
Lembrando que, para as fórmulas PROCV abaixo, a 11ª coluna é a de Prejuízo a Compensar.
=PROCV(A17;'Imposto de Renda'!A:K;11;0)
12) Resultado até o mês anterior – Day Trade
A fórmula abaixo vai trazer o valor de prejuízo a compensar da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda Day Trade e vai pegar os valores respectivo encontrado na coluna 9.
Lembrando que, para as fórmulas PROCV abaixo, a 9ª coluna é a de Prejuízo a Compensar.
=PROCV(A17;'Imposto de Renda Day Trade'!A:I;9;0)
13) I.R.R.F. Day Trade no mês
A fórmula abaixo vai trazer o valor do I.R.R.F. Day Trade da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda Day Trade e vai pegar os valores respectivo encontrado na coluna 12.
Lembrando que, para as fórmulas PROCV abaixo, a 12ª coluna é a de I.R.R.F. Day Trade.
=PROCV(A18;'Imposto de Renda Day Trade'!A:L;12;0)
14) I.R.R.F no mês
A fórmula abaixo vai trazer o valor do I.R.R.F. no mês da aba de Imposto de Renda.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivo encontrado na coluna 14.
Lembrando que, para as fórmulas PROCV abaixo, a 14ª coluna é a de I.R.R.F. no mês.
=PROCV(A19;'Imposto de Renda'!A:N;14;0)
15) Imposto pago
A fórmula abaixo vai trazer o valor do imposto pago da aba de Imposto de Renda, no mês, somado com o valor do imposto pago da aba de Imposto de Renda Day Trade.
Vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda e vai pegar os valores respectivo encontrado na coluna 17 na aba Imposto de Renda e na coluna 15 na aba Imposto de Renda Day Trade .
Lembrando que, para as fórmulas PROCV abaixo, a coluna 17 na aba Imposto de Renda e a coluna 15 na aba Imposto de Renda Day Trade representam as colunas de Imposto Pago.
=PROCV(A20;'Imposto de Renda'!A:Q;17;0)
+PROCV(A20;'Imposto de Renda Day Trade'!A:O;15;0)
Formatação condicional nas colunas D, E e F
Vamos inserir formatações para destacar no nosso modelo de controle os números positivos (maiores que zero) e negativos (menores do que zero).
Observe abaixo as formatações condicionais criadas, de forma a contemplar esses casos descritos acima.
Lembrando que para adicionar a formatação condicional, deveremos ir na guia página inicial > Estilos > Formatação condicional, e optar pelas configurações desejadas.
Aba nova de Operações Fundos Invest. Imob.
Vamos criar uma aba e dar o nome de “Operações Fundos Invest. Imob.”, para criar também um controle específico para esses tipos de fundo (FII).
Observe como ficará o modelo dessa aba pronto:
1) Resultado líquido do mês
A fórmula abaixo vai trazer o valor de resultado líquido no mês da aba de Imposto de Renda F.I.I.
Ela vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda F.I.I. e pegar o valor respectivo encontrado na coluna 2.
Lembrando que, para a fórmula PROCV abaixo, a 2ª coluna é a de Resultado F.I.I..
=PROCV(A4;'Imposto de Renda F.I.I.'!A:B;2;0)
2) Resultado negativo até o mês anterior
A fórmula abaixo vai trazer o valor de prejuízo a compensar da aba de Imposto de Renda F.I.I.
Ela vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda F.I.I. e pegar o valor respectivo encontrado na coluna 3.
Lembrando que, para a fórmula PROCV abaixo, a 3ª coluna é a de Prejuízo a Compensar.
=PROCV(A4;'Imposto de Renda F.I.I.'!A:C;3;0)
3) Imposto retido no mês
A fórmula abaixo vai trazer o valor de imposto retido no mês aba de Imposto de Renda F.I.I.
Ela vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda F.I.I. e pegar o valor respectivo encontrado na coluna 6.
Lembrando que, para a fórmula PROCV abaixo, a 6ª coluna é a de I.R.R.F. no mês.
=PROCV(A4;'Imposto de Renda F.I.I.'!A:F;6;0)
4) Imposto pago
A fórmula abaixo vai trazer o valor de imposto pago na aba de Imposto de Renda F.I.I.
Ela vai fazer a procura do mês respectivo de análise (coluna A) na aba de Imposto de Renda F.I.I. e pegar o valor respectivo encontrado na coluna 9.
Lembrando que, para a fórmula PROCV abaixo, a 9ª coluna é a de imposto pago.
=PROCV(A4;'Imposto de Renda F.I.I.'!A:I;9;0)
Nessa aula nós vamos te mostrar algumas novas funcionalidades na aba resumo, então vamos fazer alguns ajustes sugeridos para melhorar a planilha!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, pessoal!
Na aula de hoje da Série Controles para Bolsa de Valores iremos fazer alguns ajustes sugeridos por vocês nos comentários, como segmentar gráficos para facilitar a visualização de acordo o tipo de ativo selecionado pelo usuário.
Preparados? Vamos lá!
Tabela Auxiliar 1
- Ativos Prejuízo (Coluna N)
=SE(L2#<0;SE(Y2="";F2#;SE(R2#=Y2;F2#;""));"")
A fórmula acima vai analisar o que está escrito na coluna L (variação absoluta monetária de lucro ou prejuízo com o ativo listado na coluna F).
Se na coluna L houver um valor negativo(menor do que 0), vamos analisar a célula Y2. Se na célula Y2 tiver escrito vazio, vamos partir para a comparação de o que estiver escrito entre a célula Y2 e na coluna R (tipo do ativo em questão, da coluna F).
Se for igual, retornaremos nessa célula o que estiver na coluna F, ou seja, o ativo em questão que gerou prejuízo.
Se não for igual (referente à comparação acima), ou se o que tiver na coluna L for positivo, o resultado da fórmula será vazio (“”).
OBS: Lembrando que o que está na célula Y2 é o resultado do que está selecionado na lista de tipo de ativos criada, para condicionar o nosso gráfico de ativos
- Int. Prejuízo (Coluna O)
=SE(L2#<0;SE(Y2="";L2#;SE(R2#=Y2;L2#;0));0)
A fórmula acima vai fazer uma lógica parecida com a fórmula apresentada anteriormente (categoria acima)
Mas se na coluna L houver um valor negativo, vamos analisar a célula Y, e se na célula Y2 tiver escrito vazio, vamos partir para a comparação de o que estiver escrito entre a célula Y2 e na coluna R (tipo do ativo em questão, da coluna F).
Se for igual, retornaremos nessa célula o que estiver na coluna L, ou seja, a própria variação absoluta do ativo (negativa, monetária).
Se não for igual (referente à comparação acima), ou se o que tiver na coluna L for positivo, o resultado da fórmula será igual a zero (0).
- Ativos Lucro (Coluna P)
=SE(L2#>0;SE(Y2="";F2#;SE(R2#=Y2;F2#;""));"")
A fórmula acima vai analisar o que está escrito na coluna L (variação absoluta monetária de lucro ou prejuízo com o ativo listado na coluna F).
Se na coluna L houver um valor positivo (maior do que 0), vamos analisar a célula Y2. Se na célula Y2 tiver escrito vazio, vamos partir para a comparação de o que estiver escrito entre a célula Y2 e na coluna R (tipo do ativo em questão, da coluna F).
Se for igual, retornaremos nessa célula o que estiver na coluna F, ou seja, o ativo em questão que gerou lucro.
Se não for igual (referente à comparação acima), ou se o que tiver na coluna L for positivo, o resultado da fórmula será vazio (“”).
- Int. Lucro (Coluna Q)
=SE(L2#>0;SE(Y2="";L2#;SE(R2#=Y2;L2#;0));0)
A fórmula acima vai fazer uma lógica parecida com a fórmula apresentada anteriormente (categoria acima)
Mas se na coluna L houver um valor positivo, vamos analisar a célula Y, e se na célula Y2 tiver escrito vazio, vamos partir para a comparação de o que estiver escrito entre a célula Y2 e na coluna R (tipo do ativo em questão, da coluna F).
Se for igual, retornaremos nessa célula o que estiver na coluna L, ou seja, a própria variação absoluta do ativo (positiva, monetária).
Se não for igual (referente à comparação acima), ou se o que tiver na coluna L for positivo, o resultado da fórmula será igual a zero (0).
- Tipo (Coluna R)
=PROCV(F2#;A:B;2;0)
A fórmula acima vai trazer o valor correspondente do tipo do ativo em questão da coluna F.
Vai fazer a procura do ativo em questão (coluna A) na própria tabela da aba Resumo e vai pegar os valores respectivo encontrado na coluna 2 (B).
Tabela Auxiliar 2
- Ativos Prejuízo, Int. Prejuízo, Ativos Lucro, Int. Lucro (Colunas S até V)
=FILTRO(N2:Q1048576;(O2:O1048576<>"")*((O2:O1048576+Q2:Q1048576)<>0))
A fórmula acima vai ser uma fórmula matricial baseada na função FILTRO.
Vai acontecer um filtro de todas as células das colunas entre N e Q, considerando que queremos apenas retornar as células/linhas que tiverem com a coluna O diferente de vazio e os valores que não estão como 0 nas colunas O nem Q, que são as colunas de Intervalo Prejuízo e Intervalo Lucro, respectivamente.
Assim, teremos o filtro aplicado ao mesmo tempo, para várias colunas, e retornaremos apenas os filtros de tipo de ativo desejados aplicados.
- Tipos (Coluna W)
=FILTRO(ÚNICO(B2#);ÚNICO(B2#)<>"")
Adaptações nos nomes do gráfico
Nome: Ativos Lucro
Fórmula: =ÍNDICE(Resumo!$S$2#;0;3)
Nome: Intervalo Lucro
Fórmula: =ÍNDICE(Resumo!$S$2#;0;4)
Nome: Ativos Prejuízo
Fórmula: =ÍNDICE(Resumo!$S$2#;0;1)
Nome: Intervalo Prejuízo
Fórmula: =ÍNDICE(Resumo!$S$2#;0;2)
Adaptações no gráfico
Seguiremos replicando as formatações e opções/propriedades aplicadas e explicadas na aula 13 da série, onde mostramos como construir o gráfico Lucro x Prejuízo.
Basicamente mostramos mais rápido o que fizemos anteriormente para interpretar e aparecer no gráfico final apenas o que tiver preenchido na nossa tabela auxiliar 2.
Adaptações em macro
Bonificação, IPO, Subscrição (em 2 lugares) e Split_Inplit, acrescentamos a estrutura do If mostrada em vídeo (final da aula), para que se carregue o comentário antigo, se houver na OBS.
Dessa forma, o comentário anterior na coluna de OBS não será apagado, caso uma nova informação seja acrescentada.
Nessa aula nós vamos fazer a inclusão de ajustes de sugestões na planilha para que você possa ter uma planilha bem completa com tudo que precisa!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, galera! Essa é a aula de número 47 da nossa Série Controles para Bolsa de Valores!
Nessa aula vamos fazer alguns ajustes baseados nas sugestões e feedbacks que vocês nos deram nos comentários!
1º Ajuste) Macro de subscrição
O primeiro ajuste é na macro de subscrição, onde vamos ajustar a estrutura de repetição for dentro dessa macro para corrigir um leve erro que temos!
Acompanhe:
Trocar)
For i = 4 To linha
If Sheets("Movimentações").Cells(linha, 6) = "Subscrição" And Sheets("Movimentações").Cells(linha, 5) = ativo Then
id_mov = Sheets("Movimentações").Cells(linha, 1)
Sheets("Movimentações").Cells(linha, 6) = tipo
End If
Next
Por)
For i = 4 To linha
If Sheets("Movimentações").Cells(i, 6) = "Subscrição" And Sheets("Movimentações").Cells(i, 5) = ativo Then
id_mov = Sheets("Movimentações").Cells(i, 1)
Sheets("Movimentações").Cells(i, 6) = tipo
End If
Next
2º Ajuste) Macro de incluir notas e incluir notas day trade
O outro ajuste será na macro de incluir notas para corrigir a aba em que vamos obter as informações de Notas, pois a aba anterior foi somada duas vezes dentro da nossa fórmula.
Acompanhe:
Incluir Notas – Trocar)
“Notas Day Trade”
Incluir Notas – Por)
“Notas”
Quando ocorre a definição do ID.
Incluir Notas Day Trade – Trocar)
“Notas Day Trade”
Incluir Notas Day Trade – Por)
“Notas”
Também quando ocorre a definição do ID.
3º Ajuste) Macro de bonificações
O terceiro ajuste que vamos fazer é na macro de bonificações, vamos corrigir uma variável que ainda está buscando as informações da aba notas quando deveria buscar as informações da aba movimentações.
Acompanhe:
Aqui vamos usar a ferramenta de localizar e substituir, pelo atalho Ctrl+L para achar uma variável específica: “quantidade”. Quando acharmos, vamos fazer a substituição de “Notas” por “Movimentações”.
4º Ajuste) Aba de DARF a pagar
O quarto ajuste não é um erro, mas é uma sugestão para chamar atenção na aba de DARF a pagar para facilitar a visualização dos impostos a pagar. Para isso vamos utilizar a formatação condicional no Excel.
Acompanhe:
A primeira formatação condicional vai ser aplicada se o valor da coluna C (DARF a pagar) for maior do que 0.
Caso seja, será formatado de vermelho em cor e preenchimento, como mostrado abaixo:
A segunda formatação condicional vai ser aplicada na coluna D (Data de pagamento) se o valor da coluna C (DARF a pagar) for maior do que 0.
Caso seja, será formatado de vermelho em cor e preenchimento, como mostrado abaixo:
5º Ajuste) Macro de preenchendo_taxas
a) Vamos usar a ferramenta de localizar para procurar por “Sim” e trocar onde tiver escrito “Sim” e mudar para 6, porque no começo estamos usando uma msgbox que nos retorna 6, e não mais “Sim”, como na criação da macro.
b) O próximo passo é trocar:
For Each taxa In m_taxas
m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa)
cont = cont + 1
Next
Por:
For Each taxa In m_taxas
m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa, "Inserção de Taxas", 0)
If Len(m_valor_taxas(cont)) = 0 Then Exit Sub
cont = cont + 1
Next
c) Colocar a formatação de moeda das colunas K até a coluna Y da linha analisada.
Ao final de um cadastro de taxas, vamos inserir a seguinte linha de código para a aba Notas:
Sheets("Notas").Range("K" & i & ":Y" & i).Style = "Currency"
6º Ajuste) Aba Imposto de Renda
Conforme a Instrução Normativa RFB Nº 1585, de 31 de Agosto de 2015, vamos mudar a fórmula da coluna B para:
=SOMASES(TabelaNotas[Valor Operação];TabelaNotas[C/V];"V";TabelaNotas[Tipo];"Ação";TabelaNotas[Data Pregão];">="&[@Mês];TabelaNotas[Data Pregão];"<="&FIMMÊS([@Mês];0))
7º Ajuste) Macro split_inplit_v2
Vamos perguntar o tipo no começo da macro, pelas linhas de código abaixo.
Além disso, vamos verificar como está o que foi digitado pelo usuário da planilha, que tem como desejado receber o tipo como “Ação” ou “FII”. Observe:
tipo = InputBox("Qual o tipo desse ativo? [Ação/FII]")
If tipo = "" Then
Exit Sub
ElseIf tipo <> "Ação" And tipo <> "FII" Then
MsgBox ("Digite um tipo adequado [Ação/FII]")
Exit Sub
End If
Do meio pro final, ao invés de colocar “Ação”, vamos colocar a variável tipo no que for passado para a aba de Movimentações. Com o seguinte código:
Sheets("Movimentações").Cells(linha, 6) = tipo
As datas de pregão/liquidação do split/inplit será 1 data posterior à data de corte. No código, se reflete como:
Sheets("Movimentações").Cells(linha, 3) = data_corte + 1
Sheets("Movimentações").Cells(linha, 4) = data_corte + 1
Para assumir valores de proporção de split/inplit que não sejam inteiros, precisamos trocar a função de conversão de CInt por um somar 0 na inputbox, que vai transformar ele se for decimal, ou manter, se for inteiro. Observe:
de = InputBox("Digite a proporção de ""saída"" do evento. Ex: se for um split de 1 para 4, digite 1. Se for um inplit de 4 para 1, digite 4.") + 0
If de = "" Then Exit Sub
para = InputBox("Digite a proporção de ""chegada"" do evento. Ex: se for um split de 1 para 4, digite 4. Se for um inplit de 4 para 1, digite 1.") + 0
If para = "" Then Exit Sub
Para manter essa mudança, também deveremos modificar ao final da macro as casas decimais dessas variáveis (“de” e “para”). Observe:
If Sheets("Movimentações").Cells(linha, 9) = "" Then
Sheets("Movimentações").Cells(linha, 9) = "Ajuste na proporção - De: " & Format(de, "0.0000") & " Para: " & Format(para, "0.0000")
Else
Sheets("Movimentações").Cells(linha, 9) = Sheets("Movimentações").Cells(linha, 9) & " - Ajuste na proporção - De: " & de & " Para: " & para
End If
Nessa aula nós vamos falar sobre as ações Isentas de Imposto de Renda (pequenas e médias empresas) para complementar nossa planilha!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, galera! Essa é a aula de número 48 da nossa Série Controles para Bolsa de Valores!
Nessa aula nós vamos tratar das ações isentas de imposto de renda (pequenas e médias empresas).
Acompanhe abaixo com a gente:
Nova aba de Ações Isentas
Listamos aqui algumas ações e suas datas limites, respectivas, como um resumo para ajudar a dar esse pontapé inicial de controle de ações isentas.
Aba Imposto de Renda
Alienações
A parcela subtraída na fórmula abaixo (já criada anteriormente) vai desconsiderar das alienações (tabela notas) as ações isentas listadas na tabela acima (aba Ações Isentas), e ao mesmo tempo considerar a data limite de 31/12/2023.
=
SOMASES(TabelaNotas[Valor Operação];TabelaNotas[C/V];"V";TabelaNotas[Tipo];"Ação";TabelaNotas[Data Pregão];">="&[@Mês];TabelaNotas[Data Pregão];"<="&FIMMÊS([@Mês];0))
-
SOMA(SOMASES(TabelaNotas[Valor Operação];TabelaNotas[C/V];"V";TabelaNotas[Tipo];"Ação";TabelaNotas[Data Pregão];">="&[@Mês];TabelaNotas[Data Pregão];"<="&FIMMÊS([@Mês];0);TabelaNotas[Ticker];AcoesIsentas[Ação];TabelaNotas[Data Pregão];"<=31/12/2023"))
Resultado Ação
A parcela subtraída na fórmula abaixo (já criada anteriormente) vai desconsiderar o resultado ação (tabela movimentações) das ações isentas listadas na tabela acima (aba Ações Isentas), e, ao mesmo tempo, considerar a data limite de 31/12/2023.
=SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Ação";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0))
-
SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Ação";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0);TabelaMovimentacoes[Descrição];AcoesIsentas[Ação];TabelaMovimentacoes[Data Pregão];"<=31/12/2023"))
Lucro Isento
Da mesma forma, a parcela somada na fórmula abaixo do lucro isento (já criada anteriormente) vai levar em consideração as ações isentas listadas na tabela acima (aba Ações Isentas), e ao mesmo tempo considerar a data limite de 31/12/2023.
Repare que, essa parcela somada aqui é a mesma parcela subtraída na coluna de “Resultado Ação”, destacada acima.
=SE([@Alienações]<20000;SE([@[Resultado Ação]]>0;[@[Resultado Ação]];0);0)
+
SOMA(SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Tipo];"Resultado Ação";TabelaMovimentacoes[Data Pregão];">="&[@Mês];TabelaMovimentacoes[Data Pregão];"<="&FIMMÊS([@Mês];0);TabelaMovimentacoes[Descrição];AcoesIsentas[Ação];TabelaMovimentacoes[Data Pregão];"<=31/12/2023"))
Ajustes para ações “.N”
Está destacada abaixo a alteração necessária para que a aba de opções interprete as ações “.N” como as próprias ações.
Ou seja, caso haja uma ação “.N”, ela será substituída por vazio (“”) e a ação considerada será a normal.
Isso acontece porque precisamos torná-la oficial para buscar seu valor de histórico na bolsa com o nome original, pois a “.N” não existe.
Fórmula aplicada à célula D2:
=SUBSTITUIR(FILTRO(TabelaMovimentacoes[[Descrição]:[Tipo]];(TabelaMovimentacoes[Tipo]<>"Aporte")*(TabelaMovimentacoes[Tipo]<>"Provento")*(TabelaMovimentacoes[Tipo]<>"Retirada")*(TabelaMovimentacoes[Tipo]<>"Resultado Ação")*(TabelaMovimentacoes[Tipo]<>"Resultado Ação Day Trade")*(TabelaMovimentacoes[Tipo]<>"I.R.R.F. Ação")*(TabelaMovimentacoes[Tipo]<>"I.R.R.F. Ação Day Trade")*(TabelaMovimentacoes[Tipo]<>"I.R.R.F. BDR")*(TabelaMovimentacoes[Tipo]<>"Resultado BDR")*(TabelaMovimentacoes[Tipo]<>"Renda Fixa")*(TabelaMovimentacoes[Tipo]<>"Resultado FII")*(TabelaMovimentacoes[Tipo]<>"I.R.R.F. FII"));".N";"")
Está destacada também abaixo a alteração necessária para que a aba de cotações interprete as ações “.N” como as próprias ações originais, como explicado acima.
Fórmula aplicada à célula A2:
=CLASSIFICAR(ÚNICO(SUBSTITUIR(FILTRO(TabelaMovimentacoes[Descrição];(TabelaMovimentacoes[Tipo]<>"Aporte")*(TabelaMovimentacoes[Tipo]<>"Retirada"));".N";"")))
Está destacada também abaixo a alteração necessária para que a aba de resumo interprete as ações “.N” como as próprias ações originais, como explicado acima.
Fórmula aplicada à célula A2 (ativo):
=CLASSIFICAR(ÚNICO(SUBSTITUIR(FILTRO(TabelaMovimentacoes[Descrição];(TabelaMovimentacoes[Tipo]<>"Aporte")*(TabelaMovimentacoes[Tipo]<>"Retirada"));".N";"")))
A fórmula abaixo vai considerar agora essas ações “.N” para o tipo das ações.
Fórmula aplicada à célula C2 (tipo):
=SOMASES(TabelaMovimentacoes[Quantidade];TabelaMovimentacoes[Descrição];A2#;TabelaMovimentacoes[Tipo];B2#)
+
SOMASES(TabelaMovimentacoes[Quantidade];TabelaMovimentacoes[Descrição];A2#&".N";TabelaMovimentacoes[Tipo];B2#)
A fórmula abaixo vai considerar agora essas ações “.N” para o valor investido nas ações.
Fórmula aplicada à célula D2 (valor investido):
=-SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Descrição];A2#;TabelaMovimentacoes[Tipo];B2#)
-
SOMASES(TabelaMovimentacoes[Valor];TabelaMovimentacoes[Descrição];A2#&".N";TabelaMovimentacoes[Tipo];B2#)
Nessa aula nós vamos te mostrar como criar um Gráfico de Alocação de Classes e como criar um Painel de Macros para facilitar o uso delas!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala, galera! Essa é a aula de número 49 da nossa Série Controles para Bolsa de Valores!
Nessa aula nós vamos corrigir alguns problemas e fazer alguns ajustes que foram sugestões nos comentários, assim como nas aulas anteriores!
Depois disso vamos criar um gráfico de alocação de classe na nossa aba resumo, utilizando o gráfico de pizza.
Por fim nós vamos te mostrar como criar painel de macros no Excel para termos um “menu inicial”, assim teremos um facilitador para acessar as macros e não ficar bagunçado tendo que procurar uma a uma!
Ajustes
Na coluna de preço médio (V), vamos colocar um ajuste na fórmula, porque se houver a quantidade 0 de ativos, uma divisão por zero será considerada e gerará erro no cálculo do preço médio.
Observe abaixo a fórmula colocada na célula V2, e o destaque para as mudanças:
=SE(P2="Sem nenhum ativo";"";SEERRO(PROCV(P2#;E:M;9;0)/U2#;0))
Na coluna de Corretora 1 (G), vamos fazer um outro ajuste na fórmula porque estava acontecendo um erro de cálculo pelo fato de termos uma ação isenta (com final “.N”) inserida na aula anterior.
Observe abaixo a fórmula colocada na célula G2, e o destaque para as mudanças:
=SE(E2="";"";TRANSPOR(ÚNICO(FILTRO(TabelaMovimentacoes[Conta];(TabelaMovimentacoes[Descrição]=E2)+(TabelaMovimentacoes[Descrição]=E2&".N")))))
Na coluna W, dos códigos para cada tipo de ação (na célula W2), vamos fazer o seguinte ajuste ao final da fórmula:
=SE(P2="Sem nenhum ativo";"";SE(T2#="Ação";31;SE(T2#="BDR";49;SE(T2#="FII";73;SE(T2#="ETF";74;47)))))
Novo gráfico de alocação de classe
Em primeiro lugar vamos criar uma coluna nova na tabela da aba de Resumo, que vai representar o total somado da variação por tipo de ação. Ela vai ficar na coluna X, e na célula X2 vai ficar a fórmula:
=SOMASES(K:K;R:R;W2#)
Vamos copiar as colunas que fazem parte do gráfico de pizza de pizza que contém o resultado da carteira, e colar um pouco mais para a direita.
Formataremos da forma mostrada na videoaula, em passo a passo.
Ao final do procedimento teremos o seguinte gráfico pronto:
Repare que ele tem as mesmas fórmulas e mesmos resultados dados pelo gráfico “irmão” de pizza e pizza.
Agrupamento de colunas
1) No gráfico de pizza de pizza, selecionaremos as colunas AG até AO, e iremos em Dados > Agrupar (em estrutura de tópicos)
2) No gráfico de pizza criado nas etapas acima, selecionaremos as colunas AP até AX, e iremos em Dados > Agrupar (em estrutura de tópicos)
Painel de Macros
Onde:
Nessa aula vamos fazer a finalização da série e vamos te dar algumas dicas de utilização da planilha completa para que possa utilizar de forma eficiente!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Fala Galera! Essa é a aula de número 50 da nossa Série Controles para Bolsa de Valores!
Essa é a nossa última aula dessa série então vamos fazer alguns ajustes finais e dar algumas explicações da planilha.
Aqui vamos fazer alguns ajustes na planilha para que ela funcione mesmo quando não tenha nenhuma informação, pois você vai utilizar a planilha zerada, então ela precisa estar funcionando.
Ajustes de fórmulas
a) Aba Opções
Na coluna ativo (D), vamos acrescentar a fórmula ÚNICO no começo para ter os ativos de forma a tê-los sem repetição.
Além disso, além de citar os ativos que não queríamos pegar, vamos citar apenas os que queremos. Assim a fórmula fica mais enxuta e objetiva.
Por fim, acrescentaremos uma condição de alternativa de resultado de erro dado pela fórmula. As aspas duplas destacadas em negrito abaixo representam o resultado “vazio” ao invés de erro para essa fórmula.
Observe abaixo a fórmula colocada na célula D2, e o destaque para as mudanças:
=CLASSIFICAR(ÚNICO(SUBSTITUIR(FILTRO(TabelaMovimentacoes[[Descrição]:[Tipo]];(TabelaMovimentacoes[Tipo]="Ação")+(TabelaMovimentacoes[Tipo]="ETF")+(TabelaMovimentacoes[Tipo]="FII")+(TabelaMovimentacoes[Tipo]="BDR")+(TabelaMovimentacoes[Tipo]="Opção")+(TabelaMovimentacoes[Tipo]="Futuro")+(TabelaMovimentacoes[Tipo]="Termo");"");".N";"")))
b) Vamos levar essa fórmula para as abas de Cotações (célula A2) e para a aba de Resumo (célula A2 também), com uma leve alteração (considerando apenas a descrição da tabela movimentações):
=CLASSIFICAR(ÚNICO(SUBSTITUIR(FILTRO(TabelaMovimentacoes[[Descrição];(TabelaMovimentacoes[Tipo]="Ação")+(TabelaMovimentacoes[Tipo]="ETF")+(TabelaMovimentacoes[Tipo]="FII")+(TabelaMovimentacoes[Tipo]="BDR")+(TabelaMovimentacoes[Tipo]="Opção")+(TabelaMovimentacoes[Tipo]="Futuro")+(TabelaMovimentacoes[Tipo]="Termo");"");".N";"")))
c) Aba Resumo
Na coluna ativos prejuízo (S), vamos colocar a seguinte fórmula:
=FILTRO(N2:Q1048576;(O2:O1048576<>"")*SEERRO((O2:O1048576+Q2:Q1048576)<>0;0))
Ela vai produzir um resultado alternativo, caso o que estiver nas colunas O e Q sejam vazias. O resultado de erro se transformará em zero (0).
Gerenciador de Nomes
Precisaremos adaptar os nomes antigos dados para a células/intervalos da aba Resumo.
As fórmulas dos nomes agora vão levar em consideração as primeiras células de dados referente a cada coluna (U, S, V, T), considerando a lista de valores existentes.
Havendo mais de um valor, o deslocamento de tamanho do intervalo será respectivamente do tamanho da quantidade de valores daquela coluna.
Não havendo nenhum valor, o tamanho do intervalo será colocado como padrão de valor sendo 1, para interpretar apenas as células U2, S2, V2 e T2.
Observe os nomes e suas respectivas novas fórmulas:
a) Ativos Lucro
=DESLOC(Resumo!$U$2;;;MÁXIMO(CONT.VALORES(Resumo!$U:$U)-1;1))
b) Ativos Prejuízo
=DESLOC(Resumo!$S$2;;;MÁXIMO(CONT.VALORES(Resumo!$S:$S)-1;1))
c) Intervalo Lucro
=DESLOC(Resumo!$V$2;;;MÁXIMO(CONT.VALORES(Resumo!$V:$V)-1;1))
d) Intervalo Prejuízo
=DESLOC(Resumo!$T$2;;;MÁXIMO(CONT.VALORES(Resumo!$T:$T)-1;1))
Ajustar a primeira linha da tabela (linha_mov) na macro incluir_nota
Vamos substituir a linha de código abaixo em toda a macro de incluir_nota (inteira), em todas as aparições.
Assim, registraremos sempre a nota na linha correta: se houver algo na planilha, pegará a última linha preenchida + 1; caso não haja, registrará na quarta linha da aba.
Acompanhe:
Substituir:
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
Por:
If Sheets("Movimentações").Range("A4") = "" Then
linha_mov = 4
Else
linha_mov = Sheets("Movimentações").Range("A1000000").End(xlUp).Row + 1
End If
Executar código em VBA mais rápido (mudar em todas as macros)
Vamos acrescentar 4 linhas em todas as macros da nossa planilha, sendo 2 no começo, e duas ao final.
Essas linhas vão fazer a desativação da atualização de tela no começo e ativação ao final do código, assim como desativação do cálculo das fórmulas no começo e ativação ao final da macro.
Acompanhe:
Sub exemplo()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
‘codigo
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Além dessa modificação nas macros, vamos substituir em todas as macros, e nos dois módulos, como mostrado no vídeo:
Substituir: Exit Sub
Por: GoTo Fim
Incluir antes das linhas de desativação de tela e ativação de cálculos da planilha: Fim:
O final das macros ficará algo parecido como o seguinte:
Fim:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Macro evento da aba de Resumo
Vamos fazer algumas mudanças na macro de ativação da aba Resumo.
Agora ela vai excluir fórmulas a mais deixadas de forma errada, e ao mesmo tempo se precisar vai ajeitar as fórmulas se tiver menos linhas de fórmulas e mais ativos preenchidas na aba de cotações.
Abaixo temos o código final mostrado em vídeo:
Private Sub Worksheet_Activate()
linha_a = Sheets("Cotações").Range("A1048576").End(xlUp).Row
linha_b = Sheets("Cotações").Range("B1048576").End(xlUp).Row
If linha_a > linha_b Then
Sheets("Cotações").Range("B2").Formula2Local = "=SEERRO(HISTÓRICODEAÇÕES(A2;HOJE();;;0);SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-1;;;0);SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-2;;;0);SEERRO(HISTÓRICODEAÇÕES(A2;HOJE()-3;;;0);HISTÓRICODEAÇÕES(A2;HOJE()-4;;;0)))))"
If linha_a > 2 Then
Sheets("Cotações").Range("B2:B" & linha_a).FillDown
End If
ElseIf linha_a < linha_b Then
Sheets("Cotações").Range("B" & linha_a + 1 & ":B" & linha_b).Delete shift:=xlUp
End If
End Sub
Nessa aula, estamos apresentando a planilha Excel ações Bovespa, e no vídeo mostramos todas as suas funcionalidades, e também como preenchê-la da forma correta. Também pudemos acompanhar todas as suas abas, e especificidades dessa planilha ações.
Ela vai permitir que você faça o controle de suas ações, investimentos, e impostos relacionados a essas ações.
Essa planilha vai gerar uma série de aulas e posts que vão ensinar os passos e conceitos necessários para construí-la. Então, fique ligado no nosso canal do YouTube para mais detalhes da série, e para outros conteúdos de VBA, Excel e Power BI!
Para acessar outras publicações de VBA, clique aqui!
Expert em VBA e Power BI da Hashtag Treinamentos. Auxilia no suporte aos alunos da Hashtag Treinamentos e na criação de conteúdos para os que acompanham nossos canais.