Импорт курса биткойна в 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=¤cy=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 выдаст окно со списком таблиц, которые можно импортировать. Нужно найти требуемую таблицу в списке слева (их там бывает несколько), ориентируясь на предпросмотр справа, и нажать затем внизу кнопку Правка (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 числа, на самом деле, тоже еще не числа, т.к. в них используются нестандартные разделители. Это можно вылечить, нажав в шапке таблицы кнопку формата и выбрав затем опцию Используя локаль (Use locals):
Наиболее подходящей будет локаль Английский (США) и тип данных - Десятичное число:
После нажатия на ОК мы получим полноценные числовые значения курсов покупки:
Останется посчитать по ним среднее на вкладке Трансформация - Статистика - Среднее (Transform - Statistics - Average) и выгрузить получившееся число на лист командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load To...):
Теперь можно добавить ссылку на загруженный курс в формулу к нашей таблице портфеля и посчитать разницу в стоимости по всем нашим инвестициям на текущий момент:
Теперь можно периодически открывать этот файл, щелкать правой кнопкой мыши по запросу и, выбрав команду Обновить (Refresh), наблюдать изменения, которые автоматически будут подгружаться в нашу таблицу.
P.S.
Как легко сообразить, совершенно аналогичным образом можно импортировать курс не только биткойна, но и любой другой валюты, акции или ценной бумаги. Главное - найти подходящий сайт и построить запрос, а дальше все сделает умный Power Query.
Ссылки по теме
- Импорт курса валют из интернета
- Функция для получения курса валюты для любой заданной даты
- Сборка таблиц из разных файлов с помощью Power Query
с bestchange.ru - у меня не получилось выдало ошибку
localbitcoins - сайт уже заблокирован и ваш шаблон не работает
Можно ли настроить курсы с tradingview.com ?
пока поменял локальный разделитель и это прошло. Но хотелось бы иметь пример как это сделать с помощью функции text,replace или в какую функцию таблицы ее запихнуть чтобы она внутри меняла одно текстовое значение на другое.