Андрей Филиппович
Андрей Филиппович личный блог
02 мая 2024, 23:34

Excel таблица для мониторинга облигационного портфеля с данными из API московской биржи

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

В таблице реализовано:

— Краткое название бумаги
— Доходность купона в %
— Доходность купона в рублях
— НКД
— Цена бумаги в процентах
— Номинал бумаги
— Цена бумаги в рублях (смог решить вопрос с амортизируемыми бумагами)
— Дата погашения
— Дата оферты
— Доходность к оферте
— YTM
— Эффективная доходность
— G-spread
— Дней до погашения
— Дюрация

Всё это будет вам доступно лишь при введении ISIN бумаги. Реализовано много решений, которые сильно упрощают работу.

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

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

В этой статье я разберу каждый из пунктов по отдельности, чтобы сразу ответить на все вопросы

Для большего понимания можете также заглянуть в мою предыдущую статью. В ней я подробно рассказываю как работают формулы

Начинаем с ISIN и режима торгов

Excel таблица для мониторинга облигационного портфеля с данными из API московской биржи

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

 

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")

 

 

 

Название бумаги и количество


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=SHORTNAME");"//document//data//rows//row/@SHORTNAME")

 

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

Купон в рублях и процентах + НКД


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=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")

 

Цена бумаги и общая стоимость бумаг в портфеле


Excel таблица для мониторинга облигационного портфеля с данными из API московской биржи

Это моя личная гордость, долго мучался с тем как же считать цену бумаги. Сначала проценты умножал на 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 облигации

Дата погашения и дата оферты

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=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");«нет оферты»)

 

Всевозможные доходности

Excel таблица для мониторинга облигационного портфеля с данными из API московской биржи

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

