vsebina
Predpostavimo, da ste pokazali poslovni smisel in intuicijo ter v preteklosti kupili več porcij neke kriptovalute (isti bitcoin, na primer). V obliki pametne tabele je vaš »naložbeni portfelj« videti takole:
Naloga: hitro oceniti trenutno vrednost vaših naložb po trenutnem tečaju kriptovalute. Tečaj bomo opravili na internetu s katerega koli primernega mesta (borza, izmenjevalec) in povprečje za zanesljivost.
Eno izmed rešitev – klasično spletno zahtevo – sem že podrobno obravnaval na primeru uvoza tečajnice. Zdaj pa poskusimo za spremembo uporabiti drugo metodo – dodatek Power Query, ki je idealen za uvoz podatkov v Excel iz zunanjega sveta, tudi iz interneta.
Izbira mesta za uvoz
S katerega mesta bomo vzeli podatke - to na splošno ni pomembno. Klasična Excelova spletna poizvedba je zelo zahtevna glede strukture in notranje zasnove uvožene spletne strani in včasih ne deluje na vseh mestih. Power Query je v tej zadevi veliko bolj vsejed. Tako lahko izberete povprečni nakupni tečaj:
- v menjalnikih www.bestchange.ru – velika izbira možnosti, minimalna tveganja, vendar ne zelo donosen menjalni tečaj
- iz trgovalne platforme www.localbitcoins.net – malo več tveganja, a veliko boljši tečaj
- s spletne strani borze – če trgujete neposredno na borzi, potem tega članka skoraj ne potrebujete 🙂
Najprej v brskalniku odpremo spletno mesto, ki ga potrebujemo. Vzemimo, zaradi konkretnosti, trgovalno platformo localbitcoins.net. Izberite zgornji zavihek Hitra prodaja in možnost Prenosi prek določene banke (ali katero koli drugo, kar potrebujete) in pritisnite gumb Iskalnik:
Zdaj morate naslov strani, ki se prikaže, kopirati v odložišče, ker. vsebuje vse parametre zahteve, ki jih potrebujemo:
https://localbitcoins.net/instant-bitcoins/?action=prodaja&koda_države=RU&znesek=¤cy=RUB&place_country=RU& spletni_ponudnik=SPECIFIC_BANK&find-offers=IščiPotem je na vrsti Power Query.
Uvoz tečaja v Excel s pomočjo Power Query
Če imate Excel 2010-2013 in Power Query nameščen kot ločen dodatek, je ukaz, ki ga potrebujemo, na istoimenskem zavihku – poizvedba o moči. Če imate Excel 2016, potem na zavihku datum (Datum) pritisni gumb Iz interneta (Iz interneta). V oknu, ki se prikaže, morate prilepiti kopiran naslov spletne strani iz prejšnjega odstavka in klikniti OK:
Po razčlenjevanju spletne strani bo Power Query prikazal okno s seznamom tabel, ki jih je mogoče uvoziti. Na seznamu na levi morate poiskati želeno tabelo (več jih je), s poudarkom na predogledu na desni in nato kliknite spodnji gumb Popravek (Uredi):
Po tem se odpre glavno okno urejevalnika poizvedb Power Query, v katerem lahko izberemo le potrebne vrstice in po njih izračunamo povprečje nakupne stopnje:
Priporočam, da nemudoma preimenujete našo zahtevo v plošči na desni in ji date neko razumno ime:
Filtriranje in čiščenje podatkov
V prihodnje bomo potrebovali le stolpce z opisi Način plačila in nakupni tečaj Cena / BTC – tako da ju lahko varno ločite z Ctrl in z desnim klikom nanje izberemo ukaz Izbrišite druge stolpce (Odstrani druge stolpce) – vsi stolpci bodo izbrisani razen izbranih.
Recimo, da želimo izbrati samo tiste trgovce, ki delajo prek Sberbank. Filter je znana zadeva, niansa pa je v tem, da je filter v Power Queryju občutljiv na velike in male črke, torej Sberbank, Sberbank in Sberbank zanj niso isto. Zato, preden izberete potrebne vrstice, združimo vse opise v eno obliko. Če želite to narediti, morate izbrati stolpec Način plačila in na zavihku Preoblikovanje izberite ekipo Oblika – male črke (Pretvorba — Oblika — Male črke):
Zdaj filtrirajte po stolpcu Način plačila uporabo možnosti Besedilni filtri – Vsebuje (Besedilni filtri — Vsebuje):
V oknu filtra takoj preklopite od zgoraj na način Poleg tega (Napredno) in predstavite tri pravila za izbiro:
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 (OR) Namesto И (In) V nasprotnem primeru pravilo ne bo delovalo pravilno. Po kliku na OK Na zaslonu naj ostanejo samo možnosti, ki jih potrebujemo:
Zdaj odstranite stolpec Način plačila desni klik na glavo stolpca Izbriši stolpec (Odstrani stolpec) in delajte naprej s preostalim enim stolpcem tečajev:
Težava pri njem je, da je tam poleg številke tudi oznaka valute. To lahko enostavno počistite s preprosto zamenjavo tako, da z desno miškino tipko kliknete naslov stolpca in izberete ukaz Zamenjava vrednosti (Zamenjaj vrednosti):
Številke, pridobljene po odstranitvi RUB, pravzaprav še niso številke, ker uporabljajo nestandardna ločila. To lahko odpravite tako, da kliknete gumb za oblikovanje v glavi tabele in nato izberete možnost Uporaba jezika (Uporabi lokalne):
Najbolj primeren kraj bi bil Angleščina (ZDA) in tip podatkov – Дdecimalno število:
Po kliku OK dobili bomo celotne številčne vrednosti nakupnih tečajev:
Ostaja še izračunati povprečje zanje na zavihku Transformacija – Statistika – Povprečje (Pretvorba — Statistika — Povprečje) in dobljeno številko naložite na list z ukazom Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri & Naloži — Zapri & Naloži v…):
Zdaj lahko v našo portfeljsko tabelo dodamo povezavo do prenesenega tečaja v formuli in izračunamo razliko v vrednosti za vse naše naložbe v trenutnem trenutku:
Zdaj lahko občasno odprete to datoteko, z desno miškino tipko kliknete poziv in izberete ukaz Posodobi in shrani (Osveži), opazujte spremembe, ki se bodo samodejno naložile v našo tabelo.
PS
Kot si zlahka predstavljate, lahko na popolnoma enak način uvozite tečaj ne samo bitcoina, ampak tudi katere koli druge valute, delnice ali vrednostnega papirja. Glavna stvar je najti primerno spletno mesto in zgraditi poizvedbo, nato pa bo pametni Power Query naredil vse.
- Uvozi menjalne tečaje iz interneta
- Funkcija za pridobitev menjalnega tečaja za kateri koli dan
- Sestavljanje tabel iz različnih datotek s pomočjo Power Query