Блог им. Svaroggg

Учет инвестиций с помощью Google Spreadsheet. Получаем статистику и красивые графики.

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

 В первую очередь, стоит обратить внимание на лист Портфель, на котором будет основная информация по акциям в портфеле:

Учет инвестиций с помощью Google Spreadsheet. Получаем статистику и красивые графики.

Что же интересного можно почерпнуть здесь?

Во-первых, наглядно видно, сколько лотов каждой акции у вас уже есть в наличии и сколько денег было отдано на их покупку (колонки Лотов в наличии, Сумма покупок). Особенно важная колонка — Средняя цена покупки, она рассчитывается автоматически. Также показывается и текущая стоимость по каждой акции, но нужно вручную вводить котировки в колонку «Рыночная цена», чтобы иметь достаточно актуальные цифры.

Также отсюда можно легко понять, насколько эффективны были ваши инвестиции (это колонки Чистая курсовая прибыль, Курсовая прибыль в %, Прирост реальной стоимости). Колонки имеют градиентный фон, чтобы с одного взгляда можно было понять, где у вас что-то пошло не так.

Но наиболее ценными для меня оказались колонки «Вес в портфеле», «Текущая доля в портфеле», «Желаемая доля в портфеле», «Докупить на сумму» и «Докупить лотов». Зачем они нужны и как работают?

Вы для каждой своей позиции тем или иным образом (уж кто как умеет) прикидываете, какой «вес» каждого эмитента вы хотите иметь у себя в портфеле. После того, как веса определены, будет автоматически высчитана желаемая доля в портфеле, а колонка «Разбаланс в портфеле» покажет, насколько желаемая доля отличается от текущей доли. А что самое удобное, в колонке «Докупить лотов» будет подсказано по каждой позиции, сколько лотов нужно докупить/продать, чтобы получить желаемую долю. Я этим постоянно пользуюсь, когда докупаю акции при очередном пополнении. Не нужно самому рассчитывать, сколько лотов купить, когда выставляешь заявку в Quik. Так что очень рекомендую этим пользоваться (кстати, комментарии к колонкам в самом Гугл документе очень хорошо поясняют, как это должно работать).

Также есть вспомогательные колонки (Сектор, Категория ЦБ), которые нужно заполнять вручную, они позволяют получить красивые круговые диаграммы, представленные на листе «Структура»:


Учет инвестиций с помощью Google Spreadsheet. Получаем статистику и красивые графики.

Чем эти диаграммы могут быть полезны? Например, диаграмма «Текущая структура портфеля по секторам» наглядно показывает, насколько хорошо диверсифицирован  ваш портфель по секторам экономики. А то бывают перекосы, когда набирают разных эмитентов, но они при этом принадлежат одному сектору экономики (Электроэнергетика или Металлы, например).

 Учет инвестиций с помощью Google Spreadsheet. Получаем статистику и красивые графики.

Этот график позволяет примерно оценить «рискованность» портфеля. Например, если достаточно большая доля средств вложена в акции Второго эшелона, то можно прогнозировать, что волатильность портфеля будет большой…

Для себя я еще добавил несколько категорий для каждой позиции, чтобы лучше понимать, что же я накупил…

  • Валюта — чтобы примерно представлять, как будет себя вести портфель (да и отдельные эмитенты) при валютных колебаниях
  • Акции/Облигации — стараюсь держать определенную долю портфеля в облигациях (хотел написать в «низко рисковых инструментах», но не уверен, что этот термин подходит для ОФЗ)
  • Стратегия (Доходная/Стоимостная/Рисковая/Хедж)

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

И последний лист (но, возможно, самый главный) — это «Баланс». Сам по себе лист простой и наглядный, не требующий особых пояснений:

Учет инвестиций с помощью Google Spreadsheet. Получаем статистику и красивые графики.

