Учет инвестиций в гугл-таблицах №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,PREVLEGALCLOSEPRICE", concatenate("//row[@SECID='",A4,"']/@PREVLEGALCLOSEPRICE"))

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

• вместо 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"))

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

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

• 11 самых популярных вопросов про облигации
• Про евробонды простыми словами

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

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

В идентификаторе режима торгов. У ОФЗ он - 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,PREVLEGALCLOSEPRICE", concatenate("//row[@SECID='",A7,"']/@PREVLEGALCLOSEPRICE"))

Для автоматической вставки правильного названия бумаги также меняем идентификатор на 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,PREVLEGALCLOSEPRICE", concatenate("//row[@SECID='",A13,"']/@PREVLEGALCLOSEPRICE"))

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

=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 рублей.

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

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

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

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

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

Файл-шаблон

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

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

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

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