Импорт курсов валюты в Excel
Один из самых эффективных способов вставить курс валюты с сайта ЦБ в ячейку Excel — использовать надстройку с макросом. После установки программы VBA-Excel у вас появится новая вкладка на ленте с командой вызова функции Курс валюты.
Эта команда открывает удобную форму для импорта курса на заданную дату.
Выбор даты импорта
На форме имеется встроенный календарь для выбора даты на которую импортируется курс валюты. По умолчанию выбрана текущая дата.
Установить текущую дату можно также нажав на кнопку Сегодня в календаре
Выбор валюты
Далее необходимо указать курс какой валюты необходимо импортировать. Для этого кликните по нужной строке в списке.
Для выбора доступны все валюты с сайта ЦБ РФ. Для удобства самые популярные курсы доллара и евро вынесены в начало списка. Остальные отсортированы по алфавиту.
Выбор единицы расчета
Обратите внимание, что не все курсы валют берутся из расчета за 1 единицу. Например, для Армянских драммов расчет ведется за 100 единиц. Если необходимо, чтобы курс вставлялся за 1 единицу, то установите опцию В расчете за 1 единицу валюты в нижнем левом углу.
Тип вставки
Если вы не планируете обновлять курс валюты в дальнейшем, то советую вставлять курс валюты "текстом" тогда Excel не будет обращаться к сайту ЦБ для обновления данных. Для этого нажмите кнопку Вставка текстом.
Если вы хотите периодически обновлять курс (например на текущую дату), то используйте кнопку Вставить формулой. В этом случае в выбранную ячейку вставится функция КУРС с установленными параметрами.
Использовать функцию КУРС
Вставить курс валюты Вызвать функцию можно с помощью функции. Использовать ее можно так же как и любую другую встроенную в Excel — просто введите в ячейку =КУРС([Дата]; [ВАЛЮТА]; [ За1ед ]).
- [Дата] — Дата, на которую необходимо определить валюту. По умолчанию текущая дата.
- [ВАЛЮТА] — Текст, определяющий код валюты в соответствии с сайтом cbr.ru. По умолчанию USD.
- [ За1ед ] — Не все курсы валют на сайте Центробанка указываются за 1 единицу валюты. Чтобы валюта рассчитывалась из расчета за 1 единицу укажите значение этой переменной = 1
Само собой необходим интернет для пользования данной функцией. Прошу не мучить свой компьютер, интернет и не делать выгрузку курсов за большой период времени, для этого на сайте Центробанка есть специальный функционал.
EXCEL: Загружаем курсы валют с сайта ЦБ
В современных версиях EXCEL загрузку курсов валют через API лучше делать через встроенную функцию ВЕБСЛУЖБА (WEBSERVICE) или через макросы (VBA) . Примеры такой загрузки описаны в статье:
Описываемый ниже способ загрузки курсов валют через Power Query и WebQuery подходит только для старых версий EXCEL (до 2016).
Продолжаем тему загрузки исторических данных из различных бесплатных баз. В прошлый раз мы говорили о простейшем способе подключения к Yahoo Finance: Загружаем исторические данные из Yahoo Fianance.
На этот раз расскажем о том, как загрузить котировки валют с сайта Центрального банка России, а заодно познакомился в EXCEL с двумя полезными инструментами Web Query и Power Query.
Сразу надо сказать, что возможности сайта Центробанка закрывают потребности инвестора (и спекулянта тоже) на 90%. Почему на 90%? На сайте ЦБ нет исторических данных по нерублёвым валютным парам, например, EUR/USD.
Готовый пример таблицы EXCEL, поддерживающей автоматическое скачивание курсов валют, можно загрузить по ссылке в конце статьи.
Последовательность шагов
Все скриншоты сделаны в EXCEL 2016.
Шаг 1
Итак, зайдя на сайт ww.cbr.ru мы сразу видим ссылки на курсы валют.
Шаг 2
Мы попадаем на страницу баз данных валют. Далее нам в раздел, посвященный динамике курса заданной валюты.
Шаг 3
Задаем параметры поиска: тип валюты, даты и тип выдачи данных (таблица):
Шаг 4
В результатах нас интересует только адресная строка, копируем её в буфер (CTRL-C):
Шаг 5
Открываем новый документ в EXCEL. Переходим на закладку Данные. Далее – Получение внешних данных / Из интернета:
Шаг 6
Загружается встроенный браузер. Копируем в адресную строку ссылку, которую мы получили ранее на шаге 4 (Ctr-V), и снова видим таблицу с историей курсов валюты. Можно весь путь к таблице сделать и без использования внешнего браузера, но встроенный в EXCEL браузер работает на базе IE, что не очень удобно.
Единственное, что нам нужно здесь сделать, это нажать на кнопку «Сохранить запрос»:
Шаг 7
Параметры WEB запроса (поэтому он называется WEB Query) сохраняем в файл с расширением .iqy
Шаг 8
Открываем записанный файл в каком-либо текстовом редакторе, например, в Notepad. В строке запроса находим даты и код валюты (R01235 для доллара США):
Шаг 9
Меняем эти значения на переменные и сохраняем изменения. Название переменных произвольное. Синтаксис переменной – [“Name”]:
Шаг 10
Возвращаемся в EXCEL и готовим значения переменных, которые будут считываться при обращении к сайту ЦБ. Значение каждой переменной нужно разместить в отдельной ячейке на листе. Популярные коды валют: Доллар США — R01235, Евро — R01239, Юань — R01375. Код любой другой валюты можно узнать, сформировав новый запрос на сайте ЦБ.
Формат даты необходимо поменять на текстовый, т.к. по умолчанию EXCEL дата представляет собой пятизначное число. Делается это при помощи формулы =ТЕКСТ(B3;»ДД.ММ.ГГГГ»). В дальнейшем колонку «Переменные» можно скрыть.
Шаг 11
Снова обращаемся к закладке «Данные», раздел «Существующие подключения». Выбираем «Найти другие …» и загружаем записанный файл в формате .iqy
Шаг 12
Выбираем отображение данных на новом листе.
Шаг 13
Указываем ячейки, в которых находятся значения переменных. Выбираем «Использовать данное значение по умолчанию» и «Автоматически обновлять при изменении значения ячейки», если есть желание подгружать данные в автоматическом режиме:
Шаг 14
На новом листе формируется таблица с котировками валюты. В принципе эти данные уже можно использовать, но на листе кроме таблицы присутствует много лишнего «мусора».
Шаг 15
Для очистки данных нам поможет Power Query. В EXCEL 2016 раздел Power Query (Скачать и преобразовать) находится по соседству с «Получением данных» (вкладка данные). Нас интересует создание нового запроса из таблицы. При нажатии на «Из таблицы» должна быть активной страница с полученными данными.
Шаг 16
Открывается диалог интерфейса Power Query. Для начала удаляем лишнюю колону. Для этого надо ее выделить и нажать «Удалить столбцы».
Шаг 17
Далее убираем ненужную информацию, расположенную в верхних строках: «Удаление верхних строк». И указываем номер последней строки с мусором (26).
Шаг 18
Теперь можно использовать верхнюю строку в качестве заголовка таблицы:
Шаг 19
Выбираем колонку Дата и сортируем ее по убыванию (от новых дат к старым):
Шаг 20
После сортировки становится понятно, что внизу таблицы тоже был «мусор». Чистим его по аналогии с шагом 17. Таблица начинает приобретать рабочий вариант.
Остается только выделить колонку Дата и указать для нее правильный формат:
Шаг 21
Последний шаг – указать где должны отображаться очищенные данные:
Таблица с новыми данными в удобном формате будет показана на новом листе. Теперь с этой информацией можно работать по своему усмотрению.
Еще одним удобством использования Power Query является обратимость всех действий по преобразованию запроса. В любой момент можно вернуться к редактору запроса (Показать запросы). В правой части интерфейса расположена история редактирования запроса, куда всегда можно внести изменения и удалить действия.
Для обновления данных в итоговой таблице нужно нажать «Обновить все» в закладке Данные:
ВНИМАНИЕ
Автоматическая загрузка курса выбранной валюты теперь доступна через встроенные функции EXCEL (ВЕБСЛУЖБА). Подробнее о загрузки курса валют через API Центрального банка с помощью функции EXCEL и VBA: EXCEL: Загружаем курсы валют с сайта ЦБ через функцию ВЕБСЛУЖБА или VBA.
Файлы для скачивания
Файл: currency_cbr.zip
Размер: 161928 байт
Для скачивания файлов необходимо зарегистрироваться или авторизоваться
Лайфхак в Excel: как подгрузить курсы валют с сайта ЦБ
Большинство работников финансовой сферы воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных. Предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и создать функцию выбора курса валют на необходимую дату.
Немного больше 60 лет прошло с момента изобретения первого компьютера и около 50 лет с первых шагов по созданию интернета. Сейчас практически у каждого в кармане лежит мини-компьютер (смартфон) с доступом в сеть, уже начали появляться машины без водителя, некоторые и вовсе собираются колонизировать Марс. При этом большинство наших слушателей (работников финансовой сферы) до сих пор воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных.
Сегодня предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и обработать их (создать функцию выбора курса валют на необходимую дату).
Для чего может понадобиться эта возможность?
Если вам регулярно необходим курс Центрального Банка РФ, то функция «Загрузка курса с сайта ЦБ» позволит сэкономить много времени.
Разбиваем задачу на две части:
(1) Автоматизируем загрузку курсов валют за необходимый период с сайта Центрального Банка России
(2) Пишем небольшой скрипт, который создаст «Пользовательскую функцию» для выбора курса на дату и предоставит его вставку в ячейку
Как в эксель привязать курс валют онлайн
С помощью типа данных Валюты можно легко получить и сравнить обменные курсы по всему миру. В этой статье вы узнаете, как вводить пары валют, преобразовывать их в тип данных и извлекать дополнительные сведения для надежных данных.
Примечание: Валютные пары доступны только для Microsoft 365 (клиенты с несколькими клиентами по всему миру).
Использование типа данных «Валюты» для расчета обменных курсов
Введите валютную пару в ячейку, используя такой формат: Из валюты / В валюту с кодами валют ISO.
Например, введите «ДОЛЛАР/ЕВРО», чтобы получить курс обмена от одного доллара США к евро.
Выберем ячейки и выберите Вставить >таблицу. Создание таблицы не обязательно, но значительно упростит вставку данных из типа данных.
Выбрав ячейки, перейдите на вкладку Данные и выберите тип данных Валюты.
Если Excel обнаружит совпадение между валютной парой и поставщиком данных, текст преобразуется в тип данных, а в ячейке 
значок Валюты.
Примечание: Если в ячейке 
вместо значка Валюты, Excel затруднение при совпадении текста с данными. Исправь все ошибки и нажмите ввод, чтобы повторить попытку. Можно также выбрать значок, чтобы открыть селектор данных, в котором можно найти валютную пару или указать нужные данные.
Чтобы извлечь дополнительные сведения из типа данных Валюты, выберите одну или несколько преобразованных ячеек и нажмите кнопку Вставить данные 
отобразить или нажать клавиши CTRL/CMD+SHIFT+F5.
Вы увидите список всех доступных полей. Выберите поля, чтобы добавить новый столбец данных.
Например, «Цена» представляет обменный курс для валютной пары, а время последней сделки — время, в течение которого курс был цитироваться.
После того как у вас есть все необходимые данные о валюте, их можно использовать в формулахи вычислениях. Чтобы убедиться, что данные обновлены, перейдите в> Обновить все, чтобы получить обновленную кавычка.
Внимание: Сведения о валюте предоставляются «как есть» и могут быть задержаны. Поэтому эти данные не следует использовать в целях торговли или советов. Дополнительные сведения см. всведениях об источниках данных.