Ведение бухгалтерии

Деньги любят счет!

Привет жители планеты! Моё имя Антон и я уже 3 года веду личную бухгалтерию. Выводов пока не очень много, но они есть и достаточно интересные. Первые 2 года я использовал специальное приложение, но в 2020 перешел на голый Excel и формулы никакого программирования и скриптов. Как это было и что из этого вышло под катом с картинками.

Сперва немного вводной

В конце мая 2017 мне подумалось, что неплохо бы начать отслеживать на что и как именно я трачу свои деньги. Доходы тогда у меня были небольшие, но на жизнь хватало. Было решено с 01 июня 2017 записывать свои доходы и расходы в специальное приложение и смотреть на что уходят деньги. После нескольких пробных запусков выбор пал на одного польского разработчика с бесплатными возможностями. Итак, 01.06.2017 мой стартовый баланс составлял 4 031,49 рублей.

До конца 2017 года записи вносились от случая к случаю. Приложение отдавало не информативную статистику, меня это крайне не устраивало. Поэтому с 01.01.2018 бухгалтерия ведется строго и тщательно вплоть до того что каждое воскресенье открывается каждый интернет-банк и сверяются текущие остатки. Это дало неплохие результаты уже через пару месяцев. Привычка вносить всё закрепилась достаточно быстро, и дело пошло продуктивнее.
Приложение, которым я пользовался, позволяло бесплатно вести учет по 2 аккаунтам. Но вскоре у меня их стало слишком много и пару раз мне приходилось платить за приложение. В итоге, в конце января 2020 мне стало жаль денег на новое продление, я скачал все данные и начал вести учет в голом Excel. Через полгода могу сказать что это гораздо интереснее и информативнее, чем в приложении. Я сильно погрузился в сам Excel, в статистику и готов показать свои первые результаты сообществу.

Начало. Сводим баланс из приложения и в Excel

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

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

Первые попытки подружиться с Excel

Терять всю имеющуюся аналитику из приложения было грустно, поэтому я начал ее потихоньку восстанавливать своими силами в Excel. Сразу же было решено отказаться от VBA поскольку я не программист от слова совсем. Что-то пытаюсь, но это скорее баловство. Но вернемся к учету.
Сперва с помощью фильтров были подчищены очевидные ошибки при заполнении самих записей: опечатки, дублирование названий, склонения и прочие особенности Великого и Могучего.

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

Мне хотелось сделать все максимально автоматически. Создать на старте конфигурацию по умолчанию, прописать все формулы и вносить только записи в таблицу доходов-расходов. Остальное Excel должен считать сам. Слишком идеально, не?

Через 2 месяца после начала использования Excel я научился обращаться с таблицами, научился автоматически считать остатки по всем счетам и в сумме, разобрался в десятке самых используемых формул и начал опыты над сводными таблицами и условным форматирование. Интересно, а 2 месяца до сводных таблиц это много или мало для новичка?

PQ и PP

Еще примерно через 2 недели я познакомился с PowerQuery и PowerPivot. И если первый мне особо не помог (т.к. все велось в одном файле), то второй решил многие проблемы. Свободные таблицы стало создавать немного проще, а из таблицы записей удалось избавиться от нескольких столбцов их можно вычислять в PowerPivot.

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

Например, через PowerPivot и связи таблиц удалось наконец-то построить свободную с расходами в разбивке по категориям (150+ штук!). Строить такую в лоб приходилось через сводную из записей, вручную группировать категории до нужного уровня и визуально сравнивать значения. Это очень неудобно, хотя бы потому что любая новая категория в таблице записей ломала всю структуру сводной. И на восстановление уходило очень много времени. При помощи же PowerPivot на это требовалось 3 клика мыши.

Революция

Где-то примерно в это же время мне становиться тесно в моём файле и появляется еще один, для тестирования. В нем я могу делать с данными все что хочу, не боясь испортить результаты в основном. Есть только одна проблема чтобы формулы (и результаты) были одинаковыми, таблицу записей приходится вести в обоих файлах одновременно. Простое копирование новых строк ломает формулы и всё приходится перебивать руками. И новые фичи после тестирования руками построчно переносить в основной файл тоже такое себе удовольствие.

