How to Import Stock Data into Google Sheets

Bringing live financial data into Google Sheets is much easier than you might think, and you don't need any complex add-ons to do it. Google has a powerful built-in function called GOOGLEFINANCE that can pull stock prices, market caps, and historical data directly into your spreadsheets. This article will walk you through how to use this function to get real-time stock quotes, pull historical performance data, and even build a simple portfolio tracker from scratch.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Your New Favorite Tool: The GOOGLEFINANCE Function

The magic behind pulling market data into Google Sheets is the GOOGLEFINANCE function. It’s a dedicated tool designed to retrieve current and historical securities information from Google Finance, all without leaving your spreadsheet. Understanding its syntax is the first step to unlocking its power.

The basic structure of the formula looks like this:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

This might look intimidating, but each part is straightforward:

• Ticker: This is the most important part. It's the unique symbol for the security you want to track. You must include the stock exchange symbol to get consistent results. For instance, Alphabet is "GOOGL," but its full ticker symbol is "NASDAQ:GOOGL." Likewise, Apple is "NASDAQ:AAPL" and a stock on the Toronto Stock Exchange, like Shopify, would be "TSE:SHOP".
• Attribute (Optional): This tells the function what specific piece of information you want. If you leave it blank, it defaults to the real-time stock price.
• Start Date (Optional): When you want your historical data to begin.
• End Date | Num_days (Optional): When you want your historical data to end, or the number of days of data you want from the start date.
• Interval (Optional): The frequency of the data, either "DAILY" or "WEEKLY".

We'll cover how to use all these parameters, but for now, let's focus on the basics: getting current stock data.

Getting Live Stock Data in Three Easy Steps

For most people, the main goal is to get a live (or near-live, with up to a 20-minute delay) price of a stock. To do this, you only need the ticker and the desired attribute.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Common Attributes for Current Data

Here are some of the most useful attributes you can request for a stock:

• "price": The real-time price.
• "priceopen": The price at 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.
• "pe": The price-to-earnings ratio.
• "eps": The earnings per share.
• "name": The full name of the company or security.

Let’s put it into practice.

Step 1: Set Up Your Spreadsheet

Open a new Google Sheet. Create headers to keep your data organized. For example, in row 1, type "Ticker" in A1, "Company" in B1, and "Current Price" in C1.

Step 2: Enter Your Ticker Symbol

In cell A2, type the full ticker symbol for the stock you want to track. Let's use Microsoft as an example: NASDAQ:MSFT.

Step 3: Write Your Formula

Now for the fun part. In cell C2, you’ll write the formula to get the current price. You can hardcode the ticker into the formula like this:

=GOOGLEFINANCE("NASDAQ:MSFT", "price")

While that works perfectly, a better method is to reference the cell containing the ticker (A2). This makes your sheet dynamic, allowing you to easily change or add new stocks later.

So, in cell C2, type this formula:

=GOOGLEFINANCE(A2, "price")

Press Enter, and the current price of Microsoft stock will instantly appear. To get the company name in cell B2, you can use:

=GOOGLEFINANCE(A2, "name")

Now you can list more tickers in column A (like "NASDAQ:AAPL," "NYSE:V," "NASDAQ:AMZN"), and simply drag the formulas in columns B and C down to expand your list. Google Sheets will automatically update the cell references for you.

How to Pull Historical Stock Data

The GOOGLEFINANCE function is also incredibly useful for historical analysis. By defining a date range, you can pull open, high, low, close, and volume data for any public company over a specific period.

To do this, you'll need to use the start_date and end_date parameters. It's best practice to use Google Sheets' own DATE function to format these correctly or reference cells containing dates.

For example, to retrieve the daily stock prices for Tesla (NASDAQ:TSLA) for all of 2023, you would use this formula:

=GOOGLEFINANCE("NASDAQ:TSLA", "price", DATE(2023, 1, 1), DATE(2023, 12, 31), "DAILY")

When you enter this formula, it won't just fill one cell, it will automatically create a full table of data containing the date, open, high, low, close, and volume for each trading day in your specified range. This is incredibly powerful for creating historical charts to visualize a stock's performance.

If you'd rather see weekly data, simply change "DAILY" to "WEEKLY".

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

A Tip for Dynamic Date Ranges

If you want your historical data to always stay current, you can use the TODAY() function in place of a fixed end date. For instance, to see data for the last 365 days, you could use this:

=GOOGLEFINANCE("NASDAQ:NVDA", "all", TODAY()-365, TODAY())

Using the attribute "all" pulls open, high, low, close, and volume data all at once.

Practical Example: Let's Build a Simple Portfolio Tracker

The real power of importing stock data into Google Sheets comes from integrating it into your own custom tools. Here’s how to build a basic but effective portfolio tracker that automatically updates its value.

1. Setting up the Columns

In a new sheet, create the following headers in row 1:

• A1: Ticker
• B1: Name
• C1: Shares Owned
• D1: Purchase Price
• E1: Current Price
• F1: Cost Basis
• G1: Market Value
• H1: Gain/Loss

2. Populate Tickers and Your Data

In column A, list the tickers of the stocks you own (e.g., "NASDAQ:AAPL", "NASDAQ:GOOGL"). In column C, type the number of shares you own for each stock. In column D, enter the average price you paid per share. Columns C and D are the only ones you need to fill in manually.

3. Add a GOOGLEFINANCE Formula for Real-Time Prices

In cell E2, use the GOOGLEFINANCE function to fetch the current price, referencing the ticker in A2:

=GOOGLEFINANCE(A2, "price")

To fill out the company name, write this formula in B2:

=GOOGLEFINANCE(A2, "name")

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

4. Calculate the Financials

Next, use simple arithmetic formulas to calculate your position's value:

• In F2 (Cost Basis): =C2*D2 (Shares Owned * Purchase Price)
• In G2 (Market Value): =C2*E2 (Shares Owned * Current Price)
• In H2 (Gain/Loss): =G2-F2 (Market Value - Cost Basis)

5. Expand Your Tracker

Select cells B2 through H2 and drag the small blue box in the bottom-right corner down to apply these formulas to all the tickers in your list. Now you have a full view of your portfolio that updates automatically!

6. Calculate the Total

Finally, click a cell below your "Market Value" column, say G10, and use the SUM function to calculate your total portfolio value:

=SUM(G2:G9)

Do the same for your "Gain/Loss" column to see your total portfolio profit or loss at a glance.

Troubleshooting: Common GOOGLEFINANCE Errors

Sometimes the function might not work as expected. Here are the most common errors and how to fix them.

• #N/A Error ("Ticker not found"): This almost always means the ticker symbol is incorrect or missing the exchange prefix. A stock might have a similar symbol on a different exchange. Double-check your symbol on Google Finance and make sure to include the prefix (e.g., use "TSE:SHOP" not just "SHOP").
• #REF! Error ("Array result was not expanded"): This happens when you try to pull historical data, but there isn't enough empty space below and to the right of the formula cell for the data table. Make sure you have plenty of empty cells for the results to load into.
• Formula Parse Error: You likely have a typo in your formula. Check for missing quotation marks around tickers and attributes, or misplaced commas.
• Old or Incorrect Data: Remember, Google Finance data can be delayed by up to 20 minutes and won't be sufficient for high-frequency trading. Also, a formula might sometimes get "stuck" on a cached value. A simple workaround is to make a small change in the sheet to force it to recalculate.

Final Thoughts

With just one powerful function, GOOGLEFINANCE, you can turn a simple Google Sheet into a dynamic dashboard for tracking investments and analyzing market trends. Whether you’re just fetching a single price or building a detailed multi-stock portfolio tracker, it’s an incredibly accessible tool for anyone interested in financial data.

While building custom trackers in Google Sheets is empowering, the manual setup and maintenance can become time-consuming, especially when you connect data across multiple platforms. At Graphed, we help you connect all your data sources and build real-time dashboards just by describing what you want. Instead of wrestling with formulas, you can ask for things like, "Create a dashboard tracking the market value of my tech stocks against our HubSpot marketing leads this quarter," and get an interactive dashboard in seconds that auto-updates for you.