Съдържание
Да приемем, че сте проявили бизнес усет и интуиция и сте закупили няколко порции от някаква криптовалута (същия биткойн, например) в миналото. Под формата на интелигентна таблица вашето „инвестиционно портфолио“ изглежда така:
Задача: бързо да оцените текущата стойност на вашите инвестиции при текущия курс на криптовалута. Ще вземем курса в Интернет от всеки подходящ сайт (обмен, обменник) и средно за надеждност.
Едно от решенията – класическа уеб заявка – вече разгледах подробно на примера за импортиране на валутния курс. Сега нека опитаме, за промяна, да използваме друг метод - добавката Power Query, която е идеална за импортиране на данни в Excel от външния свят, включително от Интернет.
Избор на сайт за импортиране
От кой сайт ще вземем данните - това като цяло няма значение. Класическата уеб заявка на Excel е много взискателна към структурата и вътрешния дизайн на импортираната уеб страница и понякога не работи на всеки сайт. Power Query е много по-всеяден по този въпрос. Така че можете да вземете средния процент на покупка, от който да избирате:
- в обменниците www.bestchange.ru – голям избор от опции, минимални рискове, но не много изгоден обменен курс
- от платформата за търговия www.localbitcoins.net – малко повече риск, но много по-добър курс
- от уебсайта на борсата – ако търгувате директно на борсата, едва ли имате нужда от тази статия 🙂
Първо, нека отворим сайта, от който се нуждаем, в браузъра. Да вземем, за конкретност, платформата за търговия localbitcoins.net. Изберете горния раздел Бърза продажба и опция Преводи през определена банка (или всяка друга, от която се нуждаете) и натиснете бутона Търсене:
Сега трябва да копирате адреса на страницата, която се появява в клипборда, защото. съдържа всички необходими параметри на заявката:
https://localbitcoins.net/instant-bitcoins/?action=продажба&код_на_държава=RU&сума=¤cy=RUB&place_country=RU& online_provider=SPECIFIC_BANK&find-offers=ТърсенеСлед това зависи от Power Query.
Импортиране на курс в Excel с помощта на Power Query
Ако имате инсталирани Excel 2010-2013 и Power Query като отделна добавка, тогава командата, от която се нуждаем, е в раздела със същото име – Запитване за захранване. Ако имате Excel 2016, тогава в раздела Дата (Дата) Натисни бутона От Интернет (От интернет). След това в прозореца, който се появява, трябва да поставите копирания адрес на уеб страница от предишния параграф и да щракнете OK:
След анализиране на уеб страницата Power Query ще покаже прозорец със списък от таблици, които могат да бъдат импортирани. Трябва да намерите необходимата таблица в списъка отляво (има няколко от тях), като се фокусирате върху визуализацията вдясно и след това щракнете върху бутона по-долу Корекция (Edit):
След това ще се отвори основният прозорец на редактора на заявки на Power Query, в който можем да изберем само необходимите редове и да осредним скоростта на покупка върху тях:
Препоръчвам незабавно да преименуваме нашата заявка в панела вдясно, като й дадем някакво разумно име:
Филтриране и почистване на данни
В бъдеще ще имаме нужда само от колони с описания Начин на плащане и процент на покупка Цена / BTC – за да можете безопасно да ги различите и двете с Ctrl и като щракнете с десния бутон върху тях, изберете командата Изтрийте други колони (Премахване на други колони) – всички колони ще бъдат изтрити с изключение на избраните.
Да кажем, че искаме да изберем само онези търговци, които работят чрез Сбербанк. Филтърът е познато нещо, но нюансът е, че филтърът в Power Query е чувствителен към главни и малки букви, тоест Сбербанк, Сбербанк и Сбербанк не са едно и също за него. Ето защо, преди да изберете необходимите редове, нека приведем случая на всички описания в една форма. За да направите това, трябва да изберете колона Начин на плащане и на раздела Трансформация изберете екип Формат – малки букви (Трансформиране — Форматиране — Малки букви):
Сега филтрирайте по колона Начин на плащане използване на опция Текстови филтри – Съдържа (Текстови филтри — Съдържа):
В прозореца на филтъра веднага превключете отгоре към режима Освен това (Advanced) и въведете три правила за избор:
Както може би се досещате, като правим това, ние избираме всички редове, където думата „sber“ присъства на или английски, плюс тези, които работят чрез която и да е банка. Не забравяйте да зададете логическа връзка отляво Or (OR) вместо И (И) В противен случай правилото няма да работи правилно. След като щракнете върху OK На екрана трябва да останат само опциите, от които се нуждаем:
Сега премахнете колоната Начин на плащане щракнете с десния бутон върху заглавката на колоната Изтриване на колона (Премахване на колона) и работете по-нататък с оставащата единична колона от курсове:
Проблемът при него е, че там освен цифрата има и обозначение на валутата. Това може лесно да се изчисти с просто заместване, като щракнете с десния бутон върху заглавието на колоната и изберете командата Замяна на стойности (Замяна на стойности):
Числата, получени след премахването на RUB, всъщност също все още не са числа, тъй като използват нестандартни разделители. Това може да се коригира, като щракнете върху бутона за форматиране в заглавката на таблицата и след това изберете опцията Използване на Locale (Използвайте местни):
Най-подходящият локал би бил Английски (САЩ) и тип данни – Ддесетично число:
След като кликнете върху OK ще получим пълните числени стойности на курсовете за покупка:
Остава да изчислим средната стойност за тях на табл Трансформация – Статистика – Средно (Трансформация — Статистика — Средно) и качете полученото число в листа с командата Начало — Затвори и зареди — Затвори и зареди в… (Начало — Затвори и зареди — Затвори и зареди в...):
Сега можем да добавим връзка към изтегления лихвен процент във формулата към нашата таблица с портфолио и да изчислим разликата в стойността за всички наши инвестиции в текущия момент:
Сега можете периодично да отваряте този файл, да щракнете с десния бутон върху подканата и да изберете командата Актуализиране и запазване (Опресняване), наблюдавайте промените, които автоматично ще бъдат заредени в нашата таблица.
PS
Както лесно можете да си представите, по абсолютно същия начин можете да импортирате курса не само на биткойни, но и на всяка друга валута, акции или ценни книжа. Основното нещо е да намерите подходящ сайт и да създадете заявка, а след това интелигентната Power Query ще направи всичко.
- Импортирайте валутни курсове от интернет
- Функция за получаване на обменния курс за всяка дадена дата
- Сглобяване на таблици от различни файлове с помощта на Power Query