Учет инвестиций в гугл-таблицах №5 — выгружаем котировки облигаций: ОФЗ, муни, корпораты и евробонды

Продолжаем использовать гугл таблицы для учета инвестиций (начало здесь). Разберем как можно автоматически получать котировки и название облигаций, обращающихся на Московской бирже. На что обратить внимание и некоторые особенности получения данных и учет в гугл таблицах (Google Sheets).

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

Получаем данные по ОФЗ

Формула вызова цены по облигациям выглядит следующим образом:

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

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

  • вместо shares пишем bonds;
  • идентификатор меняем на TQOB;
  • остальное без изменений.

В ячейке (в данном примере - A4) вместо тикера (как у акций), прописываем код ценной бумаги.

На примере ОФЗ с погашением в 2034 году. На сайте Мосбирже ищем карточку нужного инструмента. Смотрим код ценной бумаги (SU26225RMFS1) и идентификатор торгов (TQOB) - ссылка.

Код облигации

Обратите внимание!

Не путать Код ценной бумаги и ISIN код. Для Мосбиржи именно для ОФЗ - это разные значения. По другим бумагами коды могут полностью совпадать.

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

Цены ОФЗ

 

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

Для вызова названия облигаций используем следующую формулу:

=IMPORTxml("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,COUPONPERCENT,MATDATE", concatenate("//row[@SECID='",A4,"']/@SECNAME"))

В итоге таблица принимает более человеческий вид.

Автоматическое получения названия ОФЗ
Учет ОФЗ в Google Sheets

Статьи в тему:

Корпоративные, муниципальные и еврооблигации

Если применить описанную выше формулу к другим бумагам (не ОФЗ), таблица будет выдавать ошибку. В чем дело?

В идентификаторе режима торгов. У ОФЗ он - TQOB, у не ОФЗ будет другой.

На примере облигаций Система с погашением в 2028 году. 

Снова ищем страницу инструмента (облигации) на сайте Мосбиржи.

Идентификатор торгов - TQCB.

Обратите внимание: код ценной бумаги совпадает с ISIN.

 

Система корпоративные бумаги

Поэтому формула для получения котировок по корпоративным облигациям будет иметь вид:

=IMPORTxml("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE", concatenate("//row[@SECID='",A7,"']/@PREVADMITTEDQUOTE"))

Для автоматической вставки правильного названия бумаги также меняем идентификатор на TQCB:

=IMPORTxml("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,COUPONPERCENT,MATDATE", concatenate("//row[@SECID='",A7,"']/@SECNAME"))

Идентичный идентификатор торгов (соотвественно  и формулу) будет иметь муниципальные бумаги.


По еврооблигациям (торгующимися в валюте) в формуле используем другой идентификатор.
Для примера возьмем еврооблигации Газпрома в погашением в 2034 году.

Ищем на сайте Мосбиржи карточку инструмента (ссылка).

Узнаем Код - XS0191754729 и Идентификатор - TQOD.

Еврооблигация Газпрома

 

Соответственно формулы вызова котировок и названия у еврооблигаций будут иметь вид:

Котировки евробондов:

=IMPORTxml("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOD/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE", concatenate("//row[@SECID='",A13,"']/@PREVADMITTEDQUOTE"))

Название еврооблигации:

=IMPORTxml("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOD/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,COUPONPERCENT,MATDATE", concatenate("//row[@SECID='",A13,"']/@SECNAME"))

После всех манипуляций в гугл таблице у меня вышло вот так: вызов различных значений по четырем разным видам облигаций.

Облигации в гугл таблицах

 

На этом собственно можно было и завершить мою поэму. Но есть парочка особенностей.

Настройка таблицы

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

Обычно номинал российских бумаг - 1 000. Соответственно, если в котировках ОФЗ мы видим 101,587 - это значит, что цена облигации 101,58% от номинала или 1 015,87 рублей.

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

Учет облигаций в гугл таблице
Учет облигаций в Google Sheets

Идентификаторы, код и поиск облигаций на Мосбиржи

Запомнить идентификаторы торгов для разных видов облигаций:

  • TQOB - ОФЗ;
  • TQCB - муниципальные и корпоративные;
  • TQOD - евробонды.

У всех облигаций (кроме ОФЗ), код ценной бумаги совпадает с ISIN.  ISIN код всегда указывается рядом с названием бумаги (в приложении брокера). Это я к тому, что не обязательно каждый раз искать на Мосбирже карточку облигаций.

Файл-шаблон

Оставляю ссылку на мой файл со всеми вышеперечисленными примерами и рабочими формулами. Файл работает только на просмотр (без права редактирования).

Чтобы утащить к себе, в верхнем меню выбираем "Файл - > Создать копию". Сохраняем и дальше тираним и переделываем под себя.

Очень приветствуются замечания, пожелания и советы по улучшению.

Удачных инвестиций!

Подпишись на рассылку лучших статей блога

Логотип telegramЗа обновлениями в этой и других статьях теперь можно следить на Telegram-канале: @vsedengy.
Оцените статью
Поделиться с друзьями
Записки инвестора
Добавить комментарий

Внимание! Ваш комментарий будет опубликован после модерации.

  1. Дим_Димыч

    По моему проще настроить выгрузку из квика.

    Ответить
  2. Дмитрий!

    Не все пользуются квиком. Не везде можно выгрузить. Тут отслеживается в онлайне и в легкой табличке!
    Класс! Искусство! :)

    Ответить
  3. Денис

    Еще бы понять как вытащить НДК?

    Ответить