Ведение облигационного портфеля в Excel и «Google Таблицах» с привязкой к API Московской биржи

Опыт показывает, что большое количество людей хотят вести подсчёт всех показателей своего облигационного портфеля в таблицах Excel. Об этом говорят сотни репостов, лайков, комментариев под постами по таблицам, что я публиковал.

В ведении excel таблицы с облигациями есть много преимуществ. Одним из главных считаю возможность кастомизации всего, что угодно. Если вам нужен любой из десятков параметров, вы можете без труда их указать. Миксовать по своему усмотрению всё, что только вздумается.

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

В этой статье собрал абсолютно все материалы по работе с таблицами excel и гугл, что написал более чем за год.

Статья состоит из следующих разделов:

У Excel таблиц есть всевозможные версии, модификации и надстройки, чтобы всё работало одинаково нужно, чтобы вы привели свою таблицу к тому же виду, которым пользуюсь я. Тогда проблем возникать не будет.

Сейчас разберём, как же начать работать с таблицей, чтобы не появлялись #ЗНАЧ! или #ИМЯ

Эти ошибки возникают чаще всего.

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

1- Начну сразу с версии Excel, от них много чего зависит. Вам нужен офис не старее 16 года.

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

Выход весьма простой: нажимаете Файл → Сохранить как → Выбираете удобное вам место → Тип файла выбирайте Книга Excel как на скриншоте

3- Часто Excel может спрашивать про WEBSERVICE при входе и их нужно включать, так как без них данные с интернета не будут подтягиваться

4- Следующим этапом нужно обновить страницу, чтобы данные заново подгрузились, это делается при использовании комбинации ctrl + alt + F9

Эту же комбинацию можете использовать, когда вводите свои данные, так к ним подгрузится актуальная информация.

5- Данные не подгружаются, если вы пробуете это сделать в часы, когда мосбиржа не работает. Речь идёт о новых данных, допустим, если вы введёте новый ISIN, то он не обновится, но если у вас уже был ранее подгруженный ISIN, то информация будет обновляться.

До конца не понял как это точно работает, но раз на раз не приходится. Иногда новые данные и в выходные грузятся.

6- Использование точки и запятой в формулах

Заходим в Файл → Параметры → Дополнительно → ищем Использовать системные разделители. Нужно, чтобы всё было как у меня на скриншоте.

В некоторых случаях помогают разные вариации: с галочкой или без, точка с запятой на разных местах стоят. В общем, если не получится, то попробуйте все варианты перебрать (пробуете вариант, сохраняете, нажимаете комбинацию клавиш ctrl + alt + F9 и смотрите результат)

Все ссылки работают через API Московской Биржи.

Чтобы понять, что такое API проведу аналогию с рестораном. База данных московской биржи- это кухня ресторана, мы и в ресторане и в финансовом мире- клиенты. Как, что, кем готовится на кухне или в базе данных биржи нас не волнует, нам важен конечный продукт. В ресторане официант принимает от нас информацию о том, что мы хотим, передаёт на кухню, там забирает заказ и приносит нам готовый заказ. API делает тоже самое, мы ему говорим что хотим, он делает все манипуляции с базой данных мосбиржи и приносит нам готовую информацию.

Чтобы начать пользоваться таблицей Excel необходимо лишь научиться работать с API, что мы сейчас и сделаем.

Все формулы выглядят одинаково и в них меняется всего пара деталей.

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

Для всех формул нам нужно 2 главных компонента. Это ISIN бумаги и её режим торгов. Все данные будь то название компании, величина купона или дата оферты опираются на них.

isin мы вписываем сами, а вот режим торгов проще всего узнавать через формулу.

Так выглядит формула по получению режима торгов облигации Контрол Лизинг выпуск 2:

Если вставить её в ячейку Excel, то после нажатия на Enter вам выдаст текст “TQCB”. TQCB- это режим торгов в котором торгуются облигации Контрол Лизинга. На скриншоте под цифрой 1 выделен фрагмент куда я вставил формулу, а под цифрой 2 показано, что в ячейке B2 появилась надпись TQCB.

Не пугайтесь, далее всё расписано подробнее

Функция состоит из 2 частей:

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

Взгляните на ссылку:

https://iss.moex.com/iss/engines/stock/markets/bonds/securities/RU000A1086N2/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=BOARDID

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

На скриншоте выделил фрагмент кода, где нам отдаётся нужная информация.

То есть формула Excel лишь обрабатывает ссылку, которая ведёт на специальный раздел Московской Биржи с точечной информацией и отдаёт её в виде текста в ячейке Excel.

