Function Problems

How to Use BYROW with STOCKHISTORY to Get Latest Stock Prices

Khadija KhanKhadija Khan Nov 10, 2026 870 views

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.

How to Use BYROW with STOCKHISTORY to Return the Latest Price for Each Symbol
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.
Before you start

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.

Solution 1Recommended

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.

1
Define the date range using LET

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.

2
Iterate through the symbol array

Use BYROW(E4#, LAMBDA(symbol, ...)) to cycle through your spilled range of stock tickers (for example, cell E4#).

3
Fetch the history and isolate the latest price

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.

4
Apply the complete formula

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))))

Combine LET, BYROW, and TAKE with STOCKHISTORY
Dynamic Array Spilling: Once you press Enter, this formula will automatically spill downwards, keeping the latest prices perfectly aligned with your original spilled array of ticker symbols.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx workbooks with perfect formatting and formula retention.
  3. 3. Use Advanced Dynamic Arrays: Leverage powerful functions like BYROW and LET to perform complex data analysis without a premium subscription.
100% compatibility with Microsoft Excel (.xlsx) file formatsNatively supports advanced dynamic arrays, LET, LAMBDA, and BYROW functionsFree, lightweight, and features a familiar user interface for seamless migration
microsoft office alternative - wps office

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.