=ЕСЛИОШИБКА(ФИЛЬТР.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")

 

Дней до погашения и дюрация:

Excel таблица для мониторинга облигационного портфеля с данными из API московской биржи

Дни до погашения не смог по формуле из 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")

 

Дата следующего купона и периодичность выплат


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=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 таблица для мониторинга облигационного портфеля с данными из API московской биржи

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

 

Спасибо тем, кто прочитал до конца, надеюсь, что было полезно. Если у вас есть вопросы или замечания, то пишите комментарии, на все отвечу.

Также ставьте лайк и подписывайтесь, это мотивирует!

 

Мой канал в телеграм, где я пишу больше всего постов и публикую таблицы: https://t.me/filippovich_money








33 Комментария
  • Сергей Кириков
    02 мая 2024, 23:59
    Как цену акций также получить?
  • IliaM
    03 мая 2024, 07:51
    Обновление данных в таблице раз в день?
  • โอเล็ก
    03 мая 2024, 07:55
    Не помешал бы столбец с текущей купонной доходностью.
    • Влад
      03 мая 2024, 08:32
      โอเล็ก, это кажется проще обычной формулой и сделать: кол-во купонов в год*купон/текущую стоимость
      • โอเล็ก
        03 мая 2024, 08:49
        Влад, понятно, что считается элементарно.
        Просто это добавляет юзабельности таблице, когда возникает необходимость отфильтровать массив именно по ТКД.
  • Влад
    03 мая 2024, 08:35
    Спасибо за полезное знакомство с апи мосбиржи.
    Все никак руки не доходили, хоть и мысль была:)
  • vgnz51mrg
    03 мая 2024, 08:42
    идея хорошая, но не работает )
    • vgnz51mrg, я свой файл давно сделала. Эти данные не обновляются во время работы биржи. Я обновляю с 6.00мск и до начала работы мск биржи, т е примерно 9-50. Автоматом тоже не получается. Только вручную. Я уже по этому поводу перелопатила кучу материалов- бесполезно. Вроде всё должно, а…
      • MarshalTX
        03 мая 2024, 16:41
        Татьяна Голованова, тупой вопрос — как вместо точки сделать разделителем запятую? Все, вроде, по инструкции написал, все-равно выводить точку.

        iss.moex.com/iss/engines/futures/markets/forts/securities/cnyrubf.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SWAPRATE
        • MarshalTX, Я изначально ставлю точку в таблице, поэтому не сталкивалась. Об этом писал один из авторов, что не будет работать. И еще он пишет, что при использовании дат нужно брать европейский стандарт — «гггг.мм.дд». Вроде биржи только так работают.
          • MarshalTX
            04 мая 2024, 09:25
            Татьяна Голованова, даты трансформируются нормально при подтягивании. Проблему с точкой решил через ПОДСТАВИТЬ.

            =ПОДСТАВИТЬ(@ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/futures/markets/forts/securities/»&I14&".xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SWAPRATE");"//document//data//rows//row/@SWAPRATE");".";",")
        • Your bunny wrote
          08 мая 2024, 00:15
          MarshalTX, еще можно попробовать в настройках ОС изменить формат разделителя: Регион >> Форматы >> Доп. параметры >> Пункт «Разделитель целой и дробной части» (изменить, на .)
  • Елена Тимофеева
    03 мая 2024, 13:11
    А мне вот не хватает столбца с датой начала торгов — выбирать самые свежие выпуски
  • Обновите пожалуйста ссылку на скачивание. Файл не скачивается
  • sihaia
    03 мая 2024, 14:38
    либре офис и опен офис так и не смог подружить с котировками ((

    в гугл доке — было очень удобно через указание биржи и тикета.
    в яндекс документах — вообще ничего подобного не нашёл ((
  • Файл так скачать и не получилось((
  • Алексей Киселев
    03 мая 2024, 20:54
    это в гугл-таблицах работать будет или в таблицах на яндекс диске?
    • 0PK
      07 мая 2024, 02:37
      Алексей Киселев,
      В гугле будет работать, но надо формулы менять. Там синтаксис немного отличается. Но то что работает, это точно.
      На я-таблицаз тоже скорее всего. Если синтаксис языка таблицы позволяет работать с api.
      Единственное что проблематично в гугл таблицах — при больших обьемах данных требовательны к интернету. А в этих таблицах обьем данных большой.
  • Владимир С.
    05 мая 2024, 15:07
    А где удобно эти самые ИСИНы по названию облигации найти скопом, а не по 1й руками вбивать?
    • 0PK
      07 мая 2024, 02:34
      Владимир С., незнаю насчет скопом, но попробуйте на bonds.fiman.ru
  • InvestingAsHobby_v_Telegramm
    06 мая 2024, 15:17

    Спасибо, полезно. Только что-то ОФЗ не достаёт. ПО ISIN RU000A106E90 
    Они в другом месте хранятся?

    • 0PK
      09 мая 2024, 01:33
      InvestingAsHobby_v_Telegramm, у офз, как впрочем иму других облиг, нужно не isin вводить, а торговвый код /тикер. Просто isin и торговый код корп облиг совпадает, у офз они различаются.
      Офз 26238
      Isin RU000A1038V6
      Торговый код/тикер(SecID в формулах) SU26238RMFS4

      Смотреть можно на сайте мосбиржи.

      Еще как вариант во второй ячейке, где выводится режим торгов, замени marketdata на securities

      =ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/»&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=BOARDID");"//document//data//rows//row/@BOARDID")

      Не могу проверить — нет екселя под рукой, но должно получиться.
  • BoldInvestor
    07 мая 2024, 16:41
    По SU26240RMFS0 получается лажа, потому что подтягивается не тот режим торгов (в этой бумаге их несколько и текущая формула берёт первый (SPOB (Поставка по ОФЗ)), а не нужный (TQOB (Т+: Гособлигации — безадрес.))).

    Глубоко не разбирался, как правильно выбрать правильный режим, вижу, что в SPOB вообще нет котировки, поэтому вот так помогло:

    Исправить XPath так:
    "//document//data//rows//row[@LAST>0]/@BOARDID"

    Суть изменения — брать не первую попавшуюся marketdata, а ту, где LAST цена отлична от 0.
    Возможно правильнее вообще брать не из marketdata, а из marketdata_yields (из неё сейчас достаётся Эффективная доходность). Там вообще только одна борда.
    • 0PK
      09 мая 2024, 21:15
      BoldInvestor,
      А если
      Попробывать так
      =ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/»&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=BOARDID");"//document//data//rows//row[@MARKETCODE=FNDT & @FACEUNIT=SUR]/@BOARDID"

      Убьешь сразу 2х зайцев
      -вводишь только isin (в маркетдате использует тикер, и у корпоратов тикер=isin, но у офз он отличается)
      — оставляешь один тип торгов не только у офз (MARKETCODE=FNDT, но и что бы все торговалось в рублях FACEUNIT=SUR, ведь могут быть корпораты с 1 isin/ тикиром, но в разных валютах. Например RU000A107RH8.
      П.с. Не уверен что правильно xPath написал, когда & два условия.
  • protorus
    07 мая 2024, 19:03
    А что если вместо даты OFFERDATE использовать BUYBACKDATE?

    А все выплаты можно, например, так:
    iss.moex.com/iss/statistics/engines/stock/markets/bonds/bondization/RU000A0JVN64.xml?iss.meta=off&iss.only=coupons&coupons.columns=name,coupondate,value&start=0&limit=100
  • Кирилл Крамаров
    12 мая 2024, 19:09
    Всем привет! Подскажите как решили вопрос с корректным отображением информации по ОФЗ?  Использовал предложенные тут варианты, нет 100% рабочей схемы. 



    подскажите для чайника что нужно исправить?

      • Кирилл Крамаров
        12 мая 2024, 19:12
        Андрей Филиппович, Спасибо большое, да ввел в ручную все ок! СПАСБОИ!
  • Кирилл Крамаров
    13 мая 2024, 18:10
    Подскажите как решили вопрос:
    1. Облигации с переменным доходом RUONIA +% — как что указываете вы? может как идея для каждой акции писать формулу (тянуть с официальной ставкой на текущий день + % который эмитент дает сверху)

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

    3. НКД тоже некоректные данные подгружает

    4. и цена бумаги в % некоторые бумаги не хочет видеть цену. 

    в чем может быть причина, как вылечить?

  • Your bunny wrote
    16 мая 2024, 00:27

    Я бы предложил режим торгов всё же получать по формуле:

    =ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/securities/»&A2&".xml?iss.meta=off&iss.only=boards&boards.columns=secid,boardid,is_primary");"//document//data//rows//row[@is_primary=1]/@boardid")

    Параметр is_primary=1: выбирает именно основной режим торгов для инструмента.

    Только сейчас дошли руки посмотреть на файл, плюс проверил на ОФЗ.

  • PavelRM
    16 мая 2024, 10:46
    А как обновить данные всего листа? F9 не помогает. Помогает только зайти в формулу ячейки и нажать Enter.

Активные форумы
Что сейчас обсуждают

Старый дизайн
Старый
дизайн