Деньги любят считать! Привет жители планеты! Меня зовут Антон и я занимаюсь личным бухгалтерским учетом уже 3 года.Теги: #Лайфхаки для гиков #Финансы в IT #Excel #бухгалтерия #бухгалтерияВыводов пока не очень много, но они достаточно интересны.
Первые 2 года я пользовался специальным приложением, но в 2020 году перешёл на голый Excel и формулы — никакого программирования и скриптов.
Как это произошло и что из этого получилось — под катом с картинками.
Сначала немного вводного
В конце мая 2017 года я подумал, что было бы неплохо начать отслеживать, на что и как именно я трачу свои деньги.Мой доход тогда был небольшим, но его хватало на жизнь.
Было решено с 1 июня 2017 года фиксировать свои доходы и расходы в специальном приложении и смотреть, куда уходят деньги.
После нескольких пробных запусков выбор пал на одного польского разработчика с бесплатными функциями.
Итак, на 1 июня 2017 года мой стартовый баланс составил 4 031,49 рубля.
До конца 2017 года записи делались эпизодически.
Приложение предоставило неинформативную статистику, что меня крайне не удовлетворило.
Поэтому с 01.01.2018 учет ведется строго и тщательно - вплоть до того, что каждое воскресенье открывается каждый интернет-банк и сверяются текущие балансы.
Это дало хорошие результаты уже через пару месяцев.
Привычка все внедрять прижилась достаточно быстро, и дело стало более продуктивным.
Приложение, которое я использовал, позволило мне бесплатно отслеживать 2 аккаунта.
Но вскоре их у меня стало слишком много и пару раз мне пришлось заплатить за приложение.
В итоге в конце января 2020 года мне стало жаль денег на новое продление, я скачал все данные и начал вести учет в голом Excel. Спустя полгода могу сказать, что гораздо интереснее и познавательнее, чем в приложении.
Я глубоко погружен в сам Excel, в статистику и готов показать сообществу свои первые результаты.
Начинать.
Балансируем баланс из приложения и в Excel Хотя приложение хорошее, мне на экспорт выдали только записи: 5600 строк совершенно неструктурированной информации.
Окей, мы взяли файл, определили и уточнили все нужные столбцы, вырезали все ненужные и придали ему красивый табличный вид. В этот момент я начинаю понимать, что балансы не суммируются - у меня есть только записи, без стартовых сумм.
Создается новая таблица, все счета и их остатки вносятся на начало учета - 01.06.2017. Чтобы не мучиться с категориями расходов, было решено полностью скопировать структуру из того же приложения.
Щепотка волшебства — и у меня получился очень маленький и простой файл.
К счастью, он показывал мне только мои текущие балансы, уровень доходов и расходов по годам, и все записи велись в нем.
Первые попытки подружиться с Excel
Было грустно терять всю имеющуюся аналитику из приложения, поэтому я начал потихоньку восстанавливать ее самостоятельно в Excel. От VBA было сразу решено отказаться, так как я вообще не программист. Я пытаюсь что-то сделать, но это больше похоже на баловство.Но вернемся к бухгалтерскому учету.
Во-первых, с помощью фильтров были убраны явные ошибки при заполнении самих записей: опечатки, дублирование имен, склонений и другие особенности Великого и Могучего.
На втором этапе начались эксперименты с аналитикой — графики движения с разбивкой по валютам, по датам, по счетам и многому другому.
При этом я активно гуглил всё, что мог, и смотрел, как ведут записи другие (слишком сложно вводить и слишком просто для анализа).
Я хотел сделать все максимально автоматически.
Создайте для начала конфигурацию по умолчанию, пропишите все формулы и делайте только записи в таблице доходов-расходов.
Excel должен рассчитать остаток сам.
Слишком идеально, не так ли? За 2 месяца начала использования Excel я научился обращаться с таблицами, научился автоматически рассчитывать балансы по всем счетам и в общей сложности разобрался с десяткой самых используемых формул и начал эксперименты со сводными таблицами и условным форматированием.
Интересно, 2 месяца до сводных таблиц — это много или мало для новичка?
ПК и ПП
Примерно через две недели я познакомился с PowerQuery и PowerPivot. И если первый мне не особо помог (потому что все делалось одним файлом), то второй решил многие проблемы.Сводные таблицы стало немного проще создавать, и нам удалось избавиться от нескольких столбцов из таблицы записей — их можно рассчитать в PowerPivot. Вообще PowerPivot — штука достаточно сложная и крайне чувствительная к любым действиям.
При этом материала по нему мало и во многом приходится разбираться самому.
Но результат может просто порадовать, он заметен.
Например, с помощью PowerPivot и ссылок на таблицы мы наконец смогли построить сводку расходов с разбивкой по категориям (более 150 позиций!).
Для построения этого «в лоб» необходимо было использовать сводку записей, вручную группировать категории до необходимого уровня и визуально сравнивать значения.
Это очень неудобно хотя бы потому, что любая новая категория в таблице записей нарушала всю структуру сводки.
И на восстановление ушло много времени.
При использовании PowerPivot для этого потребовалось 3 щелчка мыши.
Революция
Где-то в это время мой файл становится переполненным и появляется еще один для тестирования.В нем я могу делать с данными все, что захочу, не опасаясь существенно испортить результаты.
Есть только одна проблема — чтобы формулы (и результаты) были одинаковыми, таблица записей должна вестись в обоих файлах одновременно.
Простое копирование новых строк нарушает формулы, и вам придется все переделывать вручную.
А после тестирования вручную переносить новые фичи построчно в основной файл тоже такое удовольствие.
Поэтому в начале июня было принято решение вынести таблицу рекордов в отдельный файл, а формулы и графики рассчитывать отдельно.
Вместе с таблицей рекордов переехала таблица счетов и некоторые некритические детали.
В самом файле DATA минимум расчетов - всего пара именованных диапазонов и формула расчета текущих балансов.
Просто потому, что загружать файл с расчетами, чтобы посмотреть балансы, оказалось глупой идеей.
Назначение этого файла — добавление строк в таблицу записей, контроль остатков на счетах и добавление счетов при необходимости.
Для всего остального - файл с расчетами.
И здесь вся магия и мощь Excel раскрылась в полной мере.
Файл с вычислениями получает данные через PowerQuery, обрабатывает их и отправляет в формулы и сводные таблицы.
Рядом с ним PowerPivot предоставляет свои данные и связывает существующие таблицы в единую структуру.
Все это позволяет получить довольно хорошую аналитику.
Количество графиков и расчетов растет с каждым днем, объем файла с расчетами постоянно увеличивается, что-то меняется на страницах.
И все это на 90% автоматизировано!
Будущее
Сейчас я веду учет в трех файлах - это ДАННЫЕ + файл с простыми расчетами (БЕЙСИК) + файл с расширенными расчетами (ТЕСТ).Таблица записей уже насчитывает 6800 строк, а общие остатки на счетах увеличились в несколько раз.
Я стала существенно меньше тратить на импульсивные покупки — мне просто лень за них платить, а даже если и купила, то обидно, когда это сделаю.
В сводной таблице расходов по категориям наглядно видно, как изменились расходы за время самоизоляции — общественный транспорт и походы в кафе/рестораны + обеды на работе упали до нуля.
В июне есть хороший шанс завершить месяц в плюсе – в третий раз за 3 года, а то и в третий раз подряд. И очень понятно, в какой момент жена перестала переводить мне деньги на оплату счетов и я начал переводить ей деньги на оплату продуктов.
Но это наша внутренняя кухня.
Я работаю над файлами каждый день в свободное время.
Сегодня, например, я все формулы деления перебил на формулы ЕСЛИОШИБКА - так меньше всплывающего спама.
В целом у меня в заметках более 20 идей, над которыми я могу работать.
Какие-то вещи сделать легко, какие-то требуют менять структуру всех трёх файлов (не хочу), а какие-то я просто не умею делать и их нужно погуглить и попробовать.
Например, я не могу понять, как построить ежемесячный бюджет и контролировать его выполнение, не нарушая структуру таблицы.
В общем, я знаю, чего хочу, знаю, как это должно выглядеть.
Но все чаще я начинаю натыкаться на ограничения самого Excel и его возможностей.
Интересно попробовать собственное дополнение для личного учета, но то, что я увидел, мне не понравилось.
Думаю, что у меня больше, детальнее и точнее.
Ну и еще много всего в процессе, я просто реализовал всю аналитику, которая была в приложении.
В любом случае, продолжение следует.
-
Изучение Node.js
19 Oct, 24 -
Видеонаблюдение Через 3/4G (Часть 1)
19 Oct, 24 -
Наши Статьи, Хабракат, Что-То Не Так :)
19 Oct, 24 -
Как Наши Сенсоры Провели Свою Первую Зиму
19 Oct, 24 -
Перерезка Кабеля В 2019 Году
19 Oct, 24 -
Evermap → Nevermap
19 Oct, 24
