Деньги любят счет!Привет жители планеты! Моё имя Антон и я уже 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 и его возможностей. Интересно попробовать их собственную надстройку для ведения личного учета, но то что я видел мне уже не нравится. Считаю что у меня больше, детальнее и точнее. Ну и еще многое в процессе, я только-только реализовал всю аналитику что была в приложении. В любом случае, продолжение следует…
