Na-update ang exchange rate sa Excel

Paulit-ulit kong sinuri ang mga paraan upang mag-import ng data sa Excel mula sa Internet na may kasunod na awtomatikong pag-update. Sa partikular:

• Sa mga mas lumang bersyon ng Excel 2007-2013, maaari itong gawin sa isang direktang kahilingan sa web.
• Simula noong 2010, magagawa ito nang napakaginhawa gamit ang Power Query add-in.
  

Sa mga pamamaraang ito sa mga pinakabagong bersyon ng Microsoft Excel, maaari ka na ngayong magdagdag ng isa pa - ang pag-import ng data mula sa Internet sa XML na format gamit ang mga built-in na function.

Ang XML (eXtensible Markup Language = Extensible Markup Language) ay isang pangkalahatang wika na idinisenyo upang ilarawan ang anumang uri ng data. Sa katunayan, ito ay plain text, ngunit may mga espesyal na tag na idinagdag dito upang markahan ang istraktura ng data. Maraming mga site ang nagbibigay ng mga libreng stream ng kanilang data sa XML na format para ma-download ng sinuman. Sa website ng Central Bank of Our Country (www.cbr.ru), sa partikular, sa tulong ng isang katulad na teknolohiya, ang data sa mga halaga ng palitan ng iba't ibang mga pera ay ibinigay. Mula sa website ng Moscow Exchange (www.moex.com) maaari kang mag-download ng mga quote para sa mga stock, mga bono at maraming iba pang kapaki-pakinabang na impormasyon sa parehong paraan.

Mula noong bersyon 2013, may dalawang function ang Excel para sa direktang paglo-load ng XML data mula sa Internet sa mga cell ng worksheet: SERBISYONG WEB (WEBSERVICE) и FILTER.XML (FILTERXML). Gumagana ang mga ito sa pares - una ang function SERBISYONG WEB nagsasagawa ng kahilingan sa gustong site at ibinabalik ang tugon nito sa XML na format, at pagkatapos ay ginagamit ang function FILTER.XML "pinara-parse" namin ang sagot na ito sa mga bahagi, na kinukuha ang data na kailangan namin mula dito.

Tingnan natin ang pagpapatakbo ng mga function na ito gamit ang isang klasikong halimbawa – pag-import ng exchange rate ng anumang pera na kailangan namin para sa isang partikular na pagitan ng petsa mula sa website ng Central Bank ng Ating Bansa. Gagamitin namin ang sumusunod na konstruksyon bilang blangko:

Dito:

• Ang mga yellow cell ay naglalaman ng mga petsa ng pagsisimula at pagtatapos ng panahon ng interes sa amin.
• Ang asul ay may drop-down na listahan ng mga pera gamit ang command Data – Pagpapatunay – Listahan (Data — Pagpapatunay — Listahan).
• Sa mga berdeng cell, gagamitin namin ang aming mga function upang lumikha ng string ng query at makuha ang tugon ng server.
• Ang talahanayan sa kanan ay isang sanggunian sa mga code ng pera (kakailanganin namin ito mamaya).
  

Tayo na!

Hakbang 1. Pagbuo ng string ng query

