Статья состоит из следующих разделов:
Подготовка таблицы Excel к работе
Принцип работы формул с привязкой к API Московской биржи
Пример практического использования таблицы
Работа с ОФЗ в Excel
Работа с гугл таблицами
Подготовка таблицы Excel к работе
У Excel таблиц есть всевозможные версии, модификации и надстройки, чтобы всё работало одинаково нужно, чтобы вы привели свою таблицу к тому же виду, которым пользуюсь я. Тогда проблем возникать не будет.
Сейчас разберём, как же начать работать с таблицей, чтобы не появлялись #ЗНАЧ! или #ИМЯ
Эти ошибки возникают чаще всего.
Сразу скажу, что эта инструкция для компьютеров на Windows. Apple считает, что продукты Microsoft на их компьютерах не нужны(((
В вашем случае сразу же листайте на часть статьи про гугл таблицы. Они работают у всех одинаково.
Также инструкции не подойдут для тех, кто читает с телефона. В статье речь идёт только про десктопные версии таблиц
Пойду по пунктам, что стоит сделать, чтобы заработала таблица. Сам пользуюсь таблицей с разных устройств и не редко сталкиваюсь с подобными проблемами. Какой-то из пунктов вам явно поможет.
1- Начну сразу с версии Excel, от них много чего зависит. Вам нужен офис не старее 16 года.
2- При скачивании у вас автоматически будет выбран такой формат файла, который используется на вашем компьютере по умолчанию(а их очень много).
Выход весьма простой: нажимаете Файл → Сохранить как → Выбираете удобное вам место → Тип файла выбирайте "Книга Excel". Важно выбрать просто "Книга 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(ВЕБСЛУЖБА("https://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 частей:
По поводу связки формул вообще не заморачивайтесь, она не меняется, а все действия происходят внутри ссылки.
Прежде всего это рабочая ссылка и если перейти по ней, то попадёте на страницу где вам будет показан код с ответом на наш запрос. Мы хотели получить режим торгов облигации RU000A1086N2 и получили.
На скриншоте выделил фрагмент кода, где нам отдаётся нужная информация.
То есть формула Excel лишь обрабатывает ссылку, которая ведёт на специальный раздел Московской Биржи с точечной информацией и отдаёт её в виде текста в ячейке Excel.
Перейдём к следующей ячейке в таблице и узнаем название эмитента через запрос к API
Формула выглядит следующим образом:
=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://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
Теперь разберём как менять формулу, чтобы получать всё, что нам необходимо
Формулу можно представить вот в таком виде:
https://iss.moex.com/iss/engines/stock/markets/bonds/boards/РЕЖИМТОРГОВ/securities/ИДЕНТИФИКАТОР/securities.xml?iss.meta=off&iss.only=РАЗДЕЛ&РАЗДЕЛ.columns=ДАННЫЕ
С режимом торгов и идентификатором(ISIN) мы разобрались, разберёмся и с РАЗДЕЛ и ДАННЫЕ
Разделы и данные находятся тут
Это специальный список для работы с API Мосбиржи. В нём можно найти название нужных вам данных, посмотреть как они называются в API и в каком разделе располагаются.
На скриншоте отметил что нам нужно смотреть, если мы хотим получить краткое наименование ценной бумаги.
В области 1 указано, что в формулу были добавлены нужные данные и раздел, а в области 2 мы видим сокращенное наименование эмитента.
Небольшое отступление по всей формуле, а не только ссылке. ДАННЫЕ необходимо подставлять не только в ссылке, но и в конце формулы, как указано в примере ниже:
=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/РЕЖИМТОРГОВ/securities/ИДЕНТИФИКАТОР/securities.xml?iss.meta=off&iss.only=РАЗДЕЛ&РАЗДЕЛ.columns=ДАННЫЕ");"//document//data//rows//row/@ДАННЫЕ")
Как видите, ничего сложного.
Для лучшего понимания ниже распишу все формулы в таблице и откуда они берутся.
Вот так выглядит формула для подтягивания доходности купонов по облигациям в процентах:
=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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(ВЕБСЛУЖБА("https://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("https://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("https://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("https://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("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/...", concatenate("//row[@SECID='",A3,"']/@ACCRUEDINT"))
У неё режим торгов другой. Примерно процентов 70 по гугл-таблице именно из-за такой мелочи. Будьте внимательны!
Почему мне сильно не нравится работать с таблицей в гугл таблицах
В самом начале я делал все таблицы исключительно в гугл таблицах из-за того, что тут есть ряд функций, которых нет в Excel, но 1 крайне важная вещь полностью отвернула меня от этого сервиса.
Этой вещью является ограничение на количество отправляемых запросов. Бесконечные Loading...
Из-за этого отправка более 5-10 запросов разом может превратиться и в 20 минут ожидания. А 5-10 запросов- это даже не 1 строка данных, которые я собираю, то есть 1 бумага.
У меня как-то было более 30 бумаг и для полной прогрузки такого количества запросов гугл таблица без перерыва стояла 2 часа!!!
Иногда и на 2 запросах гугл таблицы могут грузиться 10 минут. Это крайне неудобно, когда тебе надо быстренько зайти, прогрузить информацию и выйти, а тут тебе надо долго ждать.
В Excel таблицах такого ограничения нет и вы можете хоть 1000 бумаг разом грузить, никаких ограничений не будет.
В общем, много кому именно гугл таблицы и нужны, поэтому появилась эта таблица.
На этом статья подходит к концу.
Буду крайне рад, если статья окажется полезна.
