Can Excel STOCKHISTORY Retrieve Historical Bond Yields?
Question details
The user wants to know if they can retrieve historical yield data for bonds, such as 10-year U.S. Treasury bonds, alongside price data using the STOCKHISTORY function in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Pulling historical financial data for bonds over a specific range of dates to display both prices and yields for analysis.
- Observed behavior
- Excel's STOCKHISTORY function successfully retrieves open, close, high, low, and volume price data for stocks and bonds, but it does not output historical bond yields as a separate data field.
Verify that you are using a Microsoft 365 subscription, as the STOCKHISTORY function is not available in standalone versions of Excel like 2019 or 2021.
Import Historical Bond Yields From External Financial Sources
Since STOCKHISTORY does not natively provide historical yield fields for bonds, you must import this data from a reliable third-party financial source and merge it with your Excel price data.
Excel's financial data provider focuses primarily on equities and standard price metrics. To analyze historical bond yields over a date range, you need to pull data from fixed-income databases like FRED (Federal Reserve Economic Data), Yahoo Finance, or similar financial portals.
Visit a financial website that offers historical bond data (such as FRED for U.S. Treasuries) and locate the specific bond yield data you need.
Download the historical yield data for your desired date range as a CSV file.
In Excel, go to the 'Data' tab, click 'From Text/CSV', select your downloaded file, and click 'Load' to bring the yield data into a new worksheet.
Use the XLOOKUP or VLOOKUP function in your main worksheet to match the dates from your imported yield table with the dates generated by your STOCKHISTORY bond price formula.
Check Current Bond Yields Using Excel Data Types
If you do not strictly require historical data and only need the present-day yield for a bond, you can use Excel's built-in Stocks Data Type feature.
Use WPS Office for Financial Data Management
Advanced array functions like STOCKHISTORY require a paid Microsoft 365 subscription. If you rely on importing CSV datasets from external financial sources to calculate bond yields, WPS Office offers a completely free, highly compatible alternative with powerful lookup functions and seamless data handling.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and complete the installation on your device.
- 2. Open Your Financial Workbook: Launch WPS Spreadsheet and open your existing .xlsx financial tracking file.
- 3. Import and Analyze: Use the Data tab to import CSV yield data from external sources and merge it using built-in lookup functions.
Frequently Asked Questions
What data fields does the STOCKHISTORY function actually support?
Currently, STOCKHISTORY can only return Date, Close, Open, High, Low, and Volume. Yield and other advanced fundamental metrics are not available as historical array outputs.
Why does STOCKHISTORY return a #VALUE! error for my bond ticker?
This usually happens if the bond ticker is not recognized by Excel's financial data provider (Refinitiv), or if the requested start date precedes the historical data available for that specific fixed-income security.
Is there any add-in that can pull historical bond yields directly into Excel?
Yes. Professional financial add-ins such as the Bloomberg Terminal add-in or Refinitiv Workspace can pull historical fixed-income yields directly into spreadsheet cells, though these require expensive premium subscriptions.
