Google Finance doesn't recognise my date field and is returning an error message

Question

I'm having an issue with the following formula in google sheets:

=IFS(F2<> "EUR", E2*GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2), F2 = "EUR", E2)

H2 is a cell which holds a date. I am trying to get a formula I can use for multiple different dates. I got the following error message:

Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number.

According to my research the formula has these constraints:

=GOOGLEFINANCE("Currency:USDGBP", "price", DATE(YYYY,MM,DD), DATE(YYYY,MM,DD)

Where the first nested DATE function is the start date, and the second DATE function is the end date. And they are optional.

I tried Date(H2) and I got this error message:

Wrong number of arguments to DATE. Expected 3 arguments, but received 1 arguments.

Thank you in advance!

Answer 1

I've tested your function and the error Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number is due to this part E2*GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) in your IFS function.

The return value of the GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) is an array (it contains strings and numbers) and multiplying it to E2 value is not possible. Also, if running GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) alone works just fine, then the H2 (the date cell) isn't the main cause of the error.

RECOMMENDED SOLUTION:

I've checked on how to only return price instead of an array on GOOGLEFINANCE function and stumbled upon an answer from How can I get GOOGLEFINANCE to return only the historical price of a stock and not an array?.

Instead, you can try this function below:

=IFS(F2<>"EUR", min(GoogleFinance("Currency:"&F2&$G$1, "PRICE", H2))*E2, F2 = "EUR",E2)

Here's a sample test on my end:

Answer 2

any time GoogleFinance() reutrns a historical array, you need to INDEX() it to get just the single answer.

It's almost always the second row and second column of the array that you want.

So:

=INDEX(Goooglefinance(.... ), 2, 2)