зміст
Припустимо, що ви проявили діловий глузд і інтуїцію і купили в минулому кілька порцій якоїсь криптовалюти (той же біткоін, наприклад). У вигляді розумної таблиці ваш «інвестиційний портфель» виглядає так:
Завдання: швидко оцінити поточну вартість ваших інвестицій за поточним курсом криптовалюти. Візьмемо курс в інтернеті з будь-якого підходящого сайту (біржа, обмінник) і середнього по надійності.
Одне з рішень – класичний веб-запит – я вже детально розглядав на прикладі імпорту курсу валют. Тепер для різноманітності спробуємо скористатися іншим способом – надбудовою 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=КОНКРЕТНИЙ_БАНК&find-offers=ПошукТоді справа за Power Query.
Імпортування курсу в Excel за допомогою Power Query
Якщо у вас встановлено Excel 2010-2013 і Power Query як окрему надбудову, то потрібна нам команда знаходиться на однойменній вкладці – PowerQuery. Якщо у вас Excel 2016, то на вкладці дані (Дата) натисніть кнопку З Інтернету (З Інтернету). У вікні, яке з’явиться, потрібно вставити скопійовану адресу веб-сторінки з попереднього абзацу та натиснути OK:
Після аналізу веб-сторінки Power Query відобразить вікно зі списком таблиць, які можна імпортувати. У списку зліва потрібно знайти потрібну таблицю (їх декілька), орієнтуючись на попередній перегляд справа, а потім натиснути кнопку нижче Корекція (Edit):
Після цього відкриється головне вікно редактора запитів Power Query, в якому ми можемо виділити тільки потрібні рядки і усереднити по них курс покупки:
Я рекомендую негайно перейменувати наш запит на панелі праворуч, давши йому якесь розумне ім'я:
Фільтрація та очищення даних
Надалі нам знадобляться тільки колонки з описами Спосіб оплати і курс купівлі Ціна / BTC – щоб ви могли безпечно розрізнити їх обох Ctrl і клацнувши на них правою кнопкою миші, виберіть команду Видалити інші стовпці (Видалити інші стовпці) – усі стовпці будуть видалені, крім вибраних.
Припустимо, ми хочемо вибрати тільки тих трейдерів, які працюють через Ощадбанк. Фільтр звична річ, але нюанс в тому, що фільтр в Power Query чутливий до регістру, тобто Сбербанк, Сбербанк і Сбербанк для нього не одне і те ж. Тому, перш ніж вибирати потрібні рядки, давайте приведемо корпус усіх описів до одного вигляду. Для цього потрібно вибрати колонку Спосіб оплати і на вкладці Перетворення вибрати команду Формат – малі літери (Перетворення — Формат — Нижній регістр):
Тепер фільтруйте за стовпцями Спосіб оплати використання опції Текстові фільтри – містить (Текстові фільтри — містить):
У вікні фільтра відразу перейдіть зверху в режим Додатково (Додатково) і ввести три правила вибору:
Як ви могли здогадатися, при цьому ми вибираємо всі рядки, де є слово «sber» англійською або мовою, а також ті, які працюють через будь-який банк. Не забудьте встановити логічне посилання зліва Or (АБО) замість И (І) Інакше правило працюватиме некоректно. Після натискання на OK На екрані повинні залишитися лише ті параметри, які нам потрібні:
Тепер зніміть колонку Спосіб оплати клацніть правою кнопкою миші заголовок стовпця Видалити стовпець (Видалити стовпець) і далі працювати з єдиним стовпцем курсів, що залишився:
Проблема його в тому, що там, крім номера, є ще й позначення валюти. Це можна легко очистити простою заміною, клацнувши правою кнопкою миші заголовок стовпця та вибравши команду Заміна значень (Замінити значення):
Числа, отримані після видалення RUB, по суті, також ще не є числами, тому що в них використовуються нестандартні роздільники. Це можна виправити, натиснувши кнопку форматування в заголовку таблиці та вибравши відповідний параметр Використання локалі (Використовуйте місцеві):
Найбільш відповідним місцем буде Англійська (США) і тип даних – Ддесяткове число:
Натиснувши на OK отримаємо повні числові значення курсів закупівлі:
Залишається обчислити середнє для них на табл Трансформація – Статистика – Середнє (Перетворення — Статистика — Середнє) і завантажити отримане число на аркуш з командою Головна — Закрийте та завантажте — Закрийте та завантажте в… (Домашня сторінка — Закрити та завантажити — Закрити та завантажити до…):
Тепер ми можемо додати посилання на завантажену ставку у формулу до таблиці нашого портфоліо та розрахувати різницю у вартості для всіх наших інвестицій на поточний момент:
Тепер ви можете періодично відкривати цей файл, клацати правою кнопкою миші на підказці та вибирати команду Update & Save (Оновити), спостерігайте за змінами, які будуть автоматично завантажені в нашу таблицю.
PS
Як ви можете легко собі уявити, точно таким же чином ви можете імпортувати курс не тільки біткойна, але й будь-якої іншої валюти, акції чи цінного паперу. Головне знайти підходящий сайт і побудувати запит, а далі розумний Power Query все зробить.
- Імпорт курсів валют з Інтернету
- Функція для отримання курсу обміну на будь-яку задану дату
- Складання таблиць із різних файлів за допомогою Power Query