Using Google Sheets as Book Keeping Tools
I tried to track my expanse countless time before, using many apps, and its all comes down to the same problem: I’m too lazy to updates the records. Some apps provides integrations that connects to your bank to populate the records, but for me, it’s just a small portion of all my expense. A largest portion is expense using digital wallet (like gopay or ovo) that do not have integrations yet to the book-keeping apps. Putting all the transaction one-by-one in a really repetitive manner is a huge turn-off for me.
Another things is sometimes it’s troublesome that if you want to introduce a new account, you have to go and create the account first, after that you can use it in transactions. When you are inserting a transaction that doesn’t have a corresponding account, this will pause the insertion process, further making it “unappealing” to already cumbersome process.
Why Google Sheets?
I’m using google sheets because I can’t update the records per transaction basis. Most of the time, it will be batch update spanning few days up to a month inserting tens if not hundreds of records. Form based input (like book-keeping apps usually have) is not optimized for this kind of input, rather spreadsheet application is a wonderful tools for batch input, especially if you are familiar with it.
Another thing is gsheet is very extensible. There is google apps script that can extend the processing beyond spreadsheet formula capability, even accessing other google service like Gmail, that stores email receipt (although I don’t use this yet, but it’s a possibility).
Creating format mapping from a table to another format is a simple things if you experienced with spreadsheet applications, and most of financial services provide table based account statement, that can be copy-pasted directly into google sheet. Transform that and we don’t have to put rows after rows manually.
How Does it Works?
I mainly create 2 sheets: transactions and budget. All I have to do is just populate these 2 sheets, batch putting the transactions whenever I have the chance, and setting up budget every start of the month. It’s really simple works that often does not take more than a few minutes.
The columns are simple, it supports 3 types of transaction: in for income, trf for internal transfer between accounts, and out for outgoing expense. Only out type transaction will affect the budget. And because it’s a spreadsheet, you can put any value to the from/to column, and an account with that name will be generated by a formula, easy!
There’s also budget sheet, it’s kind of commitment you make at the start of the month, to just spend this much on food for example. It’s also supports carry over (positive and negative) to the next month.
Split Accounts
There is a feature called split-account (or sub-account) that makes a sub account from your primary account. For example
There is a transfer transaction from bankA-myfriend to bankA-myfriend@saving. Here, the “@saving” postfix marks that is a sub account saving under the account bankA-myfriend. In the real world, the account of myfriend in bankA is still just one account, but here, I want to split it into a saving account, and common account.
There is an autogenerated sheet that breaks down the accounts and its sub-account so you can see the detailed view. Another autogenerated sheet called account balance is provided for reconciliation purpose to make it easy to compare with your real-world account (bank statement, credit card statement, etc.)
Here it’s easier to see that the total balance of bankA-myfriend rather than the split accounts.
Afterwords
One of the big effect for me after using this is I always look at the balance of certain budget, before I make an expensive purchase of something. Let say I’m craving an expensive food, I look at the balance for food budget, and how long until the end of the month, to justify if I should buy that food, or opt for a cheaper options.
The other is with budgeting, I can set a target for certain account. Let say if I want to buy PS5 in the next 3 months, I will create a budget called PS5 saving and at the start of the month, all other budget will have to recalculated to accommodate for this budget.
If you are interested in this, please take a look at this template. You can make a copy and start using it too!
Any questions and suggestion please leave a comment down below.
Thank you for reading!
