Блог им. Svaroggg

Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных

В это части постараюсь описать, чего уже можно добиться с помощью документа в Google Spreadsheet, разработанного Вячеславом (пример — https://docs.google.com/spreadsheets/d/1IUxJfnRjzpqkNpuKAU83eTqxCOLyWVZmkVTI9galxZ0/edit#gid=0), а также пройтись по листам, на которых вносятся необходимые для учета данные.

А в следующей части уже посмотрим, что получаем на выходе. И станет ясно, куда можно расти и что улучшать.

Итак, Гугл таблица позволяет получать подробную информацию по портфелю:

  1. Сумма ваших инвестиций
  2. Сумма накопленных дивидендов
  3. Текущий остаток на депозите
  4. Текущую стоимость портфеля
  5. Номинальный доход и доходность (без инфляции)
  6. Реальный доход и доходность (с учетом инфляции)
  7. Структура портфеля по секторам (реальная и желаемая)
  8. По каждой акции в портфеле показывается количество лотов в наличии, дивиденды, средняя цена покупки, текущая стоимость, прибыль и многое другое. Производится расчет как текущей доли в портфеле, так и желаемой доли, а также дается рекомендация, сколько лотов нужно купить/продать, чтобы получить желаемую долю. К сожалению, данные по рыночным ценам не подтягивались автоматически, поэтому нужно было вручную их вносить, чтобы видеть актуальную версию картины. Но при относительно пассивном инвестировании это не так и важно. Если раз в месяц (или еще реже) осуществлять покупки, то можно и вручную обновить котировки.

 

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

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

Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных

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

Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных

Какую величину инфляции стоит использовать для подсчета реальной доходности портфеля? Тут все не так однозначно. Самое простое — использовать официальные данные Росстата. Но им не всегда есть доверие, поэтому можно ставить любую цифру, соответствующую вашим «ощущениям». Еще более сложный вариант — рассчитывать свою личную годовую инфляцию, но для этого нужно вести учет своих расходов. Про это как-нибудь поговорим отдельно.

Сделки. Следующая вкладка (достаточно объемная) — Сделки. Большинство колонок рассчитывается автоматически, но некоторые из них надо заполнить вручную данными по своим состоявшимся сделкам.

Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных

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

Расходы. Расходы по брокерскому счету. Сюда вносятся все суммы, списанные за обслуживание с брокерского счета (депозитарное обслуживание и т.п.). Этот лист использую достаточно редко, так как за обслуживание не плачу, а брокерские комиссии учитываются в Сделках. Правда, пару раз попадал на дополнительные комиссии (РЕПО, доступ к Web-QUIK), но это единичные случаи.


Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных

Дивиденды. Сюда записываются все поступающие дивиденды по акциям, а также купоны по облигациям.

Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных

Опять же, этот лист я немного модернизировал, добавив подсчет заплаченных сумм НДФЛ и флаг того, нужно ли самому потом платить налог. А то бывают эмитенты, зарегистрированные за рубежом (Ros Agro, Rusal PLC), которые присылают дивиденды в долларах, а брокер с таких поступлений налог не берет. Поэтому очень удобно заранее проставить нужный флаг, чтобы в следующем году мучительно не вспоминать, что нужно включить в налоговую декларацию.

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

  




★41
33 комментария
А для фортс работает?
avatar
andydiver, Нет, заточено под акции/облигации. Но если есть желание — можно и под фортс, все же работает внутри обычной Гугл Таблицы.
avatar
А почему бы котировки не подтянуть? Это не сложно
avatar
dimakor, Да, про это будет дальше. У меня котировки подтягиваются раз в час. Пока я просто описываю базовую функциональность, которая была изначально. Может, кому-то и этого будет достаточно.
avatar
Сергей Ветко, откуда бы дивиденды подтянуть )
avatar
dimakor, Вы про реально поступившие дивиденды или про предстоящие выплаты? Если первое, то единственный вариант — парсить отчеты брокера. Я же руками вношу, не так и часто это происходит, да и событие приятное, чего бы и не занести.
Если про будущие, то можно здесь попробовать: https://smart-lab.ru/dividends
Интересно, есть у Тимофея вывод в каком-нибудь удобном формате?
avatar
Сергей Ветко, надо будет подумать к следующему дивидендному сезону )
avatar
Знакомая таблица))) Тоже взял её за основу, немного допилил под себя и пользуюсь.
Дмитрий Александрович, приветствую сотоварища:)! А что вы допиливали, если не секрет?
avatar
Сергей Ветко, приветствую! Из основного — добавил автоматическое обновление цены последней сделки через скрипт, который там же, на roundabout был выложен. Googlefinace с префами криво работает. По крайней мере так было год назад, после этого я его больше не трогал. По облигам — НКД через тот же срипт. Вы интересные графики привели в предыдущей статье. Как их автоматизировали? Я пока не придумал ничего лучше, чем в последний день каждого месяца данные ручками вбивать и по ним строить.
Дмитрий Александрович, Я данные по котировкам забираю с сайта Московской Биржи, вроде все цифры правильные.
Я оптимизировал не графики, а подсчет стоимости портфеля на конец дня, а также всех покупок. В конце дня по триггеру запускается скрипт, который собирает данные. В результате получается примерно такая табличка, на основе которой я уже строил графики: 

