Разбираем загрузку валютных курсов с сайта ЦБ РФ в MS Excel. Подобная статья есть и для Power BI (ссылка).
Этап 1: Сгенерим ссылку на сайте ЦБ РФ.
Зайдём на сайт по адресу: https://www.cbr.ru/ В меню найдём раздел - “Динамика официального курса заданной валюты” (“Документы и данные” - “Базы данных” - “Базы данных по курсам валют” - “Динамика официального курса заданной валюты”)
Выберем табличный вид, выберем валюту, период дат. Нажмём - “Получить данные”. Увидим табличку с курсами валюты за выбранный диапазон дат:
Обратим внимание, что в ссылке содержатся даты выбранного диапазона. Нам это пригодится, когда будем настраивать адаптируемый под нашу задачу диапазон дат.
В Excel перейдём на панели вкладку “Данные”, выберем получение данных с web-страницы (“Создать запрос” - “Из других источников” - “Из интернета”):
В появившемся окошке вставим полученную на предыдущем этапе ссылку на сайт ЦБ РФ:
В навигаторе увидим, что на странице распознано четыре табличных элемента. Нам нужен последний. Нажмём “Преобразовать данные”:
Попадаем в Power Query, где мы можем произвести дополнительную обработку данных перед загрузкой в модель данных Power BI. В правой плашке видим уже произведённые шаги - подключение к источнику данных, навигация по таблицам и изменение типов данных. Шаг с изменением типов данных удалим (если оставить, ничего страшного не случится, но он лишний):
Далее удалим первую строку с указанием на валюту:
И поднимем оставшуюся строку до уровня заголовков:
Получим вот такую вполне приличную табличку:
Этап 3: Сделаем диапазон дат подстраивающимся под реальность:
Отправимся в “Расширенный редактор” (“Advanced Editor”): Там увидим вот такой код, который описывает на языке M все действия, которые мы произвели ранее:
Для желающих сократить путь, вот этот код:
let
Источник = Web.Page(Web.Contents("https://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01010&UniDbQuery.From=05.03.2021&UniDbQuery.To=12.03.2021 ")),
#"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Единиц", Int64.Type}, {"Курс", type number}})
in
#"Измененный тип"
Вспомним, что в ссылке у нас есть даты. Воспользуемся этим и заменим их на те, которые нам нужны. Для этого перед подключением к источнику данных две переменных - дату начала диапазона для выгрузки курсов валюты и дату окончания диапазона. Дату начала определим для начала так: start_date = #date(2020,1,1)
Далее преобразуем её в текст с помощью функции Date.ToText, чтобы впоследствии вставить в нашу ссылку: start_date = Date.ToText(#date(2020,1,1), "dd.MM.yyyy")
Дату окончания диапазона получим с помощью функции DateTime.LocalNow: end_date = DateTime.LocalNow()
В результате получим текущую дату и время. Нам нужна только дата, поэтому следующим шагом извлечём дату с помощью функции Date.From: end_date = Date.From(DateTime.LocalNow())
И напоследок аналогично дате старта - преобразуем в текст: end_date = Date.ToText(Date.From(DateTime.LocalNow()), "dd.MM.yyyy")
Внимательные читатели спросят - а зачем нам извлекать дату из даты и времени, если мы потом в текст берём всё равно только элементы даты. Казалось бы, можно вместо вот такого: end_date = Date.ToText(Date.From(DateTime.LocalNow()), "dd.MM.yyyy")
написать вот так: end_date = Date.ToText(DateTime.LocalNow(), "dd.MM.yyyy") Но - нет. Функция Date.ToText в качестве аргумента принимает формат date, но не datetime. Однако, справедливо будет отметить альтернативный вариант с использованием DateTime.ToText: end_date = DateTime.ToText(DateTime.LocalNow(), "dd.MM.yyyy")
Так или иначе, наша задача - получить две даты в текстовом формате "dd.MM.yyyy" с тем, чтобы после вставить их в ссылку для подключения к web-странице.
После получения дат преобразуем запрос к источнику так, чтобы можно было заменить часть текстовой ссылки на переменные. Для этого разобьём ссылку на части, заменим даты на переменные и соберём обратно с помощью Text.Combine:
Text.Combine({"https://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01010&UniDbQuery.From=", start_date, "&UniDbQuery.To=", end_date}) Заменим исходную ссылку на вот эту сборную. В конечном итоге получим код: