Dynamic Currency Conversion in Google Sheets

Question

I'm using Google Sheets to organize data from my global royalty statements. Currently I'm querying several tabs (one for each country) to produce a single table with results from all countries. As you can imagine, I don't want 125 Japanese Yen showing up in my charts and graphs as $125 USD (125 Y is equivalent to about $1.09 USD).

Since I receive my royalty statements in their respective currencies, I'd like to apply average conversion rates either during the query operation or after the fact. Since the table is being generated dynamically, the values won't always be the same, so I need some way to apply the conversion by searching the list of currencies on the fly. I've got a separate table on the same tab containing all the average conversion rates for each currency. Here's a sample of how this is set up:

So basically I just don't know how to say, in coding terms, "If this line item comes from the UK, divide the royalty amount by the UK exchange rate. If it comes from Canada, divide by the Canadian rate, etc."

Anyone have any insight as to how I might pull this off (or whether it's possible)? The actual table includes over 500 line items from a dozen different countries, so doing this by hand is something I'd like to avoid.

Answer 1

I believe you are looking for the GoogleFinance() function. You may want to set the Country to a pick list of the valid country entries so you could create the string for the conversion. I have not looked at many, but this will take a value in CA & and apply the exchange rate to convert it to the US $ Equivalent. The exchange rate in this case is an average of, I believe, the past 30 days.

=C2 * GoogleFinance("CURRENCY:CADUSD" , "average")

For your use, you can get the country code from row M if you change it to match what the formula is after, such as CAD for Canadian Dollars."

=C2 * GoogleFinance("CURRENCY:" & M2 & "USD" , "average")

Another option would be to create a lookup table and use VLOOKUP or some other function, depending on how you set up your table.