Google Sheets is a great way to keep track of your stocks. Not only can you get real-time stock prices, but you can also get historical data going back years. In this guide, we will show you how to set up your Google Sheet to track stock prices. We will also give some tips on how to use the data in order to make better investment decisions. Let's get started!

How to get real-time stock prices on Google Sheets using Google Finance

The easiest way to get real-time stock prices on Google Sheets is to use the =GOOGLEFINANCE() function which pulls the data from Google Finance itself. This function is already installed on Google Sheets by default, so you don't need to install programs or enter any code.

The syntax of the function is simple:

=GOOGLEFINANCE(“ticker”, “attribute”)

Here is a breakdown of the function components:

Ticker

Refers to the company ticker or symbol you want to get the real-time data for. You can hard code this value into the formula by entering "AAPL" for example, or reference a cell that has a ticker like A2.

The easiest way to find company tickers is to Google the company name followed by the word stock, for example, "Apple stock." This will return the stock ticker you need to use for that particular company.

For international stock exchanges outside of the US, you can also access real-time stock price data by entering the designated exchange code followed by a colon and then the ticker. This is information is easily accessible by following the method of Googling the company name followed by the word stock. For example, you can see here how for Telus, a Canadian company, the ticker you need to enter is "TSE: T."

Attribute

Refers to the real-time attribute you want to get the data for. The attributes available along with their definition include:

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

Similar to the ticker, you can enter the attribute directly in quotes or reference a cell that has the attribute. Keep in mind the spacing and capitalization do not matter, but the spelling must be the same.

Example of using the Google Finance function to get real-time stock data.

Altogether you can get Apple's real-time stock price by entering on any cell of your spreadsheet =GOOGLEFINANCE(“AAPL”, “price”)

In the same way for international stocks, if you want to get the stock price for BHP Group, an Australian stock, you can do =GOOGLEFINANCE(“ASX: BHP”, “price”).

Altogether you can build stock watchlists and trackers like these that allow you to track multiple stocks at once right from your spreadsheet.

How to get historical stock prices on Google Sheets using Google Finance

Getting historical stock price data on your Google Sheet spreadsheet is very similar to real-time price data, except the function syntax is a bit different.

=GOOGLEFINANCE(“ticker”, “metric”, start date, end date, interval)

Here is a breakdown of the function components:

Ticker

The ticker works precisely the same as it does for getting real-time price data (see details above).

Metric

The full list of metrics available for historical data includes:

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

Like the ticker, you can enter the attribute directly using quotes or reference a cell with the attribute. Keep in mind the spacing and capitalization do not matter, but the spelling must be the same.

Start date

The start date is the beginning date from which you'd like to get data. This must be in the data format using the =DATE() function or like this: "day/month/year."

End date

The start date is the end date from which you'd like to get data. This must be in the data format using the =DATE() function or like this: "day/month/year."

Interval

The interval is the frequency of the historical data. The options are "daily" and "weekly."

Example of using the Google Finance function to get historical stock data.

To get the historical price data for Apple from January 01, 2021 to May 24, 2021 you need to enter: =GOOGLEFINANCE("AAPL", "price", "01/01/2021","24/05/2021", "daily")

Using this knowledge, you can build spreadsheet models that allows you to track multiple stocks' historical price data at once.

How to get stock financials on Google Sheets using Wisesheets

The key downside of the Google Finance function is the lack of historical financials, key metrics, and dividend data.

With the Wisesheets Google Sheets add-on, you can get all this data in one click.

More importantly, you can access the same data as well as dividends separately using the WISE and WISEPRICE functions to build dynamic stock models like this:

This allows you to analyze multiple stocks at once and find the best investments for your portfolio in less time.

Click here for a complete guide on the Wisesheets Google Sheets add-on.

Why track stock data in Google Sheets?

There are many reasons to track stocks in Google Sheets.

For one, it's the best tool for financial analysis. You can analyze stocks exactly how you want with the aid of external data and visualize and update the data any way you like using filters, charts, etc.

Second, You don't need to sign up for expensive services or buy software. All you need is a Google account, and you can start immediately.

Another reason is that it's easy to use and share your analysis. This allows you to sanity check your assumptions and ensure your analysis is on the right track.

Finally, it's versatile. You can track multiple stocks at once, get real-time or historical data, and even get financials, key metrics and dividend data.

Conclusion

In conclusion, tracking stocks in Google Sheets is a powerful way to analyze your investments. It's easy to use, versatile, and free. Now you can quickly get historical and live stock data on your spreadsheet, and with the Wisesheets add-on, you can get even more stock data to help you make the best investment decisions for your portfolio.

I hope this guide helps, let me know if you have any questions in the comments below 👇