Skip to main content

 

How to Use GOOGLEFINANCE Function in Google Sheets?

Luciano Marzoni

Are you researching investment opportunities or stocks? If so, you’re going to find the GOOGLEFINANCE function very useful. This tool connects you with Google Finance, a platform that has become extremely popular with analysts who want to collect information on stocks, Exchange Traded Funds (ETFs), and financial news. The function allows you to bring all the data you want from Google Finance onto your spreadsheet and perform your analysis with minimal error and maximum efficiency.

By the end of this article, you’ll know how to use the GOOGLEFINANCE function by understanding its syntax and parameters, especially the attribute parameter which allows you to retrieve real-time and historical data on stocks. Lastly, you’ll see how more advanced attributes can help you analyze mutual funds and current exchange trends.

GOOGLEFINANCE Syntax in Google Sheets

GOOGLEFINANCE Syntax in Google Sheet

The GOOGLEFINANCE syntax, as explained in Google’s Docs Editors Help, is as follows:

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

It is important to understand the role of each parameter in the syntax as this will help you avoid formula parse errors.

  • ticker: This parameter relates to publicly traded securities which may contain letters, numbers, or both. For the company, Apple Inc., the ticker is “AAPL”, whereas for 888 Holdings PLC the ticker is a number, “888”. To avoid confusion, the trade exchange for the given stock can also be specified: “NASDAQ:AAPL” for Apple Inc. and “LON:888” for 888 Holdings PLC. Keep in mind that in all cases it is important to place the ticker and the trade exchange information between quotation marks.
  • attribute: An optional parameter that can retrieve various types of information, amongst which are price and earnings per share. Similar to the GOOGLEFINANCE ticker parameter, the attribute is read as text so it needs to be placed between quotation marks (“”). If no attribute is given, Google Sheets retrieves the price information by default.

However, you should know that this parameter becomes mandatory when you provide a date value.

GOOGLEFINANCE Attribute unspecified

If we input any date-related parameter, we will receive an #N/A message.

GOOGLEFINANCE Attribute unspecified + Date specified
  • start_date: Also optional, this parameter can retrieve historical data from a given date onwards. If no value is indicated, the information retrieved will be from that same day. If a value is given for start_date but not num_days|end_date parameter, it will only retrieve data for that day.
  • num_days|end_date: This optional parameter fetches stock data for a specific time frame. It is also important to note that dates are treated as noon UTC time. Any exchange that closes before this time will be shifted by one day.
  • interval: This final and optional parameter determines a weekly or daily frequency by using the two following inputs, “WEEKLY” and “DAILY”.
How To Import Yahoo Finance Data Into Google Sheets

How To Import Yahoo Finance Data Into Google Sheets

You want to analyze Yahoo Finance data in Google Sheets but are still using copy-paste? Here’s how to import Yahoo Finance data into Google Sheets instead.

READ MORE →

How to use the GOOGLEFINANCE Attributes in Google Sheets?

Retrieve Real-Time Data on Google Finance Stocks

GOOGLEFINANCE Attributes for Real-Time Stock Data

For example, to access the price quote for Apple Inc., the formula would be:

=GOOGLEFINANCE(“NASDAQ:AAPL”)

We would obtain 175.65.

But what if you want to retrieve real-time of all the above attributes? You can do this by simply pasting this table onto your Google Sheet without quotation marks. Then, you can adapt the formulas by referring to the cell and not the value itself:

GOOGLEFINANCE All Real-time Data Attributes

Retrieve Historical Data on Google Finance Stocks

These attributes allow you to retrieve data for deeper analysis on Google Finance stocks.

GOOGLEFINANCE Attributes for Historical Stock Data

As in the previous exercise, include the value for each parameter in the cell and then simply refer to it in the GOOGLEFINANCE formula. Don’t forget to include all remaining parameter values since we are interested in a specific time range.

For instance, to retrieve all the historical data attributes for the most recent quarter (Q3) for Apple Inc. this year (2021) we would use the following formula:

GOOGLEFINANCE All Historical Data Attributes

If you get a #REF! error, it’s probably because of how the information is distributed in Google Sheets. The program is telling us that it needs more space to display all the information since we asked for an interval of 7 days. In this case, we use a numeric value instead of “WEEKLY”.

You will soon see that it takes only a matter of seconds to receive all the necessary data to start your comparative analysis.

Popular posts from this blog

  How to Lock Cells in Google Sheets? (Rows/Columns/Sheets) Luciano Marzoni Do you ever get frustrated when you reopen a spreadsheet to find that your important data has been changed? Not only can it lead to inaccurate data or loss of critical information, but it can also cause repercussions for the rest of your Google Sheets file. Learning how to lock in Google Sheets is a simple solution to gain complete control over certain data, avoid human error, and protect important formulae vital to your spreadsheet. This article will first show you how to lock cells, lock rows, lock columns, and even lock sheets in Google Sheets so that only you can edit them. It will then demonstrate how you can give editing permission to other users of your choice or leave a warning. Finally, it will show you how to unlock anything you have locked. Excel also allows you to lock and unlock your work in spreadsheets, although this   technique is different . How to lock cells in Google Sheets? The foll...
Read more
  នាព្រឹកថ្ងៃសុក្រ ទី២៧ ខែធ្នូ ឆ្នាំ២០២៤ លោក សាន សុខិដា អនុប្រធានក្រុមការងាររាជរដ្ឋាភិបាលចុះមូលដ្ឋានឃុំស៊ីធរតំណាងដ៏ខ្ពង់ខ្ពស់ឯកឧត្តមបណ្ឌិត វង្ស ប៊ុនឥន្ទ្រាវុធ ប្រធានក្រុមការងាររាជរដ្ឋាភិបាលចុះមូលដ្ឋានឃុំវិហារសួគ៌ ឃុំស៊ីធរ ឃុំសន្លុង និងឃុំកំពង់ចំលង នៃស្រុកខ្សាច់កណ្តាល បានចូលរួមកម្មវិធីចែកវិញ្ញាបនបត្រសម្គាល់ម្ចាស់អចលនវត្ថុ ស្ថិតនៅភូមិស៊ីធរកើត ភូមិព្រៃបាំង ភូមិមេបាន់ ឃុំស៊ីធរ ស្រុកខ្សាច់កណ្តាល ខេត្តកណ្តាល ក្រោមអធិបតីភាព ឯកឧត្តម នូ សាខន អនុប្រធានក្រុមប្រឹក្សាខេត្តកណ្តាល និង ឯកឧត្តម គួច ចំរើន អភិបាលនៃគណៈអភិបាលខេត្តកណ្តាល។
Read more
  Conclusion The use of the GOOGLEFINANCE function is not recommended for real-time investment decisions, given its 20-minute update delay. Nonetheless, Google Sheet users have access to financial data that can be used to keep financial reports or up-to-date calculations with live currency and stock information. Understanding the role of each parameter in the GOOGLEFINANCE syntax will allow you to unlock the full potential of this formula in your financial analyses. We’ve illustrated examples with the most common attributes to track real-time data on stocks as well as retrieve historical data for a given time period. Furthermore, the attributes related to mutual funds and currency exchange can provide more valuable information for investors.  Overall, the GOOGLEFINANCE function is another example of a highly useful free tool that is accessible to everyone.
Read more