Меню

Importxml google таблицы облигации



Учет инвестиций в гугл-таблицах №5 — выгружаем котировки облигаций: ОФЗ, муни, корпораты и евробонды

Продолжаем использовать гугл таблицы для учета инвестиций (начало здесь). Разберем как можно автоматически получать котировки и название облигаций, обращающихся на Московской бирже. На что обратить внимание и некоторые особенности получения данных и учет в гугл таблицах (Google Sheets).

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

Получаем данные по ОФЗ

Формула вызова цены по облигациям выглядит следующим образом:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/ bonds /boards/ TQOB /securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE» , concatenate ( «//row[@SECID='» , A4 , «‘]/@PREVADMITTEDQUOTE» ) )

В отличии от акций (котировки которых мы получали в этой статье), для облигаций торгующихся на Мосбирже, формула немного отличается:

  • вместо shares пишем bonds;
  • идентификатор меняем на TQOB;
  • остальное без изменений.

В ячейке (в данном примере — A4) вместо тикера (как у акций), прописываем код ценной бумаги.

На примере ОФЗ с погашением в 2034 году. На сайте Мосбирже ищем карточку нужного инструмента. Смотрим код ценной бумаги (SU26225RMFS1) и идентификатор торгов (TQOB) — ссылка.

Не путать Код ценной бумаги и ISIN код. Для Мосбиржи именно для ОФЗ — это разные значения. По другим бумагами коды могут полностью совпадать.

В таблицах это будет выглядеть примерно так:

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

Для вызова названия облигаций используем следующую формулу:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,COUPONPERCENT,MATDATE» , concatenate ( «//row[@SECID='» , A4 , «‘]/@SECNAME» ) )

В итоге таблица принимает более человеческий вид.

Учет ОФЗ в Google Sheets

Корпоративные, муниципальные и еврооблигации

Если применить описанную выше формулу к другим бумагам (не ОФЗ), таблица будет выдавать ошибку. В чем дело?

В идентификаторе режима торгов. У ОФЗ он — TQOB, у не ОФЗ будет другой.

На примере облигаций Система с погашением в 2028 году.

Снова ищем страницу инструмента (облигации) на сайте Мосбиржи.

Идентификатор торгов — TQCB.

Обратите внимание: код ценной бумаги совпадает с ISIN.

Поэтому формула для получения котировок по корпоративным облигациям будет иметь вид:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/bonds/boards/ TQCB /securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE» , concatenate ( «//row[@SECID='» , A7 , «‘]/@PREVADMITTEDQUOTE» ) )

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

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/bonds/boards/ TQCB /securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,COUPONPERCENT,MATDATE» , concatenate ( «//row[@SECID='» , A7 , «‘]/@SECNAME» ) )

Идентичный идентификатор торгов (соотвественно и формулу) будет иметь муниципальные бумаги.

По еврооблигациям (торгующимися в валюте) в формуле используем другой идентификатор.
Для примера возьмем еврооблигации Газпрома в погашением в 2034 году.

Читайте также:  В каком режиме торгуются российские облигации

Ищем на сайте Мосбиржи карточку инструмента (ссылка).

Узнаем Код — XS0191754729 и Идентификатор — TQOD.

Соответственно формулы вызова котировок и названия у еврооблигаций будут иметь вид:

Котировки евробондов:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/bonds/boards/ TQOD /securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE» , concatenate ( «//row[@SECID='» , A13 , «‘]/@PREVADMITTEDQUOTE» ) )

Название еврооблигации:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/bonds/boards/ TQOD /securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME,COUPONPERCENT,MATDATE» , concatenate ( «//row[@SECID='» , A13 , «‘]/@SECNAME» ) )

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

На этом собственно можно было и завершить мою поэму. Но есть парочка особенностей.

Настройка таблицы

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

Обычно номинал российских бумаг — 1 000. Соответственно, если в котировках ОФЗ мы видим 101,587 — это значит, что цена облигации 101,58% от номинала или 1 015,87 рублей.

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

Учет облигаций в Google Sheets

Идентификаторы, код и поиск облигаций на Мосбиржи

Запомнить идентификаторы торгов для разных видов облигаций:

  • TQOB — ОФЗ;
  • TQCB — м униципальные и корпоративные;
  • TQOD — евробонды.

У всех облигаций (кроме ОФЗ), код ценной бумаги совпадает с ISIN. ISIN код всегда указывается рядом с названием бумаги (в приложении брокера). Это я к тому, что не обязательно каждый раз искать на Мосбирже карточку облигаций.

Файл-шаблон

Оставляю ссылку на мой файл со всеми вышеперечисленными примерами и рабочими формулами. Файл работает только на просмотр (без права редактирования).

Чтобы утащить к себе, в верхнем меню выбираем «Файл — > Создать копию». Сохраняем и дальше тираним и переделываем под себя.

Очень приветствуются замечания, пожелания и советы по улучшению.

Источник

Учет инвестиций в гугл-таблицах №2 — получаем котировки акций и ETF с Мосбиржи

В прошлой статье мы разобрали способ получения котировок акций ( и ETF) с помощью функции GoogleFinance. Правда по некоторым российским бумагам получении инфы таким образом невозможно. Речь идет о привилегированных акциях (типа Сбера, Татнефти, Сургута) и практически всех российских биржевых фондах (FXIT, VTBX, SBSP и так далее).

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

Котировки российских акций

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

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST» , concatenate ( «//row[@SECID='» , B5 , «‘]/@LAST» ) )

В формуле B5 (выделено красным) — это адрес ячейки, где прописан тикер акции (у вас адрес может быть другим). В остальном формула остается неизменной для всех российских акций.

Читайте также:  Ближайший выпуск облигаций втб

Вот как это выглядит в таблице:

Получение котировок для российских привилегированных акций

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

Для вывода названия акций (компании) используем формулу:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME» , concatenate ( «//row[@SECID='» , B5 , «‘]/@SECNAME» ) )

Также при необходимости меняем только адрес ячейки с тикером (в моей формуле — это B5).

Получаем цену ETF и БПИФ в рублях / долларах и евро

Если мы захотим получить котировки российских биржевых фондов по вышеописанной формуле, просто заменив тикер акции на тикер ETF (или БПИФ), то нас постигнет фиаско. Формула будет выдавать ошибку.

Для биржевых фондов нужна формула следующего вида:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST» , concatenate ( «//row[@SECID='» , B14 , «‘]/@LAST» ) )

Как всегда, при необходимости меняем адрес ячейки с тикером.

Получаем название и котировки российских биржевых фондах в Гугл-таблицах

На первый взгляд формулы для акций и ETF абсолютно идентичны. Правда есть небольшое отличие всего в четыре буквы. Для вызова акций мы использовали в формуле TQBR, а для ETF — заменили на TQTF.

Что это за зверь такой?

TQBR или TQTF — это идентификатор режима торгов. Узнать его можно на сайте Мосбирже. Идентификатор прописывается в карточку каждого инструмента. Для примера возьмем ETF FXRU. Находим его через поиск на сайте бирже. И видим TQTF.

Соответственно прописываем в формулу нужный идентификатор.

Дабы сэкономить читателю время, в формуле вызова котировок используем идентификатор:

  • для акций — TQBR;
  • для рублевых ETF — TQTF;
  • для ETF с расчетам в евро — TQTE;
  • для ETF с расчетам в долларах — TQTD.

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

В таблице накидал несколько примеров вызова цен по валютным фондам.

Выводим котировки биржевых фондов РФ в долларах и евро

Цена иностранных акций на Мосбирже

На Московской бирже обращаются иностранные акции, номинированные в рублях.

В отличии от оригинала, они имеют тикер дополненный «-RM». То есть для Apple, рублевый тикер будет AAPL-RM, для Facebook вместо FB — пишем FB-RM и так далее.

Читайте также:  Аукционов облигаций федерального займа

Формула вызова котировок имеет следующий вид:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/ foreignshares /boards/ FQBR /securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST» , concatenate ( «//row[@SECID='» , B47 , «‘]/@LAST» ) )

Красным пометил отличия от предыдущих формул:

  • Как обычно — меняем адрес ячейки с тикером под себя.
  • Рублевые иностранные акции имеют идентификатор торгов — FQBR
  • Вместо shares ставим foreignshares (иностранные ценные бумаги).

Настройка таблицы или возможные проблемы

Иногда про получении данных о котировках по API (по вышеописанным формулам) может выходить ошибка. Часто причина ошибки — неправильные настройки таблицы. Данные передаются в формате дробных чисел с точкой (например — 0.55). В настройках вашей таблицы может по умолчанию стоит отображение чисел с запятой (0,55).

Для исправлении ошибки выбираем в меню «Файл->Настройки таблицы».

На вкладке «Общие» в поле Региональные настройки» нужно выбрать Соединенные Штаты.

Нужно знать

Не забываем про задержку во времени. Так же как и ГуглФинанс, котировки по API Мосбиржи подгружаются примерно с 20 минутным опозданием.

На открытии торгов Мосбиржи (в 10 утра по МСК) — некоторое время все (или многие) котировки могут быть недоступны и формула будет выдавать ошибку.

В приоритете (там где это возможно) лучше использовать функцию GoogleFinfnce. Если в таблице много инструментов, то загрузка по API может быть очень долгой. В этом плане Гуглфинанс работает пошустрее. Поэтому используем API Мосбиржи только там, где Гугл бессилен: префы российских компаний и биржевые фонды.

Для акций у которых котировки рассчитываются до более 2-х знаков после запятой, тоже лучше использовать API. В этом случае цена будет передаваться более точная.

Как пример, акции ВТБ. Текущие котировки — 0.04244. GoogleFinance округлит цену до 0.04.

Из подобного есть еще Русал, ИнтерРао, Русгидро, АФК Система, Россети, ФСК ЕЭС и много чего еще.

Готовый шаблон

Как всегда прикладывают файл-шаблон со всеми прописанными формулами. Таблица только для просмотра. Для использования сохраните себе копию (в верхнем меню: «Файл — > Создать копию»).

Комментарии, замечания, пожелания приветствуются!

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

Как оказалось после публикации статьи — при копировании формул вызова котировок выходила ошибка. Проблема заключалась в неправильном отображении блогом кавычек. Вместо двойных кавычек (сверху и снизу по две запятых » . текст. » система отображала французские (или кавычки-елочки). А для гугл-таблиц — это ошибка. Сейчас настроил правильное отображение и все работает.

Всем огромное спасибо за замечания!

Источник