The most annoying thing I do about the family balance management is to repeat the almost the same actions each month. There are a plenty of tools already available which may help on this (like Excel) but all of them has some limitation and what I want is a tool which once configured will need only a couple of click to be feed.
Spoiler: As you may see reading this post and also the other following this I’m not 100% sure I reached the target but I did my best.
Let’s start from the requirements:
- The source of the expenses are normally coming from different side: my bank account, my wife’s one, the Credi Card balance…
- The format of these files can be several, mainly csv and xlsx
- The content of these files can be very different: the number and the date format for instance or the name and the order of the columns
- What we need is that all these files combined together will be loaded in a unique Excel file on which apply some pivot to easily monitor monthly the status of all the expenses and sort it out easily if a specific entry is higher than expected.
- Finally, each month, load the new files in few clicks. Some adjustment in the mapping can be needed of course but not mandatory.
Another key part is the mapping since as is the information stored in the files may not relevant or to granular to extract meaningful information. As an example: I would like that if one of the expenses is “Pizzeria da Mario” (I’m assuming you know what a pizza is) this may be categorized like “Lunch & Dinners” and hopefully all the data containing “Pizza” as well. This means that load data is just not enough we need to transofrm the information in a process that IT guys normally call ETL (Extract Transform and Load).
As I already stated above there are a lot of tools which provide such feature (Excel among the other). Unfortunately, maybe because of my poor skills, I’m not able to find a unique tool which can do all that I want and because of this I prefer to split the actions like below:
- Extract: Powershell
- Transform: Powershell
- Load: Excel
More in details: Powershell scripts will identify the source files, reading and normalizing their contents and map them in a correct way to be correctly aggregated in a Excel file which will contain a pivot easy to manage and accessible even to your wife.
