Sometimes, it is easy to lose track of your personal expenses and income sources. For me, it has been a problem since I have bank accounts across Europe, US, and Mexico, and I also have income and expenses that are dealt with by cash. I figured that what I needed is to find a quick and easy way to manage my expenses and my income so that I have a better understanding of where my cash flow is going each day, week, month, or even year.
While there are many applications out there that manage your personal finances, there is nothing quite like making something to custom fit your needs:
1. Register income and expenses as they happen
2. Have immediate feedback of accumulated income and expenses
3. Visuals of trends, expenses by the vendor and detailed view of each transaction
4. Profits or losses? Am I gaining or losing money each month?
For now, I am not in need of a budget, but in the future, I would like to also develop a budget for my expenses by following a zero-based budget strategy.
All right, the requirements are more or less figured out above. Now let’s get to the development of the solution. Today, we will be using two tools to manage my personal finance.
1. Google Forms
2. Microsoft Power BI
Google Forms will be the tool to register the income and expenses as they happen and Power BI will shape the data, prepare it for reporting and visualize it in a meaningful manner online and on my phone.
While the tools to build this are different from my Get More From Your Data post and video, the analytical and development process are almost the same.
Step by step from 0 to controlling your personal finances.
1. Set up a Google Form with all the required input fields
Google Forms is just great. You can develop a form, publish it and then save the link into your iPhone/Android’s home screen so that it looks like an App. This fixes the need for registering income and expenses as they happen. The only caveat is that I have to have an internet connection at the time. But hey, we live in the 21st century.
The basics: I am setting up transaction date, transaction amount, taxes (if applicable), vendor name, currency, type, and sub-type of transaction.
Here is a nifty trick in Google Forms. I want to record Income and Expenses as the main type of transaction, but each one has its own set of sub-categories. If you create a drop-down list with “Income” and “Expenses” as options, you can select where does the form go after the selection has been made. In this case, I created two (2) additional form sections and redirected each type of transaction to each section as seen in the image above.
Then, each section has these sub-types of transactions. Section 2 of 3 is for expenses and it has the many types of expenses I might be doing a typical year of my lifestyle.
Finally, Section 3 of 3 has the sub-types for the Income type of transactions, which in this case are easier to categorize into net salary, interest income and discretionary bonus (yeah, I am hopeful).
2. Create “App” of Google Form in your phone screen
Once the Google Form is in place, I can publish it and copy the URL on my phone as an application. (Save in home screen) At this point, I can come into the “app” and record my expenses and income easily. The front-end of this process is done.
Now, recording data is one thing, making sense of it is a completely different issue. For now, there is a phone “app” that will record expenses and income. But, where? Well, if you are familiar with Google Forms, the answers usually go to a Google Spreadsheet under your account.
3. Publish your Google Spreadsheet answers to web
The interesting part here is that you can “publish” this spreadsheet online. Meaning that the spreadsheet will have its own URL and can be accessed online by whomever you give the URL to. This is important so that the spreadsheet’s URL can serve as a data source for Power BI.
By going to the spreadsheet, then “File” and “publish to the web”
Finally, select from the dropdown list to publish as a Microsoft Excel file. This will make it easy for Power BI to read the data.
Let’s take a look at what we have so far. We created a Google Form that is now appearing on our phone’s home screen as an app. Whenever we record expenses or income in this Google Form, the data will be recorded automatically in a Google Spreadsheet, which we have just published to the web, in order for it to be accessed by anyone with the hyperlink provided above. Next up, we need to access the spreadsheet through Power BI to start manipulating the data into a meaningful report!
4. Import the live Google Spreadsheet into Power BI
Open up Power BI Desktop and go to “Get Data”. This will create a pop-up with all the possible data sources that Power BI can connect to. For now, we should go to “Other”, and then “Web”.
The following screen comes up asking for a URL. In here, we just need to paste the URL created by the Google Spreadsheet when we shared it to the web. Simple and straightforward.
Now that the data is in Power BI, we can load it into the data model and start making our report!
5. Build your report with visuals
After playing around with some visuals, I came up with this particular report in Power BI. I can see the overall profits or losses I had in the time period I select. Furthermore, I can see the breakdown of the income sources by sub-type and by the vendor and the same with the expenses. I can see the trends of my transactions and I can see a conditionally formatted matrix of all the transactions in the time period.
6. Publish the report to PowerBI.com
Now that I am happy with the way the data looks, I will publish this report from my desktop app of Power BI to the online service of PowerBI.com by going to the upper-right corner of the software and clicking on “Publish”.
At this point, this report will be visible to anyone in my shared network of PowerBI.com and also to my Power BI app on my phone.
On my phone, the report will look like this:
But not only that, once the report is published, I can embed it on any website. Let’s imagine that you or your company has a website were you can have some editorial freedom (again… I am a dreamer). You could provide your audience with a sweet live report such as this one:
As I mentioned at the beginning of this post, you could improve this report exponentially. You could connect your bank statements to this PowerBI dashboard, for example. You could develop a budget per sub-type of expense and then tie KPIs to your current expenses so that you are in better control of your expenses. You could introduce family members into this tool so that everyone in your family has the Google Forms “app” on their phone in which they would record their expenses.
After all, you are the financial advisor of your bank account. Take ownership, take control of your financial behavior by looking at the numbers and correcting your habits accordingly.
Once again, thanks for your time and your patience. I hope this was useful for you and it has sparked some ideas in your mind.
Everything written here is a personal reflection and is by no means educational, financial or professional advice in any way.
Please feel free to cite and refer reliable sources in the comment section down below.