Using GoogleFinance() to track stocks on the Australian Securities Exchange (ASX)

One of the “killer applications” for cloud computing is the ability to use live feeds from the internet to produce real time live summaries or graphs. This article is about just one such function in GoogleApps spreadsheets that demonstrates this feature beautifully and highlights why traditional offline applications like Excel are on borrowed time.

GoogleFinance() is standard function in Google Spreadsheets – part of the Google Apps suite. The function gives you access to data about shares and mutual funds. Think of it as an API into the GoogleFinance web site. The cool thing about this function is that it is live – it automatically updates data every minute – even while the shares are actively trading – and the user can just sit and watch their sheet update – you don’t even need to refresh the screen.

And if you’re thinking this only works for US shares – you’d be wrong. Even though Google do not mention it anywhere in their help pages, shares on the Australian Securities Exchange (ASX) work too – although with a 20 minute delay – you just need to prefix the share code with “ASX:”. Other exchanges work too – “LON:” for the London Stock Exchange.

So if you have an Australian share portfolio and want to build a live minute-by-minute valuation/performance worksheet – read on.

The basic structure of the GoogleFinance() function is as follows:


=GoogleFinance( "[< Exchange >:]< Code >", "< Attribute >")

    1. price
      priceopen
      high
      low
      volume
      marketcap
      tradetime
      datadelay
      volumeavg
      pe
      eps
      high52
      low52
      change
      beta
      changepct
      closeyest
      shares
      currency
      name
  1. where

    < Exchange > is the host exchange of the share. For US shares this is optional. For Australia use ASX:, for London use LON:.

    < Code > is the 3 or 4 letter share code eg GOOG

    < Attribute > is one of:

So if you want to get the current (and I mean most up to date, real-time, live) price of Google shares you use the following function:

=GoogleFinance("GOOG","price")

For BHP Billiton on the ASX exchange we would use:

=GoogleFinance("ASX:BHP","price")

Or for BP (yes that oil company) we would use:

=GoogleFinance("LON:BP","price")

Note that the price is returned always in the local currency and units of the exchange. So for US shares the price is expressed in US$, on the ASX its A$ and London its in British pence. If you have shares in multiple countries, its up to you to take care of the currency conversion. But thats pretty easy given you can get the current exchange rate using the same function:

=GoogleFinance("CURRENCY:< FROMTO >","average")

So for $A to US$ conversion it would be

=GoogleFinance("CURRENCY:AUDUSD","average")

This works for other currencies too. You can easily setup a live currency summary like this:

You can then use these fields to do currency conversions where necessary in your sheet. Remember, these are live prices so will change by the minute – and any fields dependent on them.

Share indexes are easy to get too. Just use the same code that the GoogleFinance site uses. ie .DJI for the Dow Jones or .AXJO for the All Ordinaries.

Like any formula the parameters of GoogleFinance() can be substituted with cells in the spreadsheet. So if A2 contains the value ASX:ASX, you can use it as the code parameter like so

=GoogleFinance(A2,"price")

Of course the fields returned by GoogleFinance() can be used as inputs to other fields. So if you know the current price of a share and how many shares you own you can work out the live valuation by just multiplying the fields together. As the price changes during the market day, so will your valuation.

If you want to make a live portfolio worksheet you will probably be interested in the change in price today (from yesterday’s close). For this you need to use the “change” attribute.
Now all you need to know is how many shares you have and you can develop a sheet that works out your portfolio value on a minute by minute basis – and how much it has gone up or down in the day’s trading. Add your cost basis, cash balance and some conditional formatting and you have real time portfolio valuation tool – almost as good as the brokers have.

I have made a sample sheet available on google docs showing the above features of =GoogleFinance(). You can find it here.

2 comments to Using GoogleFinance() to track stocks on the Australian Securities Exchange (ASX)

  • Alistair

    I found your explanation of how to construct a formula to extract data from Google Finance by referencing a cell containing the stock symbol helpful. However, how does one obtain the book value/share, RoE and other data for a stock. These can be set in the Google Finance stock filter but I haven’t been able to find how to access these attributes anywhere on the web.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>