Перейдём к следующей ячейке в таблице и узнаем название эмитента через запрос к API

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

Сначала разберём фрагменты "&B2&" и "&A2&". В таком виде выглядят ссылки на другие ячейки. Как вы могли догадаться, ссылки ведут на необходимые нам ISIN и режим торгов. Это нужно, чтобы далее можно было просто протянуть ячейку и каждый раз не вписывать новые данные.

Если бы нужно было получить ссылку, которая ведёт на страницу в интернете, то она выглядела бы так:

https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities/RU000A1086N2/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECNAME

Вместо ссылок у нас стоит режим торгов и isin бумаги

На скриншоте я показал как это выглядит в Excel

Формулу можно представить вот в таком виде:

С режимом торгов и идентификатором(ISIN) мы разобрались, разберёмся и с РАЗДЕЛ и ДАННЫЕ

Разделы и данные находятся тут

Это специальный список для работы с API Мосбиржи. В нём можно найти название нужных вам данных, посмотреть как они называются в API и в каком разделе располагаются.

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

В области 1 указано, что в формулу были добавлены нужные данные и раздел, а в области 2 мы видим сокращенное наименование эмитента.

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

Как видите, ничего сложного.

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

Видим, что раздел securities, а наименование COUPONPERCENT

В списке от Московской биржи есть и другие разделы, например, marketdata. В ней можно найти разную информацию, касающуюся торгов на бирже. Так, LAST показывает нам цену закрытия облигации на предыдущий день торгов.

И формула выглядит следующим образом:

На скриншоте выделил где вставлена эта формула, и что в этой ячейке отображается текущая цена облигации Контрол Лизинг.

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

Сама таблица находится тут

В этом пункте я разберу подробно каждый столбец в таблице. Распишу как он формируется, как считается, для чего нужен.

Это два самых главных элемента, которые нужны для расчёта всех остальных формул.

ISIN подставляется руками, а режим торгов высчитывается по формуле:

Название бумаги выдаётся краткое, чтобы можно было проще читать. Оно отдаётся посредством формулы:

Количество бумаг указывается вами в зависимости от размера вашего портфеля. Снизу автоматически подсчитывается суммарное количество по всем бумагам в портфеле.

Купон в процентах - это годовая процентная доходность облигации, считается по формуле:

Купон в рублях- это размер 1 купона в рублях, считается по формуле:

НКД указывается актуальный, также считается по формуле:

Это моя личная гордость, долго мучался с тем как же считать цену бумаги. Сначала проценты умножал на 10, но это не помогало, если у компании была хоть одна амортизация или же нестандартный номинал.

Цена бумаги в % показывает процентную стоимость бумаги относительно номинала. Номинал берётся за 100%. Это считается по формуле:

Номинал бумаги особенно актуален для расчёта бумаг с амортизацией и считается по формуле:

И наконец цена бумаги. Умножается номинал на процентную цену и показывает текущую цену в рублях.

Стоимость бумаг в портфеле- это обычное перемножение количество бумаг в портфеле на цену 1 облигации

В дате погашения ничего удивительного, она считается по формуле:

В формуле по дате оферты я добавил условие, чтобы если оферты нет, то писалось "нет оферты" вместо выпадающей ошибки. Считается по формуле:

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

YTM показывает % годовых, которые даёт бумага. Рассчитывается по формуле:

Тут могут немного некорректно отображаться доходности флоатеров или бумаг с близкими офертами.

Эффективная доходность показывает значение в годовых, но до ближайшего события (погашение/оферта)

Например, в примере на скриншоте последняя строка- это облигации М.Видео. Их YTM составляет 21,64% годовых, но оферта уже менее, чем через год, поэтому Эффективная доходность чуть меньше и составляет 21,3267

Эффективная доходность считается по формуле:

G-spread подтягивается с Мосбиржи и показывает разницу в доходности относительно ОФЗ. Считается по формуле:

Дни до погашения не смог по формуле из API подтянуть, не ожидал такого))))В итоге отнимаю от даты погашения сегодняшнюю дату и получаю нужное значение)

Дюрация уже считается нормально по формуле:

Дата следующего купона подтягивается по формуле:

Частота купонных выплат указывается в количестве раз в год и считаются по формуле:

Из-за того, что в формуле присутствует обычное деление, то иногда могут быть такие числа как 3,4 или 7,1. Исправлю этот недочёт в будущем.

