Get all your news in one place.
100’s of premium titles.
One app.
Start reading
The Street
The Street
Business
Dominic Diongson

How to create a stock tracker with live data using Google Finance

Google Sheets enables you to monitor a selected list of publicly traded companies and to be able to track their latest share prices during the day. Google Finance allows such functionality via Google Sheets by picking stocks and defining their attributes, such as price, change, and high and low prices during the day. In this step-by step Tools tutorial, we will create a table to track a few stocks and specific attributes.

The spreadsheet version of the following tutorial can be downloaded here. Make a copy of the worksheet by selecting "Make a copy" from the drop-down in the File menu.

Related: How to track stocks from Google Finance in Google Sheets

How to create a table of stocks tracking trading activity

The first step is to create a new spreadsheet on Google Sheets by typing "sheets.new" in the URL bar of your browser. From the top row, going from left to right by column, attributes will be listed to define the requested data. In the example below, the list of attributes is as follows:

Attributes

  • Ticker: This is the exchange name and ticker symbol of the listed company. Google Finance prefers providing the stock exchange where the company trades for specificity reasons. This most likely would avoid any conflict with a stock whose symbol appears on a different exchange if only the stock symbol were entered onto the spreadsheet's field.
  • Name: This lists the name of the company.
  • Price: This provides the latest available trading price of the stock. In Google Finance, there may be a slight delay, behind real-time, from some exchanges on prices for particular securities. 
  • Change: This provides the change in price from the closing price of the last trading day.
  • Changepct: This calculates the change in price in percentage terms.
  • High: This provides the highest price the stock traded at during the day’s session.
  • Low: This provides the lowest price the stock traded at in the day’s session.
  • Currency: This shows what currency the stock is trading in.
  • Volume: This is the stock’s trading volume, or how many shares change hands from the start of the day’s trading.
  • Tradetime: This provides the last time of trading for the stock.
In this spreadsheet, attributes are listed by column.

Google Finance via Google Sheets

Understanding Google Finance’s formula

Google Finance lays out a simple formula for displaying the data under each attribute that’s listed by column and row in the sample spreadsheet. In this case with the stock table via Google Sheets, the syntax would be in this general format:

=GOOGLEFINANCE(primary attribute, secondary attribute)

The primary attribute for creating the stock table will be the ticker, and the secondary attributes will include price, change, and so on.

In the example below, Row 1 lists attributes by column for ticker, name, price, change, percent change, high and low intraday prices, trading volume, trading time, and time delay of data. 

After entering the ticker symbol in Row 2 for Column A, a formula in the adjacent column (cell B2) will instruct Google Finance to pull the data based on the cells to the left of it and above it:

=GOOGLEFINANCE($A2, B$1)

In this syntax, copying the formula from this cell to other cells of the spreadsheet will automatically fill in the data of corresponding attributes. The formula in cell B2 instructs Google Finance where to pull information. Placing the dollar symbol ($) before the column helps with the copying and pasting of the formula and populating highlighted fields. In this example, $A2 means that Google Finance will use the ticker symbol entered under Column A to load data for the corresponding attribute by column. When the cell is copied onto other cells, the attribute for the ticker is fixed and will not change.

In the example above, only the ticker symbols need to be entered, and the ticker for Tesla (TSLA) -) is in cell A2. The second part of the formula, B$1, means that the field in cell B2 will fill in the name under column B based on the ticker in cell A2. After the formula in cell B2 is entered, Google Finance will fill in the entry, which in this case is Tesla’s name. When the cell is copied onto other cells from left to right, the attribute in each column under Row 1 is fixed and will not change, but is based on the primary attribute, which is the ticker expressed as a fixed cell in column A.

Google Finance's formula takes the ticker symbol of the company's name and fills the cell under the column of the corresponding attribute.

Google Finance via Google Sheets

Note: While the above is an indirect way to pull data automatically based on the attributes set by specific cells, there is a direct way to download the data but would require writing the attributes in the syntax manually and repetitively. Using the sample spreadsheet above, for cell B2 instead of directing the formula to pull data based on other cells defined by attributes, the direct expression is: =GOOGLEFINANCE("NASDAQ:TSLA" ,"NAME"). For cell C2, the direct syntax will be =GOOGLEFINANCE("NASDAQ:TSLA" ,"PRICE"), for cell D2, it is =GOOGLEFINANCE("NASDAQ:TSLA" ,"CHANGE"), and so on.

Copying the formula from cell B2 to the cells under each corresponding column in Row 2 means that Google Finance will populate the data for name, price, change, and the other attributes listed for Tesla.

Price, percent change, and time of trading are among the fields to monitor stock trading activity.

Google Finance via Google Sheets

How to add other stocks

To gather data on other stocks, enter the ticker symbol for Apple (AAPL) -), Google’s parent company Alphabet (GOOG) -), Netflix (NFLX) -), and Amazon (AMZN) -) in the rows below Tesla under Column A. Copy and paste onto the empty cells by column, and Google Finance populates the fields.

Based on the time of trading, stocks will show the latest price, change in price as well as percent change, the highs and lows during the day's trading, and the number of shares being traded for the day.

You’re now ready to monitor the trading activity of these stocks on Google Sheets!

You can customize what you want to track in a particular stock by setting the attributes via Google Finance.

Google Finance via Google Sheets

Again, the spreadsheet version of this tutorial can be downloaded here. Make a copy of the worksheet by selecting "Make a copy" from the drop-down in the File menu.

This is just the first of many articles about how to use tools in Google Sheets. Let us know what you'd like to see next. Email us here: tools@thearenagroup.net

Sign up to read this article
Read news from 100’s of titles, curated specifically for you.
Already a member? Sign in here
Related Stories
Top stories on inkl right now
One subscription that gives you access to news from hundreds of sites
Already a member? Sign in here
Our Picks
Fourteen days free
Download the app
One app. One membership.
100+ trusted global sources.