Сделал шаблон для учета инвестиций по стратегии равно взвешенного портфеля. Расскажу вкратце что умеет делать таблица.
Таблица состоит из нескольких блоков. Для удобства и наглядности блоки выделены разными цветами. Вот как это выглядит у меня на начальном этапе.
Начало - веса, котировки и названия
Перед началом пользования таблицей нужно указать сколько акций в портфеле вы хотите иметь. Это нужно для вычисления доли на одну акцию (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
Просто супер! Спасибо,
А есть то же самое только по ОФЗ. Хотел бы собрать ОФЗ шки что бы выплата была ежемесячно.
По ОФЗ нет )))
Добрый день, файл не скачивается. Он еще доступен?
Да вроде лежит на месте.Ссылка рабочая.
Так и не открывает файл
«Не удалось открыть файл.
Проверьте правильность адреса и повторите попытку.»
Можно на электронку отправить?
Спасибо от души! Наконец-то я нашел стратегию и главное шаблон, который мне по душе. Неделю уже рою интернет
Пожалуйста! )))
Не подтягивается стоимость акции, я так понимаю из-за точки в цене выдает ошибку. Подскажите пожалуйста как исправить.
Автор, как сделать такую же таблицу, но на 30 акций? В эксель я очень слаб, увеличил столбцы, но формулы сразу перестали работать((
Ну это же базовые азы. Неужели сложно посмотреть в сети как работать с таблицами
Перерыл кучу информации, не нашел данного ответа. Это нужно изучать программирование на уроне эксперта, и знать все нюансы эксель. Я могу добавить пару столбцов, но таблица сразу ломается, и показывает не верные данные. приходится иметь 2 таких таблицы, и вести акции в двух папках(( Эх, если бы можно было сделать одну единую на 30 штук…(
Максим, спасибо за таблицу! Научи выдергивать данные по дивидендам за 12 месяцев с сайта Доход. Я что-то не понимаю как
Пользуйтесь )))
Формула же есть в таблице по дивам с дохода ))) Копируй и пользуйся!
К сожалению не работает. Вот такая формула выходит. Помоги пожалуйста разобраться
=ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION(«iferror(index(IMPORTHTML(«»https://www.dohod.ru/ik/analytics/dividend/»»&(LOWER(B3)),»»table»», 2),2,2),0)»);39,25)
я в эксель не силен
В гугл-документы проблем нет, а если скачать и открыть в excel, то проблемы с выдергиванием данных
Потому что эксель — это оффлайн программа, а гугл — онлайн. Поэтому экселька у вас не сработает
Добрый день!
Переходя по ссылке выдает ошибку «Не удалось открыть файл.
Проверьте правильность адреса и повторите попытку.»
Можно на электронку отправить?
проверил — работает.
Добрый день, также не могу скачать, если не трудно направьте на электронку, Заранее спасибо!!!
Скорее всего у вас гугл диск заполнен. Освободите место и попробуйте еще раз.
Здравствуйте, не могу открыть файл, при создании копии выдает ошибку.
Не удалось открыть файл.
Проверьте правильность адреса и повторите попытку.
Есть ли возможность, отправить мне на почту файл? leseno4ek1987@gmail.com
Заранее благодарю)
Здравствуйте, не могу скачать таблицу, система требует входа в Google, что на данный момент не возможно. Не могли бы вы отправить на почту. Заранее спасибо.
отправил
добрый день, как сделать чтобы таблица раз в сутки обновляла котировки или хотя бы пореже их запрашивала?
Добрый день, подскажите как уменьшить время опроса данных с сервера а то целиком таблица не может прогрузиться.
Добрый день! Максим , может быть кто то уже поднимал этот вопрос,но в комментариях к Вашим статьям не нашел. Случайно нет шаблона для отслеживания стоимости иностранных ценных бумаг ? с Инвестинга например.Заранее Вам благодарен за ответ. Таблицы Ваши очень пригодились.
Есть возможность на 30 акций сделать таблицу? Я не могу, хоть по вашему это очень просто( Если нужно заплатить, давайте договоримся тогда?!
напишите мне в телегу — договоримся
https://t.me/LomovoyVD
Добрый день
подскажите пжл а если это не акция а облигация то как настроить в формуле чтобы отображалась цена актуальная? пробовала поставить isin — не работает
ответил в личку
Как скачать?
Здравствуйте есть такая табличка по облигациям??
добрый день, не открывается ссылка. не могли бы вы прислать эти таблички на мыло max94nvrsk@gmail.com? Буду вам за это очень благодарен!
Здравствуйте! Пожалуйста, отправьте файл на мою электронную почту. Не могу сама скачать