Currency - store in QVD

[Anonymous]

Hi,

i'm trying to make a currency file, where I can store historical currencies.

The QVD file should grow every time I reload, if there is new data found.

I have found some code on this forum to get me started, but I'm stuck on how to store it in a QVD without getting an error.

For i = 0 to 18

    Let dd = Date(Today()-$(i), 'MM/DD/YY');

    Calendar:

    Load

         Date(Today()-$(i)) As ExchangeDate,

         Year(Today()-$(i)) As Year,

         Month(Today()-$(i)) As Month,

         Day(Today()-$(i)) As Day,

         Week(Today()-$(i)) As Week,

         WeekYear(Today()-$(i)) & '-' & Num(Week(Today()-$(i)),'00') As RollingWeek,

         Weekday(Today()-$(i)) As WeekDay,

         Date(MonthStart(Today()-$(i)),'YYYY-MM') As RollingMonth,

         Right(year(Today()),2) & '-Q' & ceil(Month(Today())/3)       as [YearQtr],

         'Q' & Ceil(Month(Today()-$(i))/3) As Quarter

    Autogenerate(1);    

    EuroRates:

    LOAD

     Date(Today()-$(i)) As ExchangeDate,

     Currency,

     Code,

     [EUR/1 Unit]            as ToEUR,

     [Units/1 EUR]            as FromEUR

    FROM

    [http://www.oanda.com/convert/fxdaily?date=$(dd)&date_fmt=us&exch=EUR&lang=en&sel_list=USD_HUF_UAH_RU...]

    (html, codepage is 1252, embedded labels, table is @1);

    Store EuroRates into QVD\EuroRates.QVD;

Next

If I let this code run, it just stops with the message "Execution of script failed. Reload old data?".

If I leave the Store EuroRates into QVD\EuroRates.QVD; line out of it, it loads currency data but only from the last date.

I'm expecting a different currency rate for each day, if it loads 18 days.

Could someone give me a hand to get this working?

Thank you!

[#2]

hi evelien,

as you said, code is the same.

the only difference is in the system variables but i find it hard to beliave this is the reason...

your settings are:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D/MM/YYYY';

SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

while my settings are:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

maybe somthing in here.... i really dont know.

maybe jonathan has an idea.....

anyhow, when i run my code i dont see any problem with text/ numeric issues...

my numbers are good.

[Anonymous]

Elanir,

I found a solution for my problem.

I put the function EVALUATE around the ToEUr and FromEur fields.

Now, all my values are numeric.

Thanks for your help

[#4]

Hi Evelien,

I have tried using the Currency_Model file to work with multiple exchange rates.  Did you get it to work?

/Thor

[Anonymous]

Hi Thor,

If you run the QVW for the first time, the QVD stores all the currencies for all the days you've asked.

If I run the QVW a week later, the only thing he stores in the QVD are the USD currencies. All the other currencies are deleted somehow.

So no, actually it doesn't work but I haven't found a solution to get it to work.

If you should find a solution, please let me know.

[#6]

hi Evelien and Thor,

im attaching two samples of QVW files i experimented with on the currency.

take a look and see if it helps you.

Elad.

[#7]

Thanks Elad

I am getting a script error when trying to load:

Regards

Thor

[#8]

in the code,

you'll see the URL of www.oanda.com.

at the end of that line, you'll see a '@3'.

change it to '@6' and it should work. just tried it now.

but im not sure about what Evelien said about it erasing old data...  i didnt have a chance to take this solution to production...

[Anonymous]

Hi,

here you have my 2 files.

As you can see. In my Currencies QVW you only see the US Dollar going back quite some time.

The other currencies are only those of today.

if you delete the QVD, there will be a new QVD made.

The first time you use the new QVD, all the currencies will be there.

Once you have re-run the QVW the next day, most of the currencies dissappear.

FYI

[#10]

try adding something that will alow incremental load.

somthing like this:

IF isnull(QvdCreateTime('..\QVD\Currencies.qvd')) THEN //************* New Load ****************

Currencies:

LOAD

FROM <put your load script here with first date you want to run from>;

STORE NewRules into ..\QVD\NewRules.qvd(qvd);

drop table Currencies;

ELSE //************* Loads only new data ****************

IncrementalLoad:

LOAD *

FROM

..\QVD\Currencies.qvd(qvd);

MaxDate:

LOAD Timestamp(Max(LAST_MODIFIED_DATE),'YYYY-MM-DD hh:mm:ss') as MaxDateTime

Resident IncrementalLoad;

Let vMaxDateTime = Peek('MaxDateTime',-1,MaxDate);

DROP Table MaxDate;

Concatenate

LOAD

FROM <put your load script here with first date you want to run from>;

// but this time use '$(vMaxDateTime)' as the earliest date you want to extract from;

STORE IncrementalLoad into ..\QVD\Currencies.qvd(qvd);

ENDIF

i believe this should do the work.....

let me kno whow it went.

[Anonymous]

Hi Elanir,

I should saw that I put the wrong QVW in annex.

Here you see the QVW that make the QVD file.

I think i'm already doing what you suggest in the previous post.