Taxa de câmbio atualizada no Excel

Analisei repetidamente maneiras de importar dados da Internet para o Excel com atualização automática subsequente. Em particular:

• Em versões mais antigas do Excel 2007-2013, isso pode ser feito com uma solicitação direta da Web.
• A partir de 2010, isso pode ser feito de maneira muito conveniente com o suplemento Power Query.
  

A esses métodos nas versões mais recentes do Microsoft Excel, agora você pode adicionar outro – importar dados da Internet em formato XML usando funções internas.

XML (eXtensible Markup Language = Extensible Markup Language) é uma linguagem universal projetada para descrever qualquer tipo de dados. Na verdade, é texto simples, mas com tags especiais adicionadas a ele para marcar a estrutura de dados. Muitos sites fornecem fluxos gratuitos de seus dados em formato XML para qualquer um baixar. No site do Banco Central do Nosso País (www.cbr.ru), em particular, com a ajuda de uma tecnologia semelhante, são fornecidos dados sobre as taxas de câmbio de várias moedas. No site da Bolsa de Moscou (www.moex.com), você pode baixar cotações de ações, títulos e muitas outras informações úteis da mesma maneira.

Desde a versão 2013, o Excel tem duas funções para carregar diretamente dados XML da Internet em células da planilha: SERVIÇO DE INTERNET (SERVIÇO DE INTERNET) и FILTRO.XML (FILTROXML). Eles trabalham em pares – primeiro a função SERVIÇO DE INTERNET executa uma requisição para o site desejado e retorna sua resposta em formato XML, e então utilizando a função FILTRO.XML nós “analisamos” essa resposta em componentes, extraindo dela os dados que precisamos.

Vejamos o funcionamento dessas funções usando um exemplo clássico – importando a taxa de câmbio de qualquer moeda que precisamos para um determinado intervalo de datas do site do Banco Central do Nosso País. Usaremos a seguinte construção como um espaço em branco:

Aqui:

• As células amarelas contêm as datas de início e término do período de nosso interesse.
• O azul tem uma lista suspensa de moedas usando o comando Dados – Validação – Lista (Dados - Validação - Lista).
• Nas células verdes, usaremos nossas funções para criar uma string de consulta e obter a resposta do servidor.
• A tabela à direita é uma referência aos códigos de moeda (vamos precisar dela um pouco mais tarde).
  

Vamos!

Etapa 1. Formando uma string de consulta

Para obter as informações necessárias do site, você precisa solicitá-las corretamente. Vamos para www.cbr.ru e abrimos o link no rodapé da página principal' Recursos Técnicos'- Obtendo dados usando XML (http://cbr.ru/development/SXML/). Rolamos um pouco mais para baixo e no segundo exemplo (Exemplo 2) haverá o que precisamos – obter as taxas de câmbio para um determinado intervalo de datas:

Como você pode ver no exemplo, a string de consulta deve conter datas de início (data_req1) e finais (data_req2) do período de interesse para nós e o código da moeda (VAL_NM_RQ), cuja taxa queremos obter. Você pode encontrar os principais códigos de moeda na tabela abaixo:

Um guia completo de códigos de moeda também está disponível no site do Banco Central – consulte http://cbr.ru/scripts/XML_val.asp?d=0

Agora vamos formar uma string de consulta em uma célula em uma planilha com:

• o operador de concatenação de texto (&) para juntá-lo;
  
• Funcionalidades VPR (PROCV)para encontrar o código da moeda que precisamos no diretório;
  
• Funcionalidades TEXTO (TEXTO), que converte a data de acordo com o padrão de dia-mês-ano por meio de uma barra.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

Etapa 2. Execute a solicitação

Agora usamos a função SERVIÇO DE INTERNET (SERVIÇO DE INTERNET) com a string de consulta gerada como o único argumento. A resposta será uma longa linha de código XML (é melhor ativar a quebra de linha e aumentar o tamanho da célula se você quiser vê-la inteira):

Etapa 3. Analisando a resposta

Para facilitar a compreensão da estrutura dos dados de resposta, é melhor usar um dos analisadores XML online (por exemplo, http://xpather.com/ ou https://jsonformatter.org/xml-parser), que pode formatar visualmente o código XML, adicionando recuos a ele e destacando a sintaxe com cores. Então tudo ficará muito mais claro:

Agora você pode ver claramente que os valores do curso estão enquadrados por nossas tags ..., e as datas são atributos Data em etiquetas .

Para extraí-los, selecione uma coluna de dez (ou mais – se feito com margem) células vazias na planilha (porque foi definido um intervalo de data de 10 dias) e insira a função na barra de fórmulas FILTRO.XML (FILTROXML):

Aqui, o primeiro argumento é um link para uma célula com uma resposta do servidor (B8), e o segundo é uma string de consulta em XPath, uma linguagem especial que pode ser usada para acessar os fragmentos de código XML necessários e extraí-los. Você pode ler mais sobre a linguagem XPath, por exemplo, aqui.

É importante que após inserir a fórmula, não pressione Entrar, e o atalho de teclado Ctrl+Shift+Entrar, ou seja, insira-o como uma fórmula de matriz (as chaves ao redor serão adicionadas automaticamente). Se você tiver a versão mais recente do Office 365 com suporte para matrizes dinâmicas no Excel, uma simples Entrar, e você não precisa selecionar células vazias antecipadamente – a função em si terá quantas células forem necessárias.

Para extrair datas, faremos o mesmo – selecionaremos várias células vazias na coluna adjacente e usaremos a mesma função, mas com uma consulta XPath diferente, para obter todos os valores dos atributos Date das tags Record:

=FILTER.XML(B8;”//Registro/@Data”)

Agora no futuro, ao alterar as datas nas células originais B2 e B3 ou escolher uma moeda diferente na lista suspensa da célula B3, nossa consulta será atualizada automaticamente, consultando o servidor do Banco Central para novos dados. Para forçar uma atualização manualmente, você também pode usar o atalho de teclado Ctrl+outro+F9.

• Importar taxa de bitcoin para Excel via Power Query
• Importar taxas de câmbio da Internet em versões mais antigas do Excel