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

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

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

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

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

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

webquery11.png

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

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

webquery12.png

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

webquery13.png

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

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

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

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

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

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

webquery14.png

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

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

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

webquery15.png

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

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

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

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

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

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

=TEXT(TODAY(),"dd.mm.yyyy")

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

Валюта

Код   

Доллар США

R01235

Евро

R01239

Фунт стерлингов

R01035

Японская иена

R01820

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

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

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

webquery16.png

Во-вторых, откуда брать код валюты (можно установить флажок Использовать данное значение по умолчанию (Use this value/reference for future refreshes), чтобы не указывать потом каждый раз эту ячейку при обновлениях и флажок Автоматически обновлять при изменении значения ячейки (Refresh automatically when cell value changes):

webquery17.png

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

webquery18.png

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

webquery19.png

Как и в первом способе, щелкнув правой кнопкой мыши по импортированным данными и выбрав команду Свойства диапазона (Data range properties), можно настроить частоту обновления При открытии файла (Refresh on file open). Тогда при наличии доступа к интернету данные будут автоматически обновляться каждый день, т.е. таблица будет самостоятельно дополняться новыми данными.

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

webquery21.png

или в англоязычном варианте =VLOOKUP(E5,cbr,3,1)

где

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

Ссылки по теме


Страницы: 1  2  
08.09.2013 18:20:48
Добрый день Николай, прочитал статью о импорте курса валют, заинтересовало простотой и удобством!  Решил создать файл с курсами основных валют, которыми пользуюсь каждый день, но к сожалению, Exell 2013 определяет курс "4.58" как "апрель.58". вот ссылка на документ http://sdrv.ms/1dSKqih
Пробовал изменять формат ячейки перед импортом данных,  не помогает.
Может быть есть решение?
С уважением, Владас.
09.09.2013 10:24:43
Добрый день, в продолжение разговора, разобрался  с проблемой отображения чисел меньше трех знаков целого числа, все оказалось просто :), нужно настроить "не отображать дату" в настройках импорта.
Ссылка на скриншот:http://sdrv.ms/1ajcgjy
09.10.2013 13:33:09
Здравствуйте, спасибо, Способ 1 получился с первого раза.
В ячейке с нужным курсом стоит пометка up или down (насколько изменился курс), и эту ячейку не получается использовать в пересчетах. Не подскажете как решить?
28.10.2013 14:41:54
Просто выдерните курс из ячейки с пометкой с помощью конструкции =ЛЕВСИМВ(F3;7)*1
15.06.2017 16:09:53
Добрый день Николай! А можно ставить границы, заливки и т.п. на таблицу? Спасибо
Let
28.10.2013 21:16:24
Николай, а возможно ли данный запрос сохранять не в файле с расширением "iqy", а в том же эксель-файле, куда делается выгрузка данных по данному запросу?

Например на отдельном листе?

Иначе не совсем удобно. Приходится пересылать 2 файла (с запросом и выгрузкой).

И еще.
Странно это или нет, но "наложить" на выдаваемый диапазон данных умную таблицу не получается. :(
28.12.2013 11:16:39
Не думаю, что такое возможно :)
08.03.2016 08:34:29
Почему невозможно? Просто внедрить файл запроса в книгу Excel
29.10.2013 12:39:28
во как долго, но всеравно спасибо!
я решил задачу получением слова до первого пробела =ПСТР(F3;1;НАЙТИ(" ";F3)-1)
попробую ваш вариант, он кажется правильней
06.12.2013 11:32:17
Шикарные функции, на самом деле.
25.12.2013 12:04:53
центробанк изменил сайт :(
теперь появляется:  Руб 32,6284 down -0.3222
подскажите пож. как правильно выдернуть курс?
25.12.2013 19:24:30
Можно выдернуть только число в соседнюю ячейку с помощью функции =ПСТР(F3;5;7), где F3 - ячейка со строкой Руб 32,6284 down -0.3222
очень помогла
но вот вопрос, она выдает курс с 3 знаками после запятой, а нужно 4
наши бухи в этом вопросе весьма требовательны
как поправить?
сообразила
вопрос снят:)
27.12.2013 12:47:28
Спасибо! С Наступающим Вас!
07.01.2014 15:43:02
С Рождеством!
Подскажите пожалуйста, такая проблема, при прямом использование полученных данных в формулах вычисления выдает ошибку, если я скажем, умножаю цену товара на ячейку с ипортированым курсом получаю #ЗНАЧ!
08.01.2014 09:46:51
Евгений, значит у вас в ячейке с курсом не число, а текст, скорее всего. Используйте функцию преобразования текста в число ЗНАЧЕН(A1), где А1 - ячейка с курсом.
08.01.2014 16:13:28
В таком случае выдает такое вот
23.01.2014 15:13:45
Евгений, на сайте разделитель между целой и дробной часть - точка, а у вас в Excel - запятая.
Или найдите другой сайт или переключите разделитель (Файл - Параметры - Дополнительно).
23.01.2014 13:42:44
Добрый день Николай!
Огромное спасибо за статью!
По мне так Вы просто гений.

Все у меня получилось но осталось непонятным нжуен ли всегда текстовй файл запрос cbr.iqy?
Можно ли обходится без него в дальнейшем строку запроса интегрировав в Эксел?
Простите если неточно формулирую.

