Search code examples
excelfunctionexcel-formulalambda

Excel - Stockhistory + VSTACK

I am working on STOCKHOSTORY function to pull stock data of 50+ stocks/tickers ; then i am trying to put them into power query. Issue 1: Every stock history array is placed after certain interval of columns eg: A:D , E:H ...

I have thought of using VSTACK (to put them one below other and then easily importing to PQ) but i am not sure how to vstack different array without hardcoding all arrays in 1 formula. Can we use LAMBDA recursive to achieve the same?

Image of arrays I have added an image of arrays

This is with the VSTACK formula This is with the VSTACK formula

STOCKHISTORY(A1,TODAY()-10,TODAY(),0,1,0,1,2,3,4)

VSTACK(A2#,G2#,M2#,S2#,Y2#)
Solution

  • You need REDUCE for this:

    =LET(shares,A1:A3,
λgetStockData,LAMBDA(a,STOCKHISTORY(a,TODAY()-10,TODAY(),0,1,0,1,2,3,4)),
DROP(IFERROR(
    REDUCE("",shares,LAMBDA(result,share,VSTACK(result,share,λgetStockData(share))))
,""),1))

    enter image description here

    I put the relevant shares in A1:A3.