UK - read HMRC Exchange rates from xml file on internet

james_cs

Hi.

This request from a customer turned out to be so simple I thought I would post it for all to use.

The UK Tax authority , Her Majesty's Revenue & Customs (HMRC) publishes exchange rates to be used when reporting transactions in foreign currency on, for example, a VAT return. These rates are published in XML format.

Firstly, I should say that I have added a field to table 330 to hold the HMRC exchange rate as it is different to the exchange rate that the company wants to use in it's business.

so as a text export, my codeunit looks like this

OBJECT Codeunit 50020 Read HMRC Exchange Rates
{
  OBJECT-PROPERTIES
  {
    Date=03/06/15;
    Time=22:40:02;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            WebRequest@1000000006 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebRequest";
            WebResponse@1000000005 : DotNet "'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebResponse";
            XMLDoc@1000000004 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
            XMLNodeList@1000000003 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList";
            XMLNode@1000000007 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
            Enumerator@1000000002 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.IEnumerator";
            Uri@1000000001 : Text;
            MonthYear@1000000000 : Text[4];
            Currency@1000000008 : Record 4;
            CurrExRate@1000000009 : Record 330;
            StartingDate@1000000010 : Date;
            TempText@1000000011 : Text;
            TempDec@1000000012 : Decimal;
            perText@1000000013 : Text;
            i@1000000014 : Integer;
          BEGIN
            MonthYear := FORMAT(TODAY,0,'<month,2><year>');
            //MESSAGE(MonthYear);

            FOR i := 1 TO 2 DO BEGIN
              IF i = 1 THEN perText :=  'monthly'
              ELSE perText := 'weekly';

              Uri := STRSUBSTNO('http://www.hmrc.gov.uk/softwaredevelopers/rates/exrates-%1-%2.xml',
                                   perText,MonthYear);

              WebRequest := WebRequest.Create(Uri);
              WebResponse := WebRequest.GetResponse;
              //MESSAGE(WebResponse.ContentType);
              IF WebResponse.ContentType = 'text/xml' THEN BEGIN
                XMLDoc := XMLDoc.XmlDocument();
                XMLDoc.Load(WebResponse.GetResponseStream);
                XMLNodeList := XMLDoc.SelectNodes('exchangeRateMonthList/exchangeRate');
                //MESSAGE('%1',XMLNodeList.Count);
                Enumerator := XMLNodeList.GetEnumerator;
                WHILE Enumerator.MoveNext DO BEGIN
                  XMLNode := Enumerator.Current;
                  IF Currency.GET(GetNodeValue('currencyCode',XMLNode)) THEN BEGIN
                    TempText := GetNodeValue('rateDate',XMLNode);
                    EVALUATE(TempDec,GetNodeValue('rateNew',XMLNode));
                    IF TempText = '' THEN
                      StartingDate := CALCDATE('-CM',TODAY)
                    ELSE
                      EVALUATE(StartingDate,TempText);
                    CurrExRate.SETRANGE("Currency Code",Currency.Code);
                    CurrExRate.SETRANGE("Starting Date",0D,StartingDate);
                    IF CurrExRate.FINDLAST THEN
                      IF TempDec <> CurrExRate."Statutory Rate" THEN BEGIN
                        IF CurrExRate."Starting Date" <> StartingDate THEN BEGIN
                          CurrExRate."Starting Date" := StartingDate;
                          CurrExRate.INSERT;
                        END;
                        CurrExRate."Statutory Rate" := TempDec;
                        CurrExRate.MODIFY;
                      END;
                  END;
                END;
              END;
            END;
          END;

  }
  CODE
  {

    LOCAL PROCEDURE GetNodeValue@1000000027(NodeName@1000000000 : Text;VAR XMLNode@1000000002 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode") : Text;
    VAR
      XMLNode2@1000000001 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
    BEGIN
      XMLNode2 := XMLNode.SelectSingleNode(NodeName);
      IF ISNULL(XMLNode2) THEN
        EXIT('')
      ELSE
        EXIT(XMLNode2.InnerText);
    END;

    BEGIN
    END.
  }
}

Rather annoyingly, if there is no file, the status code returned is still an HTTP 200, but the page returned is an html file saying that the file doesn't exist.

This is all done using DotNets from System and System.xml. In this case, no extra automations are required. In other cases where I am communicating with a REST service over https which requires pre-emptive authentication I have had to use a separate library I have developed as C/AL cannot manipulate the .net types in the way required. If anyone wants some pointers with similar problems then please message me and I may be able to help

MBrodie1979

Is that you James Carr Saunders? I remember you from K3 days. Very nice work I like it. I am going to try an use this.

Miklos_Hollender

I too remember you, James, from TecMan days :-)

Nice job.

I think the future is using more and more these services, not manually entering these data. For example, one really useful thing to do is to use the web service to check the existence of the VAT Registration No. of EU customers or vendors. http://www.codeproject.com/Articles/969 ... er-checker