Данные Мосбиржи в гугл таблицах

21 июня 2021     920   

Половину июня (с начала месяца до 20 июня 2021 года) в гугл таблицах было невозможно получать данные российских акций через встроенную функцию.

При попытке получить котировки с префиксом MCX, например для Сбербанка, формулой из гугл таблиц =GOOGLEFINANCE("MCX:SBER") возвращался результат #N/A.

Сервис починили и данные снова доступны, но можно сильно расширить диапазон получаемых данных, если использовать API Московской биржи.

Копирование формул из таблицы-примера в ваши собственные таблицы

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

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

Моя таблица с примером получения данных с Московской биржи
Моя таблица с примером получения данных с Московской биржи

Я использую регион Соединенные Штаты, а если по умолчанию ваш регион Россия, то формулы корректно НЕ копируются!

Вот подробная инструкция как проверить региональные настройки конкретной таблицы:

  • Откройте файл в Google Таблицах на компьютере.
  • Нажмите Файл затем Настройки таблицы.
  • Выберите нужные варианты в разделах "Региональные настройки".
  • Нажмите Сохранить настройки.

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

Получение названий акций и облигаций

Гугл таблица с примерами автоматического получения имени для разных классов активов
Гугл таблица с примерами автоматического получения имени для разных классов активов

Можно получать названия акций и облигаций используя сервера Московской Биржи. Сама формула при этом выглядит следующим образом:

=IMPORTxml(
    "https://iss.moex.com/iss/engines/stock/markets/" &      IFS(
                 or(
            B3 = "TQOB",
            B3 = "EQOB",
            B3 = "TQOD",
            B3 = "TQCB",
            B3 = "EQQI",
            B3 = "TQIR"
        ),
        "bonds",
                 or(
            B3 = "TQTF",
            B3 = "TQBR",
            B3 = "SNDX",
            B3 = "TQIF"
        ),
        "shares"
    )  & "/boards/" & B3 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME",
      "//row[@SECID='" & A3 & "']/@SECNAME"
)

Получение цен акций и облигаций

Гугл таблица с примерами автоматического получения цен акций и облигаций
Гугл таблица с примерами автоматического получения цен акций и облигаций

Можно получать цены акций и облигаций используя сервера Московской Биржи. Сама формула при этом выглядит следующим образом:

=IMPORTxml(
    "https://iss.moex.com/iss/engines/stock/markets/" &      IFS(
                 or(
            B10 = "TQOB",
            B10 = "EQOB",
            B10 = "TQOD",
            B10 = "TQCB",
            B10 = "EQQI",
            B10 = "TQIR"
        ),
        "bonds",
                 or(
            B10 = "TQTF",
            B10 = "TQBR",
            B10 = "SNDX",
            B10 = "TQIF"
        ),
        "shares"
    )  & "/boards/" & B10 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE",
      "//row[@SECID='" & A10 & "']/@PREVADMITTEDQUOTE"
)

Получение даты и значения дивиденда для акций

Гугл таблица с примерами автоматического получения дат и значений дивидендов для акций
Гугл таблица с примерами автоматического получения дат и значений дивидендов для акций

Можно получать даты и значения дивидендов для акций, используя сервера Московской Биржи. У гугл финанса такого функционала никогда не было для российских акций. Сама формула при этом выглядит следующим образом:

=iferror(
     INDEX(
         IMPORTxml(
            "http://iss.moex.com/iss/securities/" & A22 & "/dividends.xml?iss.meta=off",
            "//row[@secid='" & A22 & "']/@value"
        )  ,
         ROWS(
            IMPORTxml(
                "http://iss.moex.com/iss/securities/" & A22 & "/dividends.xml?iss.meta=off",
                "//row[@secid='" & A22 & "']/@value"
            )
        )  ,
        1
    )  ,
    "нет"
)

Получение даты купона и значения для облигаций

Гугл таблица с примерами автоматического получения дат купонов и значений для облигаций<br>
Гугл таблица с примерами автоматического получения дат купонов и значений для облигаций

Можно получать даты и значения купонов для облигаций, используя сервера Московской Биржи. У гугл финанса такого функционала никогда не было для российских облигаций и вообще облигаций в сервисе нет. Сама формула при этом выглядит следующим образом:

=IMPORTxml(
    "https://iss.moex.com/iss/engines/stock/markets/" &      IFS(
                 or(
            B12 = "TQOB",
            B12 = "EQOB",
            B12 = "TQOD",
            B12 = "TQCB",
            B12 = "EQQI",
            B12 = "TQIR"
        ),
        "bonds",
                 or(
            B12 = "TQTF",
            B12 = "TQBR",
            B12 = "SNDX",
            B12 = "TQIF"
        ),
        "shares"
    )  & "/boards/" & B12 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,NEXTCOUPON,COUPONVALUE",
      "//row[@SECID='" & A17 & "']/@COUPONVALUE"
)

Получение даты оферты

Гугл таблица с примерами автоматического получения дат оферт для облигаций
Гугл таблица с примерами автоматического получения дат оферт для облигаций

Можно получать оферты облигаций, используя сервера Московской Биржи. У гугл финанса такого функционала никогда не было для российских облигаций и вообще облигаций в сервисе нет. Сама формула при этом выглядит следующим образом:

=IFNA(
     IMPORTxml(
        "https://iss.moex.com/iss/engines/stock/markets/" &          IFS(
                         or(
                B27 = "TQOB",
                B27 = "EQOB",
                B27 = "TQOD",
                B27 = "TQCB",
                B27 = "EQQI",
                B27 = "TQIR"
            ),
            "bonds",
                         or(
                B27 = "TQTF",
                B27 = "TQBR",
                B27 = "SNDX",
                B27 = "TQIF"
            ),
            "shares"
        )  & "/boards/" & B27 & "/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,OFFERDATE",
          "//row[@SECID='" & A27 & "']/@OFFERDATE"
    )  ,
    "нет"
)

Источник данных

Если вы хотите разобраться во всех нюансах работы - откуда берутся данные, то вы, также как и я можете обратиться к официальной документации к информационно-статистическому серверу Московской Биржи (ИСС / ISS). Правда, изучая этот документ, вы можете обнаружить что большая часть интересных функций, приведенная в этой статье, в документе никак не отображена.

Итоги

Можно легко расширить список получаемых в гугл таблицу параметров за счёт Московской Биржи.

Эти формулы работают только за счет API Московской биржи, с которой я никак не связан. Использую ИСС Мосбиржи только в личных информационных интересах.

Автор: Михаил Шардин,

21 июня 2021 г.

11 комментариев 👇

Дополню формулой для получения грязной цены любой облигации (в ячейке T3 тут указывается идентификатор бумаги, например, SU52001RMFS3 для ОФЗ-ИН 52001), может кому пригодится:

=IMPORTXML("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,LOTVALUE,PREVADMITTEDQUOTE,ACCRUEDINT", concatenate("//row[@SECID='",T3,"']/@*"))

Из трех полученных чисел можно получить текущую цену в валюте: LOTVALUE (номинал) * PREVADMITTEDQUOTE (цена) +ACCRUEDINT (НКД).

  Развернуть 1 комментарий

@Dmitry, 🙏🏻

  Развернуть 1 комментарий

Супер! Я для цены у себя использую LAST, а если он пуст - то OFFER.

Расскажите, почему правильно PREVADMITTEDQUOTE? Значения я естественно выбирал пальцем в потолок 🙃

  Развернуть 1 комментарий

@tiraelsedai, PREVADMITTEDQUOTE - признаваемая котировка предыдущего дня.
Если торги ещё не начались или время нерабочее, то LAST - пустой.

  Развернуть 1 комментарий

Как инвестор в акции МосБиржи, прошу в таких статьях упоминать обязательное получение разрешения или заключение договора на использование коммерческих данных МосБиржи. (https://www.moex.com/ru/products/personal)

Иначе такой контент можно расценивать как пиратский.

  Развернуть 1 комментарий

@Whispered, на RUSETFS вы платите за подписку?

  Развернуть 1 комментарий

@empenoso, да, в обязательном порядке.

  Развернуть 1 комментарий

@Whispered, а почему вы думаете что частное лицо должно заключать договор, если использует данные только в личных целях, без публичной составляющей? И тем более не на сервере.

  Развернуть 1 комментарий

@empenoso, прямые подробности на сей конкретный вопрос по ссылке выше. =)

  Развернуть 1 комментарий

@Whispered, по ссылке например написано "Ход торгов в реальном времени", тогда как без авторизации iss отдает только delayed. Этот пример наталкивает на мысль, что вероятно ваша трактовка излишне суровая.

  Развернуть 1 комментарий

@Disputy, поддерживаю 👍

  Развернуть 1 комментарий

😎

Читать можно всем, но комментирование доступно только участникам Клуба.

Что вообще здесь происходит?


Войти