Муки выбора

В последние год-два появилось много постов, как сделать таблички в Google Sheets/MS Excel для отображения актуальной информации о своем инвестиционном портфеле и т.п. Действительно хочется видеть, что там творится. Особенно актуально для продвинутых подписок/тарифных планов, когда требуется определенная сумма на счетах, и будет обидно, если из-за падения акций или курса доллара сумма снизится ниже пороговой…

Есть много инструментов для отслеживания инвестиционного портфеля. И сервисы, и таблицы… Мне хотелось иметь что-то с одной стороны легкое (не требуется учета купонов и т.п.), а с другой – чтобы легко настроить именно такой вид, такие параметры и срезы, как хочется именно мне.

Так что я попробовал несколько вариантов от электронных таблиц до записи в MySQL скриптом на python, и на текущий момент остановился на отображении моих инвестиционных счетов в Grafana.

MS Excel

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

К счастью, вовремя обнаружил, что на Android ни через приложение (даже при подписке Office Home, что на 5 членов семьи), ни через web, формулы FilterXML не работают.

Пробовал и другие клиенты, и всякие Libre Office, ничего не помогло. А я хотел универсальное решение, и со смартфона, и с планшета…

Google Sheet

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

Это и «исторические данные» (хотелось смотреть графики изменений, пусть примерные, без свечей, конечно, но все-таки). И возможность сохранения (при экспорте в Excel работоспособность теряется, а я очень привык иметь файл на компьютере, доступный offline, и при этом периодически синхронизирующийся с OneDrive).

И конфиденциальность (в OneDrive я синкаю через GoodSync с шифрованием, т.е. в облаке хранится версия, зашифрованная моим локальным ключом, а в Google Sheet все, включая токен к Тинкофф Инвестициям, было бы в открытом виде).

Python + MS Excel

В рамках осваивания python написал скриптик, дергающий Инвестиции и сохраняющий все (в т.ч. периодически историю баланса аккаунта) в XSLX.

Разумеется, тут можно накрутить много чего. Но все-таки с построением графиков и т.п. были сложности. Частые апдейты через cron на отдельной машине – слишком файл разрастется. Да и на ноут его забирать… С ноута непосредственно скрипт запускать – не всегда в одно и то же время возможно, графики толком не построить…

Пробовал в MySQL писать – из Excel не смог удобно графики строить даже через MySQL Workbench. Можно, наверное, но понял, что усилия для этого потребуются неадекватные (как выбирать временной интервал для отображения - масштаб, смещение...)

Текущий финальный выбор

Поскольку в это же время я разбирался с популярными системами мониторинга, а именно с Prometheus и Grafana, стал делать на них дашборды для домашней лабы и других личных серверов, подумал: «А почему бы и финансовый дашборд не замутить?»

Погуглил, нашел пару проектов по экспорту из Тинькофф Инвестиций в Prometheus, решил, что раз другие так делают, это не совсем дурацкая идея, и стал делать.

Что получилось

Посмотрел пару репозиториев (https://github.com/maksim77/tinkoff_exporter и https://github.com/byumov/tinkoff_investing_exporter). Не совсем мне подошли. Поскольку написаны Go, которого я совсем не знаю, поправить их я не смог, поэтому стал писать свое на python. И воспользовался некоторыми идеями по организации дашборда на Grafana.

В итоге получилось https://github.com/Anrikigai/promTinkoff. Описание там есть, а здесь я больше сфокусируюсь на особенностях и проблемах. Может быть кому-то это поможет сделать что-то более информативное.

Prometheus

В контейнере запускаю скрипт, периодически опрашивающий API Tinkoff Инвестиции и презентующий в формате Prometheus. По умолчанию раз в 10 минут, этот же интервал указан и конфигурации Prometheus:

- job_name: 'tinkoff'
  scrape_interval: 600s
   static_configs:
     - targets: ['promTinkoff:8848']

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

В конечном итоге данные представляются в виде

tcs_item{account="Tinkoff", balance_currency="EUR", currency="EUR", instance="promTinkoff:8848", job="tinkoff", name="Тинькофф Вечный портфель EUR", ticker="TEUR", type="Etf"}

tcs_item{account="Tinkoff", balance_currency="USD", currency="EUR", instance="promTinkoff:8848", job="tinkoff", name="Тинькофф Вечный портфель EUR", ticker="TEUR", type="Etf"}

tcs_item{account="Tinkoff", balance_currency="RUB", currency="EUR", instance="promTinkoff:8848", job="tinkoff", name="Тинькофф Вечный портфель EUR", ticker="TEUR", type="Etf"}

tcs_item{account="Tinkoff", balance_currency="RUB", currency="USD", instance="promTinkoff:8848", job="tinkoff", name="Pfizer", ticker="PFE", type="Stock"}

Причем для удобства дальнейшего использования данные по каждой позиции экспортируются в трех валютах: EUR, USD, RUB (balance_currency). Соответственно, далее я могу отображать результаты в требуемой валюте не пересчитывая их каждый раз по курсу на тот момент.

При этом currency получается от Tinkoff. Скажем, для "Тинькофф Вечный портфель EUR" currency=EUR, для Pfizer - currency=USD.

Аналогичный подход с дублированием в трех валютах используется для оценки прибыли/убытков в tcs_yield.

tcs_yield{account="Tinkoff", balance_currency="EUR", currency="USD", instance="promTinkoff:8848", job="tinkoff", name="Pfizer", ticker="PFE", type="Stock"} 90.99262579525735

tcs_yield{account="Tinkoff", balance_currency="USD", currency="USD", instance="promTinkoff:8848", job="tinkoff", name="Pfizer", ticker="PFE", type="Stock"} 107.5

tcs_yield{account="Tinkoff", balance_currency="RUB", currency="USD", instance="promTinkoff:8848", job="tinkoff", name="Pfizer", ticker="PFE", type="Stock"} 7866.3125

Также сохраняю курсы в tcs_rate.

И помимо вывода данных по каждому аккаунту, получаемого от Тинькофф Инвестиций (в данном случае Tinkoff, TinkoffIis), также добавляю автоматически посчитанные записи для фиктивного аккаунта _Total_

tcs_item{account="_Total_", balance_currency="RUB", currency="Multi", instance="promTinkoff:8848", job="tinkoff", name="_Total_", ticker="_Total_", type="_Total_"}

tcs_yield{account="_Total_", balance_currency="RUB", currency="Multi", instance="promTinkoff:8848", job="tinkoff", name="_Total_", ticker="_Total_", type="_Total_"}

Grafana

Суммы брокерского аккаунта скрою, буду показывать на примере недавного перенесенного в Тинькофф ИИС.

На содержимое не обращайте внимания. Хотя там и есть и акции, и облигации, в реальности я консервативен, рассчитываю на долгосрок, и растить его буду за счет ETF/БПИФ.

Ниже опишу основные элементы этой секции.

Portfolio

В левом верхнем углу дашборда я хочу видеть баланс счета крупными цифрами сразу во всех трех валютах (balance_currency).

Использую обычный виджет Stat и для каждого из полей перекрываю способ отображения (символ валюты):

Поскольку API возвращает среднюю цену позиции и баланс (количество), их произведение, выводимое в tcs_item, является суммой потраченных (инвестированных) средств.

Для получения текущей стоимости проще всего прибавить ожидаемые прибыли/убытки (tcs_yield). Поэтому везде, где нужна текущая стоимость, и используется tcs_item() + tcs_yield().

Details

Таблица с тремя query (для каждой из валют) типа

Sum(tcs_item{balance_currency="RUB"}) by (account) + Sum(tcs_yield{balance_currency="RUB"}) by (account)

И пришлось добавить Transform (убрать поле Time и поименовать колонки)

Также для красоты я переопределил суммарный аккаунт _Total_ в TOTAL

Profit/Loss

Такая же табличка, только запросы проще:

Sum(tcs_yield{balance_currency="RUB"}) by (account)

Profit/Loss without Currency

Значительная доля на брокерском счету у меня в валюте, и мне интересно отделить результат инвестирования от банальной валютной переоценки кеша. Поэтому еще одна табличка без Currency:

Sum(tcs_yield{balance_currency="RUB",account=~"T.*",type!="Currency"}) by (account)

Для ИИС видно, что портфель целиком в небольшом минусе (там до последнего момента лежали евро). При этом «w/o currency» слегка плюс.

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

Графики

Portfolio целиком в разных валютах

Две линии: текущая стоимость портфеля (tcs_item + tcs_yield) и штрих-пунктиром инвестированная (в данном случае небольшой минус)

Sum(tcs_item{balance_currency="RUB",account=~"T.*"}) + Sum(tcs_yield{balance_currency="RUB",account=~"T.*"})

Sum(tcs_item{balance_currency="RUB",account=~"T.*"})

Portfolio w/o Currency

То же, но за вычетом кеша. Желтая линия имеет ступеньку, в то время как сам портфель целиком такой не имеет. Это потому что я не вводил дополнительные средства на счет, а просто потратил остаток свободных рублей на покупку ETF на Казначейские облигации США (запарковал кеш в ожидании, что после выборов доллар подрастет).

И раздельно по портфелям

Поскольку курс евро и доллара более-менее близки, их я «прижал» к правой оси.

К сожалению, я не знаю, как задать масштаб с «дельтой» для плавающих значений. Поэтому для валют «дельта» достаточно велика (доллар формирует нижнюю границу графика, евро - верхнюю). И их колебания выглядят незначительными. А вот в рублях «расколбас по осям» всегда от минимума до максимума, хотя на самом деле относительные изменения могут быть совсем невелики. Хотелось бы рубль также вогнать в диапазон 10-20% (как отношение евро/доллар), но не знаю как это сделать.

Currency

tcs_rate{currency=~"USDRUB"}

tcs_rate{currency=~"EURRUB"}

Item info

Вверху дашборда можно выбрать инструмент для получения информации по нему

Для таблички используется 6 query. 3 «суммы» для каждой из валют и 3 просто tcs_yield

sum(tcs_item{name="$Item",balance_currency="RUB"}) + sum(tcs_yield{name="$Item",balance_currency="RUB"})

 sum(tcs_yield{name="$Item",balance_currency="RUB"})

Для графиков текущего баланса и «инвестированного»:

sum(tcs_item{name="$Item",balance_currency="RUB"}) by (name) + sum(tcs_yield{name="$Item",balance_currency="RUB"}) by (name)

 sum(tcs_item{name="$Item",balance_currency="RUB"}) by (name)

В данном случае отображаю в рублях. Ну, тут на выбор.

Важно отметить параметр $Item. Он как раз задает инструмент, выбранный вверху. Также он используется и при формировании заголовка:

Current position and yield - $Item

Портфель (TinkoffIis)

Для графиков и таблички используется Time series, как обычно. Из интересного – группирую данные по имени инструмента (name) и в легенде укаываю {{ name }}, чтобы в табличке было соответсвующее название. В качестве альтернативы можно использовать {{ticker}}. Как вывести оба типа “PFE (Pfizer)” не знаю.

Sum здесь используется для единообразного подхода для total, если инструмент встретится в обоих.

Можно группировать по разному

Для оценки диверсификации не просто выбираем 3 query в разных валютах, но и разбиваем их по типу актива. Это позволяет отдельно увидеть, например, Currency RUB и Currency EUR.

sum(tcs_item{balance_currency="RUB",account=~"TinkoffIis", currency="RUB"}) by (type) + sum(tcs_yield{balance_currency="RUB",account=~"TinkoffIis", currency="RUB"}) by (type)

 sum(tcs_item{balance_currency="RUB",account=~"TinkoffIis", currency="USD"}) by (type) + sum(tcs_yield{balance_currency="RUB",account=~"TinkoffIis", currency="USD"}) by (type)

 sum(tcs_item{balance_currency="RUB",account=~"TinkoffIis", currency="EUR"}) by (type) + sum(tcs_yield{balance_currency="RUB",account=~"TinkoffIis", currency="EUR"}) by (type)

При этом в качестве Value используется последнее ненулевое значение (Last *), а для отображения (Legend) также и доля (Percent)

Для отдельного обзора каждого типа инструмента (ну если бы их было много разных), можно сделать отдельные Pie chart типа

sum(tcs_item{type="Etf",account=~"TinkoffIis",balance_currency="RUB"}) by (name) + sum(tcs_yield{type="Etf",account=~"TinkoffIis",balance_currency="RUB"}) by (name)

sum(tcs_item{type="Stock",account=~"TinkoffIis",balance_currency="RUB"}) by (name) + sum(tcs_yield{type="Stock",account=~"TinkoffIis",balance_currency="RUB"}) by (name)

и т.п.

Немного о грустном

Диверсификация

Хотя я и привел пример с диверсификацией портфеля, в реальности не стоит особо на нее полагаться. Это ведь обычно делается для оценки риска (скажем, 90% акции / 10% облигации – портфель с высоким риском). Однако, ETF на облигации достаточно консервативны, по идее стоит отнести их к Bond, но не представляю, как это сделать, как их отделить от ETF на акции, относящиеся все же к более рискованным активам.

Впрочем, ETF FXFA (FinEx Fallen Angels UCITS ETF - Высокодоходные корпоративные облигации развитых стран) хоть и облигации, но я бы не стал относить их к консервативным. Уверен, что в кризис они ощутимо обвалятся. Так что я не стал заморачиваться. Для этой цели все равно что-то специализированное нужно (да хоть intelinvest не так давно научился ETF по секторам раскидывать).

Другая проблема - валюты Условный Пфайзер я купил за доллары, и он реально котируется в долларах. Он значится в Stock, USD. Но есть много зарубежных инструментов, купленных за рубли. И они попадут в "рублевую часть", потому что Тинькофф вернет currency="RUB" , хотя на самом деле это валютные активы, "застрахованные" от падения курса рубля.

Банковские счета и вклады

Хочется видеть в одном дашборде также и другие средства. Уж как минимум из того же Тинькофф, только уже банка. Но увы, этот API доступен только бизнес аккаунтам. Физикам вроде его могут подключать, но в индивидуальном порядке.

Кое-кто (к примеру, Дзен-мани) умеют эту информацию извлекать. Но, насколько я понял, они используют API от мобильного клиента. Сделать reverse engineering далеко выходит за пределы моих возможностей.

Так что пока увы.

Впрочем, если когда-то и получится такое сделать, это будет отдельный контейнер для Prometheus. А Grafana все объединит :)

Заключение

Мне хотелось показать, что в Grafana можно делать довольно разнообразные и информативные дашборды даже для не совсем стандартных применений. Экспортер в Prometheus пишется легко. Даже у меня, ни разу не программиста, с этим сложностей не возникло.

Буду рад комментариям. Интересен кому-то такой подход, или это извращение? Я его затеял, чтобы на чем-то практическом потренироваться в Python/Prometheus/Grafana, и своей цели достиг. Но в практической применимости в свете проблем с диверсификацией не очень уверен. Посматривать на текущий баланс, конечно, буду. Но вот принимать решения, чего прикупить исходя из этих графиков - вряд ли.

Комментарии (16)


  1. formikulo
    21.09.2021 14:08

    Очень интересно. Я профан, но тоже хочу что-то подобное сделать. Пишите ещё


    1. Std137
      22.09.2021 21:25
      -2

      Это интересно, только если развивать свои знания в сфере разработки клиентов, для крупных площадок, используя стороннее Api

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

      В свое время работал в заведении, где картинки с электронными мозгами, дурили людей. Зрелище. Люди, со слюнями тыкали кнопки оставляя последнее, что бы потом с пеной у рта доказывать, как выигрывают, богатеют... И следом бежать выносить последнее из дома.

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

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

      А зачем реальные котировки? Их же можно создавать самому... И фокус в том что площадка, имеет инсайдеровскую информацию может влиять на котировки. Тоесть видя ажиатаж скупки, начинать понижать, ажиотаж продажи, поднимать. И тут важно, что кто-то сообразит, заработает. Большинство потеряет. С учётом развития ИИ, эти игры становятся изощрённее.

      Так скажите мне, чем игры в трейдеров, не изощрённая форма игорного бизнеса? А игроки, не теже самые игромания, сменившие экраны?


      1. Anrikigai Автор
        22.09.2021 21:30

        Во многом вы правы. Если вспомнить деривативы (фючерсы...), там спекуляция, да.

        С акциями все-таки несколько иная ситуация. Выросла стоимость всей компании, при том же количестве акций стоимость каждой тоже подросла, тут нормально.

        Про "создание котировок" - это больше про Форекс-кухни, которые не выводят ваши сделки на рынок, а только внутри себя их процессят.

        Но здесь все-таки пост не о трейдинге как таковом, а о конкретной технической задачей.

        Так-то можно вспомнить, что инвестору вообще не сильно надо часто на счет поглядывать. Знай, регулярно пополняй. Лет через 20-30 порадуешься сильно выросшим балансом.


      1. ebragim
        23.09.2021 04:57
        +2

        Смешались вместе кони, люди…
        Регистрируйтесь на нормальной бирже с личным брокерским счётом, а не суржиках вроде всяких форексов. И после покупки акций можете спокойно сделать запрос в ваш депозитарий (для россии это Национальный расчетный депозитарий (НРД) будет), и убедиться что вот вы собственник такого-то количества.
        И этот актив настолько же реален, как и деньги на счету у вас в банке.


      1. it2manager
        23.09.2021 20:17
        +1

        Ребята вполне внятно ответили на вопрос, зачем они это делали. И это никак не относится к возможности заработать или спрогнозировать. Все игры в инвестии на коротком сроке, до года, это медок для лошков. Вы всегда проиграете с вероятностью 99% ) Всякие статьи, вебинары, опыт друзей это все ерунда, потому что ни один из ваших друзей, который рассказывает как он клево заработал, не вкладывал туда 10-ки миллионов. А игрушки в пару сотен тысяч это ни о чем. Лично мой опыт вложения во всякие подобные игрушки -это либо облигации , либо акции крупных компаний, которые дают прибыль выше инфляции и ставок по депозитам, но разбогатеть на этом вряд ли можно ).


  1. KonishchevDmitry
    21.09.2021 20:20
    +3

    Я себе сделал вот такое - https://www.youtube.com/watch?v=fMUxBDY3AUg (но там подход несколько другой - через парсинг отчетов).

    Лично мне всегда хотелось нарисовать то, что ни один брокер никогда не покажет: агрегированные данные по портфелям у нескольких брокеров и реальную среднегодовую доходность с учетом налогов и налоговых вычетов + в принципе некое отражение влияния налогов на итоговую доходность (чтобы оценить целесообразность отдельных налоговых вычетов). В голове всех цифр не сложишь - поэтому хочется, чтобы машина за тебя построила максимально объективную картину того, что у тебя происходит на самом деле.

    То, что рисует большинство брокеров в своих графиках и сводках - прям совсем никуда не годится:

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

    • Отображаемая доходность в долларах - зачастую на самом деле рублевая, пересчитанная по текущему курсу в доллары.


    1. Anrikigai Автор
      21.09.2021 20:49

      Абсолютно точно! Бесит, когда брокер показывая P/L в долларах на самом деле берет P/L в рублях и конвертирует в доллары. А не считает в долларах "с самого начала".

      Ну а то, что у вас и регрессионные тесты, и симуляция сделок - вообще супер. Жаль, что на Rust, которого я совсем не знаю (https://github.com/KonishchevDmitry/investments). Тоже такое хотел, но понял, что пока не под силу.


  1. it2manager
    21.09.2021 22:52

    Интересно, но так и не понял какую задачу пытались решить ?


    1. Anrikigai Автор
      22.09.2021 08:59

      ::)

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

      Хороший побочный эффект, повлиявший на финальный выбор инструмента - освоить Prometheus, Grafana, попрактиковаться в Python на чем-то полезном.

      В комментарии выше Дмитрий чуть подробнее описал свои хотелки, когда делал нечто подобное (что интересно, тоже на Prometheus + Grafana).


  1. valyala
    26.10.2021 10:54

    А не пробовали использовать VictoriaMetrics вместо Prometheus? Туда можно отправлять данные в разных форматах в т.ч. и в формате Prometheus text exposition. И там есть удобная OHLC функция.


    1. Anrikigai Автор
      26.10.2021 19:45
      +1

      Не то, что не пробовал, а даже и не слыхивал о таком.

      Пока не понял, за счет чего и сжимает хорошо, и потребляет CPU меньше, но выглядит очень интересно.

      Если действительно легко будет использовать стандартные cadviser и прочее с ней, вполне возможно перейду, очень уж заманчиво о ней отзываются. Но пока сложилось впечатление, что это скорее "хранилка". И надо таки все собирать через Prometheus, попросив его пересылать на VictoriaMetrics.

      Add the following lines to Prometheus config file (it is usually located at /etc/prometheus/prometheus.yml) in order to send data to VictoriaMetrics:

      remote_write:
        - url: http://<victoriametrics-addr>:8428/api/v1/write

      Надеюсь, я ошибаюсь, и можно напрямую. Иначе это перебор для моих задач.


      1. valyala
        26.10.2021 20:01
        +1

        VictoriaMetrics может сама собирать метрики, как Prometheus - см. https://docs.victoriametrics.com/#how-to-scrape-prometheus-exporters-such-as-node-exporter


        1. Anrikigai Автор
          26.10.2021 20:05

          Супер! Спасибо!


    1. KonishchevDmitry
      29.10.2021 20:05

      VictoriaMetrics - действительно интересная альтернатива Prometheus (которая в отличие от него ставит своей задачей хранить и отдавать исторические данные). Я у себя ее поставил за Prometheus именно как хранилище исторических данных. Правда, пользоваться получается не везде, т. к. конкретно для инвестиций графики получаются вот такими:

      хотя ровно те же данные из Prometheus отрисовываются без проблем:

      Не берусь тут судить, чья это проблема - честно говоря, не было времени раскапывать это место, но по факту получается, что в моем случае некоторые данные Grafana с VictoriaMetrics отрисовывает значительно хуже, чем с Prometheus.


      1. Anrikigai Автор
        29.10.2021 20:17

        Сначала подумал: "Ее дело хранить данные и потом отдать в Графану. Отрисовывает та же Графана. Не может же искаженные данные храниться?"

        А потом понял, что она не просто данные отдает, а отвечает на запросы, написанные на PromQL.Видимо не совсем корректно их обрабатывает при использовании каких-то хитрых формул. Или Prometheus прощает какие-то огрехи в синтаксисе...


      1. KonishchevDmitry
        30.10.2021 18:13
        +1

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