Gi-update nga exchange rate sa Excel

Gibalikbalik nako ang pag-analisar sa mga paagi sa pag-import sa datos sa Excel gikan sa Internet nga adunay sunud nga awtomatikong pag-update. Sa partikular:

• Sa mga daan nga bersyon sa Excel 2007-2013, mahimo kini gamit ang direkta nga hangyo sa web.
• Sugod sa 2010, mahimo kini nga sayon ​​kaayo gamit ang Power Query add-in.
  

Sa kini nga mga pamaagi sa pinakabag-o nga bersyon sa Microsoft Excel, mahimo ka na nga magdugang usa pa - pag-import sa datos gikan sa Internet sa format nga XML gamit ang mga built-in nga function.

Ang XML (eXtensible Markup Language = Extensible Markup Language) kay usa ka unibersal nga lengguwahe nga gidesinyo aron ihulagway ang bisan unsang klase sa datos. Sa tinuud, kini yano nga teksto, apan adunay espesyal nga mga tag nga gidugang niini aron markahan ang istruktura sa datos. Daghang mga site ang naghatag libre nga mga sapa sa ilang data sa format nga XML aron ma-download ni bisan kinsa. Sa website sa Central Bank of Our Country (www.cbr.ru), ilabina, sa tabang sa susamang teknolohiya, gihatag ang datos sa exchange rates sa lain-laing mga currency. Gikan sa website sa Moscow Exchange (www.moex.com) mahimo nimong i-download ang mga kinutlo alang sa mga stock, bond ug daghang uban pang mapuslanon nga kasayuran sa parehas nga paagi.

Sukad sa bersyon 2013, ang Excel adunay duha ka mga gimbuhaton alang sa direkta nga pagkarga sa XML data gikan sa Internet ngadto sa worksheet cells: SERBISYO SA WEB (WEBSERBISYO) и FILTER.XML (FILTERXML). Nagtrabaho sila nga managsama - una ang function SERBISYO SA WEB nagpatuman sa usa ka hangyo sa gusto nga site ug ibalik ang tubag niini sa XML nga format, ug dayon gamit ang function FILTER.XML atong "pag-parse" kini nga tubag ngadto sa mga sangkap, pagkuha sa data nga atong gikinahanglan gikan niini.

Atong tan-awon ang operasyon niini nga mga gimbuhaton gamit ang usa ka klasiko nga pananglitan - ang pag-import sa exchange rate sa bisan unsang currency nga atong gikinahanglan alang sa usa ka gihatag nga agwat sa petsa gikan sa website sa Central Bank of Our Country. Atong gamiton ang mosunod nga konstruksyon isip blangko:

Dinhi:

• Ang yellow nga mga selula naglangkob sa mga petsa sa pagsugod ug pagtapos sa panahon nga interesado kanato.
• Ang asul adunay drop-down list sa mga kwarta gamit ang command Data – Pagpamatuod – Listahan (Data — Pagpamatuod — Listahan).
• Sa berdeng mga selyula, atong gamiton ang atong mga gimbuhaton sa paghimo og query string ug pagkuha sa tubag sa server.
• Ang lamesa sa tuo usa ka pakisayran sa mga code sa salapi (kinahanglan namon kini sa ulahi).
  

Tana!

Lakang 1. Pag-umol og query string