Поэтому в начале июня было принято решение вынести таблицу записей в отдельный файл, а формулы и графики считать отдельно. Вместе с таблицей записей переехала таблица счетов и некоторые некритичные мелочи. В самом файле с DATA минимум вычислений только пара именованных диапазонов и формула для высчитывания текущих остатков. Просто потому что загружать файл с вычислениями чтобы увидеть остатки оказалось глупой затеей.
Предназначение этого файла добавление строк в таблицу записей, контроль остатков на счетах и добавление счетов, если необходимо. Для всего остального файл с вычислениями.
И вот в нем вся магия и мощь Excel открылись на полную. Файл с вычислениями получает через PowerQuery данные, работает над ними и отдает в формулы и сводные таблицы. Рядом с ним, PowerPivot отдает свои данные и связывает имеющиеся таблицы в единую структуру. Из этого всего получается очень даже неплохая аналитика. Количество графиков и вычислений растет с каждым днем, объем файла с вычислениями постоянно увеличивается, что-то меняется на страницах. И все это автоматизировано на 90%!

Будущее

Сейчас я веду учет в трех файлах это DATA + файл с простыми вычислениями (BASIC) + файл с продвинутыми вычислениями (TEST). В таблице записей уже 6800 строк, общие остатки на счетах выросли в несколько раз. Я стал значительно меньше тратить на импульсивные покупки их просто лень вносить, а если и купил, то стыдно когда вносишь. В сводной таблице с тратами по категориям очень хорошо видно как поменялись расходы на самоизоляции в ноль просел общественный транспорт и походы в кафе/рестораны+обеды на работе. В июне есть хороший шанс закончить месяц в плюсе третий раз за 3 года, да еще и третий подряд. И очень хорошо видно в какой момент жена перестала переводить мне деньги на оплату счетов и начал переводить я ей на оплату продуктов. Но это уже наша внутренняя кухня.

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

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

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


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

1. Файл DATA. Сюда вносятся все записи расходов-доходов. По сути, это самый важный файл всей системы, он регулярно бекапится и дублируется. Потому что формулы то я восстановлю, а вот чеки могут выгореть. В этом же файле хранятся категории расходов (ибо вносятся в таблицу расходов), типы счетов, виды расчётов и другая служебная часть. Здесь же считаются остатки по каждому счету, т.к. файл имеет минимум вычислений и загружается мгновенно.
2. Файл BUDGET. Содержит в себе минимальные вычисления, показывает общий баланс и пару графиков. Используется для быстрой проверки гипотез, имеет небольшой вес, запускается быстро. Аналитики почти не включает, все выводы делаются самостоятельно.
3. Файл BUDGET PRO. Вот здесь хранятся все вычисления, графики, таблички и прочая интересная математика. Прямо сейчас в книге 11 страниц, 9 сводных таблиц, 8 диаграмм, 17152 формул и много чего еще. VBA все еще не используется.

Каждый файл имеет свои тесты и зеркала. Но об этом чуть ниже.

DATA

Основой DATA является таблица с записями расход-доходов. Таблица состоит из
||Счёт||Категория||Сумма||Тип оплаты||Как оплачено||Дата||Что покупалось||Где покупалось||. Формул в таблице нет, но есть подсказки. Например, Счёт, Тип оплаты и Как оплачено можно выбрать из выпадающего списка. Счета подтягиваются из таблицы счетов, остальное из служебных таблиц. Сумма всегда указывается положительная. Категории не выбираются из списка (т.к. их слишком много) но там есть своя фича. В каждой новой строке категория проверяется на соответствие таблице Категорий и, если такой нет, ругается. На языке Excel это называется Проверка данных и позволяет исключить опечатки, ошибки склонений и другие сюрпризы. В принципе, можно настроить Проверку данных ещё и на Сумму или Дату покупки, но здесь проблем меньше.

