Taxa de canvi actualitzada a Excel

He analitzat repetidament maneres d'importar dades a Excel des d'Internet amb una actualització automàtica posterior. En particular:

• A les versions anteriors d'Excel 2007-2013, això es podria fer amb una sol·licitud web directa.
• A partir del 2010, això es pot fer de manera molt còmoda amb el complement Power Query.
  

A aquests mètodes de les últimes versions de Microsoft Excel, ara podeu afegir-ne un altre: importar dades d'Internet en format XML mitjançant funcions integrades.

XML (eXtensible Markup Language = Extensible Markup Language) és un llenguatge universal dissenyat per descriure qualsevol tipus de dades. De fet, és text senzill, però amb etiquetes especials afegits per marcar l'estructura de dades. Molts llocs ofereixen fluxos gratuïts de les seves dades en format XML perquè qualsevol pugui baixar-lo. Al lloc web del Banc Central del nostre país (www.cbr.ru), en particular, amb l'ajuda d'una tecnologia similar, es donen dades sobre els tipus de canvi de diverses monedes. Des del lloc web de la Borsa de Moscou (www.moex.com) podeu descarregar cotitzacions per a accions, bons i molta altra informació útil de la mateixa manera.

Des de la versió 2013, Excel té dues funcions per carregar directament dades XML d'Internet a les cel·les del full de treball: SERVEI WEB (SERVEI WEB) и FILTRE.XML (FILTERXML). Treballen per parelles, primer la funció SERVEI WEB executa una sol·licitud al lloc desitjat i retorna la seva resposta en format XML i després utilitza la funció FILTRE.XML "Analitzem" aquesta resposta en components, extreint-ne les dades que necessitem.

Vegem el funcionament d'aquestes funcions utilitzant un exemple clàssic: importar el tipus de canvi de qualsevol moneda que necessitem per a un interval de dates determinat des del lloc web del Banc Central del nostre país. Utilitzarem la següent construcció com a espai en blanc:

Aquí:

• Les cel·les grogues contenen les dates d'inici i finalització del període que ens interessa.
• El blau té una llista desplegable de monedes amb l'ordre Dades – Validació – Llista (Dades — Validació — Llista).
• A les cel·les verdes, utilitzarem les nostres funcions per crear una cadena de consulta i obtenir la resposta del servidor.
• La taula de la dreta és una referència als codis de moneda (la necessitarem una mica més endavant).
  

Som-hi!

Pas 1. Formació d'una cadena de consulta

Per obtenir la informació requerida del lloc, cal demanar-la correctament. Anem a www.cbr.ru i obrim l'enllaç al peu de pàgina de la pàgina principal' Recursos tècnics'- Obtenció de dades mitjançant XML (http://cbr.ru/development/SXML/). Ens desplacem una mica més avall i al segon exemple (Exemple 2) hi haurà el que necessitem: obtenir els tipus de canvi per a un interval de dates determinat:

Com podeu veure a l'exemple, la cadena de consulta ha de contenir dates d'inici (data_req1) i terminacions (data_req2) del període que ens interessa i el codi de moneda (VAL_NM_RQ), la taxa del qual volem obtenir. Podeu trobar els codis de moneda principals a la taula següent:

També hi ha disponible una guia completa dels codis de moneda al lloc web del Banc Central: vegeu http://cbr.ru/scripts/XML_val.asp?d=0

Ara formarem una cadena de consulta en una cel·la d'un full amb:

• l'operador de concatenació de text (&) per ajuntar-lo;
  
• Característiques VPR (VISUALITZACIÓ)per trobar el codi de la moneda que necessitem al directori;
  
• Característiques TEXT (TEXT), que converteix la data segons el patró donat dia-mes-any mitjançant una barra inclinada.

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

Pas 2. Executeu la sol·licitud

Ara fem servir la funció SERVEI WEB (SERVEI WEB) amb la cadena de consulta generada com a únic argument. La resposta serà una llarga línia de codi XML (és millor activar l'ajustament de paraula i augmentar la mida de la cel·la si el voleu veure sencer):

Pas 3. Anàlisi de la resposta

Per facilitar la comprensió de l'estructura de les dades de resposta, és millor utilitzar un dels analitzadors XML en línia (per exemple, http://xpather.com/ o https://jsonformatter.org/xml-parser), que pot formatar visualment el codi XML, afegint-hi sagnats i ressaltant la sintaxi amb color. Aleshores tot quedarà molt més clar:

Ara podeu veure clarament que els valors del curs estan emmarcats per les nostres etiquetes ..., i les dates són atributs Data en etiquetes .

Per extreure-les, seleccioneu una columna de deu cel·les buides (o més, si es fa amb un marge) del full (perquè s'ha establert un interval de dates de 10 dies) i introduïu la funció a la barra de fórmules. FILTRE.XML (FILTREXML):

Aquí, el primer argument és un enllaç a una cel·la amb una resposta del servidor (B8), i el segon és una cadena de consulta en XPath, un llenguatge especial que es pot utilitzar per accedir als fragments de codi XML necessaris i extreure'ls. Podeu llegir més informació sobre l'idioma XPath, per exemple, aquí.

És important que després d'introduir la fórmula, no premeu Enter, i la drecera del teclat Ctrl+Canviar+Enter, és a dir, introduïu-lo com a fórmula de matriu (les claus al voltant s'afegiran automàticament). Si teniu la darrera versió d'Office 365 amb suport per a matrius dinàmiques a Excel, aleshores és senzill Enter, i no cal que seleccioneu cel·les buides per endavant: la funció en si prendrà tantes cel·les com necessiti.

Per extreure les dates, farem el mateix: seleccionarem diverses cel·les buides a la columna adjacent i utilitzarem la mateixa funció, però amb una consulta XPath diferent, per obtenir tots els valors dels atributs Data de les etiquetes Record:

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

Ara, en el futur, en canviar les dates a les cel·les originals B2 i B3 o triar una moneda diferent a la llista desplegable de la cel·la B3, la nostra consulta s'actualitzarà automàticament, fent referència al servidor del Banc Central per obtenir dades noves. Per forçar una actualització manualment, també podeu utilitzar la drecera del teclat Ctrl+Alt+F9.

• Importeu la taxa de bitcoins a Excel mitjançant Power Query
• Importeu tipus de canvi d'Internet en versions anteriors d'Excel