30.11.21
41K
85

Как я сделал таблицу, чтобы проанализировать свой портфель на ИИС

Следить за изменением стоимости акций и соблюдать пропорции

Как я сделал таблицу, чтобы проанализировать свой портфель на ИИС

Этот текст написал читатель в Сообществе. Бережно отредактировано и оформлено по стандартам редакции.

Я решил завести таблицу для отслеживания результата инвестиций на ИИС в мае 2020 года.

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

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

Главная страница таблицы состоит из нескольких блоков:

  1. Состав портфеля. Показывает, сколько у меня сейчас акций, текущую и средние цены, прибыль/убыток, текущие пропорции и пропорции после покупки.
  2. Графики трендов и изменения цен за выбранный период.
  3. Общая информация по портфелю. Смотрю здесь, обгоняю ли я вообще инфляцию.
  4. График отдельно выбранного актива с минимальным техническим анализом.
Так выглядит главная страница таблицы
Так выглядит главная страница таблицы
Действия в портфеле — показывает, какие транзакции были совершены. Стоимость всех покупок и продаж, комиссии и пополнения — все тут
Действия в портфеле — показывает, какие транзакции были совершены. Стоимость всех покупок и продаж, комиссии и пополнения — все тут
Этот раздел показывает текущий состав портфеля по отдельным акциям. Можно посмотреть, сколько места в портфеле занимают разные акции и насладиться диверсификацией. Данные по составу ETF подгружаются из открытых источников
Этот раздел показывает текущий состав портфеля по отдельным акциям. Можно посмотреть, сколько места в портфеле занимают разные акции и насладиться диверсификацией. Данные по составу ETF подгружаются из открытых источников
Здесь находятся исторические данные актива за последние 30 дней. Смотрю динамику, которая иногда влияет на мое решение покупать или не покупать тот или иной актив. Иногда могу купить чуть позже
Здесь находятся исторические данные актива за последние 30 дней. Смотрю динамику, которая иногда влияет на мое решение покупать или не покупать тот или иной актив. Иногда могу купить чуть позже

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат

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

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


Редакция
Редакция
30.11.21, 14:16
Какие инструменты помогают вам следить за инвестиционным портфелем?
Загрузка
тьфунатебя ¯\_(ツ)_/¯

Дорогие Тиньков Инвестиции, а можно вы возьмете Автора себе на работу, чтобы он этот отчет прикрутил в личном кабинете Инвестиций?!
Ну крутая же фишка будет!

124
Volonden Auditore

тьфунатебя, конечно прикрутят, всего за 99р в месяц

13
тьфунатебя ¯\_(ツ)_/¯

Volonden, они на % хорошо зарабатывают...

1
Максим Алексеев
Отредактировано

Когда я писал себе софт для автоматизации, самым геморройным оказался расчёт налогов. Но в моем случае было много облигаций, и налоги мне нужны были для расчёта точной доходности.

По состоянию портфеля - к сожалению, из моих 3 брокеров только Тинек даёт для этого API. Мне кажется они вообще одни такие в РФ, у кого API свой и бесплатный. Безусловно, молодцы

19
Павел Никифоров

Максим,

Изображение пользователя Павел Никифоров
15
Максим Алексеев
Отредактировано

Павел, спасибо. я все время путаю, он такой один или такой как все.

7
Павел Никифоров
Отредактировано

Максим, Тиньков — как все, а Тинькофф — такой один:))

5
Вован Мясниченко

Максим, API без документации толком и техподдержки.

0
Максим Алексеев

Вован, я смотрел в мае 20го, когда оно только вышло. сейчас нашел свои токены и ноутсы:

Docs
https://api-invest.tinkoff.ru/openapi/docs/
https://tinkoffcreditsystems.github.io/invest-openapi/swagger-ui/
https://github.com/TinkoffCreditSystems/invest-openapi-java-sdk

то есть что-то из документации есть, по качеству расскажите детали тогда.

по ТП - ну так это ж для физиков все. есть телеграм канал, есть публичный багтрекер https://github.com/TinkoffCreditSystems/invest-openapi/issues. если нужно что-то серьезное в плане ТП, то это надо включаться по fix/fast как организация и через других провайдеров.

0
Даниил Прокофьев

Таблица не работает

3
Екатерина Малоземова