Aron makuha ang gikinahanglan nga impormasyon gikan sa site, kinahanglan nimo nga pangutan-on kini sa husto. Moadto kami sa www.cbr.ru ug ablihan ang link sa footer sa main page' Teknikal nga mga Kapanguhaan'- Pagkuha og datos gamit ang XML (http://cbr.ru/development/SXML/). Nag-scroll kami sa usa ka gamay nga ubos ug sa ikaduha nga pananglitan (Pananglitan 2) adunay kung unsa ang kinahanglan namon - pagkuha sa mga rate sa pagbinayloay alang sa gihatag nga agwat sa petsa:

Sama sa imong makita gikan sa pananglitan, ang string sa pangutana kinahanglan adunay mga petsa sa pagsugod (petsa_req1) ug mga katapusan (petsa_req2) sa panahon sa interes kanamo ug sa currency code (VAL_NM_RQ), ang rate nga gusto natong makuha. Makita nimo ang mga nag-unang mga code sa salapi sa lamesa sa ubos:

Ang usa ka kompleto nga giya sa mga code sa salapi anaa usab sa website sa Central Bank - tan-awa ang http://cbr.ru/scripts/XML_val.asp?d=0

Karon maghimo kami usa ka string sa pangutana sa usa ka cell sa usa ka sheet nga adunay:

• ang text concatenation operator (&) aron mahiusa kini;
  
• Features VPR (VLOOKUP)sa pagpangita sa code sa currency nga atong gikinahanglan sa direktoryo;
  
• Features TEXT (TEXT), nga nag-convert sa petsa sumala sa gihatag nga sumbanan adlaw-bulan-tuig pinaagi sa slash.

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

Lakang 2. Ipatuman ang hangyo

Karon atong gamiton ang function SERBISYO SA WEB (WEBSERBISYO) uban ang namugna nga string sa pangutana isip bugtong argumento. Ang tubag usa ka taas nga linya sa XML code (mas maayo nga i-on ang word wrap ug dugangan ang gidak-on sa cell kung gusto nimo kini makita sa kinatibuk-an):

Lakang 3. Pag-parse sa tubag

Aron mas dali masabtan ang istruktura sa data sa tubag, mas maayo nga gamiton ang usa sa mga online XML parser (pananglitan, http://xpather.com/ o https://jsonformatter.org/xml-parser), nga mahimong biswal nga mag-format sa XML code, pagdugang sa mga indent niini ug pag-highlight sa syntax nga adunay kolor. Unya ang tanan mahimong mas klaro:

Karon klaro nimong makita nga ang mga kantidad sa kurso gi-frame sa among mga tag ..., ug ang mga petsa kay mga hiyas Petsa sa mga tag .

Aron makuha kini, pilia ang usa ka kolum nga napulo (o daghan pa - kung nahimo nga adunay margin) nga mga walay sulod nga mga cell sa sheet (tungod kay gitakda ang 10 ka adlaw nga agwat sa petsa) ug isulod ang function sa formula bar FILTER.XML (FILTERXML):

Dinhi, ang una nga argumento usa ka link sa usa ka cell nga adunay tubag sa server (B8), ug ang ikaduha usa ka string sa pangutana sa XPath, usa ka espesyal nga lengguwahe nga magamit aron ma-access ang kinahanglan nga mga tipik sa XML code ug makuha kini. Mahimo nimong basahon ang dugang mahitungod sa XPath nga pinulongan, pananglitan, dinhi.

Importante nga human sa pagsulod sa pormula, ayaw pugsa Pagsulod, ug ang keyboard shortcut Ctrl+pagbalhin+Pagsulod, ie isulod kini isip array formula (ang mga curly braces sa palibot niini awtomatikong idugang). Kung ikaw adunay pinakabag-o nga bersyon sa Office 365 nga adunay suporta alang sa dinamikong mga arrays sa Excel, nan usa ka yano Pagsulod, ug dili nimo kinahanglan nga pilion ang mga walay sulod nga mga selyula daan - ang function mismo mokuha ug daghang mga selyula kung gikinahanglan.

Aron makuha ang mga petsa, buhaton namon ang parehas - magpili kami daghang mga walay sulod nga mga selyula sa kasikbit nga kolum ug gamiton ang parehas nga function, apan sa usa ka lahi nga pangutana sa XPath, aron makuha ang tanan nga mga kantidad sa mga hiyas sa Petsa gikan sa mga tag sa Record:

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

Karon sa umaabot, kung usbon ang mga petsa sa orihinal nga mga cell B2 ug B3 o pagpili sa usa ka lahi nga salapi sa drop-down list sa cell B3, ang among pangutana awtomatiko nga ma-update, nga nagtumong sa server sa Central Bank alang sa bag-ong datos. Aron mapugos ang pag-update sa mano-mano, mahimo nimong gamiton ang keyboard shortcut Ctrl+alt+F9.

• Import bitcoin rate sa Excel pinaagi sa Power Query
• Import exchange rates gikan sa Internet sa daan nga mga bersyon sa Excel