0 Replies Latest reply: Feb 14, 2014 6:58 AM by Atsushi Saijo RSS

    FOREX Integration on Loading Script

    Atsushi Saijo

      **My sincere apology that initial loading script posted on 13.02.2014 was faulty. Further QA tests, I updated the trial script.

      [Fix 1] mapping was missing;

      [Fix 2] MonthEnd needs to be further rounded downwards by FLOOR();

      [Pending 3] The attached exchange rate misses the latest (of this month). I will do certain update on this script later.

       

      ***************************

      Dear Experts, I wished to share the FOREX integration example. I would be grateful for your feedback.

       

      On the loading script, we have externally-obtained exchange rate (http://www.oanda.com/currency/historical-rates/). In my case, data were fetched as attached.

       

      INITIAL LOAD

      FOREX data are loaded first.

       

      TMP:

      LOAD DATE([END DATE],'YYYY-MM-DD') AS F.DT,

           [EUR/USD] AS USD,

           [EUR/GBP] AS GBP,

           [EUR/JPY] AS JPY,

           [EUR/CZK] AS CZK

      FROM

      $(PATH)data.csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

      TABLE CONVERSION

      Further, the loaded tables are converted into single table, ready for mapping.

       

      FOREX: LOAD F.DT,USD RESIDENT TMP;

      CONCATENATE(FOREX) LOAD F.DT,GBP RESIDENT TMP;

      CONCATENATE(FOREX) LOAD F.DT,JPY RESIDENT TMP;

      CONCATENATE(FOREX) LOAD F.DT,CZK RESIDENT TMP;

       

      FX: MAPPING LOAD * RESIDENT FOREX;

       

      MINMAX: LOAD MIN(F.DT) AS V RESIDENT TMP;

      SET VMIN = PEEK('V', 0, 'MINMAX');

       

      LIMITATION ON AVAILABLE TIME SERIES

      The maximum data are 5 years, therefore;

      MINMAX: LOAD MIN(F.DT) AS V RESIDENT FOREX;

      SET VMIN = PEEK('V', 0, 'MINMAX');

       

      ON MAIN LOADING SCRIPT

      On the loading script, we create such:

       

      [target value]/APPLYMAP('FX',

        TRIM(B1CD28)&IF(

        FLOOR(NUM(MONTHEND(DATE#(NUM([Target Date]),'YYYYMMDD'))))<$(VMIN)

        ,$(VMIN)

        ,FLOOR(NUM(MONTHEND(DATE#(NUM([Target Date]),'YYYYMMDD'))))

        )

        ,1) AS Exchanged_Figure

       

      CONCLUSIVE REMARK

      Initial load would be bulky if target currencies are @150. I wonder there might be a better scripting on this area. I appreciate if you possibly advise any improvements/comments/feedback on the above procedure to convert FOREX.