As fellow investors, we know the one thing you crave for when you look at scattered stock data – to get it all in one place. Well, you do not need to look any further because in this blog post we talk about how to get all the stock data you need in Google Sheets.

This includes both real-time and historical stock data. After following some very easy steps, you will be on your way to track your stock data, analyze it, and make decisions like a wise investor.

But why bother with Google Sheets? 

To track performance? Perform analysis? Or even more?

Why is Getting Stock Data on Google Sheets Helpful?

If you are new to stock analysis, then Google Sheets is an excellent beginner tool that you can use to learn the ropes. For both beginners and experts, Google Sheets is not only easy, but very powerful to carry out simple to complex analysis. It allows you to create charts, perform calculations, and organize the data any way you want. This will enable you to perform stock analysis, find investment opportunities, or track your stock portfolio's performance. 

Can you imagine, Google Sheets can get data from any financial statement or metric!

What Stock Data can You Get on Google Sheets?

Due to the flexibility of the platform, you can get all types of stock data from historical financials, including, but no limited to, the following:

1. Income statement
2. Balance sheet
3. Cash flow statement
4. Historical price information
5. Real-time data
6. Key metrics
7. Dividend data
8. Analyst ratings

You can get data on Google Sheets both the easy way and the hard way.

How to get Stock Data on Google Sheets

There are many ways to get stock data on Google sheets spreadsheet as follows:

One of the ways is by using a stock API to get the data and then transfer it to your Google sheet spreadsheet using programming (see best stock APIs article).

The other way is through scraping the data. This is done by importing HTML formulas and scraping the data from an existing website like Yahoo Finance.

While both ways can get you stock data, they are very tedious to set up and you will not be able to get data in the format which will be easy for you to analyze.

In this article, we will describe the two best ways to get stock data on Google Sheets to provide convenience and customization using simple formulas. The two best ways to be discussed are using the Google Finance Function and Wisesheets, both separately, and, together, to get all the data you need (more on that below).

You can get both real-time and historical data on Google sheets through Google Finance and Wisesheets!

How to Get Real-time Stock Data in Google Sheets

There are two ways to get real-time stock data on Google Sheets; Google Finance and Wisesheets.

How to get real-time stock data using the Google Finance Function

The Google Finance Function provides easy access to real-time stock data for free, and it's already available on Google Sheets by default.

All you have to do is take advantage of the function syntax for real-time data.

=GOOGLEFINANCE("ticker", "attribute")

In the ticker, you can enter the ticker manually, such as "AAPL" or reference a cell containing the ticker like A1.

Metrics

The same concept applies to the attributes available within the function you can enter any of the following metrics:

• "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.

For example, if you'd like to get Amazon's real-time stock price with the Google Finance function, all you have to do is enter =GOOGLEFINANCE("AMZN", "price"). The function has given us the closing price of Amazon stock as of June 3, 2024.

After entering the function, the price will automatically update with a possible delay of up to 20 minutes.

The number of stocks and exchanges covered is the same as Google Finance. You can see the complete list for yourself here.

If you'd like to get data from an international stock outside of the U.S., all you have to do is enter the exchange code followed by a semi-colon and then enter the ticker. The rest of the function remains the same.

For example, to get Telus' stock price (a Canadian telecommunications company listed on the Toronto Stock Exchange), all you have to do is enter the following function command =GOOGLEFINANCE("TSE:T", "price").

How to get real-time stock data using Wisesheets

A good alternative to Google Finance is Wisesheets where you can get real-ticker stock data using the WISEPRICE function. 

In order to get access to this custom function, all you have to do is get your Wisesheets account here and then download the Wiseheets add-on by clicking here.

And that's it! You will have access to the WISEPRICE Function.

Similar to the Googlefinance function the syntax for this function is =WISEPRICE("ticker/s", "parameter/s"). For example, to get Apple's real-time stock price, all you have to do is enter =WISEPRICE("AAPL", "price")

You can refresh the data any time you want by simply pressing the refresh button on the Wiseheets menu.

Most importantly, you can enter multiple stocks and parameters at once in the function by referencing the cells that contain the data you are looking for so you can get it all at once.

The metrics available for the function include:

• Symbol
• Name
• Price
• Changes Percentage
• Change
• Day Low
• Day High
• Year High
• Year Low
• Market Cap
• Price Avg 50
• Price Avg 200
• Volume
• Avg Volume
• Exchange
• Open
• Previous Close
• EPS
• PE
• Shares Outstanding
• Earnings announcement
• Timestamp

For international stock exchanges outside the U.S., in order to get stock data, all you have to do is enter the ticker followed by a period and the exchange extension (very similar to the Yahoo Finance ticker organization).

For example, to get the same price data for the Canadian company Telus all you have to do is enter =WISEPRICE("T.TO", "price")

You can see the list of stock exchanges covered and extensions here.

How to get historical stock data on Google Sheets

Getting historical stock data is a bit more complicated than getting real-time data on Google sheets. But you don't have to worry. Just follow the steps we outline below. Please note that for historical data too, you can use both Google Finance and Wisesheets.

Also, there are different types of historical stock data you can get; historical price data and historical fundamentals. You will learn how to get data for each below.

How to get stock financials and historical fundamentals to Google Sheets

Unfortunately, the Google Finance Function does not provide historical financials and fundamentals on Google Sheets. 