Upang makuha ang kinakailangang impormasyon mula sa site, kailangan mong itanong ito nang tama. Pumunta kami sa www.cbr.ru at buksan ang link sa footer ng pangunahing pahina' Teknikal na Mapagkukunan'- Pagkuha ng data gamit ang XML (http://cbr.ru/development/SXML/). Nag-scroll kami ng kaunti pababa at sa pangalawang halimbawa (Halimbawa 2) ay magkakaroon ng kung ano ang kailangan namin - pagkuha ng mga halaga ng palitan para sa isang ibinigay na pagitan ng petsa:

Tulad ng nakikita mo mula sa halimbawa, ang string ng query ay dapat maglaman ng mga petsa ng pagsisimula (petsa_req1) at mga wakas (petsa_req2) ng panahon ng interes sa amin at ang currency code (VAL_NM_RQ), ang rate na gusto nating makuha. Mahahanap mo ang pangunahing mga code ng pera sa talahanayan sa ibaba:

Available din ang kumpletong gabay sa mga currency code sa website ng Central Bank – tingnan ang http://cbr.ru/scripts/XML_val.asp?d=0

Ngayon ay bubuo tayo ng query string sa isang cell sa isang sheet na may:

• ang text concatenation operator (&) upang pagsamahin ito;
  
• Mga tampok VPR (VLOOKUP)upang mahanap ang code ng pera na kailangan namin sa direktoryo;
  
• Mga tampok TEXT (TEXT), na nagko-convert ng petsa ayon sa ibinigay na pattern araw-buwan-taon sa pamamagitan ng slash.

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

Hakbang 2. Isagawa ang kahilingan

Ngayon ginagamit namin ang function SERBISYONG WEB (WEBSERVICE) na may nabuong string ng query bilang ang tanging argumento. Ang sagot ay isang mahabang linya ng XML code (mas mainam na i-on ang word wrap at dagdagan ang laki ng cell kung gusto mong makita ito nang buo):

Hakbang 3. Pag-parse ng sagot

Upang gawing mas madaling maunawaan ang istruktura ng data ng tugon, mas mainam na gumamit ng isa sa mga online na XML parser (halimbawa, http://xpather.com/ o https://jsonformatter.org/xml-parser), na maaaring biswal na mag-format ng XML code, magdagdag ng mga indent dito at i-highlight ang syntax na may kulay. Pagkatapos ang lahat ay magiging mas malinaw:

Ngayon ay malinaw mong makikita na ang mga halaga ng kurso ay naka-frame ng aming mga tag ..., at ang mga petsa ay mga katangian petsa sa mga tag .

Para i-extract ang mga ito, pumili ng column na may sampu (o higit pa – kung tapos na sa margin) na walang laman na mga cell sa sheet (dahil nakatakda ang 10 araw na pagitan ng petsa) at ilagay ang function sa formula bar FILTER.XML (FILTERXML):

Dito, ang unang argumento ay isang link sa isang cell na may tugon sa server (B8), at ang pangalawa ay isang query string sa XPath, isang espesyal na wika na maaaring magamit upang ma-access ang mga kinakailangang fragment ng XML code at i-extract ang mga ito. Maaari kang magbasa nang higit pa tungkol sa wikang XPath, halimbawa, dito.

Mahalaga na pagkatapos ipasok ang formula, huwag pindutin Magpasok, at ang keyboard shortcut Ctrl+Ilipat+Magpasok, ibig sabihin, ilagay ito bilang array formula (awtomatikong idaragdag ang mga kulot na braces sa paligid nito). Kung mayroon kang pinakabagong bersyon ng Office 365 na may suporta para sa mga dynamic na array sa Excel, kung gayon ay isang simple Magpasok, at hindi mo kailangang pumili ng mga walang laman na cell nang maaga - ang function mismo ay kukuha ng maraming mga cell hangga't kailangan nito.

Upang kunin ang mga petsa, gagawin namin ang parehong - pipili kami ng ilang mga walang laman na cell sa katabing column at gagamitin ang parehong function, ngunit may ibang query sa XPath, upang makuha ang lahat ng mga halaga ng mga katangian ng Petsa mula sa mga tag ng Record:

=FILTER.XML(B8;”//Record/@Petsa”)

Ngayon sa hinaharap, kapag binabago ang mga petsa sa orihinal na mga cell B2 at B3 o pumipili ng ibang currency sa drop-down na listahan ng cell B3, ang aming query ay awtomatikong ia-update, na tumutukoy sa server ng Central Bank para sa bagong data. Upang pilitin ang isang pag-update nang manu-mano, maaari mo ring gamitin ang keyboard shortcut Ctrl+Alt+F9.

• Mag-import ng bitcoin rate sa Excel sa pamamagitan ng Power Query
• Mag-import ng mga exchange rate mula sa Internet sa mga mas lumang bersyon ng Excel