По облигам — НКД через тот же срипт
А НКД облигаций вы как используете? Для определения, какие облигации покупать?
Я по облигациям больше смотрю дату следующей выплаты, экспирацию и купон.
avatar
Сергей Ветко, в стоимости портфеля учитываю стоимость облигации по цене закрытия и НКД (беру их из вкладки bonds). Для выбора облигаций, конкретно ОФЗ, делал отдельный документ, данные в который подставлялись также из листа bonds. В дальнейшем стал пользоваться для этих целей QUIK'ом и сайтом rusbonds. Было бы интересно увидеть тот скрипт, который у вас данные в конце дня собирает. И еще мучает меня такой вопрос: если вывод средств со счета делать, как его правильно будет туда занести, что думаете?
И еще мучает меня такой вопрос: если вывод средств со счета делать, как его правильно будет туда занести, что думаете?

Выводы я просто вношу со знаком минус на лист «Инвестиции», также, как и пополнения. Вроде нормально работает, уменьшает значение в «Текущий остаток на депо» на листе «Баланс».
avatar
Сергей Ветко, тоже прикидывал, что так должно работать. Меня тут больше интересовала величина годового оборота, правильно ли она будет рассчитываться в этом случае. На бумажке прикидывал, вроде правильно.
 в стоимости портфеля учитываю стоимость облигации по цене закрытия и НКД (беру их из вкладки bonds)

Кстати, отличная идея! А то сразу после покупки облигации немного грустно смотреть на минус из-за уплаченного НКД, но еще не полученного купона.

Скрипт, собирающий данные — достаточно простой. Вот он (надеюсь, не сильно порежется при отправке):

function updateAsssetsValues() {
var sheet = googleSpreadSheet.getSheetByName(«AssetsValues»);

var lastRow = sheet.getLastRow();
Logger.log(lastRow);
var oldDate = '';

if (lastRow > 1) {
//we already have some resutls
var cell = sheet.getRange('A'+lastRow);
oldDate = cell.getValue();
Logger.log('oldDate1 = ' + oldDate);
oldDate = Utilities.formatDate(oldDate, «GMT+12», «yyyy-MM-dd»);
Logger.log(oldDate);
}

//Я забираю из базы стоимость нужного портфеля на конкретную дату.
//Но думаю, что можно и просто брать с листа «Баланс» нужное значение в строке «Текущая стоимость портфеля»
var allAsssetsValues = getAssetsValuesByDate(oldDate, portfolioID);

var allData = [];
var portfolioValues = allAsssetsValues['p'+portfolioID];

//Собираем массив с данными, где один элемент массива содержит дату и сумму
if (portfolioValues != undefined) {
for (var i = 0; i < portfolioValues.length; i++) {
var date = portfolioValues[i]['date'];
var value = portfolioValues[i]['value'];

var len = allData.length;
allData[len] = [];
allData[len].push(date);
allData[len].push(value);
}
}

Logger.log(allData);
//Если собрали какие-то значения, то вставляем их на лист
if (allData.length > 0) {
var dataRange = sheet.getRange(lastRow+1, 1, allData.length, allData[0].length);
dataRange.setValues(allData);
}

return true;
}

// Don's array approach — checks first column only
// With added stopping condition & correct result.
// From answer stackoverflow.com/a/9102463/1677912
function getFirstEmptyRowByColumnArray(sheetName) {
var dataStocks = googleSpreadSheet.getSheetByName(sheetName);

var column = dataStocks.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}

