Портфель взвешенный по капитализации или в равных долях (часть 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 способов собрать портфель из акций.

 

♻️ Приглашаю заглянуть в мой небольшой 👉 Телеграм-канал, где мы вместе используем различные финхаки и прочие неэффективности для увеличения своего капитала в инвестициях и жизни.


Оцените статью
Записки инвестора
Добавить комментарий

  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) автор

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

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

      Сергей, в какую колонку вставлять? ничего почему-то не работает, пишет ошибку.

      Ответить
  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) автор

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

      Ответить
  25. Ирина

    Большое спасибо за таблицу!
    Если еще добавить лист Движение денежных средств, куда заносить все покупки и продажи, то и никакие сервисы по учету портфелей не нужны!

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

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

    Ответить
  27. АЛЕКСЕЙ

    Максим пожалуйста для тугодума где находится формула чтобы поменять тикер

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

      Колонка «котировки»

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

      Колонка «котировки»

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

    https://i.imgur.com/b2MUbAd.jpeg, Все сделал как написано в инструкции, но пишет следующее, ссылка не действительна. Пытался Газпромнефть подтянуть. В формуле все одинаково как у вас.

    Ответить
  29. Владислав

    С облигациями так же получиться сделать?

    Ответить
  30. Vladislav

    А не че то что некоторые акции продаются лотами? Например МТС 1=10

    Ответить
  31. Константин

    Просто супер! Спасибо,
    А есть то же самое только по ОФЗ. Хотел бы собрать ОФЗ шки что бы выплата была ежемесячно.

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

      По ОФЗ нет )))

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

    Добрый день, файл не скачивается. Он еще доступен?

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

      Да вроде лежит на месте.Ссылка рабочая.

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

        Так и не открывает файл
        «Не удалось открыть файл.
        Проверьте правильность адреса и повторите попытку.»
        Можно на электронку отправить?

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

    Спасибо от души! Наконец-то я нашел стратегию и главное шаблон, который мне по душе. Неделю уже рою интернет

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

      Пожалуйста! )))

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

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

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

    Автор, как сделать такую же таблицу, но на 30 акций? В эксель я очень слаб, увеличил столбцы, но формулы сразу перестали работать((

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

      Ну это же базовые азы. Неужели сложно посмотреть в сети как работать с таблицами

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

        Перерыл кучу информации, не нашел данного ответа. Это нужно изучать программирование на уроне эксперта, и знать все нюансы эксель. Я могу добавить пару столбцов, но таблица сразу ломается, и показывает не верные данные. приходится иметь 2 таких таблицы, и вести акции в двух папках(( Эх, если бы можно было сделать одну единую на 30 штук…(

        Ответить
  36. Роман

    Максим, спасибо за таблицу! Научи выдергивать данные по дивидендам за 12 месяцев с сайта Доход. Я что-то не понимаю как

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

      Пользуйтесь )))
      Формула же есть в таблице по дивам с дохода ))) Копируй и пользуйся!

      Ответить
      1. Роман

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

        =ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION(«iferror(index(IMPORTHTML(«»https://www.dohod.ru/ik/analytics/dividend/»»&(LOWER(B3)),»»table»», 2),2,2),0)»);39,25)

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

          я в эксель не силен

          Ответить
      2. Роман

        В гугл-документы проблем нет, а если скачать и открыть в excel, то проблемы с выдергиванием данных

        Ответить
        1. Вадим

          Потому что эксель — это оффлайн программа, а гугл — онлайн. Поэтому экселька у вас не сработает

          Ответить
  37. Юля

    Добрый день!
    Переходя по ссылке выдает ошибку «Не удалось открыть файл.
    Проверьте правильность адреса и повторите попытку.»
    Можно на электронку отправить?

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

      проверил — работает.

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

    Добрый день, также не могу скачать, если не трудно направьте на электронку, Заранее спасибо!!!

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

      Скорее всего у вас гугл диск заполнен. Освободите место и попробуйте еще раз.

      Ответить
  39. Олеся

    Здравствуйте, не могу открыть файл, при создании копии выдает ошибку.

    Не удалось открыть файл.

    Проверьте правильность адреса и повторите попытку.

    Есть ли возможность, отправить мне на почту файл? leseno4ek1987@gmail.com
    Заранее благодарю)

    Ответить
  40. Артур

    Здравствуйте, не могу скачать таблицу, система требует входа в Google, что на данный момент не возможно. Не могли бы вы отправить на почту. Заранее спасибо.

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

      отправил

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

    добрый день, как сделать чтобы таблица раз в сутки обновляла котировки или хотя бы пореже их запрашивала?

    Ответить
  42. Василий Иваглвич

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

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

    Добрый день! Максим , может быть кто то уже поднимал этот вопрос,но в комментариях к Вашим статьям не нашел. Случайно нет шаблона для отслеживания стоимости иностранных ценных бумаг ? с Инвестинга например.Заранее Вам благодарен за ответ. Таблицы Ваши очень пригодились.

    Ответить
  44. ТОм

    Есть возможность на 30 акций сделать таблицу? Я не могу, хоть по вашему это очень просто( Если нужно заплатить, давайте договоримся тогда?!

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

      напишите мне в телегу — договоримся
      https://t.me/LomovoyVD

      Ответить
  45. Кристина

    Добрый день
    подскажите пжл а если это не акция а облигация то как настроить в формуле чтобы отображалась цена актуальная? пробовала поставить isin — не работает

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

      ответил в личку

      Ответить
      1. Вячеслав

        Как скачать?

        Ответить
  46. Катя

    Здравствуйте есть такая табличка по облигациям??

    Ответить
  47. Максим

    добрый день, не открывается ссылка. не могли бы вы прислать эти таблички на мыло max94nvrsk@gmail.com? Буду вам за это очень благодарен!

    Ответить
  48. Анастасия

    Здравствуйте! Пожалуйста, отправьте файл на мою электронную почту. Не могу сама скачать

    Ответить