Чаще всего я захожу на него посмотреть, чтобы порадоваться (огорчиться) своим успехам (неудачам). Стоит периодически сравнивать значение в строке «Текущий остаток на депо» с реальной цифрой на брокерском счету, чтобы быть уверенным, что вы не забыли ничего внести. У меня порой случалось расхождение рублей в 500, но что приятно, на счету было больше средств:).

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



 






★24 | ₽ 113
20 комментариев
Было бы круто, если бы в конце ее выложили (свой вариант).
Дмитрий Александрович, Может быть и выложу (я пока до конца не определился), правда там надо много всего почистить перед этим. И некоторая функциональность все равно работать не будет, так как завязана на отправку/получение данных из моей базы MySQL в другом месте. А переписывать эту часть и проверять, что она не сломалась я точно не буду.
avatar
Сергей Ветко, да вы еще и базу данных прикрутили? Что там храните?
Дмитрий Александрович, отсылку данных в MySQL прикручивал, но больше для интереса, чтобы понять, как это может работать:). Ну и немного для бэкапа использую, храню стоимость каждой позиции на конец дня. И стоимость всего портфеля.
avatar
А зачем эти проблемы с экселем? Можно же взять любой сервис для ведения портфеля.
avatar
relige, никаких проблем с экселем. Подробно о причинах выбора Google Spreadsheet я писал в самой первой статье (https://smart-lab.ru/blog/488667.php). В частности, были такие пункты:
4. По сравнению с Excel намного проще смотреть и редактировать с различных устройств. Например, откуда-нибудь из поездки можно легко внести информацию о пришедших на счет дивидендах.
5. Возможность самому что-то донастраивать, доделывать и дописывать (в меру своих сил и умений). Например, я настроил автоматические уведомления об определенных событиях, что было бы сложно сделать в Excel.

Можете подсказать, какие конкретно сервисы вы имеете в виду под «любой серсвис для ведения портфеля»?
avatar

Сергей Ветко, навскидку:

smart-lab
intelinvest.ru
conomy.ru
blackterminal.ru
investing.com

avatar
relige, спасибо за ссылки. Про первые два я уже писал, чем они не подходят. Остальные не смотрел, но предполагаю что у них похожие недостатки. Может, подскажите, на чем вы сами остановили выбор и почему?
avatar
Сергей Ветко, У меня очень мало эмитентов и по сути я только докупаю раз в месяц, потому мне хватает личного кабинета брокера.

Но результаты такого подхода мне не нравятся — так что скорее всего увеличу количество эмитентов и остановлюсь на каком-нибудь сервисе выше.
avatar
у меня данные из квика подтягиваются в Excel, там тоже это возможно или ручками надо? А то мой способ ведения портфеля может уже устарел) Тоже делал разные плюшки для учетов дивидендов, даже индикаторы, но в итоге отказался, так как руками это заполнять муторно, да и без пользы, так первое время поиграться. Самое важное это итоговая доходность и рост депо)
а вот расчет лотов для ребаланса это самая полезная функция что я реализовал, как я понял у вас такое есть
Евгений Демидов, согласен, автоматический подсчет лотов для ребаланса очень облегчает процесс закупок. Раз определился с желаемыми долями, а потом просто механически поддерживаешь нужные соотношения в моменты ребалансировок.

Я тоже сначала подтягивал данные по котировкам из QUIK в Excel, а потом вручную переносил в гугл таблицу. Но мне быстро надоело, поэтому написал скрипт, который раз в час забирает необходимые данные с сайта Мосбиржи. Это одно из преимуществ Google spreadsheet, что он работает на сервере, а не на локальном компьютере.
Тоже делал разные плюшки для учетов дивидендов, даже индикаторы, но в итоге отказался, так как руками это заполнять муторно, да и без пользы, так первое время поиграться.
Что за плюшки для учета дивидендов вы делали? Я просто записываю реально поступившие деньги на счет, чтобы иметь правильную сумму свободных средств. А какие индикаторы делали?
avatar
Сергей Ветко, тоже самое я и делал, сформировал таблицу приходящих дивидендов, потом это суммировал, причем разные дивиденды я складывал с учетом ставки дисконтирования, и это для доходности по кокретной бумаге. Но так как бумаг у меня больше 20, заполнять мне надоело, пользы по факту от этого нет для меня, причем порфелей сейчас несколько. А данные с мосбиржи платные? А то я так понял даже состав индекса без регистрации и оплаты не вытащить, я через картинку распознаю)
Евгений Демидов, У меня тоже эмитентов достаточно много, пока заполнять не надоело:). Не так и часто приходят дивиденды. Но я без ставок дисконтирования...

Данные бесплатные. Скорее всего с какой-то задержкой (15 минут?), но это не так и важно. Вот пример ссылки по акциям: https://iss.moex.com/issrpc/marketdata/stock/shares/daily/download/micex_stock_shares.xml?collection_id=3&board_group_id=57&start=0&limit=10000&lang=ru
avatar
очень полезный шаблончик, я делаю свой, похожий. Ломаю голову над тем как учитывать сделки в валюте и рублях в рамках одного портфеля, пока не придумал) тут ещё нужно учитывать и курс валюты на каждую сделку и валютную переоценку, то есть какая доходность реальная в валюте и какая с учётом валютной переоценки, то есть рублёвая
Человек с земли, мне пока до валютного портфеля далековато. Но надеюсь когда-нибудь начать инвестировать и за рубежом (все жду падения американского рынка процентов так на 50:). Но думаю, что в этом случае буду заводить отдельный документ и все доходности считать в долларах. Как я понимаю, курс валюты нужен будет только для отчетности в налоговую? Думаю, это не должно быть очень сложно, ведь даты сделок и так есть, а курс ЦБ на каждую дату легко забрать с их же сайта.

А вы инвестируете в валюте через иностранного брокера или через нашего? Через кого, если не секрет?
avatar
Сергей Ветко, для доступа к иностранным бумагам использую тинькофф (раньше и для доступа к ФР МБ использовал), для наших бумаг сейчас полностью перехожу в открывашку (сейчас пересмотрю тарифы и вероятно полностью уйду в открывашку).

А учёт в валюте и рублях усложняется тем, что всё происходит в рамках одного портфеля (тинькофф), несмотря на то что использую его только для американских бумаг (пока что), там до сих пор остались и рублёвые эмитенты. Поэтому таблица должна быть универсальной как для рублёвых сделок, так и для долларовых. Для каждой валютной сделки нужно определять рублёвый курс и отслеживать как рублёвую доходность, так и долларовую. И вся проблема для меня как считать долларовые сделки. Например на купленные в начале 2018 года доллары было совершено много сделок — считать прибыль/убыток по первоначальному курсу или по курсу на момент сделки — такого рода вопросы крутятся в моей голове) или вообще не париться и брать средний курс покупки бакса для расчета профита по сделке и в целом по портфелю. 

пс сори за долгий ответ. был в отпуске.
avatar
Человек с земли, Человек с земли, Курс валюты на каждую сделку же нужен только для расчета налога? Я сделал для сделок по долларовым активам отдельные таблицы (отдельно для евробондов и акций на СПб), и суммарную оценку потом по текущему курсу перевожу, чтобы видеть доходность относительно рубля. По каждой позиции, наверное, смысла нет считать, ведь обмен денег осуществляется один раз, а дальше учет уже ведется в своей валюте. Надеюсь, будет полезно.
avatar
Добрый день! А категоризацию активов как делаете? Вручную списки формируете или откуда-то можно выгрузить?
avatar
retverd, Категории расставлял вручную. Это делается один раз, а эмитентов не особо много, так что времени много не занимает.
avatar

теги блога Сергей Ветко

....все тэги



UPDONW