Спаибо заранее!
23.01.2014 15:11:33
К сожалению, файл запроса нужен.
23.01.2014 16:40:48
Спасибо Николай!
А тогда гскажите плиз где файл эксел и файл запроса должны находитсяна при переносе на другой компютер?
Просто в одной папке или в папке с  таким же путем и названием как при создании таблицы эксел с запросом для валюты?
Спасибо!!!
23.01.2014 14:41:30
Николай и сразу другой вопрос выплывает а если текущий курс мне нужен в цифрах а он же его мне пишет как текст
Руб 45,8242
Руб 45,9125 up +0.0883
У меня не хвтает знаний решить этот вопрос.
Если возмрожнопрошу подсказать.
Спасибо!
23.01.2014 15:11:13
Посмотрите комменты выше - это уже спрашивали.
23.01.2014 16:48:16
Спаибо Николай вопрос решен!!!
23.01.2014 17:14:36
Николай, у меня таблица с курсами называется уже не cbr a cbr_4  т.к.  их много поназагружал.
Не смог понять как можно почистить их чтобы остался 1 запрос cbr.
Подскажите плиз!
Спасибо!
11.02.2014 01:26:16
как быть на макоси? офис мак 2011 не позволяет грузить данные ((
11.02.2014 01:47:08
Office for Mac много чего не позволяет, к сожалению :(
Был бы для Mac нормальный Office - давно на них перешел бы.
Вроде бы, обещали 2014 версию в апреле - посмотрим.
11.02.2014 01:48:00
К концу года только, согласно слухам.
12.02.2014 14:21:02
Николай здравствуйте. У меня есть к Вам вопрос. Возможно ли сделать автоматическую конвертацию всех данных в столбце указанных в казахских тенге в российский рубль при открытии файла. Например таблица
Наименование инвестиций       Сумма, тенге
Земельный участок (1 000 м2)     3 831 500,00
Производственное помещение 71 615 332,80
Ремонт                                           11 668 000,00
ГАЗ-330202-14                               2 792 000
Орг. техника, НМА, мебель               2 180 000
Оборотные средства                 79 053 749
Итого:                                               471 649 093
Можно ли обойтись  без доп. столбца для конвертации, а сконвертировать столбец с суммой, т.к. к нему подвязаны формулы на многих других страницах.
17.02.2014 12:29:40
Здравствуйте, подскажите пожалуйста, можно ли каким либо образом сделать импорт цен на драгоценные металлы с сайта ЦБ РФ аналогично указанному выше способу за определенный период?
Дело в том, что при настройке импорта не появляется желтая стрелочка над таблицей с ценами на драгоценные металлы
03.03.2014 15:06:31
стрелочка не над таблицей, а в левом нижнем углу экрана
05.03.2014 08:35:43
А с новым дизайном сайта ЦБ РФ от Студии Артемии Лебедева таблицу для импорта теперь выделить нельзя и способ из статьи не работает.
05.03.2014 10:24:05
Я тоже вчера столкнулся с обговлением сайта ЦБ РФ :(. Мне очень симпатичны работы Студии Артемия Лебедева, НО тут перебор!
ВЕРНИТЕ ОБРАТНО отдельную таблицу с результатами параметрического веб-запроса для получения курса валют на заданный интервал дат!!!

А если по делу, то Способ 2 из статьи работает (проверено), только теперь выгружается вся страница целиком :(, НО после выгрузки её можно "доработать напильником" ;).
05.03.2014 10:54:59
Я с этим обновлением столкнулся вчера на тренинге, когда показывал импорт данных с веб-страницы по обновляемому запросу. Статью можно переписывать :(
05.03.2014 11:05:33
Николай, ждём обновление статьи.
Не всегда явные, НО от этого не менее преданные и благодарные пользователи :).
07.03.2014 13:51:39
Присоединяюсь ))
28.07.2014 13:42:18
Способ работает, как и раньше. Просто ссылка на таблицу находится теперь не слева от нее, а в самом низу страницы слева. Ищите :)
12.03.2014 13:16:02
здравствуйте, я новичок и столкнулся вот с такою проблемой
живу я в Молдавии и на одних компьютерах стоит русский windows а на других английский
чтобы у меня дата отображалось правильно и работал запрос, формулу =ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ") я написал так =ТЕКСТ(СЕГОДНЯ();"dd.mm.yyyy"). на компьютерах с русской ОС это не работает
подскажите плиз как в одну ячейку написать две формулы с временем так чтобы и на английском ОС и русском запрос работал правильно
14.03.2014 11:10:41
Люди плиз помогите
я же болеее просстой запрос использую (http://cbr.ru/currency_base/daily.aspx?date_req=["Дата"]) и он работает а с датой не как не могу зделать чтобы в мои документ определялась на всех компьтерах
19.03.2014 12:45:57
Добрый день!
Возникла такая проблема. см. ссылки
С сайта ЦБ курс не загрузить
http://yadi.sk/d/Y9RHBXtvKoEzS
http://yadi.sk/d/5I2uVWu7KoEzG
http://yadi.sk/d/8tTKiKTIKoEyy
19.03.2014 12:49:30
да нет, они то загружаются. Просто слишком много лишней информации получается. Я её просто скрываю.
29.03.2014 12:13:13
Можно загружать только таблицу, без "лишней информации". Для этого нужно использовать черно-желтую стрелку в нижнем левом углу страницы (а не в верхнем левом углу). Параметрический вэб-запрос работает как и раньше, необходимо только сделать небольшие исправления в строке запроса и обновить подключение.
31.03.2014 12:36:47
 не нашёл эту стрелку в нижнем углу...
03.04.2014 15:13:42
Подскажите, а как можно качать данные из xml.
Вот есть сайт
http://www.micex.ru/marketdata/quotes с него хотелось скачать значение индекса RVI
но как в указанном видео желтеньких квадратиков здесь нету, но при этом ест ссылка на xml
http://www.micex.ru/issrpc/marketdata/stock/index/daily/short/result_2014_04_03.xml?boardid=RTSI&secid=RVI

можно ли каким то образом загрузить это в excel ?

В google.docs есть подобная функция для загрузки с ЦБ
=IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp"; "//ValCurs/Valute[@id="&char(34)&"R01235"&char(34)&"]/Value";)
но разобраться с ней и найти аналоги в excel не удалось.
Помогите пожалуйста.
19.04.2014 10:52:50
В Excel 2013 появились функции ВЕБСЛУЖБА и ФИЛЬТР.XML для таких задач.
05.05.2014 21:58:49
С помощью Fiddler вытащил курсы Сбербанк-Кострома на заданную дату.
Если кому-то интересно, все мысли и комментарии - СЮДА.
04.07.2014 09:05:59
Может, немного не в тему.. Если в одной книге порядка 15-20 запросов из веб на один и тот же сайт, можно ли с помощью макроса заставить книгу поочередно с промежутками в 5 секунд делать эти запросы?
Если обновлять командой "обновить все", во многих запросах появляется ошибка времени выполнения.
04.07.2014 09:59:32
Включить запись макроса.
Обновить каждый запрос вручную по очереди (правой кнопкой - Обновить).
Залезть в код записанного макроса (Alt+F8 и Изменить) и добавить между обновлениями паузу с помощью оператора
Application.Wait(Now + TimeSeries("00:00:05"))
05.08.2014 10:45:51
Добрый день, Николай!
Обновляю курс с помощью макроса:
 With Worksheets("Курс").QueryTables("cbrEUR")    .Connection = "URL;http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01239&date_req1=01.01.2014&date_req2=" & Format(Date + 1, "dd.mm.yyyy") & "&rt=1&mode=1"    .RefreshEnd With

Вчера столкнулся с проблемой - вероятно из-за того, что сайт cbr.ru был перегружен или со связью были перебои, но эта процедра подвесила эксель!
Вопрос - можно ли в код добавить таймаут или ограничить количество запросов к сайту?
С уважением, Роман!
ОТЛИЧНАЯ Статья! Но....

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

Во-вторых Это замечательное решение работает если в строке веб запроса присутствуют Коды финансового  инструмента, дата начала и дата конца загружаемых данных.  А что делать если разработчик сайта скрыл в адресной  строке эти параметры ?

К примеру, возьмём  Исторические Данные пары USD/CAD (http://ru.investing.com/currencies/usd-cad-historical-data) В скобках  - адрес , который выдаёт страница и ни слова о датах, таймфрейме, только название инструмента и не более того. Вот как тут быть? Есть решение от профессионала?    ;)
08.09.2014 13:01:46
Александр, тут вариант только один - писать макрос, который будет детально анализировать (парсить) текст веб-страницы и выдергивать оттуда нужные данные. Если оно сделано на flash, то - совсем никак, ибо картинка.
Вот то-то и оно. Весь интернет перерыл чтобы хоть небольшой примерчик найти. Ничего. В этом смысле Ваша статья - ЕДИНСТВЕННО Полезная. Все в основном "льют" очевидное - про стандартный web запрос и не более. Что собственно в любом учебнике расписано детально.
08.09.2014 12:43:28
Алгоритм не работает.  
08.09.2014 12:59:47
Георгий, почитайте статью, в ней обновленная методика. То, что описано в видео уже не актуально, т.к. сайт ЦБ изменил дизайн :(
А Вы когда статью обновляли? На Сайте сбербанка чёрно-жёлтая стрелка и квадратик совсем исчезли.
08.09.2014 13:50:21
Способ 2. Параметрический выдает не таблицу, а какую-то кашу из бокового меню сайта. МОжет есть какие-нибудь иные варианты добавления данных?
24.11.2014 15:43:11
Добрый день!
Успешно получилось привязать курс валют к интернету, все отлично обновляется.
Но возник один момент, менеджеры забывают нажимать кнопку "обновить" и выставляют счета по вчерашнему (к примеру) курсу. Можно ли обновлять автоматически файл при каждом входе в него и как это сделать?
Спасибо!  
25.11.2014 12:52:19
Алексей, щелкните правой кнопкой мыши по ячейке с курсом, выберите команду Свойства диапазона и включите там галочку Обновлять при открытии файла.
17.12.2014 12:06:33
Николай, добрый день!

Подскажите, можно как-то задавать формат в ячейках, которые импортируются со сторонних страниц с помощью первого способа.
Поясню. Есть столбец, значения которого на сайте представлены в виде "3/4", "12/10". Excel автоматом перегоняет это в даты. Как этого можно предотвратить и оставить первоначальные значения? Excel 2013.

Заранее спасибо за ответ.
Страницы: 1  2  
Наверх