As discussed in my previous post on why you should use Google Sheets for personal finances, I will outline how to use it in this post. I will be referring to my Google Sheet and how it is set up for illustration purposes. However, I recommend you take the time to prepare your own Google Sheet instead of using templates that are available online. By building your own Google Sheet one cell at a time, it will give you an insight into your personal finances since you have to work out why the information is in the Google Sheet and how best to illustrate it.
How to use Google Sheets?
Basically, it works like an online Excel spreadsheet but its functions are limited. You won’t get the wide range of functions like Microsoft Excel but you will have enough for the purposes of your personal finances.
Why not Microsoft Excel?
Interesting question I have been asked quite a bit. You definitely can use a spreadsheet on Microsoft Excel to track your personal finances. The main reason I prefer to use Google Sheets is that it is better for online work as I pull data (e.g. prices from Yahoo Finance and Google Finance) into the sheet from other websites. The other reason is that Google Sheets is better for collaboration since my wife and I can work on the sheet simultaneously and update it online from anywhere.
Assets
The first tab in my Google Sheet is for my assets. I had mentioned in an earlier post about the importance of asset allocation. To be able to push funds into your various assets to reach your target asset allocation, you must first be able to track the value of the assets. I will describe the basic components of my Assets tab here to help you along. My wife and I have separate Assets tab which we combine into a total Assets tab.
Singapore Cash
- Emergency Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash
- Spending Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash
- Investment Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash
The bullet point represents a different row and the hyphen represents a different column. I group my cash into 3 categories per my asset allocation and each category serves a different purpose. You can have more than the 3 categories but they are sufficient for me. Each category of cash can be held in more than 1 bank account but the key is to separate the different categories of cash. I usually update the figures at the end of each week to see how my cash levels are from income earned and expenses incurred for the week. I also try to add hyperlinks to the internet banking websites of the various bank accounts so I can click into them and login to access my online banking information.
Overseas Cash
- Overseas Funds (Bank Account Name) – $ Amount – % Proportion of Total Cash
Yes, you can split this into the 3 categories if you want. For me, I prefer to see it in one line because of the nature of these funds. I hold cash in Australia because I studied, worked and lived in Melbourne and Sydney. It’s a country I return to often and makes sense for me to leave some of my savings behind when I left Australia and returned to Singapore. It functions as Spending and Emergency Funds.
Since the funds are in A$, I use this formula to pull the AUD:SGD exchange rate from Google Finance to convert the A$ into S$:
=GoogleFinance(“Currency:AUDSGD”)
Since I am now living and working in Singapore, it makes sense for me to do this foreign currency conversion on my Google Sheet since I am viewing my assets from a S$ perspective.
Singapore Stocks
- Name of Share (Ticker Code) – Number of Shares – $ Sell Price or $ Last Traded Price – $ Value – % Proportion of Total Stocks Value
I prefer to have an overview of the value of the stocks and a more real-time update of the share portfolio in my Google Sheet. When I first set up my Google Sheet, I was updating the price of each share manually at the end of the week. You can imagine the amount of work that went into this. Subsequently, I realised you could use a formula to pull price data into the sheet. Despite working in accounting in an office and being relatively proficient in Microsoft Excel, I still have no idea to this day why it took me such a long time to realise there had to be a formula to replicate what I was doing manually!
At first, using OCBC Bank (Ticker Code on SGX is O39) as an example, I used this formula: =GoogleFinance(“SGX:O39″,”price”)
However, this formula no longer works because Google Finance does not support SGX anymore. Nevertheless, pulling last traded price data from Google Finance on Google Sheets works well and I use it for the other stock exchanges. I must point out that the search for another formula to pull Singapore share price data from another website was long and frustrating. I had to do quite a bit of research and tested multiple formulae I found from different websites before finding one that worked reasonably well.
To save you time, I will write out the formula I use now to pull Singapore share price data from Yahoo Finance:
=value(substitute(Index(ImportHTML(ʺhttps://sg.finance.yahoo.com/q?s=O39.SIʺ,ʺtableʺ,2), 4,2),ʺ*ʺ,ʺʺ))
Overseas Stocks
- Name of Share (Ticker Code) – Number of Shares – $ Sell Price or $ Last Traded Price – $ Value – % Proportion of Total Stocks Value
Again, you can use the Google Finance currency formula to do the foreign currency conversion. It’s likely that you can also use the Google Finance formula to retrieve last traded price data for overseas stocks.
Using ANZ Bank (Ticker Code on ASX is ANZ) as an example:
=GoogleFinance(“ASX:ANZ”,”price”)
There you have it – how I construct the basic cash and stocks components of my Assets tab. I will cover the other components in another post because there is already quite a bit of detail in this post. In any case, you can use the above as a starting point and develop your own template with additional information. What’s important is that it will become your own Assets tab and you will be more inclined to monitor and update it regularly!