How to convert amounts from one currency to another with the correct exchange rate?

[#1]

Hi,

I have a Purchases table in which I have purchases in different currencies. I would need to convert the purchase amounts to my home currency. For that I have an exchrate table where the exchange rates are. The problem is that there may be many exchange rates for the same currency in that table because they have been inserted in the table at different times. However, I would need to pick the one that is closest to the date when the purchase was done.

I have a simple example of the problem attached here. You can see more closely what I mean by examining the small piece of script in the attachment. The example script is also displayed below.

Purchases:
LOAD* INLINE [
Purchase_Date, Amount, CurrencyCode
'14.1.2009', 100, 'USD'
];

Exchrates:
LOAD* INLINE [
Input_Date, Exchrate, CurrencyCode
'1.1.2009', 1.1, 'USD'
'1.2.2009', 1.2, 'USD'
'1.3.2009', 1.3, 'USD'
];

As you can see I have a purchase done in 14.1.2009 and therefore I would need to use the Exchrate '1.1' from the Exchrates table. However, I find it difficult to define that '1.1' should be picked and used in converting Amount 100 to home currency. Do you have any ideas how to do it?

One solution might be that a calendar table would be created based on the min and max dates of one of the Date -fields. Then the Exchrates and currency codes could be applymapped to the correct dates like this:

'1.1.2009', 1.1, 'USD'
'2.1.2009', - , -
'3.1.2009', - , -
.
.
.
'1.2.2009', 1.2, 'USD'
'2.2.2009', - , 'USD'
.
.
.
'1.3.2009', 1.3, 'USD'

Then I would have all the dates and I could match each date from Purchase_Date -field with the correct date in the table above. However, as all the dates in the above table don't have the respective exchange rates, it's not possible to pick up the exchange rate for each purchase amount. Therefore I would need guidance in how to generate the missing exchange rates for the dates like 2.1.2009 and 3.1.2009. I would like to have the rates generated so that
1.1.2009-31.1.2009 would have the rate 1.1 and for February it would be 1.2. In other words, every time when there is a new exchange rate value in the table, it would be picked up and inserted for the following dates until the next exchange rate value is found.

Can anybody let me know how this kind of problem could be solved?

Thanks!

-Petteri

[johnw]

This isn't exactly your example, but it's similar, so it might be a reasonable starting point. To convert to your example, yes, I'd think you'd want a calendar with exchange rates for each date. To fill in the missing exchange rates, probably first load in a table that sets an ending date for each currency to the next date in sorted order, minus one day. Then do some sort of intervalmatch to generate the rates for every currency for every day.