Blog

Postado em em 1 de maio de 2021

Preenchimento das Taxas com VBA 3 de 3 – Bolsa de Valores

Preenchimento das Taxas com VBA 3 de 3

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 Controles para Bolsa de Valores!

Nela vamos dar prosseguimento a série, e 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:

  • Array no VBA
  • WorksheetFunction
  • SumIf e SumIfs
  • CountIf e CountIfs
  • IF VBA
  • Estrutura de repetição For

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):

  • Voltando ao ponto da estrutura For mais externa (esquerda) do nosso código, temos a situação: se a célula que está na linha analisada, na coluna de notas (3), for igual ao digitado e armazenado na variável “nota”, queremos que o nosso código faça um outro loop, auxiliar, de 1 até a 11, que vá interpretar qual taxa estamos usando, e qual cálculo faremos para ela.
  • Vamos inserir uma nova matriz para interpretar os tipos de corretagem, chamada de “m_valor_opcoes”, com duas dimensões. Então, definiremos ela como “m_valor_opcoes(1 to 7)”
  • Também vamos inserir uma nova matriz para as opções propriamente ditas: “m_opcoes = Array(“Ação”, “ETF”, “FII”, “BDR”, “Opção”, “Futuro”, “Termo”)
  • Vamos perguntar ao usuário, logo no começo do código, através de uma InputBox, qual o tipo de corretagem e armazenar isso na variável “tipo_corretagem”
  • Se “Sim” for a resposta, vamos armazenar o valor dessas essas opções na nossa matriz “m_valor_opcoes”
  • Se for diferente de “Sim” e “Não”, sairemos do nosso código (para evitar erros de digitação)
  • Prosseguindo: Vamos inserir no If mais interno (que avalia as colunas das taxas de uma linha de interesse, adicionar o caso em que:
  • i) j é igual a 7, a taxa em que estaremos é a Taxa Operacional/Corretagem. Quando tivermos nesse caso:
  1. ii) se “tipo_corretagem” é igual a “Não”, a célula daquela linha, da coluna correspondente à coluna (14+j) receberá VALOR DA TAXA (j) / n_ativos , arredondado para 2 casas decimais (função Round)
  2. ii) se “tipo_corretagem” é igual a “Sim”,, precisaremos percorrer uma nova estrutura de repetição (de k = 0 até 6), para ver qual o tipo de corretagem. Se m_opcoes(k) for igual à célula daquela linha, na coluna 8 (tipo do ativo), então, a célula daquela linha, da coluna correspondente à coluna (14+j) receberá o valor de m_valor_opcoes(k+1).

Quando colocarmos o valor de corretagem, daquele tipo específico de opção de ativo, iremos sair desse For e seguiremos.

  • i) j é igual a 10, a taxa em que estaremos é a Taxa ISS, a célula daquela linha, da coluna correspondente à coluna (14+j) receberá VALOR DA TAXA (j) * TAXA DE CORRETAGEM (daquela linha) / m_valor_taxas (j), arredondado para 2 casas decimais (função Round)

