1. Основы работы с API Московской биржи
Основы работы с API Московской биржи
Представьте, что вы пришли в ресторан. Вы — клиент, который хочет получить свое блюдо (данные о котировках). Кухня ресторана — это сервер Московской биржи, где хранятся все актуальные цены, объемы торгов и исторические данные. Вы не можете просто зайти на кухню и взять то, что вам нужно. Вам нужен посредник, который примет ваш заказ, передаст его поварам и принесет готовое блюдо. Этим посредником выступает API.
> API (Application Programming Interface) — это набор правил и механизмов, с помощью которых одна программа может общаться с другой.
В нашем случае Microsoft Excel будет отправлять запросы через API к серверам Московской биржи, а биржа будет возвращать в ответ структурированные текстовые данные, которые Excel автоматически распределит по ячейкам.
Что такое MOEX ISS
Московская биржа предоставляет открытый программный интерфейс, который называется ИСС Московской биржи (MOEX Informational & Statistical Server, или просто ISS). Это официальный и абсолютно легальный способ получать биржевую информацию без необходимости покупать платные терминалы или оформлять подписки.
Главное преимущество ISS MOEX заключается в том, что базовые данные предоставляются бесплатно и без регистрации. Единственный нюанс — котировки транслируются с задержкой в 15 минут, что является стандартом для бесплатных биржевых потоков во всем мире. Для долгосрочного инвестора или человека, который хочет просто отслеживать свой портфель в конце дня, эта задержка не играет никакой роли.
!Схема взаимодействия Microsoft Excel и сервера Московской биржи
Две главные функции Excel
Для того чтобы связать вашу таблицу с сервером биржи, нам не потребуются сложные макросы или навыки программирования на Python. В современных версиях Microsoft Excel для Windows (начиная с версии 2013 года) встроены две мощные сетевые функции.
1. Функция ВЕБСЛУЖБА
Функция ВЕБСЛУЖБА (WEBSERVICE в английской версии) выполняет роль курьера. Вы даете ей точный веб-адрес (URL-ссылку), она отправляется по этому адресу в интернет, забирает весь текст, который там находится, и приносит его в ячейку.
Если вы просто вставите ссылку на Московскую биржу в эту функцию, ячейка заполнится огромным массивом непонятного текста со множеством скобок. Этот текст отформатирован в стандарте XML (eXtensible Markup Language — расширяемый язык разметки). XML похож на матрешку: данные в нем хранятся внутри вложенных друг в друга тегов.
2. Функция ФИЛЬТР.XML
Поскольку нам не нужен весь массив данных, а требуется только одна конкретная цифра (например, текущая цена), в дело вступает функция ФИЛЬТР.XML (FILTERXML).
Она берет ту самую «матрешку» (XML-текст), которую принесла первая функция, и с помощью специального языка запросов достает из нее строго определенное значение. Этот язык запросов называется XPath — он работает как путь к файлу на вашем компьютере, указывая Excel, в какой именно папке (теге) лежит нужная цифра.
Анатомия запроса к Московской бирже
Чтобы курьер принес правильную посылку, ему нужен точный адрес. Серверы Мосбиржи хранят миллионы записей, поэтому наша ссылка-запрос должна быть максимально конкретной.
Базовый адрес для получения данных по конкретной ценной бумаге выглядит так:
https://iss.moex.com/iss/engines/[движок]/markets/[рынок]/boards/[режим_торгов]/securities/[тикер].xml
Давайте разберем переменные в скобках:
Движок (engine*): Глобальный раздел биржи. Для акций и облигаций это всегда stock (фондовый рынок), для валюты — currency.
Рынок (market*): Подраздел. Для акций это shares, для облигаций — bonds.
Режим торгов (board*): Это критически важный параметр. На бирже одна и та же бумага может торговаться в разных режимах (например, крупными блоками для институционалов или обычными лотами для частных инвесторов). Основной режим торгов для российских акций (Т+1) обозначается кодом TQBR.
Тикер (secid*): Уникальный буквенный код актива. Например, Сбербанк — это SBER, Газпром — GAZP.
Чтобы не перегружать Excel лишними данными (историей торгов, описанием компании), к ссылке добавляют специальные параметры фильтрации: ?iss.meta=off&iss.only=marketdata. Это говорит серверу: «Не присылай метаданные, дай только текущие рыночные цифры».
Итоговая идеальная ссылка для получения котировок Сбербанка выглядит так:
https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/SBER.xml?iss.meta=off&iss.only=marketdata
Как читать XML-ответ и писать XPath
Если вы перейдете по собранной ссылке в браузере, вы увидите структуру, похожую на эту (сильно упрощено для понимания):
Здесь мы видим тег row (строка), внутри которого лежат атрибуты — пары «название = значение».
* LAST — цена последней сделки (текущая цена).
* VALTODAY — объем торгов за сегодняшний день в рублях.
* LASTTOPREVPRICE — изменение цены к закрытию предыдущего дня в процентах.
Чтобы извлечь цену (250.50), нам нужно написать путь (XPath) к атрибуту LAST. В синтаксисе XPath двойной слеш // означает «искать везде в документе», а символ @ указывает на то, что мы ищем именно атрибут.
Путь будет таким: //row[@SECID='SBER']/@LAST
Перевод на человеческий язык: «Найди в документе строку (row), у которой атрибут тикера (SECID) равен 'SBER', и выдай мне значение ее атрибута цены (LAST)».
Собираем финальную формулу
Теперь у нас есть все элементы пазла. Мы объединяем две функции Excel в одну конструкцию. Функция ФИЛЬТР.XML оборачивает функцию ВЕБСЛУЖБА.
Формула для получения текущей цены Сбербанка:
=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/SBER.xml?iss.meta=off&iss.only=marketdata"); "//row[@SECID='SBER']/@LAST")
Формула для получения изменения цены за день в процентах:
=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/SBER.xml?iss.meta=off&iss.only=marketdata"); "//row[@SECID='SBER']/@LASTTOPREVPRICE")
Как сделать таблицу динамической
Вставлять длинную ссылку в каждую ячейку вручную — плохая идея. Если у вас в столбце A написаны тикеры (например, в ячейке A2 написано SBER), вы можете «склеить» ссылку прямо внутри формулы с помощью символа амперсанда &.
Динамическая формула будет выглядеть так:
=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/" & A2 & ".xml?iss.meta=off&iss.only=marketdata"); "//row[@SECID='" & A2 & "']/@LAST")
Теперь, если вы протянете эту формулу вниз по столбцу, Excel автоматически подставит тикеры из соседних ячеек, сформирует правильные запросы к Мосбирже и выдаст актуальные цены для всего вашего портфеля.
Важные технические ограничения
При работе с этим методом необходимо учитывать несколько строгих технических нюансов, которые зависят от архитектуры самого Microsoft Excel:
ВЕБСЛУЖБА и ФИЛЬТР.XML используют внутренние компоненты операционной системы Windows для отправки сетевых запросов и парсинга XML. В версиях Excel для компьютеров Mac (Apple) эти функции отсутствуют. Если вы откроете файл на Mac, формулы выдадут ошибку #ИМЯ?.Понимание этих базовых принципов маршрутизации запросов и разбора XML-ответов открывает огромные возможности. Вы больше не зависите от сторонних платных сервисов и можете конструировать аналитические панели любой сложности, опираясь на первичные данные напрямую от торговой площадки.