Учет инвестиций с помощью Google Spreadsheet. Базовые настройки и ввод данных
В это части постараюсь описать, чего уже можно добиться с помощью документа в Google Spreadsheet, разработанного Вячеславом (пример — https://docs.google.com/spreadsheets/d/1IUxJfnRjzpqkNpuKAU83eTqxCOLyWVZmkVTI9galxZ0/edit#gid=0), а также пройтись по листам, на которых вносятся необходимые для учета данные.
А в следующей части уже посмотрим, что получаем на выходе. И станет ясно, куда можно расти и что улучшать.
Итак, Гугл таблица позволяет получать подробную информацию по портфелю:
- Сумма ваших инвестиций
- Сумма накопленных дивидендов
- Текущий остаток на депозите
- Текущую стоимость портфеля
- Номинальный доход и доходность (без инфляции)
- Реальный доход и доходность (с учетом инфляции)
- Структура портфеля по секторам (реальная и желаемая)
- По каждой акции в портфеле показывается количество лотов в наличии, дивиденды, средняя цена покупки, текущая стоимость, прибыль и многое другое. Производится расчет как текущей доли в портфеле, так и желаемой доли, а также дается рекомендация, сколько лотов нужно купить/продать, чтобы получить желаемую долю. К сожалению, данные по рыночным ценам не подтягивались автоматически, поэтому нужно было вручную их вносить, чтобы видеть актуальную версию картины. Но при относительно пассивном инвестировании это не так и важно. Если раз в месяц (или еще реже) осуществлять покупки, то можно и вручную обновить котировки.
Как видите, в базовой версии уже заложен огромный набор возможностей по учету своего портфеля. Но чтобы всего это добиться, нужно разобраться с логикой ввода данных и теми настройками, что есть. Пройдусь по листам, на которых нужно вносить информацию об инвестициях, расходах, сделках и дивидендах.
Инвестиции. Учет проинвестированных денег. Для точного подсчета нужно регулярно записывать все суммы, попавшие на брокерский счет. Вносится как сама сумма, так и дата пополнения. Это нужно для более точного подсчета денег, участвующих в «обороте», а также дисконта на инфляцию.
Константы. Различные константы, учитываемы для корректного расчета затраченных на сделки сумм, а также влияющие на подсчет реальной доходности портфеля (с учетом инфляции).
Какую величину инфляции стоит использовать для подсчета реальной доходности портфеля? Тут все не так однозначно. Самое простое — использовать официальные данные Росстата. Но им не всегда есть доверие, поэтому можно ставить любую цифру, соответствующую вашим «ощущениям». Еще более сложный вариант — рассчитывать свою личную годовую инфляцию, но для этого нужно вести учет своих расходов. Про это как-нибудь поговорим отдельно.
Сделки. Следующая вкладка (достаточно объемная) — Сделки. Большинство колонок рассчитывается автоматически, но некоторые из них надо заполнить вручную данными по своим состоявшимся сделкам.
Этот лист я немного оптимизировал, чтобы упростить ввод сделок, в результате на вставку сделок я трачу минимум времени (скопировать нужное количество строк из Excel, потянуть расчетные формулы, добавить комментарий при необходимости). Про все оптимизации и улучшения — будет отдельно.
Расходы. Расходы по брокерскому счету. Сюда вносятся все суммы, списанные за обслуживание с брокерского счета (депозитарное обслуживание и т.п.). Этот лист использую достаточно редко, так как за обслуживание не плачу, а брокерские комиссии учитываются в Сделках. Правда, пару раз попадал на дополнительные комиссии (РЕПО, доступ к Web-QUIK), но это единичные случаи.
Дивиденды. Сюда записываются все поступающие дивиденды по акциям, а также купоны по облигациям.
Опять же, этот лист я немного модернизировал, добавив подсчет заплаченных сумм НДФЛ и флаг того, нужно ли самому потом платить налог. А то бывают эмитенты, зарегистрированные за рубежом (Ros Agro, Rusal PLC), которые присылают дивиденды в долларах, а брокер с таких поступлений налог не берет. Поэтому очень удобно заранее проставить нужный флаг, чтобы в следующем году мучительно не вспоминать, что нужно включить в налоговую декларацию.
По вводу данных вроде бы все. В следующий раз посмотрим листы Портфель, Структура и Баланс, отвечающие за наглядной отображение различной информации по портфелю.
Кстати, отличная идея! А то сразу после покупки облигации немного грустно смотреть на минус из-за уплаченного НКД, но еще не полученного купона.
Скрипт, собирающий данные — достаточно простой. Вот он (надеюсь, не сильно порежется при отправке):
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);
}