Import směnných kurzů z internetu

Import kurzu dané měny z internetu s automatickou aktualizací je velmi častým úkolem mnoha uživatelů Microsoft Excel. Představte si, že máte ceník, který se musí každé ráno přepočítávat podle kurzu. Nebo rozpočet projektu. Nebo náklady na smlouvu, které je třeba vypočítat pomocí směnného kurzu dolaru k datu uzavření smlouvy.

V takových situacích můžete problém vyřešit různými způsoby – vše záleží na tom, jakou verzi Excelu máte nainstalovanou a jaké doplňky jsou nad ní.

Metoda 1: Jednoduchý webový požadavek na aktuální směnný kurz

Tato metoda je vhodná pro ty, kteří mají v počítači stále staré verze Microsoft Office 2003-2007. Nepoužívá žádné doplňky ani makra třetích stran a funguje pouze s vestavěnými funkcemi.

lis Z internetu (web) Karta Data (Datum). V okně, které se objeví, v řádku Adresa (Adresa) zadejte adresu URL webu, ze kterého budou informace přebírány (například http://www.finmarket.ru/currency/rates/) a stiskněte klávesu vstoupit.

Po načtení stránky se na tabulkách, které může Excel importovat, objeví černé a žluté šipky. Kliknutím na takovou šipku označíte tabulku pro import.

Po označení všech potřebných tabulek klikněte na tlačítko import (Import) ve spodní části okna. Po nějaké době potřebné k načtení dat se v buňkách listu objeví obsah označených tabulek:

Pro další přizpůsobení můžete kliknout pravým tlačítkem na kteroukoli z těchto buněk a vybrat příkaz z místní nabídky. Vlastnosti rozsahu (Vlastnosti rozsahu dat).V tomto dialogovém okně je v případě potřeby možné nakonfigurovat frekvenci aktualizací a další parametry:

Kurzy akcií, protože se mění každých pár minut, můžete je aktualizovat častěji (zaškrtávací políčko Obnovte každých N min.), ale směnné kurzy ve většině případů stačí aktualizovat jednou denně (zaškrtávací políčko Aktualizace při otevření souboru).

Všimněte si, že s celým importovaným rozsahem dat zachází aplikace Excel jako s jednou jednotkou a má svůj vlastní název, který lze zobrazit ve Správci názvů na kartě vzorec (Vzorce — Správce jmen).

Metoda 2: Parametrický webový dotaz k získání směnného kurzu pro dané časové období

Tato metoda je mírně modernizovanou první možností a dává uživateli možnost získat směnný kurz požadované měny nejen pro aktuální den, ale také pro jakékoli jiné datum nebo časový interval zájmu. K tomu je třeba náš webový požadavek převést na parametrický, tedy přidat k němu dva upřesňující parametry (kód měny, kterou potřebujeme a aktuální datum). Za tímto účelem provedeme následující:

1. Vytvoříme webovou žádost (viz způsob 1) na stránku webu Centrální banky naší země s archivem kurzů: http://cbr.ru/currency_base/dynamics.aspx

2. Ve formuláři nalevo vyberte požadovanou měnu a nastavte počáteční a koncové datum:

3. Klikněte na tlačítko Chcete-li získat data a po několika sekundách vidíme tabulku s hodnotami kurzu, které potřebujeme pro daný časový interval. Posuňte výslednou tabulku úplně dolů a označte ji pro import kliknutím na černo-žlutou šipku v levém dolním rohu webové stránky (jen se neptejte, proč je tato šipka tam a ne vedle tabulky – to je otázka pro tvůrce stránek).

Nyní hledáme tlačítko s disketou v pravém horním rohu okna Uložit požadavek (Uložit dotaz) a uložte soubor s parametry našeho požadavku do libovolné vhodné složky pod libovolným vhodným názvem – například in Moje dokumenty pod jménem cbr. iqy.  Poté lze okno Web Query a celý Excel prozatím zavřít.

4. Otevřete složku, do které jste uložili požadavek, a vyhledejte soubor požadavku cbr. iqy, poté na něj klikněte pravým tlačítkem – Otevřít pomocí – Poznámkový blok (nebo jej vyberte ze seznamu – obvykle se jedná o soubor Notepad.exe ze složky C: Windows). Po otevření souboru požadavku v programu Poznámkový blok byste měli vidět něco takového:

Nejcennější je zde řádek s adresou a v něm parametry dotazu, které dosadíme – kód měny, kterou potřebujeme (zvýrazněno červeně) a koncové datum, které nahradíme dnešním (zvýrazněno v modrý). Pečlivě upravte řádek, abyste získali následující:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Kód měny"]&date_req1=01.01.2000&r1=1&date_req2=["Datum"]&rt=1&mode=1

Vše ostatní nechte tak, jak je, uložte a zavřete soubor.

5. Vytvořte novou knihu v Excelu, otevřete list, kam chceme importovat archiv sazeb centrální banky. Do libovolné vhodné buňky zadejte vzorec, který nám poskytne aktuální datum v textovém formátu pro nahrazení dotazu:

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

nebo v anglické verzi

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

Někde poblíž zadáme kód měny, kterou potřebujeme z tabulky:

Požadovaný kód lze také nahlédnout do řetězce dotazu přímo na webu centrální banky.

6. Načteme data na list, jako základ použijeme vytvořené buňky a soubor cbr.iqy, tj. přejděte na tab. Data – Připojení – Najít další (Data — Stávající připojení). V okně pro výběr zdroje dat, které se otevře, vyhledejte a otevřete soubor cbr. iqy. Před importem si s námi Excel vyjasní tři věci.

Nejprve, kam importovat datovou tabulku:

Za druhé, odkud získat kód měny (můžete zaškrtnout políčko Použijte tuto výchozí hodnotu (Tuto hodnotu/odkaz použijte pro budoucí aktualizace), takže později pokaždé tato buňka není specifikována během aktualizací a zaškrtávací políčko Automaticky aktualizovat, když se změní hodnota buňky (Automaticky se obnoví, když se změní hodnota buňky):

Zatřetí, ze které buňky převzít datum ukončení (zde můžete také zaškrtnout obě políčka, abyste zítra nemuseli tyto parametry nastavovat ručně při aktualizaci):

klikněte OK, počkejte několik sekund a získejte kompletní archiv směnného kurzu požadované měny na listu:

Stejně jako u prvního způsobu kliknutím pravým tlačítkem myši na importovaná data a výběrem příkazu Vlastnosti rozsahu (Vlastnosti rozsahu dat), můžete upravit obnovovací frekvenci Při otevírání souboru (Obnovit při otevření souboru). Poté, pokud máte přístup k internetu, budou data automaticky aktualizována každý den, tj. Tabulka bude automaticky aktualizována novými daty.

Nejjednodušší je extrahovat sazbu pro požadované datum z naší tabulky pomocí funkce VPR (VYHLEDAT) – pokud s tím nejste obeznámeni, pak vám důrazně doporučuji, abyste to udělali. Pomocí takového vzorce můžete například vybrat směnný kurz dolaru pro 10. ledna 2000 z naší tabulky:

nebo v angličtině =VLOOKUP(E5,cbr,3,1)

kde

• E5 – buňka obsahující dané datum
  
• cbr – název rozsahu dat (automaticky generovaný při importu a obvykle stejný jako název souboru dotazu)
• 3 – pořadové číslo sloupce v naší tabulce, odkud data získáváme
• 1 – argument, který obsahuje přibližné vyhledání funkce SVYHLEDAT, abyste mohli najít kurzy pro ty přechodné termíny, které ve sloupci A ve skutečnosti nejsou (bude se brát nejbližší předchozí datum a jeho kurz). Více o přibližném vyhledávání pomocí funkce VLOOKUP si můžete přečíst zde.

• Makro pro získání kurzu dolaru pro dané datum v aktuální buňce
• Doplňková funkce PLEX pro získání směnného kurzu dolaru, eura, hřivny, libry šterlinků atd. pro jakékoli dané datum
• Do doplňku PLEX vložte jakýkoli kurz měny k libovolnému datu