Автоматизация сбора данных: как подтянуть курсы валютных пар ЦБ РФ

При расчёте доходности приобретения валютных пар и акций pre-IPO всегда нужны актуальные валютные курсы. Постоянно открывать сайт Центробанка РФ (ЦБ РФ) и копировать валютные курсы в Excel становится лень – и тут на помощь приходят Excel и VBA.

Решение простое как пареная гречка — сделать в Excel выпадающий список с выбором валюты и написать макрос, который сам подтянет нужный курс с сайта ЦБ РФ. Тогда я смогу просто выбрать, например, USD или EUR, и таблица сама подставит актуальный курс в расчет. Звучит классно, пора делать.

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

Первый раз в первый класс. Сделаем выпадающий список валют в Excel

Как создать выпадающий список валют в Excel — это база, это знать надо. Для этого есть несколько способов, самый простой – использовать проверку данных (Data Validation):

• В отдельном листе или столбце выписать список кодов валют, которые используются в расчетах. В XML ЦБ это: USD, EUR, GBP, CNY и др.;

• Выделить ячейку, где должен быть выбор валюты (скажем, B2), и в меню Данные → Проверка данных задать тип "Список". В качестве источника выбрать диапазон с кодами валют;

• В ячейке B3 также настроить список с выбором валюты, а в ячейке B4 задать формулу валютной пары B2/B3;

User-friendly список готов, теперь легко выбрать нужную валюту без риска опечататься в коде. А правильный код – это важно, ведь именно по нему макрос будет искать курс.

VBA-макрос для получения валютного курса с сайта ЦБ РФ

С выпадающим списком готово, переходим к самому интересному – напишем макрос на VBA, который будет парсить курс выбранной валюты с сайта Центробанка РФ. Благо ЦБ предоставляет удобный API: по ссылке вида http://www.cbr.ru/scripts/XML_daily.asp?date_req=DD/MM/YYYY можно получить XML-файл с официальными курсами валют на указанную дату. Если дату не указать, вернется список на текущий день.

Как будет работать макрос:

1. Прочитает из ячейки B2 код валюты, выбранный пользователем.

2. Сформирует URL для запроса курса. Например, для сегодняшней даты формируется строка запроса XML_daily.asp?date_req=02/04/2025 (формат dd/mm/yyyy).

3. Обратится по этому URL и получит XML с курсами на нужную дату.

4. Найдет в XML нужную валюту по коду (например, <CharCode>USD</CharCode>) и вытащит соответствующее значение курса (узел <Value>).

5. Запишет полученный курс в нужную ячейку (в ячейках справа от выпадающих списков - C2 и C3) или вернет как результат функции.

Звучит последовательно. Использовать для этого будем объект MSXML2.DOMDocument – встроенный парсер XML, доступный для VBA. Ниже код для макроса:

Sub GetExchangeRate()
    Dim ws As Worksheet
    Dim curCode As String
    Dim url As String
    Dim xmlDoc As Object
    Dim nodeList As Object
    Dim node As Object
    Dim rateStr As String
    Dim nominalStr As String
    Dim rateVal As Double
    Dim nominalVal As Double
    Set ws = ActiveSheet
    curCode = Trim(UCase(ws.Range("B2").Value))
    url = "https://www.cbr.ru/scripts/XML_daily.asp?date_req=" & Format(Date, "dd/MM/yyyy")

    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    xmlDoc.async = False
    If Not xmlDoc.Load(url) Then
        MsgBox "Не удалось загрузить данные с сайта ЦБ РФ.", vbExclamation
        Exit Sub
    End If

    Set nodeList = xmlDoc.getElementsByTagName("Valute")
    For Each node In nodeList
        If node.SelectSingleNode("CharCode").Text = curCode Then
            rateStr = node.SelectSingleNode("Value").Text
            nominalStr = node.SelectSingleNode("Nominal").Text
            On Error Resume Next
            rateVal = CDbl(rateStr)
            nominalVal = CDbl(nominalStr)
            On Error GoTo 0
            If nominalVal <> 0 Then rateVal = rateVal / nominalVal

            ws.Range("C2").Value = Round(rateVal, 4)
            ws.Range("C2").NumberFormat = "0.0000"
            Exit Sub
        End If
    Next node
