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