Předpokládejme, že jste projevili obchodní smysl a intuici a v minulosti jste koupili několik částí nějaké kryptoměny (například stejný bitcoin). Ve formě chytré tabulky vypadá vaše „investiční portfolio“ takto:
Úkol: rychle vyhodnotit aktuální hodnotu vašich investic při aktuálním kurzu kryptoměny. Kurz absolvujeme na internetu z jakékoli vhodné stránky (burza, výměník) a průměr pro spolehlivost.
Jedno z řešení – klasický webový požadavek – jsem již podrobně zvažoval na příkladu importu kurzu. Nyní zkusme pro změnu využít jinou metodu – doplněk Power Query, který je ideální pro import dat do Excelu z vnějšího světa včetně internetu.
Výběr webu k importu
Ze kterého webu budeme data přebírat – na tom v podstatě nezáleží. Klasický webový dotaz Excel je velmi náročný na strukturu a vnitřní design importované webové stránky a někdy nefunguje na každém webu. Power Query je v této věci mnohem všežravější. Můžete si tedy vybrat z průměrné sazby nákupu:
- ve výměnících www.bestchange.ru – velký výběr možností, minimální rizika, ale nepříliš ziskový kurz
- z obchodní platformy www.localbitcoins.net – trochu větší riziko, ale mnohem lepší kurz
- z webu burzy – pokud obchodujete přímo na burze, pak tento článek téměř nepotřebujete 🙂
Nejprve si v prohlížeči otevřeme web, který potřebujeme. Vezměme si pro konkrétnost obchodní platformu localbitcoins.net. Vyberte horní kartu Rychlý prodej a možnost Převody přes konkrétní banku (nebo jakýkoli jiný, který potřebujete) a stiskněte tlačítko Vyhledávání:
Nyní je třeba zkopírovat adresu stránky, která se zobrazí do schránky, protože. obsahuje všechny parametry požadavku, které potřebujeme:
https://localbitcoins.net/instant-bitcoins/?action=prodat&country_code=RU&částka=¤cy=RUB&place_country=RU& online_provider=SPECIFIC_BANK&find-offers=HledatPak už je to na Power Query.
Import kurzu do Excelu pomocí Power Query
Pokud máte Excel 2010-2013 a Power Query nainstalovaný jako samostatný doplněk, pak příkaz, který potřebujeme, je na kartě se stejným názvem – Dotaz na napájení. Pokud máte Excel 2016, tak na záložce Data (Datum) zmáčknout tlačítko Z internetu (Z internetu). V okně, které se objeví, musíte vložit zkopírovanou adresu webové stránky z předchozího odstavce a kliknout OK:
Po analýze webové stránky zobrazí Power Query okno se seznamem tabulek, které lze importovat. Požadovanou tabulku musíte najít v seznamu vlevo (je jich několik) se zaměřením na náhled vpravo a poté kliknout na tlačítko níže Oprava (Edit):
Poté se otevře hlavní okno editoru dotazů Power Query, ve kterém můžeme vybrat pouze potřebné řádky a zprůměrovat nad nimi cenu nákupu:
Doporučuji okamžitě přejmenovat náš požadavek v panelu vpravo a dát mu nějaký rozumný název:
Filtrování a čištění dat
V budoucnu budeme potřebovat pouze sloupce s popisy Způsob platby a nákupní sazba Cena / BTC – takže je můžete oba bezpečně odlišit Ctrl a kliknutím pravým tlačítkem na ně vyberte příkaz Odstraňte další sloupce (Odstranit další sloupce) – všechny sloupce budou smazány kromě vybraných.
Řekněme, že chceme vybrat pouze ty obchodníky, kteří pracují přes Sberbank. Filtr je známá věc, ale nuancí je, že filtr v Power Query rozlišuje malá a velká písmena, tj. Sberbank, Sberbank a Sberbank pro něj nejsou totéž. Než tedy vybereme potřebné řádky, uveďme případ všech popisů do jednoho formuláře. Chcete-li to provést, musíte vybrat sloupec Způsob platby a na kartě Proměna vybrat tým Formát – malá písmena (Transformace — Formát — Malá písmena):
Nyní filtrujte podle sloupce Způsob platby pomocí možnosti Textové filtry – obsahuje (Textové filtry — obsahuje):
V okně filtru se ihned přepněte shora do režimu Kromě toho (Pokročilý) a zavést tři pravidla pro výběr:
Jak asi tušíte, tímto způsobem vybereme všechny řádky, kde se slovo „sber“ vyskytuje v angličtině nebo v angličtině, plus ty, kteří pracují prostřednictvím jakékoli banky. Nezapomeňte si vlevo nastavit logický odkaz Or (OR) místo toho И (A) V opačném případě nebude pravidlo správně fungovat. Po kliknutí na OK Na obrazovce by měly zůstat pouze možnosti, které potřebujeme:
Nyní sloupek vyjměte Způsob platby klikněte pravým tlačítkem na záhlaví sloupce Smazat sloupec (Odstranit sloupec) a dále pracovat se zbývajícím jediným sloupcem kurzů:
Problém je v tom, že je tam kromě čísla i označení měny. To lze snadno vyčistit jednoduchou záměnou kliknutím pravým tlačítkem na záhlaví sloupce a výběrem příkazu Výměna hodnot (Nahradit hodnoty):
Čísla získaná po odstranění RUB ve skutečnosti také ještě nejsou čísla, protože používají nestandardní oddělovače. To lze vyléčit kliknutím na tlačítko formátování v záhlaví tabulky a následným výběrem možnosti Používání národního prostředí (Použijte místní):
Nejvhodnější lokalita by byla Angličtina (US) a datový typ – Дdesetinné číslo:
Po kliknutí na tlačítko OK získáme plné číselné hodnoty nákupních sazeb:
Zbývá jim vypočítat průměr na záložce Transformace – Statistika – Průměr (Transformace — Statistika — Průměr) a nahrajte výsledné číslo na list příkazem Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít a načíst — Zavřít a načíst do…):
Nyní můžeme přidat odkaz na staženou sazbu ve vzorci do naší tabulky portfolia a vypočítat rozdíl v hodnotě všech našich investic v aktuálním okamžiku:
Nyní můžete tento soubor pravidelně otevírat, klepněte pravým tlačítkem myši na výzvu a vyberte příkaz Aktualizovat a uložit (Obnovit), sledujte změny, které se automaticky načtou do naší tabulky.
PS
Jak si snadno dokážete představit, úplně stejným způsobem můžete importovat kurz nejen bitcoinu, ale i jakékoli jiné měny, akcie nebo cenného papíru. Hlavní věc je najít vhodný web a sestavit dotaz a pak vše udělá chytrý Power Query.
- Import směnných kurzů z internetu
- Funkce pro získání směnného kurzu pro dané datum
- Sestavení tabulek z různých souborů pomocí Power Query