Блог им. a1pha

📊 Полноценный учёт портфеля на базе Google таблиц

    • 08 сентября 2020, 10:32
    • |
    • a1pha
  • Еще
📊 Полноценный учёт портфеля на базе Google таблиц

Добрый день, коллеги инвесторы!

Меня зовут Артур, сегодня речь пойдёт об очередном моём проекте, который позволяет реализовать полноценный учет вашего инвестиционного портфеля в Google таблицах. Я уже публиковал его анонс, в котором подробно рассказывал о возможностях Инвест Учёта, поэтому лишний раз повторяться не буду; кому интересно можете посмотреть здесь. Ну а если вкратце — то он он умеет почти всё, что необходимо инвестору.

Полного обзора в виде статьи не будет, потому что у меня не хватит сил это написать, а у вас — прочитать. Поэтому я подготовил видеоинструкцию.

 

Перед началом обзора, хочу выразить благодарность людям, без которых этого проекта либо не было бы совсем, либо работа была бы значительно сложнее:

  1. Вячеслав, на базе чьей таблицы в общем-то и создан Инвет Учёт;
  2. Сергей Ветко, в блоге которого я прочитал о его опыте редактирования шаблона Вячеслава, и так же решил начать изменять его под себя;
  3. Канал STM Solution, где есть очень много советов по работе с таблицами (если вы хотите стать профи в Google таблицах, то этот канал просто must have);
  4. Александр с канала ФинИнди за его не менее полезные проекты в таблицах, и в целом за хороший, интересный YouTube канал;
  5. Михаил Шардин, за систематизацию справочника по API МосБиржи;
  6. И наконец Тимофей Мартынов, за smart-lab, который является и источником некоторых данных для Инвест Учёта, и просто хорошим форумом для трейдеров и инвесторов (кто бы там что ни говорил).



 

Кому не подойдёт Инвест Учёт?
  

📊 Полноценный учёт портфеля на базе Google таблиц 
  • На одном из сайтов, когда я опубликовал анонс этого проекта, один человек написал в комментариях: мол, всё это сложно, кому это нужно, всё и так ведёт брокер, а мне лень лишний раз вносить куда-то ещё сделки. Если вы придерживаетесь такой же позиции, то можете дальше не читать смотреть — нам с вами не по пути. Что касается меня, я не представляю себе как можно инвестировать без знания ответа на простейший вопрос — какова динамика моих инвестиций. Это как использовать автомобиль и не заниматься его техобслуживанием: в целом возможно какое-то время, но в один прекрасный день может оказаться, что сегодня вы никуда не едете. Так что, «быть или не быть» — решать только вам, я никого ни к чему не призываю, если всё это не для вас и вам нечего сказать по существу, пожалуйста, просто проходите мимо.

  • Фанатам рубля. Хоть я и старался по максимуму реализовать параллельный учет и в рублях, и в долларах — на 100% это сделать сложно. Например, в сервисе Google Finance отсутствуют индексы российского рынка, так что не получится использовать RTS или IMOEX как бенчмарк. Учёт по секторам тоже реализован в валютной переоценке. Собственно, почему доллар, а не рубль. Всё просто: рубль — «мягкая» валюта. Рубль сегодня, не то же самое, что рубль 10 лет назад, поэтому он просто не подходит для этой роли. Конечно, доллар так же имеет свою инфляцию и подвержен обесценению, но всё же, говоря про доллар, мы максимум может уточнить о каком веке идёт речь, в отличие от рубля, когда мы говорим: «рубль при курсе 24/30/60/70» и всё это укладывается в нынешний век.

  • Ещё желательно понимать как устроены электронные таблицы и иметь опыт работы с ними, однако, если такого опыта нет, то в конце видео есть один из вариантов решения этого вопроса.



