Taxa de cambio actualizada en Excel

Analizei varias veces formas de importar datos a Excel desde Internet coa posterior actualización automática. En particular:

• Nas versións anteriores de Excel 2007-2013, isto podería facerse cunha solicitude web directa.
• A partir de 2010, pódese facer de xeito moi cómodo co complemento Power Query.
  

A estes métodos nas últimas versións de Microsoft Excel, agora pode engadir outro: importar datos de Internet en formato XML mediante funcións integradas.

XML (eXtensible Markup Language = Extensible Markup Language) é unha linguaxe universal deseñada para describir calquera tipo de datos. De feito, é texto sinxelo, pero con etiquetas especiais engadidas para marcar a estrutura de datos. Moitos sitios ofrecen fluxos gratuítos dos seus datos en formato XML para que calquera poida descargar. No sitio web do Banco Central do noso país (www.cbr.ru), en particular, coa axuda dunha tecnoloxía similar, ofrécense datos sobre os tipos de cambio de varias moedas. Desde o sitio web da Bolsa de Moscova (www.moex.com) podes descargar cotizacións de accións, bonos e moita outra información útil do mesmo xeito.

Desde a versión 2013, Excel ten dúas funcións para cargar directamente datos XML de Internet nas celas da folla de traballo: SERVIZO WEB (SERVIZO WEB) и FILTRO.XML (FILTERXML). Eles traballan en parellas - primeiro a función SERVIZO WEB executa unha solicitude ao sitio desexado e devolve a súa resposta en formato XML, e despois emprega a función FILTRO.XML "analizamos" esta resposta en compoñentes, extraendo dela os datos que necesitamos.

Vexamos o funcionamento destas funcións usando un exemplo clásico: importar o tipo de cambio de calquera moeda que necesitemos para un determinado intervalo de datas desde o sitio web do Banco Central do noso país. Usaremos a seguinte construción como espazo en branco:

Aquí:

• As celas amarelas conteñen as datas de inicio e finalización do período que nos interesa.
• O azul ten unha lista despregable de moedas usando o comando Datos – Validación – Lista (Datos — Validación — Lista).
• Nas celas verdes, usaremos as nosas funcións para crear unha cadea de consulta e obter a resposta do servidor.
• A táboa da dereita é unha referencia aos códigos de moeda (necesitarémolo un pouco máis adiante).
  

Imos!

Paso 1. Formación dunha cadea de consulta

Para obter a información requirida do sitio, cómpre preguntala correctamente. Imos a www.cbr.ru e abrimos a ligazón no pé de páxina da páxina principal' Recursos técnicos - Obtención de datos mediante XML (http://cbr.ru/development/SXML/). Desprazámonos un pouco máis abaixo e no segundo exemplo (Exemplo 2) haberá o que necesitamos: obter os tipos de cambio para un determinado intervalo de datas:

Como podes ver no exemplo, a cadea de consulta debe conter datas de inicio (data_req1) e desinencias (data_req2) do período que nos interesa e o código de moeda (VAL_NM_RQ), a taxa que queremos obter. Podes atopar os principais códigos de moeda na seguinte táboa:

Tamén está dispoñible unha guía completa dos códigos de moeda no sitio web do Banco Central: consulte http://cbr.ru/scripts/XML_val.asp?d=0

Agora formaremos unha cadea de consulta nunha cela dunha folla con:

• o operador de concatenación de texto (&) para xuntalo;
  
• características VPR (BÚSQUEDA V)para atopar o código da moeda que necesitamos no directorio;
  
• características TEXTO (TEXTO), que converte a data segundo o patrón dado día-mes-ano mediante unha barra.

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

Paso 2. Executar a solicitude

Agora usamos a función SERVIZO WEB (SERVIZO WEB) coa cadea de consulta xerada como único argumento. A resposta será unha longa liña de código XML (é mellor activar o axuste de palabras e aumentar o tamaño da cela se queres velo na súa totalidade):

Paso 3. Analizando a resposta

Para facilitar a comprensión da estrutura dos datos de resposta, é mellor utilizar un dos analizadores XML en liña (por exemplo, http://xpather.com/ ou https://jsonformatter.org/xml-parser), que pode formatear visualmente o código XML, engadindo sangrías e resaltando a sintaxe con cor. Entón todo quedará moito máis claro:

Agora podes ver claramente que os valores do curso están enmarcados polas nosas etiquetas ..., e as datas son atributos data en etiquetas .

Para extraelos, seleccione unha columna de dez (ou máis, se se fai cunha marxe) celas baleiras da folla (porque se estableceu un intervalo de datas de 10 días) e introduza a función na barra de fórmulas. FILTRO.XML (FILTROXML):

Aquí, o primeiro argumento é unha ligazón a unha cela cunha resposta do servidor (B8), e o segundo é unha cadea de consulta en XPath, unha linguaxe especial que se pode usar para acceder aos fragmentos de código XML necesarios e extraelos. Podes ler máis sobre a linguaxe XPath, por exemplo, aquí.

É importante que despois de introducir a fórmula, non prema Entrar, e o atallo de teclado Ctrl+Desprazarse+Entrar, é dicir, introdúceo como unha fórmula matricial (as chaves que o rodean engadiranse automaticamente). Se tes a última versión de Office 365 con soporte para matrices dinámicas en Excel, entón é sinxelo Entrar, e non precisa seleccionar celas baleiras con antelación: a propia función ocupará tantas celas como necesite.

Para extraer datas, faremos o mesmo: seleccionaremos varias celas baleiras na columna adxacente e usaremos a mesma función, pero cunha consulta XPath diferente, para obter todos os valores dos atributos Data das etiquetas de rexistro:

=FILTRO.XML(B8;”//Rexistro/@Data”)

Agora, no futuro, ao cambiar as datas nas celas orixinais B2 e B3 ou escoller unha moeda diferente na lista despregable da cela B3, a nosa consulta actualizarase automaticamente, facendo referencia ao servidor do Banco Central para obter novos datos. Para forzar unha actualización manualmente, tamén pode usar o atallo de teclado Ctrl+alt+F9.

• Importar a taxa de bitcoins a Excel mediante Power Query
• Importar tipos de cambio de Internet en versións antigas de Excel