An interesting problem. Here’s how I’m thinking I would do it. I would add two new columns, one called ‘Tags’ (if you don’t already have one), the other called ‘RealAmount’.
The tags column can be used for all sorts of useful stuff, but in this case, I’d put “USD” in any rows that were US transactions (if you need to use other tags for other things, separating them with a comma would probably work depending on how the other function checks for tags). In the ‘RealAmount’ column I’d put the following formula:
=IF(REGEXMATCH(E2,"USD"),INDEX(GOOGLEFINANCE("CURRENCY:CADUSD", "price", A2),2,2)*D2,D2)
It does the following (assuming you’re columns are laid out as mine are in the image below):
- If the ‘Tags’ column does not contain ‘USD’ it will list the original amount.
- If the ‘Tags’ column does contain ‘USD’, it will look up the exchange rate using Google Finance for the date of the transaction, multiply it by the amount, and list the ‘Real’ amount. Not sure if I have the right exchange listed, you might have to use USDCAD depending on which way you need to convert.
You’d need to ‘fill’ the formula up/down the column for each new row as they are created (GoogleFinance doesn’t work in an Arrayformula, so can’t use that option),. You could then hide the ‘Amount’ column, and just show the ‘RealAmount’ column. Seems like it should do what you want, give it a try!