Obsah
Predpokladajme, že ste prejavili obchodný zmysel a intuíciu a kúpili ste si v minulosti niekoľko častí nejakej kryptomeny (napríklad ten istý bitcoin). Vo forme inteligentnej tabuľky vyzerá vaše „investičné portfólio“ takto:
Úloha: rýchlo zhodnotiť aktuálnu hodnotu vašich investícií pri aktuálnom kurze kryptomeny. Kurz absolvujeme na internete z akejkoľvek vhodnej stránky (burza, výmenník) a priemer na spoľahlivosť.
Jedno z riešení – klasickú webovú požiadavku – som už podrobne zvažoval na príklade importu kurzu. Teraz skúsme pre zmenu použiť inú metódu – doplnok Power Query, ktorý je ideálny na import údajov do Excelu z vonkajšieho sveta, vrátane internetu.
Výber lokality na import
Z ktorej stránky budeme brať údaje – na tom celkovo nezáleží. Klasický webový dotaz Excel je veľmi náročný na štruktúru a interný dizajn importovanej webovej stránky a niekedy nefunguje na každej stránke. Power Query je v tejto veci oveľa všežravejší. Môžete si teda vybrať z priemernej sadzby nákupu:
- vo výmenníkoch www.bestchange.ru – veľký výber možností, minimálne riziká, ale nie veľmi výnosný výmenný kurz
- z obchodnej platformy www.localbitcoins.net – trochu väčšie riziko, ale oveľa lepší kurz
- zo stránky burzy – ak obchodujete priamo na burze, tak tento článok takmer nepotrebujete 🙂
Najprv si v prehliadači otvoríme stránku, ktorú potrebujeme. Pre konkrétnosť si zoberme obchodnú platformu localbitcoins.net. Vyberte hornú kartu Rýchly predaj a možnosť Prevody cez konkrétnu banku (alebo akékoľvek iné, ktoré potrebujete) a stlačte tlačidlo Vyhľadanie:
Teraz musíte skopírovať adresu stránky, ktorá sa zobrazí do schránky, pretože. obsahuje všetky parametre požiadavky, ktoré potrebujeme:
https://localbitcoins.net/instant-bitcoins/?action=predať&country_code=RU&suma=¤cy=RUB&place_country=RU& online_poskytovateľ=SPECIFIC_BANK&find-offers=HľadaťPotom je to už na Power Query.
Importovanie kurzu do Excelu pomocou Power Query
Ak máte Excel 2010-2013 a Power Query nainštalovaný ako samostatný doplnok, potom príkaz, ktorý potrebujeme, je na karte s rovnakým názvom – mocenský dotaz. Ak máte Excel 2016, tak na karte dátum (Dátum) stlač tlačidlo Z internetu (Z internetu). V zobrazenom okne musíte vložiť skopírovanú adresu webovej stránky z predchádzajúceho odseku a kliknúť OK:
Po analýze webovej stránky Power Query zobrazí okno so zoznamom tabuliek, ktoré je možné importovať. Požadovanú tabuľku musíte nájsť v zozname vľavo (je ich niekoľko) so zameraním na náhľad vpravo a potom kliknúť na tlačidlo nižšie Oprava (Edit):
Potom sa otvorí hlavné okno editora dotazov Power Query, v ktorom môžeme vybrať iba potrebné riadky a spriemerovať z nich cenu nákupu:
Odporúčam okamžite premenovať našu požiadavku na paneli vpravo a dať jej nejaký rozumný názov:
Filtrovanie a čistenie údajov
V budúcnosti budeme potrebovať iba stĺpce s popismi Spôsob platby a nákupná sadzba Cena / BTC – aby ste ich mohli bezpečne rozlíšiť pomocou oboch ctrl a kliknutím na ne pravým tlačidlom myši vyberte príkaz Odstráňte ostatné stĺpce (Odstrániť ďalšie stĺpce) – vymažú sa všetky stĺpce okrem vybratých.
Povedzme, že chceme vybrať len tých obchodníkov, ktorí pracujú cez Sberbank. Filter je známa vec, ale nuansou je, že filter v Power Query rozlišuje veľké a malé písmená, tj Sberbank, Sberbank a Sberbank pre neho nie sú rovnaké. Preto pred výberom potrebných riadkov priveďme prípad všetkých popisov do jednej formy. Ak to chcete urobiť, musíte vybrať stĺpec Spôsob platby a na karte Premena vybrať tím Formát – malé písmená (Transformácia – formát – malé písmená):
Teraz filtrujte podľa stĺpca Spôsob platby pomocou možnosti Textové filtre – obsahuje (Textové filtre — obsahuje):
V okne filtra sa ihneď prepnite zhora do režimu Okrem toho (Advanced) a zaviesť tri pravidlá výberu:
Ako by ste mohli uhádnuť, týmto spôsobom vyberieme všetky riadky, kde sa slovo „sber“ vyskytuje v angličtine alebo v angličtine, plus tie, ktoré pracujú prostredníctvom akejkoľvek banky. Nezabudnite nastaviť logický odkaz vľavo Or (OR) namiesto toho И (A) V opačnom prípade pravidlo nebude fungovať správne. Po kliknutí na OK Na obrazovke by mali zostať iba možnosti, ktoré potrebujeme:
Teraz odstráňte stĺpec Spôsob platby kliknite pravým tlačidlom myši na hlavičku stĺpca Odstrániť stĺpec (Odstrániť stĺpec) a ďalej pracovať so zvyšným jediným stĺpcom kurzov:
Problém je v tom, že je tam okrem čísla aj označenie meny. To sa dá ľahko vyčistiť jednoduchým nahradením kliknutím pravým tlačidlom myši na hlavičku stĺpca a výberom príkazu Nahradenie hodnôt (Nahradiť hodnoty):
Čísla získané po odstránení RUB v skutočnosti ešte nie sú číslami, pretože používajú neštandardné oddeľovače. Dá sa to vyriešiť kliknutím na tlačidlo formátovania v hlavičke tabuľky a následným výberom možnosti Používa sa miestne nastavenie (Použite miestnych obyvateľov):
Najvhodnejšie miesto by bolo Angličtina (US) a typ údajov - Дdesatinné číslo:
Po kliknutí na OK získame úplné číselné hodnoty nákupných sadzieb:
Zostáva vypočítať priemer pre nich na karte Transformácia – Štatistika – Priemer (Transformácia – Štatistika – Priemer) a nahrajte výsledné číslo na hárok pomocou príkazu Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť a načítať — Zavrieť a načítať do…):
Teraz môžeme pridať odkaz na stiahnutú sadzbu vo vzorci do našej tabuľky portfólia a vypočítať rozdiel v hodnote pre všetky naše investície v aktuálnom okamihu:
Teraz môžete tento súbor pravidelne otvárať, kliknúť pravým tlačidlom myši na výzvu a vybrať príkaz Aktualizovať a uložiť (Obnoviť), sledujte zmeny, ktoré sa automaticky načítajú do našej tabuľky.
PS
Ako si viete ľahko predstaviť, presne rovnakým spôsobom môžete importovať kurz nielen bitcoinu, ale aj akejkoľvek inej meny, akcie alebo cenného papiera. Hlavná vec je nájsť vhodnú stránku a zostaviť dopyt a potom všetko urobí inteligentný Power Query.
- Import výmenných kurzov z internetu
- Funkcia na získanie výmenného kurzu pre daný dátum
- Zostavovanie tabuliek z rôznych súborov pomocou Power Query