Получение курсов валют в Google Sheets
Michael SmirnovПравильный канал о Таблицах (Google Sheets): @google_sheets
Чат канала: @google_spreadsheets_chat
Здравствуйте, товарищи!
Часто спрашивают в чате, как получить курсы валют в Google Sheets? Разберём несколько подходов на простых примерах.
Сходу - таблица с примерами (делайте копию), далее - описание.
⚠️ Внимание! Увага! Назар аударыңыз! 注意！توجه! ⚠️
На текущий момент данные Национального банка Республики Беларусь описанным ниже методом получить не удаётся. Предположительно, они закрылись от гугловских IP (с которых идут запросы, когда вы пишите формулы импорта), а, может, и не только от гугловских, но от всего Запада (из России всё доступно).
Почему и зачем они это сделали, не очень понятно. Может, мы с вами слишком часто курсы валют запрашивали? А, может, не работает совсем по другой причине.
Получить данные пробовали и скриптом, результат такой же: не получилось, вылетело по timeout или с бессмысленными капризами "ой, нас DDoS'ят!!! памагити!!!".
Проблемы эти наблюдаются с 27.03.2022 (добрые люди обратили наше внимание) и на сегодняшний день (14.03.2024) никуда они не делись.
ЦБ РФ тоже временно не работал с табличками (примерно с марта 2023), но отпустило. 18.07.2023 всё было ок.
Потом клоуны из ЦБ РФ опять закрыли доступ. С 2024-03-14 (или раньше) снова нельзя формулами забрать данные.
⚠️ Внимание! Увага! Назар аударыңыз! 注意！توجه! ⚠️
Google Finance
Функция GOOGLEFINANCE() (документация) берёт данные с Google Finance, где, например, можно посмотреть курс доллара в рублях по ссылке:
https://www.google.com/finance/quote/USD-RUB
Или наоборот, курс рубля в долларах:
https://www.google.com/finance/quote/RUB-USD
Данные, которые возвращает GOOGLEFINANCE(), обновляются примерно каждые 20 минут.
Хоть данные и не с потолка берутся, Google советует к ним относиться с аккуратностью, носят чисто информационных характер, не использовать для торговли, не считать рекомендациями и пр.
Получение текущего курса USD в рублях:
=GOOGLEFINANCE("CURRENCY:USDRUB")
Результат:
73,4836
Получение курса, который был неделю назад:
=GOOGLEFINANCE("CURRENCY:USDRUB"; "price"; TODAY() - 7)
Результат:
| Date | Close | |---------------------|---------| | 18.05.2021 23:58:00 | 73,6974 |
Получение курсов за прошлые 7 дней:
=GOOGLEFINANCE("CURRENCY:USDRUB"; "price"; TODAY() - 7; 7)
Результат:
| Date | Close | |---------------------|---------| | 18.05.2021 23:58:00 | 73,6974 | | 19.05.2021 23:58:00 | 73,7663 | | 20.05.2021 23:58:00 | 73,4719 | | 21.05.2021 23:58:00 | 73,641 | | 22.05.2021 23:58:00 | 73,6226 | | 23.05.2021 23:58:00 | 73,6478 | | 24.05.2021 23:58:00 | 73,4738 |
В таблице есть примеры вызовов GOOGLEFINANCE(), которые мне непонятны. Если знаете, что они возвращают, напишите в чате.
Официальные курсы ЦБ РФ, НБУ и НБ РБ
Посмотреть курсы валют в российских рублях на заданную дату, которые устанавливает ЦБ РФ, можно на сайте. Например, по ссылке, которую легко составить в Google Sheets, можно получить курсы валют на 25.05.2021:
https://www.cbr.ru/currency_base/daily/?UniDbQuery.Posted=True&UniDbQuery.To=25.05.2021
Данные со страницы можно вытащить с помощью IMPORTHTML(), но мы так делать не будем - есть метод получше.
ЦБ РФ предоставляет данные в виде XML (описание). Они менее нагружены, чем HTML страницы, ничего лишнего - будут быстрее загружаться, легче будет разбирать.
Тут нам поможет функция IMPORTXML() (документация) и небольшие знания XPath (tutorial).
ЦБ РФ - Справочник по валютам
Тут у ЦБ РФ справочник по валютам:
https://www.cbr.ru/scripts/XML_valFull.asp
С помощью следующей формулы достаём все поля этого справочника. Первой колонкой атрибут ID элементов <Item> (одна из валют) - xpath //Item/@ID, последующие колонки - все подэлементы каждого <Item> - xpath //Item.
={
"ID"\ "Name"\ "EngName"\ "Nominal"\ "ParentCode"\ "ISO_Num_Code"\ "ISO_Char_Code";
IMPORTXML("https://www.cbr.ru/scripts/XML_valFull.asp"; "//Item/@ID")\
IMPORTXML("https://www.cbr.ru/scripts/XML_valFull.asp"; "//Item")
}
ЦБ РФ - Курсы валют на заданную дату
По этой ссылке можно получить курсы валют на заданную дату (для другой даты достаточно соответственно поменять конце строки):
https://www.cbr.ru/scripts/XML_daily.asp?date_req=25/05/2021
Обратите внимание на поле Nominal. Чаще всего это 1 (например, 73,5266 Российских рублей за 1 доллар США), но бывает больше (например, 17,1809 Российских рублей за 100 Казахстанских тенге). Для получения курса за 1 единицу валюты, значение элемента <Value> надо разделить на значение элемента <Nominal>.
Отсюда формула. Первая колонка - элемент <CharCode> (полученный курс надо будет где-то использовать, найти его сможем, например, по CharCode), вторая - курс.
В ячейке A6 мы подготовим URL:
="http://www.cbr.ru/scripts/XML_daily.asp?date_req=" & TEXT(DATE(2021; 5; 25); "dd/mm/yyyy")
Итоговая формула:
={
"CharCode"\ "Курс";
IMPORTXML(A6; "//CharCode")\
ARRAYFORMULA(
IMPORTXML(A6; "//Value") / IMPORTXML(A6; "//Nominal")
)
}
ЦБ РФ - Значения курса валюты за период
По этой ссылке можно получить курс доллара США за предыдущие 7 дней:
С датами, думаю, всё понятно. Только стоит помнить, что курс на воскресенье и понедельник (они отсутствуют в выдаче) равен субботнему курсу, он устанавливается в пятницу и на выходных не меняется.
Параметр в ссылке VAL_NM_RQ - это внутренний ID валюты ЦБ РФ, его можно взять в справочнике (см. выше).
В ячейке A6 мы подготовим URL:
="https://www.cbr.ru/scripts/XML_dynamic.asp?" & "date_req1=" & TEXT(TODAY() - 7; "dd/mm/yyyy") & "&date_req2=" & TEXT(TODAY(); "dd/mm/yyyy") & "&VAL_NM_RQ=R01235"
Следующая формула выведет дату курса и его значение в двух колонках:
={
"Дата"\ "Курс";
IMPORTXML(A6; "//Record/@Date")\
ARRAYFORMULA(
IMPORTXML(A6; "//Value") / IMPORTXML(A6; "//Nominal")
)
}
НБУ - Курсы валют на заданную дату
Аналогично ЦБ РФ, не будем разбираться с HTML, так как НБУ предоставляет (описание) различные данные в формате XML или JSON.
Нас интересует первый раздел с курсами валют.
По следующей ссылке можно получить курс выбранной валюты (ISO Code) на указанную дату (25.05.2021):
https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?valcode=EUR&date=20210521
Если убрать из запроса параметр valcode, то получим курсы всех известных НБУ валют на 25.05.2021:
https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?date=20210521
В ячейке A6 мы подготовим URL:
="https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?date=" & TEXT(DATE(2021; 5; 25); "yyyymmdd")
Следующая формула выведет нам две колонки: код валюты и курс.
={
"CharCode"\ "Курс";
IMPORTXML(A6; "//cc")\
ARRAYFORMULA(
--SUBSTITUTE(IMPORTXML(A6; "//rate"); "."; ",")
)
}
Не забываем менять точку на запятую (SUBSTITUTE(...; "."; ",")) и преобразовывать в число (--(...)).
Если убрать из ссылки параметр date, получим текущие курсы валют.
НБУ - Значения курса валюты за период
Я не увидел в документации НБУ ссылки, по которой можно получить данные о динамике курса выбранной валюты за указанный период. Поделитесь в чате, если знаете.
НБ РБ - Справочник по валютам
Всё очень похоже на ЦБ РФ.
Описание XML тут (сильно лучше ЦБшного):
https://www.nbrb.by/statistics/rates/xml
Тут у ЦБ РФ справочник по валютам:
https://www.cbr.ru/scripts/XML_valFull.asp
С помощью следующей (в A6 ссылка на данные справочника) формулы достаём все поля этого справочника. Первой колонкой атрибут Id элементов <Currency> (одна из валют) - xpath //Currency/@Id, последующие колонки - все подэлементы каждого <Currency> - xpath //Currency.
={
"Id"\ "NumCode"\ "CharCode"\ "Scale"\ "Name"\ "EnglishName"\ "ParentCode";
IMPORTXML(A6; "//Currency/@Id")\
IMPORTXML(A6; "//Currency")
}
НБ РБ - Курсы валют на заданную дату
По этой ссылке можно получить курсы валют на заданную дату (для другой даты достаточно соответственно поменять конце строки):
https://www.nbrb.by/services/xmlexrates.aspx?ondate=05/25/2021
Обратите внимание на элемент <Scale>. Чаще всего это 1 (например, 2.5092 Белорусских рубля за 1 доллар США), но бывает больше (например, 2.3043 Белорусских рубля за 100 Японских йен). Для получения курса за 1 единицу валюты, значение элемента <Rate> надо разделить на значение элемента <Scale>.
Отсюда формула. Первая колонка - элемент <CharCode> (полученный курс надо будет где-то использовать, найти его сможем, например, по CharCode), вторая - курс.
В ячейке A6 мы подготовим URL:
="https://www.nbrb.by/services/xmlexrates.aspx?ondate=" & TEXT(DATE(2021; 5; 25); "mm/dd/yyyy")
Итоговая формула:
={
"CharCode"\ "Курс";
IMPORTXML(A6; "//CharCode")\
ARRAYFORMULA(
--SUBSTITUTE(IMPORTXML(A6; "//Rate"); "."; ",") / IMPORTXML(A6; "//Scale")
)
}
НБ РБ - Значения курса валюты за период
По этой ссылке можно получить курс доллара США за предыдущие 7 дней:
https://www.nbrb.by/services/xmlexratesdyn.aspx?curid=145&fromdate=5/18/2021&todate=5/24/2021
Параметр в ссылке curid - это внутренний ID валюты НБ РБ, его можно взять в справочнике (см. выше, первый столбец).
В ячейке A6 мы подготовим URL:
="https://www.nbrb.by/services/xmlexratesdyn.aspx?" & "curid=145" & "&fromdate=" & TEXT(TODAY() - 7; "mm/dd/yyyy") & "&todate=" & TEXT(TODAY(); "mm/dd/yyyy")
Следующая формула выведет дату курса и его значение в двух колонках:
={
"Дата"\ "Курс";
IMPORTXML(A6; "//Record/@Date")\
ARRAYFORMULA(
--SUBSTITUTE(IMPORTXML(A6; "//Rate"); "."; ",")
)
}
НБ РК
Аналогично можно получить курсы с сайта НБ РК. Описание тут.
- https://nationalbank.kz/rss/rates_all.xml - текущие курсы валют (как мы поняли)
- https://nationalbank.kz/rss/get_rates.cfm?fdate=23.02.2009 - курсы валют на заданную дату
Соответствующий пример добавили в таблицу.
XE.com - Курсы валют на заданную дату
XE.com - один из популярных ресурсов, где тоже можно посмотреть курсы валют (и исторические данные доступны без javascript, так что мы их сможем легко импортировать в таблицу). У них есть платное API, но можно импортировать HTML страницу с данными.
Например, курс доллара США в других валютах на 25.05.2021:
https://www.xe.com/currencytables/?from=USD&date=2021-05-25
Во второй половине страницы таблица с данными, которая нас интересует:
В ячейке A6 мы подготовим URL:
="https://www.xe.com/currencytables/?from=USD&date=" & TEXT(DATE(2021; 5; 25); "yyyy-mm-dd")
Достанем первую таблицу с данными с помощью функции IMPORTHTML() (документация):
=IMPORTHTML(A6; "table"; 1)
На XE.com можно узнать, например, сайт центрального банка для какой-нибудь валюты (может, у них тоже есть ссылки с XML?):
https://www.xe.com/currency/inr-indian-rupee/
MOEX – Текущий курс с Московской Биржи
Добавили в таблицу пример конкретно для текущих курсов 3-х валют: CNY, EUR, USD по отношению к RUB.
Аналогично предыдущим примерам достаём данные из xml (но можно и html или json использовать).
Нам понадобится ссылка:
https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities.xml?iss.only=marketdata&marketdata.columns=SECID,LAST&securities=USD000000TOD,EUR_RUB__TOM,CNY000000TOD
Откуда такие ссылки брать немного описано у нас в короткой статье про то, как доставать данные с мосбиржи.
Данных может не быть, если биржа работает, но за сегодня ещё не было сделок. Данные предоставляются с задержкой в 15 минут (для этих бесплатных запросов).
Тут мы запросили только 3 валюты. Если убрать этот фильтр (securities=..., тут через запятую перечисленные коды инструментов, получим всё, что есть.
Тут можно посмотреть значение на самой бирже, чтобы проверить.
Существует множество других источников данных о курсах валют. Для них можно самостоятельно попробовать адаптировать то, что написано выше.
Cсылки
Таблица с описанными примерами (делайте копию)
Функции Google Sheets
XPath
В интернете полно информации об использовании XPath. Больше экспериментируйте, чтобы разобраться.
- Туториал от W3C - XPath Tutorial (англ.)
- Есть достаточно материалов на русском языке: поиск xpath в яндексе
- Полезно посмотреть, что есть на Stack Overflow с тегом xpath
Источники данных по курсам валют
- Буквенные коды валют - Wikipedia - ISO 4217 - Active codes
- Google Finance
- ЦБ РФ (Россия) - База данных по курсам валют
- ЦБ РФ (Россия) - Получение данных, используя XML
- НБУ (Украина) - Фінансові ринки
- НБУ (Украина) - Офіційний курс гривні щодо іноземних валют
- НБУ (Украина) - API для розробників (справочник ссылок для получения данных в формате XML или JSON)
- НБ РБ (Белоруссия) - Официальный курс белорусского рубля
- НБ РБ (Белоруссия) - Получение данных, используя XML
- XE.com - Historical rate tables
Правильный канал о Таблицах (Google Sheets): @google_sheets
Чат канала: @google_spreadsheets_chat