Quer aprender a usar a API do Banco Central com VBA? Nessa aula eu vou te mostrar para exemplificar APIs com paginação!
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 vamos lidar com a API do Banco Central com VBA para obter cotações ao longo do tempo!
A API apresentada na aula de hoje é uma API com paginação. Ou seja, temos que fazer requisições em partes (páginas) para obter as informações.
A ideia aqui é lidar com uma API quando encontrar requisições quando o resultado tem mais de uma página.
Pronto para usar a API do Banco Central no VBA para automatizar cotações no VBA?
Inicialmente, temos uma tabela pré-montada no Excel, e queremos retornar data de cotação, cotação de compra e cotação de venda.
Essas informações serão retornadas do site do Banco Central, vindas da API executada aqui na aula de hoje.
Abaixo mostramos a tabela vazia, antes de executarmos o código, que vai receber as informações. Veja:
Ativando referências no VBA
Importantíssimo para a aula de hoje, e para o desempenho da API de forma correta é utilizar duas referências específicas no VBA.
Precisamos ativar as referências correspondentes ao “Microsoft WinHTTP Services” (como na foto abaixo) e referente também ao “Microsoft Scripting Runtime”
Após ativar as referências no VBA, poderemos partir para o código que vai desempenhar a API de taxas de câmbio referente ao post de hoje.
O código que vai lidar com APIs com paginação, mais especificamente falando uma API do Banco Central no VBA:
Sub lerAPIBancoCentral()
Dim requisicao As New WinHttpRequest Dim resposta As Object Dim url As String, parametros As String Dim linha As Long, skip As Long, salto As Long
skip = 0 salto = 100
Do While True ' Definir a URL e os parâmetros url = "https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)" parametros = "?@dataInicial='11-27-2017'&@dataFinalCotacao='09-16-2022'&$top=" & salto & "&$skip=" & skip & "&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao"
' Enviar a requisição requisicao.Open "Get", url & parametros
'requisicao.SetRequestHeader requisicao.Send
' Tratamento de erros If requisicao.Status <> 200 Then MsgBox "Erro: " & requisicao.ResponseText Exit Sub End If
' Converter o JSON Set resposta = JsonConverter.ParseJson(requisicao.ResponseText)
' Tratar as informações Dim value As Collection Dim itemCotacao As Dictionary
Set value = resposta("value")
If value.Count = 0 Then Exit Do
linha = Range("A1048576").End(xlUp).Row + 1
For Each itemCotacao In value
Cells(linha, 1).value = itemCotacao("dataHoraCotacao") Cells(linha, 2).value = itemCotacao("cotacaoCompra") Cells(linha, 3).value = itemCotacao("cotacaoVenda") linha = linha + 1
Next
skip = skip + salto
Loop
Range("A:A").NumberFormat = "dd/mm/yyyy"
End Sub
Comentários a respeito do código acima:
Dimensionar:
a variável “requisicao” como sendo do tipo um novo objeto do tipo WinHttpRequest, que é um objeto da biblioteca que ativamos, a “Microsoft WinHTTP Services”
a variável “resposta” como um objeto
as variáveis “url” e “parametros” do tipo texto
as variáveis “linha”, “skip” e “salto” como números grandes (do tipo Long)
Armazenar o valor 0 na variável “skip”
Armazenar o valor 100 na variável “salto”
Abrir uma estrutura de repetição do tipo Do While, com a condição “padrão” para continuar for verdadeira. No meio da estrutura, terá uma condição que fará o código sair dessa estrutura, e pontuaremos a seguir.
Armazenar na variável “url” o texto que corresponde ao site exatamente que vamos acessar para pegar as informações, que é o link disponibilizado, juntamente e composto com as variáveis “skip” e “salto”, onde definiremos quantos “itens” serão gerados em cada página da API
Abrir o objeto que é representado pela variável “requisicao”, passando os argumentos “url” e “parametros”, dos quais ela vai pegar informações (get, em inglês)
Passar para o cabeçalho da “requisicao” os parâmetros “padrão” informados pela API
Dar o comando de “enviar” requisição (send, em inglês), que é de fato quando o VBA envia a requisição para o site
Com um simples tratamento de erro, vamos verificar através de uma estrutura If se a propriedade “Status” da variável “requisicao” é diferente de 200 (porque essa resposta é a esperada quando uma resposta válida é retornada pela API). Caso o “Status” seja diferente de 200:
Uma mensagem será gerada na tela informando “Erro”.
Configurar a variável “resposta” para receber o objeto da biblioteca JSON, que vai ser responsável por retornar a resposta, em texto, da “requisicao”
Dimensionar a variável “value” como uma variável do tipo “Collection”, da biblioteca JSON
Dimensionar a variável “itemCotacao” como uma variável do tipo “Dictionary”, da biblioteca JSON
Configurar a variável “value” como sendo o termo “value” da requisição “resposta”
Usar uma estrutura de repetição para verificar a contagem de itens que estão na variável “value” no momento do código. Se a contagem for igual a 0, o código sairá da estrutura de repetição Do While, aberta no começo do código
Caso a contagem de itens da varável “value” seja maior do que 0, continuaremos com o código
Armazenar na variável “linha” o valor da primeira linha vazia da tabela que vai receber as informações, baseada na última linha da célula da coluna A, adicionando 1 unidade
Utilizar uma estrutura de repetição do tipo For Each, percorrendo todos os itens individuais (itemCotacao) no total de itens (value), fazendo as seguintes ações:
Na célula de linha dada pela variável “linha” e coluna 1 o valor resultante da consulta da variável “itemCotacao”, buscando o item “dataHoraCotacao”
Na célula de linha dada pela variável “linha” e coluna 2 o valor resultante da consulta da variável “itemCotacao”, buscando o item “cotacaoCompra”
Na célula de linha dada pela variável “linha” e coluna 3 o valor resultante da consulta da variável “itemCotacao”, buscando o item “cotacaoVenda”
Adicionar 1 unidade na variável linha, para pular para uma próxima linha de preenchimento
Para fazer a “mudança de página”, vamos adicionar o próximo “salto” na nossa variável de “skip”, definida inicialmente.
Se inicia a nova estrutura, a partir de um novo “skip”, e um novo “salto”, repetindo todas as ações descritas acima, até termos “0” itens na próxima “página”
Após encerrar todas as páginas e trazer todas as informações, formatar a coluna A da nossa tabela com o formato de data, que é “DD/MM/AAAA” (onde D = dia, M = mês e A = ano)
Hoje você aprendeu a lidar com o uso de API no VBA com paginação.
Mais especificamente falando lidou com uma API do Banco Central para obter cotações no VBA. Extraiu diretamente informações do site do Banco Central para o seu Excel com uma API!
Valeu, Impressionadores! 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 básico gratuito?
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.
Procure o que você quiser sobre Excel, VBA, Power BI ou Python:
Aprenda tudo de Excel para se tornar uma referência no Mercado de Trabalho
Leve os treinamentos da Hashtag para dentro da sua Empresa