Считать облигации вручную — прошлый век! Автоматизируем всё 📈

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

От Екатерины 👋

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

Улучшают ликвидность, оптимизирую показатель риск/доходность, придают гибкость при принятии решений. А на сегодняшний день они ещё и самый доходный и предсказуемый инструмент.

Мало кто из частных инвесторов заморачивается ведением своего портфеля. Конечно, это неправильно, но вполне можно понять. Слишком много времени уходит на учет, а его всегда и всем не хватает. Поэтому и доверяют своему торговому приложению. Но не видя картинки в целом, очень трудно принимать как стратегические, так и тактические решения по портфелю.

Сейчас есть приложения, которые помогают вести портфель. Они не бесплатные, но порядок в портфеле того стоит.

Предыстория 📚

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

• Как вести учет облигаций в Excel
• Как вести учет облигаций в Excel. Расчет доходности
• Как вести учет облигаций в Excel. Дюрация
• Как рассчитать дюрацию «на коленке». Часть 4

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

Важность учёта купонов 💡

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

Почему учёт купонов так важен:

✅ Позволяет прогнозировать денежные потоки

✅ Упрощает реинвестирование

✅ Помогает оценить реальную доходность портфеля

Проблема учёта 🤔

Например, брокерские приложения принципиально не учитывают выплаченные купоны, что вносит дикую путаницу в умах инвесторов. Они бесконечно жалуются на "красные" портфели, и сетуют на убытки. Забывая о том, что облигации - это еще и полученные купоны.

Собрать самому купонные выплаты можно, но очень трудоемко. Так как все портфели я веду в Excel, то прошла долгий путь от вбивания каждой купонной выплаты руками, до того, о чем мы расскажем в этой статье.

Основная проблема

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

Существующие способы учёта:

• Вести записи вручную в Excel
• Использовать специализированные приложения (платно)

Ручной способ в Excel 📊

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

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

Техническое решение от Михаила 💻

Автоматизированное получение данных о купонах и выплатах номинала облигаций

Этот Python скрипт автоматически скачивает данные о купонах и выплатах номинала через API Московской биржи для списка облигаций из первой вкладки Excel-файла и записывает результат обратно в этот же файл на вкладку "Ден.поток". Третья вкладка содержит формулы анализа полученных данных.

Почему отдельный скрипт?

Я выбрал использовать Python скрипт отдельно от встроенной функции Python в Excel, потому что встроенная функция накладывает множество ограничений:

• Microsoft Office 2021 - функции Python нет
• Microsoft Office 2024 - есть встроенный Python, но нужна подписка Office 365 (Mondo 2016)
• Работает только под Windows
• Обрезанный Python с ограниченным набором библиотек

Преимущества отдельного скрипта 🚀

Исходя из этого я выбрал написать отдельный Python скрипт - ведь тогда:

• Не важно какая версия Excel установлена
• Поддерживаются разные операционные системы (Windows, macOS, Linux)
• Можно использовать LibreOffice Calc как альтернативу Excel

Как это помогает? 💪

• Видно, когда и сколько денег поступит
• Можно заранее планировать реинвестирование
• Повышается контроль над кэшем в портфеле

Все это касается уже существующих портфелей.

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

Практическая реализация 🛠️

Скрипт является open-source проектом на GitHub. Любой может не только посмотреть код, но и предложить свои улучшения через pull request (PR).

Инструкция по установке:

1. Скачайте скрипт с GitHub (кнопка Code / Download ZIP)
2. Основной файл - 2_bonds_search coupons.py
3. Установите Python с официального сайта
4. Откройте командную строку и выполните:

pip install requests openpyxl

Это установит необходимые библиотеки:

• Requests: для HTTP-запросов
• OpenPyXL: для работы с Excel

Использование:

1. Заполните файл-шаблон bonds.xlsx вашими данными
2. Закройте Excel-файл
3. Запустите скрипт двойным кликом
4. Дождитесь завершения и проверьте результаты

Итоги: почему стоит использовать это решение? 🎯

🔹 Без учёта облигаций инвестор упускает важную часть доходности
🔹 Системный учёт купонных выплат даёт чёткое понимание будущих денежных потоков
🔹 Автоматизация позволяет не тратить время на рутину
🔹 Использование скрипта упрощает управление облигационной частью портфеля

Авторы:

• Михаил Шардин
• Екатерина Кутняк

Что дальше? 🔮

P.S. Планируется ещё три продолжения статьи.

P.P.S. Анонс следующей публикации: мы создали скрипт, который автоматически собирает последние новости о компаниях-эмитентах по их коду с Московской биржи. Это особенно полезно для частных инвесторов, у которых не всегда есть время на глубокий анализ каждого эмитента.