Импорт курса валют из интернета
Импорт курса заданной валюты из интернета с автоматическим обновлением - весьма частая задача для многих пользователей Microsoft Excel. Представьте, что у вас есть прайс-лист, который должен пересчитываться каждое утро в соответствии с курсом. Или бюджет проекта. Или стоимость договора, которую надо посчитать, используя курс доллара на дату заключения договора.
В подобных ситуациях можно решить проблему по разному - всё зависит от того, какая версия Excel у вас установлена и какие надстройки поверх неё стоят.
Способ 1. Простой веб-запрос для текущего курса валют
Этот способ подойдет тем, у кого на компьютере пока ещё старые версии Microsoft Office 2003-2007. Он не использует никаких сторонних надстроек или макросов и оперирует только встроенными функциями.
Нажмите кнопку Из интернета (Web) на вкладке Данные (Data). В появившемся окне в строку Адрес (Address) введите URL сайта, с которого будет браться информация (например http://www.finmarket.ru/currency/rates/) и нажмите клавишу Enter.
Когда страница загрузится, то на таблицах, которые 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). После открытия файла запроса в Блокноте должны увидеть примерно следующее:
Самое ценное здесь - строка с адресом и параметры запроса в ней, которые мы будем подставлять - код нужной нам валюты (выделено красным) и конечная дата, которую мы заменим на сегодняшнюю (выделено синим). Аккуратно редактируем строку, чтобы получилось следующее:
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 уточнит у нас три момента.
Во-первых, куда импортировать таблицу с данными:
Во-вторых, откуда брать код валюты (можно установить флажок Использовать данное значение по умолчанию (Use this value/reference for future refreshes), чтобы не указывать потом каждый раз эту ячейку при обновлениях и флажок Автоматически обновлять при изменении значения ячейки (Refresh automatically when cell value changes):
В-третьих, из какой ячейки брать конечную дату (тут также можно установить оба флажка, чтобы завтра не пришлось задавать эти параметры вручную при обновлении):
Жмем ОК, ждем пару секунд и получаем полный архив курса нужной валюты на листе:
Как и в первом способе, щелкнув правой кнопкой мыши по импортированным данными и выбрав команду Свойства диапазона (Data range properties), можно настроить частоту обновления При открытии файла (Refresh on file open). Тогда при наличии доступа к интернету данные будут автоматически обновляться каждый день, т.е. таблица будет самостоятельно дополняться новыми данными.
Вытаскивать из нашей таблицы курс за нужную дату проще всего с помощью функции ВПР (VLOOKUP) - если вы с ней не знакомы, то очень советую сделать это. Вот такой формулой, например, можно выбрать из нашей таблицы курс доллара за 10 января 2000 года:
или в англоязычном варианте =VLOOKUP(E5,cbr,3,1)
где
- E5 - ячейка, где лежит заданная дата
- cbr - имя диапазона данных (автоматически создается при импорте и обычно совпадает с именем файла запроса)
- 3 - порядковый номер столбца в нашей таблице, откуда мы берем данные
- 1 - аргумент, включающий для функции ВПР приблизительный поиск, чтобы можно было находить курсы и для тех промежуточных дат, которые фактически не присутствуют в столбце А (будет браться ближайшая предыдущая дата и ее курс). Подробнее про приблизительный поиск с помощью функции ВПР можно понятно почитать тут.
Пробовал изменять формат ячейки перед импортом данных, не помогает.
Может быть есть решение?
С уважением, Владас.
Ссылка на скриншот:
В ячейке с нужным курсом стоит пометка up или down (насколько изменился курс), и эту ячейку не получается использовать в пересчетах. Не подскажете как решить?
Например на отдельном листе?
Иначе не совсем удобно. Приходится пересылать 2 файла (с запросом и выгрузкой).
И еще.
Странно это или нет, но "наложить" на выдаваемый диапазон данных умную таблицу не получается.
я решил задачу получением слова до первого пробела =ПСТР(F3;1;НАЙТИ(" ";F3)-1)
попробую ваш вариант, он кажется правильней
теперь появляется: Руб 32,6284 down -0.3222
подскажите пож. как правильно выдернуть курс?
но вот вопрос, она выдает курс с 3 знаками после запятой, а нужно 4
наши бухи в этом вопросе весьма требовательны
как поправить?
вопрос снят:)
Подскажите пожалуйста, такая проблема, при прямом использование полученных данных в формулах вычисления выдает ошибку, если я скажем, умножаю цену товара на ячейку с ипортированым курсом получаю #ЗНАЧ!
Или найдите другой сайт или переключите разделитель (Файл - Параметры - Дополнительно).
Огромное спасибо за статью!
По мне так Вы просто гений.
Все у меня получилось но осталось непонятным нжуен ли всегда текстовй файл запрос cbr.iqy?
Можно ли обходится без него в дальнейшем строку запроса интегрировав в Эксел?
Простите если неточно формулирую.
Спаибо заранее!
А тогда гскажите плиз где файл эксел и файл запроса должны находитсяна при переносе на другой компютер?
Просто в одной папке или в папке с таким же путем и названием как при создании таблицы эксел с запросом для валюты?
Спасибо!!!
Если возмрожнопрошу подсказать.
Спасибо!
Не смог понять как можно почистить их чтобы остался 1 запрос cbr.
Подскажите плиз!
Спасибо!
Был бы для Mac нормальный Office - давно на них перешел бы.
Вроде бы, обещали 2014 версию в апреле - посмотрим.
Наименование инвестиций Сумма, тенге
Земельный участок (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
Можно ли обойтись без доп. столбца для конвертации, а сконвертировать столбец с суммой, т.к. к нему подвязаны формулы на многих других страницах.
Дело в том, что при настройке импорта не появляется желтая стрелочка над таблицей с ценами на драгоценные металлы
ВЕРНИТЕ ОБРАТНО отдельную таблицу с результатами параметрического веб-запроса для получения курса валют на заданный интервал дат!!!
А если по делу, то Способ 2 из статьи работает (проверено), только теперь выгружается вся страница целиком
Не всегда явные, НО от этого не менее преданные и благодарные пользователи
живу я в Молдавии и на одних компьютерах стоит русский windows а на других английский
чтобы у меня дата отображалось правильно и работал запрос, формулу =ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ") я написал так =ТЕКСТ(СЕГОДНЯ();"dd.mm.yyyy"). на компьютерах с русской ОС это не работает
подскажите плиз как в одну ячейку написать две формулы с временем так чтобы и на английском ОС и русском запрос работал правильно
я же болеее просстой запрос использую (;) и он работает а с датой не как не могу зделать чтобы в мои документ определялась на всех компьтерах
Возникла такая проблема. см. ссылки
С сайта ЦБ курс не загрузить
Вот есть сайт
с него хотелось скачать значение индекса RVI
но как в указанном видео желтеньких квадратиков здесь нету, но при этом ест ссылка на xml
можно ли каким то образом загрузить это в excel ?
В google.docs есть подобная функция для загрузки с ЦБ
=IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp"; "//ValCurs/Valute[@id="&char(34)&"R01235"&char(34)&"]/Value";)
но разобраться с ней и найти аналоги в excel не удалось.
Помогите пожалуйста.
Если кому-то интересно, все мысли и комментарии - .
Если обновлять командой "обновить все", во многих запросах появляется ошибка времени выполнения.
Обновить каждый запрос вручную по очереди (правой кнопкой - Обновить).
Залезть в код записанного макроса (Alt+F8 и Изменить) и добавить между обновлениями паузу с помощью оператора
Application.Wait(Now + TimeSeries("00:00:05"))Обновляю курс с помощью макроса:
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 () В скобках - адрес , который выдаёт страница и ни слова о датах, таймфрейме, только название инструмента и не более того. Вот как тут быть? Есть решение от профессионала?
Успешно получилось привязать курс валют к интернету, все отлично обновляется.
Но возник один момент, менеджеры забывают нажимать кнопку "обновить" и выставляют счета по вчерашнему (к примеру) курсу. Можно ли обновлять автоматически файл при каждом входе в него и как это сделать?
Спасибо!
Подскажите, можно как-то задавать формат в ячейках, которые импортируются со сторонних страниц с помощью первого способа.
Поясню. Есть столбец, значения которого на сайте представлены в виде "3/4", "12/10". Excel автоматом перегоняет это в даты. Как этого можно предотвратить и оставить первоначальные значения? Excel 2013.
Заранее спасибо за ответ.
В настройках книги заменил наоборот точку на запятую - все работает. Извините.