Блог им. AndreyFilippovich

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

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

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

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

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

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

 

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

  • Подготовка таблицы Excel к работе
  • Принцип работы формул с привязкой к API Московской биржи
  • Пример практического использования таблицы
  • Работа с ОФЗ в Excel
  • Работа с гугл таблицами
 

Подготовка таблицы Excel к работе

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

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

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

 

Сразу скажу, что эта инструкция для компьютеров на Windows. Apple считает, что продукты Microsoft на их компьютерах не нужны(((

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

Также инструкции не подойдут для тех, кто читает с телефона. В статье речь идёт только про десктопные версии таблиц

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

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

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

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

3- Часто Excel может спрашивать про WEBSERVICE при входе и их нужно включать, так как без них данные с интернета не будут подтягиваться
Ведение облигационного портфеля в Excel и «Google Таблицах» с привязкой к API Московской биржи

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

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

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

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

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

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

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

В некоторых случаях помогают разные вариации: с галочкой или без, точка с запятой на разных местах стоят. В общем, если не получится, то попробуйте все варианты перебрать (пробуете вариант, сохраняете, нажимаете комбинацию клавиш 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.
Ведение облигационного портфеля в Excel и «Google Таблицах» с привязкой к API Московской биржи

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

 

Функция состоит из 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 и «Google Таблицах» с привязкой к API Московской биржи

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

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

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

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

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

 

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

 

 iss.moex.com/iss/engines/stock/markets/bonds/boards/РЕЖИМТОРГОВ/securities/ИДЕНТИФИКАТОР/securities.xml?iss.meta=off&iss.only=РАЗДЕЛ&РАЗДЕЛ.columns=ДАННЫЕ

 

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

 

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

 

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

 

На скриншоте отметил что нам нужно смотреть, если мы хотим получить краткое наименование ценной бумаги.
Ведение облигационного портфеля в Excel и «Google Таблицах» с привязкой к API Московской биржи
В области 1 указано, что в формулу были добавлены нужные данные и раздел, а в области 2 мы видим сокращенное наименование эмитента.

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

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

 =ФИЛЬТР.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

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

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

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

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

 =ФИЛЬТР.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)

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

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


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

Пример практического использования таблиц

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

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

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

Ведение облигационного портфеля в Excel и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к 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 и «Google Таблицах» с привязкой к API Московской биржи

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

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

 

Работа с ОФЗ в Excel

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

У ОФЗ же 2 режима торгов. API Московской биржи отдаёт оба этих режима, но работает исключительно с одним: TQOB
Ведение облигационного портфеля в Excel и «Google Таблицах» с привязкой к API Московской биржи

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

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

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

В поле режима торгов по ОФЗ нет формул, а просто вписанный руками текст «TQOB»

Работа с гугл таблицами

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

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

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

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

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

Подготовка

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

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

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

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

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

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

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

Как работать с таблицей?


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

 

В гугл таблицах есть несколько отличий от таблиц excel:

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

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

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

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

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

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

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

 =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/

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

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

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

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

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

 

 =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 бумаг разом грузить, никаких ограничений не будет.

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

На этом статья подходит к концу

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

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

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

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

Дзен

VCRU

Пульс

★91
16 комментариев
Андрей Филиппович, а у Вас есть статьи про котировки акций в Excel?
Воронов Дмитрий, такого нет, но могу в будущем посмотреть
Андрей Филиппович, было бы очень интересно. 
Уже писал. С гуглом такая проблема потому что при погрузки одной формулы импорт пересчитываются все формулы. У гугла есть возможность через скрипты писать запросы, можно ставить триггеры (обновление например каждый час) и отправка, например, в телеграмм уведомление. Хотите чтоб каждый вечер приходило в виде картинки или пдф скриншот таблицы в телеграм -это легко сделать. Хотите чтоб по нажатию одной кнопки в телеграмме это делалось — тоже можно.
Добрый вечер! Подскажите формулы для excel будут работать в других офисах? Типа "мой офис" или "open office"?
олег самсонов, в этом подсказать не могу ничего

олег самсонов, У меня получилось в LibreOffice. Нет такой красивой таблицы как у автора, но получить нужный мне параметр по нужным акциям удалось. Вот например как у меня выглядел запрос последней цены:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(«https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/»&A3&"/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=LAST");"//document//data//rows//row/@LAST")
Вместо "&A3&" можно просто написать тикер акции, например VTBR

PS.
Местный редактор заменяет кавычки как ему нравится. В формуле у меня везде вот такие ".
avatar
Конечно, всё это очень избыточно. Для каждого столбца делается отдельный запрос. Я лично делаю 1 запрос, парсю на пхп и кладу результат в базу и в файлы. Но конечно посмотреть содержимое этой базы можно только если написал для этого соотв. скрипты тоже. 
avatar
Слишком сложно. Проще с квика. Вывод любой таблицы через DDE
avatar
когда вставляю формулу в ячейку эксель ошибку пишет, что формула не верна и не дает ее вставить. точку и запятую по всякому в настройках менял, не помогает
avatar
efor, Скиньте сюда формулу, которую пытаетесь вставить

Андрей Филиппович, собственно =ФИЛЬТР.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")
avatar
efor, попробуйте добавить https:// перед iss.moex в начале формулы
ФИЛЬТР.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")
avatar
Огромное спасибо! Плюсик Вам в карму +++ ))
avatar
Спасибо за подробные разъяснения по API и затягиванию разделов/данных!
Полезно)
avatar
День добрый! Помогите разобраться пожалуйста)) У меня не подтягивает цену по нескольким облигациям. т е по всем подтягивает, а например
RU000A105VP7 РКК БО-01
RU000A105ZW4 РКК БО-02
RU000A106GZ5 РКК БО-03
RU000A1053R3 Феррони01
нет. Что с ними не так? Формула
=ФИЛЬТР.XML(ВЕБСЛУЖБА(СЦЕПИТЬ(«www.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities/»;B69;".xml?iss.meta=off&iss.only=marketdata&securities.columns=LAST"));"//document//data//rows//row/@LAST")*10+ПОДСТАВИТЬ(ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities/»&B69&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,ACCRUEDINT");"//document//data//rows//row/@ACCRUEDINT");".";".").
И еще — не могу разобраться с фондами. Например
RU000A101EJ5 Индекс Мосбиржи
RU000A1014L8 ОВИМ Ликвидность. Судя по ISIN должна быть формула облигаций. Не работает(((

теги блога Андрей Филиппович

....все тэги



UPDONW
Новый дизайн