I am working on a personal budget tracker which has this structure :
Year
Month
Categories
Sub categories
Expenses
Here is how it works :
I create my categories & sub categories. Each sub category has a monthly budget, that gives me the parent category’s monthly budget.
Every month, I add a month where I select the categories depending on my lifestyle (rent, electricity…). With relations, this gives me my total monthly budget, which is added to my yearly budget.
The problem is, if I change a sub categories’ budget, it also change my past monthly budget, since my monthly and yearly budget are tied to it with relation.
For example, let’s say that my rent was 600 in 2022, giving me a yearly budget of 7200. In 2023, it increases to 800, so I edit my rent category budget, but by doing so it also changes my 2022 yearly budget which makes my past budget, average etc false.
Is there a way, when I add a month, to calculate my budget based on the selected categories but as a fixed value at the moment of creation ? I don’t want it to update in the future.
something is wrong with your logic… what exactly you wanna achieve?
Do you need a budget for the whole year or each category? which doesn’t make sense…
also, you can just put the date… and then split that date into month + year, and just year for relations… only a month will not do it… unless is just one-year data… but you are recording year… so this is not the case…
so your structure should look like this:
date
category
sub category
amount
description
then you split date into month+year, and just year
I think each time you record an expense, you will need to record the monthly budget (at that point in time) for the associated sub-category. Then you can use this number in your monthly summaries. The only issue might be if you change a budget for a sub-category part way through the month. Then you’d need to decide which budget to use for that month.
something is wrong with your logic… what exactly you wanna achieve?
What I am interested in is long term data to get average (monthly, yearly, by category…) as precise as possible. In the short term, it helps me to closely follow up my expenses and budget which depends on selected categories.
you can just put the date… and then split that date into month + year, and just year for relations… only a month will not do it… unless is just one-year data… but you are recording year… so this is not the case…
That is already the case, each expense is tied to a month & year, and a year.
My issue here is somewhere else, it is about changing a budget without updating it in the past.
That’s exactly what I would to do, but I can’t seem to find a way. I have a relation in my monthly table that gives me all the monthly expenses, and then the monthly budget with a rollup. Which means if I later change a category budget, it changes my past monthly budget.
What you suggest is that I add a new column in my expense tab “sub-category budget” (which will be fixed in time), and then use it to have my fixed monthly budget ?
Yes that’s why, I set up a monthly budget for each category which then gives me a monthly budget depending on the selected categories. It allows me not to overspend.
Average is good, but I need budgets with it.
Here in the screen each bar is a month. For exemple I want to see in 2022, by months, my total expenses, my budget and my average monthly expenses. I can display it, but the budget is not “fixed” since it is based on a relation (sub-category budget) that is not fixed in time.
Yes, exactly.
I’ve done something very similar to that in an App that I built for a client.
The only other option would be to maintain a history of your budgets, and add a new row every time you modify a budget - with a Date, Sub-Category & Amount. But I think the other way is easier.
Yes I have taught of that, for now I try to figure out a way without creating new tabs or rows (If it’s in the end the only option i’ll do it of course)
Exactly, Imagine for 3 years my rent budget is 600, and now it goes to 800, it means that my yearly budget for rent during these past 3 years will also be updated which distorts past data.
I’m very sorry… I do not understand your budget concept. For me, the budget is the money you can spend… not the money you already spent… so the past expenses… you only sum them according to categories and time
Something I hadn’t thought of, what if there are 0 expense in a particular category for a month ? This invalidate the idea…
It’s the case for example with unfrequent expenses such as holidays. Holidays is always selected so that I have a real monthly average (not only divided by month when the category is used, but by total past month of the year), but there is not a holiday expense in every month, so i can’t get the fixed category budget like that
mmm, that’s a good point. In the App where I used this approach, there were guaranteed to be entries every month, so this wasn’t an issue.
If this is a problem, then you may have to use the second approach - Budget History Table. That will be a little more complicated - and as you pointed out it will increase the row count. But it will be doable.
If you don’t modify the budgets that often, then it may not be too bad.
haha maybe I wasn’t clear enough in my explanation, and we all have our own logic when it comes to manage expenses and budget.
I agree with you. But I also want to be able to go back in time, let’s say January 2019, see how much i spent AND what was my monthly budget. As this budget depends on selected categories for the month, it will automatically update if I change the budget of a subcategory, even if I change it in 2022.
Yes as it’s for personal expenses I won’t use every category each month.
I’ll try this option, I’ve never done it before (not in this particular case) so I’ll dig a little and come back as soon as possible, thanks for the help
