Data Import & Export

How to Fix Excel Stock Codes for FTSE 100 and Commodity Indexes

Ayan Masood Oct 16, 2026 868 views

Question details

The user needs the correct stock symbols and exchange identifiers for indexes like the FTSE 100 and commodities in Excel to retrieve accurate financial data without triggering errors.

How to Fix Excel Stock Codes for FTSE 100 and Commodity Indexes

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using the Excel Stocks data type to pull historical or current market data for specific indexes like the FTSE 100, gold, silver, or crude oil.
Observed behavior: Excel sometimes resolves the symbol to the wrong exchange (e.g., XPAR instead of LSE for UKX), resulting in incorrect numeric values or a #NAME? error.

Before you start

Ensure you have an active internet connection and are using a Microsoft 365 subscription, as the built-in Stocks data type requires it to fetch live market data from online providers.

Solution 1Recommended

Explicitly Specify the Exchange Identifier

Prevent Excel from defaulting to the wrong regional exchange by explicitly stating the exchange code (like XLON or LSE) alongside the ticker symbol.

When typing ambiguous symbols like 'UKX' for the FTSE 100, Excel may automatically map it to the Paris exchange (XPAR:UKX) instead of London, causing the returned index values to be drastically incorrect. Specifying the exchange identifier forces Excel to query the correct dataset.

Select the target cell

Click on the cell in your workbook where you want to input the stock index.

Input the specific exchange and ticker

Type the ticker symbol prefixed with the correct exchange identifier. For the FTSE 100 in London, type XLON:UKX or LSE:UKX instead of just UKX.

Convert to Stocks data type

Go to the Data tab on the Excel ribbon and click on 'Stocks' in the Data Types group to convert your text.

Extract the required metrics

Click the 'Insert Data' icon that appears next to the converted cell to extract specific metrics like Price, 52-week High, or Exchange abbreviation.

Explicitly Specify the Exchange Identifier

Using STOCKHISTORY Formula: If you are using the STOCKHISTORY formula directly, write it as =STOCKHISTORY("XLON:UKX", "1/1/2022", TODAY()) to ensure the correct exchange is queried seamlessly.

Solution 2

Import Stock Data from Yahoo Finance via Web Query

If the native Stocks data type fails or is unsupported in your Excel version, you can import the data directly from Yahoo Finance using the Get Data feature.

WPS Spreadsheet Solution

Import Financial Data from the Web Using WPS Spreadsheet

If you are struggling with proprietary data type errors or do not have a premium Microsoft 365 subscription, WPS Office offers a powerful, free alternative. You can easily pull live or historical stock data for the FTSE 100 and commodities directly from financial websites using the Data Import feature in WPS Spreadsheet.

1. Open WPS Spreadsheet: Launch WPS Office, create a new Spreadsheet, and click on the 'Data' tab in the top ribbon.
2. Select Import External Data: Click on 'Import Data' and then choose 'Import External Data' to open the web query dialog box.
3. Enter the financial website URL: Paste the URL of a financial data site (such as Yahoo Finance's FTSE 100 historical data page) and click 'Go'.
4. Import the table: Select the specific data table you wish to extract by clicking the yellow checkmark next to it, then click 'Import' to load the financial data into your worksheet.

Completely free and fully compatible with Microsoft Excel (.xlsx) formatsLightweight application that runs smoothly and efficiently on all devicesEasily import live financial tables via robust external web queries

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel stock code return a #NAME? error?

The #NAME? error typically occurs if the Stocks data type feature is not available in your version of Excel (it requires an active Microsoft 365 subscription), if your formula is misspelled, or if a required symbol is entered without the correct quotation marks in formulas like =STOCKS().

How do I find the correct exchange abbreviation for commodity indexes?

You can find supported exchange abbreviations by searching the financial instrument on the official Refinitiv or Microsoft data providers' sites. Common examples include XLON or LSE for the London Stock Exchange, and XNYM for the New York Mercantile Exchange.

Why is the FTSE 100 returning a value of 89 instead of over 7800?

Excel may be automatically defaulting to the wrong regional exchange for the 'UKX' symbol, such as the Paris exchange (XPAR:UKX), which represents a different security entirely. Prefixing the symbol with 'XLON:UKX' forces Excel to pull the correct index data from London.

Excel & Data Problems