STOCKHISTORY Function in Excel
The STOCKHISTORY function retrieves historical stock prices and trading data from Microsoft's financial sources for Excel 365 analysis.
Excel
Google SheetsExcel=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], ...)Quick Answer
STOCKHISTORY function STOCKHISTORY function retrieves historical stock prices and trading data from Microsoft's financial sources in Excel 365. It returns a dynamic array with date, open, high, low, close prices and volume for any publicly traded security.
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers])- Basic syntax: `=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers])` Example: `=STOCKHISTORY("MSFT", TODAY()-365)` gets one year of Microsoft stock data with daily prices and volume automatically.
Practical Examples
Basic Historical Stock Data Retrieval
Get one year of daily closing prices for Microsoft stock
Monthly Data for Long-Term Analysis
Retrieve 5 years of monthly stock data for trend analysis
Multiple Stocks for Portfolio Tracking
Track multiple stocks simultaneously with ticker symbols in column
Custom Data Properties - Close Price and Volume Only
Retrieve only closing prices and volume to reduce data size
Integration with Financial Calculations
Calculate average daily return using STOCKHISTORY with formulas
Common Errors and Solutions
Unable to establish connection to data source
No internet connection, Microsoft 365 services down, or Connected Experiences disabled in Excel settings
1. Verify internet connection is active 2. Check Excel Options > Trust Center > Privacy Settings > 'Allow Connected Experiences' 3. Ensure Microsoft 365 subscription is active and signed in 4. Try refreshing data with Ctrl+Alt+F5 5. Wait a few minutes if Microsoft's data services are experiencing issues
Keep Connected Experiences enabled in Excel settings and maintain stable internet connection
Example:
Invalid stock symbol or ticker not recognized
Ticker symbol misspelled, stock delisted, or symbol not available in Microsoft's financial database
1. Verify correct ticker symbol on financial website (Yahoo Finance, Google Finance) 2. Try ISIN number instead of ticker symbol for international stocks 3. Check if stock is actively traded (not delisted or suspended) 4. Use standard ticker format without exchange suffix (MSFT not MSFT.NASDAQ) 5. For ETFs and mutual funds, verify symbol is supported
Double-check ticker symbols before using and maintain reference list of valid symbols
Example:
Function results would overwrite existing data
Dynamic array output blocked by data in cells below or to the right of formula cell
1. Clear all cells to the right and below where formula is placed 2. Move formula to area with more empty space 3. Delete or move blocking data to different location 4. Use smaller date range to reduce array size 5. Filter results to fewer rows/columns
Always place STOCKHISTORY formulas in cells with ample empty space to right and below (at least 20 rows × 10 columns)
Example:
Invalid date format or parameter value
Date parameters not recognized as valid dates, interval value outside 0-2 range, or headers value not 0 or 1
1. Use DATE(year, month, day) function for dates instead of text strings 2. Verify interval is 0 (daily), 1 (weekly), or 2 (monthly) only 3. Check headers is either 0 (no headers) or 1 (with headers) 4. Ensure start_date is before end_date 5. Avoid future dates that don't have data yet
Always use DATE() function or cell references with date formatting, not text strings
Example:
Data unavailable for specified date range or stock
Requested date range predates available data, stock wasn't trading during period, or data temporarily unavailable
1. Check if stock existed/was public during requested date range 2. Verify stock was actively trading (not halted or suspended) 3. Try shorter date range closer to present day 4. For new IPOs, start from listing date forward 5. Wait and retry if data service is temporarily unavailable
Research stock trading history before requesting data for historical periods. Most stocks have data from 2000 onward, but coverage varies by exchange and stock
Example:
Best Practices and Advanced Techniques
Optimize Performance with Calculated Columns
Instead of using multiple STOCKHISTORY calls, retrieve data once and use INDEX, CHOOSECOLS, or dynamic array formulas to extract specific columns. This reduces data fetches and improves workbook speed significantly, cutting API calls by 80% and improving calculation speed by 10x.
Use Named Ranges for Stock Symbols
Create named ranges for frequently used stock symbols and date ranges. This makes formulas more readable, easier to update, and reduces errors from typos in ticker symbols. Update multiple formulas by changing a single named range.
Combine with FILTER for Date-Specific Analysis
Use FILTER function to extract only specific dates, months, or years from STOCKHISTORY results. Perfect for quarter-end reporting or fiscal year analysis without changing the base formula. This enables dynamic filtering without manual data manipulation.
Data Refresh Timing Considerations
STOCKHISTORY updates during market hours but may lag by 15-20 minutes. Current day data may be incomplete until market close. Don't rely on real-time data for trading decisions. Use end_date of TODAY()-1 for guaranteed complete daily data.
International Stock Symbol Format
For non-US stocks, use ticker symbols with exchange identifiers or ISIN codes. Format varies by exchange: London Stock Exchange uses .L suffix (BP.L for BP plc), Tokyo uses .T (7203.T for Toyota), Germany uses .DE (DAI.DE for Daimler). ISIN codes (12-character alphanumeric) work universally across exchanges.
Create Self-Updating Dashboards
Build investment dashboards that automatically update by using TODAY() in formulas and setting interval to match your analysis frequency. Combine with charts for visual tracking. This creates zero-maintenance reporting that opens fresh every day. Use line charts for price trends, column charts for volume, and combo charts for price + volume correlation.
Need Help with STOCKHISTORY Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
AMORLINC calculates linear depreciation for each accounting period using French accounting standards, ideal for period-specific asset depreciation.
ExcelCalculate maturity values of discount securities with Excel's RECEIVED function. Complete guide with examples and solutions for Treasury Bills.
Excel