How to Use Excel Data Types to Pull Live Stock Quotes

For years, financial analysts building stock portfolios in Excel had to rely on complex VBA macros, clunky web-scraping formulas, or expensive third-party add-ins like the Bloomberg Terminal to import real-time market data. If a ticker symbol changed or a website updated its layout, the entire spreadsheet would break. Microsoft has finally solved this problem by integrating a direct data feed into the core engine of Microsoft 365. By utilizing Linked Data Types, you can turn a simple text string like “MSFT” into a rich object containing live, continuously updating financial data.

Step 1: Enter Your Ticker Symbols

To begin, you just need a list of companies.

1. Open a blank Excel workbook.
2. In column A, type a list of company names or stock ticker symbols. You do not need to be precise; Excel’s AI is remarkably good at guessing.
   • A2: Apple
   • A3: MSFT
   • A4: Tesla
   • A5: Ford Motor Company

Step 2: Convert Text to the “Stocks” Data Type

Now, we transform this static text into live objects.

1. Highlight the cells containing your companies (A2:A5).
2. Navigate to the Data tab on the ribbon.
3. In the “Data Types” group (usually located in the middle), click the Stocks icon (it looks like a small building).

Excel will connect to the internet (via Refinitiv data) and attempt to match your text to a publicly traded company. You will know it worked when a small building icon appears next to the text in the cell, and the text turns into a clickable link. (If it guesses wrong, a question mark will appear, allowing you to manually search the database).

Step 3: Extract the Financial Data

Your cells no longer contain just text; they contain a massive array of hidden financial data. You can extract this data into adjacent columns in two ways.

Method 1: The UI Button

1. Click on cell A2 (Apple).
2. A small, square “Insert Data” button will appear floating near the top right corner of the cell. Click it.
3. A scrollable list of metrics will appear (Price, 52-week High, P/E Ratio, Market Cap, Number of Employees, etc.).
4. Click Price. The current stock price will instantly populate in cell B2.

Method 2: Dot Notation (The Pro Method)

You can interact with these Data Types using formulas, exactly like object-oriented programming.

1. Click in cell C2.
2. Type =A2. (Make sure to include the period).
3. The moment you type the period, an autocomplete menu will appear with all the available metrics.
4. Select [52 week high] and press Enter. The formula =A2.[52 week high] will calculate the value.

You can drag these formulas down the column to populate the data for the rest of your portfolio.

Step 4: Refreshing the Data

The stock market moves constantly, but Excel does not update these numbers every millisecond (to save CPU and network resources).

When you want to pull the absolute latest prices:

1. Go to the Data tab on the ribbon.
2. Click the large Refresh All button.

Excel will ping the server and instantly update every single metric in your spreadsheet to reflect current market conditions.

Limitations to Keep in Mind

While incredible, this feature has a few caveats. The data is provided by Refinitiv, not a direct exchange feed. Therefore, the quotes are typically delayed by about 15 minutes. It is perfect for building personal portfolio trackers, fundamental analysis models, and educational tools, but you absolutely should not use it to execute high-frequency algorithmic day trading.