Project Motivation
There are websites like mint that access your personal banking accounts and fetch all the details to show you some charts and statistics. The caveat, you trust third party with all your personal finance data. Some may feel the risk is not worth it.
This project replicates the this functionality to analyze the credit card transactions.
Project Description
The whole project is done in GCP. It uses Cloud Storage Buckets, Cloud Functions, BigQuery and Data Studio.
Here is how the system works -
Step 1: Download Credit Card transactions from your bank (e.g. Chase) in csv format.
Step 2: Upload the csv file to your personal GCP bucket.
Step 3: Cloud Function detects that the file has been uploaded to the bucket and runs automatically. This function parses the data and loads it to BigQuery database.
Step 4: Data Studio dashboard updates every 15 minutes and gives you fresh look at your personal spending details. Bonus: Data Studio also sends you (and your wife :) ) email on specific date every month showing key statistics.
Implementation
Note: This assumes some familiarity with GCP
Step 1: Create Cloud Storage Bucket for this project.
Use versioning, multi-regional settings if you want.
Step 2: Create BigQuery dataset and table
Note: You can create relevant columns. The columns shown here are appropriate for Chase banks credit card transaction csv file format. CSV file of some banks like Bank of America has very few columns.
Step 3: Write Cloud Function
This function will be triggered as soon as some file gets uploaded to bucket above. The function will read the file, parse the contents and load the data to BigQuery table created above. The details of the bucket and table would be used in the function. Whenever a new csv file is uploaded, it’s contents are ‘appended’ to the existing table. The code is as follows
Step 4: Upload the csv file to the Bucket and let Cloud Function run
Once you upload the csv file to the bucket, the cloud function should run automatically and should load the transaction data to the BigQuery table.
Note that this approach is somewhere between ETL and ELT. the raw data is loaded without much cleanup. All the data insights are drawn by writing queries on BigQuery table.
Drag and drop the csv file to bucket
The loaded BigQuery table should look like this. This would confirm that the data has been loaded correctly
Step 5: Design the Data Studio Dashboard and view the data
This is where you can do all the magic of analysis and display. Connecting to BigQuery table is really easy. Once you connect, all the charts can be created by dragging and dropping. Data Studio even has various themes and layouts available for some inspiration. The data freshness can be set to as low as 15 minutes.
I normally upload the transaction csv files once a month. The cloud function runs once a month, the BigQuery table size is typically tiny compared to what google offers so cost of all this comes down to zero.
Improvements
The Cloud Function can be easily expanded to work on multiple credit card csv files from same and different banks.
Bank transactions can also be added.
Since the data is in BigQuery, BigQuery ML functionality can also be invoked.
The data can be sliced in various ways and the fields on Design Studio Dashboard can be made as complex as one wants.
Get the source code
For original source code visit