How to track stocks in a spreadsheet with Google Finance

Tracking stock prices is easy using Google Finance.

Your Google Finance watchlist provides real-time stock market quotes, plus data and analytics about international markets and finance. It's a treasure trove of information, available for free to anyone. In addition, you can start to gather, track, and customize all that data using Google Sheets. Just follow these step-by-step instructions to pull stock data via Google Finance. 

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.

What is Google Finance?

Google Finance is a tool created by Google’s parent company Alphabet Inc. (GOOG). It allows Google users to download financial data about stocks, bonds, mutual funds, indexes, currency, and cryptocurrency exchange rates onto Google Sheets.

Google pulls its data from various sources, including exchanges in the U.S. and globally, and content providers such as S&P Capital IQ. Google Finance has fixed fields for label attributes that download specific types of data, like price (which is the latest price available), close, volume, and market capitalization.

Inputting specific attributes onto Sheets allows you to create a customized spreadsheet. You can track a publicly traded company’s stock price over a period of days, weeks, months, or years.

Downloading stock prices can be easily done by using Google Finance and Google Sheets. 

Shutterstock

How to create a spreadsheet tracking a stock using Google Sheets

You must have a Google account to access Google Sheets. Type sheets.new into your browser or create a new spreadsheet in your Google account. Google has a formula that allows you to download data from your Google Finance watchlist onto a spreadsheet, and this can be accomplished by following these steps. These examples use Elon Musk's electric vehicle company Tesla TSLA.

Understanding Google Finance’s formula

Google Finance’s formula for pulling up historical data on a particular stock is set up as an equation with different fields. Its syntax follows:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Ticker

This field must include the exchange followed by a colon and ticker symbol (no spaces).

Attribute

This field is a particular entry of a company’s stock action. It can range from price to close to 52-week high and low.

Start_date

This represents the first day of the attribute.

End_date or number of days

This represents the last day of the attribute. Number of days including the start date can be entered in lieu of the end date.

Interval

This signifies the frequency of the attribute. Google Finance only allows daily or weekly results, none (as of now) on a monthly or yearly basis.

How to download your portfolio using Google Finance

There are two ways to download the data: a direct way by inputting the fields directly into the formula, and an indirect way in which defined cells populate the formula. 

Direct way

The direct way is to input the entries in the fields of the formula, in either the formula bar or in the cell. For example, to get the closing stock prices of Tesla on a daily basis in 2023, the formula would appear as such:

=GOOGLEFINANCE("nasdaq:tsla", "close", "1/1/2023", "12/31/2023", "daily")

The result lists the historical data for the selected date range. Using the first day and last day of the calendar year automatically enters the first day of trading and the latest or last day of trading.

Google recommends using the symbol of an exchange preceding a company's ticker symbol, most likely to avoid conflicting with the use of a ticker symbol on another exchange in its list of available exchange data. However, only entering the ticker symbol will work in the formula.

The direct method involves entering the attribute and other fields in Google Finance's formula into a cell on Google Sheets.

Google Sheets

After entering the information in the formula, the historical price data are downloaded onto the spreadsheet.

Google Sheets

Indirect way

The indirect way is to create a table on Google Sheets and replace the fields in the formulas with cells that correspond to those fields, as shown in the example below. Column A will describe what fields need to be filled in Column B. In a separate cell, the formula is filled out with the fields corresponding to those cells, resulting in the historical data for the selected date range.

For example, using the same formula for pulling up the historical table, it would be written as such:

=GOOGLEFINANCE(B1,B2,B3,B4,B5)

The indirect method means defining the fields and entering the information separately onto cells in Google Sheets and then entering the fields that correspond to specific cells into the Google Finance formula.

Google Sheets

After completing the formula, the historical data is downloaded onto the spreadsheet.

Google Sheets

Again, the Google spreadsheet 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