ECB (European Central Bank) Exchange Rates to Workday ERP

Showcase
1

Today I wanted to show how to use Make.com to get Exchange rates from the ECB and then load them into Workday. The ECB offers free spot rate between the EUR and 30 common currencies, using an HTTP module we can access the current rates (or 90 days of history) to then pass to the Workday module (if you’re an enterprise customer of Make, if not you can use the HTTP module to load the rates but that is a bit more complex). In our scenario we have 2 branches, the first branch is to load the current rates each day, and the second branch is to load the Average rates if today is the last day of the month.

Here is a screenshot of the overall flow (Note in my showcase I removed the error handling, but I recommend for any production scenario you consider error handling):

image
image1473×630 92.5 KB

  1. We use the HTTP Make a Request module to ‘Get’ the data from URL: http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml (Make sure Parse Response is set to ‘Yes’). See the screenshot below, the rates are nested within a couple of arrays so our next step is to use an iterator to get to the rates.

image
image1086×853 100 KB

  1. Iterate through the cube array to get to the 30 exchange rates that we want to load into Workday.

image
image1307×441 70.1 KB

  1. Using the Workday ‘Make a SOAP API Call (Advanced)’ we can then load the rates. Here is the code for the ‘put currency conversion rate’ SOAP API call and a screenshot of how we configured the Workday module. Of course the Workday SOAP API call to load the rates should be modified to fit your own Workday environment.
<bsvc:Put_Currency_Conversion_Rate_Request bsvc:Add_Only="0">
         <bsvc:Currency_Conversion_Rate_Data>
            <bsvc:Effective_Timestamp>{{formatDate(now; "YYYY-MM-DD")}}T02:00:00</bsvc:Effective_Timestamp>
            <bsvc:From_Currency_Reference>
               <!--Zero or more repetitions:-->
               <bsvc:ID bsvc:type="Currency_ID">EUR</bsvc:ID>
            </bsvc:From_Currency_Reference>
            <bsvc:Target_Currency_Reference>
               <!--Zero or more repetitions:-->
               <bsvc:ID bsvc:type="Currency_ID">{{4.`_attributes`.currency}}</bsvc:ID>
            </bsvc:Target_Currency_Reference>
            <bsvc:Currency_Rate_Type_Reference>
               <!--Zero or more repetitions:-->
               <bsvc:ID bsvc:type="Currency_Rate_Type_ID">CURRENT</bsvc:ID>
            </bsvc:Currency_Rate_Type_Reference>
            <bsvc:Currency_Rate>{{4.`_attributes`.rate}}</bsvc:Currency_Rate>
            <!--Optional:-->
            <bsvc:Calculate_Inverse_Rate>1</bsvc:Calculate_Inverse_Rate>
         </bsvc:Currency_Conversion_Rate_Data>
      </bsvc:Put_Currency_Conversion_Rate_Request>

image
image599×898 79 KB

  1. Last step we load the currency data into a Data Store using ‘update a record’ - so when it’s the last day we can extract that data from the data store to load the average rates. Instead of this method you could also use https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml to get the last 90 days of rate data and calculate the average rates.

image
image970×864 109 KB

For the average rates, here is the filter I use to determine if today is the last day of the month.

image
image1257×702 122 KB

And after we search the data store we only want to proceed for the current month data, so here is the filter we use for that.

image
image1016×494 66.6 KB

Then we use the Numeric Aggregator to determine the average rates (grouping by each currency code of course).

image
image986×646 82.3 KB

Similar to the Current Rates, see the code here we use for our average rates. Again this will need to be customized to fit your business requirements. Note, Workday can only accept up to 12 digits of precision, so we use the parseNumber and formatNumber to ensure all average rates are rounded to the 12th digit and then converted back into a number.

<bsvc:Put_Currency_Conversion_Rate_Request bsvc:Add_Only="0">
         <bsvc:Currency_Conversion_Rate_Data>
            <bsvc:Effective_Timestamp>{{formatDate(now; "YYYY-MM-DD")}}T02:00:00</bsvc:Effective_Timestamp>
            <bsvc:From_Currency_Reference>
               <!--Zero or more repetitions:-->
               <bsvc:ID bsvc:type="Currency_ID">EUR</bsvc:ID>
            </bsvc:From_Currency_Reference>
            <bsvc:Target_Currency_Reference>
               <!--Zero or more repetitions:-->
               <bsvc:ID bsvc:type="Currency_ID">{{8.`__IMTKEY__`}}</bsvc:ID>
            </bsvc:Target_Currency_Reference>
            <bsvc:Currency_Rate_Type_Reference>
               <!--Zero or more repetitions:-->
               <bsvc:ID bsvc:type="Currency_Rate_Type_ID">Average</bsvc:ID>
            </bsvc:Currency_Rate_Type_Reference>
            <bsvc:Currency_Rate>{{parseNumber(formatNumber(8.result; 12; "."; ","); ".")}}</bsvc:Currency_Rate>
            <!--Optional:-->
            <bsvc:Calculate_Inverse_Rate>1</bsvc:Calculate_Inverse_Rate>
         </bsvc:Currency_Conversion_Rate_Data>
      </bsvc:Put_Currency_Conversion_Rate_Request>

That’s the end of my showcase, in summary we can leverage Make to load currency rates from the ECB. This requires much less code vs using XLST in Workday, and there are other advantages such as ease of use (very quick for us to build this scenario), easy to build error handling, and quick to re-run or ad-hoc load rates as needed. Also the data store can act as a sort of buffer or history of rates that we have loaded, and we can build another scenario that deletes older rates that we no longer need to have a history of.

Let me know if you have any questions.

1 Like