currency conversion of historical data

[jason_nicholas]

This site has been indispensable in developing the program needed to analyze my company data. Hopefully, this thread can help with a solution for a relatively tricky issue.

I am loading cost data from our global locations, and I am looking for a way to convert the cost from the local currency to USD for the report. UK data is entered into the source as Pounds, the rest of Europe as Euros, and different Asia locations are entered in their local currency. However, the source data system does not distinguish this in the exports. ie- the numbers are accurate for the regional currency, but show up currency-neutral in the report. There is no indicator, aside from the building address, which will allow me to determine which currency the total cost is in.

As a second step, I want to be able to convert this historical data based on the exchange rate on the date of the entry, as opposed to the exchange rate of the day I run the report.

Here's what I have:

LOAD

[Booking Date]

Building

Cost

FROM (host document.xls)

LOAD * INLINE [

Currency, Building

Pound, Buildingname1

Pound, Buildingname2

Euro, Buildingname2

Rupee,Buildingname4

Dollar, Buildingname5

];

However, that inline load statement would be ridiculously long because there are maybe 100 buildings on my list. I could create an Excel chart listing all of the buildings and their appropriate currency, but I don't know how to take the next step and bring that into Qlikview in an effective way.

So, my questions are:

A- is this best handled through a long inline load statement, a pre-formed Excel chart, or some way I haven't thought of?

B- Once I have Qlikview looking at the different buildings in their appropriate currency categories, how do I program it to generate a total of the Cost multiplied by the exchange rate?

C- How to I generate an exchange rate dynamically based on the date? I know I can load a web source, such as http://www.xrates.com/historical to bring the data in, but I don't know how to use the [Booking Date] field from the host document to control the date selector on that page. The data loaded when I perform this function is based on whatever is displaying natively on that page, and does not provide a date search option.

[Anonymous]

HI Jason,

In one way you can take a list of currencies and their exchange rate in USD and then you can map your data with that excel.

If you can share a sample of your data so will be helpful for us to figure it out.

Thanks,

[praveenkumar_s]

plz send any snapshot..so that we can help..

[Vegar]

You could try this code created by my colleague joachim.boivie‌ on http://bi-effekten.se/2012/04/16/flera-valutor-och-avsaknaden-av-valutakurser/ (in Swedish, but the important part is the script below)

SET SourceQVDPath = 'Currencies.qvd';

SUB GetAllCurrenciesFromHistory

    //Check if qvd exists

    IF QvdCreateTime($(SourceQVDPath)) then

        //Load data from qvd

        Currencies:

        LOAD 

             currency,

             rate,

             time

        FROM

        $(SourceQVDPath)(qvd);

        //Get max date

        temp:

        LOAD

            MAX(time) as MaxDate

        Resident Currencies;

        //Store maxdate into var.

        LET maxDate = FieldValue('MaxDate',1);

    ELSE

        //Set maxdate to first date available. (ECB has currencies from 1999-01-01

        LET maxDate = '1999-01-01';

        Currencies:

        LOAD * INLINE [

            tempField

            1

        ];

    END IF

    //Load dates where is greater then maxdate

    tempCurrencies:

    LOAD

    time,

    %Key_Cube_1FC04070DD9A58E4 as key

     FROM [http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml] (XmlSimple, Table is [Envelope/Cube/Cube]) WHERE (time > '$(maxDate)');

    //join data on to date

    right join

    LOAD currency,

    rate,

    %Key_Cube_1FC04070DD9A58E4 as key

    FROM [http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml] (XmlSimple, Table is [Envelope/Cube/Cube/Cube]);

    //Concatenate tables

    Concatenate (Currencies)

    LOAD

        time,

        currency,

        rate

    Resident tempCurrencies;

    Drop table tempCurrencies;

    store Currencies INTO $(SourceQVDPath)(qvd);

    drop table Currencies;

    exit Script;

ENDSUB

[jason_nicholas]

Unfortunately, I have file upload restrictions, which prohibit me from adding a snapshot. Text description is the best I can do.

The [Cost] data comes in numeric form, without any currency indicator. But if that data was entered in London, it is in Pounds. If it were entered in Paris, it is in Euros. In India, Rupees, and in US, Dollars.

If the [Building] is one of 10-20 particular London locations, that is the only way I know it is in Pounds. Same goes for any other location. [Building] is an address, and that is my only source of designating currency. So my goal is to use the entry in the [Building] field as an indicator to determine what currency to change, and use the [Booking Date] field to determine the appropriate exchange rate.

My alternate option is to handle it externally. I can filter down to a currency group, take the sum and multiply it by the exchange rate on the billing date for that month. My concern is that it is a manual process, and I am looking to reduce manual external efforts. It may not be possible in this case, but I appreciate any suggestions.

[jason_nicholas]

Thank you, Vegar. I am going to try to unpack this and give it a shot. Unfortunately, as a novice to Qlikview and coding in general, I may not be able to use something this complex. If I can't explain it, I can't use it in my current project. If it ends up being a solution beyond my ability, I do hope it helps a future viewer of this thread.