Post-Image

Кто добыл, того и тапки

Рвав-рвав, собака Смайл снова творит!

После долгого перерыва (пандемия и вот это вот все), решил снова заняться написанием нового контента, поскольку обновлять старый трудолюбивой собаке слегка поднадоело. Появилась идея расписать процесс получения валютных курсов из Интернета в автоматическом режиме за любой период времени. Тема, конечно, далеко не нова, но, как говорится, “Своя рубашка ближе к телу…”, хоть смысл вложен был не тот :-)

Задача:

Необходимо получить официальный валютный курс доллара США к российскому рублю за произвольный промежуток времени.

Процесс разработки:

  • Перед началом разработки необходимо определиться с источником данных, и, поскольку источником официальной информации являются различные государственные органы, в качестве источника данных у нас будет выступать сайт Центрального банка Российской Федерации.

  • Далее нужно найти раздел с интересующей информацией, а поскольку нас интересует доллар США, курс которого устанавливается на ежедневной основе, можно смело перейти к разделу Официальные курсы валют на заданную дату, устанавливаемые ежедневно.

  • В данном разделе имеется список валют на текущую дату, но тут возникает следующая проблема: Power BI сам по себе не умеет хранить историю, и по указанной выше ссылке можно получить информацию только за одно число. Соответственно, для накопления данных нужно будет создать какое-то внешнее хранилище, куда будут складываться данные на ежедневной основе, или, например, агрегировать информацию вручную при помощи того же файла Excel.

    Рвав-рвав, нормальные герои всегда идут в обход, поэтому мы поступим по-другому: пошарив по окрестностям, собака Смайл обнаружил еще один раздел, а именно Динамика официального курса заданной валюты.

  • Здесь необходимо выбрать отображение данных, нужную валюту, а также желаемый период времени:

    art_013_screen_1

  • Затем нужно нажать кнопку “Получить данные” (по поводу кнопки “Экспорт” есть комментарий выше), и будет сформирована таблица следующего вида:

    art_013_screen_2

    Рвав-рвав, как вы думаете, в чем тут смысл? А смысл тут в том, что все это затевалось для получения некой параметрической ссылки, позволяющей получить курсы за определенный период времени.

  • Поэтому следующим шагом надо сохранить себе ссылку из адресной строки для дальнейшего использования при получении данных непосредственно в Power BI. Значение ссылки получилось следующим:

    https://www.cbr.ru/currency_base/dynamics/?"

    “UniDbQuery.Posted=True&”

    “UniDbQuery.mode=1&”

    “UniDbQuery.date_req1=&”

    “UniDbQuery.date_req2=&”

    “UniDbQuery.VAL_NM_RQ=R01235&”

    “UniDbQuery.From=01.12.2020&”

    “UniDbQuery.To=06.12.2020”

  • Дальше необходимо проверить, взлетит эта штука или нет, поэтому необходимо сделать тестовый запрос для получения данных с источником “Интернет”, используя при это полученную ссылку:

    art_013_screen_3

  • Нажимаем кнопку “ОК”, и ожидаем некоторое время, пока Power BI пытается подключиться к указанному источнику данных, при этом, если необходимо, используем анонимную авторизацию.

  • Если попытка подключения пройдет успешно, то появится окно навигатора с предварительным просмотром:

    art_013_screen_4

  • В части окна, относящейся к навигатору, необходимо выбрать нужную HTML-таблицу из доступного списка, поскольку, как правило, на интернет-странице таблица не одна. В нашем случае – это “Таблица 3”:

    art_013_screen_5

  • Нажимаем кнопку “ОК”, и получаем следующий результат:

    art_013_screen_6

  • Как видно, все вполне себе работает, и осталось только обработать полученные данные, добившись следующего вида:

    art_013_screen_7

  • Последним этапом обработки тестового запроса будет изменение его имени с “Таблица 3” на “Курс USD”.

  • Теперь на уровне Power Query необходимо сделать 2 параметра, при помощи которых можно будет задавать необходимый промежуток времени. Нюансы создания и работы параметров описаны в статье Параметры на уровне Power Query, нам же нужны даты начала и окончания:

    art_013_screen_8

    Рвав-рвав, поскольку встроенного календаря при вводе параметров Power Query не предусмотрено, а параметр, работающий с типом значений нам не подходит, то при создании параметров вполне можно оставить текстовый формат даты – это никак не повлияет на удобство конечного пользователя. Также можно ввести для параметров значения по умолчанию, в данном случае, период равен 2020-му (текущему) году.

  • Далее требуется получить таблицу, содержащую столбец с параметрической ссылкой, для этого сохраненную ранее ссылку нужно разбить на составные части, а затем –- собрать заново. На уровне Power Query необходимо создать таблицу (пусть имя будет, например, “Генератор”), содержащую в себе набор символов до 1-го параметра (начальной даты):

    art_013_screen_9

  • Следующим шагом в таблице “Генератор” надо создать настраиваемый столбец со ссылкой на параметр начала периода, и нажать кнопку “ОК” для сохранения результата:

    art_013_screen_10

  • Затем проделать аналогичные действия по добавлению необходимых частей генерируемой ссылки, добавив еще 2 столбца, в результате чего таблица “Генератор” примет вид, представленный ниже:

    art_013_screen_11

  • Заключительным этапом в формировании данной таблицы являет получение полной ссылки URL, при помощи сцепки всех столбцов, указанных выше:

    art_013_screen_12

    Рвав-рвав, сформированный столбец должен иметь текстовый тип данных, но, учитывая легкую склонность к перфекционизму, надо присвоить указанный тип данных всем столбцам таблицы “Генератор”.

  • Поскольку все необходимые составляющие для воплощения задачи, а именно основной запрос, параметры и параметрическая ссылка, подготовлены, осталось только подставить указанную ссылку вместо статической, при помощи расширенного редактора. Для этого нам нужно переписать строку “Источник” следующим образом:

    Источник = Web.BrowserContents(Генератор{0}[URL])

  • Как только изменения, внесенные в код запроса, вступят в силу (при нажатии кнопки “ОК”), таблица “Курс USD” будет дополнена данными в соответствии с промежутком, обозначенным параметрами:

    art_013_screen_13

    Рвав-рвав, дальше все зависит только от вашей фантазии. Собака Смайл добыл валютные курсы, и с чувством выполненного долга уходит на прогулку.

    Ваш Смайл