Google Sheets isn’t just a copy of excel anymore, it has some exciting extra functionalities Excel just hasn’t. One of them are Google Specific Formulas: Th GoogleFinance Function is one of them. This function allows you to import both real-time and historical, financial and currency market data straight into Google Sheets. This data comes from Google Finance.
Usage
To use the GoogleFinance function in Google Sheets, select the cell and type in the following formula.
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
Examples could be:
Example 1: The current price
=GOOGLEFINANCE(“GOOG”, “price”)
Example 2: The price of the last 30 days:
=GOOGLEFINANCE(“GOOG”, “price”,TODAY()-30,TODAY())
Example 3: Percentage from 52 Week High
=(GOOGLEFINANCE(“NASDAQ:GOOG”,”price”))/(GOOGLEFINANCE(“NASDAQ:GOOG”,”high52″))-1
Ticker
The first variable you need to fill in is the ticker. We recommend to use both the exchange symbol and ticker symbol for the best results. For example, use “NASDAQ:GOOG” instead of “GOOG”.
Not sure what ticket to use? Search for the company on Google Finance, the ticker will pop up.
If the exchange symbol is not specified, GOOGLEFINANCE will use its best judgment to choose one for you.
Attribute
There are a lot of options to choose from, for the Attribute. That’s why we’ve created a table with all of them. I’d recommend carefully go by each one before you open up Sheets. Since there is a lot of logic and data already available, you don’t have to create complex formulas to get the info.
List of attributes
| “price” | Real-time price quote, delayed by up to 20 minutes. |
| “priceopen” | The price as of market open. |
| “high” | The current day’s high price. |
| “low” | The current day’s low price. |
| “volume” | The current day’s trading volume. |
| “marketcap” | The market capitalization of the stock. |
| “tradetime” | The time of the last trade. |
| “datadelay” | How far delayed the real-time data is. |
| “volumeavg” | The average daily trading volume. |
| “pe” | The price/earnings ratio. |
| “eps” | The earnings per share. |
"high52" | The 52-week high price. |
| “low52” | The 52-week low price. |
| “change” | The price change since the previous trading day’s close. |
| “beta” | The beta value. |
| “changepct” | The percentage change in price since the previous trading day’s close. |
| “closeyest” | The previous day’s closing price. |
| “shares” | The number of outstanding shares. |
| “currency” | The currency in which the security is priced. Currencies don’t have trading windows, so open, low, high, and volume won’t return for this argument. |
| “closeyest” | The previous day’s closing price. |
| “date” | The date at which the net asset value was reported. |
"returnytd" | The year-to-date return. |
| “netassets” | The net assets. |
| “change” | The change in the most recently reported net asset value and the one immediately prior. |
| “changepct” | The percentage change in the net asset value. |
| “yieldpct” | The distribution yield, the sum of the prior 12 months’ income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month’s net asset value number. |
| “returnday” | One-day total return. |
| “return1” | One-week total return. |
| “return4” | Four-week total return. |
| “return13” | Thirteen-week total return. |
| “return52” | Fifty-two-week (annual) total return.” |
| “return156” | 156-week (3-year) total return. |
| “return260” | 260-week (5-year) total return. |
| “incomedividend” | The amount of the most recent cash distribution. |
| “incomedividenddate” | The date of the most recent cash distribution. |
| “capitalgain” | The amount of the most recent capital gain distribution. |
| “morningstarrating” | The Morningstar “star” rating. |
| “expenseratio” | The fund’s expense ratio. |
Start Date
The start date is optional. Logically, the value entered must be date formatted. When entered, the start date will fetch historical data from this point in time. If the start_date is specified but the end_date of num_days is not, then only one single day’s data is returned. Examples could be:
=GOOGLEFINANCE(“GOOG”, “price”,TODAY())
=GOOGLEFINANCE(“GOOG”, “price”,"2021-01-01")
End Date / Number of Days
Also, the end date is optional. Here you can fill in one of the two following values:
End date
Also for the end date, it’s true that the value entered must be date formatted. The end date will be the last date if you’re fetching historical data. It will look like this to get price data between January 1st, 2021, and January 21st, 2021.
=GOOGLEFINANCE(“GOOG”, “price”,"2021-01-01","2021-01-21"
To get the data of the last 30 days:
=GOOGLEFINANCE(“GOOG”, “price”,TODAY()-30,TODAY())
Number of Days
If you choose to go for a fixed amount of days, you’ll only need to set the amount of days you want to receive historical data from. In the example below, that’s 10. We’ll get historical price data from between January 1st, 2021, and January 11th, 2021.
=GOOGLEFINANCE(“GOOG”, “price”,”2021-01-01″,10)
Use cases
Now you know how the formula works, It’s time to put learnings to practice. I’m sure that you can think of a dozen of ideas yourself to create. You can create a research tool, where you only need to enter the ticket and all stock information will pop up. Or you could create a portfolio tracker where you have insights into your gains and losses.
Stock Tracker
For inspiration, I’d like to show you an example sheet we’ve created that uses Google Finance Data to get basic insights into a stock. We’ve created an overview of all stock market listed food delivery companies and their stock information. We wanted to make something that has both real-time information as historical information. Also, it needed to be easy to use. We wanted it fully dynamic, where you only need to enter the ticker and the sheet will do the rest. We wanted to have insights into the following variables:
- Company Name
- Year Chart
- Price
- Day on Day % Change in Price
- Earnings per Share
- Price to Earnings ratio
- Shares Issued
- Market Cap
- 52 Week High
- 52 Week Low
- Percentage from 52Week High
- Percentage from 52 Week Low
- Volume in Shares
- 90 Day Volume
- Dollar Volume
The sheet, which you can find here, looks like this:
If you also want to use this sheet for your own stocks. Make a copy. You can do this by clicking the ‘File’ menu on the left upper corner. Followed by ‘Make a copy’. I’ll quickly go through the columns one by one to explain the formulas used and how they look like.
Ticket
In the first column, there are no formulas. This column serves as a variable that we can later use in other formulas.
Company Name
Firstly, we wanted to retrieve the company name. By using a formula, you’ll not only save time but also make sure that you have the right Ticker. Note that our ticker is inside cell A.
=GOOGLEFINANCE(A3,”name”)
Year Chart
Secondly, we wanted to see a trend of how things were going over the span of a year. To do this, we used a sparkling formula. This will automatically create a trend line in a cell.
=SPARKLINE(GoogleFinance(A3, “price”, TODAY()-365, TODAY()))
Price
Next, we wanted to get the current price of the stock. For this we used price attribute.
=GOOGLEFINANCE(A3,”price”)
Day on Day % Change in Price
To get the day on day change percentage we used the attribute “changepct”. Thereafter we divided it by 100 and formatted it as ‘Percent’ to get the actual change percentage. To make it a bit more insightful we also applied some condition formatting. Resulting in a green number if the change is positive, and red when the change is negative.
=GOOGLEFINANCE(A3, “changepct”)/100
Earnings per Share
Next up, we used the attribute “eps” to get the earnings per share. Since a lot of food delivery companies aren’t profitable yet, we were getting a lot of ugly ‘#N/A’ errors back. To make this a bit less sore to the eye, we’ve added an “iferror” formula to get back ‘Not available’.
=iferror(GOOGLEFINANCE(A3,”eps”),”Not Available”)
Price to Earnings ratio
Accordingly, the same error happens to the “pe” attribute. Hence the same “iferror” formula.
=iferror(GOOGLEFINANCE(A3,”pe”),”Not Available”)
Shares Issued
To get the number of shares, we used the “shares” attribute. The amount of numbers you get back is huge, that’s why we followed the formula by a division of 1.000.000, to make it a bit more clear and easy to understand.
=GOOGLEFINANCE(A3,”shares”)/1000000
Market Cap
Next up is the Market Cap. The same huge amount of numbers applies to the attribute “marketcap”. That’s why we also chose to divide it by a million.
=GOOGLEFINANCE(A3,”marketcap”)/1000000
52 Week High
Nothing really special happens in this formula. Just the highest price of the last 52 weeks.
=GOOGLEFINANCE(A3,”high52″)
52 Week Low
The same applies to “low52”. The lowest price of the last 52 weeks.
=GOOGLEFINANCE(A3,”Low52″)
Percentage from 52Week High
We wanted to know what the difference was between the current price and the highest price of the last 52 weeks. Herefore we divided the current price (D3) by our 52 weeks high (J3), we then subtract 1 and format it as a percentage to get the actual percent difference.
=(D3/J3)-1
Percentage from 52 Week Low
For this, the same applies as above, but then for the 52 weeks low.
=(D3/K3)-1
Volume in Shares
We use the “volume” attribute to get the total amount of vulmes in shares.
=GOOGLEFINANCE(A3,”volume”)
90 Day Volume
Followed by the 90-day volume. Again, we use a sparkling to see the trend of the volume in the last 90 days. We use the “TODAY()-90” as the start date to set it on 90 days ago. The end date is set on today by using the formula “TODAY()”. Resulting in data of the last 90 days.
=SPARKLINE(GoogleFinance(A3, “volume”, TODAY()-90, TODAY()))
Dollar Volume
Lastly, in the last column, we want to get the volume in dollars. To do this we will use the “volume” attribute and multiply it by the current price (D3). Again, we see huge numbers coming back. Hence the division by 1.000.000.
=(GOOGLEFINANCE(A3,”volume”)*D3)/1000000
GoogleFinance not working?
There are a couple of common errors with Google Finance.
- Data does not correspond with targeted Ticker
Make sure you use both the exchange symbol and ticker symbol for the best results. For example, use “NASDAQ:GOOG” instead of “GOOG”. This way you’ll get the right price from the right exchange.
- #REF! error when retrieving historical data
When retrieving historical data, you might encounter the ‘#REF!’ error. This error happens when cells near your function already contain data. This function would have to overwrite the nearby data. Luckily, Google Sheets doesn’t do this. If you want to solve this error, make sure there are enough empty cells around the formula. You can do this by replacing nearby data or adding rows and columns.
If your error is not in this list, please let us know, we’ll try to figure it out and add it to the list!
Sum up
That’s it! This is all you need to know to work with the GOOGLEFINANCE formula in Google Sheets. If you still have any questions, don’t hesitate and leave a comment down below. We’ll check all comments daily and try to help you out with your issues. Cheers!