Finance Smiths

Personal finance apprentices-in-training

Google Sheets – Assets – Cash and Stocks

by //

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),ʺ*ʺ,ʺʺ))

One of the readers (Steven) commented that when you copy the formula into the Google Sheet, it doesn’t work. I tried this and it really doesn’t work. Yet somehow, the same formula is working on my Google Sheet. I was able to rectify this by retyping or replacing the quotation marks ” ” of the copied and pasted formula in the Google Sheet. This means that when you copy the formula into the Google Sheet, you have to retype or replace the quotation marks ” ” using your keyboard to activate the formula. It should work after that!
Notice the number “4” in the formula above? This pulls the sell price data of the Singapore share from Yahoo Finance. If you change it to “1”, that’s the previous closing price data. If you change it to “2”, that’s the opening price data. If you change it to “3”, that’s the buy price data. This formula updates the price of the Singapore share automatically and gives you a more real-time feed of the value of your stocks. Because I use the number “4”, which pulls sell price data, it sometimes comes up with an error (#N/A or #REF!) because it’s live data that changes and this fluctuation can cause problems when trying to retrieve the price from Yahoo Finance. Nevertheless, this does not happen often and it usually goes away by itself after a while, which means you just need to click into your Google Sheet at a later time for an error (#N/A or #REF!)-free update.

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”)

This formula is more stable than pulling price data from Yahoo Finance and is almost always error (#N/A or #REF!)-free.

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!

Comments

  1. thanks TFS! was so looking forward to this post!

    " If you change it to "1", that's the previous closing price data. If you change it to "2", that's the opening price data. If you change it to "3", that's the buy price data. This formula updates the price of the Singapore share automatically and gives you a more real-time feed of the value of your stocks. Because I use the number "4", which pulls sell price data,"

    quick question. i understand you have a buy/sell price, but is there a "number" for last price?

  2. No worries! I have yet to work out what is the "number" for last price or whether it requires a different formula. Since the sell price is usually quite close to the last price, I didn't explore this any further. If I do figure it out eventually, I should update it on my blog!

  3. Cant get this to work:

    =value(substitute(Index(ImportHTML(ʺhttp://finance.yahoo.com/q?s=O39.SIʺ,ʺtableʺ,2), 4,2),ʺ*ʺ,ʺʺ))

    it gives #ERROR! (Formula parse error))

  4. Hi Steven,

    The issue is with the quotation marks ""

    When you copy the formula into Google Sheets, retype or replace the quotation marks "" of the formula again.

    That should activate the formula and make it work. I should make a note about this in my post!

    Cheers,
    TFS

  6. TFS,
    saw it this morning, when i played with it, but someone beat me to it..anyway, thanks! works like a charm!

    since it only works in GS, i am contemplating to move my excel to GS. but i am a bit reluctant..as there are a lot ref and links among my excel sheets. not sure if it will screw it up if when i shift it over..
    hmm, any words of wisdom?

  8. No worries!

    I presume you are referring to the Upload function to move your Microsoft Excel spreadsheet to Google Sheet. To be honest, I have never tried such a shift. Not sure if any of the other readers have attempted something like that before and can advise on this.

    Ultimately, you will have to weigh the benefits and cons of maintaining the Microsoft Excel spreadsheet or Google Sheet. Maybe try shifting some of it across to see if it works better for you. If you really prefer Google Sheet after the trial run, you can try uploading the Microsoft Excel spreadsheet into Google Sheet and fix any errors with the references and links. All the best!

    Cheers,
    TFS