Актуальные курсы валют в Excel: Power Query, WEBSERVICE и валютный API

Каждое руководство о том, как получить актуальные курсы валют в Excel, обещает одно и то же, а по-тихому выдаёт совсем другое. Вы проходите все шаги, в ячейке появляется число, и выглядит оно как живое. Обычно это не так. Это либо справочный курс, обновляемый раз в сутки, либо котировка с задержкой, либо HTML-таблица, снятая парсером, которая при следующем обновлении молча вернёт не ту валюту.

В этом руководстве разобраны четыре метода, которые действительно работают: встроенный тип данных Валюты, функция WEBSERVICE, Power Query и переиспользуемая LAMBDA. И, что важнее, здесь сказано, какой из них запустится именно в вашей версии Excel, насколько свежие данные каждый из них реально даёт и в каких местах каждый выдаёт неверные числа, не выбрасывая при этом ошибку.

Все формулы и запросы ниже написаны под задокументированные форматы ответов Finexly API и проверены на корректность перед публикацией.

Четыре метода и какой из них вам реально доступен

Валютные возможности Excel необычно сильно зависят от версии. Половина статей на эту тему советует метод, которого на машине читателя попросту нет. Начните отсюда.

1. Если у вас Mac, веб-коннектора Power Query нет в списке доступных источников, а WEBSERVICE не работает вообще. Microsoft прямо пишет, что WEBSERVICE «может отображаться в галерее функций Excel для Mac, но опирается на возможности операционной системы Windows, поэтому на Mac результатов не возвращает». Пользователи Mac на Microsoft 365 реалистично ограничены типом данных «Валюты».
2. Если у вас бессрочная лицензия Excel 2019, 2021 или 2024, тип данных «Валюты» вам недоступен — связанные типы данных есть только в Microsoft 365 и Excel в вебе. Ваш путь — WEBSERVICE или Power Query.

Метод 1: встроенный тип данных «Валюты»

Это вариант без единой строчки кода, и именно с него начинает большинство статей.

1. Введите валютные пары в столбец, используя коды ISO 4217, разделённые слэшем или двоеточием: USD/EUR, GBP:JPY. (Если не уверены, какой код нужен, наш справочник ISO 4217 перечисляет их все.)
2. Выделите ячейки и перейдите в Данные → Типы данных → Валюты.
3. Каждая ячейка превратится в связанную запись с небольшим значком валюты. Щёлкните по значку или нажмите кнопку Вставить данные и выберите Price, чтобы вывести курс в соседнюю ячейку.
4. Обновляйте через Данные → Обновить всё.

Что на самом деле обещает Microsoft

Эту часть статьи обычно пропускают. В собственной документации Microsoft на странице о валютах стоит предупреждение: «Информация о валютах предоставляется "как есть" и может быть устаревшей. Поэтому эти данные не следует использовать для торговли или в качестве рекомендаций». Интервал обновления для валютных пар нигде не опубликован. Финансовые данные приходят из LSEG Data & Analytics (бывший Refinitiv) и поставляются через Bing.

Ещё три ограничения, которые важны, если вы строите что-то настоящее:

• Доступность зависит от тенанта. Microsoft указывает, что «валютные пары доступны только учётным записям Microsoft 365 (клиентам Worldwide Multi-Tenant)». Если ваша организация работает в суверенном или государственном облаке, кнопка «Валюты» будет неактивна, и никакая диагностика этого не изменит.
• Серверного обновления не существует. Связанные типы данных обновляются только пока книга открыта в Excel. Автоматическое обновление раз в пять минут существует, но сейчас доступно только участникам программы Insiders, и Microsoft отмечает, что «некоторые связанные типы данных можно обновить только вручную».
• Пара непрозрачна. Вы получаете число. Вы не видите ни отметку времени, ни источник, ни спред. Для выставления счетов или отчётности нужен проверяемый курс с привязанной датой — а это совсем другая задача.

