Как вставить текущий курс обмена в Excel?

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

Один из наиболее эффективных способов вставить обменный курс с сайта Центрального банка в ячейку Excel — использовать макрос-надстройку. После установки VBA-Excel у вас появится новая вкладка в ленте с командой вызова функции Exchange Rate.

Вы можете использовать эту команду для импорта ставок на заданную дату с помощью удобной формы.

Выбор даты импорта

В форму встроен календарь для выбора времени импорта курса валюты. По умолчанию выбирается текущая дата.

Вы также можете установить текущую дату, нажав на кнопку Today на календаре

Выбор валюты

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

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

Выбор единицы расчета

Обратите внимание, что не все обменные курсы берутся за единицу. Например, для армянских драм расчет ведется на 100 единиц. Если вы хотите, чтобы курс обмена вставлялся за единицу, установите флажок Включить расчет за 1 валютную единицу в левом нижнем углу.

Тип вставки

Если вы не планируете обновлять курс валюты в дальнейшем, то советую вставлять курс валюты "текстом" тогда Excel не будет обращаться к сайту ЦБ для обновления данных. Для этого нажмите кнопку Вставка текстом.

Если вы хотите периодически обновлять ставку (например, на текущую дату), используйте кнопку Вставить формулу. В этом случае функция ставки будет вставлена в выбранную ячейку с заданными параметрами.

Последовательность шагов

Все снимки экрана сделаны в Excel 2016.

Шаг 1

Поэтому, когда мы заходим на ww.cbr.ru, мы сразу же видим ссылки на курсы валют.

Шаг 2

Попадаем на страницу баз данных валют. Затем переходим к разделу, посвященному динамике данной валюты.

Шаг 3

Введите параметры поиска: тип валюты, даты и вид таблицы:

Шаг 4

В результатах нас интересует только адресная строка, копируем ее в буфер обмена (CTRL-С):

Шаг 5

Открываем новый документ в EXCEL. Переходим на закладку Данные. Далее – Получение внешних данных / Из интернета:

Шаг 6

Загрузился встроенный браузер. В шаге 4 (мы нажали Control-V) мы скопировали ссылку, которая у нас была с предыдущего шага, и снова увидели таблицу с историей курсов валют. Можно перейти от начала работы к таблице без использования браузера, но браузер EXCEL основан на IE, что не очень удобно.

Единственное, что нам нужно здесь сделать, это нажать на кнопку «Сохранить запрос»:

Шаг 7

Параметры WEB-запроса (поэтому он и называется WEB-запросом) сохраняются в файле с расширением .iqy.

Шаг 8

Откройте записанный файл в текстовом редакторе, например, в Блокноте. Найдите даты и код валюты (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 является его обратимость. Вы можете в любой момент вернуться в редактор запросов (Show Queries). В правой части интерфейса находится история редактирования запросов, где вы всегда можете внести изменения и удалить действия.

Для обновления данных в итоговой таблице нужно нажать «Обновить все» в закладке Данные:

С помощью встроенных функций EXCEL (без истории курсов) теперь можно автоматически загружать выбранный курс валют. Ниже приведены некоторые советы по загрузке курсов валют с помощью EXCEL:

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Excel вообще продвинулся очень хорошо в последние несколько лет. Мои любимые спарклайны, работа с данными — это вообще шедевр. Но наш любимый Эксель стал напоминать Фотошоп, без инструкции уже никуда. Поэтому продолжу. Сейчас получим пару EUR-USD из интернета. А в следующем посте покажу как рассылать автоматические обновления.

Вы можете искать исходные курсы валют или другую информацию в поисковой системе, набрав что-то вроде xml eur usd или Free XML Currency Exchange Rate (Бесплатный XML-курс обмена валют).

Запустите наш Excel (у меня версия 2019 года, мне не нравится версия 365).

Данные -> Из интернета -> Вставляем адрес нашей найденной таблицы в поле URL. Например, http://www.floatrates.com/daily/eur.json

Что касается другой основной валюты, то евро заменяется другой валютой. Например, с долларом США.

Фотографии можно кликать.

Мы находимся в Power Query и видим таблицу значений. Я рекомендую вам прочитать руководство по Power Query. На этой же странице представлено множество шаблонов на все случаи жизни.

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

Я удалю лишние строчки.

Сортировка в соответствии с выбранной ячейкой. Да, это влияет на сортировку.

Все сработало как надо: валюта, дата, обменный курс, обратный курс.

Но это еще не все. Вам необходимо настроить автоматизацию.

Здесь вы можете выбрать любую конфигурацию для приема данных.

Вот как все закончилось.

Данные можно копировать из ячеек в любую электронную таблицу или документ. Да, я чуть не забыл, что в Word это работает точно так же. Это очень удобно!

Итак, мы получили из интернета пару EUR-USD в наш документ Excel.