I have used a few budgeting applications to varying degrees of success. They are generally good at tracking and reporting on your spending. It’s nice to have a detailed breakdown of your spending in a typical month once you go through the ordeal of entering all the records and resolving discrepancies. Once you know your spending breakdown, you don’t get much out of this by repeating it every month. You may not even need this if you follow a buckets approach (like the one in Barefoot Investor). Once you have basic budgeting in place, the most helpful thing you can have is a future cash flow report. I tried to set this up with YNAB, but I wasn’t convinced it is worth $15/month for something I can quickly put together myself. This post will explain how I set up my basic cash flow forecaster using Google Docs, Spreadsheets, and some Apps Script glue. You can follow simple instructions and set up your own with some copy/paste. Based on my future income and commitments, I need to know my situation at any point in the next 6–12 months.
If it were my past self, I would start developing an app for it. However, my experience made me think twice about how I approach this. The wiser me would never reinvent the wheel and write code to handle recurring expenses. Therefore, the cornerstone of my plan was to use Calendar to track them. Also, since I didn’t know what kind of report I would require, the other non-negotiable requirement was to generate a report that I could manipulate and process further. A spreadsheet was the best fit for this. All that remained was creating a glue code to scrape my calendar and add individual entries in a spreadsheet, where I could create custom reports based on it.
The end result
Here is a hypothetical scenario for my future income and expenses. You can see the schedule view here, and it’s easy to see that I have different frequencies for different income/expense items. For example, I get a fortnightly salary, but my car loan and rent are paid monthly. It gets pretty complicated very quickly, knowing that I have many more things to deal with in the real world.
After running the script, all the entries are added to the “Events” sheet. The script conveniently drops a “NEW MONTH” marker because I thought it was handy for getting month-by-month reports.
Now, I can do whatever I want with these records. My other sheet is less boring. It calculates running balances and colour codes rows based on the value.
Scrolling down, it’s easy to see that I went negative on September 20.
That’s the primary value I get from this report, but I can also zoom out and see a month-by-month view. I created a chart of balances on the first day of each month. Still, I could easily get other interesting reports, like the worst position in any given month, where the negative balance I mentioned above would pop up.
How to set it up
Follow these steps to get your own setup:
- Create a new Google calendar to track expenses and incomes. Use the screenshot above to see how you should name events. Use calendar’s flexibility to configure the frequency of events. Go to calendar settings and find its ID under the “Integrate calendar” section.
- Copy https://docs.google.com/spreadsheets/d/1sfczZhRES0c38i3mkM2EFBaRQ3lCOutxlgnHId1dzMg/edit to your own account. Note the new URL.
- Head to https://script.google.com/home/my and create a new project.
- Copy https://github.com/n0rmzzz/google-apps-scripts-for-work/blob/master/budget.gs into your new script and update it with the calendar ID and the spreadsheet URL. Optionally update other configuration.
- Run the script and wait for the “Events” sheet to be populated with data from your calendar events.
- Update the first row in the “Forecast” and see how your balance changes over time.
Final words
Please leave a comment if you face an issue. If you are interested in turning this into a real product, reach out to me for a chat.
