Как вести учет инвестиций в google таблице как мой портфель акций в excel и результаты
С помощью формул в гугл-таблицах можно автоматически получать текущие котировки практически любых акций. Для этих целей можно использовать функцию GOOGLEFINANCE или API Московской биржи.
Первый способ в большей степени подходит для иностранных компаний. Второй - для отечественных бумаг (акций, биржевых фондов и облигаций).
Начнем с разбора принципов работы GoogleFinance.
Но сперва немного пофилософствуем на тему: а зачем это нужно?
Для чего нужно вести учет инвестиций в таблицах?
У каждого в личном кабинете (приложении) брокера есть отображение информации "о состоянии дел". Наверное для большинства этого хватит за глаза. Особенно, если у вас только один брокер и всего пара-тройка используемых инструментов.
Приведу несколько примеров, когда самостоятельный учет может быть полезен.
Портфельное управление
Часто бывает (как например у меня), что деньги раскиданы по разным брокерам и счетам. И вы не видите картины в целом. Особенно, если вы придерживаетесь портфельной теории и распределяете деньги по разным активам с заранее выделенными пропорциями. Конечно, можно затратить немного времени и без помощи таблиц свести дебет с кредитом и вычислить сколько в процентном соотношении у меня приходится на акции США (Китай, России), золото, облигации на каждый момент времени момент. Если ли перекос от изначально заданных пропорций, насколько он критичен? Не пора ли сделать ребалансировку? Какие активы требуется купить в первую очередь, а какие в ближайшее время брать не нужно.
Короче, всегда нужно делать какую-то предварительную работу для оценки портфеля и принятия решения о будущих покупках.
Вот вам к примеру портфель со следующими пропорциями:
- акции - 80% (распределено на 4 страны равными долями, включают отдельные бумаги и ETF);
- золото - 10% (как в виде ETF, так и акции золотодобытчиков);
- облигации - 10% (отдельны бумаги и фонды).
Акции США - в идеале на них должно приходится 20% от всего веса портфеля. На часть денег куплены биржевые фонды (допустим FXUS и FXIT), на другую часть отдельные бумаги - с десяток другой акций.
Акции на РФ - допустим часть снова в акциях, часть в нескольких фондах.
И так далее и тому подобное по каждому инструменту, каждому классу активов.
В совокупности, всего на 3 класса актива из портфеля может приходится десятки различных инструментов. И это все сводить постоянно воедино, даже торгую только через одного брокера - будет очень трудозатратно.
Несколько отдельных стратегий
Или можно назвать это как ведение нескольких отдельных портфелей. В рамках одного капитала инвестор может использовать различные стратегии: агрессивная, дивидендная, индексная, спекулятивная, пенсионная и т.д. И естественно не хочется смешивать все активы в одну кучу.
Получаем обратную ситуацию: нужно не объединить, а разделить активы по стратегиям.
Дивиденды
У вас есть акции или биржевые фонды, которые выплачивают дивиденды?
А вы можете оценить общий размер дивидендов, которые вы получаете по своим бумагам?
Сколько дивов вы получаете в год (или в среднем в месяц)?
С помощью таблиц можно достаточно легко выдернуть информацию по размеру дивидендов на акцию. Далее путем нехитрых манипуляций - вычислить дивидендную доходность своего портфеля и сумму дивов, как общую так и по каждой бумаге отдельно.
Как альтернативу, для этих целей можно использовать различные сервисы (платные и бесплатные). Тоже кстати неплохой вариант. Но учет в таблицах позволяет делать это бесплатно, настроить "все под себя" и ни от кого не зависеть.
Добавляем иностранные бумаги через GoogleFinance
Для получения котировок нужных нам акций достаточно знать тикер (биржевой код) бумаги. Сама формула имеет следующий вид:
=GOOGLEFINANCE ( "тикер" , "price")
Руками в формулу нужно подставить нужный тикер бумаги или адрес ячейки, где этот тикер прописан.
Пример 1. Тикеры прописываем в отдельной ячейке (колонка С). В формуле указываем адрес ячейки: С2, С3 и так далее.
Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.
Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.
Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью API. В статье расскажу, как настроить их под свои нужды.
Что за АПИ
В большинстве случаев данные с бирж передаются через программный интерфейс, называемый API — application programming interface. Грубо говоря, это инструмент, благодаря которому одна программа начинает говорить на одном языке с другой. В нашем случае мы используем API финансовых бирж для «дружбы» с гугл-таблицами.
API Московской биржи
Для чего. Получить информацию о российских акциях и облигациях.
Как это работает. У Московской биржи есть API, который позволяет видеть любую информацию с российской биржи внутри гугл-таблиц. Например, можно получать имена инструментов, цены закрытий, даты оферт и другие параметры.
Работа этой функции полностью зависит от сервера Московской биржи. Если биржа поменяет формат выдачи, организует какие-то другие режимы торгов или изменит что-то в существующей системе, то для корректной работы формул потребуется их доработка.
Такая выборочная информация с биржи пригодится для разных целей. Можно создать гугл-таблицу , чтобы покупать облигации с хорошей ликвидностью, — об этом я уже рассказывал. А можно следить за параметрами бумаг в собственном портфеле — например, за датами оферт. Это поможет не пропустить важную дату, если хотите принять участие в досрочном погашении.
В таблице-примере к этой статье я привел несколько полезных параметров ценных бумаг. Вот что она умеет:
- подтягивать название бумаги по идентификатору — столбец «Названия акций и облигаций»;
- показывать текущие курсы — столбец «Цены акций и облигаций»;
- получать даты выплат облигаций — столбец «Даты купона и значения для облигаций»;
- получать дивиденды — столбец «Даты и значения дивиденда для акций»;
- видеть возможность досрочного погашения облигаций — столбец «Даты оферт».
Список далеко не полный, ведь на Мосбирже огромное количество параметров, включая срочный рынок и валюты.
Когда в первом столбце вы меняете идентификаторы бумаг, получаете актуальные цены, названия, даты и величины купонов
Я везде старался использовать формулы в понятной записи с разбивкой на несколько строк — чтобы каждый смог воспроизвести эту конструкцию в своей таблице.
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 параметров каждой бумаги.
Смотрите, как это работает:
«Яху-финанс»
Для чего. Отслеживать любые финансовые данные со всего мира.
Чтобы подружить «Яху» и таблицы, можно пойти двумя путями: написать скрипт, который будет импортировать выдачу API в специальном текстовом формате JSON в гугл-таблицу , или воспользоваться формулой IMPORTHTML — еще есть вариант с IMPORTXML, — которая импортирует таблицы и списки с сайтов.
Я остановился на втором варианте — получение данных через формулу IMPORTHTML. Важно: эта формула подтягивает данные только из таблиц или списков, другую информацию через нее не получить.
Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы — данные из нее и появятся в гугл-таблице.
Моя формула обращается к тикеру, который нужно прописать самостоятельно, и ищет данные по этой акции на «Яху». К написанию некоторых тикеров есть требования:
Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом.
Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.
Мне было сложно увидеть полную картину активов, потому что у разных финансовых посредников нет единой формы и стандарта отчетов. Ни одна из программ, которыми я пользовался, не подходила мне на сто процентов: в основном приходилось слишком долго возиться с добавлением новых бумаг, подтягиванием нужных котировок.
Поэтому я разработал собственную отчетную форму в «Гугл-таблицах»: туда я импортирую отчеты разных брокеров и записываю активы, чтобы понимать, что происходит с моим капиталом, и видеть достоверный бюджет поступлений на месяц вперед.
Как работает таблица
Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования 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% от номинала.
Код ценной бумаги можно посмотреть в отчете брокера или на сайте биржи
Разнесем данные по столбцам таблицы, которые надо заполнить вручную:
Брокерский счет. Допустим, на брокерском счете — бумаги двух эмитентов:
- 70 рублевых облигаций группы компаний «Пионер» серии БО-02, код ценной бумаги — RU000A0JWK66, куплены 28 сентября 2018 года по цене 65% от номинала, то есть за 45 500 Р .
- 10 акций биржевого инвестиционного фонда FXMM, куплены 20 апреля 2018 года по цене 1426,8 Р за штуку.
Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:
Если в дальнейшем я буду докупать те же бумаги, нужно просто обновить в этой строке количество бумаг и базовую цену. Остальные значения остаются неизменными. Таким образом, «Дата покупки» — это, строго говоря, дата первой покупки актива.
Квартира в Москве. Основная идея табличного отчета в том, что можно брать цены на совершенно любые классы финансовых активов — лишь бы они публиковались в интернете в свободном доступе. Так и с недвижимостью: есть множество сайтов, которые публикуют статистику цен. Представим, что у вас есть доля в мини-квартире в Москве, в районе Арбата. Квартира сдается в аренду. Правда, вам принадлежит всего 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 дней вперед и назад, а еще — о лидерах роста и падения вместе с историей капитала.
Запомнить
- Эта таблица — удобный вариант, когда у вас несколько разных брокеров, в том числе зарубежных, и есть доходная недвижимость. Вклады и другие активы тоже легко учесть.
- Данные в вашем экземпляре таблицы принадлежат только вам, ни у кого другого не будет к ним доступа. В любой момент только вы сами можете изменить или удалить любую информацию из вашей копии.
- За счет автоматических мини-отчетов на основе данных из таблицы вы всегда знаете, что происходит в портфеле. Неважно, насколько он сложен.
- Если вы хотите добавить свой актив, изменение цены на который можно отслеживать на общедоступном сайте, то напишите мне в комментарии к статье. Я помогу с написанием формулы или гугл-скрипта.
Тебе не придётся напрягаться с учётом инвестиций, если у тебя их нет!
(картинка, с умным негром)
тьфунатебя, да, обычно так и пишут в комментариях. 🤷♂️ Недавно еще было популярный комментарий - инвестировал в пачку гречки.
Спасибо за материал, взял на заметку пару интересных моментов.
Для себя тоже веду Excel-таблицу, сначала она была такая же сложная, потом постепенно приходил к выводу, что та или иная аналитика избыточна. В итоге пришел к варианту буквально с двумя страницами: на первой веду все операции с активами (покупка, продажа, купоны/дивиденды), на второй автоматически собирается вся необходимая мне сводная информация:
1. Текущая доля каждого класса актива и отклонение от желаемой доли.
2. Текущая доля распределения по валютам и по отраслям.
3. Текущая доля каждого актива относительно всего портфеля.
4. Текущая стоимость каждого актива в рублевом эквиваленте (тоже подтягивается автоматически из разных источников).
5. Количество.
6. Годовая доходность каждого актива.
7. Общая стоимость и годовая доходность всего портфеля.
8. Денежный поток по каждому активу (сколько купонов/дивидендов получено).
9. Общая прибыль в рублевом выражении с учетом изменения цены актива и всех полученных по нему купонов/дивидендов.
В итоге, когда приходит момент докупить какой-то актив, я за 2 минуты понимаю какой именно это должен быть актив: смотрю сначала на текущую долю в классах (акции/облигации), потом смотрю на долю в валютах, а потом, сузив диапазон до "Ага, понятно, нужно купить акции в $", уже выбираю конкретную акцию в зависимости от отрасли, доли каждой акции в портфеле и текущей экономической ситуации. После этого добавляю строчку о сделке, а все остальное перерасчитывается автоматически.
А, ну еще есть одна страница, на которой красивый график, показывающий прогнозную стоимость моего портфеля на 20 лет вперед, если я буду продолжать дисциплинированно инвестировать дальше. И на этом же графике вторая кривая, показывающая фактический результат. Очень мотивирует.
А в следующей части уже посмотрим, что получаем на выходе. И станет ясно, куда можно расти и что улучшать.
Итак, Гугл таблица позволяет получать подробную информацию по портфелю:
- Сумма ваших инвестиций
- Сумма накопленных дивидендов
- Текущий остаток на депозите
- Текущую стоимость портфеля
- Номинальный доход и доходность (без инфляции)
- Реальный доход и доходность (с учетом инфляции)
- Структура портфеля по секторам (реальная и желаемая)
- По каждой акции в портфеле показывается количество лотов в наличии, дивиденды, средняя цена покупки, текущая стоимость, прибыль и многое другое. Производится расчет как текущей доли в портфеле, так и желаемой доли, а также дается рекомендация, сколько лотов нужно купить/продать, чтобы получить желаемую долю. К сожалению, данные по рыночным ценам не подтягивались автоматически, поэтому нужно было вручную их вносить, чтобы видеть актуальную версию картины. Но при относительно пассивном инвестировании это не так и важно. Если раз в месяц (или еще реже) осуществлять покупки, то можно и вручную обновить котировки.
Как видите, в базовой версии уже заложен огромный набор возможностей по учету своего портфеля. Но чтобы всего это добиться, нужно разобраться с логикой ввода данных и теми настройками, что есть. Пройдусь по листам, на которых нужно вносить информацию об инвестициях, расходах, сделках и дивидендах.
Инвестиции. Учет проинвестированных денег. Для точного подсчета нужно регулярно записывать все суммы, попавшие на брокерский счет. Вносится как сама сумма, так и дата пополнения. Это нужно для более точного подсчета денег, участвующих в «обороте», а также дисконта на инфляцию.
Константы. Различные константы, учитываемы для корректного расчета затраченных на сделки сумм, а также влияющие на подсчет реальной доходности портфеля (с учетом инфляции).
Какую величину инфляции стоит использовать для подсчета реальной доходности портфеля? Тут все не так однозначно. Самое простое — использовать официальные данные Росстата. Но им не всегда есть доверие, поэтому можно ставить любую цифру, соответствующую вашим «ощущениям». Еще более сложный вариант — рассчитывать свою личную годовую инфляцию, но для этого нужно вести учет своих расходов. Про это как-нибудь поговорим отдельно.
Сделки. Следующая вкладка (достаточно объемная) — Сделки. Большинство колонок рассчитывается автоматически, но некоторые из них надо заполнить вручную данными по своим состоявшимся сделкам.
Этот лист я немного оптимизировал, чтобы упростить ввод сделок, в результате на вставку сделок я трачу минимум времени (скопировать нужное количество строк из Excel, потянуть расчетные формулы, добавить комментарий при необходимости). Про все оптимизации и улучшения — будет отдельно.
Расходы. Расходы по брокерскому счету. Сюда вносятся все суммы, списанные за обслуживание с брокерского счета (депозитарное обслуживание и т.п.). Этот лист использую достаточно редко, так как за обслуживание не плачу, а брокерские комиссии учитываются в Сделках. Правда, пару раз попадал на дополнительные комиссии (РЕПО, доступ к Web-QUIK), но это единичные случаи.
Дивиденды. Сюда записываются все поступающие дивиденды по акциям, а также купоны по облигациям.
По вводу данных вроде бы все. В следующий раз посмотрим листы Портфель, Структура и Баланс, отвечающие за наглядной отображение различной информации по портфелю.
Итак, основные данные по сделкам и т.п. мы ввели в прошлой части. Теперь осталось понять, зачем мы все это делали и что полезного можно получить на выходе.
В первую очередь, стоит обратить внимание на лист Портфель, на котором будет основная информация по акциям в портфеле:
Что же интересного можно почерпнуть здесь?
Во-первых, наглядно видно, сколько лотов каждой акции у вас уже есть в наличии и сколько денег было отдано на их покупку (колонки Лотов в наличии, Сумма покупок). Особенно важная колонка — Средняя цена покупки, она рассчитывается автоматически. Также показывается и текущая стоимость по каждой акции, но нужно вручную вводить котировки в колонку «Рыночная цена», чтобы иметь достаточно актуальные цифры.
Также отсюда можно легко понять, насколько эффективны были ваши инвестиции (это колонки Чистая курсовая прибыль, Курсовая прибыль в %, Прирост реальной стоимости). Колонки имеют градиентный фон, чтобы с одного взгляда можно было понять, где у вас что-то пошло не так.
Но наиболее ценными для меня оказались колонки «Вес в портфеле», «Текущая доля в портфеле», «Желаемая доля в портфеле», «Докупить на сумму» и «Докупить лотов». Зачем они нужны и как работают?
Вы для каждой своей позиции тем или иным образом (уж кто как умеет) прикидываете, какой «вес» каждого эмитента вы хотите иметь у себя в портфеле. После того, как веса определены, будет автоматически высчитана желаемая доля в портфеле, а колонка «Разбаланс в портфеле» покажет, насколько желаемая доля отличается от текущей доли. А что самое удобное, в колонке «Докупить лотов» будет подсказано по каждой позиции, сколько лотов нужно докупить/продать, чтобы получить желаемую долю. Я этим постоянно пользуюсь, когда докупаю акции при очередном пополнении. Не нужно самому рассчитывать, сколько лотов купить, когда выставляешь заявку в Quik. Так что очень рекомендую этим пользоваться (кстати, комментарии к колонкам в самом Гугл документе очень хорошо поясняют, как это должно работать).
Также есть вспомогательные колонки (Сектор, Категория ЦБ), которые нужно заполнять вручную, они позволяют получить красивые круговые диаграммы, представленные на листе «Структура»:
Чем эти диаграммы могут быть полезны? Например, диаграмма «Текущая структура портфеля по секторам» наглядно показывает, насколько хорошо диверсифицирован ваш портфель по секторам экономики. А то бывают перекосы, когда набирают разных эмитентов, но они при этом принадлежат одному сектору экономики (Электроэнергетика или Металлы, например).
Этот график позволяет примерно оценить «рискованность» портфеля. Например, если достаточно большая доля средств вложена в акции Второго эшелона, то можно прогнозировать, что волатильность портфеля будет большой…
Для себя я еще добавил несколько категорий для каждой позиции, чтобы лучше понимать, что же я накупил…
- Валюта — чтобы примерно представлять, как будет себя вести портфель (да и отдельные эмитенты) при валютных колебаниях
- Акции/Облигации — стараюсь держать определенную долю портфеля в облигациях (хотел написать в «низко рисковых инструментах», но не уверен, что этот термин подходит для ОФЗ)
- Стратегия (Доходная/Стоимостная/Рисковая/Хедж)
Но вообще здесь достаточно много разных вариантов, которые было бы полезно отслеживать. Если у вас есть интересные идеи — буду рад обсудить в комментариях.
И последний лист (но, возможно, самый главный) — это «Баланс». Сам по себе лист простой и наглядный, не требующий особых пояснений:
Чаще всего я захожу на него посмотреть, чтобы порадоваться (огорчиться) своим успехам (неудачам). Стоит периодически сравнивать значение в строке «Текущий остаток на депо» с реальной цифрой на брокерском счету, чтобы быть уверенным, что вы не забыли ничего внести. У меня порой случалось расхождение рублей в 500, но что приятно, на счету было больше средств:).
Фуф, объемное такое получилось описание функциональности, которая была изначально заложена. В следующих частях начну уже описывать, как можно улучшить данную Гугл таблицу, чтобы иметь более полную информацию о своем портфеле и лучше его контролировать.
Читайте также: