Updating Currency Rates

04 July 2017 - 1306 views
Currency rates in SAP Business One can be programmically updated using the SAP DI API. This article describes creating a simple console application, that will fetch currency rates from a data source and update currency rates in SAP B1.

For the prupose of this article we will use an XML feed provided free by the European Central Bank (https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml). The feed does not contain all currencies, so be sure to check the list.
Create a new Console Application and paste the following code into the Main method.

Listing 1

Dictionary<stringdouble> rates = new Dictionary<stringdouble>();

using (XmlTextReader reader = new XmlTextReader("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"))
{
    while (reader.Read())
    {
        switch (reader.Name)
        {
            case "Cube":

                var currency = reader.GetAttribute("currency");
                var rate = reader.GetAttribute("rate");

                if (null != rate)
                {
                    rates.Add(currency, Double.Parse(rate.ToString()));
                }
                break;
        }
    }
}

The code above stores all currencies provided by the feed in a rates list object. All that is required now is to connect to the company database using the DI API and call the SetCurrencyRate method as shown in Listing 2 below.

Listing 2

SAPbobsCOM.Company company = new SAPbobsCOM.Company();

company.Server = "server ip";
company.CompanyDB = "company db";
company.DbUserName = "db username";
company.DbPassword = "db password";
company.UserName = "sap user";
company.Password = "sap password";
company.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_HANADB;

int result = company.Connect();

if (result == 0)
{
    SAPbobsCOM.SBObob bo = company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge);

    bo.SetCurrencyRate("USD", DateTime.Now, rates["USD"]true);
    company.Disconnect();
}
else

{
    Console.WriteLine(company.GetLastErrorDescription());
}

You can only set a curreny rate for a currency that has been added to the exchange rates for the selected company. If you attempt to set a rate for a non existing currency, you will recieve a System.Runtime.InteropServices.COMException: 'Invalid currency ' exception.

The base currency for the rates feed is EUR. If your base currency is different, you will need to convert the rates using your base currency. For example, if your base currency is GBP and you want the USD rate, you can use the formula below.

Listing 3

double rateValue = rates["USD"] / rates["GBP"];