Тут вопросов, думаю, возникнуть не должно. В заголовках уже всё расписано, чтобы понять что к чему относится. Никаких сложных формул нет.

Всё это вы можете добавлять или удалять по своему усмотрению. Всё удобно.

Как писал выше, все данные подтягиваются через isin и режим торгов.

У ОФЗ же 2 режима торгов. API Московской биржи отдаёт оба этих режима, но работает исключительно с одним: TQOB

Но при автоматическом подтягивании режима торгов подтягивает режим торгов SPOB и дальше не работает.

В этом случае необходимо руками вписать в поле режима торгов ОФЗ TQOB, а все остальные формулы не трогать. В таком случае всё будет работать точно так как и с прочими облигациями.

Гугл таблицы чаще всего пригождаются людям с макбуками так как apple не считает нужным давать своим пользователям удобный доступ к сервисам, которыми пользуется большинство жителей планеты. Excel не исключение.

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

Также может пригодиться вам, если по той или иной причине не подходят классические таблицы excel.

Прямо сейчас уже много раз читал, что гугл таблицы не работают с API Московской биржи так как она под санкциями и так гугл их выполняет, но сегодня 05.07.2024 и у меня всё работает.

Поэтому добавляю и этот блог в статью. Поехали!

Тут также необходимо подготовка так как без неё у вас могут не подгружаться данные.

Вся подготовка заключается в изменении региональных настроек.

Переходим в "Файл" -> "Настройки"

Далее в "Региональные настройки" меняем регион на "Соединенные Штаты". Нажимаем "Сохранить настройки"

Это автоматически включит все настройки, которые нам необходимы, больше ничего не требуется.

Для примера вы можете использовать мою таблицу-пример, которая находится тут: https://t.me/filippovich_money/652

1- при входе у вас будут спрашивать про обмен данными с третьими сторонами. Вам надо разрешить доступ.

2- у гугл таблиц формулы немного отличаются

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

Пойдём по порядку.

Честно, я не нашёл способа как подтягивать режим торгов. Он точно есть, но на данный момент у меня нет решения 😑

Поэтому режим торгов необходимо проставлять руками... В будущем обязательно надо исправить косяк.

Для того чтобы найти режим торгов бумаги нам необходимо зайти на сайт Московской биржи и в поиске ввести ISIN бумаги. Сайт найдёте по ссылке: https://www.moex.com/

Для примера возьмём бумагу Контрол Лизинг выпуск 2 с ISIN RU000A1086N2

Вводим в поиске сайта и переходим

При переходе на страницу бумаги прокручиваем немного вниз и находим поле Идентификатор режима торгов. Это то, что нам нужно.

С режимом торгов разобрались, теперь посмотрим ещё раз на формулу

Сейчас разберём, что такое РАЗДЕЛ и ДАННЫЕ.

Это мы ищем на специальном сайте для API Московской биржи. Вот ссылка: https://iss.moex.com/iss/engines/stock/markets/bonds/

Например, нам надо узнать НКД у бумаги, на сайте он расположен так:

Securities- это раздел, где находится множество всевозможных данных.

ACCRUEDINT - это название тех данных, которые нам нужны. В данном случае так называется НКД

Теперь нам известны ISIN, режим торгов, раздел и данные. Подставим всё это в формулу:

В приведённом примере эта формула располагается в ячейке F2

Прошу обратить внимание на ту же самую формулу, но для другой бумаги:

У неё режим торгов другой. Примерно процентов 70 по гугл-таблице именно из-за такой мелочи. Будьте внимательны!

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

Этой вещью является ограничение на количество отправляемых запросов. Бесконечные Loading...

Из-за этого отправка более 5-10 запросов разом может превратиться и в 20 минут ожидания. А 5-10 запросов- это даже не 1 строка данных, которые я собираю, то есть 1 бумага.

У меня как-то было более 30 бумаг и для полной прогрузки такого количества запросов гугл таблица без перерыва стояла 2 часа!!!

Иногда и на 2 запросах гугл таблицы могут грузиться 10 минут. Это крайне неудобно, когда тебе надо быстренько зайти, прогрузить информацию и выйти, а тут тебе надо долго ждать.

В Excel таблицах такого ограничения нет и вы можете хоть 1000 бумаг разом грузить, никаких ограничений не будет.

В общем, много кому именно гугл таблицы и нужны, поэтому появилась эта таблица.

Буду крайне рад, если статья окажется полезна.

Ставьте лайк и подписывайтесь на меня 👍

Мои социальные сети:

Телеграм канал

Дзен

Smart-Lab

Пульс