40 Replies Latest reply: Nov 28, 2012 2:07 AM by elanir01 RSS

    Currency - store in QVD

    Evelien Morel

      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_RUB_PLN_CNY&value=1&format=HTML&redirected=1]

          (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!

        • Re: Currency - store in QVD
          Jonathan Dienst

          Evelien

           

          I have a few suggestions for you to look at:

           

          • The STORE statement is inside the loop. That means that the qvd will be overwritten on each pass, and only the last date will be stored. Try moving the STORE statement after the Next statement.
          • If the STORE is causing an error, check that the folder exists. STORE does not create the folder.
          • If that is not the problem, delete the qvd. Sometimes when QV terminates with an error, it leave a lock on the file. If you cannot delete the file, close all open QV files, go to task manager and terminate the qv process. Then try again.
          • If you are still getting only one day's data, check that the OANDA url is returning the data you expect. If you step through in debugger, copy the generated URL and paste into your browser address bar.

           

          Hope that helps

          Jonathan

            • Re: Currency - store in QVD
              Evelien Morel

              Johan,

               

              thanks a lot for your suggestions.

              I've put the Store statement after the Next statement. That works fine.

              So now the loop works & gives me data for the number of days I put in the For i = 0 to ...

               

              In the original statement I used 18 days.

              If I reload with a number of days that is smaller than 18, some data dissappaers.

               

              Is there a way to add data to a QVD file?

              So that I can let it reload daily (automaticaly) and the new data is added?

               

              Thanks!

               

               

              Here, you find my adjusted code:

               

              For i = 0 to 16

               

                  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_RUB_PLN_CNY&value=1&format=HTML&redirected=1]

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

               

              Next

               

              Store EuroRates into EuroRates.QVD;

                • Re: Currency - store in QVD
                  Evelien Morel

                  Hi,

                   

                  I found out that I have to use an incremental load to get the data added in the QVD.

                  After reading the reference manual, I'm not sure on how to build it up.

                   

                  Anyone can help me on that?

                    • Re: Currency - store in QVD
                      Jonathan Dienst

                      Evelien

                       

                      Add the following code between the Next and the Store statements:

                       

                      Concatenate (EuroRates)

                      LOAD * From EuroRates.qvd (qvd)

                      Where Not(Exists(ExchangeDate))

                      ;

                       

                      This loads the "old" qvd, but excludes dates that you loaded in your loop. So after this your table contains the old dates and the newly loaded ones.

                       

                      Regards

                      Jonathan

                        • Re: Currency - store in QVD
                          Jonathan Dienst

                          You could optimise this by loading data from the last Exchange date to today as follows:

                           

                           

                          T_LastDate:

                          LOAD Max(ExchangeDate) As MaxExchangeDate From EuroRates.qvd (qvd);

                           

                          Let zMaxExchangeDate = Peek(MaxExchangeDate);

                          DROP Table T_LastDate;

                           

                          For zi = zMaxExchangeDate + 1 To Today()

                           

                               Let dd = Date(zi, 'MM/DD/YY');

                           

                          ....

                           

                          The rest of the script after Let dd is the same as before.

                           

                          Regards

                          Jonathan

                            • Re: Currency - store in QVD
                              Evelien Morel

                              Johan,

                               

                              I tried to paste your piece of code together with mine.

                               

                              When I run it, I get this error:

                              Script line error:

                              For zi = zMaxExchangeDate + 1 To Today()

                               

                               

                              This is my current code:

                              T_LastDate:
                              LOAD 
                              Max(ExchangeDate) As MaxExchangeDate 
                              From EuroRates.qvd (qvd);
                              
                              Let zMaxExchangeDate = Peek(MaxExchangeDate);
                              DROP Table T_LastDate;
                              
                              For zi = zMaxExchangeDate + 1 to Today()
                              
                                  Let dd = Date(zi, 'MM/DD/YY');
                                   
                                  EuroRates:
                                  LOAD
                                   Date(Today()-$(i))                                             As ExchangeDate,
                                   Currency, 
                                   Code, 
                                   [EUR/1 Unit]                                                    as ToEUR, 
                                   [Units/1 EUR]                                                    as FromEUR,
                                    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
                                 
                                  
                                  FROM
                                  [http://www.oanda.com/convert/fxdaily?date=$(dd)&date_fmt=us&exch=EUR&lang=en&sel_list=USD_HUF_UAH_RUB_PLN_CNY&value=1&format=HTML&redirected=1]
                                  (html, codepage is 1252, embedded labels, table is @1);
                              Next
                              
                              
                              Concatenate (EuroRates)
                              LOAD * From EuroRates.qvd (qvd)
                              Where Not(Exists(ExchangeDate));
                              
                              Store EuroRates into EuroRates.QVD;
                              
                                • Re: Currency - store in QVD
                                  Jonathan Dienst

                                  Evelien

                                   

                                  It looks like zMaxExchangeDate is not getting a value (ie is null).

                                  Check that the Peek is working.Step through the script with the debugger, and check that zMaxExchangeDate is a number (about 41080).

                                   

                                  This could happen if the qvd file is empty.

                                   

                                  Regards

                                  Jonathan

                                    • Re: Currency - store in QVD

                                      Evelien (and Jonathan)

                                       

                                      when you use incremental load, at first run (or in case your file was deleted) you will get an empty QVD file.

                                       

                                      so i always start my sintax with the following:

                                       

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

                                       

                                      <your query> but with no date limit.

                                       

                                      drop table EuroRates;

                                       

                                       

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

                                       

                                      <your query's last version - the one with the max date>

                                       

                                       

                                       

                                      BTW,

                                      after you finish and your query is up and running,

                                      please post it here..... im interested in the final result.

                                      thanks.

                                      • Re: Currency - store in QVD
                                        Evelien Morel

                                        Jonathan

                                        I will stop calling you 'Johan' from now, sorry for that.

                                         

                                        The Eurorates QVD file is not empty & still it gives an null value for the peek(MaxExchangeDate).

                                         

                                        Here you see a picture of the debugger.

                                         

                                        Is this normal?

                                         

                                         

                                        zMaxExchangeDate.png

                                          • Re: Currency - store in QVD
                                            Jonathan Dienst

                                            Evelien

                                             

                                            Is the ExchangeDate field a string perhaps? Could you possibly post a copy of the qvd here?

                                             

                                            Regards

                                            Jonathan

                                              • Re: Currency - store in QVD
                                                Evelien Morel

                                                Jonathan,

                                                 

                                                here you have the QVD file.

                                                The code hasn't changed since the last version we put together.

                                                 

                                                Thanks!

                                                  • Re: Currency - store in QVD
                                                    Jonathan Dienst

                                                    Evelien

                                                     

                                                    I have no problem with the Peek. Check the attached QVW.

                                                     

                                                    Regards

                                                    Jonathan

                                                      • Re: Currency - store in QVD
                                                        Jonathan Dienst

                                                        Looking back, I omitted the quotes around the field name in the peek in my earlier post .

                                                         

                                                        Jonathan

                                                          • Re: Currency - store in QVD
                                                            Evelien Morel

                                                            Jonathan,

                                                             

                                                            I changed the peek function, it works now.

                                                            Thanks a lot for all your help!

                                                             

                                                             

                                                            Elanir,

                                                             

                                                            here, you find the code.

                                                            I didn't add the extra code you suggested. That you will have to do yourself.

                                                             

                                                            The only problem left now is that the values in ToEur and FromEur are sometimes numeric, sometimes text.

                                                            I don't know what's going wrong. Can you tell me, if you are going to use the code yourself, if you also have this problem?

                                                             

                                                            Thanks.

                                                             

                                                            T_LastDate:
                                                            LOAD
                                                            Max(ExchangeDate) As MaxExchangeDate 
                                                            From EuroRates.qvd (qvd);
                                                            
                                                            Let zMaxExchangeDate = Peek('MaxExchangeDate');
                                                            DROP Table T_LastDate;
                                                            
                                                            For zi = zMaxExchangeDate + 1 to Today()
                                                            
                                                                Let dd = Date(zi, 'MM/DD/YY');
                                                                 
                                                                EuroRates:
                                                                LOAD
                                                                 Date(Today()-$(i))                                             as ExchangeDate,
                                                                 Currency, 
                                                                 Code, 
                                                                 [EUR/1 Unit]                                                    as ToEUR, 
                                                                 [Units/1 EUR]                                                    as FromEUR,
                                                                 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
                                                               
                                                                
                                                                FROM
                                                                [http://www.oanda.com/convert/fxdaily?date=$(dd)&date_fmt=us&exch=EUR&lang=en&sel_list=USD_HUF_UAH_RUB_PLN_CNY&value=1&format=HTML&redirected=1]
                                                                (html, codepage is 1252, embedded labels, table is @1);
                                                            Next
                                                            
                                                            
                                                            Concatenate (EuroRates)
                                                            LOAD * From EuroRates.qvd (qvd)
                                                            Where Not(Exists(ExchangeDate));
                                                            
                                                            Store EuroRates into EuroRates.QVD;
                                                            
                                                              • Re: Currency - store in QVD

                                                                hi evelien,

                                                                 

                                                                i already managed to make it work, thank you.

                                                                 

                                                                attached is the currency model i built. my use of it is a little diffrent than yours since i want to hold all exchange rates from and to Euro / Dollar.

                                                                i added the peace of code i suggested so that model is updates once a day every day and gets only the new rates.

                                                                 

                                                                i didnt see any text / numeric problems in the result.

                                                                 

                                                                 

                                                                looking at your code, i think there is a mistake ( or somthing i dont understand)

                                                                 

                                                                you use 'zi' as a parameter for the loop but use ' i ' for the caculation of dates. how does ' i ' get his +1 step?

                                                                 

                                                                all your date and time calculations are based on '(Today()-$(i))' while the date in the html string is based on 'zi'. this will give you the right exchange rates for the wrong dates.

                                                                 

                                                                anyway, if you use the the "if" i suggested, the process will finish after 1 second which is good, and than if you only need a range of dates you select them in a different QVW  and in case you need different dates for other implementations, you already have your updated QVD and dont need to recreate it again.....

                                      • Re: Currency - store in QVD
                                        Christian Conejero

                                        Don't forget to write the whole sentence

                                        Store EuroRates into EuroRates.QVD; wrong

                                         

                                        Store EuroRates into EuroRates.QVD (qvd); right.