[Финансы в IT, Лайфхаки для гиков] Ведение собственного бюджета в Excel: проще чем два файла перебрать
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
В первой части я описал основные принципы и подходы к своему бюджету. Можно вечно рассуждать много или мало я зарабатываю, на что именно трачу деньги, какие управленческие решения принимаю из таблицы учёта, и удобен ли Excel вообще для этого. Но это все-таки технический блог. Поэтому вторая часть будет посвящена технической стороне моего бюдж учёта финансов.
Вкратце напомню, как ведётся учёт сегодня, если уважаемый читатель не ознакомлен с первой частью. После нескольких месяцев проб и ошибок, было решено перейти к системе из трёх основных файлов:
- Файл DATA. Сюда вносятся все записи расходов-доходов. По сути, это самый важный файл всей системы, он регулярно бекапится и дублируется. Потому что формулы то я восстановлю, а вот чеки могут выгореть. В этом же файле хранятся категории расходов (ибо вносятся в таблицу расходов), типы счетов, виды расчётов и другая служебная часть. Здесь же считаются остатки по каждому счету, т.к. файл имеет минимум вычислений и загружается мгновенно.
- Файл BUDGET. Содержит в себе минимальные вычисления, показывает общий баланс и пару графиков. Используется для быстрой проверки гипотез, имеет небольшой вес, запускается быстро. Аналитики почти не включает, все выводы делаются самостоятельно.
- Файл BUDGET PRO. Вот здесь хранятся все вычисления, графики, таблички и прочая интересная математика. Прямо сейчас в книге 11 страниц, 9 сводных таблиц, 8 диаграмм, 17152 формул и много чего еще. VBA все еще не используется.
Каждый файл имеет свои тесты и зеркала. Но об этом чуть ниже.
DATA
Основой DATA является таблица с записями расход-доходов. Таблица состоит из
||Счёт||Категория||Сумма||Тип оплаты||Как оплачено||Дата||Что покупалось||Где покупалось||. Формул в таблице нет, но есть подсказки. Например, Счёт, Тип оплаты и Как оплачено можно выбрать из выпадающего списка. Счета подтягиваются из таблицы счетов, остальное — из служебных таблиц. Сумма всегда указывается положительная. Категории не выбираются из списка (т.к. их слишком много) но там есть своя фича. В каждой новой строке категория проверяется на соответствие таблице Категорий и, если такой нет, ругается. На языке Excel это называется «Проверка данных» и позволяет исключить опечатки, ошибки склонений и другие сюрпризы. В принципе, можно настроить Проверку данных ещё и на Сумму или Дату покупки, но здесь проблем меньше.
Лайфхак
SPL
В прошлый раз спрашивали как вносить длинные чеки. Открыл для себя комбинацию клавиш CTRL+D — копирует ячейку выше. Нажимаем несколько раз, и на новую запись мне нужно внести только Категорию, Сумму и Что покупалось. Нет ничего проще.
Служебные таблицы и таблицу Категорий расписывать не буду, тут вроде всё понятно. Давайте сразу к таблице счетов. ||Название||Валюта||Вид счёта||Начальный баланс||Текущий остаток|| — ничего лишнего, все очень просто. Вид счёта тянется из служебной таблички (Наличные, Валюта, Эл. кошелёк и другие) и выбирается из списка, валюта пока используется как есть. Не исключаю таблицу с валютами при увеличении их количества, но пока не требуется. Было интересно сколько рублей живёт в валюте, начал искать возможность получать курсы автоматом с сайта ЦБ. Пока остановился на формуле
Формула курсов
SPL
=ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01239");".";",");"=""1,0";"=""1.0");"//ValCurs//Record//Value")
Не самое изящное решение, но достаточно простое. Готов к другим вариантам, готов к обсуждению. Хочу простую формулу чтобы получать текущий курс от ЦБ и дальше уже работать с ним, памяти не надо, графиков и вот этого всего — тоже. Только курс прямо сейчас.
Текущие остатки считаются тоже очень просто: Доход — Расход + Перевод на — Перевод с. Ну и плюс Начальный баланс, конечно же.
Формула остатков
SPL
=СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Доход")-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Расход")+СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Перевод на")-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Перевод с")+[@[Начальный баланс]]
Благодаря тому что таблица с записями и таблица счетов размечены как таблицы, а не массив данных — формула универсальна для любой строки. При добавлении нового счёта, все формулы будут добавлены автоматически, от меня требуется только указать Вид счета и Валюту. Да и то, первый выбираю из готового выпадающего списка.
Лайфхак
SPL
Ещё пара слов про валюты и курсы. Так вышло что я очень много перевожу денег между своими счетами. Поэтому, помимо записей типа Расход/Доход существуют ещё Перевод с/Перевод на. Позже, это оказалось весьма эффективным при покупке валюты. Сейчас я сразу вижу сколько валюты купил, сколько на нее потратил и могу даже посчитать свой средний курс.
Недостатки DATA
Самый большой сейчас недостаток — курсы валют. По воскресеньям ЦБ не публикует курсы, поэтому формула отдаёт ошибку. Чтобы побороть её надо переписать формулу. В поисках вдохновения или подсказок.
Второе — непонятно что делать с закрытыми счетами. Удалять их из таблицы счетов неправильно, тогда и таблица записей сломается. Болтаться им в общей таблице счетов тоже не к месту. Фильтр скрывает их, но не решает проблемы. Создавать отдельную таблицу с закрытыми счетами тоже не очень. В пассивном поиске решения.
Ну и самое странное для меня — при операциях с финансами, Excel начинает считать суммы типа 0,999999999999999999999 вместо 1, E-12, -0 и другие. Форматирование как денежный тип данных решает отображение ячеек, но в формулах такие значения ломаются. Например, 0,00-(-2,27374E-13) не считается как равное нулю, хотя и является таковым. И таких ошибок все больше и больше в моих таблицах.
Лайфхак
SPL
Вместо тестовых счетов и записей очень эффективно оказалось тестирование пустыми данными. Служебные таблицы остаются не тронутыми, а таблица записей и таблица счетов вычищаются полностью. Такое простое решение позволило мне исправить несколько существенных ошибок.
BUDGET
Как я уже говорил, BUDGET содержит в себе минимальные вычисления. Если не нужна аналитика, но хочется посмотреть какие-то существенные вещи — открывается этот файл. Прямо сейчас здесь всего 3 листа: таблица записей, общий баланс (формальный остаток) + распределение денег по счетам и график движения в периоде. Практически весь файл считается и обновляется автоматически, я его только просматриваю. Ну и дорабатываю, но это не относится к финансам.
Изначально, в файл BUDGET через PowerQuery подгружаются таблица Счетов и таблица Записей. При этом, таблица Счетов практически не обрабатывается: сортировкой по сумме остатка можно пренебречь, да и спорное это решение. Чуть ниже расскажу зачем я это сделал. Таблица Записей же обрабатывается сильнее. Для начала, все записи сортируются по дате операции. Потому что записи вносятся не всегда сразу, не в хронологическом порядке, сортировка записей в DATA выполняется от случая к случаю. Поэтому, для контроля, мне проще сортировку выполнять при первичной обработке. Вторым этапом меняется столбец Суммы операции с числового на валютный. Столбец Даты операции сам хорошо распознается как дата, остальные столбцы размечены как текстовые. В третий этап добавляется столбец с индексом записей и в таком виде таблица выгружается на лист. Справедливости ради, к таблице Записей в BUDGET я практически не обращался, в основном работаю с ней в DATA.
Лайфхак
SPL
В определённый момент было решено сортировку по индексу делать в обратном порядке. Таким образом, последние операции у меня всегда вверху таблицы, это оказалось банально удобнее.
График движения
График движения тоже максимально прост. Собирается сводная таблица по общему обороту в разрезе типов операции за каждый месяц. На основе этой таблицы строится график. На графике сразу же видно когда расходы превышали доходы, когда покупалось много валюты, какие вообще объёмы движения денег за определённый месяц. При желании, с графика можно убрать переводы и смотреть только на отношение доходов к расходам. Но меня все устраивает.
Лайфхак
SPL
В Excel есть обычные горизонтальные графики и графики с накоплениями. Из каждого из них можно делать свои выводы или управленческие решения. Мне ближе оказались обычные графики, здесь каждый может выбрать для себя.
Таблица счетов
Ну и пожалуй самый важный лист в этом файле — Счета. Но и здесь не очень много анализа. Как я уже говорил, таблица Счетов практически без изменений подгружается на лист из DATA. Чтобы мне не мешали лишние данные, несколько столбцов сразу же скрываются, например Начальный остаток и Валюта. Казалось бы, зачем их сперва подгружать, а затем прятать? Дело в том, что эти столбцы участвуют в вычислениях. Поэтому, они все равно нужны, хоть я и не хочу их видеть.
Лайфхак
SPL
Пока таблица Счетов была статична — обозначение валюты стояло приколоченным к каждой ячейке. Но после включения сортировки пришлось искать другой способ. И дело было даже не столько в самом символе валюты, скорее в общем формате. Победить обе проблемы получилось через условное форматирование по формуле. Если валюта в таблице указана как RUR, формат ячейки показывается как 0,00 ₽. Аналогично и для других валют.
На основании Текущих остатков таблицы Счетов строится круговая диаграмма распределения денег. Таким образом, я сразу же вижу на каком счёте какая часть денег находится. Для этого и понадобилось сортировать таблицу Счетов по Текущему остатку — части диаграммы тоже отсортированы по возрастанию, а не в хаотичном порядке.
Кроме того, в отдельных ячейках считается весь текущий остаток в разрезе валют. Это нужно для понимания сколько у меня денег вообще в данный момент. Формула максимально простая: =СУММЕСЛИ(accounts[Валюта];"RUR";accounts[Текущий Баланс])
Лайфхак
SPL
Когда я начал замечать что где-то расчёты не совпадают, решил сам себя перепроверить. Так, рядом с суммой текущего остатка по таблице Счетов появились суммы текущего остатка по таблице Записей. Сравнить глазами три пару чисел не так сложно. Ну или можно добавить ещё условного форматирования.
Немного аналитики имеется и на этом листе. Здесь считаются Доходы в текущем месяце/году, Расходы в текущем месяце/году и выводятся балансы на начало месяца/года. Это позволяет с первого взгляда понять сколько я заработал за год, сколько я могу потратить чтобы не уйти в минус или сделать другие выводы. Не скажу что я всегда придерживаюсь этих данных, но не зря же они высчитываются :-)
===========
Источник:
habr.com
===========
Похожие новости:
- [Открытые данные, Визуализация данных, Венчурные инвестиции, Статистика в IT, Финансы в IT] На фондовом рынке США сформировался пузырь небывалых размеров
- [Кодобред, Системное программирование, Разработка под Linux, Лайфхаки для гиков] Локализация своих скриптов на BASH
- [Законодательство в IT, Финансы в IT] Администрация Байдена пообещала не снимать санкции с Huawei и других китайских компаний
- [Законодательство в IT, Финансы в IT] Активностью онлайн-сообществ трейдеров в отношении акций GameStop заинтересовались регуляторы США
- [Статистика в IT, Научно-популярное, Финансы в IT] Южная Корея возглавила индекс инновационности Bloomberg, США вышли из десятки
- [Производство и разработка электроники, Финансы в IT] Международный союз электросвязи купит питерскую разработку для поиска взрывчатки
- [Разработка игр, Финансы в IT, Игры и игровые приставки, IT-компании] Gearbox продался за 1,3 млрд долларов холдингу Embracer Group
- [Информационная безопасность, Лайфхаки для гиков] Как правильно зарегистрировать электронную почту
- [1С-Битрикс, CRM-системы, Разработка под e-commerce, Лайфхаки для гиков] Разработка бизнес-процесса в Битрикс24. Советы руководителю отдела
- [Финансы в IT, Игры и игровые приставки] Nintendo Switch достигла отметки в 80 млн проданных экземпляров
Теги для поиска: #_finansy_v_it (Финансы в IT), #_lajfhaki_dlja_gikov (Лайфхаки для гиков), #_excel, #_vedenie_buhgalterii (ведение бухгалтерии), #_microsoft_office, #_finansy_v_it (
Финансы в IT
), #_lajfhaki_dlja_gikov (
Лайфхаки для гиков
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 23:40
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
В первой части я описал основные принципы и подходы к своему бюджету. Можно вечно рассуждать много или мало я зарабатываю, на что именно трачу деньги, какие управленческие решения принимаю из таблицы учёта, и удобен ли Excel вообще для этого. Но это все-таки технический блог. Поэтому вторая часть будет посвящена технической стороне моего бюдж учёта финансов.  Вкратце напомню, как ведётся учёт сегодня, если уважаемый читатель не ознакомлен с первой частью. После нескольких месяцев проб и ошибок, было решено перейти к системе из трёх основных файлов:
DATA Основой DATA является таблица с записями расход-доходов. Таблица состоит из ||Счёт||Категория||Сумма||Тип оплаты||Как оплачено||Дата||Что покупалось||Где покупалось||. Формул в таблице нет, но есть подсказки. Например, Счёт, Тип оплаты и Как оплачено можно выбрать из выпадающего списка. Счета подтягиваются из таблицы счетов, остальное — из служебных таблиц. Сумма всегда указывается положительная. Категории не выбираются из списка (т.к. их слишком много) но там есть своя фича. В каждой новой строке категория проверяется на соответствие таблице Категорий и, если такой нет, ругается. На языке Excel это называется «Проверка данных» и позволяет исключить опечатки, ошибки склонений и другие сюрпризы. В принципе, можно настроить Проверку данных ещё и на Сумму или Дату покупки, но здесь проблем меньше. ЛайфхакSPLВ прошлый раз спрашивали как вносить длинные чеки. Открыл для себя комбинацию клавиш CTRL+D — копирует ячейку выше. Нажимаем несколько раз, и на новую запись мне нужно внести только Категорию, Сумму и Что покупалось. Нет ничего проще.
Служебные таблицы и таблицу Категорий расписывать не буду, тут вроде всё понятно. Давайте сразу к таблице счетов. ||Название||Валюта||Вид счёта||Начальный баланс||Текущий остаток|| — ничего лишнего, все очень просто. Вид счёта тянется из служебной таблички (Наличные, Валюта, Эл. кошелёк и другие) и выбирается из списка, валюта пока используется как есть. Не исключаю таблицу с валютами при увеличении их количества, но пока не требуется. Было интересно сколько рублей живёт в валюте, начал искать возможность получать курсы автоматом с сайта ЦБ. Пока остановился на формуле Формула курсовSPL=ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01239");".";",");"=""1,0";"=""1.0");"//ValCurs//Record//Value")
Не самое изящное решение, но достаточно простое. Готов к другим вариантам, готов к обсуждению. Хочу простую формулу чтобы получать текущий курс от ЦБ и дальше уже работать с ним, памяти не надо, графиков и вот этого всего — тоже. Только курс прямо сейчас. Текущие остатки считаются тоже очень просто: Доход — Расход + Перевод на — Перевод с. Ну и плюс Начальный баланс, конечно же. Формула остатковSPL=СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Доход")-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Расход")+СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Перевод на")-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];"Перевод с")+[@[Начальный баланс]]
Благодаря тому что таблица с записями и таблица счетов размечены как таблицы, а не массив данных — формула универсальна для любой строки. При добавлении нового счёта, все формулы будут добавлены автоматически, от меня требуется только указать Вид счета и Валюту. Да и то, первый выбираю из готового выпадающего списка. ЛайфхакSPLЕщё пара слов про валюты и курсы. Так вышло что я очень много перевожу денег между своими счетами. Поэтому, помимо записей типа Расход/Доход существуют ещё Перевод с/Перевод на. Позже, это оказалось весьма эффективным при покупке валюты. Сейчас я сразу вижу сколько валюты купил, сколько на нее потратил и могу даже посчитать свой средний курс.
Недостатки DATA Самый большой сейчас недостаток — курсы валют. По воскресеньям ЦБ не публикует курсы, поэтому формула отдаёт ошибку. Чтобы побороть её надо переписать формулу. В поисках вдохновения или подсказок. Второе — непонятно что делать с закрытыми счетами. Удалять их из таблицы счетов неправильно, тогда и таблица записей сломается. Болтаться им в общей таблице счетов тоже не к месту. Фильтр скрывает их, но не решает проблемы. Создавать отдельную таблицу с закрытыми счетами тоже не очень. В пассивном поиске решения. Ну и самое странное для меня — при операциях с финансами, Excel начинает считать суммы типа 0,999999999999999999999 вместо 1, E-12, -0 и другие. Форматирование как денежный тип данных решает отображение ячеек, но в формулах такие значения ломаются. Например, 0,00-(-2,27374E-13) не считается как равное нулю, хотя и является таковым. И таких ошибок все больше и больше в моих таблицах. ЛайфхакSPLВместо тестовых счетов и записей очень эффективно оказалось тестирование пустыми данными. Служебные таблицы остаются не тронутыми, а таблица записей и таблица счетов вычищаются полностью. Такое простое решение позволило мне исправить несколько существенных ошибок.
BUDGET Как я уже говорил, BUDGET содержит в себе минимальные вычисления. Если не нужна аналитика, но хочется посмотреть какие-то существенные вещи — открывается этот файл. Прямо сейчас здесь всего 3 листа: таблица записей, общий баланс (формальный остаток) + распределение денег по счетам и график движения в периоде. Практически весь файл считается и обновляется автоматически, я его только просматриваю. Ну и дорабатываю, но это не относится к финансам. Изначально, в файл BUDGET через PowerQuery подгружаются таблица Счетов и таблица Записей. При этом, таблица Счетов практически не обрабатывается: сортировкой по сумме остатка можно пренебречь, да и спорное это решение. Чуть ниже расскажу зачем я это сделал. Таблица Записей же обрабатывается сильнее. Для начала, все записи сортируются по дате операции. Потому что записи вносятся не всегда сразу, не в хронологическом порядке, сортировка записей в DATA выполняется от случая к случаю. Поэтому, для контроля, мне проще сортировку выполнять при первичной обработке. Вторым этапом меняется столбец Суммы операции с числового на валютный. Столбец Даты операции сам хорошо распознается как дата, остальные столбцы размечены как текстовые. В третий этап добавляется столбец с индексом записей и в таком виде таблица выгружается на лист. Справедливости ради, к таблице Записей в BUDGET я практически не обращался, в основном работаю с ней в DATA. ЛайфхакSPLВ определённый момент было решено сортировку по индексу делать в обратном порядке. Таким образом, последние операции у меня всегда вверху таблицы, это оказалось банально удобнее.
График движения График движения тоже максимально прост. Собирается сводная таблица по общему обороту в разрезе типов операции за каждый месяц. На основе этой таблицы строится график. На графике сразу же видно когда расходы превышали доходы, когда покупалось много валюты, какие вообще объёмы движения денег за определённый месяц. При желании, с графика можно убрать переводы и смотреть только на отношение доходов к расходам. Но меня все устраивает. ЛайфхакSPLВ Excel есть обычные горизонтальные графики и графики с накоплениями. Из каждого из них можно делать свои выводы или управленческие решения. Мне ближе оказались обычные графики, здесь каждый может выбрать для себя.
Таблица счетов Ну и пожалуй самый важный лист в этом файле — Счета. Но и здесь не очень много анализа. Как я уже говорил, таблица Счетов практически без изменений подгружается на лист из DATA. Чтобы мне не мешали лишние данные, несколько столбцов сразу же скрываются, например Начальный остаток и Валюта. Казалось бы, зачем их сперва подгружать, а затем прятать? Дело в том, что эти столбцы участвуют в вычислениях. Поэтому, они все равно нужны, хоть я и не хочу их видеть. ЛайфхакSPLПока таблица Счетов была статична — обозначение валюты стояло приколоченным к каждой ячейке. Но после включения сортировки пришлось искать другой способ. И дело было даже не столько в самом символе валюты, скорее в общем формате. Победить обе проблемы получилось через условное форматирование по формуле. Если валюта в таблице указана как RUR, формат ячейки показывается как 0,00 ₽. Аналогично и для других валют.
На основании Текущих остатков таблицы Счетов строится круговая диаграмма распределения денег. Таким образом, я сразу же вижу на каком счёте какая часть денег находится. Для этого и понадобилось сортировать таблицу Счетов по Текущему остатку — части диаграммы тоже отсортированы по возрастанию, а не в хаотичном порядке. Кроме того, в отдельных ячейках считается весь текущий остаток в разрезе валют. Это нужно для понимания сколько у меня денег вообще в данный момент. Формула максимально простая: =СУММЕСЛИ(accounts[Валюта];"RUR";accounts[Текущий Баланс]) ЛайфхакSPLКогда я начал замечать что где-то расчёты не совпадают, решил сам себя перепроверить. Так, рядом с суммой текущего остатка по таблице Счетов появились суммы текущего остатка по таблице Записей. Сравнить глазами три пару чисел не так сложно. Ну или можно добавить ещё условного форматирования.
Немного аналитики имеется и на этом листе. Здесь считаются Доходы в текущем месяце/году, Расходы в текущем месяце/году и выводятся балансы на начало месяца/года. Это позволяет с первого взгляда понять сколько я заработал за год, сколько я могу потратить чтобы не уйти в минус или сделать другие выводы. Не скажу что я всегда придерживаюсь этих данных, но не зря же они высчитываются :-) =========== Источник: habr.com =========== Похожие новости:
Финансы в IT ), #_lajfhaki_dlja_gikov ( Лайфхаки для гиков ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 23:40
Часовой пояс: UTC + 5