Пользуйтесь этим методом, чтобы прикинуть курс на глаз. Не используйте его как вход для того, что вам потом придётся защищать.

Метод 2: WEBSERVICE плюс валютный API

WEBSERVICE(url) выполняет HTTP GET и возвращает тело ответа в виде текста. У неё ровно один аргумент — URL. Именно этот факт определяет всё остальное в этом методе.

Почему API-ключ приходится класть в URL

У WEBSERVICE нет параметра для заголовков, поэтому передать Authorization: Bearer … невозможно. Любой API, который вы вызываете из WEBSERVICE, обязан принимать ключ как query-параметр. Finexly поддерживает оба варианта:

# Recommended everywhere else — but impossible from WEBSERVICE
curl -H "Authorization: Bearer YOUR_API_KEY" \
  "https://api.finexly.com/v1/rate?from=USD&to=EUR"

# The query-parameter form, which is what WEBSERVICE needs
https://api.finexly.com/v1/rate?from=USD&to=EUR&api_key=YOUR_API_KEY

Честно оцените компромисс. Ключи в URL попадают в логи доступа сервера и могут утечь через HTTP-заголовок referrer, а сам ключ хранится в книге открытым текстом — значит, ключ есть у каждого, кому вы отправите файл по почте. Заведите отдельный ключ с низкой квотой для таблиц и никогда не рассылайте книгу с WEBSERVICE, в которой лежит продакшен-ключ.

Рабочая формула

Положите URL в A1, а сырой ответ — в A2:

A1:  ="https://api.finexly.com/v1/rate?from="&B1&"&to="&C1&"&api_key="&$D$1
A2:  =WEBSERVICE(A1)

Теперь A2 содержит буквальный текст ответа:

{"pair":"USD_EUR","rate":0.9215}

Осталось извлечь число. Почти все конкурирующие руководства делают это по позиции — INDEX(TEXTSPLIT(...), 1, 6) — и такой подход молча ломается, как только API добавит поле или вы смените эндпоинт. Привязывайтесь к имени ключа:

=LET(
   raw,  A2,
   p,    FIND("""rate"":", raw) + 7,
   e,    MIN(IFERROR(FIND(",", raw, p), 9999), IFERROR(FIND("}", raw, p), 9999)),
   IFERROR(VALUE(MID(raw, p, e - p)), NA())
 )

Формула находит подстроку "rate":, перешагивает её семь символов и читает до того, что встретится раньше, — запятой или закрывающей фигурной скобки. Она возвращает 0,9215 и из ответа /v1/rate с двумя полями, и из ответа /v1/convert-amount с четырьмя, а при сбое вызова отдаёт #N/A вместо неверного числа. LET требует Excel 2021 или Microsoft 365; в Excel 2016/2019 те же вызовы FIND можно вписать напрямую ценой читаемости.

Ловушка волатильности

WEBSERVICE — волатильная функция. Она пересчитывается практически при любом изменении на листе, а не только по нажатию F9. Соберите конвертер на 40 строк с одним WEBSERVICE в каждой — и одно нажатие клавиши отправит 40 HTTP-запросов.

На бесплатном тарифе в 1 000 запросов в месяц это примерно 25 нажатий до отсечки. Есть две линии обороны: держать WEBSERVICE в единственной ячейке и ссылаться на неё отовсюду либо переключить вычисления в ручной режим (Формулы → Параметры вычислений → Вручную) на время сборки. А ещё лучше — использовать Power Query, который не волатилен.

Метод 3: Power Query — тот, что масштабируется

Power Query — единственный метод здесь, который умеет отправлять заголовок Authorization, обновляться по расписанию и переваривать таблицу на пятьдесят тысяч строк, не плавясь. При этом каждое руководство показывает его исключительно через клики в интерфейсе, так что настоящий код на M найти негде. Вот он.

Откройте Данные → Получить данные → Запустить редактор Power Query, затем Создать источник → Пустой запрос, затем Главная → Расширенный редактор и вставьте:

let
    ApiKey = "YOUR_API_KEY",
    Pairs  = "USD_EUR,USD_GBP,USD_JPY,USD_CHF",
    Source = Json.Document(
        Web.Contents(
            "https://api.finexly.com",
            [
                RelativePath = "v1/convert",
                Query        = [ q = Pairs ],
                Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
            ]
        )
    ),
    ToTable  = Record.ToTable(Source),
    Expanded = Table.ExpandRecordColumn(ToTable, "Value", {"rate"}, {"rate"}),
    Split    = Table.SplitColumn(
                   Expanded, "Name",
                   Splitter.SplitTextByDelimiter("_", QuoteStyle.None),
                   {"Base", "Quote"}
               ),
    Typed    = Table.TransformColumnTypes(
                   Split,
                   {{"Base", type text}, {"Quote", type text}, {"rate", type number}}
               )
in
    Typed

Назовите запрос FxRates и выгрузите на лист. Вы получите чистую таблицу из трёх столбцов: Base, Quote, rate.

Три детали в этом запросе делают настоящую работу, и всех трёх нет ни в одном разборе через интерфейс:

• Headers держит ключ вне URL. Это главное преимущество Power Query перед WEBSERVICE с точки зрения безопасности.
• RelativePath и Query вынесены из базового URL намеренно. Если склеить весь URL в одну строку, Power Query сочтёт источник динамическим, а такой источник нельзя обновлять без участия человека в службе Power BI. Передача пути и параметров по отдельности оставляет источник статическим и обновляемым.
• Один запрос — много пар. /v1/convert возвращает все запрошенные пары за один вызов. Книга, обновляющаяся раз в час в течение восьмичасового дня на протяжении 22 рабочих дней, сделает так 176 запросов в месяц. Тридцать отдельных вызовов /v1/rate по тому же расписанию дали бы 5 280 — впятеро больше бесплатного месячного лимита за идентичные данные.

Переиспользуемая функция курса

Чтобы получать одну пару по требованию, оберните вызов в функцию на M:

let
    FxRate = (baseCode as text, quoteCode as text) as number =>
        let
            ApiKey = "YOUR_API_KEY",
            Source = Json.Document(
                Web.Contents(
                    "https://api.finexly.com",
                    [
                        RelativePath = "v1/rate",
                        Query        = [ from = baseCode, to = quoteCode ],
                        Headers      = [ #"Authorization" = "Bearer " & ApiKey ]
                    ]
                )
            )
        in
            Source[rate]
in
    FxRate

Вызывайте её из любого другого запроса как FxRate("USD", "EUR").

Как конвертировать 50 000 строк без 50 000 просмотров

Протягивать XLOOKUP по большой таблице медленно и ненадёжно, а если результат запроса при обновлении меняет размер, ссылки на весь столбец вроде F:F тихо съедут. Делайте соединение внутри Power Query:

1. Загрузите таблицу транзакций в Power Query.
2. Главная → Объединить запросы, сопоставив столбец Currency с FxRates[Quote].
3. Разверните объединённый столбец и оставьте rate.
4. Добавление столбца → Настраиваемый столбец с формулой конвертации.

Это одно соединение по всей таблице, вычисляемое один раз за обновление, и ни одной волатильной формулы во всей книге.

Метод 4: переиспользуемая =FXRATE() на LAMBDA

Если книгой пользуются люди, привыкшие к таблицам, а не авторы запросов, спрячьте всё вышеописанное за одной функцией. Зайдите в Формулы → Диспетчер имён → Создать, назовите её FXRATE и в поле Диапазон укажите:

=LAMBDA(from_code, to_code, key,
   LET(
     url,  "https://api.finexly.com/v1/rate?from=" & from_code &
           "&to=" & to_code & "&api_key=" & key,
     raw,  WEBSERVICE(url),
     p,    FIND("""rate"":", raw) + 7,
     e,    MIN(IFERROR(FIND(",", raw, p), 9999), IFERROR(FIND("}", raw, p), 9999)),
     IFERROR(VALUE(MID(raw, p, e - p)), NA())
   )
 )

Теперь любой может написать =FXRATE("USD","EUR",$D$1) и получить 0,9215. Сохраните ключ один раз в D1, защитите эту ячейку — и остальная книга к нему больше не притрагивается. Учтите, что этот вариант наследует все ограничения WEBSERVICE — только Windows, волатильность, ключ в URL, — так что это выигрыш в удобстве, а не в безопасности или масштабе.

Пять способов молча испортить валютную конвертацию в Excel

Это ошибки в результатах, а не сообщения об ошибках. Ничего не краснеет. Просто числа не те, что вы думаете.

1. Умножение вместо деления

Это самая частая ошибка в опубликованных руководствах по валютам в Excel, и стоит точно оценить ущерб. Курс USD_EUR, равный 0,9215, конвертирует USD в EUR. Чтобы пойти в обратную сторону — из EUR в USD, — нужно делить.

• Правильно: 100 / 0,9215 = 108,52 USD
• Неправильно: 100 × 0,9215 = 92,15 USD

Результат отличается на квадрат курса: 0,9215² = 0,8492 — занижение на 15,1 %, которое в отчёте выглядит совершенно правдоподобно. Пропишите направление в заголовках столбцов (курс USD→EUR, а не «Курс») — и ошибиться станет трудно.

2. Разбор JSON по позиции

INDEX(TEXTSPLIT(response, ":", "}"), 1, 6) работает ровно до момента, когда API добавит поле, — после чего индекс 6 вернёт другое значение и никакой ошибки не будет. Привязывайтесь к имени ключа, как в формуле с LET выше, или используйте Json.Document из Power Query, который разбирает JSON по-настоящему.

3. Округление всего до двух знаков

Excel по умолчанию берёт два знака после запятой; ISO 4217 — нет. У JPY, KRW и VND ноль знаков в дробной части — ¥250.75 не является допустимой суммой. У BHD, KWD, OMR и TND их три. Округлить сумму в JPY до двух знаков, а потом округлить ещё раз ниже по цепочке — верный способ получить сверку, которая не сходится на несколько единиц. Полный набор правил разобран в нашем руководстве по округлению валют и знакам после запятой.

4. Принимать средний рыночный курс за тот, по которому с вас спишут

Любой курс в этой статье — и в любом бесплатном API — это средний рыночный курс (mid-market): середина между bid и ask. Это не то, что даст вам банк, платёжная система или процессинг. Реальные спреды идут примерно от 0,5 % до нескольких процентов. Если вы формируете цену или выставляете клиенту котировку, добавьте явный столбец наценки вместо того, чтобы выдавать средний рыночный курс за расчётный. Наш разбор о том, откуда валютные API берут данные, объясняет, что эти потоки отражают, а что — нет.

5. Позволять живой книге переписывать прошлогодние числа

Эта ошибка тяжёлая и почти нигде не упоминается. Если ячейки с курсами обновляются вживую, то каждая историческая строка в книге переоценивается по сегодняшнему курсу при каждом открытии файла. Мартовская выручка меняется в апреле. Ваша сверка никогда не сойдётся дважды.

Как только транзакция совершилась, её курс становится фактом о конкретной дате, а не живым значением. Зафиксируйте его: конвертируйте один раз, вставьте результат как значение рядом с использованным курсом и датой, а живой запрос оставьте только для новых строк. Ту же дисциплину системы мультивалютного выставления счетов и управления расходами обеспечивают на уровне базы данных.

Бонус: кросс-курсы

Если ваш API отдаёт всё относительно одной базовой валюты, конвертация EUR→GBP означает деление одного курса на другой:

EUR→GBP  =  USD_GBP / USD_EUR  =  0.7892 / 0.9215  =  0.856430
1,000 EUR  =  856.43 GBP

Округляйте только на последнем шаге. Округление промежуточного кросс-курса до четырёх знаков с последующим умножением вносит ошибку, которая накапливается на большой таблице. Подробнее — в нашем руководстве по кросс-курсам валют.

Автообновление, лимиты запросов и жизнь на бесплатном тарифе

Чтобы таблица курсов в Power Query обновлялась сама: щёлкните запрос правой кнопкой в Запросы и подключения → Свойства, затем включите Обновить данные при открытии файла и/или Обновлять каждые N мин.

О двух вещах вас никто не предупреждает:

• Обновление при открытии вызывает предупреждение безопасности Excel о внешних данных. Пользователи увидят жёлтую полосу, и пока они не нажмут «Включить», «автоматическое» обновление не сделает ничего. Добавление папки в Центр управления безопасностью → Надёжные расположения снимает вопрос.
• Обновляться каждые 15 минут против курса, который публикуется раз в день, — чистая трата. Соотнесите интервал с тем, как часто источник действительно меняется. Для большинства задач по счетам и отчётности достаточно одного обновления при открытии.

Следите за расходом по заголовкам ответа, которые возвращает Finexly: X-RateLimit-Limit, X-RateLimit-Used и X-RateLimit-Units. Бесплатный план даёт 1 000 запросов в месяц без привязки карты — комфортно для книги, обновляемой при открытии, и впритык для волатильной сетки на WEBSERVICE. Если вы стабильно упираетесь в потолок, решение почти всегда в пакетировании через /v1/convert, а не в переходе на платный тариф. Наше руководство по кэшированию и обработке ошибок разбирает те же принципы вне Excel.

Устранение неполадок

Часто задаваемые вопросы

Может ли Excel получать курсы валют в реальном времени? По-настоящему — нет. Тип данных «Валюты» прямо задокументирован как данные с задержкой без публикуемого интервала, а большинство бесплатных API публикуют курсы раз в рабочий день. Что вы можете получить — это текущий курс, обновляемый по требованию из живого источника, и именно это даёт валютный API. Для настоящего потикового FX нужен стриминговый фид, а не электронная таблица.

Как получить курсы валют в Excel без API-ключа? Тип данных «Валюты» ключа не требует, но нуждается в Microsoft 365 и не даёт ни отметки времени, ни аудиторского следа. Для всего, что придётся потом сверять, API с ключом — ответ лучше, а тариф бесплатного валютного API не стоит ничего.

Почему WEBSERVICE не работает в Excel для Mac? В документации Microsoft сказано, что WEBSERVICE опирается на возможности операционной системы Windows, поэтому на Mac она не возвращает результата, хотя и присутствует в галерее функций. То же касается FILTERXML. Пользователям Mac стоит по возможности брать Power Query либо тип данных «Валюты».

Как получить в Excel исторические курсы на конкретную дату? Постройте таблицу курсов с одной строкой на дату, наполнив её из источника исторических курсов, а затем ищите по ней через XLOOKUP с режимом сопоставления -1, чтобы выходные и праздники откатывались к последнему доступному рабочему дню. Никогда не переоценивайте прошлые транзакции по сегодняшнему курсу.

Можно ли конвертировать всю таблицу транзакций сразу через Power Query? Да, и именно так и следует. Объедините запрос транзакций с запросом курсов по коду валюты, разверните столбец с курсом и добавьте пользовательский столбец с конвертированной суммой. Одно соединение заменяет десятки тысяч волатильных формул и обновляется за один проход.

Какой метод выбрать? Одна пара, эпизодическая проверка — тип данных «Валюты». Прототип на Windows — WEBSERVICE. Всё, от чего зависят другие люди, — Power Query, всегда.

Готовы завести в своей таблице актуальные курсы как положено? Получите бесплатный API-ключ Finexly — карта не нужна. Вы получаете 1 000 запросов в месяц по 170+ валютам, query-параметр api_key, благодаря которому вообще возможен WEBSERVICE, и аутентификацию через заголовки для Power Query. Полный справочник эндпоинтов — в документации API, а если вы всё ещё выбираете, посмотрите сравнение валютных API.