Cotação Histórica de Ações no Excel com VBA

Hoje eu quero te mostrar como trazer cotação histórica de ações no Excel com VBA em qualquer versão do seu Excel!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Fala, Impressionadores! Na aula de hoje nós vamos fazer uma cotação histórica de ações no Excel com VBA.

Esse foi um tema muito pedido por vocês nos comentários, então vou te mostrar o passo a passo para fazer essa cotação histórica de ativos no Excel (para todas as versões).

Nessa aula nós vamos abordar variáveis no VBA, parâmetros de URL, tratamento de erros no VBA, matrizes, estrutura de repetição for, fórmulas de data no VBA, formatação e substituição de textos e criação de abas no VBA.

Como vai ser uma aula mais completa vamos conseguir abordar todos esses temas para que você consiga fazer esse histórico em qualquer versão do Excel.

Isso é importante, pois as versões atuais do Excel já possuem uma função própria para trazer esses valores, mas vamos fazer de uma maneira que todos consigam utilizar!

Para isso vamos utilizar uma API do Yahoo Finance para trazer esse histórico dos ativos que você escolher.

Ativando referências no VBA

Precisaremos utilizar uma biblioteca específica do Excel/VBA para nos auxiliar na aula de hoje. Portanto, precisaremos ativá-la para fazer esses procedimentos a seguir.

Precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.

Logo após você terá, com o VBA aberto, que:

• Ir na guia Ferramentas
• Referências…
• Marcar a caixinha correspondente ao “Microsoft WinHTTP Services” (como na foto abaixo)
• OK

Inserindo um Módulo no VBA

Para criar um código dentro de um módulo, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.

Como não temos nenhum módulo destinado ao tema da aula de hoje, e por questão de organização, vamos criar um módulo à parte.

Vamos na guia Inserir > Módulo, e uma caixa branca se abrirá.

Por padrão ele terá o nome de “Módulo 1”. Vamos mudar sua propriedade Name para “modCotacaoAtivos”.

Colocaremos a seguinte sub dentro dele:

Sub cotacaoAtivos()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

 
Dim Http As New WinHttpRequest
Dim abaAt As Worksheet
Dim novaAba As Worksheet
Dim ultLin As Long
Dim dataIni As LongLong
Dim dataFim As LongLong

 
Set abaAt = ThisWorkbook.Sheets("Ativos")

ultLin = abaAt.Range("A1000000").End(xlUp).Row

For i = 2 To ultLin

    'Epoch Time - Diferença entre uma data e o dia 1/1/1970 em segundos

    dataIni = DateDiff("s", "1/1/1970", abaAt.Cells(i, 2).Value)
    dataFim = DateDiff("s", "1/1/1970", abaAt.Cells(i, 3).Value)
    linkCot = "https://query1.finance.yahoo.com/v7/finance/download/" & abaAt.Cells(i, 1).Value _
            & ".SA?period1=" & dataIni & "&period2=" & dataFim & "&interval=1d&events=history"

    Http.Open "GET", linkCot, False
    Http.Send
    resp = Http.ResponseText
   

    On Error GoTo erroCriarAba
    Set novaAba = Worksheets(abaAt.Cells(i, 1).Value)
    On Error GoTo 0

passarErroCriarAba:

    novaAba.Cells.Clear
    linhasResp = Split(resp, Chr(10))
 

    For j = 0 To UBound(linhasResp)
        novaAba.Range("A" & j + 1 & ":G" & j + 1) = Split(linhasResp(j), ",")
    Next j

    novaAba.UsedRange.Replace What:=".", Replacement:="."
    novaAba.UsedRange.Replace What:="-", Replacement:="/"
    novaAba.Range("B:F").NumberFormat = "$ #,##0.00"
    novaAba.UsedRange.Columns.AutoFit

Next i

abaAt.Activate
MsgBox "Terminou!"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub

erroCriarAba:

    Set novaAba = Worksheets.Add()
    novaAba.Name = abaAt.Cells(i, 1).Value
    Resume passarErroCriarAba

End Sub

Explicação do código acima:

• A primeira linha do código diz respeito ao cálculo das planilhas abertas no Excel. Com esse comando, desativamos o cálculo automático do programa como um todo, ficando apenas ativados os cálculos manuais (quando entramos numa célula e damos enter, ela calcula)
• A segunda linha do código diz respeito à atualização de tela do Excel. Ou seja, desativamos a “piscada” de tela ao realizar ações com trocas de abas ou arquivos, e faz com que o código fique mais fuido
• Vamos dimensionar:
  • a variável “Http” como sendo do tipo um novo objeto do tipo WinHttpRequest, que é um
  • as variáveis “abaAt” e “novaAba” como sendo do tipo Worksheet
  • a variável “ultLin” como sendo do tipo Long (número grande)
  • as variáveis “dataIni” e “dataFim” como sendo do tipo LongLong (número muito grande)
  • Para entender melhor sobre os tipos de dados no VBA, visite esse link oficial
