Учет инвестиций google таблица

Учет инвестиций в гугл-таблицах | Получаем котировки акций через GoogleFinance

С помощью формул в гугл-таблицах можно автоматически получать текущие котировки практически любых акций. Для этих целей можно использовать функцию GOOGLEFINANCE или API Московской биржи.

Первый способ в большей степени подходит для иностранных компаний. Второй — для отечественных бумаг (акций, биржевых фондов и облигаций).

Начнем с разбора принципов работы GoogleFinance.

Но сперва немного пофилософствуем на тему: а зачем это нужно?

Для чего нужно вести учет инвестиций в таблицах?

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

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

Портфельное управление

Часто бывает (как например у меня), что деньги раскиданы по разным брокерам и счетам. И вы не видите картины в целом. Особенно, если вы придерживаетесь портфельной теории и распределяете деньги по разным активам с заранее выделенными пропорциями. Конечно, можно затратить немного времени и без помощи таблиц свести дебет с кредитом и вычислить сколько в процентном соотношении у меня приходится на акции США (Китай, России), золото, облигации на каждый момент времени момент. Если ли перекос от изначально заданных пропорций, насколько он критичен? Не пора ли сделать ребалансировку? Какие активы требуется купить в первую очередь, а какие в ближайшее время брать не нужно.

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

Вот вам к примеру портфель со следующими пропорциями:

• акции — 80% (распределено на 4 страны равными долями, включают отдельные бумаги и ETF);
• золото — 10% (как в виде ETF, так и акции золотодобытчиков);
• облигации — 10% (отдельны бумаги и фонды).

Акции США — в идеале на них должно приходится 20% от всего веса портфеля. На часть денег куплены биржевые фонды (допустим FXUS и FXIT), на другую часть отдельные бумаги — с десяток другой акций.

Акции на РФ — допустим часть снова в акциях, часть в нескольких фондах.

И так далее и тому подобное по каждому инструменту, каждому классу активов.

В совокупности, всего на 3 класса актива из портфеля может приходится десятки различных инструментов. И это все сводить постоянно воедино, даже торгую только через одного брокера — будет очень трудозатратно.

Несколько отдельных стратегий

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

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

Дивиденды

У вас есть акции или биржевые фонды, которые выплачивают дивиденды?

А вы можете оценить общий размер дивидендов, которые вы получаете по своим бумагам?

Сколько дивов вы получаете в год (или в среднем в месяц)?

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

Как альтернативу, для этих целей можно использовать различные сервисы (платные и бесплатные). Тоже кстати неплохой вариант. Но учет в таблицах позволяет делать это бесплатно, настроить «все под себя» и ни от кого не зависеть.

Добавляем иностранные бумаги через GoogleFinance

Для получения котировок нужных нам акций достаточно знать тикер (биржевой код) бумаги. Сама формула имеет следующий вид:

=GOOGLEFINANCE ( «тикер» , «price»)

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

Пример 1. Тикеры прописываем в отдельной ячейке (колонка С). В формуле указываем адрес ячейки: С2, С3 и так далее.

Получаем котировки акций через GoogleFinance

Пример 2. Тикер прописывается непосредственно в формуле.

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

Ниже пример такого портфеля из 15 иностранных (американских) акций.

Пример учета инвестиций в помощью GoogleFinance

Российские акции и возможные проблемы

Функция GoogleFinance отлично работает с иностранными акциями. С получением котировок, по компаниями входящим в состав различных мировых индексов, проблем не возникает. Если компания более-менее крупная, так или иначе, цену акции можно выдернуть.

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

Сначала о хорошем. Ниже пример того, что GoogleFinance прекрасно распознает котировки российских бумаг.

Автоматическое получение котировок российских акций

Теперь про сложности, с которыми можно столкнуться:

Проблема №1. GoogleFinance не определяет цены на привилегированные акции (от слова совсем). Под раздачу попали префы Сбера, Башнефти, Татнефти, Ростелекома, Сургутнефтегаза.

На картинке ниже видно, что котировки обычных акций — система определила, а по префам возникли ошибки.

Невозможность получения котировок префов через GoogleFinance

Проблема №2. Нельзя вытянуть котировки большинства биржевых фондов обращающихся на Мосбирже. Аналогично ситуации с префами — будет выдавать ошибку.

Проблема №3. Функция может неверно определять или «путать» цены с другими тикерами, акции которых обращаются на других биржах.

Первые две проблемы можно обойти через API Московской биржи (в конце статьи есть ссылка как это сделать).

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

Как пример, акции компании ЮНИПРО. Тикер на Мосбирже — UPRO. Но если попробовать использовать его в таблицах, GoogleFinance будет выдергивать котировки иностранного биржевого фонда ProShares UltraPro S&P500, имеющего аналогичный тикер. Да еще и с долларовыми ценами.

Что делаем? Вместо UPRO прописываем MCX:UPRO и функция обратиться на Московскую биржу и подтянет нужные нам котировки именно с нее.

Второй пример: акции одной и той же компании могут обращаться на разных биржах и номинироваться в разных валютах (Лукойл, Qiwi).

На Московской бирже есть бумаги, выдающие по GoogleFinance некорректные для нас данные.

Накидал несколько примеров подобных разногласий: при вводе одного и того же тикера: с MCX и без.

Неверный вывод котировок по тикеру акций

Еще пара нюансов про GoogleFinance

Котировки поступают в таблицы с задержкой (примерно минут 20). Если вам нужны актуальные данные, то лучше использовать другие ресурсы (приложения или программы брокера).

Помимо котировок, функция ГуглФинанс способна отдавать информацию по мультипликаторам компании (P/E, капитализация, EPS), ценам открытия-закрытия на любой день, изменению котировок за день (неделю, месяц), достигнутых максимумах-минимумах за определенный период и много чего другого. Часть этого может найти практическое применение в системе учета инвестиций.

Оставляю ссылку на шаблон таблицы. Все про что писал выше, там есть.

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

За обновлениями в этой и других статьях теперь можно следить на Telegram-канале: @vsedengy.

Источник

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

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

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

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

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

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

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

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

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

Если вы прочитали данный текст, то видеоинструкцию можете начинать смотреть с 6:45.

Источник