sadržaj
Pretpostavimo da ste u prošlosti pokazali poslovni smisao i intuiciju i kupili nekoliko porcija neke kriptovalute (isti bitcoin, na primjer). U obliku pametnog stola, vaš “investicioni portfolio” izgleda ovako:
Zadatak: brzo procijeniti trenutnu vrijednost vaših investicija po trenutnoj stopi kriptovalute. Mi ćemo pohađati kurs na Internetu sa bilo koje odgovarajuće stranice (mjenjačnice, mjenjačnice) i prosječne pouzdanosti.
Jedno od rješenja – klasični web zahtjev – već sam detaljno razmatrao na primjeru uvoza kursa. Pokušajmo sada, za promjenu, koristiti drugu metodu – dodatak Power Query, koji je idealan za uvoz podataka u Excel iz vanjskog svijeta, uključujući i internet.
Odabir stranice za uvoz
Sa kojeg sajta ćemo uzeti podatke - ovo, uglavnom, nije bitno. Klasični Excel web upit je vrlo zahtjevan za strukturu i interni dizajn uvezene web stranice i ponekad ne radi na svakoj stranici. Power Query je mnogo omnivorniji po ovom pitanju. Dakle, možete uzeti prosječnu stopu kupovine da birate između:
- u izmjenjivačima www.bestchange.ru – veliki izbor opcija, minimalni rizici, ali ne baš profitabilan tečaj
- sa trgovačke platforme www.localbitcoins.net – malo više rizika, ali mnogo bolja stopa
- sa web stranice berze – ako trgujete direktno na berzi, onda vam ovaj članak teško da treba 🙂
Prvo, otvorimo stranicu koja nam je potrebna u pretraživaču. Uzmimo, radi konkretnosti, trgovačku platformu localbitcoins.net. Odaberite gornju karticu Brza prodaja i opciju Transferi preko određene banke (ili bilo koji drugi koji vam je potreban) i pritisnite dugme pretraživanje:
Sada trebate kopirati adresu stranice koja se pojavljuje u međuspremnik, jer. sadrži sve potrebne parametre zahtjeva:
https://localbitcoins.net/instant-bitcoins/?action=prodati&country_code=RU&iznos=¤cy=RUB&place_country=RU& online_provider=SPECIFIC_BANK&find-offers=TražiOnda je na Power Queryju.
Uvoz kursa u Excel koristeći Power Query
Ako imate instaliran Excel 2010-2013 i Power Query kao poseban dodatak, tada se naredba koja nam treba nalazi na kartici istog imena – power query. Ako imate Excel 2016, onda na kartici podaci (Datum) pritisnite dugme Sa interneta (Sa Interneta). U prozoru koji se pojavi tada morate zalijepiti kopiranu adresu web stranice iz prethodnog pasusa i kliknuti OK:
Nakon raščlanjivanja web stranice, Power Query će prikazati prozor sa listom tabela koje se mogu uvesti. Potrebno je da pronađete traženu tabelu na listi sa leve strane (ima ih nekoliko), fokusirajući se na pregled sa desne strane, a zatim kliknite na dugme ispod korekcija (Edit):
Nakon toga će se otvoriti glavni prozor Power Query uređivača upita u kojem možemo odabrati samo potrebne redove i prosječnu stopu kupovine preko njih:
Preporučujem da odmah preimenujete naš zahtjev u panelu s desne strane, dajući mu neko razumno ime:
Filtriranje i čišćenje podataka
Ubuduće će nam trebati samo kolone sa opisima Način plaćanja i kupovna stopa Cijena / BTC – tako da ih možete bezbedno razlikovati Ctrl i desnim klikom na njih odaberite naredbu Izbrišite druge kolone (Uklonite ostale kolone) – sve kolone će biti izbrisane osim odabranih.
Recimo da želimo odabrati samo one trgovce koji rade preko Sberbanke. Filter je poznata stvar, ali nijansa je u tome što je filter u Power Queryju osjetljiv na velika i mala slova, odnosno Sberbank, Sberbank i Sberbank za njega nisu isto. Stoga, prije odabira potrebnih linija, dovedemo slučaj svih opisa u jedan oblik. Da biste to učinili, morate odabrati stupac Način plaćanja i na kartici transformacija izabrati tim Format – mala slova (Transformacija — Format — Mala slova):
Sada filtrirajte po koloni Način plaćanja koristeći opciju Filteri teksta – sadrži (Tekstualni filteri — sadrži):
U prozoru filtera, odmah pređite odozgo na režim Pored toga (Napredno) i uvesti tri pravila za odabir:
Kao što možete pretpostaviti, na taj način biramo sve redove u kojima je riječ “sber” prisutna na ili engleskom jeziku, plus one koji rade preko bilo koje banke. Ne zaboravite postaviti logičku vezu na lijevoj strani Or (OR) umjesto toga И (I) U suprotnom, pravilo neće raditi ispravno. Nakon klika na OK Na ekranu trebaju ostati samo opcije koje su nam potrebne:
Sada uklonite kolonu Način plaćanja desni klik na zaglavlje kolone Izbriši kolonu (Ukloni kolonu) i dalje raditi sa preostalom jednom kolonom kurseva:
Problem je što se tu, osim broja, nalazi i oznaka valute. Ovo se lako može očistiti jednostavnom zamjenom desnim klikom na naslov kolone i odabirom naredbe Zamjena vrijednosti (Zamijenite vrijednosti):
Brojevi dobijeni nakon uklanjanja RUB-a, zapravo, također još nisu brojevi, jer koriste nestandardne graničnike. Ovo se može izliječiti klikom na dugme za formatiranje u zaglavlju tabele, a zatim odabirom opcije Korištenje jezika (Koristite lokalce):
Najprikladnija lokacija bi bila English (US) i tip podataka – Дdecimalni broj:
Posle klika na OK dobićemo pune numeričke vrijednosti otkupnih stopa:
Ostaje izračunati prosjek za njih na kartici Transformacija – Statistika – Prosjek (Transform — Statistika — Prosjek) i učitajte rezultirajući broj na list pomoću naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori i učitaj — Zatvori i učitaj za…):
Sada možemo dodati link na preuzetu stopu u formuli u našu tabelu portfelja i izračunati razliku u vrijednosti za sve naše investicije u trenutnom trenutku:
Sada možete povremeno otvarati ovu datoteku, desnim klikom na prompt i odabrati naredbu Ažurirajte i sačuvajte (Osvježiti), promatrajte promjene koje će se automatski učitati u našu tabelu.
PS
Kao što možete lako zamisliti, na potpuno isti način možete uvesti kurs ne samo bitcoina, već i bilo koje druge valute, dionice ili vrijednosnice. Glavna stvar je pronaći odgovarajuću stranicu i napraviti upit, a onda će pametni Power Query učiniti sve.
- Uvoz kurseva sa interneta
- Funkcija za dobivanje tečaja za bilo koji datum
- Sastavljanje tabela iz različitih datoteka pomoću Power Queryja