Даниил, точнее ссылку удалили :(

1
Виталий Василенко

Отличная штука, жаль, что не работает

3
Роман Иваницкий

Попробуйте Snowball-income, мне кажется это проще и лучше..

3
Егор Караваев

Роман, не знал про них, выглядит конечно здорово. Пожалуй даже лучшее что я видел среди таких сервисов. Но, опять же, необходимо загружать брокерские отчеты (муторно) или в случае Тинькофф давать свой токен (небезопасно). Они конечно пишут, что все безопасно и все хранится в зашифрованном виде, но кто его знает. Я бы не хотел давать ключи от полного доступа в свой портфель непонятно кому.

3
Julia

Егор или Роман, а можно ли в Snowball давать для Тинька токен песочницы, то есть через который нельзя торговать, а только считать, выгружать исторические данные и моделировать? Это наверное безопаснее

1
Роман Иваницкий

Julia, К сожалению Тинькофф не выдаёт по такому токену реальные данные, только по виртуальному портфелю. Знаю что многие просят у них сделать "нормальный" токен только для чтения, но пока только обещают что-то сделать... Поэтому как есть, либо доверять сервису (токен хранят в зашифрованном виде, ключ придумывает пользователь и он нигде не хранится, вводится при каждой синхронизации), либо грузить отчёты вручную.

3
Егор Караваев

Julia, без понятия. Надо пробовать.

0
Роман Иваницкий

Егор, Единственное, что могу посоветовать - включить в кабинете брокера подтверждение всех сделок по смс, тогда невозможно без кода из смс провести сделку по апи токену.

0
Егор Караваев

Роман, конечно так и сделал.

0
Рамиль Альфредович

Добрый день. Подскажите пожалуйста, сделал все по инструкции , в итоге появились тикеры
из ИИС ниже таблицы, но в самой таблице ERROR. Например в ячейке А1 формула =getIISPort(), я понимаю что в скобках должно быть значение, но есть инструкция что конкретно и где нужно прописывать.

И в редакторе скриптов 18:25:01 Примечание Выполнение начато
18:25:02 Ошибка
TypeError: Cannot read property 'replace' of undefined
isoToDate @ API.gs:11

в этой строчке const str = dateStr.replace(/-/, '/').replace(/-/, '/').replace(/T/, ' ').replace(/\+/, ' \+').replace(/Z/, ' +00')

Ничерта не понимаю в скриптах, но очень хотелось бы разобраться. Спасибо

2
Дмитрий Жук

Рамиль, согласно того, что вижу на гитхабе:

function isoToDate(dateStr){
// How to format date string so that google scripts recognizes it?
// https://stackoverflow.com/a/17253060
const str = dateStr.replace(/-/,'/').replace(/-/,'/').replace(/T/,' ').replace(/\+/,' \+').replace(/Z/,' +00')
return new Date(str)

функция принимает в себя аргументом дату в виде строки - то что вам видимо ранее вернул АПИ Тинькова. И далее в этой строке она наводит "красоту" через реплэйсы чтобы потом вернуть в формат, но уже не текста, а даты (return new Date(str))

Вам просто надо написать в скобках формулы =getIISPort("дату в виде текста") как ссылку на ячейку где она возвращена от тинькофф апи.

Просто если вы в формуле ничего не передаете, ей и обрабатывать нечего, потому скрипт и ругается на undefined

1
Роман Эйзен

Дмитрий, очень непонятно звучит. Просто покажите, как должен выглядеть в этом месте конечный скрипт...

0
Дмитрий Жук

Роман, какую ошибку он у вас выдает? У меня при подключении выдавал 429 ошибку, но автор статьи описал алгоритм действий :

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

Как только я добавил нужное количество строк - всё взлетело

0
Роман Эйзен
Отредактировано

Дмитрий, я только сейчас понял, что =getIISPort - это то, что находится в самом документе, а не в скрипте シ
Но все равно - я не вижу ячеек, где была бы дата в виде текста, возвращенная от тинькофф апи
Я не программист, поэтому чтобы что-то смог сделать, нужно чтобы объяснили пошагово что и куда тыкать... заранее прошу прощения, что ничего не понимаю
(строки добавлял - ничего не изменилось)

1
Дмитрий Жук

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

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

В связи с этим первый вопрос - у вас именно ИИС? Потому что функция работает только с ИИС, в скрипте прописано что brokerAccountType == "TinkoffIis"

Второй вопрос - вы получили апи токен на сайте тинькофф инвестиции и прописали его в скрипте? Без него вообще ничего работать не будет.
Это восьмая строчка в скрипте с именем "API"
const OPENAPI_TOKEN = "сюда-надо-вставить-апи"

2
Егор Караваев

Роман, напишите мне на почту. Постараюсь помочь.

0
Alexandr Sudakov

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

2
yuber

а в сбере есть опенапи?

1
Егор Караваев

yuber, вроде только тинькофф такой прогрессивный. Есть какое-то апи у мосбиржи, но я там ноги поломал.

3
Егор Караваев

Алексей, через googlefinance или importxml? Очень интересно узнать ваш способ, т.к. тоже пытался сначала тянуть данные с мосбиржи. Первая не работает со многими российскими тикерами, а импорт с сайтов корявый. Мало кто предоставляет исторические данные бесплатно, а в Tinkoff API они есть.

1
Алексей Тур

Егор, c гуглфинанс подтягиваю только курс доллара, с API Мосбиржи почему-то не работает корректно. А исторические данные мне не нужны, только текущие котировки для расчета куда сколько надо внести.
Общий вид формулы для российских акций:
=IMPORTxml("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST", concatenate("//row[@SECID='",C6,"']/@LAST"))
С6 тут это ячейка, куда у меня забит тикер.
Но есть некоторые нюансики для фондов, долларовых активов и облиг, там будет чуть по другому

2
Дмитрий Жук

Егор,
https://dmitriizhuk.ru/2021/11/17/importxml-%d0%bf%d0%b0%d1%80%d1%81%d0%b8%d0%bc-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d0%b5-%d0%b8%d0%b7-%d0%b8%d0%bd%d1%82%d0%b5%d1%80%d0%bd%d0%b5%d1%82%d0%b0-%d0%b8-%d0%b5%d1%89%d0%b5-%d0%be%d0%b1/
корявый, да, но если подняться на уровень повыше в пути импортхмла - во многих случаях можно парсить весь "блок" сразу а потом его причесывать. Посмотрите ссылку, буквально недавно делал нечто похожее, правда более упрощенное :)

1
Егор Караваев

Алексей, в этой таблице даже вносить ничего не надо. Она своей жизнью живет полностью. Плюс можно посмотреть историю сделок. Вручную всё это забивать долго и мучительно. А скрипты не такие уж сложные если разобраться)

