If you're looking for a way to get all the stock data you need in one place, then look no further than Google Sheets. This tutorial will show you how to add real-time and historical stock data to your spreadsheets. Then, with just a few simple steps, you'll be able to analyze stocks in Google Sheets like a pro and track the performance of your favorite stocks with ease!

Why is getting stock data on Google Sheets helpful?

There are a few reasons why getting stock data in Google Sheets can be helpful. First, the tool is designed to be easy to use and is very powerful when it comes to analyzing financial data. It allows you to create charts, perform calculations and organize the data any way you want. Second, these capabilities will enable you to perform stock analysis, find investment opportunities or track your stock portfolio's performance. And lastly, if you're new to investing and want to better understand how to perform stock analysis, then using Google Sheets is a great way to do that.

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:

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

How to get stock data on Google Sheets?

There are many ways to get stock data on your google sheet spreadsheet. One of them is 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 to use import HTML formulas and try to scrape the data from an existing website like yahoo finance. While both of these ways can work and get you the data you need, they can be very tedious to set up and rigid, not allowing you to get the data you are looking for in the proper format so you can quickly perform your stock analysis.

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 Wiseshests both separately and together to get all the data you need (more on that below).

How to get real-time stock data in Google Sheets?

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

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.

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 apples real-time stock price with the google finance function, all you have to do is enter =GOOGLEFINANCE("Apple", "price")

After entering the function, it will automatically refresh from time to time to reflect the new price, which can be up to 20 minutes delayed.

As for the number of stocks and exchanges covered it 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("TSX:T", "price").

Now you know how to get real-time stock data on your Google Sheet spreadsheet using the Google Finance function.

How to get real-time stock data using Wisesheets

Wisesheets is the best alternative to google finance, and 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.

After this login and 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")

The beauty of this function is that you can refresh the data any time you want by 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 intentional stock exchanges outside of the U.S, 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.

Now you know how to get real-time stock data on your Google Sheet spreadsheet using Wisesheets.

How to get historical stock data on Google Sheets?

Getting historical stock data on Google Sheets is a bit complicated luckily, you can still do it with Google Finance and Wisesheets. 

The thing to note is that 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?

Getting historical financials and fundamental data in your spreadsheet is one of the most important things you can do to perform stock analysis and valuations.

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 statement dump (more on that below).

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

The way statement dump is very simple; all you have to do is enter the company name or ticker into 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, key metrics, and growth metrics in one click. 

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 company's symbol you'd like to get the data from. There are over 40,000 companies covered by different global stock exchanges. If the company is listed in the U.S., all you have to do is enter the ticker. In contrast, you need to enter the ticker for international exchanges, 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 do "net income" or "Net Income" and you will still be able to get the data.

Period

As for the period, you can enter a specific year like 2021. You will get the annual data for that year or "TTM" for the trailing twelve months or "LY" for the last year "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 2021.

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 different periods, you can use this function.

Now you know the easiest way to get stock fundamentals data on your Google Sheet spreadsheet.

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 the beginning period you'd like to get the data from. It's 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 a 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.

Altogether if you want to get Apple's close price from January 1st, 2021, until May 17, 2021, you would have to use the function as follows =GOOGLEFINANCE("aapl", "close", "01/01/2021", "24/05/2021").

Now you know how to historical stock price data on Google Sheets using the Google Finance Function.

Tips and tricks for getting stock data on Google Sheets

Suppose you are looking to get all the stock data you need on Google Sheets without coding or any complex formulas. In that case, the best way is to combine Wisesheets and Google Finance Together. 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 a lot faster and build dynamic dashboards and templates that suit your needs and provide you with the stock data you require.

Mastering both and building templates can take some time, for this reason, we have included various Google Sheets templates you can use right now that 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.
• 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.

Conclusion

In this article, you learned how to get real-time and historical stock data on Google Sheets using the Google Finance Function and Wisesheets, as well as how to use various Google Sheets templates to quickly get all the stock data you need. With this information, you should be able to find better investment opportunities and make smarter investment decisions.

If you have any questions or comments, please feel free to leave them in the comments section below. Thanks for reading!

Best regards,

The Wisesheets team