Microsoft Flow: Automatically uploading foreign exchange rates daily from the European Central Bank

Many applications require reference foreign exchange rates to be updated daily and/or a table of historical forex rates to be maintained.  These are typically accounting, expense reporting, portfolio tracking, e-commerce applications.  Microsoft Flow is perfect for this, and the data can be stored almost anywhere.

The ECB (European Central Bank) publishes end-of-day FX rates here for all major currencies and more.  As the basis of our flow we can use this XML document which is updated at close of business every day.  Access to the ECB data is open and free.  The individual elements of information can be extracted from the XML using XPath expressions.

You can tailor your flow to store the foreign exchange rates using any currency you like as the reference currency.   In my example I will use the USD since this will require conversion of the raw EUR rates as supplied by the ECB.  If you need the EUR as the base currency then your flow will be even simpler.

Preparatory steps

• Create a one-column table (Currencies) of target currencies (as you may not want to store rates for all currencies on the ECB list) having this column
  • CurrencyCode – text type of length 3
•  Create a table (FxRates) to store the FX data (one row for each currency/date pair) having the following columns:
  • CurrencyCode – text type of length 3
  • FxDate – date type
  • FxRate – numeric (float) type

If you are using Azure SQL Database or similar, you could use this T-SQL to create the Currencies table:

CREATE TABLE [dbo].[Currencies](
 [CurrencyCode] [nchar](3) NOT NULL PRIMARY KEY CLUSTERED 
)

You could also use a SharePoint list or any other data source that can be accessed by Flow.

Please populate your table of currencies.  The flow will work whether you include the base currency or not.  The table should look something like this:

Please note that I have included USD even though it is my base currency.  This is done because this list is also the basis for dropdown control choices etc.  However the flow will not upload a USD/USD rate to the FxRates table as this would be trivially 1 every day.

Similarly, here is the T-SQL to create the FxRates table:

CREATE TABLE [dbo].[FxRates](
 [CurrencyCode] [nchar](3) NOT NULL,
 [FxDate] [date] NOT NULL,
 [FxRate] [float] NOT NULL,
 CONSTRAINT [PK_FxRates] PRIMARY KEY CLUSTERED 
(
 [CurrencyCode] ASC,
 [FxDate] ASC
)
)
GO

ALTER TABLE [dbo].[FxRates] WITH CHECK ADD CONSTRAINT [FK_FxRates_Currencies] FOREIGN KEY([CurrencyCode])
REFERENCES [dbo].[Currencies] ([CurrencyCode])
GO

Again, this table could be in any data source that can be modified by Flow.

Here is what this table look like with some sample data:

Please note that there is no USD rate, as this would be trivially 1 every day.

Now the preparations are done, so we can create our flow.

Overview of the flow

The flow basically is initialised by a schedule trigger to run every night (after the ECB has updated the XML file for the day), and the flow then has the following steps

• Initialise all variables (including a list of holidays on which ECB does not publish FX rates)
• If today is a week-end or an ECB holiday then do nothing, otherwise do the following
  • Fetch the XML foreign exchange data from the ECB
  • Calculate the USD rate (since USD is the base currency)
  • Fetch all the currencies except for USD from the table Currencies
  • For each of the currencies calculate the USD rate (=EUR rate/USD rate) and insert the data as a new row in the table FxRates

Here is what the whole flow looks like in the flow designer:

And here is the last step exploded to show some detail:

Detail of the flow

The daily recurrence trigger settings are self-explanatory

The first action step is an ‘Initialize variable’ type of step (I exclude the trigger from the step count), and you need to put the following into the Expression editor in order to set the array type variable TargetClosingDays with the next few ECB holidays for which data should not be uploaded.

createArray('2017-12-25','2017-12-26','2018-01-01','2018-03-30','2018-04-02','2018-05-01','2018-12-25','2018-12-26')

You can get a fresh list of these holidays by putting ‘TARGET closing days’ in your favourite search engine or looking for ECB working days.

For the second step use the following expression to set the DateToday variable with the current date:

formatDateTime(utcNow(),'yyyy-MM-dd')

For the third step just initialise the EcbDate variable with no data, this is to ensure it is of string data type (this works with Azure SQL Database, but you may need to tweak the data type for your data source).  This variable will contain the date of the data from the ECB, and this will be used instead of today in order to ensure that data is consistent.

Likewise in the fourth step we just initialise the EcbUsdRate variable with no data.  The initialisation of this and the previous variable are probably not strictly necessary, because this can be done when a value is first set, but I find it tidier to initialise all variables up front.

In the fifth step we set the variable ItemXpathString to this value

number(//*[@currency='XXX']/@rate)

If like me you were not familiar with XPath at the start of this project, then look it up if you are interested.  Basically it is the way in which the individual items of data that you want can be extracted from the XML file.

In the sixth step we again initialise the empty variable EcbItemRate, as this will hold the foreign exchange rate for each non-base currency as we loop through them later in the flow.

Next we have a condition type step to check whether today is a weekday and not an ECB holiday with this expression:

@and(contains(createArray(1, 2, 3, 4, 5), dayOfWeek(variables('DateToday'))), not(contains(variables('TargetClosingDays'), variables('DateToday'))))

(apologies but the editor is hiding the expression text)

In case the condition is not met, the we do nothing, whereas we go to the following seventh action step (HTTP type of step) in case the condition is met

Please note that the URI is that for the ECB XML data

http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

Next we have a set variable type step (the variable has already been initialised) to set the EcbDate with what has been returned by the HTTP step with this expression:

xpath(xml(body('HTTP')),'string(//@time)')

Then we set the EcbUsdRate variable with what has been returned by the HTTP using this expression:

xpath(xml(body('HTTP')),'number(//*[@currency=''USD'']/@rate)')

This is needed since we want to use the USD and not EUR as the base rate.

Now we fetch all currencies except the USD, please note the Filter Query expression CurrencyCode ne ‘USD’ in the advanced options below.  This is for an SQL Server or Azure SQL Database type of data source.  You may have to adjust if you use SharePoint, CDS or other types of sources to store your tables.  On the other hand it might work just fine.

Then we set up the loop to go through each currency, fetch the item’s (i.e. currency’s) EUR rate and save the USD rate by dividing the EUR rate by the USD rate

Use the following expression to fetch the item’s EUR rate from the HTTP result.  The trick is to set the rate to 1 for EUR itself.

if(equals(item().CurrencyCode,'EUR'),1.0,xpath(xml(body('HTTP')),replace(variables('ItemXpathString'),'XXX',item().CurrencyCode)))

In the ‘insert new row’ type of action use the following expression to obtain the USD rate from the EUR rate

div(variables('EcbItemRate'),variables('EcbUsdRate'))

Final thoughts

In my productive implementation of this flow I had a number of error-checking steps to make sure that the correct data was fetched and uploaded, as well as logging the operations to be able to have some audit trail.  I have excluded these for the purposes of this blog so that the key components were more clearly visible.

Please leave comments/questions and I will be very happy to address these.