Как вставить курс криптовалют в excel
Перейти к содержимому

Как вставить курс криптовалют в excel

Загружаем курсы криптовалют с помощью 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 адрес" записываем адрес интересующей пары. Для загрузки остальных нужно просто поменять краткие названия криптовалют в конце строки.

курсы валют через Power Query

Нажимаем "ОК" и попадаем в редактор запроса и представления на листе Excel.

курсы валют через Power Query

Видим, появился редактор, данные и параметры запроса. Мы находимся на вкладке "Конвертировать". Перейдём на вкладку "Просмотр" и поставим галку "Строка формул", чтобы видеть запрос в привычном представлении.

курсы валют через Power Query

Возвращаемся на вкладку "Конвертировать" и нажимаем кнопку "В таблицу".

курсы валют через Power Query

Поскольку нам ничего пока больше не нужно, в появившемся окне нажимаем "Закрыть и загрузить", чтобы данный запрос появился на листе.

курсы валют через Power Query

В результате получим вот такую картину.

курсы валют через Power Query

Цель достигнута, курсы подгрузились. Теперь нужно настроить их на автоматическое обновление. Справа у нас есть столбец "Параметры запроса", щелкаем правой кнопкой мышки по нашем запросу и выбираем "Свойства", ставим галку "Быстрая загрузка" -> Ок.

курсы валют через Power Query

Далее мы оказываемся на вкладке "Конструктор", в блоке кнопок "Данные из внешней таблицы" щёлкаем по треугольнику после кнопки "Обновить", затем выбираем пункт "Свойства подключения". В следующем окне нужно настроить время обновления нашего запроса.

курсы валют через Power Query

Проставляем галки как на рисунке. Тем самым мы добиваемся автоматического обновления котировок в течение одной минуты, а если нам нужно — можем обновить сами по нажатию кнопки. Нажимаем "ОК".

Готово! Ждём минуту, курс обновляется сам. Теперь котировки будут подгружаться автоматически.

Единственный минус — для большого количества запросов придётся всё создавать ручками, что в свою очередь будет сильно нагружать программу 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

После анализа веб-страницы 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) , наблюдать изменения, которые автоматически будут подгружаться в нашу таблицу.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *