Как работать с таблицами Excel. Как работают формулы?

В статье я расписываю как пользоваться Excel таблицей с подтягиванием информации из 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 + ВЕБСЛУЖБА
• Ссылка

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

Взгляните на ссылку:

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(ВЕБСЛУЖБА("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 и режим торгов. Это нужно, чтобы далее можно было просто протянуть ячейку и каждый раз не вписывать новые данные.

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

https://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

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

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

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

С режимом торгов и идентификатором(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(ВЕБСЛУЖБА("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(ВЕБСЛУЖБА(«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)

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

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

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

Также не забывайте про лайки и подписку на канал 👍

Больше всего постов я пишу в своём телеграм канале: https://t.me/filippovich_money