Содержание:
Предположим, вы проявили деловое чутье и интуицию и купили в прошлом несколько порций какой-нибудь криптовалюты (того же биткойна, например). В виде умной таблицы ваш «инвестиционный портфель» выглядит так:
Задача: быстро оценить текущую стоимость ваших инвестиций при текущем курсе криптовалюты. Возьмем курс в Интернете с любого подходящего сайта (биржи, обменника) и среднего по надежности.
Одно из решений — классический веб-запрос — я уже подробно рассмотрел на примере импорта курса валют. Теперь попробуем для разнообразия использовать другой метод — надстройку Power Query, которая идеально подходит для импорта данных в Excel из внешнего мира, в том числе из Интернета.
Выбор сайта для импорта
С какого сайта мы будем брать данные – это, по большому счету, не имеет значения. Классический веб-запрос Excel очень требователен к структуре и внутреннему оформлению импортируемой веб-страницы и иногда работает не на каждом сайте. Power Query в этом вопросе гораздо более всеяден. Итак, вы можете взять среднюю стоимость покупки на выбор:
- в обменниках www.bestchange.ru – большой выбор вариантов, минимальные риски, но не очень выгодный курс обмена
- с торговой платформы www.localbitcoins.net – чуть больше риска, но гораздо лучший курс
- с сайта биржи — если вы торгуете напрямую на бирже, то эта статья вам вряд ли понадобится 🙂
Для начала откроем в браузере нужный нам сайт. Возьмем для конкретики торговую площадку localbitcoins.net. Выберите верхнюю вкладку Быстрая продажа и вариант Переводы через конкретный банк (или любой другой, который вам нужен) и нажмите кнопку Поиск:
Теперь нужно скопировать адрес появившейся страницы в буфер обмена, т.к. он содержит все необходимые нам параметры запроса:
https://localbitcoins.net/instant-bitcoins/?action=продаем&код_страны=RU&amount=¤cy=Рубль&place_country=RU& online_provider=КОНКРЕТНЫЙ_БАНК&find-offers=ПоискДальше дело за Power Query.
Импорт курса в Excel с помощью Power Query.
Если у вас Excel 2010-2013 и Power Query установлены отдельной надстройкой, то нужная нам команда находится на одноименной вкладке — Power Query. Если у вас Excel 2016, то на вкладке Данные (Дата) нажми на кнопку Из Интернета (Из Интернета). В появившемся окне необходимо вставить скопированный адрес веб-страницы из предыдущего пункта и нажать кнопку OK:
После анализа веб-страницы Power Query отобразит окно со списком таблиц, которые можно импортировать. Вам необходимо найти нужную таблицу в списке слева (их несколько), ориентируясь на превью справа, а затем нажать кнопку ниже Коррекция (Редактировать):
После этого откроется главное окно редактора запросов Power Query, в котором мы сможем выбрать только необходимые строки и усреднить по ним скорость покупки:
Рекомендую сразу переименовать наш запрос в панели справа, дав ему какое-нибудь вменяемое имя:
Фильтрация и очистка данных
В дальнейшем нам понадобятся только столбцы с описаниями Способ оплаты и скорость покупки Цена/БТД – так что вы можете смело различать их обоих с помощью Ctrl и щелкнув по ним правой кнопкой мыши, выберите команду Удалить другие столбцы (Удалить другие столбцы) – будут удалены все столбцы, кроме выбранных.
Допустим, мы хотим отобрать только тех трейдеров, которые работают через Сбербанк. Фильтр вещь привычная, но нюанс в том, что фильтр в Power Query чувствителен к регистру, т.е. Сбербанк, Сбербанк и Сбербанк для него не одно и то же. Поэтому, прежде чем выделять нужные строки, приведем корпус всех описаний к одному виду. Для этого вам нужно выбрать столбец Способ оплаты и на вкладке трансформация выбрать команду Формат – строчные буквы (Преобразование — Формат — Нижний регистр):
Теперь отфильтруйте по столбцу Способ оплаты используя опцию Текстовые фильтры – Содержит (Текстовые фильтры — Содержит):
В окне фильтра сразу переключитесь сверху в режим Дополнительно (Advanced) и ввести три правила выбора:
Как нетрудно догадаться, этим мы выбираем все строки, где присутствует слово «сбер» на или английском языке, плюс те, которые работают через любой банк. Не забудьте установить логическую ссылку слева Or (OR) вместо И (А также) В противном случае правило будет работать неправильно. После нажатия на OK На экране должны остаться только нужные нам опции:
Теперь удалите столбец Способ оплаты щелкните правой кнопкой мыши заголовок столбца Удалить столбец (Удалить столбец) и работаем дальше с оставшейся единственной колонкой курсов:
Проблема в том, что там кроме номера есть еще и обозначение валюты. Это можно легко исправить простой заменой, щелкнув правой кнопкой мыши заголовок столбца и выбрав команду Замена значений (Заменить значения):
Числа, полученные после удаления RUB, по сути, тоже еще не являются числами, поскольку используют нестандартные разделители. Это можно исправить, нажав кнопку форматирования в заголовке таблицы и выбрав опцию Использование локали (Используйте местные жители):
Наиболее подходящей локалью будет Английский (США) и тип данных – Ддесятичное число:
После нажатия на OK получим полные числовые значения ставок закупок:
Осталось посчитать среднее для них на вкладке Преобразование – Статистика – Среднее (Преобразование — Статистика — Среднее) и загрузим полученное число на лист командой На главную — Закрыть и загрузить — Закрыть и загрузить… (Домой — Закрыть и загрузить — Закрыть и загрузить в…):
Теперь мы можем добавить ссылку на скачанную ставку в формуле в нашу таблицу портфеля и посчитать разницу в стоимости всех наших инвестиций на текущий момент:
Теперь вы можете периодически открывать этот файл, кликая по подсказке правой кнопкой мыши и выбирая команду Обновить и сохранить (Обновить), наблюдаем за изменениями, которые автоматически загрузятся в нашу таблицу.
PS
Как вы легко можете себе представить, точно таким же образом вы можете импортировать курс не только биткойна, но и любой другой валюты, акций или ценных бумаг. Главное найти подходящий сайт и построить запрос, а дальше умный Power Query все сделает.
- Импортировать курсы валют из Интернета
- Функция для получения курса обмена на любую заданную дату
- Сборка таблиц из разных файлов с помощью Power Query