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

Половину июня (с начала месяца до 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
    )  ,
    "нет"
)

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

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

=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 г.