Русский
Русский
English
Статистика
Реклама

Ведение собственного бюджета в Excel проще чем два файла перебрать

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

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

DATA


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

Служебные таблицы и таблицу Категорий расписывать не буду, тут вроде всё понятно. Давайте сразу к таблице счетов. ||Название||Валюта||Вид счёта||Начальный баланс||Текущий остаток|| ничего лишнего, все очень просто. Вид счёта тянется из служебной таблички (Наличные, Валюта, Эл. кошелёк и другие) и выбирается из списка, валюта пока используется как есть. Не исключаю таблицу с валютами при увеличении их количества, но пока не требуется. Было интересно сколько рублей живёт в валюте, начал искать возможность получать курсы автоматом с сайта ЦБ. Пока остановился на формуле
Формула курсов
=ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01239");".";",");"=""1,0";"=""1.0");"//ValCurs//Record//Value")

Не самое изящное решение, но достаточно простое. Готов к другим вариантам, готов к обсуждению. Хочу простую формулу чтобы получать текущий курс от ЦБ и дальше уже работать с ним, памяти не надо, графиков и вот этого всего тоже. Только курс прямо сейчас.
Текущие остатки считаются тоже очень просто: Доход Расход + Перевод на Перевод с. Ну и плюс Начальный баланс, конечно же.
Формула остатков
=СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Доход")-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Расход")+СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Перевод на")-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Перевод с")+[@[Начальный баланс]]

Благодаря тому что таблица с записями и таблица счетов размечены как таблицы, а не массив данных формула универсальна для любой строки. При добавлении нового счёта, все формулы будут добавлены автоматически, от меня требуется только указать Вид счета и Валюту. Да и то, первый выбираю из готового выпадающего списка.
Лайфхак
Ещё пара слов про валюты и курсы. Так вышло что я очень много перевожу денег между своими счетами. Поэтому, помимо записей типа Расход/Доход существуют ещё Перевод с/Перевод на. Позже, это оказалось весьма эффективным при покупке валюты. Сейчас я сразу вижу сколько валюты купил, сколько на нее потратил и могу даже посчитать свой средний курс.


Недостатки DATA


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


BUDGET


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

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


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

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


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

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

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

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Финансы в it

Лайфхаки для гиков

Excel

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

Microsoft office

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru