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

Прошлую итерацию этого гайда делал в 2024 году. Пришло время сильно дополнить его новыми фишками.

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

Чего только стоил тот факт того, что можно не подгружать по каждой бумаге режим торгов и это оказался вообще ненужный элемент. На просторах интернета многое смотрел, но так и не нашёл ни одной статьи с этими формулами без ненужного подтягивания режима торгов.

Нашёл большое количество решений по реализации моих хотелок и делюсь с вами.

На какие вопросы ответит эта статья:

• Зачем нужно использовать Excel таблицы при инвестировании

• Подготавливаем Excel к работе

• Принцип работы формул с привязкой к API Московской биржи

• Как работают формулы Excel с ссылкой на API Мосбиржи

• Как устроена таблица для отслеживания информации по облигациям с фиксированным купоном

• Как устроена таблица для отслеживания информации по флоатерам

• Как устроена таблица для отслеживания информации по ОФЗ

• Как устроена таблица для отслеживания информации по акциям

• Как я делаю карту рынка со всеми выпусками облигаций на рынке

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

• Выводы

   

Начну с того, что если вы читаете эту статью, то вам вряд ли надо долго объяснять плюсы и минусы Excel, вы и так всё знаете.

С водой закончили, переходим к важной информации.

Таблицы, используемые в статье, вы найдёте тут: t.me/philippovich_bonds

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

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

Есть иные программы, но они не 1 в 1 как классический Excel. Если кто-то из читающих найдёт способ реализовать всё тоже самое на Excel, то буду сильно благодарен!

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

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

Самый часто задаваемый вопрос про таблицы – это #ЗНАЧ! или #ИМЯ в полях с формулами.

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

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

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

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

Чтобы сохранить в нужном формате документ идём по седеющему пути:

Файл → Сохранить как → Выбираете удобное вам место → Тип файла выбирайте Книга Excel как на скриншоте.

3-      Часто Excel может спрашивать про WEBSERVICE при входе и их нужно включать, так как без них данные с интернета не будут подтягиваться

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

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

Альтернативный способ — это пойти по пути: Данные -> Обновить всё

Тут уж кому как удобнее.

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

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

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

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

Это самая распространённая причина ошибок.

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

В некоторых случаях помогают разные вариации: с галочкой или без, точка с запятой на разных местах стоят. В общем, если не получится, то попробуйте все варианты перебрать (пробуете вариант, сохраняете, обновляете таблицу (как в пункте 4) и смотрите результат).

Принцип работы формул с привязкой к API Московской биржи

Все ссылки работают через API Московской Биржи.

Чтобы понять, что такое API проведу аналогию с рестораном. База данных московской биржи — это кухня ресторана, мы и в ресторане, и в финансовом мире- клиенты. Как, что, кем готовится на кухне или в базе данных биржи нас не волнует, нам важен конечный продукт. В ресторане официант принимает от нас информацию о том, что мы хотим, передаёт на кухню, там забирает заказ и приносит нам готовый заказ. API делает тоже самое, мы ему говорим что хотим, он делает все манипуляции с базой данных Мосбиржи и приносит нам готовую информацию.

Чтобы начать пользоваться таблицей Excel необходимо лишь научиться работать с API, что мы сейчас и сделаем.

Что нам отдаёт iss московской биржи

Ссылка на iss выглядит так: https://iss.moex.com/iss/engines/stock/markets/

При переходе по ней мы увидим следующую картину:

Если прочитать ссылку, то увидим, что она заканчивается на «markets», а на выдаче мы получаем список этих самых «маркетов» или по-русски «рынков».

Тут у нас и рынок акций, и рынок облигаций, и рынок OTC и так далее.

Чтобы найти, что нам отдаёт iss по рынку облигаций нам нужно добавить в формулу приписку bonds и теперь ссылка будет такой: https://iss.moex.com/iss/engines/stock/markets/bonds

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

В нём вы найдёте ссылки на информацию по НКД бумаги, режиму торгов, длительностью купона и так далее. Очень удобно и это далеко не единственный раздел. Есть также marketdata и прочие.

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

При добавлении приписки YIELD ссылка вам отдаст YTM конкретной бумаги.

Теперь мы можем дополнить формулу новой припиской и конкретным ISIN.

Для примера возьмём ISIN Контрол Лизинг выпуск 2: RU000A1086N2

Получается следующая формула: https://iss.moex.com/iss/engines/stock/markets/bonds/securities/RU000A1086N2

При переходе по ней мы увидим такую информацию:

Тут отображаются и режим торгов, и краткое название бумаги, её YTM, дата следующего купона, объём торгов и так далее. В общем, всё, что нам нужно и даже больше.

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

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

