Лайфхак в Excel: как подгрузить курсы валют с сайта ЦБ


Большинство работников финансовой сферы воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных. Предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и создать функцию выбора курса валют на необходимую дату.



Немного больше 60 лет прошло с момента изобретения первого компьютера и около 50 лет с первых шагов по созданию интернета. Сейчас практически у каждого в кармане лежит мини-компьютер (смартфон) с доступом в сеть, уже начали появляться машины без водителя, некоторые и вовсе собираются колонизировать Марс. При этом большинство наших слушателей (работников финансовой сферы) до сих пор воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных.

Сегодня предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и обработать их (создать функцию выбора курса валют на необходимую дату).

 

Для чего может понадобиться эта возможность?

Если вам регулярно необходим курс Центрального Банка РФ, то функция «Загрузка курса с сайта ЦБ» позволит сэкономить много времени.

Разбиваем задачу на две части:

(1) Автоматизируем загрузку курсов валют за необходимый период с сайта Центрального Банка России

(2) Пишем небольшой скрипт, который создаст «Пользовательскую функцию» для выбора курса на дату и предоставит его вставку в ячейку

 

   

Хотите уметь делать так сами? Это не сложно...

  

Ознакомьтесь со следующими курсами по Excel от HOCK Training:



 

(1) Загрузка курсов

Для решения данной задачи воспользуемся стандартным малоизвестным функционалом Excel.

* оговорка о совместимости версий

⚠ Данный функционал приводится для версий MS Office до 2020 года выпуска. Отличие новых версий офиса состоит в том, что путь к странице с данными указывается строкой (копи-пастом из браузера), а не через встроенный браузер MS Excel. Остальные действия по разработке функции аналогичны приведённым в данной статье.

Подробный разбор алгоритма действий в более новых версиях офиса приведен в дистанционном курсе «Программирование в Excel».

   

(1.1) Линейка «Данные» --> «Получение внешних данных» --> «Из Интернета»:

 

Загрузка данных в Excel из Интернета

 

(1.2) В открывшемся внутреннем браузере Excel переходим на сайт ЦБ РФ и переходим на страницу с курсами:

 

Загрузка данных в Excel из Интернета

 

(1.3) Далее необходимо указать период для загрузки:

 

Загрузка данных в Excel из Интернета

 

(1.4) После получения данных нажмите кнопку «Импорт» и данные загрузятся в лист Excel 

 

ВАЖНО! 

Не забудьте перед этим поставить галочку в небольшом квадратике вверху страницы. На рисунке выше он зеленого цвета.

Загрузка данных в Excel из Интернета

При этом в Excel будут загружены данные:

 

Загрузка данных в Excel из Интернета

 

(1.5) Если все данные действия записать в виде макроса, то получим следующий скрипт в Visual Basic:

 

Макрос загрузки данных в Excel из Интернета

 

Обратите внимание на выделенные элементы кода. Если вносить в них изменения и запускать код на выполнение - будем получать курсы за нужный период. И не придется каждый раз повторять операции описанные выше в пунктах (1.1-1.4).

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

 

(2) Создание функции «Выбор курса на дату».

Используем возможности создания «Пользовательских функций» в редакторе Visual Basic:

 

(2.1) Создаем процедуру для обработки данных. Проверяем корректность работы программы на процедуре. Найденное значение курса записывается в переменную kurs (см листинг ниже).

 

Программа в Excel функции Выбор курса ЦБ на определенную дату

 

(2.2) Меняем процедуру на функцию:

 

Программа в Excel функции Выбор курса ЦБ на определенную дату

 

 

Скачать программу 

 

 

(2.3) Вставляем в ячейку на листе с данными нашу функцию (ищите её в категории «Определенные пользователем»):

 

Вставка в ячейку на листе с данными нашей пользовательской функции

   

Вставка в ячейку на листе с данными нашей пользовательской функции

  

(2.4) «Растягиваем» ячейку с формулой на необходимый нам диапазон стандартным образом:

 

Копирование ячейки с формулой в необходимый диапазон ячеек

 

Работает 😉

 


Подарок самым усидчивым за прочтение статьи про Excel


Только для дочитавших до конца статьи - наш подарок:

Бесплатный воркшоп «Как начать программировать в Excel, совершенно не зная языка Visual Basic»

95% слушателей отметили вебинар как "очень полезный"

Вам понадобится компьютер с Windows и MS Office


Получить воркшоп в подарок!



 
Если вы хотите разобраться в автоматизации MS Excel более подробно, обратите внимание на следующие полезные ресурсы:

Справочник по VBA для Excel
https://docs.microsoft.com/ru-ru/office/vba/api/overview/excel


Справочная система «Объектная модель (Excel)»
https://docs.microsoft.com/ru-ru/office/vba/api/overview/excel/object-model

Дистанционный курс HOCK Training «Программирование в Excel»
https://www.hocktraining.com/distancionny-excel-dlya-finansistov

Все блоги