avatar
Сергей Ветко, спасибо, поразбираюсь на досуге. А по учету НКД: из моей практики и брокеры, и управляющие его учитывают. Недавно попадалась статья по учету портфеля от Александра Шадрина (УК Арсагера), хочу глянуть что там они предлагают. Может и вам будет интересно. http://blogs.investfunds.ru/post/74205/
Дмитрий Александрович, за ссылку спасибо, изучу обязательно.
avatar
Дмитрий Александрович, Прочитал статью по ссылке. Как я понимаю, в «нашем» гугл документе примерно так и происходит расчет номинального дохода в %, а реализовано это за счет «суммарного годового оборота». Чем дольше деньги проинвестированы, тем больше они влияют на этот оборот.

Я в своих подсчетах добавил учет дат реальных пополнений и дивидендов с помощью функции XIRR (или ЧИСТВНДОХ в русском Excel), но очень большой разницы со значением в «Номинальный доход, % годовых» не заметил. Например, сейчас у меня «Номинальный доход, % годовых» равен 8,6%, а XIRR дает 8,4% (думаю, разница появляется из-за учета дивидендов в XIRR).

Но зато XIRR помогает точнее оценить доходность в % годовых конкретной акции. Купил на 10000 рублей, подержал два дня, продал на 100 рублей дороже. Вот и получил доходность в 100% годовых (образно говоря). Приятно.
avatar
Сергей Ветко, судя по всему вы там много чего перелопатили! Признаюсь, пока не понял как вы XIRR использовали. Файл в студию))))
Дмитрий Александрович, ну не слишком много, но какое-то время было интересно все сделать удобно и наглядно для себя. Хотя, лучше бы книжки по инвестированию читал бы, наверное:).

Пример с XIRR по конкретным акциям вот: https://docs.google.com/spreadsheets/d/1o1I3fA0RczgF9Ar7q9bnbGniYUeAVNu6tdN3I2EJdHg/edit#gid=541213349
Главное, собрать все имеющиеся данные на одном листе, чтобы удобно было считать.
avatar
Сергей Ветко, спасибо! Обязательно гляну. И про книжки в точку, особенно когда смотришь на результаты некоторых инвесторов, которые по 300% за 3 года делают))))
Кто-то может финальную таблицу дать?
avatar
Денис Охотин, вот здесь самый продвинутый вариант: smart-lab.ru/mobile/topic/644978/comments/
avatar
Сергей Ветко, Вау, вот это монстр, Вы им пользуетесь или на своей решили? Я просто пытался свою таблицу создать, но в итоге очень многое реализовать не получилось, знаний не хватает, а тут такое…
avatar
Денис Охотин, по привычке пользуюсь своим. Но сейчас, если бы только начинал, то использовал бы этот.
avatar
Приветствую!
Подскажите пожалуйста, возможно ли в разделе «константы» учитывать комиссии разных брокеров для одного портфеля?
Так уж сложилось что счета открыты у разных, но являются частью единого портфеля.
Спасибо!
avatar

K_Sloths, Да, это возможно, как раз недавно столкнулся с этой задачей, когда переоткрывал ИИС у другого брокера. Я просто на лист «Константы» забил для каждого брокера комиссии


А, для каждой сделки ввел колонку «Брокерский счет», а в колонке «Комиссия Брокера» прописал формулу: 

=$I894*VLOOKUP($B894,'Константы'!$A$15:$B$18, 2, FALSE)



И получается, что для каждой сделки считается своя правильная комиссия

avatar
Сергей Ветко, большое спасибо!
avatar
А где урок, как автоматизировать авто выгрузку таблицы из квика в гугл таблицы? например раз в сутки? Спасибо!
avatar
Andrey Karpov, нет такого урока.
avatar
Сергей Ветко, Спасибо за быстрый ответ. А где можно подобное найти?
avatar
Andrey Karpov, К сожалению, не знаю. Quik — на компьютере, Гугл таблицы — в облаках. Автоматически связать их не так-то и просто. Компьютер постоянно включенным держать? И не очень понятно, что за таблицу нужно выгружать и зачем.

Вот здесь я описывал, как автоматически получать текущие котировки: https://smart-lab.ru/blog/493737.php
avatar

теги блога Сергей Ветко

....все тэги



UPDONW