Tired of keypunching Currency Exchange rates?
Even more tired of researching keypunch errors?
Replace those tedious keypunch activities with automated import of Currency Exchange rates in Microsoft D365 ERP.
Today I will use Oanda as an example.
1. Create currencies
Many implementations that I have seen, have all the currencies already created and ready for use. If you need to create some, use standard ISO naming for the currencies you need to create.
General ledger > Currencies > Currencies
2. Create your exchange rate types
You can name the types anything you want.
I happen to like using "Default" to store my daily imported rates.
General ledger > Currencies > Exchange rate types
3. Ledger Setup
Assign the appropriate rates to your Ledger.
Note: The exhibit is for a USA legal entity, thus it has USD as accounting and reporting currencies.
General ledger > Ledger setup > Ledger (Currency section)
If you are interested in other Ledger setup settings, see earlier blog:
4. Create Currencies.
Create applicable currencies pairs for your Legal Entity.
Repeat for each "Exchange rate type" that you wish to use.
My example is for the "Default" Exchange Rate Type.
General ledger > Currencies > Currency exchange rates (left side)
Note: I prefer increased legibility, and more decimals, thus I use a Conversion Factor of "100" (instead of "1")
5. Configure your Exchange Rate Provider
Out of the box, you can choose:
Oanda
Central bank of the Russian Federation
Central bank of Europe
Majority of my clients prefer to use Oanda.
It is a paid subscription. Oanda offers free rates on a trial basis.
In my opinion, Oanda fees are very economical.
As I mentioned earlier, my blog will use "Oanda" for an example.
I assume you have already purchased an Oanda subscription and obtained an "API key" from Oanda.
Setup as exhibited using the "API key" purchased from Oanda, and then save your setup.
Note: API Key is redacted in exhibit.
General ledger > Currencies > Configure exchange rate providers
6. Import historical rates (optional)
You may desire to import historical rates.
The import feature is limited to 92 days at a time, thus I like to import by quarter
i.e.
1/1/2019 - 3/31/2019
4/1/2019 - 6/30/2019
and so on through yesterday.
General ledger > Currencies > Import currency exchange rates
Fill out as exhibited:
(change date range as desired, 92 day maximum)
Note: This import is to the "Exchange rate type" = Default
Repeat as desired for additional "Exchange rate types" i.e. "Closing"
Note: In my experience, import takes about 10 seconds on a Gb internet connection for:
30 days of rates for one currency pair
7. Setup Automatic Nightly Batch Job
Here is the magic !
I did my setup in a STAGE environment for testing purposes.
I did this effective starting on 1/14/2022 and ending 17 days later.
Thus I will get automatic rate updates thru my specified setup: 4/17/2022 thru 1/31/2022.
After the below exhibit, I will specify the setup edits for a PROD evironment.
Fill out as exhibited:
Note: “Import as of” and “Batch processing” has different settings than above historical imports.
Note:
Use the exhibited settings for your testing in a a TEST or STAGE environment.
Below the exhibit are the setup edits for PROD environment.
General ledger > Currencies > Import currency exchange rate
Note, Setup edits for PROD environment:
In PROD environment, recommended setting is “NO END DATE” (not END AFTER)
Sit back and enjoy your automated import of daily currency exchange rates !