FOREX Integration on Loading Script

atsushi_saijo — Thu, 13 Feb 2014 15:02:54 GMT

**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