1
Денис Спиридонов

Можно подушнить слегка?) Таблица огонь, но iferror() все же надо освоить)

1
Алексей Покревский

спасибо, полезная информация, но очень сыровато, надо под себя дорабатывать

1
Егор Караваев

Алексей, цели масштабировать не было, поэтому да, надо дорабатывать для себя.

2
Константин Жуков

Дмитрий, так считать проценты неправильно. Если у одного фонда комиссия 0.79, а у второго 0.1 и доли в портфелях равные - тогда вы отдаете (0,79+0,1)/2=0,445 в среднем, а никак не 0.89. Исходя их этого можно набирать сколько угодно, выбирая с минимальными издержками

1
Владимир Б

Дмитрий, а зачем перемножать 0,79% на 8 фондов? По каждому фонду теряем 0,79% ежегодно, не больше и не меньше.

1
Дмитрий Покревский

Очень интересно разобраться. А как подключить брокерский счет?

1
Егор Караваев

Дмитрий, скрипты немного разные. Внутри есть пример если покопаться.

1
Роман Тихонов

Советую таки освоить массивы, а то вот от такого кода становится не по себе:
let A2_buy = 0;
let A3_buy = 0;
let A4_buy = 0;
let A5_buy = 0;
let A6_buy = 0;
let A7_buy = 0;
let A8_buy = 0;
let A9_buy = 0;

Я понимаю когда активов меньше 9 (хотя уже не очень), но а если сотня? А если их количество меняется... Если сидеть и дописывать это до 100 (потом до 1000000) активов, то программирование станет не таким интересным)))

1
Егор Караваев

Роман, полностью согласен) В свое оправдание скажу, что это был мой самый первый код на JavaScript после hello world. В данном случае сработало правило "работает - не трогай". Как-то не обращал внимания, но вообще конечно надо будет исправить хотя бы ради очищения души. Боюсь представить какие ещё грехи найдут тру программисты в этом коде.

1
Елена Ткаченко

А по-моему, так это просто выброшенное время. Ну сколько и чего вы купите на 400тыс....Неужели сами посчитать не сможете, а больше 400тыс. в год нет смысла заводить на ИИС, т.к. вычета не получите с большей суммы, а без вычета вообще смысла нет, т.к. больше 100% в год вы не заработаете. У меня и сынв уже по третьему ИИСу открыто с 2015г., и всё просчитано. И ещё, есть брокеры, у которых в личном кабинете вы на графике можете видеть результаты, и расчёт налогов проводить хоть каждый день, автоматически за 1 минуту делают.

1
Егор Караваев

Елена, ну хотя бы для того чтобы понимать свою годовую доходность. А это вы вряд ли посчитаете на коленке в блокноте.

2
Егор Караваев

Роман, давайте e.k.karavaev@gmail.com

1
Антон Могилев

Смысл от ETF только в том что надо подождать пока они вырастут достаточно и можно их продавать ?
По ним же никаких промежуточных выплат нет

1
Егор Караваев

Антон, если вы про дивидендную историю, то тут я резко против. Зачем мне заморачиваться их реинвестированием, да еще и платить лишние налоги если за меня их реинвестирует провайдер ETF? Расчет на рост активов конечно.

1
Евсикова Елена

О боги финансов! Как же хочется такую табличку заиметь, но все попытки сделать по инструкции провалились((((( Какие то разные ошибки то 401 то 500...
А кто может помочь с настройкой?

1
Егор Караваев

Евсикова, напишите на почту e.k.karavaev@gmail.com

0
Егор Караваев

Владимир, переделать не сложно. Мб займусь этим как будет время.

1

Сообщество

Лучшее за неделю