Как создать дневник в excel

Обновлено: 07.07.2024

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

Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.

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

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

Как работает таблица

Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования VBA, но сейчас я перенес его в гугл-таблицу без использования скриптов.

Чтобы таблица была не просто очередным шаблоном, я дал ей собственное имя — SilverFir: Investment Report. Название говорит о том, что это инвестиционный отчет, а silver fir отсылает к разновидности вечнозелёных деревьев.

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

Форматы данных. В настройках таблицы указаны региональные настройки Соединенных Штатов. Это означает, что разделитель целой и дробной части числа — точка, то есть 105.1 — правильная запись, а 105,1 выдаст ошибку. Это сделано, чтобы не загромождать формулы автоматической заменой точки на запятую. Все американские и многие российские сайты выдают цены именно с точкой в качестве разделителя.

Даты указаны в формате «год-месяц-день», то есть «2020-03-11» — 11 марта 2020 года.

Разделитель в формулах при американских региональных настройках — запятая, в отличие от российского формата — точки с запятой. Если вы будете переносить формулы в какие-то свои таблицы, имейте это в виду.

Как победить выгорание

Курс для тех, кто много работает и устает. Цена открыта — назначаете ее сами

Основные параметры, используемые в таблице. Чтобы заполнить таблицу и корректно ею пользоваться, необходимо знать следующие параметры:

Идентификатор — обычно тикер или ISIN, международный идентификационный код ценной бумаги.
Валюта — в соответствии с кодами валют: USD, RUB, EUR, GBP.
Дата покупки — нужна, чтобы считать доходность и определять стоимость в рублях на момент покупки, если актив в валюте, отличной от рубля.
Количество — может измеряться и в штуках, и в квадратных метрах.
Цена покупки — заносится вместе с комиссией, ведь иной раз за сделку приходится платить ощутимые комиссионные.

Знание экселя и регулярных выражений не помешает

Актуальные цены многих активов подтягиваются со сторонних сайтов с помощью функции ImportXML. Для разных активов используются разные сайты. Например, данные по актуальной стоимости квартиры на Арбате я беру с сайта «Домофонд». И тут две проблемы.

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

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

Если вам нужна будет помощь с этим, я постараюсь отвечать в комментариях к статье.

Пошаговое руководство по заполнению

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

Представим, что у вас есть несколько типов активов: два вклада в разных валютах, ИИС, обычный брокерский счет, арендная квартира в Москве и монета «Георгий Победоносец». Разберемся, как получить полную картину по сбережениям.

Начнем с вкладов. Готовые примеры занесены в строки 7 и 8 таблицы.

Пусть это будет вклад 50 000 Р под 5,8% годовых, открытый 22 марта 2020 года сроком на год — до 22 марта 2021 года. Разнесем данные по столбцам таблицы:

Как следить за бюджетом

«Имя» — произвольное.
«Базовая цена» — 50 000 Р .
«Ожидаемая дох-ть, %» — 5,8.
«Дата поступления» — 2021-03-22 . Это дата, когда вы сможете обналичить вклад.
«Размер поступления» — в ячейке уже стоит формула, суммирующая первоначальную сумму вклада и ожидаемый доход.
«Валюта» — RUB.
«Посредник» — в этой ячейке я указываю название брокера, банка или просто тип активов, например «вклады». Это нужно для того, чтобы было удобно просматривать данные в сводных таблицах.
«Дата покупки» — 2020-03-22.

Если ваш вклад не в рублях, то таблица автоматически рассчитает начальные затраты в рублях в столбце «Цена покупки, Р » по курсу на дату открытия вклада.

На скриншоте показаны столбцы, которые надо заполнить вручную. Остальные столбцы скрыты

Индивидуальный инвестиционный счет (ИИС). Допустим, что на ИИС куплено 100 облигаций федерального займа ОФЗ-ПД 26225. Код этой ценной бумаги — SU26225RMFS1. Облигации куплены 3 сентября 2018 года по цене 89% от номинала.

Код ценной бумаги можно посмотреть в отчете брокера или на сайте биржи

Разнесем данные по столбцам таблицы, которые надо заполнить вручную:

На скриншоте показаны столбцы, которые надо заполнить вручную

Брокерский счет. Допустим, на брокерском счете — бумаги двух эмитентов:

70 рублевых облигаций группы компаний «Пионер» серии БО-02, код ценной бумаги — RU000A0JWK66, куплены 28 сентября 2018 года по цене 65% от номинала, то есть за 45 500 Р .
10 акций биржевого инвестиционного фонда FXMM, куплены 20 апреля 2018 года по цене 1426,8 Р за штуку.

Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:

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

На скриншоте показаны столбцы, которые надо заполнить вручную

Квартира в Москве. Основная идея табличного отчета в том, что можно брать цены на совершенно любые классы финансовых активов — лишь бы они публиковались в интернете в свободном доступе. Так и с недвижимостью: есть множество сайтов, которые публикуют статистику цен. Представим, что у вас есть доля в мини-квартире в Москве, в районе Арбата. Квартира сдается в аренду. Правда, вам принадлежит всего 1 м². Купили вы эту долю 13 декабря 2017 года за 460 000 Р . Каждый месяц 25 числа вы получаете арендный доход — 3742 Р .

Разнесем данные по столбцам таблицы:

«Имя» — квартира в Москве, район Арбат.
«Кол-во» — 1 м².
«Базовая цена» — 460 000 Р .
«Дата поступления» — в этой ячейке записана формула, которая всегда будет показывать дату следующего платежа. В примере это 25 число, его можно поменять на любое другое.
«Размер поступления» — ежемесячный платеж, который поступает вам за аренду.
«Ожидаемая доходность» — из расчета, что платят 10 месяцев в году: Ежемесячный платеж × 10 / Текущая стоимость недвижимости × 100.
«Валюта» — RUB.
«Дата покупки» — 2017-12-13.

На скриншоте показаны столбцы, которые надо заполнить вручную

Монета «Георгий Победоносец». Такая же ситуация и с инвестиционными монетами. Например, 11 лет назад, 13 июля 2009 года, вы купили 50 серебряных монет «Георгий Победоносец» номиналом 3 Р . Заплатили 600 Р за каждую.

Разнесем данные по столбцам таблицы:

«Имя» — Георгий Победоносец (3 рубля).
«Кол-во» — 50 штук.
«Базовая цена» — 30 000 Р .
«Посредник» — монеты.
«Дата покупки» — 2009-07-13.

На скриншоте показаны столбцы, которые надо заполнить вручную

Что делать после заполнения данных

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

Теперь можно узнать следующие показатели по каждому из активов:

Прибыль или убыток у вас по позиции на текущий момент в рублях — независимо от валюты вложения: столбец G.
Сколько процентов годовых приносит эта инвестиция, если срок больше года, или просто процент изменения, если срок меньше года: столбец H.
Ожидаемая доходность и средний ежемесячный доход в валюте вложения: столбцы J и K.
Даты грядущих поступлений — или прошлых, если информация по будущим выплатам еще недоступна: столбцы L и M. Если вы покупаете дивидендные акции, ячейки посчитают размер и дату ближайшего дивиденда.
Процентное изменение бумаги за последние несколько лет для акций: столбец I. За сколько именно — зависит от сайта, откуда берутся данные.
Текущий вес бумаги в портфеле: столбец V.

Дополнительно вручную можно указать категории и классы активов, если вы хотите смотреть распределение и по ним. Автоматическое скачивание возможно реализовать только на гугл-скриптах.

Анализ сводных показателей портфеля

Перейдем теперь к сводным показателям всего портфеля. Их можно смотреть на разных вкладках.

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

«Валюты» — полностью автоматическая вкладка, которая содержит отчет по используемым валютам. Как только вы редактируете что-либо на вкладке «Данные», этот мини-отчет сразу меняется.

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

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

«Классы активов» — здесь вы увидите отчет о диверсификации вашего портфеля. Я формализовал описания классов активов из Quicken и описаний нескольких авторов, в том числе Сергея Спирина, Александра Силаева, Павла Комаровского.

«Покупки» — это мини-отчет об истории покупок по времени. Здесь вы сможете узнать, в каком месяце сколько денег потратили.

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

«Капитал график» — визуализирует данные с вкладки «Капитал».

«Идентификаторы» — в графическом виде отображает распределение по бумагам в таблице.

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

Запомнить

Эта таблица — удобный вариант, когда у вас несколько разных брокеров, в том числе зарубежных, и есть доходная недвижимость. Вклады и другие активы тоже легко учесть.
Данные в вашем экземпляре таблицы принадлежат только вам, ни у кого другого не будет к ним доступа. В любой момент только вы сами можете изменить или удалить любую информацию из вашей копии.
За счет автоматических мини-отчетов на основе данных из таблицы вы всегда знаете, что происходит в портфеле. Неважно, насколько он сложен.
Если вы хотите добавить свой актив, изменение цены на который можно отслеживать на общедоступном сайте, то напишите мне в комментарии к статье. Я помогу с написанием формулы или гугл-скрипта.

