[SOLVED] Suppress date and column heading in googlefinance historical data

Suppress date and column heading in googlefinance historical data

I'm attempting to get a column of data which represents the highest stock price for a given stock beginning on an arbitrary date, through today. I have a column of ticker symbols in column A, and a purchase date in column B. I want to populate column C with the highest stock price for that stock since the purchase date.

Symbol	Purchase date	High since purchase
IBM	1/1/2023	(googlefinance formula)
TSLA	5/1/2023	(googlefinance formula)

I'm trying:

=GOOGLEFINANCE(a2,"high",b2,today(),"weekly")

or more literally:

=GOOGLEFINANCE("IBM","high","1/1/2020",today(),"weekly")

What that returns is two columns of data, one for date and the other for the requested value, each with column headers, with a separate row for each date and associated value. It looks like:

Symbol	Purchase date	High since purchase
IBM	1/1/2023	#REF
TSLA	5/1/2023	Date	High
		4/14/2023 16:00:00	191.58
		4/21/2023 16:00:00	189.69
		4/28/2023 16:00:00	165.65

It only processes the last ticker in the list, because the higher ones would overwrite the lower ones.

I don't need the date or the column headers; what I need is ONLY the highest value returned for the entire specified date range. I have a couple hundred different ticker symbols in column A, so I don't want to try to muck around with hidden rows and columns - I just need an answer in a single cell, like this:

Symbol	Purchase date	High since purchase
IBM	1/1/2023	147.18
TSLA	5/1/2023	191.58

I also tried a max() wrapper around the entire function, which does return a single value, but unfortunately it's always a date, because the number of days since the number of days since 1900 is pretty much always going to be more than any stock price, even Bitcoin.

Solution

You may try:

=map(A2:A,B2:B,lambda(a,b,if(a="",,max(choosecols(googlefinance(a,"high",b,today(),"weekly"),2)))))