https://iss.moex.com/iss/engines/stock/markets/bonds/securities/RU000A1086N2/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SHORTNAME

При переходе по ссылке видим такую картину:

Мы искали краткое название бумаги, мы его нашли.

Находить информацию в iss Мосбиржи научились, теперь надо разобраться как научить Excel находить и отображать тоже самое.

Как работают формулы Excel с ссылкой на API Мосбиржи

Все формулы выглядят одинаково и в них меняется всего пара деталей.

Напомню, что таблицу вы можете скачать тут: t.me/philippovich_bonds/1445

Так выглядит формула по получению краткого названия облигации Контрол Лизинг выпуск 2:

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

Обратите внимание, что для вывода краткого названия бумаги нужно указать SHORTNAME не только в ссылке на iss Мосбиржи, но и в самом конце формулы.

Это нужно, потому что iss отдаёт нам данные с большим количеством вспомогательной информации, а нам не нужна информация находящаяся iss по пути document//data//rows//row.

Если вставить её в ячейку Excel, то после нажатия на Enter вам выдаст текст “CTRLлиз1Р2”. На скриншоте под цифрой 1 выделено поле куда вставил формулу, а под цифрой 2 показано, что в ячейке A1 появилась надпись CTRLлиз1Р2.

На этом этапе у вас может возникнуть ошибка из-за кавычек. Заметил, что где-то работают кавычки вида «», а где-то они должны быть такого формата " ".

Ссылка состоит из двух частей.

1. Формула ФИЛЬТР.XML внутри которой используем ещё формулу ВЕБСЛУЖБА;
   
   
2. Внутри всего этого у нас находится ссылка на iss московской биржи, с которым мы разобрались ранее.

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

Делается это добавлением ссылкой на ячейку с ISN. В таком случае мы меняем только ISIN в 1 ячейке и все остальные ячейки автоматом меняют данные.

Ссылка будет выглядеть так:

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

Вместо ISIN мы добавляем ссылку на конкретную ячейку с этим ISIN, но просто выделить ячейку не получится. Ссылку нужно обернуть в "&A1&" – кавычки и два символа, похожие на человечка, который сидит на попе и тащит себя руками вперёд 😊

В Excel это будет выглядеть вот так:

Но не все данные есть в поле securities и тогда формулу также нужно чуток изменить.

Например, в моём случае для отдачи цены я беру данные из marketdata.

В таком случае формула меняется чуть-чуть:

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

Часть securities&securities меняем на marketdata&marketdata и в конце делаем приписки LAST так как по нему отдаётся информация о цене бумаги.

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

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

Вот так сейчас выглядит главный лист моей таблицы:

Пойду по порядку.

Как устроена таблица для отслеживания информации по облигациям с фиксированным купоном

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

Первые 2 столбца мы с вами выше разбирали, а в столбец с количеством бумаг вам нужно руками вбить сколько у вас бумаг этой компании. Из iss такое подтянуть нельзя)

Размер купонных выплат в процентах и рублях

Формулы сильно похожи друг на друга.

Формула для вывода купона в процентах:

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

Формула для вывода купона в рублях:

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

Количество выплат в год

Формула:

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

Если перейти по ссылке, то нам отдаст 30, а не 12.

Чтобы получить 12 я разделил количество дней в году, то есть 365 на результат, который нам отдаёт ссылка.

Однако, при делении 365 на 30 мы получим 12,167, что также неудобно для глаза.

Чтобы убрать это я уменьшил разрядность чисел.

Находится она здесь:

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

В итоге получился такой формат. Если же вам всё же хочется получать период выплат в днях, то в начале формулы уберите «365/»

YTM (Yield To Maturity)

Формула:

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

Цена

Формула:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/»&A3&"/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/securities/»&A3&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=FACEVALUE");"//document//data//rows//row/@FACEVALUE")

ТКД (Текущая Купонная Доходность)

В iss этого значения я не нашёл и поэтому считаю его формулой.

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

Получившееся значение делю на цену бумаги в рублях, которую получаю умножением номинала на цену бумаги в процентах (чтобы всё правильно работало обратите внимание, что ячейке I3 приписал %. Так получается 1000*103,75% и равняется 1037,5. Если же не вставить %, то выйдет 103750).

Получившееся значение умножаю на 100, чтобы привести значение к процентам.

Формула:

=(E3*F3)/(J3*I3%)*100

Вот так она выглядит в Excel:

Купон и количество выплат перемножаются первыми, и они отмечены синим и красным цветами.

Всё это делится на значение, получившееся после умножения цены на номинал.

Стоимость бумаг в портфеле

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

Доля в портфеле

Отдельно просуммировал стоимости всех бумаг в портфеле. Про это будет позже.

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

Дата следующего купона

Формула:

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

