How to Track Stocks in Google Spreadsheets
Who doesn’t love a large and complicated spreadsheet? But for most engineers I’m sure we all think spreadsheets are awesome and just works for what you need to do with it.
Now, a powerful and hidden feature that I’m sure most people aren’t aware of is the ability to track stocks in spreadsheets. As I primarily work with ASX listed companies, I am able to open the spreadsheets every night and see updated prices.
Here are a list of the functions I use.
=GOOGLEFINANCE(CONCATENATE(A3,”:”, B3),”price”)
=GOOGLEFINANCE(CONCATENATE(A3,”:”, B3),”changepct”)
= GOOGLEFINANCE(CONCATENATE(A3,”:”, B3),”volumeavg”)
Where A3, is the column I've defined the exchange to be a constant "ASX" and B3 is the company Ticker, such as DOW for Downer. For more information refer to the official documentation below.
A downside before using it however is that it is unable to correctly list the market prices of some companies to 3 decimal places. So companies priced at 0.025 will show up at 0.03, though to be fair 90% of people don’t trade stocks that cheap.
Secondly, it is definitely NOT designed to update the prices real-time, therefore it can't really be designed as a sort of real-time stock screener.
So what's the point of using the google finance function. Well it supports simple research directly in the spreadsheet. For example it's able to obtain Volume, PE and EPS information.
Furthermore, it is also able to list out historical prices for a date range so you’ll be able to calculate variance and return on risk for specific companies. It definitely helps for those utilising modern portfolio theory. Though to be fair, if that’s the case you should really be using something like R.
However for those who want something simple to track or utilise for paper trade, google finance is a perfect fit for the average retail trader.
