Как я создал собственную гугл-таблицу для учета капитала

Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом.

Моя основная финансовая боль всегда была с учетом всех активов – то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.

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

Поэтому я разработал собственную отчетную форму в «Гугл- »: туда я импортирую отчеты разных брокеров и таблицы, чтобы понимать, что происходит с моим капиталом, и видеть достоверный бюджет поступлений на месяц вперед.

Как работает таблица

Изначально мой отчет был табличкой в ​​экселе с использованием упрощенного языка программирования VBA, но сейчас я перенес его в гугл-таблицу с использованием скриптов.

Чтобы была таблица не просто очередным шаблоном, я дал ей собственное имя – SilverFir: инвестиционный отчет. Название говорит о том, что это инвестиционный отчет, серебряная ель отсылает к разновидности вечнозелёных деревьев.

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

Форматы данных. В настройках таблиц указаны региональные настройки Соединенных Штатов. Это означает, что разделитель целой и дробной части числа – точка, то есть 105.1 – правильная запись, а 105,1 выдаст ошибку. Это сделано, чтобы не загромождать формулы автоматической заменой точки на запятую. Все американские и многие российские сайты выдают цены именно с точкой в ​​качестве разделителя.

Даты указаны в формате «год-месяц-день», то есть «2020-03-11» – 11 марта 2020 года .

Основные параметры, используемые в таблице. Чтобы заполнить таблицу и вы будете переносить формулы в какие-то свои таблицы. правильно ею пользоваться, необходимо знать следующие параметры:

1. Идентификатор – обычно тикер или ISIN, международный идентификационный код ценной бумаги.
2. Валюта – в соответствии с кодами валют: USD, RUB, EUR, GBP.
3. Дата покупки – нужна, чтобы считать доход и определить стоимость в рублях на момент покупки, если актив в валюте, отличной от рубля.
4. Количество – может измеряться и в штуках, и в квадратных метрах.
5. Цена покупки – заносится вместе с комиссией, ведь иначе за сделку приходится платить ощутимые комиссионные.

Пошаговое руководство по заполнению

По ссылка откроется сразу ваша копия таблицы – можно редактировать данные прямо в ней. Другой не имеет доступа к данным в вашей копии.

Представим, что у вас есть несколько типов активов: два вклада в валютах, ИИС, обычный брокерский счет, арендная квартира в Москве и монета «Георгий Победоносец ». Разберемся, как получить полную картину по сбережениям.

Начнем со вкладов. Готовые примеры занесены в строки 7 и 8 таблицы.

1. «Имя» – произвольное.
2. «Базовая цена» – 50 000 Р.
3. «Ожидаемая дох-ть,%» – 5,8.
4. «Дата поступления» – 2021-03-22.
5. «Размер поступления» – в ячейке уже стоит формула, суммирующая сумма вклада и ожидаемый доход.
6. «Валюта» – Руб.
7. «Посредник» – в этой ячейке указываю название брокера, банка или просто тип активов, например «вклады».
8. «Дата покупки» – 2020-03-22.

Если ваш вкладка – это нужно для того, чтобы было удобно просматривать данные в сводных таблицах. не в рублях, то таблица автоматически рассчитает начальные затраты в столбце «Цена покупки, Р» по курсу на дату открытия вклада.

Разнесем данные по столбцам таблицы, которые надо заполнить вручную:

1. «Имя» – ОФЗ-ПД 26225 05.10.34.
2. «Идентификатор» – SU26225RMFS1.
3. «Кол-во» – 1000 штук. Не 100, потому что цена облигаций на Мосбирже указывается в процентах. Для того, чтобы корректно вести расчеты в рублях, эти проценты можно умножить на 10: номинал облигаций – 1000 Р.
4. «Базовая цена»: 1000 Р (номинал облигации) × 89% (цена покупки) × 100 шт. = 89 000 Р.
5. «Валюта» – руб.
6. «Посредник» – в этом случае ИИС.
7. «Дата покупки »- 3 сентября 2018 г.

Брокерский счет. Допустим, на брокерском счет – бумаги двух эмитентов:

