Половину июня (с начала месяца до 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 г.
Дополню формулой для получения грязной цены любой облигации (в ячейке T3 тут указывается идентификатор бумаги, например, SU52001RMFS3 для ОФЗ-ИН 52001), может кому пригодится:
Из трех полученных чисел можно получить текущую цену в валюте:
LOTVALUE (номинал) * PREVADMITTEDQUOTE (цена) +ACCRUEDINT (НКД)
.Супер! Я для цены у себя использую LAST, а если он пуст - то OFFER.
Расскажите, почему правильно PREVADMITTEDQUOTE? Значения я естественно выбирал пальцем в потолок 🙃
Как инвестор в акции МосБиржи, прошу в таких статьях упоминать обязательное получение разрешения или заключение договора на использование коммерческих данных МосБиржи. (https://www.moex.com/ru/products/personal)
Иначе такой контент можно расценивать как пиратский.