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

На первый взгляд формулы для акций и 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, VK или на рассылку анонсов по почте
  • Есть вопросы? 🗣 Задайте их в комментариях. 🔥
  • Благодарность автору - здесь. 🎁

 
Оцените статью
Поделиться с друзьями
Записки инвестора
Добавить комментарий

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

  1. Алексей

    Спасибо!

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

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

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

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

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

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

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

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

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

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

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

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

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

        Максим, доброго дня! По облигациям таблицу не делали?

        Ответить
        1. Максим (vse-dengy) автор
          Ответить
  6. Александр_

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

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

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

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

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

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

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

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

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

    Ответить
    1. max

      Используй, value(substitute(IMPORTxml(…);».»;»,»)… Тогда точка превращается в запятую, и функция value преобразует текст с запятой в десятичное число. Если с функцией IMPORTxml возникнет ошибка, то лучше применить применить перед IMPORTxml функцию например: value(substitute(ifs(IMPORTxml(…);».»;»,»; «..»))…

      Ответить
  9. Евгений

    Спасибо за цикл статей. Весьма познавательно. Правда для себя нашёл инструмент так сказать «всё в одном». — IZI-invest.ru — система автоматического расчёта портфеля». Бесплатный, в отличии от интелиинвеста.
    Умеет импорт из отчётов брокера. Не давно прикрутили в отдельную графу отражение дохода по дивидендам и купонам. Нужно авторам подсказать вашу идею подтягивать прогноз по див/куп доходности.
    Не сочтите за рекламу.

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

    Добрый день! У меня пару вопросов: в таблице -шаблоне не все фонды, остальные по аналогии делать нужно? У меня не получается. Ссылка с сайта не работает — ошибку выдает, а из таблиц гугл- в конце цена прописана и к другим фондам эту ссылку уже не получается пристроить…если хвостик «);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)

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

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

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

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

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

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

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

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

          Ответить
  12. Искандер

    Спасибо, статья очень полезная. Только с формулой importxml у меня какая-то мистика — когда сохраняю себе ваш шаблон целиком — все работает, когда копирую ячейки или формулу в свою таблицу — пишет синтаксическая ошибка… Ладно, как нибудь разберусь потом.

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

      попробуйте у себя поменять региональные настройки таблицы (с РФ на US или наоборот)

      Ответить
  13. Макс

    Огромное спасибо! Очень ценная информация, без шуток. Блог у Вас тоже очень классный и полезный. Развития Вам и роста!

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

      Благодарю!

      Ответить
  14. Denis

    перестало работать для VTBE

    Ответить
    1. Евген

      VTBE — 1 акция стала дешевле в 10 раз

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

    Спасибо! Как всегда всё доступно и полезно. На просторах интернета, когда переводил статистику из экселя в google-таблицы (опять же воспользовавшись Вашим советом) нашёл функции, чтобы подгружать автоматически идентификаторы торгов, и на основе них автоматически выводить цену финансового инструмента:

    Получение идентификатора (в ячейке D34 значение тикета), формула внесена в Е34:
    =IMPORTxml(«https://iss.moex.com/iss/securities/»&D34&».xml?iss.meta=off&iss.only=boards&boards.columns=secid,is_primary,boardid», «//document//data//rows//row[@is_primary=1]/@boardid»)

    Получение цены финансового инструмента:
    =IMPORTxml(«https://iss.moex.com/iss/engines/stock/markets/»& IFS( or(E34=»TQOB»,E34=»EQOB»,E34=»TQOD»,E34=»TQCB»,E34=»EQQI»,E34=»TQIR»),»bonds», or(E34=»TQTF»,E34=»TQBR»,E34=»SNDX»,E34=»TQIF»),»shares» ) &»/boards/»&E34&»/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE», «//row[@SECID='»&D34&»‘]/@PREVADMITTEDQUOTE»)

    Ответить
  16. Евгений

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

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

    Подскажите, пожалуйста, как получать данные по FXRB и ЗПИФ. При использовании формулы: =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»))
    Выдаёт ошибку: #N/A (нет данных для импорта)

    Ответить
    1. Сергей

      Полезная ссылка для того, чтобы понимать, какие данные можно выдергнуть:
      https://iss.moex.com/iss/engines/stock/markets/shares/
      Примеры запросов:
      ЗПИФ ПНК-Рентал (через isin):
      =IMPORTxml(«https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQIF/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME»,»//row[@SECID=’RU000A1013V9′]/@SECNAME»)
      =IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/shares/boards/TQIF/securities/RU000A1013V9.xml», «/document/data[@id=»»marketdata»»]/rows/row/@MARKETPRICE»)
      VTBX (через короткое название):
      =IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities/VTBX.xml», «/document/data[@id=»»marketdata»»]/rows/row/@MARKETPRICE»)

      Ответить
  18. Евгений

    Не знаю, почему, но не работает данная формула с фондами: копировал формулу, не работает.
    Да даже скопировал формулу из примера и применил в своей таблице к etf tech — выдаёт ошибку.

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

      Проверил. Все работает. Проверьте настройки таблицы (Соединенные штаты).

      Ответить
  19. Константин

    Добрый день!
    Отличная статья.
    А возможно также сделать с фьючерсами на товары (нефть, золото) и акции???

    Ответить
  20. Leonid

    Автор, Вы — гений! Спасибо огромное! Это сильно упрощает жизнь!

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

      Спасибо!

      Ответить
  21. Евгений

    Перестало работать для иностранных акций на Московской Бирже

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

      Проверил. Работает.

      Ответить
  22. Alex

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

    Как пример возьмем ссылку для данных по рос. акциям: https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST
    Получается 272 котировки, которые все запрашиваются, а потом используется только одна, где код бумаги совпадает с указанной в ячейке.
    И так делается для каждой строки.

    Вместо этого можно запросить котировку _ТОЛЬКО_ по бумаге, которая указана в соотв. строке. Для этого в ссылку надо подставить тикер бумаги.
    Например для Сбера (SBER) получаем такую ссылку: https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/SBER.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=LAST

    Ответить
  23. Андрей

    Благодарю. Помогли.

    Ответить
  24. Андрей

    А по облигациям какая формула корректна? Формула от акции даёт ошибку.

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

      Про облигации здесь посмотрите

      Ответить
  25. Сергей

    Огромное спасибо за такое подспорье! По привилегированным все проходит, но на Селигдар-п SELGP споткнулся и не могу понять причину?

    Ответить
  26. Константин

    Максим, спасибо большое за статью. Пользуюсь вашими формулами — очень выручают.
    Появился вопрос, как подтянуть цену российской компании, которая торгуется в виде депозитарной расписки на СПБ бирже. Например SBER@GS ?
    У вас есть на этот счет какое-то решение?

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

      не знаю, не делал такое

      Ответить
  27. Иван

    Здравствуйте!
    А с СПб биржей можно такой же шаблон сделать? Или хотя бы с яхуфинанс котировки акций, торгующихся на СПб бирже, подгружать?

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

      Наверное как-то можно, но я не разбирался с этим ….
      А чем вам гугл финанс не устраивает — те же ин. акции, котировки примерно такие же.

      Ответить
      1. Иван

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

        Ответить
  28. Олег

    Спасибо за подсказки — все получилось), вопрос — как стащить курс доллар/рубль, евро/рубль?

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

      = GoogleFinance(«Currency:USDRUB»)

      Ответить
      1. Олег

        спасибо)

        Ответить
      2. Олег

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

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

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

          Ответить
          1. Олег

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

  29. Аноним

    Почему-то перестали подгружаться курсы валют в эксель. Формулу забиваю такую:
    =ФИЛЬТР.XML(
    ВЕБСЛУЖБА(«https://iss.moex.com/iss/engines/currency/markets/selt/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»);»//document//data//rows//row[@SECID='»&A112&»‘]/@LAST»)
    где в строке А112 забит тикер «USD000000TOD», просто я его получаю из ячейки, а не сразу в формулу вписал.
    аналогично для тикера евро «EUR_RUB__TOD»
    буквально два дня назад все работало.

    Ответить
  30. Константин

    Добрый день!
    Подскажите, сегодня у всех перестала работать формулы XML выгрузки с МосБиржи или это у меня какие-то проблемы? Даже файл-шаблон сейчас показывает #N/A на котировки, при это названия выводит корректно

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

      Так биржа сегодня закрыта до 17 00. Обещали ближе в трем ответить, что будет дальше по торгам.

      Ответить
      1. Константин

        А, понял. Я почему-то думал, что в таком случае он подтягивает исторические данные, как функция Googlefinance.
        Тогда ок, спасибо за статью!

        Ответить
  31. Илья

    Подскажите, пожлауйста:
    У вас сейчас очевидным образом стоимость в табличке посдыхала стоимость
    А можете, пожалуйста, добавить формулу по стоимости закрытия, чтобы цена хоть как-то считалась?

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

      Можете сдернуть другие формулы отсюда. Все работает

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

    Здравствуйте. таблицу скачал, копию сделал. формулы не работают, выдают ошибку: «Нет данных для импорта». подскажите, пожалуйста, в чем может быть причина?

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

      Добавил в таблицу колонку с альтернативными формулами. Можете их взять в работу …

      Ответить
  33. RENAT

    Добрый день!
    Скопировал гугл таблицу, но котировки не подгружаются. Как сейчас правильно выглядит формула?

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

      Из-за того, что многие фонды (акции) не торгуются на бирже, могут вылезать ошибки. А так вроде все работает.

      Ответить
  34. Евгений

    А можете подсказать как в Гугл таблицу вывести актуальный курс доллара/евро мосбиржи?

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

      Формула по доллару:
      = GoogleFinance(«Currency:USDRUB»)

      Ответить
      1. Евгений

        Но ведь это не мосбиржа…

        Как формула для импорта XML выглядеть должна?

        https://www.moex.com/ru/issue/USD000UTSTOM/CETS?utm_source=www.moex.com&utm_term=usdrub

        Ответить