Tipo de cambio actualizado en Excel

He analizado repetidamente formas de importar datos a Excel desde Internet con la posterior actualización automática. En particular:

• En versiones anteriores de Excel 2007-2013, esto se podía hacer con una solicitud web directa.
• A partir de 2010, esto se puede hacer de manera muy conveniente con el complemento Power Query.
  

A estos métodos en las últimas versiones de Microsoft Excel, ahora puede agregar otro: importar datos de Internet en formato XML usando funciones integradas.

XML (eXtensible Markup Language = Extensible Markup Language) es un lenguaje universal diseñado para describir cualquier tipo de datos. De hecho, es texto sin formato, pero con etiquetas especiales añadidas para marcar la estructura de datos. Muchos sitios ofrecen flujos gratuitos de sus datos en formato XML para que cualquiera los descargue. En el sitio web del Banco Central de Nuestro País (www.cbr.ru), en particular, con la ayuda de una tecnología similar, se brindan datos sobre los tipos de cambio de varias monedas. Desde el sitio web de la Bolsa de Moscú (www.moex.com) puede descargar cotizaciones de acciones, bonos y mucha otra información útil de la misma manera.

Desde la versión 2013, Excel tiene dos funciones para cargar directamente datos XML de Internet en las celdas de la hoja de cálculo: SERVICIO WEB (SERVICIO WEB) и FILTRO.XML (FILTROXML). Trabajan en parejas – primero la función SERVICIO WEB ejecuta una solicitud al sitio deseado y devuelve su respuesta en formato XML, y luego usa la función FILTRO.XML "analizamos" esta respuesta en componentes, extrayendo los datos que necesitamos de ella.

Veamos el funcionamiento de estas funciones utilizando un ejemplo clásico: importar el tipo de cambio de cualquier moneda que necesitemos para un intervalo de fechas determinado desde el sitio web del Banco Central de Nuestro País. Usaremos la siguiente construcción como un espacio en blanco:

Aquí:

• Las celdas amarillas contienen las fechas de inicio y finalización del período que nos interesa.
• El azul tiene una lista desplegable de monedas usando el comando Datos – Validación – Lista (Datos — Validación — Lista).
• En las celdas verdes, usaremos nuestras funciones para crear una cadena de consulta y obtener la respuesta del servidor.
• La tabla de la derecha es una referencia a los códigos de moneda (la necesitaremos un poco más adelante).
  

¡Vamonos!

Paso 1. Formar una cadena de consulta

Para obtener la información requerida del sitio, debe solicitarla correctamente. Vamos a www.cbr.ru y abrimos el enlace en el pie de página de la página principal' Recursos técnicos'- Obtener datos usando XML (http://cbr.ru/desarrollo/SXML/). Nos desplazamos un poco más abajo y en el segundo ejemplo (Ejemplo 2) habrá lo que necesitamos: obtener los tipos de cambio para un intervalo de fechas determinado:

Como puede ver en el ejemplo, la cadena de consulta debe contener fechas de inicio (fecha_req1) y terminaciones (fecha_req2) del período que nos interesa y el código de moneda (VAL_NM_RQ), la tasa de la que queremos obtener. Puede encontrar los principales códigos de moneda en la siguiente tabla:

Una guía completa de códigos de moneda también está disponible en el sitio web del Banco Central; consulte http://cbr.ru/scripts/XML_val.asp?d=0

Ahora formaremos una cadena de consulta en una celda de una hoja con:

• el operador de concatenación de texto (&) para unirlo;
  
• Caracteristicas VPR (VLOOKUP)para encontrar el código de la moneda que necesitamos en el directorio;
  
• Caracteristicas TEXTO (TEXTO), que convierte la fecha según el patrón dado día-mes-año a través de una barra inclinada.

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

Paso 2. Ejecutar la solicitud

Ahora usamos la función SERVICIO WEB (SERVICIO WEB) con la cadena de consulta generada como único argumento. La respuesta será una larga línea de código XML (es mejor activar el ajuste de línea y aumentar el tamaño de la celda si desea verlo completo):

Paso 3. Analizando la respuesta

Para facilitar la comprensión de la estructura de los datos de respuesta, es mejor utilizar uno de los analizadores XML en línea (por ejemplo, http://xpather.com/ o https://jsonformatter.org/xml-parser), que puede formatear visualmente el código XML, agregarle sangrías y resaltar la sintaxis con color. Entonces todo se volverá mucho más claro:

Ahora puede ver claramente que los valores del curso están enmarcados por nuestras etiquetas. ...y las fechas son atributos Fecha en etiquetas .

Para extraerlos, seleccione una columna de diez (o más, si se hace con un margen) celdas vacías en la hoja (porque se estableció un intervalo de fechas de 10 días) e ingrese la función en la barra de fórmulas FILTRO.XML (FILTRARXML):

Aquí, el primer argumento es un enlace a una celda con una respuesta del servidor (B8), y el segundo es una cadena de consulta en XPath, un lenguaje especial que se puede usar para acceder a los fragmentos de código XML necesarios y extraerlos. Puede leer más sobre el lenguaje XPath, por ejemplo, aquí.

Es importante que después de ingresar la fórmula, no presione Participary el atajo de teclado Ctrl+Shift+Participar, es decir, ingréselo como una fórmula matricial (las llaves que lo rodean se agregarán automáticamente). Si tiene la última versión de Office 365 con soporte para matrices dinámicas en Excel, entonces un simple Participar, y no necesita seleccionar celdas vacías por adelantado: la función en sí tomará tantas celdas como necesite.

Para extraer fechas, haremos lo mismo: seleccionaremos varias celdas vacías en la columna adyacente y usaremos la misma función, pero con una consulta XPath diferente, para obtener todos los valores de los atributos de fecha de las etiquetas de registro:

=FILTRO.XML(B8;”//Registro/@Fecha”)

Ahora, en el futuro, al cambiar las fechas en las celdas B2 y B3 originales o elegir una moneda diferente en la lista desplegable de la celda B3, nuestra consulta se actualizará automáticamente, remitiendo al servidor del Banco Central para obtener nuevos datos. Para forzar una actualización manualmente, también puede usar el atajo de teclado Ctrl+otro+F9.

• Importar tasa de bitcoin a Excel a través de Power Query
• Importe tipos de cambio de Internet en versiones anteriores de Excel