Сделал шаблон для учета инвестиций по стратегии равно взвешенного портфеля. Расскажу вкратце что умеет делать таблица.
Таблица состоит из нескольких блоков. Для удобства и наглядности блоки выделены разными цветами. Вот как это выглядит у меня на начальном этапе.
Начало - веса, котировки и названия
Перед началом пользования таблицей нужно указать сколько акций в портфеле вы хотите иметь. Это нужно для вычисления доли на одну акцию (5, 10 или 20%).
В первом блоке накидываем для себя список акций, который вы хотите иметь в портфеле. Для примера я добавил в файл 20 компаний из индекса Мосбиржи.
Котировки подтягиваются с биржи автоматически (прописана формула). Если будете менять бумагу на другую (или добавлять новые имена), в формуле нужно прописать новый тикер.
На примере формулы для Сбера. Тикер выделил красным. Его и нужно менять на другой.
=IMPORTXML("http://iss.moex.com/iss/engines/stock/markets/shares/securities/SBER.xml", "/document/data[@id=""marketdata""]/rows/row[@BOARDID=""TQBR""]/@MARKETPRICE")
Твой портфель
Второй сектор показывает текущее состояние вашего портфеля. Сколько и каких акций куплено и на какую сумму. А также пропорции этих акций в портфеле.
Заполнять количество акций можно в колонке "Акций куплено". Но бывает ситуации, что бумаги могут быть раскиданы по разным брокерам. И даже акции одного эмитента могут находиться по разным счетам. К примеру у меня так. Часть у одного брокер, часть у другого. Есть даже бумаги, лежащие у одного брокера, но по разным счетам (ИИС и обычный брокерский счет).
Это доставляет определенные неудобства при заполнении таблицы. Нужно постоянно складывать данные в уме. "у брокера А у меня лежит 100 акций Сбера, у брокера Б - еще 250. По брокеру В - сегодня купил 60 и было до этого на счете 40. Сколько итого нужно записать?" Или бывает случайно удалил данные по количеству акций, к примеру того же Сбера. Типа рука дрогнула и ты не заметил сразу (и не можешь сделать отмену действий). И что нужно сделать, чтобы восстановить данные? Пройтись по всем своим брокерам, посмотреть нет ли у них акций Сбера. А если удалил не одну, а несколько ячеек? У меня так было несколько раз. Приходилось не только восстанавливать, но делать сверку по всем брокерам - вдруг я что-то еще удалил случайно.
Второй минус - ты не видишь полной картины, какие бумаги и у какого брокера у тебя находятся.
Для подобных случаев я добавил разделение по брокерам. Заполняем количество по каждому брокеру отдельно, а потом все автоматом суммируется в отдельную колонку (акций куплено) При необходимости можно нажимать на "плюсик" (или "минус") и колонки с брокерами будут скрыты.
Помощь в ребалансировке
Для наглядности я сделал колонку "Расхождение весов". Она показывает на сколько отклоняются текущие пропорции акций от первоначально заданных. В зависимости от цвета колонки инвестор понимает, что ему нужно сделать с акциями:
- красный цвет - вес акции в портфеле превышен. Нужно продать часть.
- зеленый цвет - доля акций меньше заданного. Нужно докупать.
Если портфель постоянно пополняется, то продавать необязательно. Можно выравнивать пропорции покупкой отстающих, доля которых на данный момент минимальна, а расхождение максимально (зеленый цвет).
Новые пополнения
В таблице можно заполнить поле "Сумма для инвестиций (кэш)" и система сама посчитает каких акций и в каком количестве нужно купить. Причем учитывается уже купленные акции.
По сути - это подсказка куда направить новые поступления денег. Даже думать не надо. 😁
Дивиденды
Таблица выдергивает данные по дивидендам за 12 месяцев с сайта Доход (можете сравнить правильность). В итоге мы наглядно видим не только данные по каждой бумаге, но и сколько может денег приносить наш портфель в целом и какая у него дивидендная доходность. Можно поиграть с наименованием бумаг, чтобы увеличить дивидендную доходность портфеля.
Сектора
Необязательный столбец. Показывает к какому сектору относятся ваши акции. Я использую его для наглядности.
В шаблоне выводится две диаграммы - сколько веса занимает в вашем портфеле каждый сектор. Одна диаграмма показывает запланированный веса портфеля (бенчмарк). Вторая - реальные.
В чем суть?
Во-первых, когда вы выбираете эмитентов в свой портфель, сразу видно распределение по секторам. Это помогает избежать сильного доминирования одного сектора в портфеле. К примеру, большинство крупных компаний на Мосбирже относятся к нефтегазовому сектору. И если вы захотите собрать портфель из 10 акций голубых фишек, то, скорее всего, больше половины веса будет приходиться на нефтегаз. А это с точки зрения диверсификации - не есть гуд. И желательно такой портфель разбавить акциями из других отраслей.
Этот же принцип работает и в обратную сторону. Мы можем посмотреть на шаблонный вариант портфеля и заметить, что нам не хватает какого-нибудь сектора (доля которого в портфеле презрительно мала или вообще отсутствует). Это будет нам сигнал к размышлению.
Во-вторых, на диаграмме по реальному распределению по секторам мы сразу можем увидеть сильно ли наш портфель "разъехался", по сравнению с шаблонным вариантом.
К примеру, глядя на диаграммы ниже, я сразу вижу, что доля сектора "Металл и добыча" у меня намного больше запланированного. А вот сектор "Нефтегаз" сильно отстает. Следовательно, мне нужно направлять в него все новые деньги в первую очередь. И пока не вкладываться в Металлы.
Файл-шаблон
Файл для учета равно взвешенного портфеля доступен по ссылке. Напоминаю, чтобы утащить к себе, нужно в меню "Файл" выбрать "Создать копию". В этом случае вам будет доступно редактирование документа.
Удачных инвестиций!
Буду рад услышать обратную связь.
В следующей части расскажу про 5 способов собрать портфель из акций.
Большое человеческое спасибо. Давно мечтал о такой таблице, да ума не хватало её самому сделать.
👍
Огромное спасибо!!
Этого очень не хватало…сам не спец чтобы такую самому сделать….
Максим, спасибо!
Формулы в ячейках M23, O23, P23 не на весь диапазон, поэтому ошибки
ага, спс.
Еще в ячейке F23 нет формулы суммы
Максим, добрый день. Спасибо за таблицу, очень удобно.
Вопрос. Есть ли в телеге на вашем канале чат? Было бы здорово, если бы подписчики могли бы общаться друг с другом. + вам было бы проще находить например идеи для новых постов?!
Огромное спасибо!
Большое спасибо за шаблон! Максим, какой стратегии планируете придерживаться сами в ближайшее время? По сути нам не оставляют выбора, либо инвестировать в Россию, либо идти к зарубежному брокеру, но там тоже пока полно рисков.
Я все свои активы сосредоточил в РФ. Пока покупаю только наше-отечественное. То, до чего не сможет дотянуться рука Запада.
У меня был счет в IB, но еще в марте, взвесил возможные риски я распродал все свои ETF и вывел деньги на счета в РФ.
Я считаю, что лучше принять на себя риски инфляции, отмены дивов, просадку на десятки процентов. Чем получить бан, заморозку или экспроприацию своих активов где-то там (или даже здесь, имею в виду акции с СПБ). Возможно я неправильно поступаю, но мне так спокойнее.
Максим, спасибо за такой подробный ответ по волнующей теме
Спасибо! Максим, я правильно понял, что можно даже добавить какие-то бумаги? Тогда процент в т.н. бенчмарке можно выставить отличный от 5% ? Т.е. на базе Вашей таблички можно составить вобщем-то любой портфель?
Да.
Ещё раз спасибо!!
Буду тестить. Честно признаюсь, что хочу сделать благодаря Вашей заготовке,- состав свой, не индексный. Пропорции тоже свои. Просто портфель, собранный от трёх брокеров.
Если всё заработает,- просто замечательно!
Для наглядности можно было бы добавить графическое сравнение с бенчмарком (я бы взял индекс MOEX Total Return).
Можно будет задавать вопросы, если что… ?
«… Можно будет задавать вопросы, если что… ?»
Попробуйте😀😀
Под переменное кол-во акций с разными долями слишком много курочить.
Я в Exel получаю назв/цену/дивиденды/купоны по API Мосбиржи. И у меня проблемы по всего двум акциям: IRAO и AFKS,- тянется какая-то липа.
В Вашей табличке я проверил,- всё нормально.
А IMPORTxml на локальной машине в Excel у меня что-то не работает.
Отличная задумка. Очень полезная. Спасибо! Однако посли копирования файла, столбец K ( вес в портфеле) перестаёт считать и выдаёт ошибку в формуле.
Скопировал себе на другой аккаунт — вроде работает. Иногда может выдавать ошибку по некоторым котировкам. Но обычно в течение нескольких минут это проходит.
Гениально!!!
Причем я первую Вашу таблицу сам стал доделывать именно в такой вид! Спасибо!
Большое человеческое спасибо за ваш труд🙏
Спасибо. Отлично. Но, всё-таки ETF на российский рынок попроще будет, а результат в долгосроке примерно тот-же.
(П.С.: полиметалл можно сразу выкидывать).
Спасибо за табличку, буду пользоваться
Спасибо, Максим! Это просто супер таблица)) скажите, пожалуйста, как Вы решаете сколько веса должен занимать в портфеле каждый сектор?
Максим просто потрясающе
ВЕЛИКОЛЕПНО! Я инвестирую с 2010 года и стараюсь использовать Google таблицы в своей деятельности. Вашей обязательно буду пользоваться для ведения учёта Российских акций. ОГРОМНОЕ СПАСИБО!!! Я знаю о чём говорю.
👍
Добрый день, Максим.
Можно ли в таблице учитывать ещё и фонды?
В портфеле же не только акции.
Писал ранее цикл статей как выдергивать дивы, котировки по облигациями и фондам.
Касательно фондов — вот ссылка. Правда сейчас в связи с заморозкой некоторые котировки не подгружаются.
Максим, подскажите, пожалуйста, причину подобного отображения формул после скачивания файла
=ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION(«IMPORTXML(«»http://iss.moex.com/iss/engines/stock/markets/shares/securities/SBER.xml»», «»/document/data[@id=»»»»marketdata»»»»]/rows/row[@BOARDID=»»»»TQBR»»»»]/@MARKETPRICE»»)»);130)
Не могу сказать …
При сохранении из Гугл-таблицы —> на компьютер в формате Excel формулы проставляются как в Вашем примере. Т.е. выдаёт фиксированную цену 130 для SBER.
Добрый день. При замене тикера, котировки не подтягиваются с биржи.
Подскажите, пожалуйста, что не так?
Изменения нужно проводить в формуле (столбец котировки) и лучше прописывать тикер заглавными буквами
На примере сбера
=IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/shares/securities/SBER.xml«, «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQBR»»]/@MARKETPRICE»)
Немного доработал табличку которая теперь говорит сколько нужно купить лотов эмитента а не отдельное количество акций, думаю Вам тоже стоит так сделать — все же удобнее.
Да и подтягивания цены автоматизировать можно =IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/shares/securities/»&C4&».xml», «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQBR»»]/@MARKETPRICE»)
Ячейка C4 и именем тикера.
Спасибо добрый человек. Так точно удобнее.
Супер! Спасибо что делитесь не только знаниями, но и практическими материалами!
Пожалуйста!
Максим, спасибо большое за таблицу! Очень полезно! Вопрос про обновление тикера. Сохранил себе на гугл-диск копию. Поменял у Сбера тикер на SBERP по вашей инструкции. Выдает ошибку в ссылке (Ссылка http://iss.moex.com/iss/engines/stock/markets/shares/securities/SBERP.xml недействительна). Не знаете, в чем может быть дело? Повторюсь, ничего не менял, только P добавил (в латинской раскладке).
Иногда бывают проблемы с доступностью тикеров. Попробуйте повторить через время. Обычно помогает.
спасибо!!!
а еще было бы интересно вводить не сумму «взноса», а целевой размер портфеля. скажем, я стремлюсь накопить портфель на 1млн рублей. как в этом случае должны выглядеть инвестиции, сколько осталось по той или иной позиции? что думаете?
Можно и так сделать. Формулы немного переписать и будет работать )))
Большое спасибо за таблицу!
Если еще добавить лист Движение денежных средств, куда заносить все покупки и продажи, то и никакие сервисы по учету портфелей не нужны!
Денис, добрый день. Подскажи, почему не подгружаются котировки. в твоей таблице старые данные висят. У меня открывается такая же фигня. пробую править ошибку сбивается вся формула
Максим пожалуйста для тугодума где находится формула чтобы поменять тикер
Колонка «котировки»
Колонка «котировки»
https://i.imgur.com/b2MUbAd.jpeg, Все сделал как написано в инструкции, но пишет следующее, ссылка не действительна. Пытался Газпромнефть подтянуть. В формуле все одинаково как у вас.
С облигациями так же получиться сделать?
А не че то что некоторые акции продаются лотами? Например МТС 1=10