Загружаем курсы криптовалют с помощью api и Power Query
Курсы валют через Power Query
Добрый день, уважаемые читатели блога! Сегодня речь пойдёт снова о загрузке курсов валют, только теперь мы обратимся к криптовалютам.
Основным источником данных о котировках будут служить биржи — Kraken, Binance, Poliniex, Bitterex и прочие. Что их объединяет? Ответ прост — API (application programming interface, программный интерфейс приложения, если по-русски).
Самое забавное, что к этому интерфейсу нельзя обратиться с помощью простого web запроса в Excel. Как мы это делали раньше. Выход есть — использовать Power Query.
Power Query — это специальная технология получения и анализа данных от компании Microsoft. Для Excel 2016 её отдельная установка не требуется (пакет уже включён в программу), для всех остальных версий придётся качать отдельно, также нужно обратить внимание на разрядность вашего офиса (х32, х64).
Скачали, установили и теперь в Excel появится новая вкладка — POWER QUERY.
Вкладка выглядит следующим образом:
В принципе можно рассматривать Power Query как надстройку над вкладкой "Данные", так как у нас расширяется выбор источников получения данных.
Ну что ж, теперь перейдём непосредственно к загрузке. Цель — получить соотношение покупки продажи криптвалютных пар (ETH/BTC). К примеру, нужно загрузить курс с биржи Binance.
Весь труд будет в следующем — найти API биржи и его "руководство пользователя". Для нашей биржи оно находится по адресу:
Базовой точкой входа будет:
И далее идёт пространное описание возможностей синтаксиса. Экспериментальным путём и опираясь на это руководство пользователя, выяснилось следующее: для получения данных по конкретной паре валют необходимо указать путь до "бегущей строки" биржи и прописать символьное обозначение пары.
- v3 — версия api биржи;
- ticker — "бегущая строка" котировок;
- price?symbol=ETHBTC — текущая стоимость продажи.
Воспользуемся полученной ссылкой. Переходим на вкладку "Power Query" и нажимаем кнопку "Из интернета" (она самая первая). Перед нами появится окно с запросом источника загрузки данных, в поле "URL адрес" записываем адрес интересующей пары. Для загрузки остальных нужно просто поменять краткие названия криптовалют в конце строки.
Нажимаем "ОК" и попадаем в редактор запроса и представления на листе Excel.
Видим, появился редактор, данные и параметры запроса. Мы находимся на вкладке "Конвертировать". Перейдём на вкладку "Просмотр" и поставим галку "Строка формул", чтобы видеть запрос в привычном представлении.
Возвращаемся на вкладку "Конвертировать" и нажимаем кнопку "В таблицу".
Поскольку нам ничего пока больше не нужно, в появившемся окне нажимаем "Закрыть и загрузить", чтобы данный запрос появился на листе.
В результате получим вот такую картину.
Цель достигнута, курсы подгрузились. Теперь нужно настроить их на автоматическое обновление. Справа у нас есть столбец "Параметры запроса", щелкаем правой кнопкой мышки по нашем запросу и выбираем "Свойства", ставим галку "Быстрая загрузка" -> Ок.
Далее мы оказываемся на вкладке "Конструктор", в блоке кнопок "Данные из внешней таблицы" щёлкаем по треугольнику после кнопки "Обновить", затем выбираем пункт "Свойства подключения". В следующем окне нужно настроить время обновления нашего запроса.
Проставляем галки как на рисунке. Тем самым мы добиваемся автоматического обновления котировок в течение одной минуты, а если нам нужно — можем обновить сами по нажатию кнопки. Нажимаем "ОК".
Готово! Ждём минуту, курс обновляется сам. Теперь котировки будут подгружаться автоматически.
Единственный минус — для большого количества запросов придётся всё создавать ручками, что в свою очередь будет сильно нагружать программу Excel при обновлении курсов криптовалют.
Импорт курса биткойна в Excel через Power Query
Предположим, что вы проявили деловое чутье и интуицию и закупили несколько порций какой-нибудь криптовалюты (того же биткойна, например) в прошлом. В виде умной таблицы ваш "инвестиционный портфель" выглядит, допустим, так:
Задача: оперативно оценивать текущую стоимость ваших инвестиций по текущему курсу криптовалюты. Курс будем брать в интернете с любого подходящего сайта (биржи, обменника) и усреднять для достоверности.
Один из вариантов решения — классический веб-запрос — я уже подробно рассматривал на примере импорта курса валют. Теперь же давайте попробуем, для разнообразия, использовать другой способ — надстройку Power Query, которая идеально подходит для импорта данных в Excel из внешнего мира, в том числе, и из интернета.
Выбираем сайт для импорта
С какого именно сайта будем брать данные — это, по большому счету, не принципиально. Классический веб-запрос Excel весьма требователен к структуре и внутренней конструкции импортируемой веб-страницы и, бывает, срабатывает не на каждом сайте. Power Query в этом вопросе гораздо более всеяден. Так что можно взять средний курс покупки на выбор:
- в обменниках www.bestchange.ru — большой выбор вариантов, минимальные риски, но не очень выгодный курс
- с торговой площадки www.localbitcoins.net — чуть больше риска, но сильно лучше курс
- с сайта биржи — если вы торгуете напрямую на бирже, то эта статья вам вряд ли нужна 🙂
Для начала откроем нужный нам сайт в обозревателе. Возьмем, для конкретности, торговую площадку localbitcoins.net. Выберем сверху вкладку Быстрая продажа и опцию Переводы через конкретный банк (или любую другую, нужную вам) и жмем кнопку Поиск:
Теперь нужно скопировать адрес появившейся страницы в буфер, т.к. он содержит все необходимые нам параметры запроса:
Дальше дело за 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) , наблюдать изменения, которые автоматически будут подгружаться в нашу таблицу.
Как легко сообразить, совершенно аналогичным образом можно импортировать курс не только биткойна, но и любой другой валюты, акции или ценной бумаги. Главное — найти подходящий сайт и построить запрос, а дальше все сделает умный Power Query.