Тебе не придётся напрягаться с учётом инвестиций, если у тебя их нет!
(картинка, с умным негром)

тьфунатебя, да, обычно так и пишут в комментариях. 🤷‍♂️ Недавно еще было популярный комментарий - инвестировал в пачку гречки.

Спасибо за материал, взял на заметку пару интересных моментов.

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

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

В итоге, когда приходит момент докупить какой-то актив, я за 2 минуты понимаю какой именно это должен быть актив: смотрю сначала на текущую долю в классах (акции/облигации), потом смотрю на долю в валютах, а потом, сузив диапазон до "Ага, понятно, нужно купить акции в $", уже выбираю конкретную акцию в зависимости от отрасли, доли каждой акции в портфеле и текущей экономической ситуации. После этого добавляю строчку о сделке, а все остальное перерасчитывается автоматически.

А, ну еще есть одна страница, на которой красивый график, показывающий прогнозную стоимость моего портфеля на 20 лет вперед, если я буду продолжать дисциплинированно инвестировать дальше. И на этом же графике вторая кривая, показывающая фактический результат. Очень мотивирует.

Много полезного и интересного я черпаю из этого сообщества, и поэтому сейчас, когда у меня появилось чем поделиться, делаю это с удовольствием.

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

Недавно в разговоре с моим тренером узнала, что ей нужна для учета оплат и посещений табличка в excel, но у неё самой что-то не получается это сделать. Мне данная задача показалась интересной (она такой и была), и я приступила к её выполнению.

Сначала подумала над структурой.

- список клиентов (есть постоянные и разовые);

- несколько групп по разным направлениям;

- 4 типа абонементов (групповые, разовые групповые, индивидуальные), с параметрами по срокам действия и количеству занятий;

Что необходимо было реализовать:

- Учет клиентской базы;

- Табель учета посещений + чтобы оплаченный период в нем подсвечивался цветом;

Процесс реализации (излагаю то, что помню).

Тут хотела бы остановиться чуть подробнее.

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

Попробовала реализовать – ПОЛУЧИЛОСЬ. Я запрыгала и затанцевала!

Я была счастлива!

Простое и элегантное решение было найдено!

После этого дело оставалось за малым – оформить книгу под N-е количество групп и сделать условное форматирование.

В итоге получился вот такой табель.

С чем еще предстоит разобраться.

Даты в табель выводятся вообще все подряд. Пришлось их группировать по 10 дней.

По этому адресу лежат 2 дневника.

Здесь версия с исправленными ошибками.

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

Второй упрощенный вариант. Достаточно вбить время экспирации (роли не играет, просто для себя), выбрать пару, направление, процент выплаты и результат. Все данные, кроме процента, выбираются из выпадающего списка, руками вбивать ничего не надо. И поле "Комментарий" для ввода примечаний и особенностей входа.

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

Как фича, на этом же листе отображается своеобразный график доходности :)

Онлайн дневник

Примечание: Google Docs при отображении графиков использует Flash, который может не отображаться на устройствах Apple. Весь остальной функционал работает аналогично Excel версии.

Выпадающие списки в упрощенном дневнике не юзаются, ручками вбиваю. Проверь пож-та.

Алексей, а что за цифры на кривой доходности: 42007, 42008. (предположительно даты)?

Отредактировано Naigovan (11.03.2015 21:35:25)

У меня списки работают, вчера вбил более 150 сделок, все пашет. График да, должен был показывать время, но не захотел почему то. Я переделал немного дневник, выложил в то же место. Теперь там ведется дополнительная статистика по процентам успешных за неделю, месяц. И графики переделал, теперь их 2 - среднее сделок и процент успешности (на отдельных листах).

Если не работают выпадающие списки, сообщи в Skype мне (firsalexskype), посмотрим, в чем дело.

Да, действительно списки не работали. Исправил.

Не знаю, Алексей, может у меня что не так. Скачал по ссылке из первого топа 2 дневника. В упрощенном, вижу добавились новые вкладки, но выпадающее меню (пустое правда) у меня только в столбце выбора экспирации и то до 86 строки вниз, после и там нет. При этом в "сложном" дневнике всё работает как надо. Допили пож. упрощенный, чтоб вып. меню работали в столбцах выбора валютной пары и ставки (т.е. прогноз). Спасибо и респект за твои навыки в Excel!