Сумма выплат без налога и с налогом

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

Для получения суммы выплат с налогом полученную ранее сумму умножил на 0,87.

Налог у нас 13%, и чтобы вычесть его проще всего умножить сумму на 0,87.

Если у вас налог 15%, то вам надо будет умножить на 0,85 и так по аналогии до 22%.

Дата оферты

Формула:

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

Тут я добавил ещё одну формулу под названием ЕСЛИОШИБКА.

Если оферты нет, то iss отдаст пустое поле и Excel воспримет это как ошибку и выдаст #ЗНАЧ!, а нам это не нужно.

Формула ЕСЛИОШИБКА делает так, что если будет ошибка, то в ячейке будет выведено "-".

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

Дата погашения

Формула:

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

Как устроена таблица для отслеживания информации по флоатерам

У меня есть подготовленная таблица исключительно со всеми флоатерами: t.me/philippovich_bonds/1446

Новыми выпусками она не пополняется автоматом, и я это делаю руками при необходимости.

Для поиска последних флоатеров перехожу на https://smart-lab.ru/q/bonds/order_by_issue_date/desc/?bonds_variable=1, в настройках выбираю показывать только флоатеры и сортирую их так, чтобы первыми стояли самые последние выпуски.

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

Выглядит это так:

Если дважды ввожу одинаковый ISIN, то обе бумаги подсвечиваются так как в таблице есть дубли.

Ниже текст будет идти про основную таблицу, где есть чуть-чуть флоатеров

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

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

Для того, чтобы автоматически подтягивались данные по КС и RUONIA использовал экспорт данных с сайта ЦБ.

Как экспортировать данные с сайта ЦБ в Excel

Для экспорта данных по КС идём по следующему пути:

Данные -> Получение внешних данных -> Из интернета

В поисковую строку вводим ссылку на сайт ЦБ с ключевой ставкой https://cbr.ru/hd_base/KeyRate/

Нажимаем пуск и у вас вылезет окно «Ошибка сценария». Нужно будет несколько раз нажать «Да» и в конечном итоге у вас откроется нужная страница сайта.

Дальше пролистываем вниз и находим нужную нам таблицу с данными по ставке. Нажимаем на эту таблицу, она выделится синим и после нажимаем «Импорт».

ВАЖНО!

Данный инструмент часто сильно тормозит и не отвечает на нажатие. Единственный вариант, который помогает – это подождать пока всё прогрузится.

После того как вы нажмёте «импорт» вам предложит куда перекинуть данные. В выделенную ячейку или на новую страницу. Мне удобнее на новую страницу.

На лист в Excel будет импортирована следующая информация:

Эти данные выглядят как обычные, но на самом деле они обновляются также по команде Ctr + Alt + F9

При изменении ставки данные будут также меняться.

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

Нашёл решение через формулу =ПОДСТАВИТЬ(B2; ","; ".")

Она меняет запятую на точку и при обновлении страницы всё остаётся на своих местах.

По такой же логике делаем и с RUONIA.

Импортируем заново, но уже ссылку https://www.cbr.ru/hd_base/ruonia/

Делаем всё тоже самое, что делали для импорта данных по КС и в итоге должно получиться следующее:

Нам нужна информация из ячейки C2. Её переделываем уже знакомой формулой и финальный результат находится в ячейке F2.

Теперь вернёмся на лист с облигациями.

Ячейка, в которой я высчитываю размер купона выглядит следующим образом:

Формула: =(ЕСЛИ(D29=«КС»;'Ставка ЦБ'!$D$2;'Ставка RUONIA'!$F$2))+E29

Что она означает:

Если в ячейке «базовый показатель» указано КС, то берём данные с листа «Ставка ЦБ» и прибавляем к нему значение из ячейки «Спред». Если в ячейке «Базовый показатель» находится не «КС» (в нашем случае либо КС, либо RUONIA), то берём значение из листа «RUONIA» и прибавляем к ней спред.

Примерный купон

Его я реализовал формулой =((K29/100)*F29%)/H29*100

Она означает следующее:

Номинал делим на 100, чтобы получить 1% от номинала и умножаем на рассчитанный нами ранее примерный процент годовых.

Таким образом мы получаем сколько мы получим рублей за год при текущей ставке.

Далее делим на количество выплат за год и умножаем на 100.

В итоге в случае с рассматриваемой бумагой получилось значение 14,59 рублей на 1 выплату. Зашёл посмотреть у брокеров и там такое же значение.

Остальное во флоатерах всё типовое и мы это рассматривали ранее.

Как устроена таблица для отслеживания информации по замещающим облигациям

В замещающих облигациях главным вопросом встаёт отображение в процентах, в валюте и в рублях.

Для этого нам потребуется экспортировать данные с сайта ЦБ как и в предыдущих случаях со ставкой ЦБ и RUONIA

Экспортируем данные с сайта ЦБ с валютами: https://cbr.ru/currency_base/daily/

В итоге у нас в новом листе должна быть следующая картина:

Это таблица с курсами валют, которая обновляется одной кнопкой.

Валюта

Формула: =ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/»&A35&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=FACEUNIT");"//document//data//rows//row/@FACEUNIT")

Цена в рублях

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

Затем это значение умножаем на курс валют.

Подтягивание данных о валюте сделал через ВПР.

Оно смотрит на то, какая валюта указана в ячейке с валютой и затем идёт на лист с курсами валют с сайта ЦБ, ищет там нужную валюту и возвращает нам значение.

В нашем случае ВПР видит, что нужно искать USD и отдавать точное значение, которое стоит напротив USD на странице с валютами.

Если есть сложности с тем, как работает ВПР, то по первой же ссылке в интернете найдёте как с ним работать.

В остальном проблем не должно возникнуть.

Как устроена таблица для отслеживания информации по ОФЗ

Тут глобально всё точно также работает, но есть нюанс с режимом торгов.

У ОФЗ может быть сразу же 2 режима торгов и это всё путает так как Excel не понимает какой конкретно нужно использовать.

И формула тут тоже не поможет так как она возвращает 2 значения и это ломает всё.

Единственный способ, который я нашёл – это просто вписывать режим торгов руками.

Нам нужен режим TQOB.

Формула: =ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B47&"/securities/"&A47&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SHORTNAME");"//document//data//rows//row/@SHORTNAME")

Тут среди ссылки добавляется boards/"&B47&"/, что ведёт нас на ячейку с вписанным нами режимом торгов.

В остальном всё, как и везде.

Как устроена таблица для отслеживания информации по акциям

Тут также всё по аналогии, как и с облигациями, но с минимальными изменениями

Вместо ISIN мы используем тикер.

Сама формула теперь имеет вид:

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

По большому счёту во всей формуле меняется слово bonds на shares.

Тоже самое можно делать с фондами, индексами и прочим.

Как я делаю карту рынка со всеми выпусками облигаций на рынке

У себя в телеграм каждый понедельник публикую карту рынка со всеми выпусками и разбивкой по рейтингам: t.me/philippovich_bonds/1444

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

Ссылка на таблицу, которую использую: t.me/philippovich_bonds/1449

Расписывать не стал так как есть подготовленное видео, где я рассказываю и показываю, как делаю эту таблицу + рассказываю, как её использовать.

Видео на 45 минут найдёте тут: https://youtu.be/dnndj3OhdA8

Ссылка на сайт Мосбиржи с вшитыми параметрами для таблицы: www.moex.com/s2644#/?bg%5B%5D='stock_tplus','stock_t0'&sec_type%5B%5D='stock_corporate_bond','stock_exchange_bond'&faceunit%5B%5D='rub'¤cyid%5B%5D='rub'&coupon_value%5B%5D=0.001875,1890410.96&qi=''&coupon_percent%5B%5D=5.96,35

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

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

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

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

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

Подготовка

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

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

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

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

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

Таблицу для примера можете эту использовать: https://docs.google.com/spreadsheets/d/1QzRUBpXzVyKt-zq0n0NJaNFAIzg0JiUpuSgIgyCFlys/copy

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

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

2- у гугл таблиц формулы немного отличаются

=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/bonds/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME», СЦЕПИТЬ("//row[@SECID='",A2,"']/@SECNAME"))

Если вы дошли до этого момент и всё поняли из предыдущего блока статьи, то у вас не должно возникнуть вопросов как это использовать.

Почему мне не нравится работать в гугл таблицах

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

Этой вещью является ограничение на количество отправляемых запросов. Бесконечные Loading...

Из-за этого отправка более 5-10 запросов разом может превратиться и в 20 минут ожидания. А 5-10 запросов- это даже не 1 строка данных, которые я собираю, то есть 1 бумага.

У меня как-то было более 30 бумаг и для полной загрузки такого количества запросов гугл таблица без перерыва стояла 2 часа!!!

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

В Excel таблицах такого ограничения нет, и вы можете хоть 1000 бумаг разом грузить, никаких ограничений не будет.

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

Конец

На этом я рассказал всё, что знаю по работе с таблицами Excel.

Если знаете как можно улучшить таблицу, то пишите, обсудим.

Буду очень рад вашему лайку/подписке

Также подписывайтесь на мой телеграм по облигациям: t.me/philippovich_bonds

Если есть вопросы, то лучше всего писать мне в личные сообщения в телеграм, там я отвечу быстрее всего: @filippovich_andreyi

Всем спасибо за внимание!