Импорт курса валют в excel

Импорт курсов валюты в 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 единицу валюты на текущую дату.

Импорт курса валют из интернета

Импорт курса заданной валюты из интернета с автоматическим обновлением — весьма частая задача для многих пользователей Microsoft Excel. Представьте, что у вас есть прайс-лист, который должен пересчитываться каждое утро в соответствии с курсом. Или бюджет проекта. Или стоимость договора, которую надо посчитать, используя курс доллара на дату заключения договора.

В подобных ситуациях можно решить проблему по разному — всё зависит от того, какая версия Excel у вас установлена и какие надстройки поверх неё стоят.

Способ 1. Простой веб-запрос для текущего курса валют

Этот способ подойдет тем, у кого на компьютере пока ещё старые версии Microsoft Office 2003-2007. Он не использует никаких сторонних надстроек или макросов и оперирует только встроенными функциями.

Нажмите кнопку Из интернета (Web) на вкладке Данные (Data) . В появившемся окне в строку Адрес (Address) введите URL сайта, с которого будет браться информация (например http://www.finmarket.ru/currency/rates/) и нажмите клавишу Enter .

Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся черно-желтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта.

Читать еще: Почему столбцы в 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, открываем лист, куда хотим импортировать архив курсов ЦБ. В любую подходящую ячейку вводим формулу, которая даст нам текущую дату в текстовом формате для подстановки в запрос:

или в английской версии

Куда-нибудь рядом вводим код нужной нам валюты из таблицы:

Канал в Telegram

Вы здесь

Пишем функцию Excel для получения курса валют на указанную дату

В этом уроке мы напишем на языке VBA пользовательскую функцию, которая по заданным параметрам будет получать с сайта Центр-Банка РФ актуальные курсы валют, установленные на определенную дату.

О том, что такое пользовательские функции в Excel, Вы можете почитать в этом уроке. «Тело» функции будет реализовано на встроенном языке VBA(языке макросов). Если Вы не знаете, что такое «макросы», то можете почитать здесь, а также, как они создаются – здесь.

Читать еще: Легенда графика в excel

Возвращаемся к теме урока.

Если Ваша сфера деятельности тесно связана с курсом валют, или у Вас имеются инструменты(отчеты) в Excel, в которых используется конвертация рублей в некоторую валюту… Да или Вы просто любите «поиграть на курсах валют», Вам нужен инструмент, который будет автоматически актуализировать курс заданной валюты.

Как нам узнать текущий курс валюты?

В этом нам поможет официальный сайт Центрального банка Российской Федерации( www.cbr.ru ). На данном сайте можно посмотреть установленные курсы Валют на определенную дату. Конечно, обновление данных происходит не в режиме онлайн, а раз в сутки, за исключением выходных и праздничных, но такая периодичность для многих задач сгодится!

Итак, чтобы увидеть таблицу курсов, нам необходимо перейти на страницу: https://www.cbr.ru/currency_base/daily.aspx?date_req=08.03.2018 где мы увидим следующую таблицу:

Теперь заглянем на «внутренности» отображенной страницы т.е. на ее HTML-код:

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

Буквенный код – этот код будет использоваться как входной параметр для поиска курса нужной валюты;
Единиц – этот параметр потребуется для расчета стоимости одной единицы т.к. некоторые валюты продаются по сто единиц;
Курс – ну здесь собственно в рублях.

Как же нам получить из всего этого набора тегов, скриптов, ссылок и т.д. нужную информацию?!

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

Строим алгоритм!

Смотрим код, и видим что область нужных нам данных начинается с тега table с классом “data”, а за ним сразу следует тег

Отлично! Конец уже легко найти, это первый встретившийся тег

после стартовой позиции т.е. конец тела таблицы. Выбираем эти данные из общей массы.

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

Например, мы ищем курс конечно же по «Доллару США»… Буквенный код валюты «USD». Находим из выбранного ранее, первое вхождение подстроки “USD”, это и будет стартом для извлечения информации, а остановкой будет простой тег

т.е. конец строки таблицы. Таким образом мы сразу получаем кол-во единиц и курс (результат выглядит вот так: “ USD1Доллар США56,8011

”).

Ну собственно осталось отчистить от лишних тегов. В этом нам поможет функция “Replace”

Функция позволяет заменить в тексте определенный набор символов, новым набором или пустотой.

= “”(пусто); = “;”(разделитель);
= “”(пусто);

В результате получим строку «USD;1;Доллар США;56,8011». Все вполне пригодная строка данных.

Остается ее преобразовать в массив строк разделив на элементы с помощью функции «Split» и уже взять отдельные элементы массива для расчета и вернуть в функцию. Конец алгоритма!

Теперь это все реализуем в коде.

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

Файл прикреплен в конце урока.

Переходим к кодингу:

Создаем новую книгу;
Открывем редактор VBA (Alt+F11);
Добавляем модуль к проекту с любым именем.

В этот модуль пишем следующий код:

Ну и теперь осталось только вызвать функцию, а вызывается она как обычная встроенная функция Excel. Находится наша функция в категории «Определенные пользователем» и зовется «ПОЛУЧИТЬКУРСРУБ».

Более подробно смотрите в приложенном файл-примере

Автоматизация импорта котировок валютных курсов в Excel 2007

Написано admin в Ноябрь 7, 2011. Опубликовано в Рынок ценных бумаг

Рассмотрим автоматическое получение (импорт) котировок валютных пар с сайта российского центрального банка в Excel, это будет достигаться за счет написания параметрического запроса в программной среде. Алгоритм по автоматизацию импорта котировок следующий:

Открываем рабочий лист, куда мы хотим импортировать котировки.
Переходим в раздел «Данные», который находится на верхней панели инструментов, в нем выберем раздел получить внешние данные и категорию «Из Веба». На рисунке показана эта процедура:
После выбора источника данных, выйдет окно-браузер. В его адресной строке пишем адрес странички сайта центрального банка со статистикой динамики валютных пар (http://cbr.ru/currency_base/dynamics.aspx).
Дальше необходимо создать запрос на получение котировок валютной пары, например по «Австралийскому доллару». Это значит, что мы получим данные по валютной паре австралийский доллар – российский рубль. Для этого установим временной период и поставим галку на опции «таблица», после нажмем на кнопку «Получить». В правой части получим дневные котировки по валютной паре австралийский доллар – рубль.
Желтые квадраты с горизонтальными стрелочками показывают места, откуда может быть импортирована информация в Excel. Нам необходимо нажать на стрелку у таблицы котировок.
Далее нажимаем на кнопку – сохранить запрос, которая находится рядом с опцией «Параметры…»
В появившемся диалоговом окне выбираем место на диске, где сохраним созданный нами запрос.
Открываем сохраненный запрос в текстовом документе.
В третьей строке запроса находится непосредственно сам запрос на сайт центрального банка. Переменная VAL_NM_RQ означает код валюты, в нашем случае это австралийский доллар (R01010 –это непосредственно код определенной валюты), а date_req2 означает до какой даты импортировать котировки, а переменная date_req1 означает дату, с которой импортируются котировки.
http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01010&date_req1=01.10.2000&r1=1&date_req2=07.10.2010&C_month=10&C_year=2000&rt=1&mode=1&x=29&y=10
Заменяем в этом запросе переменные кода валюты и даты.http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=[“Код_валюты“]&date_req1=[“Начальная_дата“]&r1=1&date_req2=[“Конечная_дата“]&C_month=10&C_year=2000&rt=1&mode=1&x=29&y=10
Для работы с запросом по импорту котировок в разделе «Данные » выбираем подраздел «Существующие подключения».
Далее в окне «Существующие подключения», выбираем кнопку «Найти другие». И в появившемся диалоговом окне выбираем созданный нами запрос query_cbr.iqy
Далее программой будет предложено место исполнения запроса. Помимо этого отредактируем свойства исполнения запроса.
В окне «Импорт данных» перейдем к опции «Свойства…». Поставим галки в полях «обновление при открытии файла» и зададим временной диапазон.
После будут выходить информационные окна, где необходимо ввести параметры запроса: код валюты, начальную дату и конечную дату. Мы можем не только самим вводить код валюты, а можем ссылаться на ячейки с уже занесенным кодом. Аналогичным образом заполняются поля с «Конечной датой» и «Начальной датой». Поставив галки на опциях «Использовать данное значение по умолчанию» и «Автоматически обновлять при изменении значения ячейки» позволят использовать данные выбранных ячеек и обновлять при их изменении.
В итоге будут импортированы данные по указанной валюте с указанным диапазоном дат.
На следующем этапе создадим функцию, отображающую текущую дату. Для этого рядом, на этом рабочем листе, пропишем функцию, возвращающую текущую дату и переводящую формат даты в числовой, который необходим для запроса.
Текущая дата рассчитывалась по вышеуказанной формуле. Ячейка для начальной даты указывается непосредственно трейдером или инвестором. Так же написаны различные коды используемых валют.
При открытии файла данные по курсу выбранной валюты к рублю будут обновлены на текущую дату и готовы к более детальному анализу и использованию.