1. 70 рублевых облигаций группы компаний «Пионер» серии БО-02, код ценной бумаги – RU000A0JWK66, куплены 28 сентября 2018 года по цене 65% от номинала, то есть за 45 500 Р.
2. 10 акций биржевого инвестиционного фонда FXMM, куплены 20 апреля 2018 года по цене 1426,8 Р за штуку.

Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:

1. «Имя» – группа компаний «Пионер» БО-02.
2. «Идентификатор» – RU000A0JWK66.
3. «Кол-во» – 700 штук.
4. «Базовая цена» – 1000 Р × 65% × 70 шт. = 45 500 Р.
5. «Валюта» – руб.
6. «Посредник» – рос. брокер.
7. «Дата покупки» – 28.09.2018.

Если в дальнейшем я буду докупать те же бумаги, нужно просто обновить в этой строке количество бумаг и базовую цену. Остальные значения остаются неизменными. Таким образом, «Дата покупки» – это, строго говоря, дата первой покупки актива.

Квартира в Москве. Основная идея табличного отчета в том, что можно брать цены на совершенно любые классы финансовых активов – лишь бы они публиковались в интернете в свободном доступе. Так и с недвижимостью: есть множество сайтов, которые публикуют статистику цен. Представим, что у вас есть доля в мини-квартире в Москве, в районе Арбата. Квартира сдается в аренду. Правда, вам принадлежит всего 1 м². Купили вы эту долю 13 декабря 2017 года за 460 000 Р. Каждый месяц 25 числа вы получаете арендный доход – 3742 Р.

Монета« Георгий Победоносец ». Такая же ситуация и с инвестиционными монетами. Например, 11 лет назад, 13 июля 2009 года, вы купили 50 серебряных монет «Георгий Победоносец» номиналом 3 Р. Заплатили 600 Р за каждую.

Что делать после заполнения данных

После того, как вы внесли исходные данные, сразу можно увидеть работу формул.

Теперь можно узнать следующие показатели по каждому из активов:

1. Прибыль или убыток у вас по позиции на текущий момент в рублях – независимо от валюты приносит вложения: столбец G.
2. Сколько процентов годовых эта инвестиция, если срок больше года, или просто процент изменения, если срок меньше года: столбец H.
3. Ожидаемая величина и средний доход в денежном выражении: столбцы J и K.
4. Даты грядущих поступлений – или прошлых, если информация по будущим выплатам еще недоступна: столбцы L и
5. Процентное изменение бумаги за последние несколько лет для акций: столбец I. За счет именно – зависит от сайта, берутся данные.
6. Текущий вес бумаги в портфеле: столбец V.

Анализ сводных показателей портфеля

Перейдем теперь к сводным показателям всего портфеля. Их можно смотреть на разных вкладках.

«Данные» – это главная вкладка, куда вносятся все исходные. Светло-голубым выделены ячейки, которые надо заполнить вручную. Также на этой вкладке вводится прибыль и убыток по позиции, дата и размер ближайшего поступления от актива.

«Валюты» – полностью автоматическая вкладка, которая содержит отчет по используемым валютам. Как только вы редактируете что-либо на вкладке «Данные», этот мини-отчет сразу меняется.

«Посредники» – отчетная вкладка, которая показывает распределение сумм по брокерам и весовое значение процента капитала. Еще она показывает количество счетов каждого брокера и расчетный ежемесячный доход, также этот доход в процентах годовых.

. момента покупки.

«Классы активов» – здесь вы увидите отчет о диверсификации вашего портфеля. Я формализовал описание классов активов из Quicken и описаний нескольких авторов, в том числе Сергея Спирина, Александра Силаева, Павла Комаровского.

«Покупки» – это мини-отчет об истории покупок по времени. Здесь вы сможете узнать, в каком месяце сколько денег потратили.

«Капитал» – на этой вкладке отображается текущая дата и две совокупные стоимости всех активов: стоимость покупки и текущая рыночная стоимость портфеля в рублях. Эта вкладка реализована с помощью формул, а формулы не могут быть сами копироваться в другие ячейки – для создания истории придется вручную копировать эти данные на строчку.

« Капитал график » – визуализирует данные со вкладки« Капитал ».

«Идентификаторы» – в графическом виде отображает распределение по бумагам в таблице.

« Отчет » – сводный отчет о планируемых поступлениях на три месяца в рублях, то есть сумма вперед купонов , арендных платежей. Также вкладка дает информацию о ближайших выплатах на 30 дней вперед и назад, а еще – о лидерах роста и падений вместе с историей капитала.

