How to Get Company Stock Data in Excel (Data Types & STOCKHISTORY)
Question details
The user wants to extract and retrieve financial stock data, including real-time prices and historical market trends, for a specific company in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking financial markets, analyzing company stocks, or building investment portfolios.
- Observed behavior
- Excel does not have a basic STOCK function, but users can access real-time metrics using the Stocks data type and retrieve historical prices with the STOCKHISTORY function.
Ensure you have an active internet connection to retrieve live financial data and verify that you are logged into an eligible Microsoft 365 account, as these stock features are premium cloud-backed services.
Convert Text to the Stocks Data Type for Real-Time Metrics
Use Excel's linked Stocks data type to instantly pull real-time information such as current price, 52-week high/low, and exchange details.
Excel's linked data types allow you to convert plain text like a company name or ticker symbol into a rich data object connected to external financial databases.
Type the company name, ticker symbol (e.g., MSFT or AAPL), or fund name into a blank cell in your spreadsheet.
Select the cell containing the text, navigate to the 'Data' tab on the ribbon, and click 'Stocks' in the Data Types group.
Click the small 'Insert Data' icon that appears next to the selected cell, then choose the desired metric from the dropdown list, such as 'Price', 'Change', or 'Exchange'.
Extract Historical Market Data using STOCKHISTORY
Generate an array of historical stock prices over a specific date range using the built-in STOCKHISTORY function.
Manage Your Financial Portfolios with WPS Office
While live stock data types are exclusive to Microsoft 365 subscriptions, WPS Office provides a powerful, free alternative for financial modeling. Import your stock CSV data easily, use advanced charting to track trends, and enjoy full compatibility with Excel formats without the premium subscription costs.
- 1. Download stock data: Download your required historical stock CSV data from free financial websites like Yahoo Finance or Google Finance.
- 2. Import into WPS: Open WPS Spreadsheets and go to the 'Data' tab, then click 'Import Data' to load your stock prices.
- 3. Analyze and visualize: Use WPS Spreadsheets' rich charting tools and financial functions to build your customized investment dashboard.
Frequently Asked Questions
Why is the Stocks data type missing in my Excel?
The Stocks data type is a premium feature available only to Microsoft 365 subscribers. If you are using Excel 2019, 2016, or older standalone versions, this feature will not be visible on the Data tab.
How often does the Excel Stocks data update?
Stock data in Excel is updated in near real-time, but it can be delayed by about 15 minutes depending on the exchange. You can manually refresh the data by going to the Data tab and clicking 'Refresh All'.
Can I use the STOCKHISTORY function without a Microsoft 365 subscription?
No, the STOCKHISTORY function relies on cloud-based financial data services and is exclusively available to Microsoft 365 users who have an active internet connection.
Why am I getting a #BUSY! or #VALUE! error with STOCKHISTORY?
A #BUSY! error means Excel is still downloading the data from the server. Wait a moment for it to load. A #VALUE! error usually indicates an invalid ticker symbol, an unsupported exchange, or an incorrect date format in your formula parameters.
