Учет инвестиций в гугл-таблицах №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.

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

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

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

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

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

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

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

Удачных инвестиций!