Таблицы Google: Загружаем курсы валют с сайта ЦБ через функцию IMPORTXML

11 апреля 2022   Сергей Кикевич   Все авторы

Продолжаем тему загрузки курсов валют с сайта ЦБ. В прошлой статье на эту тему я рассказал об имеющихся вариантах по загрузки истории курса валют ЦБ через EXCEL. Аналог есть и для Таблиц Google (Google Sheets).

В EXCEL загрузка данных происходит через функцию ВЕБСЛУЖБА (WEBSERVICE), после чего данные необходимо отфильтровать с помощью ФИЛЬТР.XML (FILTERXML). Как мы описывали в стать, у функции ВЕБСЛУЖБА в EXCEL есть один важный недостаток – низкий параметр TimeOut, который к тому же не регулируется. Поэтому в EXCEL при работе с API Центрального банка возникают сложности, которые легко уладить с помощью макросов (VBA), но это уже программирование.

В Таблицах Google работает похожий механизм, но вместо связки функций ВЕБСЛУЖБА - ФИЛЬТР.XML есть одна функция IMPORTXML. Что важно, никаких проблем со слишком долгим ответом от API у этой функции нет.

Загрузка курсов валют через IMPORTXML

Функция IMPORTXML() имеет 3 параметра:

  • URL адрес запроса
  • фильтр на языке язык запросов XPath
  • кодировка

Последний параметр нужен только, если получается информация в экзотической кодировке.

Как мы уже знаем, запросы на REST API Центрального банка курсов валют имеют форму:

http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=01.01.2022&date_req2=01.02.2022&VAL_NM_RQ=R01235

В получаемом ответе нам нужны только значения дат (фильтр XPath: "//@Date") для колонки «Дата» и курсов валют (фильтр XPath: "//Value") для колонки «Курс».

Загрузка истории официальных курсов валют с сайта ЦБ c помощью Таблиц Google

Таблица Google с возможностью загрузки официальных курсов валют с сайта ЦБ доступна по ссылке.

Таблица Google с загрузкой истории официального курса валют Центрального банка России
ВНИМАНИЕ
: Если шаблон вам понравился, скопируйте его себе. После этого вы можете использовать его без ограничения по своему усмотрению.

Пример загрузки истории официальных курсов валют с сайта ЦБ в Google Sheets

Комментарии ()

  1. Валя 14 сентября 2022, 16:29 # 0
    !!!
    1. Андрюша 03 мая 2023, 09:49 # 0
      А как отображать последний валидный курс? Например в СБ и ВС показывать за пятницу?
      1. Сергей 03 мая 2023, 11:22 # 0
        Да, всё верно. В субботу и воскресенье продолжает действовать курс ЦБ пятницы.
      2. Наталия 12 июня 2023, 19:46 # 0
        Подскажите, пожалуйста, где найти эти коды с буквой R? Мне нужна валюта — казахстанский тенге. Спасибо
        1. Сергей 13 июня 2023, 09:07 # 0
          Здравствуйте. На сайте ЦБ есть список всех кодов валют: https://cbr.ru/scripts/XML_val.asp?d=0
        2. DMITRI 19 июня 2023, 22:10 # 0
          Подскажите, а что в формуле заменить, если необходим курс только на текущую дату.
          1. Сергей 20 июня 2023, 13:17 # 0
            Формат XML ссылки не меняется. Просто укажите в ячейке дат сегодняшнее число. Можно сделать, чтобы сегодняшнее число ставилось автоматически с помощью =СЕГОДНЯ()
            1. Дмитрий 26 июня 2023, 15:48 # 0
              Спасибо, но тогда получается курс текущей даты не зафиксировано в какой-то одной ячейке так как значение появляется в конце списка. И так как ячейка всегда разная на нее невозможно ссылаться другим формулам. Можно ли зафиксировать значение курса на текущую дату в какой-то определенной ячейке?
              1. Сергей 28 июня 2023, 13:39 # 0
                Если значения «Начало периода» и «Конец периода» показывают одну дату (сегодня), списка не будет. Показывается только одно значение в одной ячейке B2.
                http://joxi.ru/l2Z4nqKUVYLzgr
                1. Дмитрий 10 июля 2023, 11:36 # 0
                  Получается, если вставить =TODAY(), то будет давать ошибку в Субботу, Воскресенье и до изменения курса в Понедельник т.к. не сможет подтянуть конечную дату в виду ее отсутствия. Подскажите, может есть способ или формула, позволяющая брать последнюю актуальную дату?
          2. Ирина 29 июня 2023, 13:46 # 0
            А можно ли сделать постоянно обновляемую таблицу с курсами на сегодня и за предыдущие 7 дней, не прописывая конкретную дату?
            1. Сергей 04 июля 2023, 07:22 # 0
              В ячейке «Конец периода» ставите формулу =TODAY(). В ячейке «Начало периода» ссылаетесь на эту ячейку и вычитаете 7…
            2. Сергей 14 июля 2023, 06:57 # 0
              В базе данных okama эта проблема решена, в выходные курс равен предыдущему дню. Можете скачать по API: http://api.okama.io:5000/api/ts/adjusted_close/USDRUB.FX?first_date=2019-01&last_date=2020-01&period=m
              1. Alex 10 февраля 2024, 13:42 # 0
                Сергей, здравствуйте! Формула перестала работать в 2024 году. Что-то поменялось у ЦБ?
                1. Сергей 10 февраля 2024, 17:37 # 0
                  только что проверил… у меня всё работает, в т.ч. для данных за 2024 год: joxi.ru/KAgg7V5hORYjOA

                Оставьте комментарий

                наверх