Stock quotes using Google Sheets

  • Post category:Blog
Stock quotes using Google Sheets

Stock quotes using Google Sheets

In this article, we will see how to easily retrieve data from Google Finance into Google Sheets, in order to obtain stock quotes, without programming and only with a function.

It can be practical and interesting for many reasons:

  • You build your dashboard to monitor your investments in the stock market.
  • To study market or sectoral developments in Google Sheets.
  • To then export the data as a CSV file, and import it into a machine learning program in Python.
  • etc

Prerequisites

  • Having a Google Account (it’s free if you don’t already have one)

Let’s look at a quick example before going into a bit more detail.

1°) Open a new Google Sheets workbook

2°) Go to cell A1 for example, and enter the following formula and press ENTER:

=GOOGLEFINANCE(“MSFT”, “all”, DATE(2010,1,1), TODAY(), “DAILY”)

Don’t worry about language, Google Sheets accepts commands in English, and will then translate them into the language of the country of your workbook.

The above formula asks for Microsoft data from 1 January 2010 to the present.

Google Sheets will display “Loading…” while it fetches the data from the Google Finance site, then it will display the Microsoft stock data.

Stock quotes using Google Sheets

How it works?

You have understood that all the magic comes from the GOOGLEFINANCE function which takes care of everything.

The syntax of GOOGLEFINANCE function is:

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

with:

  • ticker – [MANDATORY] The ticker symbol. It’s mandatory to use both the exchange symbol and ticker symbol. For example, use “NASDAQ:MSFT” instead of “MSFT” only. We will see below how to obtain information about the exchange symbol and ticker symbol.
  • attributes – [ OPTIONAL – “price” by default ] The following attributes are available for historical data (see the function help for the full list)
    • open” – The opening price for the specified date(s).
    • close” – The closing price for the specified date(s).
    • high” – The high price for the specified date(s).
    • low” – The low price for the specified date(s).
    • volume” – The volume for the specified date(s).
    • all” – Return open, close, high, low and volume values
  • start_date – [ OPTIONAL ] – The start date when fetching historical data.
  • end_date | num-days – [ OPTIONAL ] – The end date when fetching historical data, or the number of days from start_date for which to return data.
  • interval – [ OPTIONAL ] – The frequency of returned data; either “DAILY” or “WEEKLY” (or alternatively be specified as 1 or 7 if you prefer to use numbers).

There are other attributes depending on whether you want to work on real-time data, historical data or mutual fund data.

For more details and to see all the possibilities, I invite you to consult the Google help for the GOOGLEFINANCE function: https://support.google.com/docs/answer/3093281?hl=en

How do I get the correct exchange symbol and ticker symbol?

As we have seen above, it is very strongly advised to provide both the exchange symbol and ticker symbol. But it’s not easy to know what these are, especially for symbols you don’t use often. But the Google Finance site will help us find the information we need.

  • Open your favourite browser (I’m using Brave 😉)
  • Go to https://www.google.com/finance/
  • For example, enter microsoft in the search to continue with our example above, but do not launch the search with the Enter key, to see the proposals of Google.
Stock quotes using Google Sheets
  • In the drop-down list, Microsoft is displayed 3 times depending on the country of the quotation market:
    • MSF : NASDAQ (US)
    • MSF : ETR (DE)
    • MSF : FRA (DE)
  • Consider Microsoft on the US NASDAQ market:
    • exchange symbol is NASDAQ
    • ticker symbol is MSFT
  • The ticker parameter must therefore contain “NASDAQ:MSFT“, taking care to remove the spaces. Specifying the stock exchange symbol helps to avoid possible errors. Because if no stock exchange symbol is specified, the GOOGLEFINANCE function will select one for you by deduction as indicated in the function help.
  • Our formula would therefore be: =GOOGLEFINANCE(“NASDAQ:MSFT”, “all”, DATE(2010,1,1), TODAY(), “DAILY”)

Conclusion

To finish this article, I would have liked to show you how to display a Japanese candlestick chart, from the recovered data (see screenshot below), but strangely, for this type of chart only, the date data (date + time) are not well recognized, and it is necessary to add a column calculated with only the date to achieve this. I think this is a small bug in the chart wizard that should be fixed in a future version. So I’ll pass on this example of a graph, a little too complicated and a gas factory in the context of this article devoted to the recovery of data and not their display.

Stock quotes using Google Sheets

Article published under CC BY licence

Stock quotes using Google Sheets
CC BY licence