Как сделать таблицу в Excel для анализа портфеля ИИС.

Следить за изменением стоимости акций и соблюдать пропорции. Я решил завести таблицу для отслеживания результата инвестиций на ИИС в мае 2020 года. На ИИС я придерживаюсь четкого плана в противовес случайному характеру покупок на брокерском счете. Никогда особо не доверял различным сервисам по анализу инвестиций. Могу сравнить их со своей таблицей при надобности, но никогда не отдам анализ в чужие руки. Мне важно досконально понимать, как считается каждое число, и своя таблица в этом плане опережает все подобные сервисы. Сам я работаю в сфере автоматизации, и потому таблица, по моему мнению, также должна быть полностью автоматизирована. Принципиально не хочу ничего вводить руками: знаю, что когда-нибудь забуду об этом или мне просто надоест. Основная часть информации подгружается благодаря OpenAPI Тинькофф Инвестиций. Почти все сделано с помощью скриптов. Формул — минимальное количество. Никаких брокерских отчетов загружать не надо. Главная страница таблицы состоит из нескольких блоков: Состав портфеля. Показывает, сколько у меня сейчас акций, текущую и средние цены, прибыль/убыток, текущие пропорции и пропорции после покупки. Графики трендов и изменения цен за выбранный период. Общая информация по портфелю. Смотрю здесь, обгоняю ли я вообще инфляцию. График отдельно выбранного актива с минимальным техническим анализом.

Особенности таблицы

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

Для начала я составил план распределения активов в портфеле. У вас должна быть тактика, которой вы будете придерживаться, иначе это все бессмысленно. Для себя решил, что мой портфель будет состоять только из ETF:

• США (FXUS и FXIT) — 30%;
• развитые страны (Германия FXDE + FXDM) — 30%;
• развивающиеся страны (Россия TMOS, Китай FXCN) — 30%;
• евробонды FXRU — 5%;
• золото FXGD — 5.
  
  

Целевые пропорции указаны в столбце Target allocation. Столбец Current allocation показывает текущие пропорции в портфеле. Класс активов не имеет значения.

Моей задачей было написать алгоритм, следуя которому я мог бы покупать акции, максимально приближаясь к заветным пропорциям. Для этого я написал отдельный скрипт — WhatToBuy.js, который считывает текущие цены активов, их пропорции и доступный бюджет; а также ищет наибольшее отклонение от плановой доли актива и присуждает каждому активу приоритет для покупки.

В первую очередь алгоритм будет «покупать» активы с наибольшим отклонением — до тех пор, пока не восстановит заданные пропорции. Затем перейдет к следующему по приоритету активу и так по кругу, пока не кончится бюджет. Детально описывать не буду, при желании можно самому разобраться в коде.

Алгоритм довольно прост, но потребуются небольшие знания JavaScript. Результатом его работы станет точное указание, в каких количествах покупать тот или иной актив, сколько комиссии уйдет брокеру (Fee, строка 10) и сколько бюджета останется (Cash, строка 10).

Как сделать такую же таблицу

Я создавал таблицу на базе готового исходного кода. Низкий поклон этому человеку. Просто скопируйте код с этого сервиса и посмотрите, что и как работает. Если у вас есть навыки программирования, то вам не составит труда быстро адаптировать его под свои задачи.

Если навыков нет, то вкратце этот процесс добавления скрипта выглядит так:

• Открываете новую гугл-таблицу.
• Переходите в раздел Apps Script в меню «Расширения».
• Придумываете название проекта и вставляете скопированный код.
• Получаете в личном кабинете брокера OpenApi-токен и вставляете его в кавычках в строке 4, чтобы получилось const OPENAPI_TOKEN = «ваш токен».
• Сохраняете скрипт.
  
  

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

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

Я также сделал шаблон своей таблицы, чтобы вы могли настроить его под себя. Чтобы воспользоваться им, сначала нужно получить доступ к своему портфелю при помощи OpenApi Тинькофф Инвестиций. Это самый сложный этап, так как подразумевает совершение нескольких нетривиальных операций.

Для начала придется получить уникальный токен для работы со своим портфелем — программистом тут быть не надо. Весь процесс хорошо описан в документации по ссылке. Если вкратце, нужно зайти в настройки своего инвестиционного счета в браузере и выбрать внизу настроек пункт «Токен для OpenAPI».

В самой таблице порядок такой:

• Перейти в пункт меню Расширения → Apps Script.
• Скопировать токен из Тинькофф Инвестиций и вставить его вместо фразы Paste your token here.
• Сохранить скрипт и нажать на кнопку Refresh в основной таблице. Скорее всего, таблица попросит разрешение на выполнение скрипта — для этого нужно зайти в «Дополнительные настройки» → «Перейти на страницу скрипта» и дать разрешения.
  

Важно: если в вашем портфеле больше восьми активов, функция выдаст ошибку — ей просто не хватит строк. В этом случае нужно добавить нужное количество пустых строк ниже девятой — как только строк станет достаточно, данные подгрузятся автоматически. Еще в этом случае придется увеличить диапазоны в некоторых формулах ниже — по умолчанию они будут считать только диапазон со второй по девятую строку.

Результат.

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

Мне, в принципе, скорее интересен сам результат программирования, чем результат инвестиций. Не могу остановиться и постепенно добавляю новые фишки. Побочным эффектом подучил JavaScript и «Эксель», наверное, пока это самая большая польза от этой таблицы.

Источник