Учет акций в гугл таблицах
Как я слежу за акциями в гугл-таблице: три простых способа
Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.
Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.
Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью API. В статье расскажу, как настроить их под свои нужды.
Что за АПИ
В большинстве случаев данные с бирж передаются через программный интерфейс, называемый API — application programming interface. Грубо говоря, это инструмент, благодаря которому одна программа начинает говорить на одном языке с другой. В нашем случае мы используем API финансовых бирж для «дружбы» с гугл-таблицами.
API Московской биржи
Для чего. Получить информацию о российских акциях и облигациях.
Как это работает. У Московской биржи есть API, который позволяет видеть любую информацию с российской биржи внутри гугл-таблиц. Например, можно получать имена инструментов, цены закрытий, даты оферт и другие параметры.
Работа этой функции полностью зависит от сервера Московской биржи. Если биржа поменяет формат выдачи, организует какие-то другие режимы торгов или изменит что-то в существующей системе, то для корректной работы формул потребуется их доработка.
Еще таблица пригодится, если вас не устраивают существующие сервисы учета — Intelinvest и Investing.com.
В таблице-примере к этой статье я привел несколько полезных параметров ценных бумаг. Вот что она умеет:
Список далеко не полный, ведь на Мосбирже огромное количество параметров, включая срочный рынок и валюты.
Я везде старался использовать формулы в понятной записи с разбивкой на несколько строк — чтобы каждый смог воспроизвести эту конструкцию в своей таблице.
Googlefinance внутри гугл-таблиц
Для чего. Получить информацию об американских акциях, которые торгуются на Санкт-Петербургской бирже.
Как это работает. Googlefinance — это встроенная функция гугл-таблиц. Она помогает получить текущие или архивные данные о ценных бумагах из сервиса «Гугл-финансы».
Российские акции в сервисе тоже есть. Чтобы их увидеть, надо указывать тикер — то есть код из нескольких букв, который принадлежит определенному финансовому инструменту, — с приставкой «MCX:» так, чтобы получилось «MCX:SBER».
Эта функция позволяет получать название, текущую цену акций и еще 18 параметров — например, максимальную и минимальную цену за 52 недели, количество акций в обращении. Все это есть в моем шаблоне.
Главное преимущество в том, что формулы для получения этих параметров предельно просты: например, имя бумаги — name, самая высокая цена на текущий день — high. Описание всех формул и атрибутов есть в инструкции от Гугла.
В первом столбце я оставил идентификаторы двух акций — Сбербанка (MCX;SBER) и «Нетфликс» (NFLX) — и фонда Van Eck (RSX). При изменении тикеров во втором столбце будет подтягиваться название акций и актуальные цены.
А чтобы получить подробную информацию об акции или фонде, используйте ячейки В13 и В35: в них нужно занести тикер и в таблицах ниже сразу же обновится информация.
Например, вы хотите отслеживать акции американского производителя сетевого оборудования Ubiquiti Inc (UI) и высокодивидендного фонда Highland Small-Cap Equity Y (HSZYX). В ячейку В13 заносите тикер акции UI, а в ячейку В35 — тикер фонда HSZYX. Получите 19 параметров каждой бумаги.
Смотрите, как это работает:
«Яху-финанс»
Для чего. Отслеживать любые финансовые данные со всего мира.
Я остановился на втором варианте — получение данных через формулу IMPORTHTML. Важно: эта формула подтягивает данные только из таблиц или списков, другую информацию через нее не получить.
Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы — данные из нее и появятся в гугл-таблице.
Моя формула обращается к тикеру, который нужно прописать самостоятельно, и ищет данные по этой акции на «Яху». К написанию некоторых тикеров есть требования:
Учет инвестиций в гугл-таблицах | Получаем котировки акций через GoogleFinance
С помощью формул в гугл-таблицах можно автоматически получать текущие котировки практически любых акций. Для этих целей можно использовать функцию GOOGLEFINANCE или API Московской биржи.
Начнем с разбора принципов работы GoogleFinance.
Но сперва немного пофилософствуем на тему: а зачем это нужно?
Для чего нужно вести учет инвестиций в таблицах?
У каждого в личном кабинете (приложении) брокера есть отображение информации «о состоянии дел». Наверное для большинства этого хватит за глаза. Особенно, если у вас только один брокер и всего пара-тройка используемых инструментов.
Приведу несколько примеров, когда самостоятельный учет может быть полезен.
Портфельное управление
Часто бывает (как например у меня), что деньги раскиданы по разным брокерам и счетам. И вы не видите картины в целом. Особенно, если вы придерживаетесь портфельной теории и распределяете деньги по разным активам с заранее выделенными пропорциями. Конечно, можно затратить немного времени и без помощи таблиц свести дебет с кредитом и вычислить сколько в процентном соотношении у меня приходится на акции США (Китай, России), золото, облигации на каждый момент времени момент. Если ли перекос от изначально заданных пропорций, насколько он критичен? Не пора ли сделать ребалансировку? Какие активы требуется купить в первую очередь, а какие в ближайшее время брать не нужно.
Короче, всегда нужно делать какую-то предварительную работу для оценки портфеля и принятия решения о будущих покупках.
Вот вам к примеру портфель со следующими пропорциями:
И так далее и тому подобное по каждому инструменту, каждому классу активов.
Несколько отдельных стратегий
Или можно назвать это как ведение нескольких отдельных портфелей. В рамках одного капитала инвестор может использовать различные стратегии: агрессивная, дивидендная, индексная, спекулятивная, пенсионная и т.д. И естественно не хочется смешивать все активы в одну кучу.
Получаем обратную ситуацию: нужно не объединить, а разделить активы по стратегиям.
Дивиденды
У вас есть акции или биржевые фонды, которые выплачивают дивиденды?
А вы можете оценить общий размер дивидендов, которые вы получаете по своим бумагам?
Сколько дивов вы получаете в год (или в среднем в месяц)?
Как альтернативу, для этих целей можно использовать различные сервисы (платные и бесплатные). Тоже кстати неплохой вариант. Но учет в таблицах позволяет делать это бесплатно, настроить «все под себя» и ни от кого не зависеть.
Добавляем иностранные бумаги через GoogleFinance
Для получения котировок нужных нам акций достаточно знать тикер (биржевой код) бумаги. Сама формула имеет следующий вид:
Руками в формулу нужно подставить нужный тикер бумаги или адрес ячейки, где этот тикер прописан.
Пример 1. Тикеры прописываем в отдельной ячейке (колонка С). В формуле указываем адрес ячейки: С2, С3 и так далее.
Получаем котировки акций через GoogleFinance
Пример 2. Тикер прописывается непосредственно в формуле.
С помощью GoogleFinance можно буквально за несколько минут соорудить свой шаблон по учету инвестиций. Например, указали количество купленных акций и система будет показывать вам актуальную рыночную стоимость портфеля.
Ниже пример такого портфеля из 15 иностранных (американских) акций.
Пример учета инвестиций в помощью GoogleFinance
Российские акции и возможные проблемы
Функция GoogleFinance отлично работает с иностранными акциями. С получением котировок, по компаниями входящим в состав различных мировых индексов, проблем не возникает. Если компания более-менее крупная, так или иначе, цену акции можно выдернуть.
А вот с российскими бумагами могут возникнуть определенные сложности (хотя в большинстве случаев решаемых).
Сначала о хорошем. Ниже пример того, что GoogleFinance прекрасно распознает котировки российских бумаг.
Автоматическое получение котировок российских акций
Теперь про сложности, с которыми можно столкнуться:
Проблема №1. GoogleFinance не определяет цены на привилегированные акции (от слова совсем). Под раздачу попали префы Сбера, Башнефти, Татнефти, Ростелекома, Сургутнефтегаза.
Невозможность получения котировок префов через GoogleFinance
Проблема №3. Функция может неверно определять или «путать» цены с другими тикерами, акции которых обращаются на других биржах.
Первые две проблемы можно обойти через API Московской биржи (в конце статьи есть ссылка как это сделать).
Третья проблема решается добавлением к тикеру названия биржи, с которой вы хотите получать цены.
Что делаем? Вместо UPRO прописываем MCX:UPRO и функция обратиться на Московскую биржу и подтянет нужные нам котировки именно с нее.
Второй пример: акции одной и той же компании могут обращаться на разных биржах и номинироваться в разных валютах (Лукойл, Qiwi).
На Московской бирже есть бумаги, выдающие по GoogleFinance некорректные для нас данные.
Накидал несколько примеров подобных разногласий: при вводе одного и того же тикера: с MCX и без.
Еще пара нюансов про GoogleFinance
Котировки поступают в таблицы с задержкой (примерно минут 20). Если вам нужны актуальные данные, то лучше использовать другие ресурсы (приложения или программы брокера).
Помимо котировок, функция ГуглФинанс способна отдавать информацию по мультипликаторам компании (P/E, капитализация, EPS), ценам открытия-закрытия на любой день, изменению котировок за день (неделю, месяц), достигнутых максимумах-минимумах за определенный период и много чего другого. Часть этого может найти практическое применение в системе учета инвестиций.
Оставляю ссылку на шаблон таблицы. Все про что писал выше, там есть.
В следующей статье разберем как получить котировки российских акций и биржевых фондов (в том числе номинированных в валюте) обращающихся на Московской бирже.
Учет инвестиций в гугл-таблицах №2 — получаем котировки акций и ETF с Мосбиржи
В прошлой статье мы разобрали способ получения котировок акций ( и ETF) с помощью функции GoogleFinance. Правда по некоторым российским бумагам получении инфы таким образом невозможно. Речь идет о привилегированных акциях (типа Сбера, Татнефти, Сургута) и практически всех российских биржевых фондах (FXIT, VTBX, SBSP и так далее).
Ок, будет разбираться как это можно сделать другим способом. Расскажу какие формулы можно использовать для этих целей. Отдаю готовый шаблон гугл-таблицы с уже заполненными данными.
Котировки российских акций
Для вызова котировок привилегированных акций (впрочем как и обыкновенных) используем формулу следующего вида (все пишется слитно, без пробелов):
Вот как это выглядит в таблице:
Получение котировок для российских привилегированных акций
Дополнительно, чтобы не прописывать руками, можно сделать автоматический вывод названия компании. Мы сразу будем видеть, что формула ничего не путает и отдает нам котировки нужных нам акций.
Для вывода названия акций (компании) используем формулу:
Получаем цену ETF и БПИФ в рублях / долларах и евро
Если мы захотим получить котировки российских биржевых фондов по вышеописанной формуле, просто заменив тикер акции на тикер ETF (или БПИФ), то нас постигнет фиаско. Формула будет выдавать ошибку.
Для биржевых фондов нужна формула следующего вида:
Как всегда, при необходимости меняем адрес ячейки с тикером.
Получаем название и котировки российских биржевых фондах в Гугл-таблицах
Что это за зверь такой?
Соответственно прописываем в формулу нужный идентификатор.
Дабы сэкономить читателю время, в формуле вызова котировок используем идентификатор:
Для автоматического получения названия фонда по тикеру, в формуле соответственно также нужно поменять идентификатор.
В таблице накидал несколько примеров вызова цен по валютным фондам.
Выводим котировки биржевых фондов РФ в долларах и евро
Цена иностранных акций на Мосбирже
На Московской бирже обращаются иностранные акции, номинированные в рублях.
Формула вызова котировок имеет следующий вид:
Красным пометил отличия от предыдущих формул:
Настройка таблицы или возможные проблемы
Для исправлении ошибки выбираем в меню «Файл->Настройки таблицы».
На вкладке «Общие» в поле Региональные настройки» нужно выбрать Соединенные Штаты. 
Нужно знать
Не забываем про задержку во времени. Так же как и ГуглФинанс, котировки по API Мосбиржи подгружаются примерно с 20 минутным опозданием.
В приоритете (там где это возможно) лучше использовать функцию GoogleFinfnce. Если в таблице много инструментов, то загрузка по API может быть очень долгой. В этом плане Гуглфинанс работает пошустрее. Поэтому используем API Мосбиржи только там, где Гугл бессилен: префы российских компаний и биржевые фонды.
Для акций у которых котировки рассчитываются до более 2-х знаков после запятой, тоже лучше использовать API. В этом случае цена будет передаваться более точная.
Из подобного есть еще Русал, ИнтерРао, Русгидро, АФК Система, Россети, ФСК ЕЭС и много чего еще.
Готовый шаблон
Комментарии, замечания, пожелания приветствуются!
В третьей части будем автоматически выдергивать информацию по дивидендам российских акций.
Всем огромное спасибо за замечания!
Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных
В это части постараюсь описать, чего уже можно добиться с помощью документа в Google Spreadsheet, разработанного Вячеславом (пример — https://docs.google.com/spreadsheets/d/1IUxJfnRjzpqkNpuKAU83eTqxCOLyWVZmkVTI9galxZ0/edit#gid=0), а также пройтись по листам, на которых вносятся необходимые для учета данные.
А в следующей части уже посмотрим, что получаем на выходе. И станет ясно, куда можно расти и что улучшать.
Итак, Гугл таблица позволяет получать подробную информацию по портфелю:
Как видите, в базовой версии уже заложен огромный набор возможностей по учету своего портфеля. Но чтобы всего это добиться, нужно разобраться с логикой ввода данных и теми настройками, что есть. Пройдусь по листам, на которых нужно вносить информацию об инвестициях, расходах, сделках и дивидендах.
Инвестиции. Учет проинвестированных денег. Для точного подсчета нужно регулярно записывать все суммы, попавшие на брокерский счет. Вносится как сама сумма, так и дата пополнения. Это нужно для более точного подсчета денег, участвующих в «обороте», а также дисконта на инфляцию.
Константы. Различные константы, учитываемы для корректного расчета затраченных на сделки сумм, а также влияющие на подсчет реальной доходности портфеля (с учетом инфляции).
Какую величину инфляции стоит использовать для подсчета реальной доходности портфеля? Тут все не так однозначно. Самое простое — использовать официальные данные Росстата. Но им не всегда есть доверие, поэтому можно ставить любую цифру, соответствующую вашим «ощущениям». Еще более сложный вариант — рассчитывать свою личную годовую инфляцию, но для этого нужно вести учет своих расходов. Про это как-нибудь поговорим отдельно.
Сделки. Следующая вкладка (достаточно объемная) — Сделки. Большинство колонок рассчитывается автоматически, но некоторые из них надо заполнить вручную данными по своим состоявшимся сделкам.
Этот лист я немного оптимизировал, чтобы упростить ввод сделок, в результате на вставку сделок я трачу минимум времени (скопировать нужное количество строк из Excel, потянуть расчетные формулы, добавить комментарий при необходимости). Про все оптимизации и улучшения — будет отдельно.
Расходы. Расходы по брокерскому счету. Сюда вносятся все суммы, списанные за обслуживание с брокерского счета (депозитарное обслуживание и т.п.). Этот лист использую достаточно редко, так как за обслуживание не плачу, а брокерские комиссии учитываются в Сделках. Правда, пару раз попадал на дополнительные комиссии (РЕПО, доступ к Web-QUIK), но это единичные случаи.
Дивиденды. Сюда записываются все поступающие дивиденды по акциям, а также купоны по облигациям.
Опять же, этот лист я немного модернизировал, добавив подсчет заплаченных сумм НДФЛ и флаг того, нужно ли самому потом платить налог. А то бывают эмитенты, зарегистрированные за рубежом (Ros Agro, Rusal PLC), которые присылают дивиденды в долларах, а брокер с таких поступлений налог не берет. Поэтому очень удобно заранее проставить нужный флаг, чтобы в следующем году мучительно не вспоминать, что нужно включить в налоговую декларацию.
По вводу данных вроде бы все. В следующий раз посмотрим листы Портфель, Структура и Баланс, отвечающие за наглядной отображение различной информации по портфелю.
Как я создал собственную гугл-таблицу для учета капитала
После того как 10 лет пользовался разными программами
Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом.
Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.
Мне было сложно увидеть полную картину активов, потому что у разных финансовых посредников нет единой формы и стандарта отчетов. Ни одна из программ, которыми я пользовался, не подходила мне на сто процентов: в основном приходилось слишком долго возиться с добавлением новых бумаг, подтягиванием нужных котировок.
Поэтому я разработал собственную отчетную форму в «Гугл-таблицах»: туда я импортирую отчеты разных брокеров и записываю активы, чтобы понимать, что происходит с моим капиталом, и видеть достоверный бюджет поступлений на месяц вперед.
Как работает таблица
Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования VBA, но сейчас я перенес его в гугл-таблицу без использования скриптов.
Чтобы таблица была не просто очередным шаблоном, я дал ей собственное имя — SilverFir: Investment Report. Название говорит о том, что это инвестиционный отчет, а silver fir отсылает к разновидности вечнозелёных деревьев.
Прежде чем пошагово расписать, как пользоваться шаблоном гугл-таблицы, необходимо сделать несколько важных пояснений.
Форматы данных. В настройках таблицы указаны региональные настройки Соединенных Штатов. Это означает, что разделитель целой и дробной части числа — точка, то есть 105.1 — правильная запись, а 105,1 выдаст ошибку. Это сделано, чтобы не загромождать формулы автоматической заменой точки на запятую. Все американские и многие российские сайты выдают цены именно с точкой в качестве разделителя.
Даты указаны в формате «год-месяц-день», то есть «2020-03-11» — 11 марта 2020 года.
Разделитель в формулах при американских региональных настройках — запятая, в отличие от российского формата — точки с запятой. Если вы будете переносить формулы в какие-то свои таблицы, имейте это в виду.
Как победить выгорание
Основные параметры, используемые в таблице. Чтобы заполнить таблицу и корректно ею пользоваться, необходимо знать следующие параметры:
Знание экселя и регулярных выражений не помешает
Актуальные цены многих активов подтягиваются со сторонних сайтов с помощью функции ImportXML. Для разных активов используются разные сайты. Например, данные по актуальной стоимости квартиры на Арбате я беру с сайта «Домофонд». И тут две проблемы.
Во-первых, если «Домофонд» обновит структуру сайта, формула может слететь, потому что она обращается к конкретной части страницы. На момент публикации статьи все формулы работают, но со временем что-то может поменяться.
Во-вторых, если вы захотите подтягивать актуальную цену квартиры в другом районе или городе, формулу нужно будет переписать.
Если вам нужна будет помощь с этим, я постараюсь отвечать в комментариях к статье.
Пошаговое руководство по заполнению
По ссылке откроется сразу ваша копия таблицы — можно редактировать данные прямо в ней. Никто другой не имеет доступа к данным в вашей копии.
Представим, что у вас есть несколько типов активов: два вклада в разных валютах, ИИС, обычный брокерский счет, арендная квартира в Москве и монета «Георгий Победоносец». Разберемся, как получить полную картину по сбережениям.
Начнем с вкладов. Готовые примеры занесены в строки 7 и 8 таблицы.
Пусть это будет вклад 50 000 Р под 5,8% годовых, открытый 22 марта 2020 года сроком на год — до 22 марта 2021 года. Разнесем данные по столбцам таблицы:
Как следить за бюджетом
Если ваш вклад не в рублях, то таблица автоматически рассчитает начальные затраты в рублях в столбце «Цена покупки, Р » по курсу на дату открытия вклада.
Индивидуальный инвестиционный счет (ИИС). Допустим, что на ИИС куплено 100 облигаций федерального займа ОФЗ-ПД 26225. Код этой ценной бумаги — SU26225RMFS1. Облигации куплены 3 сентября 2018 года по цене 89% от номинала.
Код ценной бумаги можно посмотреть в отчете брокера или на сайте биржи
Разнесем данные по столбцам таблицы, которые надо заполнить вручную:
Брокерский счет. Допустим, на брокерском счете — бумаги двух эмитентов:
Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:
Если в дальнейшем я буду докупать те же бумаги, нужно просто обновить в этой строке количество бумаг и базовую цену. Остальные значения остаются неизменными. Таким образом, «Дата покупки» — это, строго говоря, дата первой покупки актива.
Разнесем данные по столбцам таблицы:
Разнесем данные по столбцам таблицы:
Что делать после заполнения данных
После того как вы внесете исходные данные, сразу можно увидеть работу формул. Данные начнут скачиваться, и таблица автоматически заполнится недостающими параметрами.
Теперь можно узнать следующие показатели по каждому из активов:
Дополнительно вручную можно указать категории и классы активов, если вы хотите смотреть распределение и по ним. Автоматическое скачивание возможно реализовать только на гугл-скриптах.
Анализ сводных показателей портфеля
Перейдем теперь к сводным показателям всего портфеля. Их можно смотреть на разных вкладках.
«Данные» — это главная вкладка, куда вносятся все исходные. Светло-голубым выделены ячейки, которые надо заполнить вручную. Также на этой вкладке рассчитывается прибыль и убыток по позиции, дата и размер ближайшего поступления от актива.
«Валюты» — полностью автоматическая вкладка, которая содержит отчет по используемым валютам. Как только вы редактируете что-либо на вкладке «Данные», этот мини-отчет сразу меняется.
«Посредники» — отчетная вкладка, которая показывает распределение сумм по брокерам и весовое значение процента капитала. Еще она показывает количество бумаг у каждого брокера и расчетный ежемесячный доход, также этот доход отображается в процентах годовых.
На этой вкладке можно оценить, насколько успешен тот или иной счет, потому что отображаются изменения в рублях с момента покупки.
«Классы активов» — здесь вы увидите отчет о диверсификации вашего портфеля. Я формализовал описания классов активов из Quicken и описаний нескольких авторов, в том числе Сергея Спирина, Александра Силаева, Павла Комаровского.
«Покупки» — это мини-отчет об истории покупок по времени. Здесь вы сможете узнать, в каком месяце сколько денег потратили.
«Капитал» — на этой вкладке отображается текущая дата и две совокупных стоимости всех активов: стоимость покупки и текущая рыночная стоимость портфеля в рублях. Эта вкладка реализована с помощью формул, а формулы не могут сами копироваться в другие ячейки — для создания истории придется вручную копировать эти данные на строчку ниже.
«Капитал график» — визуализирует данные с вкладки «Капитал».
«Идентификаторы» — в графическом виде отображает распределение по бумагам в таблице.
«Отчет» — сводный отчет о планируемых поступлениях на три месяца вперед в рублях, то есть сумма купонов, арендных платежей. Также вкладка дает информацию о ближайших выплатах на 30 дней вперед и назад, а еще — о лидерах роста и падения вместе с историей капитала.
Запомнить
Тебе не придётся напрягаться с учётом инвестиций, если у тебя их нет!
(картинка, с умным негром)
Спасибо за материал, взял на заметку пару интересных моментов.
Для себя тоже веду Excel-таблицу, сначала она была такая же сложная, потом постепенно приходил к выводу, что та или иная аналитика избыточна. В итоге пришел к варианту буквально с двумя страницами: на первой веду все операции с активами (покупка, продажа, купоны/дивиденды), на второй автоматически собирается вся необходимая мне сводная информация:
1. Текущая доля каждого класса актива и отклонение от желаемой доли.
2. Текущая доля распределения по валютам и по отраслям.
3. Текущая доля каждого актива относительно всего портфеля.
4. Текущая стоимость каждого актива в рублевом эквиваленте (тоже подтягивается автоматически из разных источников).
5. Количество.
6. Годовая доходность каждого актива.
7. Общая стоимость и годовая доходность всего портфеля.
8. Денежный поток по каждому активу (сколько купонов/дивидендов получено).
9. Общая прибыль в рублевом выражении с учетом изменения цены актива и всех полученных по нему купонов/дивидендов.
А, ну еще есть одна страница, на которой красивый график, показывающий прогнозную стоимость моего портфеля на 20 лет вперед, если я буду продолжать дисциплинированно инвестировать дальше. И на этом же графике вторая кривая, показывающая фактический результат. Очень мотивирует.