Котировки акций в гугл таблицах

Получение цен акций и облигаций

Гугл таблица с примерами автоматического получения
Гугл таблица с примерами автоматического получения цен акций и облигаций

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

Видео

Отслеживание акций в действии

  Если вы хотите просмотреть ежедневные цены закры

Если вы хотите просмотреть ежедневные цены закрытия для конкретной недели, например недели от 15 октября 2018 года, вы укажите этот диапазон дат в третьем и четвертом аргументах. Код для этого становится =GOOGLEFINANCE("NASDAQ:FB","price",DATE(2018,10,15),DATE(2018,10,20)) . Обратите внимание, что просмотр исторических данных расширяет сгенерированную информацию в массив данных, который занимает соседние ячейки.

Вы также можете использовать функцию для автоматического создания данных для списка акций. Просто напечатайте тикеры в столбце, затем используйте ячейки в первом аргументе. Поскольку наш код тикера находится в ячейке C4, вы должны набрать =GOOGLEFINANCE(C4,"price") . Ниже приведен список акций с их соответствующими текущими ценами.

  Если вы хотите отслеживать список атрибутов, вы

Если вы хотите отслеживать список атрибутов, вы можете напечатать их в отдельных ячейках, как на картинке выше. Затем вы можете связать второй аргумент с ячейкой с именем атрибута. Для ценовой ячейки для NYSE: IBM в приведенном ниже примере формула будет =GOOGLEFINANCE(C$2,$B5) .

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

Функция GoogleFinance отлично работает с иностранными акциями. С получением котировок, по компаниями входящим в состав различных мировых индексов, проблем не возникает.  Если компания более-менее крупная, так или иначе, цену акции можно выдернуть.

А вот с российскими бумагами могут возникнуть определенные сложности (хотя в большинстве случаев решаемых).

Сначала о хорошем. Ниже пример того, что GoogleFinance прекрасно распознает котировки российских бумаг.

Автоматическое получение котировок российских акци
Автоматическое получение котировок российских акций

Теперь про сложности, с которыми можно столкнуться:

Проблема №1. GoogleFinance не определяет цены на привилегированные акции (от слова совсем). Под раздачу попали префы Сбера, Башнефти, Татнефти, Ростелекома, Сургутнефтегаза.

На картинке ниже видно, что котировки обычных акций — система определила, а по префам возникли ошибки.

Невозможность получения котировок префов через Goo
Невозможность получения котировок префов через GoogleFinance

Проблема №2. Нельзя вытянуть котировки большинства биржевых фондов обращающихся на Мосбирже. Аналогично ситуации с префами — будет выдавать ошибку.

Проблема №3. Функция может неверно определять или «путать» цены с другими тикерами, акции которых обращаются на других биржах.

Первые две проблемы можно обойти через API Московской биржи (в конце статьи есть ссылка как это сделать).

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

Как пример, акции компании ЮНИПРО. Тикер на Мосбирже — UPRO. Но если попробовать использовать его в таблицах,  GoogleFinance будет выдергивать котировки иностранного биржевого фонда ProShares UltraPro S&P500, имеющего аналогичный тикер. Да еще и с долларовыми ценами. 

Что делаем? Вместо UPRO прописываем MCX:UPRO и функция обратиться на Московскую биржу и подтянет нужные нам котировки именно с нее.

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

На Московской бирже есть бумаги, выдающие по GoogleFinance некорректные для нас данные.

Накидал несколько примеров подобных разногласий: при вводе одного и того же тикера: с MCX и без.

Неверный вывод котировок по тикеру акций

Алгоритм составления и работы таблицы

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

Growth — компании роста. Исторически компания растет намного лучше, чем в среднем растет рынок США (сравнивал по индексу S&P500), но доходность по дивидендам ниже 1,5%.

Basic — основные компании, которые рассматриваются для портфеля. Такие компании обычно растут быстрей S&P500 или примерно на одном уровне. Дивидендная доходность выше 1,5%

Dividend — компания растет медленней, чем в среднем S&P500, но при этом платит щедрые дивиденды выше 3% годовых.

Компании в таблице разделены на три категории: Growth, Basic, Dividend

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

Часть показателей считается автоматически, значения выше нормы выделяются цветом.

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

Получение даты и значения дивиденда для акций

Гугл таблица с примерами автоматического получения
Гугл таблица с примерами автоматического получения дат и значений дивидендов для акций

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

Теги