Search code examples
google-sheetscurrencygoogle-finance

How to calculate currency conversion for ZARCAD for a specific date with GOOGLEFINANCE?


I have a Google sheet that I use multiple currencies in. I purchase a product on a specific date and I want to lock in the conversion for that date. However, I have not been able to do that with all the different formulas that I have tried. I keep getting an error. Here is the formula that I am using. =J283*GOOGLEFINANCE("ZARCAD","price",date(2021,4,9),date(2021,4,11)) Column J is the column with the price in the currency of purchase. I need to convert it into Canadian dollars. It works if I want the current price of the currency, but not for the historical data. The error is: Error Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number. How can I fix this issue?


Solution

  • The formula you are using creates a 2D array as below: enter image description here

    In order to be able to use a function that expects number values (like MULTIPLY), you have to index a part of the 2D array that is a number.

    For example, to multiply the first "Close" value of 0.08575243, you have to tell the fomula to use that value for the calculation with the built-in INDEX function:

    =INDEX(GOOGLEFINANCE("ZARCAD","price",date(2021,4,9),date(2021,4,11)),2,2)
    

    If you wish to specify a different part of the array, just change the 2,2 (row, column) at the end to any numbers that point to the value in the array that you wish to use. Here, you can find the documentation for INDEX.