Google Finance: How to Track Stocks in Google Sheets

Google Finance: How to Track Stocks in Google Sheets
Reading Time: 4 minutes
Google Finance

Learn how to track Stocks using Google Finance in Google Sheets and examples of its usage. We will also see some other functionalities possible through the same function.

How Google Finance Works

Google Finance is a website focusing on business news and financial information hosted by Google.

The GOOGLEFINANCE function in google sheets helps you fetch real-time financial data along with historical securities data from Google Finance. Google Finance is a web service by Google, focused on business news and financial information.

If you want to gather data about any stock, usually you would’ve gone to another website to copy and then paste it back to your sheet. Or you could have used some third-party service to achieve the same. These methods are cumbersome, unreliable, and don’t give you real-time information.

You can instead use the GOOGLEFINANCE function inbuilt.

Google Finance: Syntax

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

  • GOOGLEFINANCE: function name, fetches current or historical security information
  • Ticker: ticker symbol of security, mandatory to use both exchange symbol and ticker symbol to avoid discrepancies. For Eg. use “NSE:TCS”.
  • Attribute: optional parameter(‘set to ‘price’ by default’), type of information GOOGLEFINANCE should fetch. There are a number of options available check them at (insert doc link here).
  • Start_date: optional parameter, enter the start date of the search whilst searching for historical data
  • end_date|num_days: optional parameter, end_date for the end date of the historical data search, num_days for the number of days from the start day you want to calculate. If you leave this field blank but fill the start date then it will return the data for the start date.
  • Interval: specifies the frequency of the output data, two possible values “weekly” and “daily” ( or ‘7’ and 1 respectively)

Real-Time Data Usage

Let’s first see how to track stocks using Google Finance. For example here, I have used Tesla(NASDAQ: TSLA) to experiment with this function.

Just a quick google for the same would give me this.

NASDAQ:TSLA on some given day
Figure 1: NASDAQ:TSLA on some given day

To return the current price for the stock for this example just use GOOGLEFINANC E(NASDAQ:TSLA)

Real-time stock price using Google Finance
Figure 2: real-time stock price

This is in line with the data mentioned above. Some of the other basic attributes used are listed below.

Real-time data optional attributes using Google Finance
Figure 3: real-time data optional attributes

Rather than typing “NASDAQ:TSLA” again and again I have just referenced the cell’s value from cell B1, and referenced attributes value from the first column.

Historical Data Usage

The function also provides the historical data based on the start_date and the num_days/end_date given. The attributes that can be used are as follows.

  • open: Opening price for the specified date(s)
  • close: Closing price for the specified date(s)
  • high: High price for the specified date(s)
  • low: Low price for the specified date(s)
  • volume: Volume traded for the specified  date(s)
  • all: all of the above

A sample use for the same is given 

Historical data usage using Google Finance
Figure 4: Historical data usage

There is a possibility that you might see an error like “#REF!”. This happens when you have not left enough space for the data to fill in and it is clashing with some other data in another cell.

Mutual fund data

It is as easy as calculating the data for different stocks. Also, it comes with a different set of attributes that are relevant for the same.

Mutual Fund data usage using Google Finance
Figure 5: Mutual Fund data usage

Currency Conversion

You can also use this function to convert currency on a real-time basis using the following syntax.

GOOGLEFINANCE(“CURRENCY:”&”curr1”&”curr2”)

real-time currency conversion using Google Finance
Figure 6: real-time currency conversion

Alternatively, you can also create a table for the currency exchanges over a period of time

Currency conversion table over a period of time
Figure 7: Currency conversion table over a period of time

Conclusion

If you’re a trader or work closely with money this way is far more efficient and better than conventionally copy-pasting all the securities information. Hope this article was helpful!

See Also

Introduction to DATE function: Converts Year, Month and Day into Date

Google Sheets: Candlestick Charts | An Easy Guide: To create Candlestick Charts in Google sheets

2 responses to “Google Finance: How to Track Stocks in Google Sheets”

Leave a Reply