End Sub

Основной шаг здесь – метод getElementsByTagName("Valute") – возвращает список всех валют в XML.

Гайд по запуску:

1. Alt + F11 — откроется редактор VBA;

2. Выбрать Insert → Module;

3. Вставить в форму код из статьи;

4. Закрыть форму разработчика, вернуться к странице Excel;

5. Alt + F8 → выбрать GetExchangeRate → нажать Выполнить;

6. Выпить победный кофе☕️

Если макросы отключены — включить их просто — Файл → Параметры → Центр управления безопасностью → Настройки макросов.

Теперь при выборе валюты в выпадающем списке ячейки B2 достаточно запустить макрос (например, на модуль) – и в C2 появляется актуальный курс.

Когда настроил автоматизацию кросс-курса в Excel

Проблема: на разных компьютерах разные результаты

Казалось бы, все готово: можно радоваться жизни и больше не делать лишних телодвижений. Файл протестирован у себя — прекрасно работает: выбираю USD — макрос мгновенно подставляет, скажем, 74,15 (или 74.15, в зависимости от настроек) в ячейку; выбираю EUR — получаю текущий курс евро. Чудеса! Можно отправлять файл знакомым. Но, как часто бывает, сразу радоваться преждевременно.

Стоило запустить эту же таблицу на другом компьютере, как начались вопросы. У одного знакомого значение курса стало отображаться без дробной части, например вместо 74,15 он увидел 74,00. У другого и вовсе макрос вернул ошибку или ноль, хотя у меня всё работало. Классика.

На моей машине все нормально работает!

Мы попытались разобраться: неужели где-то в коде ошибка, которая странным образом проявляется на одних ПК и не проявляется на других? 🤔 Отладка показала, что сбой происходит именно на этапе преобразования строки в число (фрагмент с CDbl(rateStr)). Либо возвращается только целая часть числа, либо конвертация не проходит и выходит 0. Но почему? Ведь запрос отработал, XML получен, нужный узел найден – строка выглядит как число, например "74.15" или "74,15". Почему Excel не хочет её понимать?

Поиск причины: региональные настройки Excel

Немного погуглив и сравнив настройки проблемных компьютеров, я обнаружил разгадку. Дело в том, что на разных машинах стояли разные региональные настройки Windows (и Excel, соответственно). У меня Windows настроен на русский язык, и Excel ожидает, что дробная часть чисел отделяется запятой (например, 74,15). У знакомого же система на английском, и Excel там принимает дроби через точку (74.15). Вот он, коварный момент!

Когда мой макрос парсил XML, он получал, например, строку "74,15". На русском Excel все отлично: CDbl("74,15") выдает число 74.15, как и задумывалось. А вот на англоязычном Excel такая запись воспринимается неправильно. Функция CDbl ориентируется на текущий системный формат: она встретила запятую и решила, что это разделитель тысяч, а не дробной части, или вообще не поняла формат. В результате получилась каша: либо 74,15 превратилось в 7415,00 (это, к счастью, маловероятно в данном случае), либо просто не сконвертировалось. Аналогично, если бы пришла строка "74.15" в русский Excel, то запросCDbl вернул бы 74 без дробной части, считав точку разделителем тысяч (или обрезал на ней ввод).

Так и вышло: на одних ПК курс отображался без копеек (дробная часть отбрасывалась на неправильном символе), а на других макрос вообще не мог преобразовать строку в число и возвращал 0. Получается, проблема не в коде как таковом, а в локали. Excel просто не понял число в нужном формате по запросу.