Служебные таблицы и таблицу Категорий расписывать не буду, тут вроде всё понятно. Давайте сразу к таблице счетов. ||Название||Валюта||Вид счёта||Начальный баланс||Текущий остаток|| ничего лишнего, все очень просто. Вид счёта тянется из служебной таблички (Наличные, Валюта, Эл. кошелёк и другие) и выбирается из списка, валюта пока используется как есть. Не исключаю таблицу с валютами при увеличении их количества, но пока не требуется. Было интересно сколько рублей живёт в валюте, начал искать возможность получать курсы автоматом с сайта ЦБ. Пока остановился на формуле

Не самое изящное решение, но достаточно простое. Готов к другим вариантам, готов к обсуждению. Хочу простую формулу чтобы получать текущий курс от ЦБ и дальше уже работать с ним, памяти не надо, графиков и вот этого всего тоже. Только курс прямо сейчас.
Текущие остатки считаются тоже очень просто: Доход Расход + Перевод на Перевод с. Ну и плюс Начальный баланс, конечно же.

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

Недостатки DATA

Самый большой сейчас недостаток курсы валют. По воскресеньям ЦБ не публикует курсы, поэтому формула отдаёт ошибку. Чтобы побороть её надо переписать формулу. В поисках вдохновения или подсказок.
Второе непонятно что делать с закрытыми счетами. Удалять их из таблицы счетов неправильно, тогда и таблица записей сломается. Болтаться им в общей таблице счетов тоже не к месту. Фильтр скрывает их, но не решает проблемы. Создавать отдельную таблицу с закрытыми счетами тоже не очень. В пассивном поиске решения.
Ну и самое странное для меня при операциях с финансами, Excel начинает считать суммы типа 0,999999999999999999999 вместо 1, E-12, -0 и другие. Форматирование как денежный тип данных решает отображение ячеек, но в формулах такие значения ломаются. Например, 0,00-(-2,27374E-13) не считается как равное нулю, хотя и является таковым. И таких ошибок все больше и больше в моих таблицах.

BUDGET

Как я уже говорил, BUDGET содержит в себе минимальные вычисления. Если не нужна аналитика, но хочется посмотреть какие-то существенные вещи открывается этот файл. Прямо сейчас здесь всего 3 листа: таблица записей, общий баланс (формальный остаток) + распределение денег по счетам и график движения в периоде. Практически весь файл считается и обновляется автоматически, я его только просматриваю. Ну и дорабатываю, но это не относится к финансам.
Изначально, в файл BUDGET через PowerQuery подгружаются таблица Счетов и таблица Записей. При этом, таблица Счетов практически не обрабатывается: сортировкой по сумме остатка можно пренебречь, да и спорное это решение. Чуть ниже расскажу зачем я это сделал. Таблица Записей же обрабатывается сильнее. Для начала, все записи сортируются по дате операции. Потому что записи вносятся не всегда сразу, не в хронологическом порядке, сортировка записей в DATA выполняется от случая к случаю. Поэтому, для контроля, мне проще сортировку выполнять при первичной обработке. Вторым этапом меняется столбец Суммы операции с числового на валютный. Столбец Даты операции сам хорошо распознается как дата, остальные столбцы размечены как текстовые. В третий этап добавляется столбец с индексом записей и в таком виде таблица выгружается на лист. Справедливости ради, к таблице Записей в BUDGET я практически не обращался, в основном работаю с ней в DATA.

График движения

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

Таблица счетов

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

На основании Текущих остатков таблицы Счетов строится круговая диаграмма распределения денег. Таким образом, я сразу же вижу на каком счёте какая часть денег находится. Для этого и понадобилось сортировать таблицу Счетов по Текущему остатку части диаграммы тоже отсортированы по возрастанию, а не в хаотичном порядке.
Кроме того, в отдельных ячейках считается весь текущий остаток в разрезе валют. Это нужно для понимания сколько у меня денег вообще в данный момент. Формула максимально простая: =СУММЕСЛИ(accounts[Валюта];"RUR";accounts[Текущий Баланс])

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