Импорт курсов валюты в Excel
Описание работы
Один из самых эффективных способов вставить курс валюты с сайта ЦБ в ячейку Excel — использовать надстройку с макросом. После установки программы VBA-Excel у вас появится новая вкладка на ленте с командой вызова функции Курс валюты.
Эта команда открывает удобную форму для импорта курса на заданную дату.
Выбор даты импорта
На форме имеется встроенный календарь для выбора даты на которую импортируется курс валюты. По умолчанию выбрана текущая дата.
Установить текущую дату можно также нажав на кнопку Сегодня в календаре
Выбор валюты
Далее необходимо указать курс какой валюты необходимо импортировать. Для этого кликните по нужной строке в списке.
Для выбора доступны все валюты с сайта ЦБ РФ. Для удобства самые популярные курсы доллара и евро вынесены в начало списка. Остальные отсортированы по алфавиту.
Выбор единицы расчета
Обратите внимание, что не все курсы валют берутся из расчета за 1 единицу. Например, для Армянских драммов расчет ведется за 100 единиц. Если необходимо, чтобы курс вставлялся за 1 единицу, то установите опцию В расчете за 1 единицу валюты в нижнем левом углу.
Тип вставки
Если вы не планируете обновлять курс валюты в дальнейшем, то советую вставлять курс валюты "текстом" тогда Excel не будет обращаться к сайту ЦБ для обновления данных. Для этого нажмите кнопку Вставка текстом.
Если вы хотите периодически обновлять курс (например на текущую дату), то используйте кнопку Вставить формулой. В этом случае в выбранную ячейку вставится функция КУРС с установленными параметрами.
Использовать функцию КУРС
Вставить курс валюты Вызвать функцию можно с помощью функции. Использовать ее можно так же как и любую другую встроенную в Excel — просто введите в ячейку =КУРС([Дата]; [ВАЛЮТА]; [ За1ед ]).
- [Дата] — Дата, на которую необходимо определить валюту. По умолчанию текущая дата.
- [ВАЛЮТА] — Текст, определяющий код валюты в соответствии с сайтом cbr.ru. По умолчанию USD.
- [ За1ед ] — Не все курсы валют на сайте Центробанка указываются за 1 единицу валюты. Чтобы валюта рассчитывалась из расчета за 1 единицу укажите значение этой переменной = 1
Само собой необходим интернет для пользования данной функцией. Прошу не мучить свой компьютер, интернет и не делать выгрузку курсов за большой период времени, для этого на сайте Центробанка есть специальный функционал.
Пример 1
Получение курса USD на сегодня.
Пример 2
Получение различных курсов валют на сегодня.
Пример 3
Получение динамики курса доллара за 5 дней.
Пример 4
Получение курсов валюты из расчета за 1 единицу валюты на текущую дату.
Как в excel сделать привязку к курсу?
В старых версиях Excel подобная задача требовала неслабых ухищрений и для многих сайтов была вообще нереализуема. Начиная с версии Excel 2003 все сводится к нескольким щелчкам мышью.
Способ 1. Простой веб-запрос для текущего курса валют
Выберите в меню Данные — Импорт внешних данных — Создать веб-запрос (Data — Import external data — Web-query) или в Excel 2007/2010 нажмите кнопку Из веба (Web) на вкладке Данные (Data).
В появившемся окне в строку Адрес (Address) введите URL сайта, с которого будет браться информация (например и нажмите Enter.
Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся черно-желтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта.
Когда все необходимые таблицы помечены — нажмите кнопку Импорт (Import) внизу окна. Спустя некоторое время, нужное для загрузки данных, содержимое импортируемых таблиц появится в ячейках на листе:
Для дополнительной настройки можно щелкнуть по любой из этих ячеек правой кнопкой мыши и выбрать в контекстном меню команду Свойства диапазона (Data range properties). В этом диалоговом окне, при желании, возможно настроить периодичность обновления и другие параметры:
Котировки акций, т.к. они меняются каждые несколько минут, можно обновлять почаще (флажок Обновлять каждые N мин.), а вот курсы валют, в большинстве случаев, достаточно обновлять раз в день (флажок Обновление при открытии файла).
Обратите внимание, что весь импортированный диапазон данных воспринимается Excel как единое целое и получает собственное имя (в нашем примере это будет www.cbr.ru)
Способ 2. Параметрический веб-запрос для получения курса валют на заданный интервал дат
Этот способ представляет собой слегка модернизированный первый вариант и дает пользователю возможность получать курс нужной валюты не только на текущий день, но и на любую другую интересующую дату или интервал дат. Для этого наш веб-запрос надо превратить в параметрический, т.е. добавить к нему два уточняющих параметра (код нужной нам валюты и текущую дату). Для этого делаем следующее:
1. Создаем веб-запрос (см. способ 1) к странице сайта Центробанка России с архивом курсов:
2. В форме слева выбираем нужную валюту и задаем начальную и конечную даты:
3. Жмем кнопку Получить данные и через пару секунд видим таблицу с нужными нам значениями курса на заданном интервале дат. Прокручиваем полученную таблицу вниз до упора и помечаем ее для импорта, щелкнув по черно-желтой стрелке в левом нижнем углу вебстраницы (только не спрашивайте почему эта стрелка находится там, а не рядом с таблицей — это вопрос к дизайнерам сайта).
Теперь ищем в правом верхнем углу окна кнопку с дискетой Сохранить запрос (Save Query) и сохраняем файл с параметрами нашего запроса в любую подходящую папку под любым удобным именем — например в Мои документы под именем cbr.iqy. После этого окно веб-запроса и весь Excel можно пока закрыть.
4. Открываем папку, куда сохранили запрос и ищем файл запроса cbr.iqy, затем щелкаем по нему правой кнопкой мыши — Открыть с помощью — Блокнот (или выбрать его из списка — обычно это файл Notepad.exe из папки C:Windows). После открытия файла запроса в Блокноте должны увидеть примерно следующее:
Самое ценное здесь — строка с адресом и параметры запроса в ней, которые мы будем подставлять — код нужной нам валюты (выделено красным) и конечная дата, которую мы заменим на сегодняшнюю (выделено синим). Аккуратно редактируем строку, чтобы получилось следующее:
Все остальное оставляем как есть, сохраняем и закрываем файл.
5. Создаем новую книгу в Excel, открываем лист, куда хотим импортировать архив курсов ЦБ. В любую подходящую ячейку вводим формулу, которая даст нам текущую дату в текстовом формате для подстановки в запрос:
или в английской версии
Куда-нибудь рядом вводим код нужной нам валюты из таблицы: