How to Pull Stock Data into Excel

Tired of manually copying and pasting stock prices into a spreadsheet? Excel has powerful, built-in tools that can pull real-time and historical stock data directly into your worksheets, saving you time and keeping your analysis up to date. This guide will walk you through the best methods, from the incredibly simple to the more advanced, to help you create your own stock tracking dashboards.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Easiest Method: Excel's Built-in 'Stocks' Data Type

If you have a Microsoft 365 subscription, this is by far the quickest and most user-friendly way to get live stock information. Excel's 'Stocks' data type feature transforms a simple stock ticker into a rich data source you can pull information from with a few clicks.

How to Use the 'Stocks' Data Type

Follow these simple steps to get started:

1. Enter Your Tickers: In a blank column, type the stock ticker symbols you want to track. For example, MSFT for Microsoft, AAPL for Apple, and so on. It can be just the ticker or the company name.
2. Select the Cells: Highlight the cells containing your tickers.
3. Convert to Stocks Data Type: Go to the Data tab on the Excel ribbon. In the 'Data Types' group, click on Stocks.
4. Confirm the Data: Excel will connect to an online source and convert your text into the 'Stocks' data type. You'll see a small building icon appear next to each ticker. If Excel is unsure about a ticker (e.g., "A" could be Agilent Technologies or a different company), it will open a 'Data Selector' panel on the right, allowing you to choose the correct stock exchange.
5. Add Your Data: With your converted cells still selected, a small "Insert Data" button (it looks like a plus sign inside a grid) will appear to the top right. Click this button to see a dropdown list of available data fields. You can select things like:

When you select a field, Excel will automatically add a new column next to your tickers and populate it with that data. You can repeat this process to add as many columns as you need.

Refreshing Your Data

The best part about this method is that the data is dynamic. To get the latest prices, simply go to the Data tab and click Refresh All. This will update all the information connected to your 'Stocks' data types.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Pros and Cons of This Method

• Pros: Incredibly easy to use, no complex formulas required, provides a very wide range of data points, and it's near-real-time (data is typically delayed by about 15-20 minutes).
• Cons: Requires a Microsoft 365 subscription. This feature is not available in older, single-purchase versions of Excel like Excel 2016 or 2019. It also offers limited access to historical data, which brings us to our next method.

Getting Historical Data with the STOCKHISTORY Function

Once you have current data, you'll often want to see how a stock has performed over time. For this, Microsoft 365 users can turn to the powerful STOCKHISTORY function. This function allows you to pull historical price and volume data for a specific period directly into a range of cells.

Understanding the STOCKHISTORY Syntax

The formula looks like this:

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

• stock: The ticker symbol for the stock (e.g., "MSFT"). You can also reference a cell that contains a ticker that has been converted with the 'Stocks' data type.
• start_date: The first date you want to get data for.
• end_date (optional): The last date for the data range. If you leave it blank, it will only pull data for the start_date.
• interval (optional): How frequently you want the data points. Use 0 for daily (default), 1 for weekly, or 2 for monthly.
• headers (optional): Determines if a header row is displayed. Use 0 for no headers, 1 to show headers (default).
• properties (optional): The data columns you want to see. You can select multiple by listing their corresponding numbers: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, 5 = Volume. The default is Date and Close.

Practical STOCKHISTORY Examples

To make this function dynamic, it's best to put your inputs (ticker, start date, end date) into separate cells and reference them in the formula.

Example 1: Daily Closing Prices for the Last 30 Days

Let's say cell A1 has the ticker "NVDA", B1 has the start date TODAY()-30, and C1 has the end date TODAY().

In cell A3, you could type:

=STOCKHISTORY(A1, B1, C1)

Excel will automatically fill the cells below and to the right with the date and daily closing price for Nvidia for the last 30 days.

Example 2: Weekly High, Low, and Close for the Past Year

Let's say cell A1 has "GOOG". We want weekly data for the past year, and we want to see the high, low, and close prices.

=STOCKHISTORY(A1, EDATE(TODAY(),-12), TODAY(), 1, 1, 3, 4, 1)

Here's a breakdown:

• EDATE(TODAY(),-12) is a clever way to get the date exactly 12 months ago.
• TODAY() gives us today's date for a seamless up-to-date range.
• 1 sets the interval to weekly.
• 1 tells Excel to include headers.
• 3, 4, 1 specifies that we want to pull the High, Low, and Close price columns.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Classic Method: Get Data From Web

What if you don't have Microsoft 365 or want data from a specific website like Yahoo Finance? You can use Excel's built-in Power Query tool (often called "Get & Transform Data") to pull data from a table on a webpage.

How to Use "From Web" for Stock Data

1. Find Your Data Source: Go to a financial website that presents historical data in a clean-looking table. Yahoo Finance is a popular and reliable choice. Find the historical data page for the stock you want to analyze and copy the URL from your browser's address bar.
2. Launch the Web Query: In Excel, go to the Data tab. In the 'Get & Transform Data' section, click on From Web.
3. Enter the URL: In the pop-up window, paste the URL you copied and click OK.
4. Select the Table: Excel will analyze the webpage and show a "Navigator" window with a list of all the tables it found. Click through the table names (e.g., Table 0, Table 1) until you see a preview of the historical price data you want. Select that table.
5. Load the Data: Click the Load button. Excel will import the data into a new worksheet, nicely formatted as an Excel Table. You can then refresh this data at any time by right-clicking the table and selecting "Refresh."

Pros and Cons of This Method

• Pros: Works in most modern versions of Excel (including 2016 and later), can pull data from countless sources, and is highly customizable if you're willing to learn the basics of the Power Query editor.
• Cons: The query can break if the source website changes its layout. Financial websites sometimes change how they structure their tables, which will cause your query to fail until you edit it again.

For Power Users: Using an API with Power Query

For the most control, flexibility, and reliability, you can pull data directly from a stock data provider using an API (Application Programming Interface). Think of an API as a direct, structured line of communication to a data source, bypassing the website's visual layout. This is the most robust method, but it has the steepest learning curve.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Getting Started with an API

1. Choose a Data Provider & Get a Key: You'll need an API key from a provider that offers financial data. Alpha Vantage and IEX Cloud are two popular options with free tiers that are perfect for personal use. Simply sign up on their website to get your free personal API key.
2. Construct the API URL: The provider's documentation will show you how to build a special URL that asks for the specific data you want. For example, using Alpha Vantage, a URL to get daily data for IBM might look like this: https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=IBM&apikey=YOUR_API_KEY
3. Use Power Query's "From Web" Feature: Just like in the previous method, go to Data > From Web and paste your API URL.
4. Transform the Data: Unlike pulling from a webpage table, API data usually comes back in a format like JSON. Power Query is excellent at handling this. When the editor opens:

While this process has more steps, it creates an incredibly durable and automated data connection that is much less likely to break than scraping a website directly.

Final Thoughts

Choosing the right method to pull stock data into Excel depends on your needs and your version of Excel. For quick, real-time insights, the built-in Stocks data type is unmatched. For historical trend analysis, the STOCKHISTORY function is your best friend. For ultimate flexibility and control, learning how to connect to web sources or APIs with Power Query will give you a powerful and automatable workflow.

Manually wrangling data in spreadsheets like this, even with these helpful functions, can take up valuable time that could be spent on strategy. At Graphed, we automate the difficult parts of data analysis. Instead of building manual reports, we enable you to instantly connect all your data sources and create live dashboards simply by describing what you want in plain English. This turns hours of spreadsheet work into a 30-second conversation, letting you get insights faster than ever.