Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных
В это части постараюсь описать, чего уже можно добиться с помощью документа в Google Spreadsheet, разработанного Вячеславом (пример — https://docs.google.com/spreadsheets/d/1IUxJfnRjzpqkNpuKAU83eTqxCOLyWVZmkVTI9galxZ0/edit#gid=0), а также пройтись по листам, на которых вносятся необходимые для учета данные.
А в следующей части уже посмотрим, что получаем на выходе. И станет ясно, куда можно расти и что улучшать.
Итак, Гугл таблица позволяет получать подробную информацию по портфелю:
- Сумма ваших инвестиций
- Сумма накопленных дивидендов
- Текущий остаток на депозите
- Текущую стоимость портфеля
- Номинальный доход и доходность (без инфляции)
- Реальный доход и доходность (с учетом инфляции)
- Структура портфеля по секторам (реальная и желаемая)
- По каждой акции в портфеле показывается количество лотов в наличии, дивиденды, средняя цена покупки, текущая стоимость, прибыль и многое другое. Производится расчет как текущей доли в портфеле, так и желаемой доли, а также дается рекомендация, сколько лотов нужно купить/продать, чтобы получить желаемую долю. К сожалению, данные по рыночным ценам не подтягивались автоматически, поэтому нужно было вручную их вносить, чтобы видеть актуальную версию картины. Но при относительно пассивном инвестировании это не так и важно. Если раз в месяц (или еще реже) осуществлять покупки, то можно и вручную обновить котировки.
Как видите, в базовой версии уже заложен огромный набор возможностей по учету своего портфеля. Но чтобы всего это добиться, нужно разобраться с логикой ввода данных и теми настройками, что есть. Пройдусь по листам, на которых нужно вносить информацию об инвестициях, расходах, сделках и дивидендах.
Инвестиции. Учет проинвестированных денег. Для точного подсчета нужно регулярно записывать все суммы, попавшие на брокерский счет. Вносится как сама сумма, так и дата пополнения. Это нужно для более точного подсчета денег, участвующих в «обороте», а также дисконта на инфляцию.
Константы. Различные константы, учитываемы для корректного расчета затраченных на сделки сумм, а также влияющие на подсчет реальной доходности портфеля (с учетом инфляции).
Какую величину инфляции стоит использовать для подсчета реальной доходности портфеля? Тут все не так однозначно. Самое простое — использовать официальные данные Росстата. Но им не всегда есть доверие, поэтому можно ставить любую цифру, соответствующую вашим «ощущениям». Еще более сложный вариант — рассчитывать свою личную годовую инфляцию, но для этого нужно вести учет своих расходов. Про это как-нибудь поговорим отдельно.
Сделки. Следующая вкладка (достаточно объемная) — Сделки. Большинство колонок рассчитывается автоматически, но некоторые из них надо заполнить вручную данными по своим состоявшимся сделкам.
Этот лист я немного оптимизировал, чтобы упростить ввод сделок, в результате на вставку сделок я трачу минимум времени (скопировать нужное количество строк из Excel, потянуть расчетные формулы, добавить комментарий при необходимости). Про все оптимизации и улучшения — будет отдельно.
Расходы. Расходы по брокерскому счету. Сюда вносятся все суммы, списанные за обслуживание с брокерского счета (депозитарное обслуживание и т.п.). Этот лист использую достаточно редко, так как за обслуживание не плачу, а брокерские комиссии учитываются в Сделках. Правда, пару раз попадал на дополнительные комиссии (РЕПО, доступ к Web-QUIK), но это единичные случаи.
Дивиденды. Сюда записываются все поступающие дивиденды по акциям, а также купоны по облигациям.
Опять же, этот лист я немного модернизировал, добавив подсчет заплаченных сумм НДФЛ и флаг того, нужно ли самому потом платить налог. А то бывают эмитенты, зарегистрированные за рубежом (Ros Agro, Rusal PLC), которые присылают дивиденды в долларах, а брокер с таких поступлений налог не берет. Поэтому очень удобно заранее проставить нужный флаг, чтобы в следующем году мучительно не вспоминать, что нужно включить в налоговую декларацию.
По вводу данных вроде бы все. В следующий раз посмотрим листы Портфель, Структура и Баланс, отвечающие за наглядной отображение различной информации по портфелю.
Если про будущие, то можно здесь попробовать: https://smart-lab.ru/dividends
Интересно, есть у Тимофея вывод в каком-нибудь удобном формате?
Я оптимизировал не графики, а подсчет стоимости портфеля на конец дня, а также всех покупок. В конце дня по триггеру запускается скрипт, который собирает данные. В результате получается примерно такая табличка, на основе которой я уже строил графики:
А НКД облигаций вы как используете? Для определения, какие облигации покупать?
Я по облигациям больше смотрю дату следующей выплаты, экспирацию и купон.
Выводы я просто вношу со знаком минус на лист «Инвестиции», также, как и пополнения. Вроде нормально работает, уменьшает значение в «Текущий остаток на депо» на листе «Баланс».
Кстати, отличная идея! А то сразу после покупки облигации немного грустно смотреть на минус из-за уплаченного НКД, но еще не полученного купона.
Скрипт, собирающий данные — достаточно простой. Вот он (надеюсь, не сильно порежется при отправке):
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);
}
Я в своих подсчетах добавил учет дат реальных пополнений и дивидендов с помощью функции XIRR (или ЧИСТВНДОХ в русском Excel), но очень большой разницы со значением в «Номинальный доход, % годовых» не заметил. Например, сейчас у меня «Номинальный доход, % годовых» равен 8,6%, а XIRR дает 8,4% (думаю, разница появляется из-за учета дивидендов в XIRR).
Но зато XIRR помогает точнее оценить доходность в % годовых конкретной акции. Купил на 10000 рублей, подержал два дня, продал на 100 рублей дороже. Вот и получил доходность в 100% годовых (образно говоря). Приятно.
Пример с XIRR по конкретным акциям вот: https://docs.google.com/spreadsheets/d/1o1I3fA0RczgF9Ar7q9bnbGniYUeAVNu6tdN3I2EJdHg/edit#gid=541213349
Главное, собрать все имеющиеся данные на одном листе, чтобы удобно было считать.
Подскажите пожалуйста, возможно ли в разделе «константы» учитывать комиссии разных брокеров для одного портфеля?
Так уж сложилось что счета открыты у разных, но являются частью единого портфеля.
Спасибо!
K_Sloths, Да, это возможно, как раз недавно столкнулся с этой задачей, когда переоткрывал ИИС у другого брокера. Я просто на лист «Константы» забил для каждого брокера комиссии
А, для каждой сделки ввел колонку «Брокерский счет», а в колонке «Комиссия Брокера» прописал формулу:
=$I894*VLOOKUP($B894,'Константы'!$A$15:$B$18, 2, FALSE)
И получается, что для каждой сделки считается своя правильная комиссия
Вот здесь я описывал, как автоматически получать текущие котировки: https://smart-lab.ru/blog/493737.php