Нюансы

  • Важно! Существует ограничение от Google на формулы (IMPORTHTML/XML), импортирующие данные из интернета. Обычно пишут о лимите в 50 таких формул на один файл, но это не совсем верно, так как в Инвест Учёте их гораздо больше и при этом всё работает. По моим наблюдениям, лимит стоит не на количество формул, а на их применение, т.е. факт загрузки. И выглядит это следующим образом: если вы превышаете лимит, то где-то через час он немного расширяется и позволяет еще немного поработать. Если и этот дополнительный лимит будет превышен, то загрузка прекратится (по моим наблюдениям) примерно до следующего дня. Поэтому всё, что не требуется для корректной работы Инвест Учёта вынесено как опция через чекбокс (т.е. можно вручную включить или выключить загрузку), а там, где можно избежать использования таких формул, либо возможно использовать альтернативу, не так сильно расходующую лимит — всё оптимизировано именно таким образом. Там где загрузка является опциональной, я рекомендую её отключать перед закрытием файла.Так что не получится включить загрузку всего и везде, а вот использовать эту функцию по мере необходимости вполне возможно. В обзоре я буду делать акцент на функциях, расходующих лимит.Иногда бывает, что некоторые сервера, с которых загружаются данные, оказываются недоступны и вы будете видеть ошибки или пустые ячейки. В этом нет ничего страшного, надо просто подождать пока связь не восстановится, а иногда достаточно просто перезагрузить Инвест Учет.

  • Следующее, что необходимо сделать — проверить Файл — Настройки таблицы — убедиться что в поле Региональные настройки выбрана Россия. Без этого параметра скорее всего вообще ничего работать не будет, так что убедитесь дважды, что всё верно.

  • И конечно, для работы вам потребуется аккаунт Google, без него нельзя. Не думаю что с этим у кого-то возникнут проблемы.

 



Преимущества Инвест Учёта перед другими сервисами

 

  1. Подходит для учёта любых классов активов, имеющих цену и/или денежные потоки.
  2. Вы можете быть уверены, что данные находятся только под вашим контролем и никакие третьи лица не имеют к ним доступ (вряд ли Google интересно содержимое ваших файлов).
  3. Проект абсолютно бесплатен: как платформа, на которой он построен, так и сама разработка. В отличие от встречающегося часто сценария, когда бесплатный на старте проект спустя время становится платным, то здесь, какое бы сильное желание или обстоятельства у меня потом не возникли, единожды скопированная на ваш Google-диск таблица никогда не станет для вас платной. Это в принципе невозможно. По этой же причине можно не переживать, что проект закроется и накопленные за много лет данные просто исчезнут.
  4. Прозрачность работы: все формулы у вас на виду, вы легко в любой момент можете узнать как всё работает, как рассчитывается тот или иной параметр. А при наличии навыков, адаптировать Инвест Учёт под себя.
  5. И наконец - всё находится в облаке, а это значит, что вы имеете доступ к файлу с любого устройства, а также не сможете случайно его удалить.
Если вы прочитали данный текст, то видеоинструкцию можете начинать смотреть с 6:45.



Ссылки

★36 | ₽ 100
57 комментариев
Такой файл должен каждый создать себе сам. С нуля.
Именно под свои потребности.
Файл будет выстрадан и выращен вместе со стратегией.

Чужое использовать не айс, но идет вытащить можно, за это спасибо.
avatar

Turbo Pascal, файл такого масштаба сами себе создать смогут единицы.

Я когда искал где вести учёт, еще до того как начал создавать свой проект, встречал много таких «самоделок». И бог с ним что они выглядят стрёмно, так самая большая проблема — это узконаправленность и ограниченность таких таблиц. Про то что сам учёт настроен некорректно, например не учитываются комиссии, инфляция, всё считается в рублях и т.п. я вообще молчу

 

Под потребности всегда можно адаптировать, в этом одно из основных преимуществ электронных таблиц. Собственно сам Инвест Учёт — это адаптация таблицы Вячеслава

avatar
a1pha, 

файл такого масштаба сами себе создать смогут единицы.

Файл такого масштаба, полагаю, и нужен единицам.
У меня, например, файл заметно попроще, но мне его хватает.
avatar

Turbo Pascal, ваша правда

 

Есть и такие, кому хватает брокера и вообще ничего не нужно))



avatar
a1pha, не, просто брокерского не хватает, т.к. он куцый.
Просто раз в сутки, получая ежедневный отчет, переписываю из него данные в свои таблички, и пересчитываю всякие свои дополнительные расчеты и статистику.
avatar

Turbo Pascal, так вы активной торговлей занимаетесь… понятно) трейдерам конечно нужен совершенно другой учёт, и вы правы — каждому свой.

 

Для долгосрочных инвестиций учёт более универсальный: курсовая прибыль, сделки, дивиденды, налоги, комиссии, инфляция, да правильные формулы в правильных местах — в общем-то это основные составляющие

 

Раз в полгода внёс сделки в таблицу, посмотрел доходность — вот и весь учёт)

avatar
выглядит очеь интересно. 
avatar
Гугл таблица удобна когда есть несколько брокеров и другие активы типа недвижимости
avatar
Огромный объем работы! Даже не представляю, сколько на все эти доработки времени ушло...
Понравилась идея с листом «Погашенные». Да и вообще есть много, что можно позаимствовать.
Не жалко такое добро бесплатно отдавать?
avatar

Сергей Ветко, спасибо)) начал работать над ней в конце осени. Почти год...

 

Бесплатно не жалко, а кто захочет — отблагодарит)

Я помню когда я искал где вести учёт, и не мог найти ничего нормально. Я был бы безумно рад тогда найти что-то подобное Инвест Учёту даже за деньги, не то что бесплатно)) Так что пусть люди пользуются, я рад принести пользу нашему коммьюнити

avatar
a1pha, главное, чтобы те, кто пользуется, действительно ценили вложенный труд. А то бывает, что народ не слишком ценит то, что досталось бесплатно. И считает, что автор должен за него поискать, как через API Мосбиржи доставать какие-то данные…
avatar
Сергей Ветко, а как ваша таблица поживает? Еще пользуетесь ей?)
avatar
a1pha, да, пользуюсь. Но до этого космического корабля моей далеко. Но нужные мне вещи делает, дивиденды считает, котировки (скриптом) забирает.

Ну и есть некоторые сложности с тем, как учитывать долларовые облигации, так как у меня основной учет в рублях. Такое ощущение, что курсовые разницы вносят некоторую погрешность.
avatar
Сергей Ветко, курсовые разницы вносят очень большую погрешность!) У меня 70% активов в валюте и из-за колебаний деревянного доходность в рублях почти в 2 раза больше, чем в долларах :D
avatar
Подскажи, я так и не понял, где сам сервис то?:)
Тимофей Мартынов, Инвест Учёт что ли?)
avatar
a1pha, я зашел туда
там какие то ссылки
а ссылку на сам сервис можно?
a1pha, хоспаде, зачем спрятал под слово СКАЧАТЬ

не мог чтоле сразу дать ссылку 

docs.google.com/spreadsheets/d/1Nb9VDWJ6PM88YYzbNLIXHV_n0JAddziSHEE3_eqdb0I/template/preview
Тимофей Мартынов, обычно СКАЧАТЬ привлекает больше внимания, чем сслыка))
avatar
a1pha, ну да, я-то искал гугл доку, поэтому и не додумался что она прячется за словом скачать)
 Интересно, а почему гугл дока так тормозит
Тимофей Мартынов, у меня всё норм  и пока никто не жаловался…
avatar
 в таблице конечно ты неслабо заморочился

Тимофей Мартынов, вынудило отсутствие нормальных сервисов 

Как говорится — хочешь сделать хорошо, сделай это сам

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

Lebowski, специально для вас писал

 

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


P.S. А еще я почти уверен, что у вас есть ошибки в учёте.

 

avatar
a1pha, какие ошибки могут быть? Приход 1000 сейчас 1100, прибыль 10%, из них 4% дивиденды, с учётом инфляции в общем 6%, где в этом простейшем уровнении может быть ошибка о великий гуру таблиц?
avatar

Lebowski, никаких 

Живите и дальше в вашей маленькой уютной табличке, у меня нет желания тратить время на борьбу с вашей самоуверенностью.

avatar
a1pha, ладно ты мастер убеждать, буду пользоваться этой таблицей
avatar
Lebowski, спасибо, что сделали одолжение 
avatar
Жаль, что фундаментальный анализ акций ММВБ на Смартлабе не пригоден для экспорта в Таблицы Гугла

Сберегатель (Сэр Лонг), если вы про саму табличку, то можно через importhtml

Только зачем?

avatar
a1pha, 
если вы про саму табличку, то можно через importhtml
а вы пробовали?

Сберегатель (Сэр Лонг), а чего там пробовать?)

https://docs.google.com/spreadsheets/d/1XWeLWrXggi8VkcpF03fZCoY8k44p9n2un99AiydErag

avatar
a1pha, у меня по этой ссылке открывается пустая таблица со ссылкой на страницу смартлаба

Сберегатель (Сэр Лонг), бывает иногда затуп, надо просто подождать. Хотя вот эту ссылку https://smart-lab.ru/q/shares_fundamental2/ сейчас подгружает нормально

avatar
a1pha, проблема даже не в этом
а проблема в том, что на смартлабе эти таблицы содержат ячейки разных форматов
например, одна ячейка содежит число в текстовом формате, а другая ячейка — в числовом
и провести с ними арифметические операции невозможно

особенно это хорошо заметно на страницах с ФА отдельных компаний, например
smart-lab.ru/q/ENRU/f/y/

поэтому весь ФА со смартлаба невзможно использовать в гугл-таблицах

Сберегатель (Сэр Лонг), 

1) Можно включить региональные настройки: США, и тогда таблица будет понимать точки как знак дроби, вместо запятых

или

2) Юзайте формулу ПОДСТАВИТЬ для замены точек запятыми и удаления пробелов внутри чисел

3) Если где-то таблица не понимает число и орёт, что это текст, юзайте формулу ЗНАЧЕН

 

P.S. во всех моих таблицах данные импортируются как правило именно в таком виде как вы написали. Так что для меня это уже не проблема.

 

А вот что было реальной проблемой, так это в Фундаментальном Скринере конвертировать данные отсюда https://stockanalysis.com/stocks/isrg/financials/ в то, что поймёт таблица. То есть написать универсальную формулу, которая числа в млн USD из западного стандарта записи сумеет конвертировать следующим образом в наш стандарт:

12.263 = 12263
12.26 = 12260
12.6 = 12600

1 = 1000000

9 412,263 = 9412263000
9 412,26 = 9412260000
9 412,6 = 1942600000

 

И выглядит она вот так:

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ(".";EY6);0)>0; ЗНАЧЕН(ПОДСТАВИТЬ(EY6;".";",")); ЗНАЧЕН(ЕСЛИОШИБКА(СЦЕПИТЬ(ОТБР(EY6); ЕСЛИ(ДЛСТР(ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)))=3; ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)); ЕСЛИ(ДЛСТР(ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)))=2; ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6))&«0»; ЕСЛИ(ДЛСТР(ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)))=1; ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6))&«00»;""))));EY6))); ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(EY6;",";"");".";","))))

 

avatar
a1pha, 

А вот что было реальной проблемой, так это в Фундаментальном Скринере конвертировать данные отсюда https://stockanalysis.com/stocks/isrg/financials/ в то, что поймёт таблица. То есть написать универсальную формулу, которая числа в млн USD из западного стандарта записи сумеет конвертировать следующим образом в наш стандарт:

12.263 = 12263
12.26 = 12260
12.6 = 12600

1 = 1000000

9 412,263 = 9412263000
9 412,26 = 9412260000
9 412,6 = 1942600000

 

И выглядит она вот так:

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ(".";EY6);0)>0; ЗНАЧЕН(ПОДСТАВИТЬ(EY6;".";",")); ЗНАЧЕН(ЕСЛИОШИБКА(СЦЕПИТЬ(ОТБР(EY6); ЕСЛИ(ДЛСТР(ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)))=3; ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)); ЕСЛИ(ДЛСТР(ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)))=2; ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6))&«0»; ЕСЛИ(ДЛСТР(ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6)))=1; ПРАВСИМВ(EY6; ДЛСТР(EY6)-НАЙТИ(",";EY6))&«00»;""))));EY6))); ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(EY6;",";"");".";","))))

мне проще застрелиться )))
a1pha, 

1) Можно включить региональные настройки: США, и тогда таблица будет понимать точки как знак дроби, вместо запятых

или

2) Юзайте формулу ПОДСТАВИТЬ для замены точек запятыми и удаления пробелов внутри чисел

3) Если где-то таблица не понимает число и орёт, что это текст, юзайте формулу ЗНАЧЕН

огромное спасибо за подсказку!
a1pha, 
Только зачем?
я хочу иметь Таблицы Гугл с ФА акций РФ
Таблица конечно шикарная, для инвесторов совершающих редкие сделки такой функционал врятли необходим. Таблица чуть притормаживает, то-ли ноут слабый, то-ли сеть тормозит. А ещё хотелось бы сравнение результатов с бенчмарком индексамосбиржи брутто. Однако, с помощью этой таблицы обнаружил ошибку в своей таблице учёта)) Но все же спасибо автору за труд и отличный результат. 

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

 

То что притормаживает, не знаю… Она загружается какое-то время, у меня где-то секунд 10-15, а потом работает как любой файл или документ на компе.

 

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

Если вы найдёте способ загружать по запросу в гугл таблицу исторические данные нужного индекса, то можно сделать отдельную таблицу под российские индексы, от которой будет питаться Инвест Учёт.

avatar

Спасибо )

огромный труд вложен. чтобы разобраться как полноценно пользоваться не одна неделя уйдет )

avatar

SpectatorLife, спасибо за коммент, я рад что есть люди, которые понимают чего стоило создать такой проект)

А то понабежало тут «специалистов», которые знают как всё сделать в 10 раз проще)))

 

Добавляйтесь в группу в телеге, если будут вопросы https://t.me/joinchat/HGgCIVP7nEYdqi9qqYN63A

avatar
Учёт портфеля в 3 клика:
1. Создать портфель
2. Залить отчёт брокера
3. Смотреть результат
avatar

Валерий, видел ваш сервис, интересный проект. Наверное, если б нашёл его до создания Инвест Учёта, возможно стал бы пользоваться именно им.

Но сейчас я бегло пробежался и вижу в нём некоторые недостатки и ошибки

avatar
Кто может подсказать решение проблемы
Для ОФЗ работает формула, а для корпоративных не работает (меняю TQOB на TQCB)
IMPORTXML(«iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml»,"//row[@SECID='"&D23&"']/@MARKETPRICE")

Для корпоративных работает эта формула, но она не работат для ОФЗ (меняю TQCB на TQOB и подставляю соотвесвующий код бумаги)
IMPORTXML(«iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities/RU000A0ZYL22.xml»,"//row[@SECID='RU000A0ZYL22']/@MARKETPRICE")
Чем они вообще эти формулы отличаются  не могу понять?
    
avatar

[@SECID='RU000A0ZYL22']


Не уверен, что в ISIN нужны кавычки ' ' когда он указан напрямую в формуле.

Я лично использую XPath вот отсюда

avatar
ipt-07, ну там же есть лист с формулами для импорта доходности
avatar
спасибо!
avatar
Можете еще подсказать что за беда у гуглтаблиц с датами постоянная: копирую полностью строчку, ставлю новую дату через форулу (сегодня) и копированием формата ячейки с предыдущей. Но формула не работает

 
avatar
ipt-07, почему не работает? У вас выделена ячейка с формулой =СЕГОДНЯ(), в ячейке стоит 16 ноября — сегодня 16 ноября. В чем проблема?
avatar
Не работает формула доходности которую скопировал с вашего примера на дату (сегодня), специально оставил сверху строчку из вашего файла и сделал ниже такую же формулу но с другой датой
avatar
ipt-07, не знаю. Возможно потому что торговая сессия еще идёт. Попробуйте использовать СЕГОДНЯ()-1
avatar

теги блога a1pha

....все тэги



UPDONW