Sadržaj
Pretpostavimo da ste pokazali poslovni smisao i intuiciju i kupili nekoliko porcija neke kriptovalute (isti bitcoin, na primjer) u prošlosti. U obliku pametne tablice vaš “investicijski portfelj” izgleda ovako:
Zadatak: brzo procijeniti trenutnu vrijednost vaših ulaganja po trenutnom tečaju kriptovalute. Provest ćemo tečaj na Internetu s bilo kojeg prikladnog mjesta (mjenjačnica, izmjenjivač) i prosječne pouzdanosti.
Jedno od rješenja – klasični web zahtjev – već sam detaljno razmotrio na primjeru uvoza tečajnice. Pokušajmo sada, za promjenu, upotrijebiti drugu metodu – dodatak Power Query, koji je idealan za uvoz podataka u Excel iz vanjskog svijeta, uključujući i s Interneta.
Odabir web mjesta za uvoz
S koje stranice ćemo uzeti podatke - to uglavnom nije važno. Klasični Excel web upit vrlo je zahtjevan za strukturu i interni dizajn uvezene web stranice i ponekad ne radi na svim stranicama. Power Query je mnogo svejediji po ovom pitanju. Dakle, možete odabrati prosječnu kupovnu stopu:
- u mjenjačima www.bestchange.ru – veliki izbor opcija, minimalni rizici, ali ne baš isplativ tečaj
- s platforme za trgovanje www.localbitcoins.net – malo više rizika, ali puno bolji tečaj
- s web stranice burze – ako trgujete izravno na burzi, onda vam ovaj članak jedva da treba 🙂
Prvo, otvorimo stranicu koja nam je potrebna u pregledniku. Uzmimo, konkretno radi, platformu za trgovanje localbitcoins.net. Odaberite gornju karticu Brza prodaja i opcija Transferi preko određene banke (ili bilo koji drugi koji vam je potreban) i pritisnite gumb Traži:
Sada trebate kopirati adresu stranice koja se pojavljuje u međuspremnik, jer. sadrži sve parametre zahtjeva koji su nam potrebni:
https://localbitcoins.net/instant-bitcoins/?action=prodati&country_code=RU&iznos=¤cy=TRLJATI&place_country=RU& online_provider=SPECIFIC_BANK&find-offers=TražiZatim je na Power Queryju.
Uvoz tečaja u Excel pomoću Power Queryja
Ako imate Excel 2010-2013 i Power Query instaliran kao zaseban dodatak, tada se naredba koja nam treba nalazi na kartici istog naziva – Upit snage. Ako imate Excel 2016, tada na kartici Datum (Datum) pritisni gumb S interneta (S interneta). U prozoru koji se pojavi potrebno je zalijepiti kopiranu adresu web stranice iz prethodnog paragrafa i kliknuti OK:
Nakon analiziranja web stranice, Power Query će prikazati prozor s popisom tablica koje se mogu uvesti. Morate pronaći željenu tablicu na popisu s lijeve strane (ima ih nekoliko), fokusirajući se na pregled s desne strane, a zatim kliknite gumb ispod Korekcija (Uredi):
Nakon toga otvorit će se glavni prozor uređivača upita Power Query u kojem možemo odabrati samo potrebne retke i prosječno izračunati stopu kupnje po njima:
Preporučujem da odmah preimenujete naš zahtjev na ploči s desne strane, dajući mu neko zdravo ime:
Filtriranje i čišćenje podataka
Ubuduće će nam trebati samo stupci s opisima Način plaćanja i kupovni tečaj Cijena / BTC – tako da ih oboje možete sigurno razlikovati pomoću Ctrl te desnim klikom na njih odabrati naredbu Izbrišite ostale stupce (Ukloni druge stupce) – svi stupci će biti izbrisani osim odabranih.
Recimo da želimo odabrati samo one trgovce koji rade preko Sberbanke. Filter je poznata stvar, ali nijansa je u tome što filter u Power Queryju razlikuje velika i mala slova, odnosno Sberbank, Sberbank i Sberbank za njega nisu isto. Stoga, prije odabira potrebnih redaka, dovedimo 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 stupcu Način plaćanja koristeći opciju Filtri teksta – Sadrži (Filtri teksta — Sadrži):
U prozoru filtera odmah se prebacite odozgo na način rada Dodatno (Advanced) i uvesti tri pravila za odabir:
As you might guess, by doing this we select all the lines where the word “sber” is present in or English, plus those who work through any bank. Don’t forget to set a logical link on the left Or (ILI) umjesto toga И (I) U suprotnom, pravilo neće raditi ispravno. Nakon klika na OK Na ekranu bi trebale ostati samo opcije koje su nam potrebne:
Sada uklonite stupac Način plaćanja desni klik na zaglavlje stupca Izbriši stupac (Ukloni stupac) i dalje radite s preostalim pojedinačnim stupcem tečajeva:
Problem s njim je što tu, osim broja, postoji i oznaka valute. To se lako može očistiti jednostavnom zamjenom desnim klikom na naslov stupca i odabirom naredbe Zamjena vrijednosti (Zamijeni vrijednosti):
Brojevi dobiveni nakon uklanjanja RUB-a zapravo još nisu brojevi jer koriste nestandardne graničnike. Ovo se može izliječiti klikom na gumb za formatiranje u zaglavlju tablice i zatim odabirom opcije Korištenje jezika (Koristite lokalno):
Najprikladniji lokalitet bio bi English (US) i tip podataka – Дdecimalni broj:
Nakon što kliknete OK dobit ćemo pune numeričke vrijednosti otkupnih stopa:
Ostaje izračunati prosjek za njih na kartici Transformacija – Statistika – Prosjek (Transformacija — Statistika — Prosjek) i učitati dobiveni broj na list s naredbom Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori i učitaj — Zatvori i učitaj u…):
Sada možemo dodati poveznicu na preuzetu stopu u formuli u našu tablicu portfelja i izračunati razliku u vrijednosti za sva naša ulaganja u trenutnom trenutku:
Sada možete povremeno otvarati ovu datoteku, desnom tipkom miša kliknuti upit i odabrati naredbu Ažurirajte i spremite (Osvježiti), promatrajte promjene koje će se automatski učitati u našu tablicu.
PS
Kao što lako možete zamisliti, na točno isti način možete uvesti tečaj ne samo bitcoina, već i bilo koje druge valute, dionice ili vrijednosnice. Najvažnije je pronaći odgovarajuće mjesto i napraviti upit, a onda će pametni Power Query učiniti sve.
- Uvoz tečajeva s interneta
- Funkcija za dobivanje tečaja za bilo koji datum
- Sastavljanje tablica iz različitih datoteka pomoću Power Queryja