Conversion of exchange rates - European Central Bank

Applies to version: 2021.1.x and above; author: Konrad Wojtycza

Introduction

The WEBCON BPS system enables the synchronization of exchange rates. The available sources of currency data are exchange rate tables provided by the National Bank of Poland, the European Central Bank, and the Central Bank of Russia.

This article describes how to download and convert the rates provided by the European Central Bank (ECB). The table of exchange rates of the ECB returns the conversion rate of how many units of a given currency corresponds to the value of one euro. The article also shows how to convert the exchange rate on weekends (the ECB exchange rate table does not return values for weekends).

For more information, see:

• Configuration of automatic currency exchange synchronization
• Global constants and rules - configure once, use every day – how business rules can be used to download exchange rates

Converting  exchange rates from the ECB

Source configuration

The following screenshot presents the query to the ExchangeRates table.

The first part of the query (before the “union” tag) allows you to retrieve the ID, code, exchange rate date, and rate for all currencies provided by ECB. Each tag returning “Exchange rate date” contains the “DATEADD” function, so that query finally refers to the day preceding the indicated date (“-1” parameter). This is because the latest rates downloaded to the table are those from yesterday - therefore, the query would not return a value for the current date.

The "Course date" form field is the value from the form, on which the currencies will be converted. In addition to today's date, you can select any date from the past here. To be consistent with registering an instance for today, even for dates in the past, the rate on the day before that date will be returned.

The ECB does not offer weekend courses. For this reason, the query has been narrowed down - if the returned day of the week is Saturday (value 7) or Sunday (value 1), the date of downloading the courses is shifted back by 1 or 2 days, respectively, i.e. the Friday rate is returned.

The exchange rate table does not return the euro currency symbol so the currency code has been manually added – after the “union” tag. The value date was set similarly to the other rates, and the rate value was set to "1".

Fig. 1. The query to the ExchangeRates table

Workflow configuration

Below is a form with the calculated values for the previous day:

Fig. 2. The completed form

The fields marked with the [TECH] prefix are technical fields that will not be displayed on the form.

In the "Source currency" form field, indicate the currency from which the exchange rate will be converted. This is a choice field type of the form field with a source returning currency codes. In the described case, a source of the "Fixed values list" type was created:

Fig. 3. The source of the "Currency" choice field

The same source has been attached to the form field where the target currency is selected.

Business rules have been created, used in the form rules that perform the change of form field values, where the source and target currencies are selected. The rule executing after the selection of the source currency is completed by the “[TECH] Avg. rate in the source currency for 1 EUR” technical form field.

Fig. 4. Form rule executed on the change of the "Currency" form field

This rule contains the “Data source value” function with the following configuration:

Fig. 5. The business rule returns the exchange rate of the source currency expressed in euro

Finally, this function returns EUR 1 expressed in the source currency. In the function configuration, indicate the data source of the rule (the query to the currency table created at the beginning of the article), then filter the values so that the function returns the value of the currency indicated on the form (the source currency code from the data source must match the one from the form). Next, select the column of the returned value - in our case, it will be a column that returns the currency rate.

Similarly, the rule should be configured after selecting the target currency and supplementing the technical form field "[TECH] Average rate in target currency for EUR 1".

Fig. 6. Form rule to be executed on value change of the "Target currency" form field

Fig. 7. The business rule returns the exchange rate of the target currency expressed in euro

When changing the value of the "[TECH] Average exchange rate in the source currency for EUR 1" technical form field, a form rule is performed that calculates the value of the exchange rate of the currency to be obtained with the euro (e.g. USD / EUR exchange rate as in the example).

Fig. 8. The rule calculating the average rate in euro for the source currency

After proper configuration, you can convert the entered amount in a given currency to the desired value. The conversion is made in the form rule, which can be set to change the value of any field in the calculator (preferably all changing):

Fig.9. Rule that calculates the value of a given amount in a new currency

The initial amount entered in the given currency is converted into a euro value and then the euro value is converted into the target currency.

An example of another conversion is shown below. The form worked correctly despite the date indicated on Sunday. The rate for the previous Friday was returned.

Fig. 10. Another example of a calculator

Summary

The simple currency converter presented above presents one of the ways to convert the exchange rates provided by the ECB into the euro currency. The method of handling the return of currency exchange rates for weekend days was also presented. Using the tools built into the WEBCON BPS system, as well as the benefits of the SQL language, we can freely configure the data sources used in workflows.