Rolling Currency Rates

[#1]

Hi all,

I need your help.

My database contains monthly PnL figures in GBP, SEK, NOK... which are converted in EUR using the last monthly rate.

We are in September, my database contains all the PnLs until August  and they are all (January, February...August) converted using August rate.

I would like to change and use the monthly rate related to the month selected:

If I select March, this means that I want a situation at the end of March and I want the data from January, Feb and March to be converted using the rate of March.

If I select June, This means that I want a situation at the end of June and I want the data from January to June to be converted using the rate of March.

I have two tables.

the fisrt one contains the figures (Country, Year, Month, PnL account, amount in local currency, currency)

the second one contains the rates (Country, Year, Month, rate, currency)

No links for the moment between the two.

If someone who already worked on such subject could help me that would be great.

Thanks a lot.

Best regards.

Mederic Eloy

[Gysbert_Wassenaar]

I'd create a key to link the tables and create a new Month field in the rates table that includes the year too. That new field RateMonth is where you make your selection in for the rate to be used for the rate conversions.

Transactions:

LOAD

   Country,

   Year,

   Month,

   PnL,

   account,

   amount,

   currency

   autonumber(Country&currency) as Key

FROM ...sourcetable1...;

Rates:

LOAD

   autonumber(Country&currency) as Key,

   Year&'-'&Month as RateMonth,

   rate

FROM ...sourcetable2...;

[#3]

Hi M. Wassenaar,

Thank you very much for your answer but i'm not sure it works.

UK_January will be always converted using the rate of January for UK even if you launch a situation at March, or July or August.

What I would like is UK_January converted with the rate of GBP for March, July or August when I launch a situation at March, July or August, Sweden_January converted with the rate of SEK for March, July or August when I launch a situation at March, July or August...