Финансовый парфюмер, там где запах денег... (perfume007) wrote,
Финансовый парфюмер, там где запах денег...
perfume007

Category:

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

Михаил Шардин кандидат технических наук. После того как 10 лет пользовался разными программами, он создал собственную гугл-таблицу для учета капитала. Ему слово. Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом. Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.


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

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


Моя таблица в «Гугл-документах»

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

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

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

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

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

Разделитель в формулах при американских региональных настройках — запятая, в отличие от российского формата — точки с запятой. Если вы будете переносить формулы в какие-то свои таблицы, имейте это в виду.

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

Знание экселя и регулярных выражений не помешает
Актуальные цены многих активов подтягиваются со сторонних сайтов с помощью функции ImportXML. Для разных активов используются разные сайты. Например, данные по актуальной стоимости квартиры на Арбате я беру с сайта «Домофонд». И тут две проблемы.
Во-первых, если «Домофонд» обновит структуру сайта, формула может слететь, потому что она обращается к конкретной части страницы. На момент публикации статьи все формулы работают, но со временем что-то может поменяться.
Во-вторых, если вы захотите подтягивать актуальную цену квартиры в другом районе или городе, формулу нужно будет переписать.
Если вам нужна будет помощь с этим, я постараюсь отвечать в комментариях к статье.

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

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

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

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

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


На скриншоте показаны столбцы, которые надо заполнить вручную. Остальные столбцы скрыты

Индивидуальный инвестиционный счет (ИИС). Допустим, что на ИИС куплено 100 облигаций федерального займа ОФЗ-ПД 26225. Код этой ценной бумаги — SU26225RMFS1. Облигации куплены 3 сентября 2018 года по цене 89% от номинала.

Разнесем данные по столбцам таблицы, которые надо заполнить вручную:
«Имя» — ОФЗ-ПД 26225 10/05/34.
«Идентификатор» — SU26225RMFS1.
«Кол-во» — 1000 штук. Не 100, потому что цена облигации на Мосбирже указывается в процентах. Для того чтобы корректно вести расчеты в рублях, эти проценты можно умножить на 10: номинал облигации — 1000 Р.
«Базовая цена»: 1000 Р (номинал облигации) × 89% (цена покупки) × 100 шт. = 89 000 Р.
«Валюта» — RUB.
«Посредник» — в этом случае ИИС.
«Дата покупки» — 2018-09-03.


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

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

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

Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:
«Имя» — группа компаний «Пионер» БО-02.
«Идентификатор» — RU000A0JWK66.
«Кол-во» — 700 штук.
«Базовая цена» — 1000 Р × 65% × 70 шт. = 45 500 Р.
«Валюта» — RUB.
«Посредник» — рос. брокер.
«Дата покупки» — 2018-09-28.

Для фонда FXMM:
«Имя» — FinEx Cash Equivalents UCITS ETF.
«Идентификатор» — FXMM.
«Кол-во» — 10 штук.
«Базовая цена» — 1426,8 Р × 10 шт. = 14 268 Р.
«Валюта» — RUB.
«Посредник» — рос. брокер.
«Дата покупки» — 2018-04-20.

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


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

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

Разнесем данные по столбцам таблицы:
«Имя» — квартира в Москве, район Арбат.
«Кол-во» — 1 м².
«Базовая цена» — 460 000 Р.
«Дата поступления» — в этой ячейке записана формула, которая всегда будет показывать дату следующего платежа. В примере это 25 число, его можно поменять на любое другое.
«Размер поступления» — ежемесячный платеж, который поступает вам за аренду.
«Ожидаемая доходность» — из расчета, что платят 10 месяцев в году: Ежемесячный платеж × 10 / Текущая стоимость недвижимости × 100.
«Валюта» — RUB.
«Дата покупки» — 2017-12-13.


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

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

Разнесем данные по столбцам таблицы:

«Имя» — Георгий Победоносец (3 рубля).
«Кол-во» — 50 штук.
«Базовая цена» — 30 000 Р.
«Посредник» — монеты.
«Дата покупки» — 2009-07-13.

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

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

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


Дополнительно вручную можно указать категории и классы активов, если вы хотите смотреть распределение и по ним. Автоматическое скачивание возможно реализовать только на гугл-скриптах.

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

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

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


Скриншот вкладки «Валюты»

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

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


Скриншот вкладки «Посредники»

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


Скриншот вкладки «Классы активов»

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


Скриншот вкладки «Покупки»

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


Скриншот вкладки «Капитал»

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

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


Скриншот вкладки «Идентификаторы»

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


Скриншот вкладки «Отчет»

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

Источник

А как вы учитываете свои инвестиции? Напишите в комментариях...




Добавиться в друзья можно вот тут

Понравился пост? Расскажите о нём друзьям, нажав на кнопочку ниже:


Tags: информация, капитал, обучение, опыт, работа с информацией, статистика
Subscribe

Recent Posts from This Journal

promo perfume007 декабрь 15, 2020 23:59 17
Buy for 20 tokens
В продолжении по циклам солнечной активности. Спасибо taxfree за тематику данного поста. Как утверждается Владимиром Левченко - после экстремумов, т.е. максимумов и минимумов солнечной активности, на следующий год всегда наблюдается провал в темпах роста мировой экономики. Левченко утверждает,…
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 5 comments