Currency conversion and exchange rate in FileMaker

Written By Stuart Moscoe

Running a business takes time and effort. The time it takes to manage the billing and payments for a consulting business, take away from billable hours. Automating as much of these tasks as possible are key to maximizing your earnings. When I started my consulting business, I had to find a simple time billing software. I looked at online accounting and billing systems but they were either too expensive of too complex for my needs. I could have used an excel spreadsheet, but excel is limited compared to a database system. I decided to use the Time Billing template that comes with FileMaker Pro.

My consulting is performed remotely, due to the pandemic. This allows me to provide my service to customers in other countries. Since I am in Canada, payments in foreign currencies, such as US dollars, need to be converted to Canadian dollars for accounting and tax purposes. Even if I keep the funds in a US dollar bank account, the Canada Revenue Agency (CRA) requires that the business report the funds in Canadian dollars using the daily exchange rate on the day of transaction or the average annual exchange rate. The Bank Of Canada has tools and an API on their website for finding the exchange rate.

https://www.bankofcanada.ca/rates/exchange/
https://www.bankofcanada.ca/valet/docs

I decided to build a script in my FileMaker Time Billing application that would query the exchange rate by date, and would then convert the customer payment to Canadian Dollars for tax purposes. It uses the paid date as the date for the query.

 With all software, testing is key to finding bugs. I noticed that on rare occasions, an exchange rate was not found. I then realized that the dates in those cases were holidays in Canada. The Bank of Canada does not publish exchange rates on holidays or weekends. So I altered my script to find a seven day date range and then use the exchange rate from the closest preceding day to my payment date. If my payment receipt occurred on July 1 (Canada Day), then the script would use the exchange rate from June 30.  This follows the rule in the Canada Income Tax Act.

 FileMaker is great for quick development to solve small problems. By integrating the script into my Time Billing application and attaching it to a button, one click gets the exchange rate and converts the payment to Canadian dollars. While this only saves me 1-2 minutes for each transaction, it helps prevent context switching, which can be worse for productivity. As you know, once you open a browser, you may be tempted to surf the web, and before you know it, an hour has gone by.

 I decided to build and share a FileMaker application file to query the Bank Of Canada API and get the exchange rates for the required date or date range. The API returns data in JSON, CSV or XML format. The file is compatible with FileMaker 18 and higher. It will run on Mac, Windows, iPad and iPhone.

Please fill out the form to get the FREE file.

Currency Converter main layout FileMaker file

The file has no password and uses "Admin" as the username. Feel free to use the file as needed.

 If you need help integrating this file into your own solution or other FileMaker work, please contact me.

 Currency Conversion Information

 Bank of Canada foreign currency conversion to and from the Canadian Dollar for accounting and tax purposes.

 The data is accessed from the Bank of Canada API at https://www.bankofcanada.ca/valet/docs

Conversion Notes

 1. Exchange rates are available for dates starting January 3, 2017. Exchange rates prior to 2017, are not available in the API. They are available for download in the Historical Noon and Closing Rates section.

https://www.bankofcanada.ca/rates/exchange/legacy-noon-and-closing-rates/

 2. The data is sorted by date ascending  by default. The data can be sorted by date descending by adding the parameter, order_dir=desc

 3. 26 foreign currencies can be converted to Canada Dollar using an average Daily, Monthly or Annual rate.
Please see the list of supported foreign currencies.

 4. Canada Dollar conversion to foreign currency can only use the Daily conversion rate.
Monthly and annual averages are not available to convert CAD to a foreign currency.

 5. A foreign currency cannot be converted to another foreign currency directly.
To do this, the foreign currency would have to be converted to CAD and then the CAD can be converted to a different foreign currency. (ie. USD -> EUR = USD -> CAD -> EUR)

 6. The Bank of Canada only includes exchange rates for business days. Holidays in Canada are excluded.
In this case, the exchange rate to be used should be the closest previous date for which an exchange rate exists.

 (The foreign exchange rate used to convert the foreign currency transaction into Canadian dollars is either
- the rate in effect on the date of the transaction, or
- the average annual exchange rate for the taxation year
as quoted by the Bank of Canada on the particular day or on the closest preceding day for which a spot rate is quoted, as per the definition of "relevant spot rate" in s. 261(1) of the Income Tax Act.)

Stuart Moscoe