Currency conversion table

[#1]

Hi All,

I have a requirement to convert XAF to AUD for a the period Jan 2009 - Jan 2010.

Users of the ERP are currently entering currency conversion rates, but the problem is that they often miss out a period of dates.

As a result the table looks similar to the one below (see 25 - 26 July missed out, as well as 01 - 01 August);

I have extracted this table into my script and joined it with a previously constructed complete date table.

My result looks like this;

How can I fill the list of currency conversion table by using the script to replace a blank line with the most previously read complete line?

FYI the reason why I am using the source system is for ease of extraction. I don't have any experience with using a designated source feed for currency conversions.I'd like to know how to achieve the result under the current set up, but i'm also interested if there is another way to achieve the result with an external feed.

Thanks guys.

[#2 · Accepted solution]

OK I opted out of scripting a solution with the source database, with missing fields etc..

Instead I used a variation of other examples using OANDA.com to create a complete currency conversion table. OANDA goes back a max of 500 days, hence the today - 499 variable.

Feel free to use it, it's ready to use PnP. The script goes something like this;

//---------------Currency Parameters--------------/
LET vBaseCurrency = 'CDF';
LET vConvertCurrency = 'AUD';


//---------------Date Parameters------------------/
LET vMinDate = Date(today() - 499,'DD/MM/YY');
LET vMaxDate = Date(today(),'DD/MM/YY') ;


RawRate:
LOAD Replace(@1,'.','/') as Date,
@2 as Rate
FROM
[http://www.oanda.com/currency/historical-rates?date_fmt=normal&date=$(vMaxDate)&date1=$(vMinDate)&exch=$(vBaseCurrency)&exch2=$(vBaseCurrency)&expr=$(vConvertCurrency)&expr2=$(vConvertCurrency)&margin_fixed=0&format=HTML&redirected=1]
(html, codepage is 1252, no labels, table is @1);
CONCATENATE
LOAD Replace(@1,'.','/') as Date,
@2 as Rate
FROM
[http://www.oanda.com/currency/historical-rates?date_fmt=normal&date=$(vMaxDate)&date1=$(vMinDate)&exch=$(vBaseCurrency)&exch2=$(vBaseCurrency)&expr=$(vConvertCurrency)&expr2=$(vConvertCurrency)&margin_fixed=0&format=HTML&redirected=1]
(html, codepage is 1252, no labels, table is @2);

STORE RawRate
INTO DWCurrencyRate.qvd (qvd);