Den Wechselkurs aktualiséiert an Excel

Ech hunn ëmmer erëm Weeër analyséiert fir Daten an Excel aus dem Internet mat spéider automateschen Aktualiséierung z'importéieren. Besonnesch:

• An eelere Versioune vun Excel 2007-2013, kéint dëst mat enger direkter Web Ufro gemaach ginn.
• Vun 2010 un, kann dëst ganz bequem mam Power Query Add-in gemaach ginn.
  

Zu dëse Methoden an de leschte Versioune vu Microsoft Excel kënnt Dir elo eng aner addéieren - Daten vum Internet am XML-Format importéieren mat agebaute Funktiounen.

XML (eXtensible Markup Language = Extensible Markup Language) ass eng universell Sprooch entwéckelt fir all Zort vun Daten ze beschreiwen. Tatsächlech ass et Kloertext, awer mat speziellen Tags bäigefüügt fir d'Datestruktur ze markéieren. Vill Site bidden gratis Streame vun hiren Donnéeën am XML-Format fir jiddereen erofzelueden. Op der Websäit vun der Zentralbank vun eisem Land (www.cbr.ru), besonnesch mat der Hëllef vun enger ähnlecher Technologie, ginn d'Donnéeën iwwer den Austausch vun de verschiddene Währungen uginn. Vun der Moskauer Exchange Websäit (www.moex.com) kënnt Dir Zitater fir Aktien, Obligatiounen a vill aner nëtzlech Informatiounen op déiselwecht Manéier eroflueden.

Zënter Versioun 2013 huet Excel zwou Funktiounen fir direkt XML Daten vum Internet an Aarbechtsblatzellen ze lueden: WEB SERVICE (WEBSERVICE) и FILTER.XML (FILTERXML). Si schaffen zu Pairen - éischt d'Funktioun WEB SERVICE fiert eng Ufro un de gewënschten Site aus a gëtt seng Äntwert am XML-Format zréck, a benotzt dann d'Funktioun FILTER.XML mir "parséieren" dës Äntwert a Komponenten, extrahéieren d'Donnéeën, déi mir brauchen.

Loosst eis d'Operatioun vun dëse Funktiounen mat engem klassesche Beispill kucken - den Austausch vun all Währung importéieren, déi mir fir e bestëmmten Datumintervall vun der Websäit vun der Zentralbank vun eisem Land brauchen. Mir benotzen déi folgend Konstruktioun als eidel:

Hei:

• Déi giel Zellen enthalen d'Start- an d'Enndatum vun der Period déi eis interesséiert.
• Déi blo huet eng Dropdown-Lëscht vu Währungen mat dem Kommando Donnéeën - Validatioun - Lëscht (Daten - Validatioun - Lëscht).
• An de gréngen Zellen benotze mir eis Funktiounen fir eng Ufro String ze kreéieren an d'Äntwert vum Server ze kréien.
• Den Dësch op der rietser Säit ass eng Referenz op Währungscodes (mir brauchen et e bësse méi spéit).
  

Lass!

Schrëtt 1. Formatioun eng Ufro String

Fir déi erfuerderlech Informatioun vum Site ze kréien, musst Dir se richteg froen. Mir ginn op www.cbr.ru an oppen de Link am Fousszeilen vun der Haaptsäit' Technesch Ressourcen - Kritt Daten mat XML (http://cbr.ru/development/SXML/). Mir scrollen e bësse méi niddereg an am zweete Beispill (Beispill 2) gëtt et wat mir brauchen - d'Wechselkursen fir e bestëmmten Datumintervall kréien:

Wéi Dir aus dem Beispill kënnt gesinn, muss d'Ufro String Startdatum enthalen (date_req1) an Endungen (date_req2) vun der Period vun Interesse fir eis an der Währung Code (VAL_NM_RQ), den Taux vun deem mir wëllen kréien. Dir kënnt d'HaaptWährungscodes an der Tabell hei ënnen fannen:

E komplette Guide fir Währungscodes ass och verfügbar op der Zentralbank Websäit - kuckt http://cbr.ru/scripts/XML_val.asp?d=0

Elo bilden mir eng Ufro String an enger Zell op engem Blat mat:

• den Text concatenation Bedreiwer (&) fir et zesummen;
  
• Eegeschaften VPR (VLOOKUP)fir de Code vun der Währung ze fannen déi mir am Verzeechnes brauchen;
  
• Eegeschaften TEXT (TEXT), deen den Datum no dem gegebene Muster Dag-Mount-Joer duerch e Slash konvertéiert.

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

Schrëtt 2. Exekutéieren der Demande

Elo benotze mir d'Funktioun WEB SERVICE (WEBSERVICE) mat der generéierter Ufro String als eenzegt Argument. D'Äntwert wäert eng laang Zeil vum XML-Code sinn (et ass besser fir Wuertwrap auszeschalten an d'Zellgréisst ze vergréisseren wann Dir se a senger Ganzheet wëllt gesinn):

Schrëtt 3. Parsing d'Äntwert

Fir et méi einfach ze maachen d'Struktur vun den Äntwertdaten ze verstoen, ass et besser ee vun den Online XML Parser ze benotzen (zum Beispill http://xpather.com/ oder https://jsonformatter.org/xml-parser), deen den XML-Code visuell formatéiere kann, d'Indents dozou bäidroen an d'Syntax mat Faarf markéieren. Da gëtt alles vill méi kloer:

Elo kënnt Dir kloer gesinn datt d'Coursewäerter vun eisen Tags encadréiert ginn ..., an Datume sinn Attributer Datum in tags .

Fir se ze extrahieren, wielt eng Kolonn vun zéng (oder méi - wann et mat enger Marge gemaach gëtt) eidel Zellen op der Blat (well en 10-Deeg Datumintervall festgeluecht gouf) a gitt d'Funktioun an der Formelbar FILTER.XML (FILTERXML):

Hei ass dat éischt Argument e Link op eng Zell mat enger ServerÄntwert (B8), an déi zweet ass eng Ufro String an XPath, eng speziell Sprooch déi benotzt ka ginn fir Zougang zu den néidege XML Code Fragmenter ze kréien an ze extrahieren. Dir kënnt méi iwwer d'Xpath Sprooch liesen, zum Beispill hei.

Et ass wichteg datt nodeems Dir d'Formel aginn hutt, net dréckt gitt, an d'Tastatur Ofkiirzung Ctrl+Verréckelung+gitt, also gitt et als Arrayformel an (déi gekrauselt Klammern ronderëm ginn automatesch derbäi). Wann Dir déi lescht Versioun vum Office 365 mat Ënnerstëtzung fir dynamesch Arrays an Excel hutt, dann eng einfach gitt, an Dir braucht keng eidel Zellen am Viraus ze wielen - d'Funktioun selwer wäert esou vill Zellen huelen wéi se brauch.

Fir Datumen ze extrahieren, maache mir datselwecht - mir wäerte e puer eidel Zellen an der ugrenzender Kolonn auswielen a benotzen déiselwecht Funktioun, awer mat enger anerer XPath Ufro, fir all Wäerter vun den Date Attributer aus de Record Tags ze kréien:

=FILTER.XML(B8;"//Record/@Datum")

Elo an der Zukunft, wann Dir d'Datume an den ursprénglechen Zellen B2 a B3 ännert oder eng aner Währung an der Dropdown-Lëscht vun der Zell B3 auswielen, gëtt eis Ufro automatesch aktualiséiert, a bezitt sech op den Zentralbankserver fir nei Donnéeën. Fir en Update manuell ze forcéieren, kënnt Dir zousätzlech d'Tastatur Ofkiirzung benotzen Ctrl+Alt+F9.

• Import Bitcoin Taux an Excel iwwer Power Query
• Import Wechselkursen vum Internet an eeler Versioune vun Excel