Import výmenných kurzov z internetu

Import kurzu danej meny z internetu s automatickou aktualizáciou je veľmi bežnou úlohou mnohých používateľov programu Microsoft Excel. Predstavte si, že máte cenník, ktorý treba každé ráno prepočítať podľa kurzu. Alebo rozpočet projektu. Alebo náklady na zmluvu, ktoré sa musia vypočítať pomocou výmenného kurzu dolára v deň uzavretia zmluvy.

V takýchto situáciách môžete problém vyriešiť rôznymi spôsobmi – všetko závisí od toho, ktorú verziu Excelu máte nainštalovanú a aké doplnky sú nad ňou.

Metóda 1: Jednoduchá webová požiadavka na aktuálny výmenný kurz

Táto metóda je vhodná pre tých, ktorí majú v počítači stále staré verzie balíka Microsoft Office 2003-2007. Nepoužíva žiadne doplnky ani makrá tretích strán a funguje iba na vstavaných funkciách.

lis Z internetu (web) pútko dátum (Dátum). V zobrazenom okne v riadku adresa (adresa) zadajte adresu URL stránky, z ktorej budú informácie prevzaté (napríklad http://www.finmarket.ru/currency/rates/) a stlačte kláves vstúpiť.

Po načítaní stránky sa na tabuľkách, ktoré môže Excel importovať, objavia čierne a žlté šípky. Kliknutím na takúto šípku označíte tabuľku na import.

Keď sú označené všetky potrebné tabuľky, kliknite na tlačidlo import (Import) v spodnej časti okna. Po určitom čase potrebnom na načítanie údajov sa obsah označených tabuliek objaví v bunkách na hárku:

Pre ďalšie prispôsobenie môžete kliknúť pravým tlačidlom myši na ktorúkoľvek z týchto buniek a vybrať príkaz z kontextovej ponuky. Vlastnosti rozsahu (Vlastnosti rozsahu údajov).V tomto dialógovom okne je v prípade potreby možné nakonfigurovať frekvenciu aktualizácie a ďalšie parametre:

Kurzy akcií, keďže sa menia každých pár minút, môžete ich aktualizovať častejšie (začiarkavacie políčko Obnovte každých N min.), ale výmenné kurzy vo väčšine prípadov stačí aktualizovať raz denne (zaškrtávacie políčko Aktualizácia pri otvorení súboru).

Všimnite si, že celý importovaný rozsah údajov spracuje Excel ako jeden celok a dostane svoj vlastný názov, ktorý je možné vidieť v Správcovi názvov na karte vzorec (Vzorce — Name Manager).

Metóda 2: Parametrický webový dotaz na získanie výmenného kurzu pre daný rozsah dátumov

Táto metóda je mierne modernizovanou prvou možnosťou a dáva používateľovi možnosť získať kurz požadovanej meny nielen pre aktuálny deň, ale aj pre akýkoľvek iný dátum alebo dátumový interval záujmu. Na to je potrebné zmeniť našu webovú požiadavku na parametrickú, teda pridať k nej dva upresňujúce parametre (kód meny, ktorú potrebujeme a aktuálny dátum). Ak to chcete urobiť, postupujte takto:

1. Vytvoríme webovú požiadavku (pozri spôsob 1) na stránku webovej stránky Centrálnej banky našej krajiny s archívom kurzov: http://cbr.ru/currency_base/dynamics.aspx

2. Vo formulári naľavo vyberte požadovanú menu a nastavte dátum začiatku a konca:

3. Kliknite na tlačidlo Na získanie údajov a po niekoľkých sekundách uvidíme tabuľku s hodnotami kurzu, ktoré potrebujeme pre daný interval dátumov. Posuňte výslednú tabuľku úplne nadol a označte ju na import kliknutím na čiernu a žltú šípku v ľavom dolnom rohu webovej stránky (len sa nepýtajte, prečo je táto šípka tam a nie vedľa tabuľky – toto je otázka pre dizajnérov stránok).

Teraz hľadáme tlačidlo s disketou v pravom hornom rohu okna Uložiť požiadavku (Uložiť dopyt) a uložte súbor s parametrami našej požiadavky do ľubovoľného vhodného priečinka pod akýmkoľvek vhodným názvom – napríklad v Moje dokumenty pod menom cbr. iqy.  Potom je možné okno Web Query a celý Excel zatiaľ zatvoriť.

4. Otvorte priečinok, do ktorého ste uložili žiadosť, a vyhľadajte súbor žiadosti cbr. iqy, potom naň kliknite pravým tlačidlom myši – Otvoriť pomocou – Poznámkový blok (alebo ho vyberte zo zoznamu – zvyčajne je to súbor Notepad.exe z priečinka C: Windows). Po otvorení súboru žiadosti v programe Poznámkový blok by ste mali vidieť niečo takéto:

Najcennejší je tu riadok s adresou a v ňom parametre dopytu, ktoré nahradíme – kód meny, ktorú potrebujeme (zvýraznený červenou farbou) a dátum ukončenia, ktorý nahradíme dnešným (zvýraznený v Modrá). Opatrne upravte riadok, aby ste získali nasledovné:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Kód meny"]&date_req1=01.01.2000&r1=1&date_req2=["Dátum"]&rt=1&mode=1

Všetko ostatné nechajte tak, ako je, uložte a zatvorte súbor.

5. Vytvorte novú knihu v Exceli, otvoríme hárok, kam chceme importovať archív kurzov centrálnej banky. Do ľubovoľnej vhodnej bunky zadajte vzorec, ktorý nám poskytne aktuálny dátum v textovom formáte pre nahradenie dopytu:

=TEXT(DNES();”DD.MM.RRRR”)

alebo v anglickej verzii

=TEXT(DNES(),»dd.mm.yyyy»)

Niekde v okolí zadáme kód meny, ktorú potrebujeme z tabuľky:

Požadovaný kód je možné nahliadnuť aj do reťazca dotazu priamo na stránke centrálnej banky.

6. Dáta načítame na hárok, pričom ako základ použijeme vytvorené bunky a súbor cbr.iqy, teda prejdite na tab. Údaje – Pripojenia – Nájsť ďalšie (Údaje – existujúce spojenia). V okne výberu zdroja údajov, ktoré sa otvorí, vyhľadajte a otvorte súbor cbr. iqy. Pred importom si s nami Excel vyjasní tri veci.

Po prvé, kam importovať tabuľku údajov:

Po druhé, odkiaľ získať kód meny (môžete začiarknuť políčko Použite túto predvolenú hodnotu (Túto hodnotu/odkaz použite pre budúce aktualizácie), takže neskôr zakaždým táto bunka nebude špecifikovaná počas aktualizácií a začiarkavacie políčko Automaticky aktualizovať, keď sa zmení hodnota bunky (Automatické obnovenie pri zmene hodnoty bunky):

Po tretie, z ktorej bunky sa má prevziať dátum ukončenia (tu môžete tiež zaškrtnúť obe políčka, aby ste zajtra nemuseli tieto parametre nastavovať ručne pri aktualizácii):

kliknite OK, počkajte pár sekúnd a na hárku získate kompletný archív výmenného kurzu požadovanej meny:

Rovnako ako v prvom spôsobe, kliknutím pravým tlačidlom myši na importované údaje a výberom príkazu Vlastnosti rozsahu (Vlastnosti rozsahu údajov), môžete upraviť obnovovaciu frekvenciu Pri otváraní súboru (Obnoviť pri otvorenom súbore). Potom, ak máte prístup na internet, údaje sa budú automaticky aktualizovať každý deň, tj tabuľka sa automaticky aktualizuje o nové údaje.

Najjednoduchšie je extrahovať sadzbu pre požadovaný dátum z našej tabuľky pomocou funkcie VPR (VLOOKUP) – ak s tým nie ste oboznámení, dôrazne vám to odporúčam. Pomocou takéhoto vzorca môžete napríklad vybrať výmenný kurz dolára pre 10. januára 2000 z našej tabuľky:

alebo v angličtine =VLOOKUP(E5,cbr,3,1)

kde

• E5 – bunka obsahujúca daný dátum
  
• cbr – názov rozsahu údajov (automaticky generovaný počas importu a zvyčajne rovnaký ako názov súboru dotazu)
• 3 – poradové číslo stĺpca v našej tabuľke, odkiaľ získavame údaje
• 1 – argument, ktorý obsahuje približné vyhľadávanie funkcie VLOOKUP, aby ste mohli nájsť kurzy pre tie prechodné dátumy, ktoré sa v skutočnosti nenachádzajú v stĺpci A (bude sa brať najbližší predchádzajúci dátum a jeho kurz). Viac o približnom vyhľadávaní pomocou funkcie VLOOKUP si môžete prečítať tu.

• Makro na získanie kurzu dolára pre daný dátum v aktuálnej bunke
• Doplnková funkcia PLEX na získanie výmenného kurzu dolára, eura, hrivny, libry šterlingov atď. pre daný dátum
• Do doplnku PLEX vložte ľubovoľný kurz meny k ľubovoľnému dátumu