Запомнить

1. Эта таблица – удобный вариант, когда у вас несколько разных брокеров, в том числе зарубежные, и есть доходная недвижимость. Вклады и другие активы тоже легко учесть.
2. Данные в вашем экземпляре таблицы принадлежат только вам, ни у кого другого не будет к ним доступа. В любой момент только вы сами можете изменить или удалить любую информацию из вашей копии.
3. За счет автоматических мини-отчетов на основе данных из таблицы вы всегда знаете, что происходит в портфеле. Неважно, насколько он сложен.
4. Если вы хотите добавить свой актив, измените цены на который можно посмотреть на общедоступном сайте, чтобы напишите мне в комментариях к статье. Я помогу с написанием формулы или гугл-скрипта.

---

---

Как я слежу за акциями в гугл-таблице: три простых метода

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

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

Для этого я комбинирую три сервиса, которые подтягивают данные с разных бирж с помощью API. В статье расскажу, как настроить их под свои нужды.

API Московской биржи

Для чего. Получить информацию о российских акциях и облигациях.

Как это работает. У Московской биржи есть API, который позволяет видеть любую информацию с российской биржи внутри гугл -таблиц. Например, можно получить имена инструментов, цены закрытий, дату оферт и другие параметры сервера.

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

Еще таблица пригодится, если вас не устраивают удобные сервисы учета – Intelinvest и Investing.com.

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

Я везде старался использовать формулы в понятной записи с разбивкой на несколько строк – чтобы каждый смог в оспроизвести эту конструкцию в своей таблице.

Попробовать таблицу

Googlefinance внутри гугл-таблиц

Как это работает. Googlefinance – это встроенная функция гугл -таблиц. Она помогает получить текущие или архивные данные о ценных бумагах сервиса «Гугл-финансы».

Российские акции в сервисе тоже есть. Чтобы их увидеть, надо указать тикер – то есть код из нескольких букв, который определенному финансовому инструменту, – с приставкой «MCX:» так, чтобы получилось «MCX: SBER».

Эта функция позволяет получить название, текущую цену акций и еще 18 параметров – например, максимальную и минимальную цену за 52 недели, количество акций в обращении. Все это есть в моем шаблоне.

Главное преимущество в том, что формулы для обеспечения этих параметров предельно просты: например, имя бумаги – name, самая высокая цена на текущий день – high. Описание всех формул и атрибутов есть в инструкции от Гугла.

В первом столбце я оставил услуги двух акций – Сбербанка (MCX; SBER) и «Нетфликс» (NFLX) – и фонд Ван Экка (RSX). При изменении тикеров во втором столбце будет подтягиваться название акций и актуальные цены.

Например, вы хотите узнать акции американского производителя сетевого оборудования Ubiquiti Inc (UI) и высокодивидендного фонда Highland Small-Cap Equity Y (HSZYX). В ячейку В13 заносите тикер акции UI, а в ячейку В35 – тикер фонда HSZYX. Получите 19 параметров каждой бумаги.

Смотрите, как это работает:

Попробовать таблицу

«Яху-финанс»

Для чего. Отслеживать любые финансовые данные со всего мира.

Как это работает. «Яху-финанс» передает данные с рынков 79 стран, включая Россию.

Чтобы подружить «Яху» и таблицы, можно пойти двумя путями: написать скрипт, который будет импортировать выдачу API в специальном текстовом формате JSON в гугл-таблицу, или использовать формулой IMPORTHTML – еще есть вариант с IMPORTXML, – которая импортирует таблицу и списки с сайтов.

Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы – данные из нее и появятся в гугл-таблице.

Попробовать таблицу

Запомнить

1. API Московской биржи в гугл-таблицах – самый быстрый способ получить данные с Мосбиржи.
2. Встроенная функция Googlefinance внутри гугл-таблиц пригодится, если нет времени разбираться с формулами, но хочется видеть курсы акций.
3. Любые финансовые данные со всего мира получайте в API «Яху-финанс» с помощью простых функций IMPORTHTML.
4. Если вы только начинаете инвестировать, пройдите бесплатный курс от Т — Ж: на сайте или в приложении для iOS.