This is a great workflow to hear about. The GoogleFinance formula is really helpful for currency conversions.
The IMPORTRANGE is one option for pulling everything together.
Another would be to put all your connected accounts USD and CAD into one spreadsheet. Then create a new “CAD Amount” column for your original CAD transactions. Periodically cut/paste the Amount for any new Canadian transactions into this column (made easier if you filter your sheet to only show Canadian banks, then do a batch cut and paste). Then use the GOOGLEFINANCE formula to put the USD value back into the empty Amount column cells.
One note with GOOGLEFINANCE. If it’s running and updating many cells, it could take performance hit to the sheet. If this happens you could copy then “paste as values” some of your entries that are now calculated and don’t always need recalculating.
Hope that helps. We are watching this and considering when and how we want to more fully support international currencies. Thanks for chiming in here!