Today I’d like to talk about a new topic: Personal Finance. This is something really important during our whole life. Many decisions are directly related to money. Having a management tool allows you to better budget, save, and spend money over time, taking into financial risks and future life events.
This article mainly shares how I built my own data collection script using Python, which powers the data analysis. It covers the following sections:
- Downloading history from your bank
- Storing them as archives
- Merging them into a single file
- Export data to external tool (Google Sheet)
- The importance of having backup(s)
Download Account History
Before getting started to build a personal finance tool, you need to have data from your bank. I’m using BNP Paribas, their website provides an option to download the recent history (the last 30 days) as CSV file. If you’ve multiple accounts, you need to download all of them. In my case, the downloaded files look like:
E123.csv E234.csv E345.csv ...
Once downloaded, you need to rename the files so that they are aligned to your
naming convention. This is because the original name might not be
meaningful, or even it is, there will be probably differences in different
banks. Having your own convention can avoid this problem. In my case, I rename
files with username (
$USER) and account name (
This solution is scalable because it can be applied to any situation. Your situation might change over the time—you might be single for now, but in love and married later on. So it’s better to have the username involved in the naming. Then, every member of this system will have at least one accounts, therefore we need the account name.
Archiving The Downloaded Files
The second step is to archive the downloaded files. Storing them as raw data makes us save to do any transformation in the future: no matter what you’ll do and which failure will be, as far as the original files are archived, it is possible to start again from the very beginning.
In my case, I split the downloaded files by lines: each line (except the header)
is considered as a transaction, and appended to an existing monthly archive
file of the target account. If that file does not exist, it will be created by
the Python script. The archive files have their convention. They start with the
YYYY-MM), followed by dot (
.) as separator, followed by the
$USER-$ACCOUNT, and ends with suffix
2018-04.paul-A.csv 2018-04.paul-B.csv 2018-04.anne-A.csv 2018-04.anne-B.csv 2018-05.paul-A.csv 2018-05.paul-B.csv 2018-05.anne-A.csv 2018-05.anne-B.csv 2018-06.paul-A.csv 2018-06.paul-B.csv ...
Now, go back to the archive problem. How can we ensure that each line is inserted properly in the target CSV file without creating duplicate? My solution is to construct a Set, add each line of the existing file into the set, and then the new lines. Since Set does not allowed duplicate, each value is unique. Then, sort them and write to the file again. (However, this is a simplified version, in reality, I use Dictionary, because I need to store more data in each row)
Create a Merged File
Once the archive files are done, the next step is to create a merged file which
contains all the transactions. The goal is provide a single file for data
analysis. This step is very simple. The logic is almost the same as the previous
step. The only changes are to transform a French date (
DD/MM/YYYY) to ISO
YYYY-MM-DD), and add a new column for the account name.
Export Data to Google Sheet
For now, all the data collection steps are done. But, we still did not talk about any data analysis! Actually, analysis is not done in Python, but in Google Sheet. I’ll talk about it in another article. You might also want to use Microsoft Excel, or any other tool. The key point is, data processing should be separated from data analysis, which makes things much simpler, especially for automation.
For Google Sheet, it has its own preference for CSV files. If you want to import the CSV file in one click, you should change your data to make Google happy, such as:
- Use comma
,as delimiter (BNP Paribas uses semi-colon
- Use dot
.as decimal point (BNP Paribas uses comma
- Use nothing as thousands separator (BNP Paribas uses space
And the destination files should prefixed by
google.*. For example, we’ve 2
files to export to Google Sheet, respectively called
accounts.csv. Then the Google-ready ones should be:
It’s important to backup your data. For me, I choose Git as a solution. There’re many advantages of using Git, such as:
- Keep all the modification history, including author
- Show the diff when adding new data
- Transport data via HTTP protocol
- Possibility to revert when things go wrong
- Easy to mirror (multiple backups)
OK, OK, actually I use Git because I’m familiar with it. You can use whatever you want, but you must do the backup, trust me :)
Let’s summary what we discussed in the post. In order to build your own personal tool, you need to:
- Collect data from your bank (usually CSV files)
- Archive them separately (e.g. per month per account)
- Merge data into a single file
- Adapt the format for data visualization
- Backup your files
- Analyse the data (not covered in this post)
Hope you enjoy this article, see you the next time!