Graphed LogoGraphed
Login

How to Get Stock Data in Google Sheets

Cody Schneider•

Pulling real-time stock data into a spreadsheet doesn't have to involve complicated scripts or expensive third-party add-ons. Tucked inside Google Sheets is a powerful, built-in function that can do the heavy lifting for you. This article will show you exactly how to use the GOOGLEFINANCE function to fetch current and historical stock prices, key financial metrics, currency exchange rates, and more - directly in your worksheet.

Understanding the GOOGLEFINANCE Function

The magic behind getting stock data into Google Sheets is a single, versatile function: GOOGLEFINANCE. At its core, this function fetches current or historical securities information from Google Finance and displays it right in a cell.

First, let’s break down its structure. The syntax looks like this:

That might look a little intimidating, but each part is straightforward. Let’s look at what each parameter does:

  • ticker: This is the most important part. It's the unique symbol for the stock or security you want to track. For reliable results, you should always include the stock exchange symbol. For example, instead of just "GOOGL" for Alphabet, you’d use "NASDAQ:GOOGL". For Apple, you’d use "NASDAQ:AAPL".

  • attribute (optional): This tells the function what specific piece of information you want. If you leave this blank, it defaults to the stock's current price. There are dozens of attributes you can use, from "marketcap" (market capitalization) to "pe" (price-to-earnings ratio).

  • start_date (optional): If you want historical data, this is the first date you want information from. You should format it using the DATE function, like DATE(2023,1,1) for January 1, 2023.

  • end_date|num_days (optional): This defines the end of your historical data period. You can either specify an end date or provide a number of days from the start date.

  • interval (optional): This sets the frequency of the historical data you retrieve. Your options are "DAILY" or "WEEKLY".

Don't worry about memorizing all of this. Most of the time, you'll only be using the first two or three parameters. The key is understanding that you need a ticker symbol and can ask for different types of data with attributes.

Common Attributes You'll Use Often

While there are many attributes, you'll likely find yourself turning to a handful of them again and again. Here are some of the most useful ones:

  • "price": The real-time price (can be delayed up to 20 minutes).

  • "priceopen": The price at the 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 delayed the real-time data is.

  • "pe": The price-to-earnings ratio.

  • "eps": The earnings per share.

  • "high52": The 52-week high price.

  • "low52": The 52-week low price.

  • "change": The change in price since the previous market close.

  • "changepct": The percentage change in price since the previous close.

  • "all": This handy attribute fetches a full table of key metrics, including open, high, low, volume, market cap, P/E, and more.

Practical Examples: Putting GOOGLEFINANCE to Work

The best way to learn is by doing. Let's walk through a few common scenarios, from getting a simple price to building a mini-portfolio tracker.

1. Getting the Current Price of a Stock

This is the most common use case. All you need is the ticker symbol. Let's say we want to find the current price of Microsoft (MSFT), which trades on the NASDAQ exchange.

  1. Click an empty cell in your Google Sheet.

  2. Type the following formula and press Enter:

Because we didn't specify an attribute, Google Sheets automatically defaults to "price." In a moment, the cell will update to show the current trading price of Microsoft stock.

2. Building a Basic Stock Overview Table

Let's make our sheet more dynamic. Instead of hardcoding the ticker into the formula, we'll reference another cell. This allows you to easily change the stock you're looking at without rewriting multiple formulas.

  1. In cell A1, type the text "Ticker". In cell A2, type "NASDAQ:AAPL" for Apple Inc.

  2. In cell B1, type "Metric". In cells B2, B3, and B4, type "Price", "P/E Ratio", and "Market Cap" respectively.

  3. Now, in cell C2 (next to "Price"), we'll write a new formula that refers to cell A2:

  1. In cell C3 (next to "P/E Ratio"), do the same, but change the attribute:

  1. And in cell C4 (next to "Market Cap"):

Now you have a neat little table that pulls key information for Apple. The best part? If you change the ticker in cell A2 from "NASDAQ:AAPL" to "NYSE:KO" (Coca-Cola), all the formulas in column C will automatically update with Coca-Cola's data.

3. Creating a Simple Stock Portfolio Tracker

Ready to level up? We can use these principles to build a simple portfolio tracker that calculates the current value of your holdings.

  1. Set up your columns.

In the first row of a new sheet, create these headers:

  • Column A: Ticker

  • Column B: Company Name

  • Column C: Shares Owned

  • Column D: Current Price

  • Column E: Market Value

  1. Enter your holdings.

Fill out your tickers in Column A (e.g., "NASDAQ:TSLA", "NYSE:V", "NASDAQ:AMZN") and the number of shares you own for each in Column C. For the company name in Column B, you can use the "name" attribute:

Drag this formula down the column to fill it for all your tickers.

  1. Pull the current price.

In cell D2, write the formula to get the price for the ticker in cell A2:

Click on the small blue square (the fill handle) at the bottom-right of cell D2 and drag it down to automatically apply this formula for all your stocks.

  1. Calculate the market value.

Finally, in cell E2, multiply the shares owned by the current price. This is a standard spreadsheet formula, not a GOOGLEFINANCE one.

Drag this formula down as well. You now have a live tracker that shows the current market value of your portfolio! You can even add a SUM formula at the bottom of Column E to see the total value.

4. Fetching Historical Data for a Chart

GOOGLEFINANCE really shines when you need to pull historical data. Let's get the daily closing price of Tesla for the entire year of 2023.

In an empty cell, enter this formula:

When you press Enter, Google Sheets will populate a table with two columns: "Date" and "Close". It will list the closing price for every trading day in 2023. If you used the "all" attribute instead of "price", it would output a full table of Date, Open, High, Low, Close, and Volume for each day.

Once you have this raw data, creating a visual is easy. Just select all the data (both columns), go to the menu, and click Insert > Chart. Google Sheets will automatically suggest a line chart, and just like that, you've visualized Tesla's stock performance over the year.

5. Beyond Stocks: Mutual Funds and Currency Exchange

The GOOGLEFINANCE function isn't limited to individual stocks. You can use it for mutual funds and currency pairs, too.

  • For a Mutual Fund (e.g., Vanguard 500 Index Fund Admiral Shares):

  • For Currency Conversion (e.g., US Dollar to British Pound):

Tips for Success and Troubleshooting

While powerful, there are a few things to keep in mind to get the most out of the function.

  • Always Include the Exchange: A ticker like "V" might exist on multiple exchanges worldwide. Using "NYSE:V" tells Google exactly which security you want, preventing errors and incorrect data. If you aren't sure, search for the stock on Google Finance and see which exchange it lists.

  • Handle Errors Gracefully with IFERROR: What if you type a ticker wrong, like "NASDA:APPL"? You'll get an ugly #N/A error. You can clean this up by wrapping your formula in an IFERROR function. It tells Sheets what to do if the formula results in an error.

Now, if the ticker is wrong, the cell will display "Invalid Ticker" instead of an error message.

  • Data is Not Instantaneous: Remember that the data can be delayed by up to 20 minutes, so it's not suitable for high-frequency trading. For everyday portfolio monitoring, however, it's more than sufficient.

Final Thoughts

As you can see, the GOOGLEFINANCE function transforms Google Sheets from a simple spreadsheet into a powerful and dynamic tool for tracking stocks, analyzing historical trends, and managing your personal portfolio. By mastering its syntax and parameters, you can build customized dashboards that automatically update with the data you care about.

While Google Sheets is an excellent resource for DIY financial tracking, managing multiple data streams for a business - such as marketing performance, sales numbers, and customer data - presents a different level of complexity. Instead of wrestling with formulas, we built Graphed to connect to all your sources like Google Analytics, Shopify, and Salesforce. Simply ask plain-English questions like, "Create a dashboard showing our sales pipeline from HubSpot this quarter," and get an interactive, real-time dashboard in seconds. We automate the busy work so you can get back to insightful analysis, faster.