6

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?

Varun Bhatia
  • 4,326
  • 32
  • 46
  • Can you show an example of the values you have in B2:B? – Jescanellas Apr 10 '20 at 11:41
  • @Jescanellas added the values – Varun Bhatia Apr 10 '20 at 12:31
  • That's odd, because I used the same values and formula and it returns 1211.45, without any errors. Try with another Sheet in incognito mode and see how it goes. – Jescanellas Apr 10 '20 at 14:05
  • didn't work for me..... – Varun Bhatia Apr 10 '20 at 20:30
  • 1
    `GOOGLEFINANCE` is not supported in array formulas. – kishkin Apr 12 '20 at 12:00
  • @kishkin I am not disagreeing with you. But I was wondering if you had a link to back that up? – DarcyThomas Mar 07 '21 at 23:54
  • 1
    @DarcyThomas sorry, no link to back that up. Google lacks (or I totally missed that) a good deal of details in Sheets formulas docs. One of those details would be the ability of some functions to work inside of `ARRAYFORMULA` (`GOOGLEFINANCE`, `FILTER`, `INDEX`, etc.) So, the knowledge is from experimenting. And it is a common knowledge now, I've seen a lot of answers like this. "Not working inside `ARRAYFORMULA`" means that the function won't be called for every element `ARRAYFORMULA` iterates over. Though a function's results (a single value or a range) could be used in `ARRAYFORMULA`. – kishkin Mar 09 '21 at 09:29
  • 1
    @DarcyThomas I just checked if `GOOGLEFINANCE` works in `ARRAYFORMULA` once again, just in case. As of now it does not. But that could change with time. – kishkin Mar 09 '21 at 09:30
  • Does this answer your question? [Google finance as an argument for ArrayFormula](https://stackoverflow.com/questions/60874426/google-finance-as-an-argument-for-arrayformula) – vilc Sep 20 '22 at 06:01

2 Answers2

3

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).

vilc
  • 183
  • 1
  • 8
2

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

kishkin
  • 5,152
  • 1
  • 26
  • 40