С помощью формул в гугл-таблицах можно автоматически получать текущие котировки практически любых акций. Для этих целей можно использовать функцию GOOGLEFINANCE или API Московской биржи.
Первый способ в большей степени подходит для иностранных компаний. Второй - для отечественных бумаг (акций, биржевых фондов и облигаций).
Начнем с разбора принципов работы GoogleFinance.
Но сперва немного пофилософствуем на тему: а зачем это нужно?
Для чего нужно вести учет инвестиций в таблицах?
У каждого в личном кабинете (приложении) брокера есть отображение информации "о состоянии дел". Наверное для большинства этого хватит за глаза. Особенно, если у вас только один брокер и всего пара-тройка используемых инструментов.
Приведу несколько примеров, когда самостоятельный учет может быть полезен.
Портфельное управление
Часто бывает (как например у меня), что деньги раскиданы по разным брокерам и счетам. И вы не видите картины в целом. Особенно, если вы придерживаетесь портфельной теории и распределяете деньги по разным активам с заранее выделенными пропорциями. Конечно, можно затратить немного времени и без помощи таблиц свести дебет с кредитом и вычислить сколько в процентном соотношении у меня приходится на акции США (Китай, России), золото, облигации на каждый момент времени момент. Если ли перекос от изначально заданных пропорций, насколько он критичен? Не пора ли сделать ребалансировку? Какие активы требуется купить в первую очередь, а какие в ближайшее время брать не нужно.
Короче, всегда нужно делать какую-то предварительную работу для оценки портфеля и принятия решения о будущих покупках.
Вот вам к примеру портфель со следующими пропорциями:
- акции - 80% (распределено на 4 страны равными долями, включают отдельные бумаги и ETF);
- золото - 10% (как в виде ETF, так и акции золотодобытчиков);
- облигации - 10% (отдельны бумаги и фонды).
Акции США - в идеале на них должно приходится 20% от всего веса портфеля. На часть денег куплены биржевые фонды (допустим FXUS и FXIT), на другую часть отдельные бумаги - с десяток другой акций.
Акции на РФ - допустим часть снова в акциях, часть в нескольких фондах.
И так далее и тому подобное по каждому инструменту, каждому классу активов.
В совокупности, всего на 3 класса актива из портфеля может приходится десятки различных инструментов. И это все сводить постоянно воедино, даже торгую только через одного брокера - будет очень трудозатратно.
Несколько отдельных стратегий
Или можно назвать это как ведение нескольких отдельных портфелей. В рамках одного капитала инвестор может использовать различные стратегии: агрессивная, дивидендная, индексная, спекулятивная, пенсионная и т.д. И естественно не хочется смешивать все активы в одну кучу.
Получаем обратную ситуацию: нужно не объединить, а разделить активы по стратегиям.
Дивиденды
У вас есть акции или биржевые фонды, которые выплачивают дивиденды?
А вы можете оценить общий размер дивидендов, которые вы получаете по своим бумагам?
Сколько дивов вы получаете в год (или в среднем в месяц)?
С помощью таблиц можно достаточно легко выдернуть информацию по размеру дивидендов на акцию. Далее путем нехитрых манипуляций - вычислить дивидендную доходность своего портфеля и сумму дивов, как общую так и по каждой бумаге отдельно.
Как альтернативу, для этих целей можно использовать различные сервисы (платные и бесплатные). Тоже кстати неплохой вариант. Но учет в таблицах позволяет делать это бесплатно, настроить "все под себя" и ни от кого не зависеть.
Добавляем иностранные бумаги через GoogleFinance
Для получения котировок нужных нам акций достаточно знать тикер (биржевой код) бумаги. Сама формула имеет следующий вид:
=GOOGLEFINANCE("тикер", "price")
Руками в формулу нужно подставить нужный тикер бумаги или адрес ячейки, где этот тикер прописан.
Пример 1. Тикеры прописываем в отдельной ячейке (колонка С). В формуле указываем адрес ячейки: С2, С3 и так далее.
Пример 2. Тикер прописывается непосредственно в формуле.
С помощью GoogleFinance можно буквально за несколько минут соорудить свой шаблон по учету инвестиций. Например, указали количество купленных акций и система будет показывать вам актуальную рыночную стоимость портфеля.
Ниже пример такого портфеля из 15 иностранных (американских) акций.
Российские акции и возможные проблемы
Функция GoogleFinance отлично работает с иностранными акциями. С получением котировок, по компаниями входящим в состав различных мировых индексов, проблем не возникает. Если компания более-менее крупная, так или иначе, цену акции можно выдернуть.
А вот с российскими бумагами могут возникнуть определенные сложности (хотя в большинстве случаев решаемых).
Сначала о хорошем. Ниже пример того, что GoogleFinance прекрасно распознает котировки российских бумаг.
Теперь про сложности, с которыми можно столкнуться:
Проблема №1. GoogleFinance не определяет цены на привилегированные акции (от слова совсем). Под раздачу попали префы Сбера, Башнефти, Татнефти, Ростелекома, Сургутнефтегаза.
На картинке ниже видно, что котировки обычных акций - система определила, а по префам возникли ошибки.
Проблема №2. Нельзя вытянуть котировки большинства биржевых фондов обращающихся на Мосбирже. Аналогично ситуации с префами - будет выдавать ошибку.
Проблема №3. Функция может неверно определять или "путать" цены с другими тикерами, акции которых обращаются на других биржах.
Первые две проблемы можно обойти через API Московской биржи (в конце статьи есть ссылка как это сделать).
Третья проблема решается добавлением к тикеру названия биржи, с которой вы хотите получать цены.
Как пример, акции компании ЮНИПРО. Тикер на Мосбирже - UPRO. Но если попробовать использовать его в таблицах, GoogleFinance будет выдергивать котировки иностранного биржевого фонда ProShares UltraPro S&P500, имеющего аналогичный тикер. Да еще и с долларовыми ценами.
Что делаем? Вместо UPRO прописываем MCX:UPRO и функция обратиться на Московскую биржу и подтянет нужные нам котировки именно с нее.
Второй пример: акции одной и той же компании могут обращаться на разных биржах и номинироваться в разных валютах (Лукойл, Qiwi).
На Московской бирже есть бумаги, выдающие по GoogleFinance некорректные для нас данные.
Накидал несколько примеров подобных разногласий: при вводе одного и того же тикера: с MCX и без.
Еще пара нюансов про GoogleFinance
Котировки поступают в таблицы с задержкой (примерно минут 20). Если вам нужны актуальные данные, то лучше использовать другие ресурсы (приложения или программы брокера).
Помимо котировок, функция ГуглФинанс способна отдавать информацию по мультипликаторам компании (P/E, капитализация, EPS), ценам открытия-закрытия на любой день, изменению котировок за день (неделю, месяц), достигнутых максимумах-минимумах за определенный период и много чего другого. Часть этого может найти практическое применение в системе учета инвестиций.
Оставляю ссылку на шаблон таблицы. Все про что писал выше, там есть.
В следующей статье разберем как получить котировки российских акций и биржевых фондов (в том числе номинированных в валюте) обращающихся на Московской бирже.
Добрый день!
Отличная статья!
А если бы был специальный онлайн сервис для учета инвестиций повторяющий, то что вы делаете в гугл таблицах Вы бы им воспользовались?
Я пробовал пользоваться сторонними сервисами, но «что-то пошло не так» …. в итоге остановился на своей собственной системе учета. Мне как долгосрочному инвестору — этого хватает за глаза;
Спасибо, информация нужная.
Правда я уже нашел её раньше.
У гуглфинанс есть ещё один минус — он определяет цену только до сотых долей.
Не совсем подходит для наших акций, которых в лоте по 1000 или по 10000 шт, например ТГК 1, ВТБ и многие другие.
Согласен.
На подходе статья как правильно дергать котировки рос. акций с ценами включающие 3-4-5 знака после запятой.
Перестало работать для российских акций. Кто-нибудь знает, что происходит?
А какая будет формула на котировку акции на определенную дату в прошлом?
Здравствуйте, Максим! С котировками всё более или менее понятно. Но какие источники Вы могли бы порекомендовать для API-выгрузки числа акций в обращении и фундаментальных показателей, чтобы можно было расширить таблицу до показателей бухгалтерских балансов компаний и инвестмультипликаторов?
Добрый день! подскажите пожалуйста как сделать чтобы таблица делала запрос пореже. никак не может до конца обновиться чтобы показать суммы.