How to Add Stock Data Type in Excel

Tired of manually copying and pasting stock prices into your spreadsheets? Excel has a powerful built-in feature called the Stocks data type that can turn your static list of tickers into a dynamic, updating portfolio tracker. This article will show you exactly how to find and use this feature to pull real-time financial information directly into your workbook, saving you time and eliminating manual data entry.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly is the Excel Stocks Data Type?

Think of the Stocks data type as a "smart" cell format. When you apply it to text, like a company name or a ticker symbol (e.g., "Microsoft" or "MSFT"), Excel connects to a trusted online data source to recognize what you've typed. It then converts that simple text into a rich data object filled with layers of information. Instead of just being a static word, the cell now contains a connection to financial data points like the current price, 52-week high, market cap, and much more.

The primary benefit is automation. Instead of visiting a finance website, finding a stock price, and typing it into a cell, this feature does all the work for you. And because it's a live connection, you can refresh the data with a single click to get the latest information without leaving your spreadsheet.

Prerequisites: Why You Might Not See the Stocks Data Type

If you're looking for the feature and can't find it, there's a reason. Before you start, make sure you meet these three requirements:

• A Microsoft 365 Subscription: The Stocks data type is a premium feature available only to Microsoft 365 subscribers. It is not included in one-time purchase versions of Excel like Excel 2019 or Excel 2021.
• An Internet Connection: Because Excel needs to fetch data from an online service, you must have an active internet connection for it to work.
• English Editing Language: In most cases, Data Types are currently only available if you have the English editing language added to your Office Language Preferences. You can check this by going to File > Options > Language.

If you meet these criteria, you're ready to start building your own dynamic financial dashboards.

How to Use the Stocks Data Type: A Step-by-Step Guide

Using this feature is surprisingly simple. Let's walk through the process from entering your tickers to building out a complete data table.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: List Your Companies or Ticker Symbols

Open a new Excel worksheet and in a single column, type the names of the stocks you want to track. You can use official ticker symbols, company names, or a mix of both. For the best accuracy, using the ticker symbol is recommended.

For example, you could create a list like this in column A:

• MSFT
• GOOGL
• Apple Inc
• AMZN
• TSLA

Step 2: Convert Your Text into the Stocks Data Type

Now, it's time to tell Excel that these aren't just regular text entries.

1. Select the cells containing your list of tickers and company names.
2. Go to the Data tab on the Excel ribbon.
3. In the Data Types group, click on the Stocks button. It has an icon that looks like a little building with columns.

Excel will then connect to its online service to identify each entry. If successful, you'll see the text in each cell align to the left and a small building icon will appear next to the name. This confirms that Excel has converted the text into a rich data type.

What if Excel can't identify a stock? If a name is ambiguous (e.g., a company name that matches multiple tickers on different exchanges), a question mark icon will appear, and a Data Selector pane will open on the right side of your screen. This pane will list potential matches, allowing you to select the correct security.

Step 3: Add Stock Information to Your Dashboard

This is where the magic happens. Now that your cells are "smart," you can start pulling in specific financial data points.

Select one or more of your newly converted cells (e.g., A1 through A5). An Insert Data button, which looks like a small grid with a plus sign, will appear just to the right of your selection.

Click this button, and a drop-down menu of available data fields will appear. This list is extensive, but some of the most popular fields include:

• Price: The latest traded price. Note that there may be a delay depending on the exchange.
• Change: The change in price from the previous day's close.
• Change (%): The percentage change from the previous day's close.
• 52 week high / 52 week low: The highest and lowest prices over the past year.
• Market cap: The company's total market capitalization.
• Volume: The number of shares traded for the current day.
• P/E: The price-to-earnings ratio.
• Previous close: The closing price from the prior trading day.

Simply click on any of these fields, and Excel will automatically add a new column next to your ticker list populated with that data for every stock.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Using Formulas with Data Types

Once you’re comfortable with the basics, you can use formulas for a more tailored layout. Instead of clicking the menu, you can reference the converted cell and pull a specific data point. Just type your converted cell (e.g., A2) followed by a period, and an IntelliSense menu will pop up with all available fields.

For example, if "MSFT" is in cell A2, you could get its price by typing this formula in any other cell:

=A2.Price

To get its market cap, you'd use:

=A2.[Market cap]

Note: If a field name contains a space, like "Market cap," you need to enclose it in square brackets.

Practical Example: Building a Personal Portfolio Tracker

Let's tie it all together by building a simple but effective portfolio tracker.

1. Set Up Your Table Headers

In the first row, create headers for your tracker, such as: Ticker, Company, My Shares, Price Per Share, and Total Value.

2. List and Convert Your Tickers

Under the "Ticker" heading, list the stock symbols for your investments. Select them and convert them to the Stocks data type as described above.

3. Add Data Fields

To get the full company name, click your converted Ticker column, press the "Insert Data" button, and select Name. To get the stock price, repeat the process and select Price. Excel will create and populate the "Company" and "Price Per Share" columns for you.

4. Enter Your Manual Data

In the "My Shares" column, manually type in the number of shares you own for each respective stock. This is your personal data, so it won’t be pulled automatically.

5. Calculate the Total Value

In the "Total Value" column, you'll create a simple multiplication formula. If your "My Shares" are in column C and "Price Per Share" is in column D, the formula in cell E2 would be:

=C2*D2

Drag this formula down for all your stocks. Now you have a perfectly good portfolio tracker that shows the current value of your holdings.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Tips, Tricks, and Troubleshooting

Here are a few pointers to help you make the most of this feature and fix common issues.

Pro-Tip: Use Excel Tables (Ctrl + T)

Before you even start, convert your data range into a proper Excel Table. Just select your initial tickers and headers and press Ctrl + T. Tables offer huge advantages:

• Auto-Expansion: When you add a new ticker to the bottom of the table, Excel will automatically apply the Stocks data type to it.
• Formula Fill-Down: Any formulas you have, like the "Total Value" calculation, will automatically copy down for new rows you add.

How to Refresh Your Stock Data

The stock information doesn't update in true real-time. To get the latest data, you need to trigger a refresh. You can do this in a few ways:

• Go to the Data tab and click the Refresh All button.
• Right-click on any cell with the Stocks data type and choose Data Type > Refresh.
• You can even set an automatic refresh interval from the Data tab by clicking Refresh All > Connection Properties and setting the data to refresh every few minutes.

What if a Field Shows a #FIELD! Error?

This error might appear if the specific data point you requested isn't available for that particular security. For instance, a P/E ratio might not be available for a company with no earnings. Less commonly, it could signal a temporary disruption in the data service connection.

Final Thoughts

Excel's built-in Stocks data type transforms the application from a simple spreadsheet into a powerful tool for financial analysis and tracking. By pulling in real-time, reliable data automatically, you can focus less on data entry and more on making informed decisions about your watchlist or portfolio.

At Graphed, we've taken this idea of simplifying data analysis even further. While Excel is fantastic for financial data, we recognized that most businesses are struggling to connect the dots between sales, marketing, and advertising data scattered across a dozen different tools. We help you connect sources like Google Analytics, Shopify, HubSpot, and Facebook Ads in seconds, allowing you to build real-time dashboards and reports simply by asking questions in plain English - no formulas needed.