Опыт показывает, что большое количество людей хотят вести подсчёт всех показателей своего облигационного портфеля в таблицах Excel. Об этом говорят сотни репостов, лайков, комментариев под постами по таблицам, что я публиковал.
В ведении excel таблицы с облигациями есть много преимуществ. Одним из главных считаю возможность кастомизации всего, что угодно. Если вам нужен любой из десятков параметров, вы можете без труда их указать. Миксовать по своему усмотрению всё, что только вздумается.
Привязка к API Московской биржи позволяет тянуть всю информацию напрямую с первоисточника, что гарантирует вам наиболее достоверные данные.
В этой статье собрал абсолютно все материалы по работе с таблицами excel и гугл, что написал более чем за год.
Статья состоит из следующих разделов:
- Подготовка таблицы Excel к работе
- Принцип работы формул с привязкой к API Московской биржи
- Пример практического использования таблицы
- Работа с ОФЗ в Excel
- Работа с гугл таблицами
Подготовка таблицы Excel к работе
У Excel таблиц есть всевозможные версии, модификации и надстройки, чтобы всё работало одинаково нужно, чтобы вы привели свою таблицу к тому же виду, которым пользуюсь я. Тогда проблем возникать не будет.
Сейчас разберём, как же начать работать с таблицей, чтобы не появлялись #ЗНАЧ! или #ИМЯ
Эти ошибки возникают чаще всего.
Сразу скажу, что эта инструкция для компьютеров на Windows. Apple считает, что продукты Microsoft на их компьютерах не нужны(((
В вашем случае сразу же листайте на часть статьи про гугл таблицы. Они работают у всех одинаково.
Также инструкции не подойдут для тех, кто читает с телефона. В статье речь идёт только про десктопные версии таблиц
Пойду по пунктам, что стоит сделать, чтобы заработала таблица. Сам пользуюсь таблицей с разных устройств и не редко сталкиваюсь с подобными проблемами. Какой-то из пунктов вам явно поможет.
1- Начну сразу с версии Excel, от них много чего зависит. Вам нужен офис не старее 16 года.
2- При скачивании у вас автоматически будет выбран такой формат файла, который используется на вашем компьютере по умолчанию(а их очень много).
Выход весьма простой: нажимаете Файл → Сохранить как → Выбираете удобное вам место → Тип файла выбирайте Книга Excel как на скриншоте
3- Часто Excel может спрашивать про WEBSERVICE при входе и их нужно включать, так как без них данные с интернета не будут подтягиваться
4- Следующим этапом нужно обновить страницу, чтобы данные заново подгрузились, это делается при использовании комбинации ctrl + alt + F9
Эту же комбинацию можете использовать, когда вводите свои данные, так к ним подгрузится актуальная информация.
5- Данные не подгружаются, если вы пробуете это сделать в часы, когда мосбиржа не работает. Речь идёт о новых данных, допустим, если вы введёте новый ISIN, то он не обновится, но если у вас уже был ранее подгруженный ISIN, то информация будет обновляться.
До конца не понял как это точно работает, но раз на раз не приходится. Иногда новые данные и в выходные грузятся.
6- Использование точки и запятой в формулах
Заходим в Файл → Параметры → Дополнительно → ищем Использовать системные разделители. Нужно, чтобы всё было как у меня на скриншоте.
В некоторых случаях помогают разные вариации: с галочкой или без, точка с запятой на разных местах стоят. В общем, если не получится, то попробуйте все варианты перебрать (пробуете вариант, сохраняете, нажимаете комбинацию клавиш ctrl + alt + F9 и смотрите результат)
Принцип работы формул с привязкой к API Московской биржи
Все ссылки работают через API Московской Биржи.
Чтобы понять, что такое API проведу аналогию с рестораном. База данных московской биржи- это кухня ресторана, мы и в ресторане и в финансовом мире- клиенты. Как, что, кем готовится на кухне или в базе данных биржи нас не волнует, нам важен конечный продукт. В ресторане официант принимает от нас информацию о том, что мы хотим, передаёт на кухню, там забирает заказ и приносит нам готовый заказ. API делает тоже самое, мы ему говорим что хотим, он делает все манипуляции с базой данных мосбиржи и приносит нам готовую информацию.
Чтобы начать пользоваться таблицей Excel необходимо лишь научиться работать с API, что мы сейчас и сделаем.
Для начала распишу общие принципы, чтобы было понятно откуда берутся данные.
Все формулы выглядят одинаково и в них меняется всего пара деталей.
Этот урок делаю на основе прикреплённой таблицы. Если что-то не получается, то можете заглянуть в таблицу и сверить.
Для всех формул нам нужно 2 главных компонента. Это ISIN бумаги и её режим торгов. Все данные будь то название компании, величина купона или дата оферты опираются на них.
isin мы вписываем сами, а вот режим торгов проще всего узнавать через формулу.
Так выглядит формула по получению режима торгов облигации Контрол Лизинг выпуск 2:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/RU000A1086N2/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=BOARDID»);»//document//data//rows//row/@BOARDID»)
Если вставить её в ячейку Excel, то после нажатия на Enter вам выдаст текст “TQCB”. TQCB- это режим торгов в котором торгуются облигации Контрол Лизинга. На скриншоте под цифрой 1 выделен фрагмент куда я вставил формулу, а под цифрой 2 показано, что в ячейке B2 появилась надпись TQCB.
Не пугайтесь, далее всё расписано подробнее
Функция состоит из 2 частей:
- Связка из двух формул ФИЛЬТР.XML + ВЕБСЛУЖБА
- Ссылка
По поводу связки формул вообще не заморачивайтесь, она не меняется, а все действия происходят внутри ссылки.
Взгляните на ссылку:
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
Формула выглядит следующим образом:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECNAME»);»//document//data//rows//row/@SECNAME»)
Сначала разберём фрагменты «&B2&» и «&A2&». В таком виде выглядят ссылки на другие ячейки. Как вы могли догадаться, ссылки ведут на необходимые нам ISIN и режим торгов. Это нужно, чтобы далее можно было просто протянуть ячейку и каждый раз не вписывать новые данные.
Если бы нужно было получить ссылку, которая ведёт на страницу в интернете, то она выглядела бы так:
Вместо ссылок у нас стоит режим торгов и isin бумаги
На скриншоте я показал как это выглядит в Excel
Теперь разберём как менять формулу, чтобы получать всё, что нам необходимо
Формулу можно представить вот в таком виде:
С режимом торгов и идентификатором(ISIN) мы разобрались, разберёмся и с РАЗДЕЛ и ДАННЫЕ
Разделы и данные находятся тут
Это специальный список для работы с API Мосбиржи. В нём можно найти название нужных вам данных, посмотреть как они называются в API и в каком разделе располагаются.
На скриншоте отметил что нам нужно смотреть, если мы хотим получить краткое наименование ценной бумаги.
В области 1 указано, что в формулу были добавлены нужные данные и раздел, а в области 2 мы видим сокращенное наименование эмитента.
Небольшое отступление по всей формуле, а не только ссылке. ДАННЫЕ необходимо подставлять не только в ссылке, но и в конце формулы, как указано в примере ниже:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/РЕЖИМТОРГОВ/securities/ИДЕНТИФИКАТОР/securities.xml?iss.meta=off&iss.only=РАЗДЕЛ&РАЗДЕЛ.columns=ДАННЫЕ»);»//document//data//rows//row/@ДАННЫЕ»)
Как видите, ничего сложного.
Для лучшего понимания ниже распишу все формулы в таблице и откуда они берутся.
Вот так выглядит формула для подтягивания доходности купонов по облигациям в процентах:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=COUPONPERCENT»);»//document//data//rows//row/@COUPONPERCENT»)
Видим, что раздел securities, а наименование COUPONPERCENT
В списке от Московской биржи есть и другие разделы, например, marketdata. В ней можно найти разную информацию, касающуюся торгов на бирже. Так, LAST показывает нам цену закрытия облигации на предыдущий день торгов.
И формула выглядит следующим образом:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=LAST»);»//document//data//rows//row/@LAST)
На скриншоте выделил где вставлена эта формула, и что в этой ячейке отображается текущая цена облигации Контрол Лизинг.
Теперь вы самостоятельно сможете пользоваться таблицей и находить необходимые формулы для гибкой настройки.
Пример практического использования таблиц
Сама таблица находится тут
В этом пункте я разберу подробно каждый столбец в таблице. Распишу как он формируется, как считается, для чего нужен.
Начинаем с ISIN и режима торгов
Это два самых главных элемента, которые нужны для расчёта всех остальных формул.
ISIN подставляется руками, а режим торгов высчитывается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=BOARDID»);»//document//data//rows//row/@BOARDID»)
Название бумаги и количество
Название бумаги выдаётся краткое, чтобы можно было проще читать. Оно отдаётся посредством формулы:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SHORTNAME»);»//document//data//rows//row/@SHORTNAME»)
Количество бумаг указывается вами в зависимости от размера вашего портфеля. Снизу автоматически подсчитывается суммарное количество по всем бумагам в портфеле.
Купон в рублях и процентах + НКД
Купон в процентах — это годовая процентная доходность облигации, считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,COUPONPERCENT»);»//document//data//rows//row/@COUPONPERCENT»)
Купон в рублях — это размер 1 купона в рублях, считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=COUPONVALUE»);»//document//data//rows//row/@COUPONVALUE»)
НКД указывается актуальный, также считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=ACCRUEDINT»);»//document//data//rows//row/@ACCRUEDINT»)
Цена бумаги и общая стоимость бумаг в портфеле
Это моя личная гордость, долго мучался с тем как же считать цену бумаги. Сначала проценты умножал на 10, но это не помогало, если у компании была хоть одна амортизация или же нестандартный номинал.
Цена бумаги в % показывает процентную стоимость бумаги относительно номинала. Номинал берётся за 100%. Это считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=LAST»);»//document//data//rows//row/@LAST»)
Номинал бумаги особенно актуален для расчёта бумаг с амортизацией и считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=FACEVALUE»);»//document//data//rows//row/@FACEVALUE»)
И наконец цена бумаги. Умножается номинал на процентную цену и показывает текущую цену в рублях.
Стоимость бумаг в портфеле- это обычное перемножение количество бумаг в портфеле на цену 1 облигации
Дата погашения и дата оферты
В дате погашения ничего удивительного, она считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=MATDATE»);»//document//data//rows//row/@MATDATE»)
В формуле по дате оферты я добавил условие, чтобы если оферты нет, то писалось «нет оферты» вместо выпадающей ошибки. Считается по формуле:
=ЕСЛИОШИБКА(ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=OFFERDATE»);»//document//data//rows//row/@OFFERDATE»);«нет оферты»)
Всевозможные доходности
Доходность к оферте показывает доходность к ближайшей оферте. Если оферты нет, то и доходность такая не считается. Находим значение по формуле:
=ЕСЛИОШИБКА(ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=YIELDTOOFFER»);»//document//data//rows//row/@YIELDTOOFFER»);«нет оферты»)
YTM показывает % годовых, которые даёт бумага. Рассчитывается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=orderbook&orderbook.columns=YIELD»);»//document//data//rows//row/@YIELD»)
Тут могут немного некорректно отображаться доходности флоатеров или бумаг с близкими офертами.
Эффективная доходность показывает значение в годовых, но до ближайшего события (погашение/оферта)
Например, в примере на скриншоте последняя строка- это облигации М.Видео. Их YTM составляет 21,64% годовых, но оферта уже менее, чем через год, поэтому Эффективная доходность чуть меньше и составляет 21,3267
Эффективная доходность считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=EFFECTIVEYIELD»);»//document//data//rows//row/@EFFECTIVEYIELD»)
G-spread подтягивается с Мосбиржи и показывает разницу в доходности относительно ОФЗ. Считается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=GSPREADBP»);»//document//data//rows//row/@GSPREADBP»)
Дней до погашения и дюрация
Дни до погашения не смог по формуле из API подтянуть, не ожидал такого))))В итоге отнимаю от даты погашения сегодняшнюю дату и получаю нужное значение)
Дюрация уже считается нормально по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=DURATION»);»//document//data//rows//row/@DURATION»)
Дата следующего купона и периодичность выплат
Дата следующего купона подтягивается по формуле:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,NEXTCOUPON,COUPONVALUE»);»//document//data//rows//row/@NEXTCOUPON»)
Частота купонных выплат указывается в количестве раз в год и считаются по формуле:
= 365/ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&»/securities/»&A2&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=COUPONPERIOD»);»//document//data//rows//row/@COUPONPERIOD»)
Из-за того, что в формуле присутствует обычное деление, то иногда могут быть такие числа как 3,4 или 7,1. Исправлю этот недочёт в будущем.
Расчёты под денежный поток
Тут вопросов, думаю, возникнуть не должно. В заголовках уже всё расписано, чтобы понять что к чему относится. Никаких сложных формул нет.
Всё это вы можете добавлять или удалять по своему усмотрению. Всё удобно.
Работа с ОФЗ в Excel
Как писал выше, все данные подтягиваются через isin и режим торгов.
У ОФЗ же 2 режима торгов. API Московской биржи отдаёт оба этих режима, но работает исключительно с одним: TQOB
Но при автоматическом подтягивании режима торгов подтягивает режим торгов SPOB и дальше не работает.
В этом случае необходимо руками вписать в поле режима торгов ОФЗ TQOB, а все остальные формулы не трогать. В таком случае всё будет работать точно так как и с прочими облигациями.
В поле режима торгов по ОФЗ нет формул, а просто вписанный руками текст «TQOB»
Работа с гугл таблицами
Гугл таблицы чаще всего пригождаются людям с макбуками так как apple не считает нужным давать своим пользователям удобный доступ к сервисам, которыми пользуется большинство жителей планеты. Excel не исключение.
Для вас единственным выходом являются гугл таблицы, которые можно использовать и через маки.
Также может пригодиться вам, если по той или иной причине не подходят классические таблицы excel.
Прямо сейчас уже много раз читал, что гугл таблицы не работают с API Московской биржи так как она под санкциями и так гугл их выполняет, но сегодня 05.07.2024 и у меня всё работает.
Поэтому добавляю и этот блог в статью. Поехали!
Подготовка
Тут также необходимо подготовка так как без неё у вас могут не подгружаться данные.
Вся подготовка заключается в изменении региональных настроек.
Переходим в «Файл» -> «Настройки»
Далее в «Региональные настройки» меняем регион на «Соединенные Штаты». Нажимаем «Сохранить настройки»
Это автоматически включит все настройки, которые нам необходимы, больше ничего не требуется.
Как работать с таблицей?
Для примера вы можете использовать мою таблицу-пример, которая находится тут: https://t.me/filippovich_money/652
В гугл таблицах есть несколько отличий от таблиц excel:
1- при входе у вас будут спрашивать про обмен данными с третьими сторонами. Вам надо разрешить доступ.
2- у гугл таблиц формулы немного отличаются
Она выглядит следующим образом:
=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/bonds/boards/РЕЖИМ_ТОРГОВ/securities.xml?iss.meta=off&iss.only=РАЗДЕЛ&РАЗДЕЛ.columns=SECID,ДАННЫЕ», concatenate(«//row[@SECID='»,ISIN,»‘]/@ДАННЫЕ»))
Пойдём по порядку.
Режим торгов
Честно, я не нашёл способа как подтягивать режим торгов. Он точно есть, но на данный момент у меня нет решения 😑
Поэтому режим торгов необходимо проставлять руками… В будущем обязательно надо исправить косяк.
Для того чтобы найти режим торгов бумаги нам необходимо зайти на сайт Московской биржи и в поиске ввести ISIN бумаги. Сайт найдёте по ссылке: https://www.moex.com/
Для примера возьмём бумагу Контрол Лизинг выпуск 2 с ISIN RU000A1086N2
Вводим в поиске сайта и переходим
При переходе на страницу бумаги прокручиваем немного вниз и находим поле Идентификатор режима торгов. Это то, что нам нужно.
С режимом торгов разобрались, теперь посмотрим ещё раз на формулу
=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/bonds/boards/РЕЖИМ_ТОРГОВ/securities.xml?iss.meta=off&iss.only=РАЗДЕЛ&РАЗДЕЛ.columns=SECID,ДАННЫЕ», concatenate(«//row[@SECID='»,ISIN,»‘]/@ДАННЫЕ»))
Сейчас разберём, что такое РАЗДЕЛ и ДАННЫЕ.
Это мы ищем на специальном сайте для API Московской биржи. Вот ссылка: https://iss.moex.com/iss/engines/stock/markets/bonds/
Например, нам надо узнать НКД у бумаги, на сайте он расположен так:
Securities — это раздел, где находится множество всевозможных данных.
ACCRUEDINT — это название тех данных, которые нам нужны. В данном случае так называется НКД
Теперь нам известны ISIN, режим торгов, раздел и данные. Подставим всё это в формулу:
=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities.xml?iss.meta=off&iss.only=Securities&Securities.columns=SECID,ACCRUEDINT», concatenate(«//row[@SECID='»,RU000A1086N2,»‘]/@ACCRUEDINT»))
В приведённом примере эта формула располагается в ячейке F2
Прошу обратить внимание на ту же самую формулу, но для другой бумаги:
=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/bonds/boards/TQIR/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,ACCRUEDINT», concatenate(«//row[@SECID='»,A3,»‘]/@ACCRUEDINT»))
У неё режим торгов другой. Примерно процентов 70 по гугл-таблице именно из-за такой мелочи. Будьте внимательны!
Почему мне сильно не нравится работать с таблицей в гугл таблицах
В самом начале я делал все таблицы исключительно в гугл таблицах из-за того, что тут есть ряд функций, которых нет в Excel, но 1 крайне важная вещь полностью отвернула меня от этого сервиса.
Этой вещью является ограничение на количество отправляемых запросов. Бесконечные Loading…
Из-за этого отправка более 5-10 запросов разом может превратиться и в 20 минут ожидания. А 5-10 запросов- это даже не 1 строка данных, которые я собираю, то есть 1 бумага.
У меня как-то было более 30 бумаг и для полной прогрузки такого количества запросов гугл таблица без перерыва стояла 2 часа!!!
Иногда и на 2 запросах гугл таблицы могут грузиться 10 минут. Это крайне неудобно, когда тебе надо быстренько зайти, прогрузить информацию и выйти, а тут тебе надо долго ждать.
В Excel таблицах такого ограничения нет и вы можете хоть 1000 бумаг разом грузить, никаких ограничений не будет.
В общем, много кому именно гугл таблицы и нужны, поэтому появилась эта таблица.
На этом статья подходит к концу
Буду крайне рад, если статья окажется полезна.
Ставьте лайк и подписывайтесь на меня 👍
Мои социальные сети: