Портфель взвешенный по капитализации или в равных долях (часть 5) | Готовый шаблон для учета инвестиций

Сделал шаблон для учета инвестиций по стратегии равно взвешенного портфеля. Расскажу вкратце что умеет делать таблица.

Таблица состоит из нескольких блоков. Для удобства и наглядности блоки выделены разными цветами. Вот как это выглядит у меня на начальном этапе.

Учет инвестиций
Общий вид таблицы по учёту равно взвешенного портфеля

Начало - веса, котировки и названия

Перед началом пользования таблицей нужно указать сколько акций в портфеле вы хотите иметь. Это нужно для вычисления доли на одну акцию (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. Сколько итого нужно записать?" Или бывает случайно удалил данные по количеству акций, к примеру того же Сбера. Типа рука дрогнула и ты не заметил сразу (и не можешь сделать отмену действий).  И что нужно сделать, чтобы восстановить данные? Пройтись по всем своим брокерам, посмотреть нет ли у них акций Сбера. А если удалил не одну, а несколько ячеек? У меня так было несколько раз. Приходилось не только восстанавливать, но делать сверку по всем брокерам - вдруг я что-то еще удалил случайно.

Второй минус - ты не видишь полной картины, какие бумаги и у какого брокера у тебя находятся.

Для подобных случаев я добавил разделение по брокерам. Заполняем количество по каждому брокеру отдельно, а потом все автоматом суммируется в отдельную колонку (акций куплено) При необходимости можно нажимать на "плюсик" (или "минус") и колонки с брокерами будут скрыты.

Учет акций - таблица
При необходимости можно добавить новые колонки. К примеру и меня на данный момент ценные бумаги раскиданы по 8 (восьми) счетам.

Помощь в ребалансировке

Для наглядности я сделал колонку "Расхождение весов". Она показывает на сколько отклоняются текущие пропорции акций от первоначально заданных. В зависимости от цвета колонки инвестор понимает, что ему нужно сделать с акциями:

  • красный цвет - вес акции в портфеле превышен. Нужно продать часть.
  • зеленый цвет - доля акций меньше заданного. Нужно докупать.

Если портфель постоянно пополняется, то продавать необязательно. Можно выравнивать пропорции покупкой отстающих, доля которых на данный момент минимальна, а расхождение максимально (зеленый цвет).

Пропорции акций
Колонка "Расхождение весов" показывает насколько отклонился вес каждой акции по сравнению с бенчмарком. Зеленый цвет - сигнал к покупке (маловато веса). Красный - к продаже (доля превышена).

Новые пополнения

В таблице можно заполнить поле "Сумма для инвестиций (кэш)" и система сама посчитает каких акций и в каком количестве нужно купить. Причем учитывается уже купленные акции.

По сути - это подсказка куда направить новые поступления денег. Даже думать не надо. 😁

Какие акции купить в портфель
Вносим сколько денег мы хотим инвестировать и таблица нам напишет какие акции нужно купить

Дивиденды

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

Дивы - учет
Мы можем примерно знать сколько дивидендов способен приносить портфель

Сектора

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

Акции по секторам

В шаблоне выводится две диаграммы - сколько веса занимает в вашем портфеле каждый сектор. Одна диаграмма показывает запланированный веса портфеля (бенчмарк). Вторая - реальные.

В чем суть?

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

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

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

К примеру, глядя на диаграммы ниже, я сразу вижу, что доля сектора "Металл и добыча" у меня намного больше запланированного. А вот сектор "Нефтегаз" сильно отстает. Следовательно, мне нужно направлять в него все новые деньги в первую очередь. И пока не вкладываться в Металлы.

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

Файл-шаблон

Файл для учета равно взвешенного портфеля доступен по ссылке. Напоминаю, чтобы утащить к себе, нужно в меню "Файл" выбрать "Создать копию". В этом случае вам будет доступно редактирование документа.

Удачных инвестиций!

Буду рад услышать обратную связь.

В сведущей части расскажу про 5 способов собрать портфель из акций.

 
  • Чтобы не пропустить новые статьи, подпишитесь на Telegram, VK или на рассылку анонсов по почте
  • Есть вопросы? 🗣 Задайте их в комментариях. 🔥
  • Благодарность автору - здесь. 🎁

 
Оцените статью
Поделиться с друзьями
Записки инвестора
Добавить комментарий

Внимание! Ваш комментарий будет опубликован после модерации.

  1. Николай

    Большое человеческое спасибо. Давно мечтал о такой таблице, да ума не хватало её самому сделать.

    Ответить
    1. Максим (vse-dengy) автор

      👍

      Ответить
  2. Александр

    Огромное спасибо!!
    Этого очень не хватало…сам не спец чтобы такую самому сделать….

    Ответить
  3. dn-m-ru

    Максим, спасибо!
    Формулы в ячейках M23, O23, P23 не на весь диапазон, поэтому ошибки

    Ответить
    1. Максим (vse-dengy) автор

      ага, спс.

      Ответить
  4. dn-m-ru

    Еще в ячейке F23 нет формулы суммы

    Ответить
  5. Алексей

    Максим, добрый день. Спасибо за таблицу, очень удобно.
    Вопрос. Есть ли в телеге на вашем канале чат? Было бы здорово, если бы подписчики могли бы общаться друг с другом. + вам было бы проще находить например идеи для новых постов?!

    Ответить
  6. Валерий

    Огромное спасибо!

    Ответить
  7. Андрей

    Большое спасибо за шаблон! Максим, какой стратегии планируете придерживаться сами в ближайшее время? По сути нам не оставляют выбора, либо инвестировать в Россию, либо идти к зарубежному брокеру, но там тоже пока полно рисков.

    Ответить
    1. Максим (vse-dengy) автор

      Я все свои активы сосредоточил в РФ. Пока покупаю только наше-отечественное. То, до чего не сможет дотянуться рука Запада.
      У меня был счет в IB, но еще в марте, взвесил возможные риски я распродал все свои ETF и вывел деньги на счета в РФ.
      Я считаю, что лучше принять на себя риски инфляции, отмены дивов, просадку на десятки процентов. Чем получить бан, заморозку или экспроприацию своих активов где-то там (или даже здесь, имею в виду акции с СПБ). Возможно я неправильно поступаю, но мне так спокойнее.

      Ответить
      1. Светлана

        Максим, спасибо за такой подробный ответ по волнующей теме

        Ответить
  8. sva52

    Спасибо! Максим, я правильно понял, что можно даже добавить какие-то бумаги? Тогда процент в т.н. бенчмарке можно выставить отличный от 5% ? Т.е. на базе Вашей таблички можно составить вобщем-то любой портфель?

    Ответить
    1. Максим (vse-dengy) автор

      Да.

      Ответить
      1. sva52

        Ещё раз спасибо!!
        Буду тестить. Честно признаюсь, что хочу сделать благодаря Вашей заготовке,- состав свой, не индексный. Пропорции тоже свои. Просто портфель, собранный от трёх брокеров.
        Если всё заработает,- просто замечательно!
        Для наглядности можно было бы добавить графическое сравнение с бенчмарком (я бы взял индекс MOEX Total Return).
        Можно будет задавать вопросы, если что… ?

        Ответить
        1. Максим (vse-dengy) автор

          «… Можно будет задавать вопросы, если что… ?»
          Попробуйте😀😀

          Ответить
          1. sva52

            Под переменное кол-во акций с разными долями слишком много курочить.
            Я в Exel получаю назв/цену/дивиденды/купоны по API Мосбиржи. И у меня проблемы по всего двум акциям: IRAO и AFKS,- тянется какая-то липа.
            В Вашей табличке я проверил,- всё нормально.
            А IMPORTxml на локальной машине в Excel у меня что-то не работает.

  9. Алексей.

    Отличная задумка. Очень полезная. Спасибо! Однако посли копирования файла, столбец K ( вес в портфеле) перестаёт считать и выдаёт ошибку в формуле.

    Ответить
    1. Максим (vse-dengy) автор

      Скопировал себе на другой аккаунт — вроде работает. Иногда может выдавать ошибку по некоторым котировкам. Но обычно в течение нескольких минут это проходит.

      Ответить
  10. Дмитрий

    Гениально!!!
    Причем я первую Вашу таблицу сам стал доделывать именно в такой вид! Спасибо!

    Ответить
  11. Елизавета

    Большое человеческое спасибо за ваш труд🙏

    Ответить
  12. Олег

    Спасибо. Отлично. Но, всё-таки ETF на российский рынок попроще будет, а результат в долгосроке примерно тот-же.
    (П.С.: полиметалл можно сразу выкидывать).

    Ответить
  13. Артем

    Спасибо за табличку, буду пользоваться

    Ответить
  14. Елизавета

    Спасибо, Максим! Это просто супер таблица)) скажите, пожалуйста, как Вы решаете сколько веса должен занимать в портфеле каждый сектор?

    Ответить
  15. Елена

    Максим просто потрясающе

    Ответить
  16. Анатолий

    ВЕЛИКОЛЕПНО! Я инвестирую с 2010 года и стараюсь использовать Google таблицы в своей деятельности. Вашей обязательно буду пользоваться для ведения учёта Российских акций. ОГРОМНОЕ СПАСИБО!!! Я знаю о чём говорю.

    Ответить
    1. Максим (vse-dengy) автор

      👍

      Ответить
  17. Андрей

    Добрый день, Максим.
    Можно ли в таблице учитывать ещё и фонды?
    В портфеле же не только акции.

    Ответить
    1. Максим (vse-dengy) автор

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

      Ответить
  18. Андрей

    Максим, подскажите, пожалуйста, причину подобного отображения формул после скачивания файла
    =ЕСЛИОШИБКА(__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)

    Ответить
    1. Максим (vse-dengy) автор

      Не могу сказать …

      Ответить
    2. sva52

      При сохранении из Гугл-таблицы —> на компьютер в формате Excel формулы проставляются как в Вашем примере. Т.е. выдаёт фиксированную цену 130 для SBER.

      Ответить
  19. Елизавета

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

    Ответить
    1. Максим (vse-dengy) автор

      Изменения нужно проводить в формуле (столбец котировки) и лучше прописывать тикер заглавными буквами
      На примере сбера
      =IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/shares/securities/SBER.xml«, «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQBR»»]/@MARKETPRICE»)

      Ответить
  20. Сергей

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

    Ответить
  21. Сергей

    Да и подтягивания цены автоматизировать можно =IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/shares/securities/»&C4&».xml», «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQBR»»]/@MARKETPRICE»)

    Ячейка C4 и именем тикера.

    Ответить
    1. Максим (vse-dengy) автор

      Спасибо добрый человек. Так точно удобнее.

      Ответить
  22. Наталья

    Супер! Спасибо что делитесь не только знаниями, но и практическими материалами!

    Ответить
    1. Максим (vse-dengy) автор

      Пожалуйста!

      Ответить
  23. Антон

    Максим, спасибо большое за таблицу! Очень полезно! Вопрос про обновление тикера. Сохранил себе на гугл-диск копию. Поменял у Сбера тикер на SBERP по вашей инструкции. Выдает ошибку в ссылке (Ссылка http://iss.moex.com/iss/engines/stock/markets/shares/securities/SBERP.xml недействительна). Не знаете, в чем может быть дело? Повторюсь, ничего не менял, только P добавил (в латинской раскладке).

    Ответить
    1. Максим (vse-dengy) автор

      Иногда бывают проблемы с доступностью тикеров. Попробуйте повторить через время. Обычно помогает.

      Ответить
      1. Аноним

        спасибо!!!

        Ответить
  24. Антон

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

    Ответить
    1. Максим (vse-dengy) автор

      Можно и так сделать. Формулы немного переписать и будет работать )))

      Ответить