But don't worry, with Wisesheets, you can quickly get this data using the =WISE function and Financials as shown below.

Before that, make sure to get a free account on this link.

The way to get financials and historical fundamentals is very simple. Simply open the Wisesheets add-in. Go to 'Financials'. Enter the company name or ticker in the menu, select whether you would like to get annual or quarterly data, and then standardized data (recommended option) or SEC as reported data.

After this, you will get all of the company's financial statements (income statement, balance sheet cashflow statement etc.), key metrics, and growth metrics in one click, as shown, in the following screenshot for Netflix. Note that the data is annual and the last period noted is 2023.

Now, if you'd like to only get specific data about a stock or a set of stocks then you can use the =WISE function, which has the following syntax =WISE("ticker", "parameter/s", "period/s", "quarter")

Ticker

The ticker is the symbol of the company that you use to get stock data. There are over 40,000 companies covered by different global stock exchanges. There are different ways if a company is listed in one of the U.S. stock exchanges vs. one of the international stock exchanges outside the U.S.

If a company is listed in a U.S. stock exchange all you have to do is enter the ticker. But if it is in one of the international stock exchanges, you need to enter the ticker for that international exchange, followed by a period and the respective extension (the same system as Yahoo Finance).

You can see the list of exchanges covered and extensions here.

Parameter

As for parameters, you can get data including company financials such as the income statement, balance sheet, and cash flow statement, key metrics, dividend data, segment revenues and growth metrics.

To see a complete list of data click here.

When using any of the available fundamental data, the only thing to remember is that the case and spacing do not matter, but the spelling must be the same i.e., you can enter "net income", or "Net Income", and you will still be able to get the data.

Period

For the period, you can enter a specific year like 2023. You will get the annual data for that year or "TTM" for the trailing twelve months, or "LY" for the last year, or "LQ" for the previous quarter of data available. You can use "LY-1", "LY-2"…. and "LQ-1", "LQ-2", etc. to get, etc. to get the data from previous fiscal quarters or years.

Quarter (optional)

Lastly, the quarter is an optional parameter, so you can get it if you are looking for specific quarterly data like "q1" of 2024.

Putting everything together you can do =WISE("aapl", "revenue", "ttm") to get Apple's TTM revenue

The great thing about the function is that rather than only getting one data point at a time, you can get many different fundamentals in one single function call by using cell references.

For example, If you want to analyze Apple's income statements for past and projected years, you can use this function. In the figure below, you can see that we have Apple's Income statements from 2022 all the way up to 2027.

How to get historical stock price data on Google Sheets

Currently, you can only get historical stock price data on Google Sheets using the Google Finance Function.

The Syntax of the function is simple =GOOGLEFINANCE("ticker", "metric", start date, end date, interval)

Like before, the ticker is the symbol of the stock you are trying to get data from.

The metrics available include:

• "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" – All of the above.

The start date is date from which you want your data to begin from. It is essential to enter this in the date format, "year, month, day."

The end date indicates the date you'd like the data to end. Similar to the start date the format should be "year, month, day". However, a handy feature is that you can indicate the number of days after the start date, for example, 90, and the function will return the last 90 days of data beginning from the starting date.

For historical data, you have the option to select between getting "weekly" and "daily" data. So, for example, if you want to get the historical stock close price, the data would indicate each day in the period you selected and the respective close price. On the other hand, if you want to get it on a weekly basis, you would get a table that indicates the week and the close price.

If you want to get Ford's close price from January 1st, 2024, until May 24, 2024, you would have to use the function as follows =GOOGLEFINANCE("ford", "close", "01/01/2024", "24/05/2024").

Become a master by combining both Google Finance and Wisesheets

The Way Forward

 With Wisesheets, you can get real-time price data as well as fundamental historical data, and with the Google Finance Function, you can get all the historical stock price data. This allows you to analyze companies in all types of ways very quickly, enabling you to find better investment opportunities.

By combining Wisesheets and Google Finance you can find investment avenues a lot faster and build dynamic dashboards and templates that suit your needs and provide you with the stock data you require.

Mastering both Google Finance and Wisesheets along with building templates can take some time. Due to this reason, we have included various Google Sheets templates you can use right now to allow you to quickly analyze stocks by providing you with the data you need to make intelligent investments below.

Google Sheets stock investment templates

Here are some of the best Google Sheets stock investment templates that you can use right now:

• WiseSheets Google Finance template: this Google Sheets template allows you to quickly get all the data you need on a company, including real-time price data as well as fundamental historical data.
• Google Sheets Portfolio tracker: this Google Sheets template allows you to track your portfolio performance by inputting the stocks you own and their respective weights. The data available is 52 week high, 52 week low, Outstanding Shares, Year high, Year low etc.
• Google Sheets Historical Dividend Template: this Google Sheets template allows you to track companies' historical dividend payments, current dividend yield, and present value based on this data.

These Google Sheets templates will allow you to quickly get all the stock data you need to make smart investment decisions without code or using complex formulas. With these templates, you can save a lot of time and effort when finding the best investment opportunities.

Summing up

In this article, you learned how to get real-time and historical stock data on Google Sheets using the Google Finance Function and Wisesheets. You also got to know about various Google Sheets templates to quickly get all the stock data that you might want. With this information, it should be more swift for you to get the stocks you want in your Google Sheet Spreadsheet and make wise investment decisions.