The GOOGLEFINANCE function in Google Sheets is useful when you need to retrieve current or historical market data from Google Finance.
Table of Contents
Google Sheets has tons of advanced functions that you can use to fast-track your work. In this article, we’ll take a look at an out-of-the-box function called GOOGLEFINANCE to help you gain insights on up-to-date financial analytics.
Whenever you research a stock, you are likely to work with a spreadsheet, storing data from reputable websites such as Google Finance. To save you the trouble of manually copying and pasting market trends information, consider using the GOOGLEFINANCE function of Google Sheets.
This function lets you access real-time data straight from Google Finance. Thus, you no longer need to use highly technical scripts or programs just to keep track of your stock portfolio efficiently.
You can import a wide range of relevant stock information such as the current price, trading volume, and market capitalization into your spreadsheet with just a few configurations.
This all seems convenient, right? Now, it’s time to learn how you can utilize the GOOGLEFINANCE function in Google Sheets.
The Anatomy of the GOOGLEFINANCE Function
In order for the GOOGLEFINANCE function to work, here’s how we should write it:
- = just like other functions, the equal sign is the first character we should type in to use GOOGLEFINANCE.
- GOOGLEFINANCE is the function that we’ll need to import data from the Google Finance website.
- ticker is the first parameter. This parameter should hold both the exchange and ticker symbols of the stock you want to know. The ticker symbol usually consists of letters and/or numbers. To give you an example, the ticker of Amazon, Inc. is AMZN. If you’re not sure about the company’s ticker symbol you want to retrieve, you can always search it in Google Finance.
- attribute is a parameter that you can use to specify the type of information you would like to retrieve. You can use the following attributes taken from Google’s official documentation.
“price” The real-time price quote with up to 20 minutes delay. “priceopen” The opening price. “high” Current day’s high price. “low” Current day’s low price. “volume” Trading volume of the current day. “marketcap” Stock’s market capitalization. “tradetime” Time of the last trade. “datadelay” Shows the delay time of the real-time data. “volumeavg” This is the average daily trading volume. “pe” Price/earnings ratio. “eps” Earnings per share. “high52” The highest price over the past 52 weeks. “low52” The lowest price over the past 52 weeks. “change” The change in price since the end of the previous day’s trading. “beta” The beta value. “changepct” The percentage change in price since the end of the previous day’s trading. “closeyest” The closing price of the previous day. “shares” The number of outstanding shares. “currency” The currency in which the stock is priced in.
For retrieving historical data, you can use the following attributes:
Attribute Description “open” The opening price of a specific date. “close” The closing price of a specific date. “high” The high price of a specific date. “low” The low price of a specific date. “volume” The trading volume of a specific date. “all” Retrieves all the attributes above.
You can also retrieve mutual fund data with these attributes:
“closeyest” The closing price of the previous day. “date” The net asset value’s reported date. “returnytd” The year-to-date return. “netassets” The net assets. “change” Change between the most recent and its previously reported net asset value. “changepct” The percentage change in the net asset value. “yieldpct” The distribution yield. “returnday” One-day total return. “return1” One-week total return. “return4” Four-week total return. “return13” Thirteen-week total return. “return52” Annual total return. “return156” 3-year total return. “return260” 5-year total return. “incomedividend” The most recent cash distribution amount. “incomedividenddate” The most recent cash distribution date. “capitalgain” The most recent capital gain distribution’s amount. “morningstarrating” The Morningstar “star” rating. “expenseratio” The fund’s expense ratio.
- start_date is an optional parameter that can be used to specify the start date of the historical data you wish to retrieve.
- end_date|num_days is also an optional parameter that holds either the end date or the number of days from the start date of the historical data you want to retrieve.
- interval is another optional parameter you can use to define the frequency of returned data. Specify in this parameter if you want to retrieve “DAILY” or “WEEKLY”. Alternatively, you can use 1 or 7.
A Real Example of Using GOOGLEFINANCE Function
Let’s understand further the GOOGLEFINANCE function using the example below.
The image above shows the current market data of Amazon taken from the Google Finance website.
For instance, we want to retrieve the price and trading volume and the high and low prices of the current day. Obviously, you can always fetch these attributes by scanning through all the data fields and graphs on the website. However, this can be cumbersome at times.
Google Sheets offer an easier and more efficient way of retrieving the required data. In the image below, we utilized GOOGLEFINANCE to retrieve the four attributes automatically.
In each of our formulas, we only defined two parameters—ticker and attribute. The second parameter holds our attribute, so you may notice that it’s the only parameter that varies. Since we only want to retrieve Amazon’s information, we used the same ticker for all formulas, NASDAQ:AMZN.
You see how easy that is? Now, make a copy of the example data and try the GOOGLEFINANCE function on your own.
How to Use GOOGLEFINANCE Function in Google Sheets
- Upon opening the copy of the example spreadsheet, click Sheet2 to display the following data.
The purpose of the spreadsheet is to record the weekly highest and lowest prices of the Amazon market from January to March 2021. Given the parameters, let’s use the GOOGLEFINANCE function to display the relevant data next to the table.
- We’ll retrieve the high price first. In cell D3, type in the equal sign ‘=’ followed by ‘GOOGLEFINANCE(’ to start the function.
- Our first parameter should contain the ticker of AMAZON, which is NASDAQ:AMZN. Since it’s already provided in cell B4, we’ll just use its cell reference. Type ‘B4‘ in our ticker parameter.
- The next parameter we should indicate is the attribute. We need to retrieve the high price, and the appropriate attribute for this is ‘high‘. Let’s specify it as our attribute parameter.
- The values for our last three parameters are already provided in the spreadsheet. Therefore, it should be easy now to complete the function. Specify the start_date, end_date, and interval parameters by indicating ‘B5‘, ‘B6‘, and ‘B7‘, respectively.
- When all parameters have been specified, type the close parenthesis ‘)‘ and press the Enter key on your keyboard. This will return a result similar to the data below.
- Good work! You just retrieved the records we need. At this point, try to display Amazon’s previous low price records from January to March. Type in your formula on cell F3.
If your formula is correct, you should have this final output.
There you go! You just learned another powerful function called GOOGLEFINANCE. Use it along with other Google Sheets formulas to make your work more efficient.