The current formula returns 2 columns and multiple rows. I want to store the result in a single "data cell" for future reference and post-processing.
Is it possible to make sure that the original formula doesn't expand into multiple cells? Is it possible to force it to remain in just one cell as a "data cell" for future reference by queries without refetching the data again over the network?
=GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31))
My goal is to avoid 3 different network calls for something like:
=AVERAGE(GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)))
=MIN(GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)))
=MAX(GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)))
you can compact it like:
=TEXTJOIN(, 1, INDEX(LET(a,
GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)), a&{"×", "¤"})))
and then unpack it as:
=INDEX(SPLIT(FLATTEN(SPLIT(D6, "¤")), "×"))
but no idea why this would benefit anything
=LAMBDA(a, REDUCE({"avg","min","max"}, a, LAMBDA(b, c, {b;
IFERROR(QUERY(QUERY(GOOGLEFINANCE(c, "price", DATE(2024,1,1), DATE(2024,1,31)),
"select avg(Col2),min(Col2),max(Col2)"), "offset 1", ), {"","",""})})))
(A2:A4)