Thank you for the great explanation.
Yes, we try to use the CAD bank account as much as possible but there are items that we have to pay in USD, thus the problem.
It looks like there is no real solution to my problem beyond converting from USD (home currency) to CAD (foreign currency) manually and running the reporting out of excel. UGH.
Thank you for your input.