'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Get FTSE 100 Data and Additional Stock Fields in Excel
Question details
The user needs to retrieve FTSE 100 (UKX) index data and specific financial fields like dividend yields, declaration dates, and payment dates that are missing from Excel's built-in Stocks data type.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing UK stock markets and dividend histories using built-in data types in Excel.
- Observed behavior
- The native Stocks data type fails to return FTSE 100 index data and lacks specific financial fields such as ex-dividend dates and payment dates.
Ensure you have a stable internet connection, as pulling external stock data and utilizing Power Query requires online access.
Import FTSE 100 Data using Power Query from Web Sources
Use Power Query to pull live or historical index price tables directly from public financial websites when the built-in Stocks data type fails.
Excel's Stock data relies on a specific financial-data provider (Refinitiv), which currently limits certain indices and fields. You cannot add missing fields manually to the native tool, but you can bypass this by importing web data.
Find a reliable public financial website that displays FTSE 100 (UKX) index prices or dividend tables, and copy the page URL.
Open your Excel workbook, navigate to the 'Data' tab on the ribbon, and click on 'From Web'.
Paste the copied URL into the dialog box and click 'OK'. Excel will establish a connection to the website.
In the Navigator window, select the table that contains the financial data you need, then click 'Load' to import it into your spreadsheet. You can click 'Refresh All' in the Data tab anytime to update the figures.
Connect to Third-Party Financial APIs for Advanced Fields
Fetch advanced financial fields such as dividend yields and ex-dividend dates using third-party APIs like Yahoo Finance or Alpha Vantage.
Submit Feedback for Native Data Support
Request Microsoft to natively add the FTSE 100 index and missing dividend fields to the Stocks data type.
A Lightweight, Free Alternative for Financial Data Analysis
Since Excel's native Stocks data type restricts missing indices and financial fields from being added manually, consider using WPS Office. It provides a lightweight, highly compatible spreadsheet environment that easily handles external data imports and CSV exports from financial platforms—completely free of charge.
Frequently Asked Questions
Why doesn't Excel's Stock data type show FTSE 100?
Excel relies on a specific data provider (Refinitiv) for its native Stocks data type. Certain indices like the UKX/FTSE 100 may not be licensed or fully available in the provider's current data feed.
Can I manually add missing fields to the Stocks data type?
No, you cannot manually append data directly to the native Stocks data type objects. The fields are hardcoded to the data provider's feed. You must use external web queries or APIs in adjacent columns to fetch unsupported fields.
Are there free APIs to get UK stock market data in Excel?
Yes, financial data providers like Alpha Vantage offer free tiers that include global stock market data. You can easily connect these services to Excel using the Data > From Web (Power Query) feature.