Отредактировано Naigovan (15.03.2015 16:16:35)

Отредактировано yel (15.03.2015 13:59:40)

Отредактировано yel (15.03.2015 15:59:10)

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

Подскажите, пожалуйста, что означает:

Автоматом этот столбик не заполняется

Если не заполняет, значит удалили ручками формулу случайно.
Скачайте последнюю версию, и либо скопируйте формулу в свой дневник, либо вставьте на лист Data данные в столбцы B-H.

хм,у меня Excel не распознает скаченный файл. ( всегда работал без траблов

Полезная вешь ОГРОМАДНОЕ СПАСИБО. Вам)

Друзья, у меня вот какой вопрос:
Скопировал себе версию дневника в Гугл-Документах(спасибо автору, конечно же), и у меня вопрос по формуле, высчитывающей средний процент успешных сделок. Собственно, сама формула представляет из себя среднее арифметикеское от суммы процентов успешных сделок. У меня в данный момент 10 сделок, из них 4 вчерашние(100% успешных) и 6 сегодня (66,67% успешных), формула показывает, что средний процент у меня 83,33. Насколько верный такой подсчет? Мне кажется, что гораздо важнее общий процент успешных сделок, то есть отношение количества успешных сделок к общему количеству сделок. Или я не прав?

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

Отредактировано mr_vavilon (12.03.2016 02:44:54)

Трейдеры форекс! откликнитесь!) кто нибудь, кто торгует на форекс, скиньте пожалуйста дневник трейдера форекс

Отредактировано swterr (04.04.2016 04:55:47)

У меня лишь один вопрос, можно ли как-то разом очистить всю информацию с дневника, который тут можно скачать? Чтобы свою вписывать, начав с чистого листа.

Upd. Извиняюсь, сам спросил - сам разобрался))

Отредактировано Djeck4 (23.04.2016 11:30:52)

По почте прислали улучшенный вариант дневника сделок.

Я продал квартиру и вложил деньги в фондовый рынок. Чтобы отслеживать изменения по портфелю, попробовал несколько публичных сервисов — платных и бесплатных, но все они показались неудобными, либо с ежемесячной оплатой. Вернулся к старому доброму «Экселю». На разработку таблицы потратил две недели.

Таблица фиксирует все мои активы: акции, облигации, кэш, фонды. Активы записаны в количестве, рублях и долларах по среднему курсу. Распределены по секторам экономики, доля каждого актива и каждого сектора измеряется в рублях и в процентах от общей стоимости портфеля.

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

На основе данных в таблице построены графики: по типам активов (акции роста, акции дивидендов, защитные активы, бонды), разбивка по секторам экономики (я визуал), по валютам всех активов.

Таблица считает сумму дивидендного дохода в год и средний в месяц, в рублях и долларах отдельно + конвертация долларов по курсу в рублях и общий итог ДД в месяц.

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

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

Действую так: Купил акцию — добавил строчку в соответствующий сектор. Указываю эмитента, сектор, количество купленных бумаг, брокера, валюту акции, сумму покупки и планируемый дивиденд на одну акцию. Формулы просчитывают все остальное.

Если акция уже была — просто изменил количество акций в строчке. Автоматически просчитывается чистая ДД (за вычетом налога) на то количество акций, которое я указал. Чистая ДД прибавляется в итоговую сумму заработка за год. Если это доллары — они конвертируются в рубли по курсу 75 рублей за доллар и добавляются к сумму заработка за год.

В комплекте к таблице идут принципы инвестирования, которым я следую. Например, доля одного эмитента не может быть более 5% от портфеля, а доля одного сектора не может быть более 15% от портфеля. Покупки совершаются в три этапа: 30% + 30% + 40% в зависимости от степени падения бумаги. По некоторым эмитентам использую так называемую «демо покупку»: когда бумага на хаях, и я захожу на одну акцию, чисто чтобы за ней следить и так далее. В совокупности таблица и принципы отлично дисциплинируют.

Благодаря таблице я точно знаю, сколько денег заработаю в следующий год. Могу отследить исторические данные по портфелю: сколько ДД принес, например, октябрь этого года, и могу сравнить его с октябрем прошлого года и оценить прибавку в ДД.

Сделки я совершаю один-два раза в месяц, каждую фиксирую в таблице. Занимает это около 10 минут.

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

Читайте также: