Excel для анализа сделок

Учет портфеля и сделок: опыт и выводы

• 29 мая 2017, 11:29
• |

• хорошо
• 
  16

Размещённые в настоящем разделе сайта публикации носят исключительно ознакомительный характер, представленная в них информация не является гарантией и/или обещанием эффективности деятельности (доходности вложений) в будущем. Информация в статьях выражает лишь мнение автора (коллектива авторов) по тому или иному вопросу и не может рассматриваться как прямое руководство к действию или как официальная позиция/рекомендация АО «Открытие Брокер». АО «Открытие Брокер» не несёт ответственности за использование информации, содержащейся в публикациях, а также за возможные убытки от любых сделок с активами, совершённых на основании данных, содержащихся в публикациях. 18+

АО «Открытие Брокер» (бренд «Открытие Инвестиции»), лицензия профессионального участника рынка ценных бумаг на осуществление брокерской деятельности № 045-06097-100000, выдана ФКЦБ России 28.06.2002 (без ограничения срока действия).

ООО УК «ОТКРЫТИЕ». Лицензия № 21-000-1-00048 от 11 апреля 2001 г. на осуществление деятельности по управлению инвестиционными фондами, паевыми инвестиционными фондами и негосударственными пенсионными фондами, выданная ФКЦБ России, без ограничения срока действия. Лицензия профессионального участника рынка ценных бумаг №045-07524-001000 от 23 марта 2004 г. на осуществление деятельности по управлению ценными бумагами, выданная ФКЦБ России, без ограничения срока действия.

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

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

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

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

По просьбам трудящихся выкладываю небольшую заметку на тему формирования статистики и анализа своих сделок.

Для анализа сделок я часто использую функционал Excel, который называется «сводная таблица». На основе истории сделок можно за 1-2 минуты сделать очень серьезный анализ. Это и общий профит/убыток в разбивке по активам, и расчет прибылей/убытков в зависимости от ставки, и (если ведется) отработка сетапов и многое другое.

К примеру, на основе такой статистики я выявил еще 4 месяца назад, что основные мои убытки были на паре EURUSD. Исключив эту пару из наблюдений, я заметно улучшил свои показатели.

Итак, что нам необходимо:
1. Статистика сделок. В МТ ее можно выгрузить в HTML формат, выделить мышью и скопировать в Excel. В других терминалах и брокерах по разному. Где то придется мышкой выделять и вставлять в Excel и корректировать, где то есть встроенный функционал.
2. MS Excel версии 2007 и выше.

Итак, допустим, у нас есть вот такая статистика сделок в Excel:

Количество полей, столбцов не важно, главное, чтобы был примерно такой табличный вид.

Открываем вкладку «Вставка», жмем «Сводная таблица»

Автоматом выделится весь наш диапазон сделок. Жмем ОК, создается болванка на отдельном листе:

Справа видим наши поля из таблицы. С ними то и будем работать, крутить вертеть.

Прежде всего добавим в поле «Название строк» (перетаскиванием мыши) поле «Актив». Слева сразу отобразится сгруппированный список валют. В графу «Значение» добавим поле «Профит». Нажмем на «профит» справа снизу, откроем его свойства и выставим там опцию «Сумма».

Теперь еще раз перетащим поле «Профит» в колонку «Значения», зайдем в свойства и выставим «Количество»:

Слева на листе у нас автоматом все меняется, и мы начинаем видеть активы, количество сделок по ним, какой профит и убыток был. Но это еще не все. Часть данных считается некорректно и нам надо эти данные убрать.
Для этого мы открываем свойства справа снизу в графе «Названия строк» — «Актив». Там есть опция «Итоги» — ставим «Нет». Также берем справа сверху поле «Направление» и перетаскиваем в раздел «Названия строк».

И что же мы видим в итоге за 3 минуты? А то, что AUDNZD, EURUSD приносит нам убытки независимо от вида ставки, вверх или вниз. А вот AUDUSD, GBPUSD, USDJPY вполне себе прибыльные. Также мы видим количество сделок по каждой паре.

Теперь уберем активы и воткнем сумму сделки. Получим примерно вот такое:

И что мы видим? А то, что 121 сделка по 40 рублей (образно, само собой, суммы и прочее — фиктивные) принесла нам основную прибыль. А сделки по 80 рублей — кучу убытков.

Собственно, на этом все. Можно менять поля, перетаскивать туда-сюда в разных вариациях активы, суммы, направления и сразу видеть, где основные косяки. Если в отдельном поле прописан сетап, типа IB, OB, pinbar и т.д. — по нему также можно провести статистику и увидеть, что, например, пинбар нифига не отрабатывает в твоей стратегии, а вот IB просто супер.