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

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

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

Итак, поехали!

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

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

=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
Получаем название и котировки российских биржевых фондах в Гугл-таблицах

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

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

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

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

 

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

 

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

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

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

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

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

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

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

В отличии от оригинала, они имеют тикер дополненный "-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.

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

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

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

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

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

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

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

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

Подпишись на рассылку лучших статей блога

Логотип telegramЗа обновлениями в этой и других статьях теперь можно следить на Telegram-канале: @vsedengy.
Оцените статью
Поделиться с друзьями
Записки инвестора
Добавить комментарий

Внимание! Ваш комментарий будет опубликован после модерации.

  1. Алексей

    Спасибо!

    Ответить
  2. Александр_

    Не для средних умов свистопляска… А что гугловцы не могут сделать универсальную финансовую функцию? В поддержку не писали?

    Ответить
  3. Александр_

    А для обычного Экселя есть подключаемая библиотека с гуглфинанс?

    Ответить
  4. Алексей

    Что-то не работает формула, пишет, что не хватает закрывающей скобки и ссылка на образец таблицы тоже недоступна(

    Ответить
    1. Максим (vse-dengy) автор

      Поправил! кавычки трансформировались в неправильные — которые гугл не мог распознать. Из-за этого и была ошибка.

      Ответить
  5. Алексей

    И по облигациям хотелось бы узнать, можно ли сделать выгрузку? На сайте биржи у них не прописан режим торгов…

    Ответить
    1. Максим (vse-dengy) автор

      по облигашкам чуть позже напишу алгоритм. там тоже есть тонкости

      Ответить
  6. Александр_

    А что нет доступа к файлу шаблона?

    Ответить
    1. Максим (vse-dengy) автор

      пардон! Слетела ссылка. Поправил

      Ответить
  7. Юрий

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

    Ответить
    1. Максим (vse-dengy) автор

      Прошу прощения! Поправил ссылку!

      Ответить
  8. Станислав

    Вместо IMPORTxml(…) лучше использовать VALUE(IMPORTxml(…)), так как IMPORTxml возвращает текстовое значение, а VALUE преобразует это текстовое значение в число. Если этого не сделать, то при использовании текстового значения в формулах будет использоваться «автоматическое форматирование», которое в некоторых случаях работает некорректно. Столкнулся с такими проблемами, когда текстовое значение заканчивалось на .5 (например: 1234.5).

    Ответить
  9. Анна

    Добрый день! У меня пару вопросов: в таблице -шаблоне не все фонды, остальные по аналогии делать нужно? У меня не получается. Ссылка с сайта не работает — ошибку выдает, а из таблиц гугл- в конце цена прописана и к другим фондам эту ссылку уже не получается пристроить…если хвостик «);1834) удаляю, то ошибка опять же.
    =ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION(«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='»»,B17,»»‘]/@LAST»»))»);1834)

    Ответить
  10. Дмитрий

    Максим, по префам формула не работает. Ни Сбер, ни Сургут — пишет
    #Н/Д. Хотя, когда перехожу по ссылке в формуле, попадаю на страницу биржи с котировками.

    Ответить
    1. Максим (vse-dengy) автор

      перепроверил — котировки подгружаются

      Ответить
      1. Дмитрий

        Да, это у меня с Мосбиржи не подгружаются данные. Странно. С гуглфинанс всё впорядке.

        Ответить
        1. Дмитрий

          Разобрался — тикер был прописан строчными буквами. Гуглфинанс понимает, а Мосбиржа — нет.
          Спасибо Вам за просветительскую деятельность в области финансов).

          Ответить