How to Import Stock Data into Excel

Manually updating stock prices in a spreadsheet is a tedious process that wastes your time and invites errors. Thankfully, you can stop copying and pasting data from finance websites forever. This tutorial will show you exactly how to import live and historical stock data directly into Excel so your portfolio trackers and analysis sheets update automatically.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 1: Using Excel’s Built-in Stocks Data Type

For most day-to-day portfolio tracking, Excel's built-in Stocks data type is the simplest and fastest solution. This feature is available to all Microsoft 365 subscribers and transforms a simple stock ticker into a rich source of live data you can pull from directly.

How the Stocks Data Type Works

When you use this feature, Excel recognizes a company name or ticker symbol (like MSFT for Microsoft) and links it to an online data source. It converts the plain text in the cell into a special data object packed with information, including current price, P/E ratio, 52-week high, market cap, and dozens of other fields. You can then add this data to adjacent columns with just a couple of clicks.

Step-by-Step Guide to Importing Live Stock Data

Let's build a simple portfolio tracker to see this in action.

1. List Your Stocks: In a blank worksheet, type the ticker symbols for the stocks you want to track in a single column. For this example, let's use some popular tech stocks: MSFT, AAPL, AMZN, GOOG, and TSLA. It's helpful to give your column a header, like "Ticker."
2. Convert to a Data Type: Select the cells containing your ticker symbols. Then, navigate to the Data tab on the Ribbon and find the "Data Types" group. Click on Stocks. Excel will take a moment to connect to its online service. If successful, you'll see the ticker symbols replaced with the full company name, and a small "building" icon will appear to the left of each name. This icon confirms that Excel has converted the text into the Stocks data type.
3. Extract Stock Information: With the cells still selected, a small button with a grid icon will appear at the top-right of your selection. This is the Insert Data button. Click it to see a dropdown list of all the available data fields you can add. Let's add the stock price. Click on Price from the list. Excel will automatically create a new column next to your tickers called "Price" and populate it with the latest stock price for each company.
4. Add More Data Fields: Repeat the process to add more information. Click the Insert Data button again and select "Change (%)" to see the day's performance. Then add "52-week high," "Volume," and "Market cap." In seconds, you have a detailed, live table.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Working with Formulas for More Control

While the "Insert Data" button is quick, you can also use standard Excel formulas to pull specific fields, giving you more flexibility in how you structure your worksheet.

Let's say your MSFT ticker is in cell A2. To get its price in cell B2, you would type:

=A2.Price

To get its industry in cell C2, you could use:

=A2.Industry

The beauty of this is that Excel’s AutoComplete will help you see all available fields. Just type =A2. and a list of all data points will appear for you to choose from. This is incredibly useful for building custom dashboards where data points are not neatly arranged in adjacent columns.

Tips for Success

• Refreshing Data: The data isn't constantly streaming but is considered "live-ish," often with a 15-20 minute delay. To get the latest numbers, go to the Data tab and click Refresh All.
• Handling Ambiguity: What if a ticker symbol could mean multiple things? For example, "BA" is Boeing but could also be the stock code for British American Tobacco on another exchange. If Excel is unsure, a question mark will appear, and a "Data Selector" pane will open on the right. You can then choose the correct security from the list.
• Using Excel Tables: For the best experience, format your list as an Excel Table (select your data and press Ctrl + T). When you use the "Insert Data" button, the new columns will automatically be incorporated into the table. Plus, if you add a new ticker to the bottom of the table, Excel will automatically convert it and fill in the formulas for you.

Method 2: Pulling Historical Data with the STOCKHISTORY Function

While the Stocks data type is great for current data, it can't give you historical prices. For that, you need the powerful STOCKHISTORY function. This function lets you pull a series of historical prices (and volumes) over a specific time period directly into a range of cells.

Understanding STOCKHISTORY's Syntax

The formula can look complex at first, but it's quite straightforward once you break it down. Here's the basic structure:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties...])

• stock: The ticker symbol (e.g., "MSFT" or a cell reference like A2).
• start_date: The first date you want data for (e.g., "1/1/2023").
• end_date (optional): The last date. If you omit it, it will just pull data for the start_date.
• interval (optional): 0 for daily (default), 1 for weekly, 2 for monthly.
• headers (optional): Specifies if headers should be shown. 0 for no headers, 1 to show headers.
• properties (optional): The data you want to see. 0 for Date and Close (default). Other popular choices are 1 for Open, 2 for High, 3 for Low, and 4 for Volume.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Practical Example: Tracking Quarterly Performance

Let's say you want to see the closing price for Apple (AAPL) every Monday for the first quarter of 2023. You can set this up easily.

1. Find a blank area in your worksheet.
2. Enter the following formula:

=STOCKHISTORY("AAPL", "1/1/2023", "3/31/2023", 1, 1, 0, 1)

Let’s decode what we just told Excel to do:

• "AAPL": Pull data for Apple Inc.
• "1/1/2023": Start date.
• "3/31/2023": End date.
• 1: Return weekly data.
• 1: Show the headers (Date, Open, etc.).
• 0, 1: Return the "Date" and "Open" price for each week.

When you press Enter, Excel will populate a full table of the weekly opening prices for that period. This is a "dynamic array" or "spill" function, meaning it automatically fills the cells it needs. Make sure you have enough empty cells below and to the right of the formula, or you'll get a #SPILL! error.

Tips for Using STOCKHISTORY

• Make it Dynamic: Instead of typing the ticker and dates directly into the formula, refer to other cells. Put "AAPL" in cell D1, your start date in E1, and your end date in F1. Then your formula can be =STOCKHISTORY(D1, E1, F1). Now you can change the inputs in those cells to analyze any stock or time period without touching the formula.
• Combine with Charts: The output from STOCKHISTORY is perfectly formatted for creating charts. After you’ve pulled the data, select it and go to Insert > Recommended Charts to quickly visualize historical performance.

Method 3: Importing Data From a Web Source with Power Query

Sometimes the built-in tools aren't quite enough. You might want to pull data from a specific website like Yahoo Finance to get access to unique historical data sets or specific formats not available through Excel Stocks. For this level of control, Power Query is your best choice.

Power Query is a data-shaping tool built into Excel. It can connect to hundreds of sources (including websites), clean and transform the data, and load it into your workbook. Think of it as an "ETL" (Extract, Transform, Load) tool on steroids.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step: Importing Historical Tables from Yahoo Finance

1. Get the URL: Go to finance.yahoo.com and search for a stock, for instance, Netflix (NFLX). Click on the "Historical Data" tab. You can set the time period on this page. For now, just copy the URL from your browser's address bar.
2. Start the Import: In Excel, go to the Data tab. In the "Get & Transform Data" group, click From Web.
3. Connect to the Page: A dialog box will appear. Paste the Yahoo Finance URL you copied into the box and click OK.
4. Navigate the Data: The Power Query Navigator window will open. It will show a list of all the tables Excel detected on that webpage. Click through them until you find the main historical data table – it's usually named "Table 0" or "Table 1" and will look like the data you saw on the website. Select it, and you'll see a preview on the right.
5. Transform and Load: At the bottom of the Navigator, you have two options. If the data looks perfect, you can click Load. But it's almost always a good idea to click Transform Data first. This opens the Power Query Editor. Here, you can remove unnecessary columns, rename headers, and make sure dates and numbers are formatted correctly. Once you're done, click the Close & Load button in the top-left.

The data will be loaded into a new worksheet as a properly formatted Excel table. The best part? This query is now saved. To get updated historical data, you just need to right-click the table and choose Refresh.

Final Thoughts

You no longer have any reason to waste time manually entering stock data into Excel. By using the built-in Stocks data type for live information, the STOCKHISTORY function for historical series, and Power Query for custom web imports, you can build powerful, automated tools for market analysis and portfolio tracking.

Manually tracking stocks is a lot like the manual reporting we see many marketing and sales teams do - spending hours just to gather data from tools like Google Analytics, Shopify, and Facebook Ads. That’s why we built Graphed to automate the entire process for business analytics. Instead of wrestling with CSV files and complex dashboards, you can connect your accounts and ask for the charts and insights you need in plain English, getting back your time to focus on strategy instead of report-building.