This article shows you how you can import live price data into your Google Sheets spreadsheet. Google Sheets spreadsheets are part of the Google Docs office suite. They are currently free to use for individuals.
Advantages of Sheets
Most of my analysis work is done using Microsoft Excel. However, in recent times I have been using Sheets extensively. I like the fact that I can access them easily through my desktop, laptop and phone.
Another big advantage of using Sheets is that they are saved online. This means that we do not have to worry about storing and backing them up on our hard drive or server. It also means that Sheets spreadsheets are permanently connected to the internet and they can run 24 hours a day.
Advantages for Traders
Sheets are a particularly useful tool for traders. Sheets offers most of the same formulas and features of the other spreadsheet packages. However, they also have some unique formulas that are highly useful for traders.
One of the most helpful formulas is =GOOGLEFINANCE()
This formula allows us to access live and historical data directly from Google Finance. Google Finance provides securities information for stock exchanges in 36 countries. It also provides data on currencies, ETFs and indices.
How to Use =GOOGLEFINANCE
The format for the formula is: =GOOGLEFINANCE(Security, Attribute)
This must be entered using the Google code for the security. This can be found by searching on Google Finance. In this example I am using the FTSE 100 Index. The code for this is UKX. Enter this code in cell B1.
There are a number of different attributes available for this function. In this example I using the Date, Open Price, High Price, Low Price and Close Price. Enter these headings in the cells B3:F3.
Next enter the formula in cell B4: =GOOGLEFINANCE(B1,”tradetime”). This brings up the date and time for the most recent trade data.
In cells C4:F4 I enter:
How to Store Live Price Data – Free Guide
The next step we can do is to store this live data to build up a database of historical prices and other information. Historical data allows traders do to all sorts of analysis and backtesting.
I have written a Free Guide showing you how to set up your Sheet to run this script. It includes all the code and a simple explanation about how to run it. To get this guide simply sign up to the Free Tradinformed Newsletter. This will keep you updated whenever a new article is published on Tradinformed.
I have recorded a video demonstrating the spreadsheet. See the video below to watch this demonstration.