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

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

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

Способ 1. Простой веб-запрос текущего обменного курса.

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

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

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

Когда все необходимые таблицы отмечены, нажмите кнопку Импортировать (Импорт) внизу окна. Через некоторое время, необходимое для загрузки данных, содержимое отмеченных таблиц появится в ячейках листа:

Для дополнительной настройки вы можете щелкнуть правой кнопкой мыши по любой из этих ячеек и выбрать команду из контекстного меню. Свойства диапазона (Свойства диапазона данных).В этом диалоговом окне при желании можно настроить частоту обновления и другие параметры:

Котировки акций, так как они меняются каждые несколько минут, вы можете обновлять чаще (галочка Обновляйте каждые N минут.), но курсы валют в большинстве случаев достаточно обновлять раз в сутки (галочка Обновление при открытии файла).

Обратите внимание, что весь импортированный диапазон данных обрабатывается Excel как единое целое и получает собственное имя, которое можно увидеть в Диспетчере имен на вкладке формула (Формулы — Менеджер имен).

Метод 2. Параметрический веб-запрос для получения обменного курса для заданного диапазона дат.

Этот метод является слегка модернизированным первым вариантом и дает пользователю возможность получить курс обмена нужной валюты не только на текущий день, но и на любую другую интересующую дату или интервал дат. Для этого наш веб-запрос необходимо превратить в параметрический, т.е. добавить к нему два уточняющих параметра (код нужной нам валюты и текущую дату). Для этого делаем следующее:

1. Создаем веб-запрос (см. способ 1) на страницу сайта ЦБ Нашей Страны с архивом курсов: http://cbr.ru/currency_base/dynamics.aspx

2. В форме слева выберите нужную валюту и установите даты начала и окончания:

3. Нажмите кнопку Чтобы получить данные и через пару секунд мы видим таблицу с нужными нам значениями курса для заданного интервала дат. Прокрутите получившуюся таблицу до конца вниз и отметьте ее для импорта, нажав на черно-желтую стрелку в левом нижнем углу веб-страницы (только не спрашивайте, почему эта стрелка там, а не рядом с таблицей – это вопрос к дизайнерам сайта).

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

4. Откройте папку, в которой вы сохранили запрос, и найдите файл запроса. cbr. ики, затем щелкните по нему правой кнопкой мыши – Открыть с помощью — Блокнот (или выберите его из списка – обычно это файл Notepad.exe из папки С: Windows). Открыв файл запроса в Блокноте, вы должны увидеть что-то вроде этого:

Самое ценное здесь — это строка с адресом и параметрами запроса в ней, которые мы подставим — код нужной нам валюты (выделено красным) и конечная дата, которую мы заменим на сегодняшнюю (выделена красным). синий). Внимательно отредактируйте строку, чтобы получить следующее:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=["Код валюты"]&date_req1=01.01.2000&r1=1&date_req2=["Дата"]&rt=1&режим=1

Все остальное оставьте как есть, сохраните и закройте файл.

5. Создаём новую книгу в Excel, открываем лист, куда хотим импортировать архив курсов ЦБ. В любую подходящую ячейку введите формулу, которая даст нам текущую дату. в текстовом формате для замены запроса:

=ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГГГ»)

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

=ТЕКСТ(СЕГОДНЯ(),»дд.мм.гггг»)

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

Нужный код также можно подсмотреть в строке запроса прямо на сайте ЦБ.

6. Загружаем данные на лист, используя за основу созданные ячейки и файл cbr.iqy, т.е. переходим на вкладку Данные – Соединения – Найти других (Данные — существующие связи). В открывшемся окне выбора источника данных найдите и откройте файл cbr. ики. Перед импортом Excel уточнит у нас три вещи.

Во-первых, куда импортировать таблицу данных:

Во-вторых, откуда взять код валюты (можно поставить галочку Используйте это значение по умолчанию (Используйте это значение/ссылку для будущих обновлений), чтобы потом каждый раз при обновлениях эта ячейка не указывалась и чекбокс Автоматически обновлять при изменении значения ячейки (Обновляется автоматически при изменении значения ячейки):

В-третьих, из какой ячейки брать дату окончания (здесь также можно поставить обе галочки, чтобы завтра не пришлось задавать эти параметры вручную при обновлении):

Нажмите OK, подождите пару секунд и получите на листе полный архив курса нужной валюты:

Как и в первом способе, щелкнув правой кнопкой мыши по импортированным данным и выбрав команду Свойства диапазона (Свойства диапазона данных), вы можете настроить частоту обновления При открытии файла (Обновляется при открытии файла). Далее, если у вас есть доступ к Интернету, данные будут автоматически обновляться каждый день, т. е. таблица будет автоматически пополняться новыми данными.

Из нашей таблицы проще всего извлечь курс на нужную дату с помощью функции ВПР (ВПР) – если вы с этим не знакомы, то я настоятельно советую вам это сделать. По такой формуле, например, из нашей таблицы можно выбрать курс доллара на 10 января 2000 года:

или по-английски =VLOOKUP(E5,cbr,3,1)

в котором

• E5 – ячейка, содержащая данную дату
  
• CBR – имя диапазона данных (автоматически генерируется при импорте и обычно совпадает с именем файла запроса)
• 3 – порядковый номер столбца в нашей таблице, откуда мы берем данные
• 1 – аргумент, включающий приблизительный поиск по функции ВПР, чтобы можно было найти курсы для тех промежуточных дат, которых на самом деле нет в столбце А (будет взята ближайшая предыдущая дата и ее курс). Подробнее о приблизительном поиске с помощью функции ВПР можно прочитать здесь.

• Макрос для получения курса доллара на заданную дату в текущей ячейке
• Дополнительная функция PLEX для получения курса доллара, евро, гривны, фунта стерлингов и т. д. на любую заданную дату.
• Вставьте любой курс валюты на любую дату в дополнении PLEX