Spis treści
Załóżmy, że w przeszłości wykazałeś się zmysłem biznesowym i intuicją oraz kupiłeś kilka porcji jakiejś kryptowaluty (na przykład tego samego bitcoina). W formie inteligentnej tabeli Twój „portfel inwestycyjny” wygląda tak:
Zadanie: szybko oszacować aktualną wartość swoich inwestycji przy aktualnym kursie kryptowaluty. Weźmiemy kurs w Internecie z dowolnej odpowiedniej strony (giełda, wymiennik) i średnio za niezawodność.
Jedno z rozwiązań – klasyczne żądanie sieciowe – już szczegółowo rozważałem na przykładzie importu kursu walutowego. Spróbujmy teraz dla odmiany skorzystać z innej metody — dodatku Power Query, który idealnie nadaje się do importowania danych do programu Excel ze świata zewnętrznego, w tym z Internetu.
Wybór witryny do importu
Z której strony będziemy pobierać dane – to w zasadzie nie ma znaczenia. Klasyczne zapytanie internetowe Excela jest bardzo wymagające pod względem struktury i wewnętrznego projektu importowanej strony internetowej i czasami nie działa w każdej witrynie. Dodatek Power Query jest pod tym względem znacznie bardziej wszystkożerny. Możesz więc wybrać średni kurs zakupu do wyboru:
- w wymiennikach www.bestchange.ru – duży wybór opcji, minimalne ryzyko, ale niezbyt opłacalny kurs wymiany
- z platformy handlowej www.localbitcoins.net – trochę większe ryzyko, ale znacznie lepsza stawka
- ze strony giełdy – jeśli handlujesz bezpośrednio na giełdzie, to prawie nie potrzebujesz tego artykułu 🙂
Najpierw otwórzmy potrzebną nam witrynę w przeglądarce. Weźmy dla konkretów platformę handlową localbitcoins.net. Wybierz górną kartę Szybka wyprzedaż i opcja Przelewy za pośrednictwem określonego banku (lub dowolny inny, którego potrzebujesz) i naciśnij przycisk Szukaj:
Teraz musisz skopiować adres strony, która pojawia się w schowku, ponieważ. zawiera wszystkie potrzebne parametry żądania:
https://localbitcoins.net/instant-bitcoins/?action=sprzedać&kod_kraju=RU&ilość=¤cy=POC&place_country=RU& online_provider=OKREŚLONY_BANK&find-offers=SzukajNastępnie wszystko zależy od dodatku Power Query.
Importowanie kursu do Excela za pomocą Power Query
Jeśli masz zainstalowany program Excel 2010-2013 i Power Query jako osobny dodatek, potrzebne polecenie znajduje się na karcie o tej samej nazwie – Zapytanie o moc. Jeśli masz Excel 2016, to na karcie Dane (Data) naciśnij przycisk Z Internetu (Z Internetu). W wyświetlonym oknie należy wkleić skopiowany adres strony internetowej z poprzedniego akapitu i kliknąć OK:
Po przeanalizowaniu strony internetowej dodatek Power Query wyświetli okno z listą tabel, które można zaimportować. Musisz znaleźć żądaną tabelę na liście po lewej stronie (jest ich kilka), skupiając się na podglądzie po prawej stronie, a następnie kliknij przycisk poniżej Korekta (Edytować):
Następnie otworzy się główne okno edytora zapytań Power Query, w którym możemy wybrać tylko niezbędne wiersze i uśrednić dla nich wskaźnik zakupów:
Polecam natychmiast zmienić nazwę naszej prośby w panelu po prawej stronie, nadając jej rozsądną nazwę:
Filtrowanie i czyszczenie danych
W przyszłości będziemy potrzebować tylko kolumn z opisami Metoda płatności i stawka kupna Cena / BTC – dzięki czemu można je bezpiecznie odróżnić za pomocą Ctrl i klikając je prawym przyciskiem myszy, wybierz polecenie Usuń inne kolumny (Usuń inne kolumny) – usunięte zostaną wszystkie kolumny oprócz wybranych.
Załóżmy, że chcemy wybrać tylko tych traderów, którzy pracują za pośrednictwem Sbierbanku. Filtr jest znajomą rzeczą, ale niuans polega na tym, że filtr w Power Query rozróżnia wielkość liter, tj. Sbierbank, Sbierbank i Sbierbank nie są dla niego takie same. Dlatego przed wybraniem niezbędnych wierszy sprowadźmy przypadek wszystkich opisów do jednej postaci. Aby to zrobić, musisz wybrać kolumnę Metoda płatności i na karcie Transformacja wybierz drużynę Format – małe litery (Przekształć — Format — Małe litery):
Teraz filtruj według kolumny Metoda płatności używając opcji Filtry tekstowe – zawiera (Filtry tekstowe — Zawiera):
W oknie filtra natychmiast przełącz się z góry na tryb do tego (Zaawansowane) i wprowadzić trzy zasady selekcji:
Jak można się domyślić, w ten sposób zaznaczamy wszystkie linie, w których występuje słowo „sber” w języku angielskim, a także te, które działają za pośrednictwem dowolnego banku. Nie zapomnij ustawić łącza logicznego po lewej stronie Or (OR) zamiast И (I) W przeciwnym razie reguła nie będzie działać poprawnie. Po kliknięciu OK Na ekranie powinny pozostać tylko te opcje, których potrzebujemy:
Teraz usuń kolumnę Metoda płatności kliknij prawym przyciskiem myszy nagłówek kolumny Usuń kolumnę (Usuń kolumnę) i dalej pracuj z pozostałą pojedynczą kolumną kursów:
Problem polega na tym, że tam, oprócz liczby, znajduje się również oznaczenie waluty. Można to łatwo wyczyścić za pomocą prostego podstawienia, klikając prawym przyciskiem myszy nagłówek kolumny i wybierając polecenie Wymiana wartości (Zastąp wartości):
Liczby uzyskane po usunięciu RUB w rzeczywistości również nie są jeszcze liczbami, ponieważ używają niestandardowych ograniczników. Można to wyleczyć, klikając przycisk formatu w nagłówku tabeli, a następnie wybierając opcję Korzystanie z ustawień regionalnych (Użyj miejscowych):
Najbardziej odpowiednią lokalizacją byłoby English (US) i typ danych – Дliczba dziesiętna:
Po kliknięciu OK otrzymamy pełne wartości liczbowe stawek zakupu:
Pozostaje obliczyć dla nich średnią na karcie Transformacja – Statystyka – Średnia (Przemiana — Statystyka — Średnia) i wgraj wynikowy numer do arkusza za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…):
Teraz możemy dodać link do pobranego kursu w formule do naszej tabeli portfela i obliczyć różnicę wartości dla wszystkich naszych inwestycji w danym momencie:
Teraz możesz okresowo otwierać ten plik, kliknij prawym przyciskiem myszy monit i wybierz polecenie Zaktualizuj i zapisz (Odświeżać), obserwuj zmiany, które zostaną automatycznie załadowane do naszej tabeli.
PS
Jak łatwo sobie wyobrazić, dokładnie w ten sam sposób możesz zaimportować kurs nie tylko bitcoina, ale także dowolnej innej waluty, akcji lub papierów wartościowych. Najważniejsze jest, aby znaleźć odpowiednią witrynę i zbudować zapytanie, a wtedy inteligentne Power Query zrobi wszystko.
- Importuj kursy walut z Internetu
- Funkcja, aby uzyskać kurs wymiany na dowolny dzień
- Składanie tabel z różnych plików za pomocą dodatku Power Query