Импорт курса валют из интернета
Импорт курса заданной валюты из интернета с автоматическим обновлением - весьма частая задача для многих пользователей 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 - аргумент, включающий для функции ВПР приблизительный поиск, чтобы можно было находить курсы и для тех промежуточных дат, которые фактически не присутствуют в столбце А (будет браться ближайшая предыдущая дата и ее курс). Подробнее про приблизительный поиск с помощью функции ВПР можно понятно почитать тут.
Кто-нибудь нашёл сайт с которого Excel логично читает курсы валют с разделителями запятыми?
Вчера попробовал всё, что предлагается выше ничего не получилось.
Убил три часа.
Дело возможно в Вашем компьютере.
Вернее в настройках ОС.
-возможно ОС + какие обновления стоят
- возможно Excel какая версия + какие настройки + какие обновления.
И т.д.
Естественно присоединяюсь ко всем восхищенным отзывам о данном ресурсе и благодарю автора и всех кто делится ценными знаниями.
Свой вопрос удалил.
Нашел причину, которая не относится к теме.
Подскажите, пожалуйста, как вставить текущую дату в сам запрос (с сайта cbr.ru), чтобы не писать формулу в excel?
Мне нужно выгружать курсы по всем валютам ежедневно на текущую дату. Но на сайте cbr.ru в запросе стоит выбранная дата, которую я хочу изменить на переменную, которая будет каждый день выдавать именно текущую дату, а не ту, которую я выбрала сегодня:
Такой запрос не работает:
Что же делать?
не подходит, потому что он часто не обновляет запрос по полдня. Хотя на этом сайте можно получить нужную информацию в том виде, в котором мне нужно.
Заранее спасибо.
Присоединяюсь к вопросу Натальи:
1. Задача: при открытии файла осуществлять выгрузку актуального курса валют (на текущую дату) с сайта ЦБ РФ.
На сегодняшний день выгрузка курсов/ создание динамического диапазона идет по запросу на сайт вида:
т.е. данные экспортируются на текущую дату. и при последующем открытии автообновление курсов не будет производиться.
(т.е будет для первоначальной даты).
т.е. для будущей даты будет необходимо по новой осуществлять выгрузку данных, создавая другой запрос.
2. Вопрос: Как можно сделать автовыгрузку/ автозамену для будущих значений данных.?
Алексей.
Сохранила файл с расширением .iqy (но не смогла выделить саму таблицу, поэтому выгружаю всю страницу)
Открыла в блокноте файл и заменила вот таким образом:
Подскажите пожалуйста, в чем моя ошибка?
Хотел уточнить, я делаю тоже самое для курсов с Нацбанка Казахстана. Проблема в том, что файл .iqy при открытии дает следующую ссылку:
Таким образом, отсутствует диапазон для изменения динамической даты. Подскажи, пожалуйста, как можно это исправить? Excel 2013
С уважением,
Максим
пишет следующее: не удается скачать нужные данные
Подскажите пожалуйста в чем проблема и как ее решить?
excel 2010.
по первому примеру выдает следующую ошибку
Этот веб-запрос не возвращает данные.
как устранить эту проблему?
спасибо
А я могу ставить границы на таблицу? Он будет обновляться?
"Этот веб-запрос не возвращает данные. Чтобы изменить запрос, нажмите кнопку 'ОК’, раскройте список имен в строке формул, выберите имя диапазона внешних данных, который используется для этого веб-запроса, щелкните его правой кнопкой мыши и выберите команду Изменить запрос"
К то сталкивался с подобным и решил эту проблему помогите пожалуйста.
Спасибо.
Попробовал импортировать курс валют как Вы и говорили во втором варианте. но к сожалению не получается.
Дело в том, что сохраненный файл в формате ( iqy ) выглядит несколько иначе чем тот что у вас.
Вот так выглядит мой файл:
WEB
1
Selection=11
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
Информацию по курсу я беру с сайта
И меня интересует курс Российского рубля к Казахстанскому тенге
Об этом есть видео - но там видимо эксель 16 года, и там внутренний браузер работает по другому
Теперь для этого есть Power Query (в 2019 версии он встроен по умолчанию).
С помощью PQ. Посмотрим будет ли обновляться.
спасибо!
работает уже более 10 лет без изменений:
Function GetRate(ByVal CurrencyName As String, Optional ByVal RateDate As Date) As Double Dim i As Long, xmldoc, nodeList, xmlNode On Error Resume Next If Len(CurrencyName) <> 3 Then Exit Function Else CurrencyName = UCase(CurrencyName) If RateDate = 0 Then RateDate = Date Set xmldoc = CreateObject("Msxml.DOMDocument"): xmldoc.async = False If Not xmldoc.Load("http://www.cbr.ru/scripts/XML_daily.asp?date_req=" & Format(RateDate, "dd\/mm\/yyyy")) Then Exit Function Set nodeList = xmldoc.SelectNodes("//Valute") For i = 0 To nodeList.Length - 1 Set xmlNode = nodeList.item(i) If xmlNode.ChildNodes(1).text = CurrencyName Then GetRate = CDbl(xmlNode.ChildNodes(4).text) / Val(xmlNode.ChildNodes(2).text) Exit Function End If Next i End Functionпримеры использования в Excel (в ячейке):
=GetRate("USD")=GetRate("EUR";"09/07/2024")=GetRate("CNY";"2024-07-09")=GetRate("KZT";ДАТА(2024;7;9))Функцию также можно использовать и в коде VBA
работает уже более 10 лет без изменений
Update: вопрос решён, эксель для.мак ос весьма обрезан в части функционала по сравнению с для винды.