Таблица учета биржевых инструментов находится по адресу:
Прежде, чем начать пользоваться таблицей, необходимо сделать ее копию на свой диск, для этого в меню выбираем Файл —> Создать копию.
Далее переходим на свою копию таблицы, которая теперь хранится на вашем диске и доступна для редактирования.
Чуть забегая вперед опишу один технический момент. К сожалению нет единого источника котировок различных инструментов. Поэтому в таблице используются три.
Для российских акций можно использовать данные Московской биржи, Yahoo Finance, Google Финансы (с рядом ограничений, не работают привилегированные акции) поэтому рекомендую использовать наиболее универсальный это Yahoo Finance.
Для зарубежных акций подойдет как Yahoo, так и Google Финансы.
Для российских облигаций, фондов торгующихся на московской бирже, использовать данные с Мосбиржи.
Понимаю, много текста и уже кажется все сложно… но на самом деле, надо только попробовать, по сути вы выбираете из трех — четырех готовых строчек и смотрите подтянулись ли данные и все.
Спешу вас успокоить, это был самый сложный момент 🙂 Далее все максимально примитивно.
Теперь по-порядку.
- Удаляем все строки, оставляем только одну №4, или по количеству бумаг в вашем портфеле.
- Заполняем в клетке С4 тиккер вашего инструмента.
В данной строчке используются данные из Yahoo Finance. Это можно понять следующим образом:
В клетке H4 использована формула функции, которая подтягивает данные о текущей цене инструмента. В клетке B4 формула, которая подтягивает название. Если вам не нравится название или вы его хотите сократить или написать что-то на свое усмотрение, можете удалить формулу в клетке B4 и текстом вписать свое, это далее ни на что не влияет.
Выбор формулы для получения текущей котировки биржевого инструмента рекомендую выбирать по следующему принципу. сначала попытаться использовать Yahoo, так как у низ самая обширная база по тиккерам.
=YahooFinanceSG(C4;«price»)
Для российских активов надо использовать тиккер следующего вида.
SBERP.ME то, есть в конце добавляем .ME
Для немцев .DE
Так-же бывают разночтения в написании самого тиккера, к примеру у российских брокеров депозитарные расписки на акции Shell имеют тиккер RDS.A, у Yahoo RDS-A.
Самостоятельно можно проверить написание тиккера на Yahoo вбив там название компании.
https://finance.yahoo.com
В 99% случаев вам хватит Yahoo, что касается акций, ETF, БПИФ.
Если вы не смогли найти какой-то тиккер, то напишите мне в телеграмм, обязательно помогу. (@GlAnton) и подпишитесь на канал, если еще не сделали этого, там выходят регулярные обновления таблицы (@DailyInsder).
В клетке D4 автоматически подтянется валюта, в которой номинирован эмитент.
В столбце А заполняем порядковые номера, я делаю так, чтобы при сортировке по столбцу А порядок инструментов совпадал с приложением брокера для удобства сверки.
В столбце Е заполняем сектор, к которому принадлежит инструмент (не обязательно, пока можно оставлять пустым) в будущем планирую сделать подсчет процента по секторам. Чтобы наглядно видеть уровень диверсификации вашего портфеля в разрезе отраслей.
В столбце F — заполняем количество акций.
В столбце G — вносим вашу среднюю цену по инструменту.
D2 — текущий курс USD
G2 — текущий курс EUR
J5 — здесь можно указать процент доходности, который вам интересен. Например для вас приемлем размер дивидендов не ниже 5%. То, в данной строчке указываете 5 и в столбце I зеленым будут выделены те инструменты, которые можно покупать по текущим ценам, красным, которые уже не проходят по вашим требования к доходности. И указана цена, ниже которой должна быть котировка, чтобы вам вновь стало интересно покупать данный актив, с точки зрения див. доходности.
Важно: Расчет идет уже чистых дивидендов с учетом столбца O, в котором указывается налог на дивиденды текущего эмитента и исходя из вашего режима налогообложения (к примеру, если вы подписали W-8BEN форму, то для американских акций там надо указать 13$ — 10% удержит США, и 3% вы самостоятельно уплатите в налоговую, подав 3НДФЛ. Если нет, то ваш налог будет 30%)
В столбце J рассчитывается учтенная стоимость ваших активов, т.е. сколько вы потратили средств на их приобретение. В клетке J2 общая сумма затрат по всем позициям.
В столбце К рассчитывается текущая стоимость инструмента исходя из котировок в моменте. К2 — соответственно стоимость портфеля на данный момент.
Столбец L — текущий доход или убыток по каждому активу.
M1, N1 — рост или падение портфеля на сумму и процент.
Столбец М — процент актива в зависимости от вашей средней цены
Столбец N — процент инструмента в портфеле исходя из его текущей стоимости.
Это удобно использовать для ребалансировки портфеля. Для восстановления процентного соотношения активов.
В столбцах P Q R S заполняются дивиденды, 4 столбца, это четыре квартала. Заполнять можно на свое усмотрение. По итогу они суммируются в столбце T
Зеленым цветом выделяю те, что уже получены на брокерский счет. Это необязательно.
Колонка U — здесь вы видите, сколько вам принесет дохода инструмент, рассчитывается сумма уже с учетом налога. В клетке U2 — сумма дивидендов за год. U1 — дивиденды в рублях по текущему курсу.
Столбец V — покажет вашу дивидендную доходность исходя из вашей средней цены.
Столбец W — доходность согласно текущим котировкам.
X2 — дивидендная доходность, если бы у вас в портфеле все активы были бы в равных долях.
Y2 — див. доходность с учетом процентного соотношения активов в портфеле. То, есть реальная.
Столбец X — здесь можно указать сумму, которую вы бы хотели получать от данного инструмента в год.
Столбец Y — покажет, сколько вам надо докупить единиц или на сколько перевыполнен план, если число будет с минусом.
Столбец Z — покажет на сколько процентов вы близки к цели по данному активу.
Столбец AA — сколько надо вложить средств исходя их текущей стоимости для достижения цели.
Для простоты и удобства рекомендую портфель разделять на валюты, в которых номинированы инструменты. То, есть сделать портфель рублевый, долларовый, евро и т.д.
Но если вы хотите мультивалютный подход, то необходимо выбрать основную валюту, к примеру USD и все отличные инструменты приводить к USD, для правильности расчетов, это делается следующим образом:
Выбираем ту валюту, которая преобладает в нашем портфеле, пусть это будет USD и в портфеле, есть Сбер, а он номинирован в RUB
Столбцы: J, K, U надо разделить на курс USD (клетка E2), но чтобы не нарушалась сортировка делим на $E$2
=F4*G4/$E$2
Значок $ нужен, чтобы при сортировке и растягивании формулы не смещалась ячейка от куда будет браться курс доллара.
Все, теперь все последующие расчеты у нас будут проводиться в одной валюте USD.
Правила округления
У ряда компаний дивиденды имеют достаточно мизерный размер, пример ВТБ, то в ячейках этих эмитентов увеличиваем количество отображаемых знаков после запятой.
А для того, чтобы корректно рассчитывалось необходимое количество акций, в столбце Y для этого эмитента, надо изменить до какого знака округлять.
То, есть если дивиденд имеет 5 знаков после запятой:
0,80091
то в формуле ставим 5
=ROUNDUP(ROUND((X25—U25);5)/ROUND((U25/F25);5))
ОФЗ и корпоративные облигации
Рекомендую их собирать в отдельный портфель, но можно и в общий.
Для них придется использовать данные с московской биржи.
В столбце B самостоятельно заполняем название, В столбце D самостоятельно заполняем валюту RUB.
Столбец H
Для ОФЗ
=VALUE(REGEXREPLACE(IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/bonds/securities/»&C31&«.xml»; «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQOB»»]/@MARKETPRICE»);«[.]»;«,»))
C31 — ячейка с тиккером инструмента.
Для корпоративных облигаций
=VALUE(REGEXREPLACE(IMPORTXML(«http://iss.moex.com/iss/engines/stock/markets/bonds/securities/»&C32&«.xml»; «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQCB»»]/@MARKETPRICE»);«[.]»;«,»))
C32 — ячейка с тиккером инструмента.
Также все эти формулы доступны в базовой таблице с примерами.
Добавление новой позиции в таблицу
Выделяете последнюю строку, проматываете в крайний правый угол и тяните за квадратик вниз на одну строку. Меняете необходимые данные, Формулы сами перенесутся с учетом изменения положения всех ячеек.
Резюме
Чуть позже запишу еще и видео инструкцию. Если, что не понятно пишите в телеграмме в закрепленном посте, буду помогать и дополнять данный мануал.
Добавить комментарий
Для отправки комментария вам необходимо авторизоваться.