Import and Store Live Price Data in Google Sheets

Last Updated on January 26, 2023 by Mark Ursell

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.

See below for the Free Guide showing how to store live price data. There is also a Video of me demonstrating the spreadsheet.

Video

I have recorded a video demonstrating the spreadsheet. See the video below to watch this demonstration.

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)

Security

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.

Attribute

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:

=GOOGLEFINANCE($B$1,”priceopen”)
=GOOGLEFINANCE($B$1,”high”)
=GOOGLEFINANCE($B$1,”low”)
=GOOGLEFINANCE($B$1,”price”)

The spreadsheet should now look like this:

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.

Do do this we can run a script that stores the data at a regular interval. The script copies the live data and stores it in the row below. I set the script to run each evening after the market has closed. The script runs automatically and you do need to be online or have you computer on. The script is written using Google Apps Script which is based on Javascript.

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.