• A variável “abaAt” vai representar a aba “Ativos” do nosso arquivo em Excel, através de uma atribuição
• Armazenar na variável “ultLin” a última linha preenchida da coluna A da “abaAt”
• Utilizar de uma estrutura de repetição do tipo For, que vai alterar o valor da variável “i”, que vai representar no mínimo a linha 2 e vai até a linha (máxima) representada pela variável “ultLin”, que vai:
  • Armazenar na variável “dataIni” a diferença em segundos da data padrão (01/01/1970) e a data mostrada na célula de linha i e na 2ª coluna (coluna B)
  • Armazenar na variável “dataFim” a diferença em segundos da data padrão (01/01/1970) e a data mostrada na célula de linha i e na 3ª coluna (coluna C)
  • Armazenar na variável “linkCot” o resultado do link de pesquisa da cotação no site do yahoo disponibilizado, considerando as datas “dataIni” e “dataFim” para puxar as informações, e o nome da ação, que está na célula de linha i e na 1ª coluna (coluna A)
  • Vai ser invocado o objeto Http, que vai ter o comando para ser aberto (open), e acessará o link “linkCot”, com 3º argumento (ASync) sendo falso, que vão ser as configurações necessárias, de fato, antes de acessar o site
  • Http vai ser acionado com as configurações indicadas anteriormente
  • Armazenar na variável “resp” o texto resultante do acesso ao site “linkCot”
  • As próximas 3 linhas dizem respeito ao tratamento de erros no VBA para a ativação de uma aba dos ativos presentes na tabela
    • Se houver um ativo novo, não haverá uma aba nova para ele criada. Portanto, precisaremos direcionar um tratamento de erros que direcionará o código para a “parte” final (erroCriarAba), onde será criada uma nova aba para esse ativo, que receberá o nome do próprio ativo, e voltará para quase no mesmo local do código (ou seja, “subirá” passarErroCriarAba)
    • Se o ativo for o mesmo de antes (já presente na última atualização), nada precisará ser feito, e o tratamento de erros voltará para o padrão (caixa de erros normalmente mostrada pelo VBA)
  • Tudo estando correto na etapa anterior, teremos a resposta armazenada na variável “resp” e a aba ativada para o ativo da linha “i”. Estando na aba ativada, limparemos essa aba
  • Armazenar, na variável “linhasResp” o que for resultante da função Split (separar, em inglês), considerando a variável “resp” e o caractere 10, que é o que representa a quebra de linha
  • Utilizar de uma estrutura de repetição que vai de 0 até o limite de linhas que há dentro da variável (matriz) “linhasResp”, que vai:
    • Armazenar, linha a linha, na aba do ativo que está representado pela variável de linha “i”, as informações de cada que está presente no site “linkCot”
  • Vamos fazer a troca, através da função Replace de “.” por “.”. Mas na verdade o segundo ponto é entendido como vírgula, então seria, na prática, substituir “.” por “,” (como exemplificado no vídeo). Dessa forma serão acertados os números
  • Vamos fazer a troca de “-“ por “/”, para acertar as datas
  • Colocaremos formato numérico, começando por R$ das colunas B até F
  • Faremos o autoajuste de colunas da aba ativa
• Ao fazer esses procedimentos acima para todas as abas, o código seguirá
• Ativar a aba “abaAt”, a nossa aba “resumo”
• Gerar uma MsgBox que vai dizer “Terminou!” quando o código tiver sido executado normalmente com as etapas acima
• Por fim, iremos ativar atualização de tela do Excel que desativamos ao começo
• Também ativaremos o cálculo automático do Excel
• Fim do código com a instrução de saída Exit Sub

OBS: Para entender melhor sobre atribuição de objetos e variáveis, visite essa outro post.

OBS2: Para entender melhor sobre o uso de MsgBox no VBA, visite esse outro post.

OBS3: Para entender melhor sobre a estrutura For de repetição, visite esse post.

No começo do código temos o seguinte cenário para cada aba (abaixo apresentamos o exemplo da aba 1 – ENEV3):

Rodando o código, teremos:

Ao final do código, teremos, para todas as abas (abaixo apresentamos o exemplo da aba 1 – ENEV3):

Conclusão da Cotação Histórica de Ações no Excel

Nessa aula você lidou com os seguintes conceitos:

• Tipos de dados no VBA
• Variáveis no VBA
• Caixa de mensagem no VBA
• Estrutura de repetição do tipo For
• Tratamento de erros no VBA
• Formatação e substituição de textos
• Matrizes

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

Hashtag Treinamentos

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

---

Quer aprender mais sobre VBA com um Minicurso Gratuito?

---