Если задуматься, это логично: разные страны – разные стандарты записи чисел. И наш отечественный код должен был бы это учитывать. Просто на этапе разработки я этого не учёл, потому что у меня-то всё работало, настроен на России. 🙃

Когда обвиняешь VBA во всех бедах, хотя причина в настройках ОС

Решение: делаем код независимым от локали

Разобравшись в причине, я быстро понял, как исправить ситуацию. Нужно унифицировать формат числа перед конвертацией. То есть, получить строку с курсом и заменить в ней неправильный разделитель на правильный в зависимости от текущей локали.

В VBA есть удобное свойство Application.DecimalSeparator – оно возвращает символ дробной части, используемый в текущей локали Excel (точка или запятая). Логика фикса такая:

• Если моя локаль использует "." (точку) как разделитель, значит строка из ЦБ скорее всего с запятой (например, "74,15"). Мне надо заменить запятые на точки.

• Если локаль – "," (запятая), то, наоборот, в строке могут быть точки (например, некоторые источники могут вернуть "74.15"). Заменяем точки на запятые.

В случае с API ЦБ РФ конкретно, как мы выяснили, значения в XML приходят с запятой (русский формат). Поэтому проблемы возникали у тех, у кого локаль ожидает точку. Но на всякий случай я сделал код универсальным в обе стороны – мало ли завтра формат выдачи изменится или данные будут браться из другого источника.

Так, я внес изменения в макрос. По сути, добавился блок:

If Application.DecimalSeparator = "." Then
    rateStr = Replace(rateStr, ",", ".")
Else
    rateStr = Replace(rateStr, ".", ",")
End If

Функция Replace проходит по строке и заменяет все вхождения одного символа на другой. Таким образом, после этого блока переменная rateStr содержит число в формате текущей локали. Например:

• В английской системе: было "74,15", станет "74.15".

• В русской системе: было "74.15", станет "74,15".

• Если вдруг строка и так в нужном формате, то замена просто ничего не найдет и оставит всё как есть.

После этого CDbl(rateStr) корректно отдает числовое значение по запросу — 74.15. Бинго! 🎉

Я протестировал обновленный код на нескольких компьютерах с разными настройками – больше никакой путаницы с дробями. Excel наконец-то “понял референс” и стал правильно интерпретировать числа независимо от точки или запятой.

Конечно, Excel теперь все понимает, но ты так больше не шути

Финал: универсальный VBA-код и выводы

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

1. Парсинг XML через VBA – позволил автоматически тянуть актуальные курсы прямо в таблицу. Использована стандартная библиотека для винды (MSXML), без сторонних надстроек;

2. Выпадающий список для выбора валюты – улучшено удобство использования файла и исключен ошибочный ввод валюты. Пользователь просто выбирает код из списка;

3. Учет региональных настроек – добавлена обработка различий в формате чисел (точка vs запятая). Именно это спасает от ошибок на компьютерах с другими языковыми настройками;

4. Проверка номинала валюты – не забываем, что курс может даваться за 10 или 100 единиц валюты. Наш код делит на номинал, получая цену за 1 единицу, и это универсальный подход.

5. Форматирование вывода – по желанию можно сразу форматировать ячейку с курсом (например, 4 знака после запятой), чтобы данные выглядели аккуратно и привычно.

В итоге, Excel сам делает то, что раньше приходилось делать вручную. Я сэкономил время и нервы, а заодно получил опыт, как нюансы локализации могут влиять на работу кода. Возьмите на заметку этот подход – он может пригодиться в любых VBA-проектах, где приходится иметь дело с числами из внешних источников.

Ну а мне осталось только нажать заветную кнопку и смотреть, как Excel и VBA делают всю работу за меня. И знаешь, это ни с чем не сравнимое удовольствие – наблюдать, как компьютер решает задачу, а ты в это время попиваешь кофе. 😉