How to Organize Raw Data Fields for Maximum Extrapolation

[IWantYourMoneyASAP]

For the New Year, I want to be able to track my personal finances. Whilst I do find this to be easily doable as I'm currently doing it (Admittedly at a basic level), I want to be able to structure the raw data as such that I can extrapolate data in various forms so I can conjure up a dash board for personal tracking.

The data I want capturing includes When, Why, Who, Where & What. I feel as though I've cracked with this? Say I purchased a sandwich for lunch at work, When would be the date (Say 3rd December), what would be food, where would name of business and location and who would be who I'm spending this on. However, I want the where to be broken down (I want to log both the business as well the Geographical location) as well as food as a whole plus work lunch being a segment.

Also, I want the data to be such that I can convert it into charts. As an example, say my monthly income is £2'000. Of that, I want to save at least £250 a month. I want to use a gauge chart to see how much I'm spending relative to my target for the month (I can appreciate this needs to be done monthly). Also, I want to be able to summarize who I spend my money on, as well what on. The former I envision the former to be a Pie Chart, but I want the latter to be a Sunburst chart.

. . . . Also! On a personal level, I've managed to achieve an excel goal of mine which was to use conditional formatting to highlight dates on a excel calendar based on certain criteria. I want to be able to carry this over here. I've attached my document which I figured it on, but the idea is I'll get an excel calendar online, and if I assign whatever I whatever I want to track with a 3 letter code, I can use a cell to search for it, then use conditional formatting to highlight the dates. As a example, say I want to track how many times I've purchased something from Amazon, Ill input AMZ into the cell and dates which I've bought something from Amazon become highlighted on the calendar.

Now, is 1 Raw Data table able to encapsulate all of this? Is an excel calendar (For 2024) feasible on a dashboard? The design of the dashboard is something I'm working, but the raw data table needs to be such that I can incorporate all of the above. Is it a case of having many columns to accommodate the above?

[AliGW]

The absolute key to optimising data for analysis is the flat database rule: one row per record.

From what you have said already, you need to make a list of ALL cetegories by which you want to measure your data. These will be your columns (database fields - categories and sub-categories).

At the very least you will need:

Date
Type of transaction (debit or credit)
Amount
Category (e.g food, subscription, utility bill, etc.)
Sub-category (e.g. eating out, groceries, Office 365 subscription, electricity, gas, water)
Business (e.g. Nandos, Tesco, Microsoft, E.ON, EDF Energy, Anglian Water)
Location
Who this is for (e.g. in the case of uni fees, son or daughter) - bearing in mind that some fields can remain empty if inapplicable

There may be more, but you need to work out what your hierarchies of analysis would need to be.

I would be inclined to brainstorm this on a big sheet of paper first.

Hope this helps.

[IWantYourMoneyASAP]

Thanks. Whilst I do fully intend on keeping 1 row per entry, I'm beginning to think 2 or more tables may be necessary to help facilitate the chart aspect here.

I feel as though for the sake of the Sunburst chart, I might definitely need to offload the data onto a separate sheet.

As for the calendar, I feel as though it would be easier to just add a column containing the codes although I imagine I won't be able to fit the calendar onto the dashboard !