How to use Googlefinance function to calculate exchange rates for different currencies and not error if converting to from same currency

Question

I have a column of currencies & income and I want to convert all of those to a common currency. In my case USD. Many of the rows are already in USD. In that case 'CURRENCY:USDUSD' returns no data. I expected CURRENCY:USDUSD to return 1, it returns #N/A

Here is my solution to the problem.

Currency Today's rate Rate from USD CAD =if(A2="USD",1, (index(GoogleFinance("CURRENCY:"&"USD"&A2,"price",today()-2,today(),"daily"),2,2)))

This returns Today's rate, from USD to B1 (CAD), as a single cell. If CAD is replaced with USD, it returns 1.

Currency	Today's rate Rate from USD
CAD	1.379955
USD	1

Answer 1

Put a 3 letter currency code in A2

in B2

=if(A2="USD",1, (index(GoogleFinance("CURRENCY:"&"USD"&A2,"price",today()-2,today(),"daily"),2,2)))

Answer 2

USD to USD will always be one. I've create a simple spreadsheet with the solution: https://docs.google.com/spreadsheets/d/1pdiUrqU6HPEoQNpoIpuYZA3VDQRek8-OFstvTrOaeCw/edit#gid=0

You gonna need two formulas:

=concat("USD",A2)

Will display "USD[Currency]", for example, if A2 has the value CAD, the formula will display "USDCAD". If it's USD, will be USDUSD.

After that, you gonna use this second formula, with a conditional statement:

=if(B2="USDUSD",1,googlefinance(B2))

Where B2 is where the first formula is. Suppose the B2 value is "USDCAD". The result will be the currency exchange rate. But if the B2 value is USDUSD, due to the if statement will display the value of one, before calling the googlefinance function

Print