Agora começaremos a tratar do “day trade”, no mesmo código:

  • Tudo entre a variável “linha”, até o final do nosso código, vamos copiar e colar novamente, ao final.
  • Vamos adaptar o código para a aba de “Notas Day Trade” (inserindo um comentário para melhor dividir o código/orientar)
  • Vamos selecionar essa última parte “debaixo” do código, a que acabamos de colar. Iremos em Editar > Substituir > Localizar: “Notas” > Substituir por: “Notas Day Trade” > Substituir todas
  • Vamos adicionar mais uma taxa (IRRF Day Trade na matriz de taxas “m_taxas”) a ser perguntada e colocaremos mais um “espaço” na matriz de taxas (m_valor_taxas)
  • Vamos trocar o nosso j para ir de 2 até 12
  • Interpretar o caso de termos o j = 12 (IRRF Day Trade). Para isso, iremos mandar ele registrar essa taxa na coluna 15, obrigatoriamente, e retiraremos a variável (14+j) anterior.
  • Criaremos um total_operacoes_v_dt
  • Adicionaremos um SumIfs para a aba de Day Trade, que ficará armazenado na variável “total_operacao_v_dt”
  • Se for uma venda, a conta feita levará em conta essa variável que vai conter o total_operacao_v_dt, porque vamos ratear apenas para as vendas de day trade, e não para todas as operações
  • Se não for venda, colocaremos 0, como explicado na aula anterior para o caso dessa taxa, e essa nota.
  • Vamos inserir um valor de contagem acumulada das nossas taxas, para evitar que os arredondamentos influenciem e nos levem a valores errados de taxas (52 min de vídeo).
  • Vamos inserir um contador c lá no começo da nossa parte da macro para Notas, começando como c = 1, e ele vai ser incrementado como c+1 no final do loop do For da aba Notas.
  • Vamos testar para ver se esse C é o mesmo valor de n_ativos (é o último ativo). Se sim, vamos percorrer todas as taxas, e tirar o valor acumulado do total. O valor que sobrar, será posto naquela célula. Se não (ou seja, não é o último ativo da nota em questão, vamos rodar o código, e ir acumulando os valores), vamos registrar as informações de taxas, e vamos acumular o valor, para guardar esse valor acumulado e depois utilizá-lo (caso sim, explicado anteriormente)
  • Para que isso funcione, precisamos criar uma nova matriz no começo do nosso código: “m_valor_acum(1 To 12)”, que vai armazenar os valores acumulados m_valor_acum (j).
  • Vamos, então, inserir em cada parte do nosso código uma linha que vai acumular esse valor alvo, dessa primeira parte (Notas) (minuto 58 do vídeo). Ela será: “m_valor_acum(j) = m_valor_acum(j) + Sheets(“Notas”).Cells(i , 14+j)
  • Para cuidar do detalhe de Vendas, em relação ao IRRF, vamos inserir um contador de vendas (c_v), junto daquele outro contador que adicionamos, começando com valor 1. Esse valor será adicionado de 1 unidade, se tivermos uma nota de Venda que tivermos daquele ativo.
  • Vamos ajustar também, o caso se o contador c_v for igual ao n_ativos_v, similar ao que foi explicado anteriormente (1h05min de vídeo)
  • Vamos inserir um contador diferente para day trade (c_v_dt), da mesma forma e lógica do criado para o c_v, explicado acima.
  • Repare que para day trade, o j vai de 2 até 12. Se j = 12, vamos colocar o valor acumulado na coluna 15. IRRF fixaremos na coluna 12. Fixaremos elas porque precisamos, para day trade.
  • Encerramos a parte de automação para taxas

Aqui estão alguns links de apoio para esse post:

 

Conclusão do Preenchimento das Taxas com VBA 3

Nessa aula finalizamos o preenchimento das taxas com VBA 3 de 3! Nessa aula vimos o final do preenchimento das taxas automaticamente, que começamos nas aulas anteriores. Então vamos incluir o rateamento de corretagem, que era algo que faltava.

Fizemos a devida separação para os valores das taxas operacional e ISS, para que possamos fazer o tratamento dessas taxas de forma separada. Abordamos também as taxas notas day trade.

Para fazer isso foi um pouco mais simples, pois já temos uma boa parte do código pronto. Então, aproveitamos para acrescentar os códigos com a função If e Else para fazer essas condições e tratamentos separados para cada ocasião específica.

O código ficou um pouco grande e complexo, mas o que vale aqui é entender que a parte teórica nós exercitamos em diversas aulas, e hoje nós pudemos fechar essa parte, que vai automatizar esse preenchimento.

Juntamos conhecimentos complexos de investimentos, com ferramental de VBA, fórmulas de Excel, parte lógica, matemática, e saiu esse código, que faz a operacionalização de forma muito rápida do cadastro de notas.

Até o próximo post! Fique ligado no nosso canal do Youtube para mais conteúdo de Excel e VBA! Um abraço!

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!


Quer aprender mais sobre VBA com um Minicurso Gratuito?


Vai começar o maior evento de Power BI da América Latina! Quer participar? Preencha seu e-mail abaixo: