Using STOCKHISTORY inside LET/LAMBDA for direct calculations

Jen LaVictoire 0 Reputation points
2026-09-16T06:58:50.0366667+00:00

I’ve been using the ⁠STOCKHISTORY⁠ function in Excel frequently, but my current workflow feels a bit clunky. Typically, I just dump the raw price and volume data into my spreadsheet and rely on a series of adjacent helper columns to run my calculations.

I would love to streamline this process and learn how to handle these computations in a more advanced, all-in-one way without cluttering my sheets. Specifically, I am wondering if anyone here has successfully built dynamic ⁠LET⁠ or ⁠LAMBDA⁠ formulas that use ⁠STOCKHISTORY⁠ as the foundational base data to instantly output advanced financial metrics inside a single cell. For example, I’m looking to dynamically calculate a 50-day or 200-day Simple Moving Average (SMA), extract daily returns, or compute annualized historical volatility entirely in memory.

Has anyone tackled this and managed with clean, nested array functions? I'd love to see examples of how you handle things like array offsets and trading-day alignments within the formula!

Microsoft 365 and Office | Excel | For business | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jade Ng 15,665 Reputation points Microsoft External Staff Moderator
    2026-09-16T07:47:00.16+00:00

    Dear Jen LaVictoire,

    Yes, you can use STOCKHISTORY inside LET or LAMBDA to perform calculations directly in Excel without first displaying all of the historical data in separate helper columns.

    For example, STOCKHISTORY can retrieve historical information as an array, while LET can assign names to intermediate calculations and reuse them within the same formula. If you want to reuse the calculation throughout the workbook, LAMBDA can also be used to create a custom reusable function.

    There are different ways to construct the formula depending on exactly what you want to return, such as a 50-day or 200-day Simple Moving Average (SMA), daily returns, historical volatility, or a complete dynamic array.

    For example, if you want to calculate the latest 50-day SMA directly from STOCKHISTORY in a single cell, you could use:

    =LET(data,STOCKHISTORY("XNAS:MSFT",TODAY()-120,TODAY(),0,0,1),prices,TOCOL(data),AVERAGE(TAKE(prices,-50)))

    User's image

    In this example:

    • STOCKHISTORY retrieves the daily historical closing prices for MSFT.
    • LET stores the returned historical prices as an in-memory array.
    • TAKE (prices,-50) selects the latest 50 trading observations.
    • AVERAGE calculates the 50-day SMA and returns a single result in the cell, rather than displaying the underlying historical data on the worksheet.

    The same concept can be adapted for a 200-day SMA, daily returns, annualized historical volatility, or packaged into a reusable LAMBDA function depending on your specific requirement.

    For additional details, I recommend referring to these Microsoft Support articles:

    Hope this helps. Please feel free to reply below if you have any update or further concern.  

    Thank you very much for your valuable time and your cooperation.

    If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

    2 people found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.