Solved

Currency update - UK

  • 31 March 2021
  • 6 replies
  • 874 views

Userlevel 2
Badge +9
  • Sidekick (Partner)
  • 28 replies

Trying to find a link to a UK web site with a suitable xml or CSV format for daily automatic IFS currency update of yesterdays rate for a UK company.  Can anybody help me with an exact link to this?

icon

Best answer by alackas 3 August 2021, 15:34

View original

This topic has been closed for comments

6 replies

Userlevel 7
Badge +28

Try xe.com

Userlevel 2
Badge +8

@brsa have you found a way to actually update the rates automatically?   If so how are you doing it?   custom code or did you find a routine to do it?

Badge +1

Hi @alackas

We have solution for autoupdate currency rates in IFS not UK but for Polish currency rate.

The Polish National Bank publishes exchange rates as XML:
=>https://www.nbp.pl/kursy/xml/LastA.xml


then we use google spreadsheet  to import date

we use standard functionality:

=IMPORTXML("https://www.nbp.pl/kursy/xml/LastA.xml?0.9803928828938182";"//pozycja")
=IMPORTXML("https://www.nbp.pl/kursy/xml/LastA.xml?0.9803928828938182";"/tabela_kursow/data_publikacji")

xml is changed to flat file  then CVS is generated : 

=TEXTJOIN( ;True;(Kursy_auto_update_XML!F1&";"&Kursy_auto_update_XML!I1&";");Kursy_auto_update_XML!$H$1+1)


google tools script   / script is scheduled to update currency

 

function getData() {
  var queryString = Math.random();
  var cellFunction1 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + '?' + queryString + '";"'+ SpreadsheetApp.getActiveSheet().getRange('A2').getValue() + '")';
  SpreadsheetApp.getActiveSheet().getRange('D1').setValue(cellFunction1);
  var cellFunction2 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A4').getValue() + '?' + queryString + '";"'+ SpreadsheetApp.getActiveSheet().getRange('A5').getValue() + '")';
  SpreadsheetApp.getActiveSheet().getRange('H1').setValue(cellFunction2);
}

next csv file is shared so it can be downloaded from google servers 

then we have cron job downloading file and saving it in  specific localization.

localization is mapped as oracle directory so it can be use in file import or data migration.

 then we have standard ifs file import (it is defined file for currency rate )

IFSAPP.EXT_FILE_TYPE => ExtCurrency

downloading file is scheduled every day.

then we have trigger on:DeferredJob  table: TRANSACTION_SYS_LOCAL_TAB

old state =Executing
procedure =External_File_Utility_API.Execute_Batch_Process2
new state !=Ready

then send email.

 

Regards

Luke

Userlevel 7
Badge +20

Hi @BRSA, @alackas 

 

I have created a free API specifically for your requirement and with that you can update the daily currency rates from European Central Bank with an RMB or a scheduled task.

 

Have a look at the post which I share the information for details

 API for updating currency rates in IFS | IFS Community

 

or the Github Repo where you can start using it right away!

GitHub - knakit/set-ifs-currency-rates-api: An out of the box solution for auto updating IFS currency rates

 

Cheers!
Damith

Userlevel 1
Badge +2

@dsj thanks for sharing the Github Currency automation link… just wondering if you have any experience in IFS oDATA Rest API for currency? https://community.ifs.com/search/activity/topics?userid=9739

 

We are having trouble using it with ValidFrom date format in the URL… thanks in advance

Userlevel 1
Badge +2

OK, it’s not a big deal after all -  just no single quotes like other multi-key format: /v1/CurrencyRatesHandling.svc/CurrencyRateSet(Company='601',CurrencyType='1',CurrencyCode='AED',ValidFrom=2022-03-01)