How to integrate GOOGLEFINANCE results in an array formula?

user_797492 0
Viewed 1291

Following ARRAYFORMULA is giving me an error "parameter 1 value is invalid."

=ARRAYFORMULA(IF(ISBLANK(B2:B), "",  GOOGLEFINANCE(B2:B, "price")))

B column of my sheet contains the stock symbols whose value I wish to fill in my sheet. Example below

Company Name
GOOG
ADBE
MSFT

Can someone help here?

google-sheets-formula google-finance google-sheets
2 Answers

LAMBDA and friends are available, so this works:

=BYROW(A2:A, LAMBDA(row, IF(row = "",, GOOGLEFINANCE(row, "price"))))

enter image description here

Old story:

GOOGLEFINANCE cannot be used in array formulas. You'll have to extend your formula downwards.

For example this formula should be in every cell from C2 and down:

=IF(NOT(ISBLANK($B$2:$B)), GOOGLEFINANCE($B$2:$B, "price"), "")

enter image description here

user_2798060

This questions seems to be a duplicate of Google finance as an argument for ArrayFormula. I have answered it with more detail. As a new user I'm only able to post an answer here, I cannot comment/flag as duplicate.

Anyway, assuming stock symbols are in range B2:B, then you put the following formula in cell C2:

=IFERROR(MAP(B2:B,LAMBDA(company,GOOGLEFINANCE(company,"price"))))

This is a new method since the introduction of LAMBDA and its helper functions in Google Sheets in August 2022.

The trick here is, as far as I understand, that MAP(LAMBDA) calculates the specified formula for each row in the input array separately (effect similar to manually expanding the formula over the whole range), whereas ARRAYFORMULA passes the whole array as an argument to the formula (GOOGLEFINANCE is special and doesn't work intuitively with such input).

user_200146330
Related