Импорт курса биткойна в Excel через Power Query

Предположим, что вы проявили деловое чутье и интуицию и закупили несколько порций какой-нибудь криптовалюты (того же биткойна, например) в прошлом. В виде умной таблицы ваш "инвестиционный портфель" выглядит, допустим, так:

Портфель инвестиций в биткойн

Задача: оперативно оценивать текущую стоимость ваших инвестиций по текущему курсу криптовалюты. Курс будем брать в интернете с любого подходящего сайта (биржи, обменника) и усреднять для достоверности.

Один из вариантов решения - классический веб-запрос - я уже подробно рассматривал на примере импорта курса валют. Теперь же давайте попробуем, для разнообразия, использовать другой способ - надстройку Power Query, которая идеально подходит для импорта данных в Excel из внешнего мира, в том числе, и из интернета.

Выбираем сайт для импорта

С какого именно сайта будем брать данные - это, по большому счету, не принципиально. Классический веб-запрос Excel весьма требователен к структуре и внутренней конструкции импортируемой веб-страницы и, бывает, срабатывает не на каждом сайте. Power Query в этом вопросе гораздо более всеяден. Так что можно взять средний курс покупки на выбор:

  • в обменниках www.bestchange.ru - большой выбор вариантов, минимальные риски, но не очень выгодный курс
  • с торговой площадки www.localbitcoins.net - чуть больше риска, но сильно лучше курс
  • с сайта биржи - если вы торгуете напрямую на бирже, то эта статья вам вряд ли нужна :)

Для начала откроем нужный нам сайт в обозревателе. Возьмем, для конкретности, торговую площадку localbitcoins.net. Выберем сверху вкладку Быстрая продажа и опцию Переводы через конкретный банк (или любую другую, нужную вам) и жмем кнопку Поиск

Сайт для импорта

Теперь нужно скопировать адрес появившейся страницы в буфер, т.к. он содержит все необходимые нам параметры запроса:

https://localbitcoins.net/instant-bitcoins/?action=sell&country_code=RU&amount=&currency=RUB&place_country=RU&
online_provider=SPECIFIC_BANK&find-offers=Поиск

Дальше дело за Power Query.

Импортируем курс в Excel через Power Query

Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то нужная нам команда находится на одноименной вкладке - Power Query. Если у вас Excel 2016, то на вкладке Данные (Data) жмем кнопку Из интернета (From Internet). В появившееся затем окно нужно вставить скопированный адрес веб-страницы из предыдущего пункта и нажать ОК:

Импорт из интернета в Power Query

После анализа веб-страницы Power Query выдаст окно со списком таблиц, которые можно импортировать. Нужно найти требуемую таблицу в списке слева (их там бывает несколько), ориентируясь на предпросмотр справа, и нажать затем внизу кнопку Правка (Edit):

Навигатор

После этого откроется главное окно редактора запросов Power Query, в котором мы сможем отобрать только нужные строки и усреднить по ним курс покупки:

Данные

Рекомендую сразу переименовать наш запрос в панели справа, дав ему какое-нибудь вменяемое имя:

Имя запроса

Фильтруем и зачищаем данные

В дальнейшем нам нужны будут только столбцы с описанием Payment method и курсом покупки Price / BTC - так что можно смело выделить их оба с Ctrl и, щелкнув по ним правой кнопкой мыши, выбрать команду Удалить другие столбцы (Remove other columns) - будут удалены все столбцы кроме выделенных.

Допустим, что мы хотим отобрать только тех трейдеров, которые работают через Сбербанк. Фильтр - штука знакомая, но нюанс в том, что фильтр в Power Query чувствителен к регистру, т.е. Сбербанк, СБЕРБАНК и СберБанк для него не одно и то же. Поэтому, прежде чем отбирать нужные строки, давайте приведем регистр всех описаний к одному виду. Для этого нужно выделить столбец Payment method и на вкладке Преобразование выбрать команду Форматировать - нижний регистр (Transform - Format - Lower case):

Конвертация в нижний регистр

Теперь отфильтруем по столбцу Payment method с использованием опции Текстовые фильтры - Содержит (Text filters - Contains):

Текстовый фильтр

В окне фильтра сразу переключаемся сверху в режим Дополнительно (Advanced) и вводим три правила для отбора:

Фильтрация

Как легко догадаться, этим мы отбираем все строки, где присутствует слово "сбер" на русском или английском, плюс тех, кто работает через любой банк. Не забудьте установить слева логическую связку Или (OR) вместо И (And) - иначе правило не сработает корректно. После нажатия на ОК на экране должны остаться только нужные нам варианты:

Отфильтрованные строки

Теперь удаляем столбец Payment method правой кнопкой мыши по заголовку столбца - Удалить столбец (Remove column) и работаем дальше уже с оставшимся единственным столбцом курсов:

Столбец курсов

Проблема с ним в том, что там, кроме числа, лежит еще и обозначение валюты. Это можно легко вычистить простой заменой, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Замена значений (Replace values):

Вычищаем RUB

Получившиеся после удаления RUB числа, на самом деле, тоже еще не числа, т.к. в них используются нестандартные разделители. Это можно вылечить, нажав в шапке таблицы кнопку формата и выбрав затем опцию Используя локаль (Use locals):

Настройка числового формата

Наиболее подходящей будет локаль Английский (США) и тип данных - Десятичное число:

Числовой формат по локали

После нажатия на ОК мы получим полноценные числовые значения курсов покупки:

Готовые отфильтрованные курсы покупки

Останется посчитать по ним среднее на вкладке Трансформация - Статистика - Среднее (Transform - Statistics - Average) и выгрузить получившееся число на лист командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load To...):

Выгружаем курс на лист

Теперь можно добавить ссылку на загруженный курс в формулу к нашей таблице портфеля и посчитать разницу в стоимости по всем нашим инвестициям на текущий момент:

Итоговая таблица портфеля

Теперь можно периодически открывать этот файл, щелкать правой кнопкой мыши по запросу и, выбрав команду Обновить (Refresh), наблюдать изменения, которые автоматически будут подгружаться в нашу таблицу.

P.S.

Как легко сообразить, совершенно аналогичным образом можно импортировать курс не только биткойна, но и любой другой валюты, акции или ценной бумаги. Главное - найти подходящий сайт и построить запрос, а дальше все сделает умный Power Query.

Ссылки по теме


18.12.2017 08:32:35
Спасибо.. Все как всегда элегантно и просто....
28.01.2018 14:59:05
Можно ли подключится у сайту, который требует авторизации ? Я пробовал, что-то не получилось
Alex Veber
03.02.2018 00:28:07
Приветствую.

с bestchange.ru - у меня не получилось выдало ошибку
localbitcoins - сайт  уже заблокирован и ваш шаблон не работает

Можно ли настроить курсы с tradingview.com ?
04.02.2018 00:06:40
У меня получилось с coinmarketcap.com, все хорошо работает  
01.03.2018 09:45:18
Можно ли в Power Query выбирать максимальные или минимальные значения?  
01.03.2018 10:50:36
Да, там это называется Группировать (Group) - и потом выбираете по каким столбцам и какую функцию аггрегирования (мин, макс) хотите применить.
22.04.2018 11:03:07
Николай, а не разобрались как во всем столбце сделать контекстную замену подстроки текста в каждой ячейке? (той же точки на запятую)
пока поменял локальный разделитель и это прошло. Но хотелось бы иметь пример как это сделать с помощью функции text,replace или в какую функцию таблицы ее запихнуть чтобы она внутри меняла одно текстовое значение на другое.
Наверх