Päivitetty valuuttakurssi Excelissä

Olen toistuvasti analysoinut tapoja tuoda tietoja Exceliin Internetistä myöhemmillä automaattisilla päivityksillä. Erityisesti:

• Excelin vanhemmissa versioissa 2007–2013 tämä voidaan tehdä suoralla verkkopyynnöllä.
• Vuodesta 2010 alkaen tämä voidaan tehdä erittäin kätevästi Power Query -apuohjelman avulla.
  

Näihin Microsoft Excelin uusimpien versioiden menetelmiin voit nyt lisätä toisen – tietojen tuomisen Internetistä XML-muodossa sisäänrakennettujen toimintojen avulla.

XML (eXtensible Markup Language = Extensible Markup Language) on universaali kieli, joka on suunniteltu kuvaamaan kaikenlaista dataa. Itse asiassa se on pelkkää tekstiä, mutta siihen on lisätty erityisiä tunnisteita tietorakenteen merkitsemiseksi. Monet sivustot tarjoavat ilmaisia ​​tietovirtoja XML-muodossa kenen tahansa ladattavaksi. Maamme keskuspankin verkkosivuilla (www.cbr.ru) annetaan erityisesti samanlaisen tekniikan avulla tietoja eri valuuttojen valuuttakursseista. Moskovan pörssin verkkosivuilta (www.moex.com) voit ladata samalla tavalla tarjouksia osakkeista, joukkovelkakirjoista ja paljon muuta hyödyllistä tietoa.

Vuodesta 2013 lähtien Excelissä on kaksi toimintoa XML-tietojen lataamiseen suoraan Internetistä laskentataulukon soluihin: VERKKOPALVELU (VERKKOPALVELU) и FILTER.XML (FILTERXML). Ne toimivat pareittain – ensin toiminto VERKKOPALVELU suorittaa pyynnön haluttuun sivustoon ja palauttaa vastauksensa XML-muodossa ja käyttää sitten toimintoa FILTER.XML "Jäsensämme" tämän vastauksen osiin ja poimimme siitä tarvitsemamme tiedot.

Katsotaanpa näiden toimintojen toimintaa klassisella esimerkillä – tuomalla minkä tahansa tarvitsemamme valuutan vaihtokurssi tietylle päivämäärävälille Maamme keskuspankin verkkosivuilta. Käytämme aihiona seuraavaa rakennetta:

Tässä:

• Keltaiset solut sisältävät meitä kiinnostavan ajanjakson alkamis- ja päättymispäivät.
• Sinisessä on avattava luettelo valuutoista komennolla Tiedot – Validointi – Luettelo (Data — Validation — List).
• Vihreissä soluissa käytämme toimintojamme kyselymerkkijonon luomiseen ja palvelimen vastauksen saamiseen.
• Oikealla oleva taulukko on viittaus valuuttakoodeihin (tarvitsemme sitä hieman myöhemmin).
  

Mennään!

Vaihe 1. Kyselymerkkijonon muodostaminen

Saadaksesi tarvittavat tiedot sivustolta, sinun on kysyttävä niitä oikein. Menemme osoitteeseen www.cbr.ru ja avaamme linkin pääsivun alatunnisteessa. Tekniset resurssit'- Tietojen hakeminen XML:n avulla (http://cbr.ru/development/SXML/). Vieritämme hieman alemmas ja toisessa esimerkissä (esimerkki 2) on se, mitä tarvitsemme – tietyn päivämäärävälin vaihtokurssit:

Kuten esimerkistä näet, kyselymerkkijonon tulee sisältää aloituspäivämäärät (date_req1) ja päätteet (date_req2) meitä kiinnostavasta ajanjaksosta ja valuuttakoodista (VAL_NM_RQ), jonka haluamme saada. Löydät tärkeimmät valuuttakoodit alla olevasta taulukosta:

Täydellinen opas valuuttakoodeista on myös saatavilla keskuspankin verkkosivuilla – katso http://cbr.ru/scripts/XML_val.asp?d=0

Nyt muodostamme kyselymerkkijonon arkin soluun, jossa on:

• tekstin ketjutusoperaattori (&) sen yhdistämiseksi;
  
• Ominaisuudet VPR (HAKU)löytääksemme tarvitsemamme valuutan koodin hakemistosta;
  
• Ominaisuudet TEKSTI (TEKSTI), joka muuntaa päivämäärän annetun kaavan mukaan päivä-kuukausi-vuosi kauttaviivalla.

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

Vaihe 2. Suorita pyyntö

Nyt käytämme toimintoa VERKKOPALVELU (VERKKOPALVELU) luotu kyselymerkkijono on ainoa argumentti. Vastaus on pitkä rivi XML-koodia (on parempi ottaa rivitys käyttöön ja lisätä solun kokoa, jos haluat nähdä sen kokonaisuudessaan):

Vaihe 3. Vastauksen jäsentäminen

Jotta vastaustietojen rakennetta olisi helpompi ymmärtää, on parempi käyttää jotakin online-XML-jäsentimistä (esimerkiksi http://xpather.com/ tai https://jsonformatter.org/xml-parser), joka voi visuaalisesti muotoilla XML-koodia lisäämällä siihen sisennykset ja korostamalla syntaksia väreillä. Sitten kaikki tulee paljon selkeämmäksi:

Nyt voit selvästi nähdä, että kurssiarvot on kehystetty tunnisteillamme ..., ja päivämäärät ovat määritteitä Päivämäärä tunnisteissa .

Poimi ne valitsemalla arkilta kymmenen (tai useamman – jos tehdään marginaalilla) tyhjän solun sarake (koska 10 päivän päivämääräväli oli asetettu) ja kirjoittamalla funktio kaavapalkkiin FILTER.XML (SUODATTAAXML):

Tässä ensimmäinen argumentti on linkki soluun, jossa on palvelinvastaus (B8), ja toinen on kyselymerkkijono XPathissa, erikoiskielessä, jota voidaan käyttää tarvittavien XML-koodinpätkien saamiseen ja niiden purkamiseen. Voit lukea lisää XPath-kielestä esimerkiksi täältä.

On tärkeää, että älä paina kaavan syöttämisen jälkeen enter, ja pikanäppäin Ctrl+siirtyminen+enter, eli kirjoita se taulukkokaavana (sen ympärillä olevat kiharat aaltosulut lisätään automaattisesti). Jos sinulla on Office 365:n uusin versio, joka tukee dynaamisia taulukoita Excelissä, niin yksinkertainen enter, eikä sinun tarvitse valita tyhjiä soluja etukäteen – toiminto itse ottaa niin monta solua kuin se tarvitsee.

Päivämäärien poimimiseksi teemme samoin – valitsemme useita tyhjiä soluja viereisestä sarakkeesta ja käytämme samaa funktiota, mutta eri XPath-kyselyllä saadaksemme kaikki päivämäärä-attribuuttien arvot Record-tageista:

=SUODATIN.XML(B8;”//Tieto/@Päiväys)

Nyt tulevaisuudessa, kun muutat päivämääriä alkuperäisissä soluissa B2 ja B3 tai valitset toisen valuutan solun B3 pudotusvalikosta, kyselymme päivittyy automaattisesti viittaamalla keskuspankin palvelimeen uusien tietojen saamiseksi. Voit pakottaa päivityksen manuaalisesti käyttämällä lisäksi pikanäppäintä Ctrl+muut+F9.

• Tuo bitcoin-kurssi Exceliin Power Queryn kautta
• Tuo vaihtokurssit Internetistä vanhemmissa Excel-versioissa