Загрузка курсов валют в Excel
Разбираем загрузку валютных курсов с сайта ЦБ РФ в MS Excel. Подобная статья есть и для Power BI (ссылка).
Этап 1: Сгенерим ссылку на сайте ЦБ РФ.
Зайдём на сайт по адресу: https://www.cbr.ru/
В меню найдём раздел — “Динамика официального курса заданной валюты” (“Документы и данные” — “Базы данных” — “Базы данных по курсам валют” — “Динамика официального курса заданной валюты”)
Выберем табличный вид, выберем валюту, период дат. Нажмём — “Получить данные”. Увидим табличку с курсами валюты за выбранный диапазон дат:
Скопируем ссылку из адресной строки браузера:
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
Обратим внимание, что в ссылке содержатся даты выбранного диапазона. Нам это пригодится, когда будем настраивать адаптируемый под нашу задачу диапазон дат.
Этап 2: Загрузим данные в Excel
В Excel перейдём на панели вкладку “Данные”, выберем получение данных с web-страницы (“Создать запрос” — “Из других источников” — “Из интернета”):
В появившемся окошке вставим полученную на предыдущем этапе ссылку на сайт ЦБ РФ:
В навигаторе увидим, что на странице распознано четыре табличных элемента. Нам нужен последний. Нажмём “Преобразовать данные”:
Попадаем в Power Query, где мы можем произвести дополнительную обработку данных перед загрузкой в модель данных Power BI. В правой плашке видим уже произведённые шаги — подключение к источнику данных, навигация по таблицам и изменение типов данных. Шаг с изменением типов данных удалим (если оставить, ничего страшного не случится, но он лишний):
Далее удалим первую строку с указанием на валюту:
И поднимем оставшуюся строку до уровня заголовков:
Получим вот такую вполне приличную табличку:
Этап 3: Сделаем диапазон дат подстраивающимся под реальность:
Отправимся в “Расширенный редактор” (“Advanced Editor”):
Там увидим вот такой код, который описывает на языке M все действия, которые мы произвели ранее:
Для желающих сократить путь, вот этот код:
Импорт курса валют из интернета
Импорт курса заданной валюты из интернета с автоматическим обновлением — весьма частая задача для многих пользователей Microsoft Excel. Представьте, что у вас есть прайс-лист, который должен пересчитываться каждое утро в соответствии с курсом. Или бюджет проекта. Или стоимость договора, которую надо посчитать, используя курс доллара на дату заключения договора.
В подобных ситуациях можно решить проблему по разному — всё зависит от того, какая версия Excel у вас установлена и какие надстройки поверх неё стоят.
Способ 1. Простой веб-запрос для текущего курса валют
Этот способ подойдет тем, у кого на компьютере пока ещё старые версии Microsoft Office 2003-2007. Он не использует никаких сторонних надстроек или макросов и оперирует только встроенными функциями.
Нажмите кнопку Из интернета (Web) на вкладке Данные (Data) . В появившемся окне в строку Адрес (Address) введите URL сайта, с которого будет браться информация (например http://www.finmarket.ru/currency/rates/) и нажмите клавишу Enter .
Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся черно-желтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта.
Когда все необходимые таблицы помечены — нажмите кнопку Импорт (Import) внизу окна. Спустя некоторое время, нужное для загрузки данных, содержимое отмеченных таблиц появится в ячейках на листе:
Для дополнительной настройки можно щелкнуть по любой из этих ячеек правой кнопкой мыши и выбрать в контекстном меню команду Свойства диапазона (Data range properties) . В этом диалоговом окне, при желании, возможно настроить периодичность обновления и другие параметры:
Котировки акций, т.к. они меняются каждые несколько минут, можно обновлять почаще (флажок Обновлять каждые N мин.), а вот курсы валют, в большинстве случаев, достаточно обновлять раз в день (флажок Обновление при открытии файла).
Обратите внимание, что весь импортированный диапазон данных воспринимается Excel как единое целое и получает собственное имя, которое можно увидеть в Диспетчере имен на вкладке Формулы (Formulas — Name Manager) .
Способ 2. Параметрический веб-запрос для получения курса валют на заданный интервал дат
Этот способ представляет собой слегка модернизированный первый вариант и дает пользователю возможность получать курс нужной валюты не только на текущий день, но и на любую другую интересующую дату или интервал дат. Для этого наш веб-запрос надо превратить в параметрический, т.е. добавить к нему два уточняющих параметра (код нужной нам валюты и текущую дату). Для этого делаем следующее:
1. Создаем веб-запрос (см. способ 1) к странице сайта Центробанка России с архивом курсов: http://cbr.ru/currency_base/dynamics.aspx
2. В форме слева выбираем нужную валюту и задаем начальную и конечную даты:
3. Жмем кнопку Получить данные и через пару секунд видим таблицу с нужными нам значениями курса на заданном интервале дат. Прокручиваем полученную таблицу вниз до упора и помечаем ее для импорта, щелкнув по черно-желтой стрелке в левом нижнем углу вебстраницы (только не спрашивайте почему эта стрелка находится там, а не рядом с таблицей — это вопрос к дизайнерам сайта).
Теперь ищем в правом верхнем углу окна кнопку с дискетой Сохранить запрос (Save Query) и сохраняем файл с параметрами нашего запроса в любую подходящую папку под любым удобным именем — например в Мои документы под именем cbr.iqy. После этого окно веб-запроса и весь Excel можно пока закрыть.
4. Открываем папку, куда сохранили запрос и ищем файл запроса cbr.iqy, затем щелкаем по нему правой кнопкой мыши — Открыть с помощью — Блокнот (или выбрать его из списка — обычно это файл Notepad.exe из папки C:\Windows). После открытия файла запроса в Блокноте должны увидеть примерно следующее:
Самое ценное здесь — строка с адресом и параметры запроса в ней, которые мы будем подставлять — код нужной нам валюты (выделено красным) и конечная дата, которую мы заменим на сегодняшнюю (выделено синим). Аккуратно редактируем строку, чтобы получилось следующее:
Все остальное оставляем как есть, сохраняем и закрываем файл.
5. Создаем новую книгу в Excel, открываем лист, куда хотим импортировать архив курсов ЦБ. В любую подходящую ячейку вводим формулу, которая даст нам текущую дату в текстовом формате для подстановки в запрос: