How to Use BYROW with STOCKHISTORY to Get Latest Stock Prices
Question details
The user needs to extract the latest historical stock value for multiple ticker symbols at once, as the STOCKHISTORY function does not natively output independent calculations when receiving a spilled array of symbols.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Retrieving the most recent daily closing price for a dynamically spilled range of stock symbols.
- Observed behavior
- When STOCKHISTORY receives a spilled range of symbols, it fails to evaluate a separate historical price for each symbol automatically, requiring a row-wise iterative function to process the array.
Ensure your spreadsheet software is updated to support dynamic array formulas (like BYROW, LET, and LAMBDA) and verify you have an active internet connection to fetch real-time financial data.
Combine LET, BYROW, and TAKE with STOCKHISTORY
Wrap the STOCKHISTORY function inside BYROW and LAMBDA to process each stock symbol individually, then use the TAKE function to isolate the most recent price.
Because STOCKHISTORY is designed to generate a 2D array of historical data for a single symbol, passing a spilled array of multiple symbols directly causes it to fail. To fix this, you can use BYROW to iterate through the list of symbols one at a time. Within that iteration, the TAKE function extracts the very last row of the generated history, giving you the latest available trading price.
Start your formula with =LET(end_date, TODAY(), start_date, end_date-10, ...) to establish a recent 10-day window. This ensures you capture the latest trading day even over weekends or holidays.
Use BYROW(E4#, LAMBDA(symbol, ...)) to cycle through your spilled range of stock tickers (for example, cell E4#).
Inside the LAMBDA, input TAKE(STOCKHISTORY(symbol, start_date, end_date, , 0, 1), -1). This retrieves the history without headers and isolates the final row, which is the latest price.
Combine the elements into your target cell: =LET(end_date,TODAY(),start_date,end_date-10,BYROW(E4#,LAMBDA(symbol,TAKE(STOCKHISTORY(symbol,start_date,end_date,,0,1),-1))))

Master Advanced Formulas with WPS Office
While specific linked data types like STOCKHISTORY require a paid Microsoft 365 subscription, WPS Spreadsheet provides a highly capable, free alternative. It supports modern dynamic array capabilities, complex financial formulas, and custom LAMBDA functions to meet your most demanding data analysis needs seamlessly.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx workbooks with perfect formatting and formula retention.
- 3. Use Advanced Dynamic Arrays: Leverage powerful functions like BYROW and LET to perform complex data analysis without a premium subscription.

Frequently Asked Questions
Why does STOCKHISTORY return a #VALUE! error when given a spilled array?
STOCKHISTORY is inherently built to process one stock symbol per formula call, outputting its own 2D array. When you pass a spilled array of symbols directly, the function cannot natively nest multiple arrays into a single output without an iterative wrapper like BYROW.
Why use TAKE with a -1 argument instead of requesting a specific date?
The stock market is closed on weekends and public holidays. By pulling a brief historical window (like 10 days) and using TAKE with a -1 argument, you guarantee the formula returns the closing price of the most recent valid trading day, preventing errors.
What role does the LAMBDA function play in this formula?
The LAMBDA function acts as a bridge for BYROW. It defines a custom, temporary parameter (named 'symbol' in this case) so that BYROW knows exactly where to plug in each ticker symbol from the spilled array as it iterates through them.




