Curency Conversion Dilemma

[Michael]

Hi Folks - Here's my situation. I have to convert local currencies into
dollars based on a conversion rate. The conversion rate changes a few times
per year. So, my sheet looks like this:

Value in Local Currency Local Currency Value in Dollars

100,000 HK 75,000
200,000 DHS 175,000


For instance, say the rate for HK conversions is .55 effective 1/1/2005. So,
I have many HK conversion transactions based on that rate. (I use a lookup
to determine what currency to convert and what rate to use). Now, on
7/1/2005, the rate changes to .65. If I change the rate in my lookup table,
then all transactions from 1/1/2005 will change. I need way to preserve the
rate conversions from 1/1/2005 through 6/30/2005. Any ideas?

Thanks.

Michael

 

[PC]

I'm assuming that your changing the rate quarterly.

Add a column to your lookup table for each quarter's FX rate so instead of
using a lookup range of A1:B5 you would use A1:E5 (Country, Q1 Rate, Q2
Rate...) (There are other ways, but it may be helpful to have the rates
laid out in a table so you can have that supporting documentation available
for other uses)

Then for the lookup use

=VLOOKUP("country",A1:E5,CEILING(MONTH("transactiondate")/3,1)+1,FALSE)

If you're only updating the rates twice a year, then change the "/3" to "/6"
and obviously eliminate the unnecessary columns from the FX table.

HTH

PC

 

[Arvi Laanemets]

Hi

You must have a date column in your transactions table. And in convert rate
lookup table, for every rate you must hase the start and end time. This
setup is very similar to one, I created to calculate production time based
on normatives. So I think you can easily adopt it.

I have a sheet Normatives with table:
Article, Normative, ValidFrom, ValidTo

Article column is formatted as text, Normative is a number,
ValidFrom/ValidTo are in date format. Into ValidFrom column the date,
started from which the normative for article was/is valid.
ValidTo is calculated by formula (as example for cell D2):
=IF(OR(A2="",C2="",C2>TODAY()),"",IF(ISERROR(MATCH(A2,OFFSET(A2,1,,COUNTA(NormArt),),0)),TODAY(),IF(OFFSET(C2,MATCH(A2,OFFSET(A2,1,,COUNTA(NormArt),),0),)>0,OFFSET(C2,MATCH(A2,OFFSET(A2,1,,COUNTA(NormArt),),0),)-1,"")))

NormArt is dynamic named range, defined as:
=INDEX(NormativesTbl,,1)

NormativesTbl is dynamic named range defined as:
=OFFSET(Normatives!$A$2,,,COUNTIF(Normatives!$A:$A,"<>")-1,4)

Entries must be ordered by ValidFrom. When there is an entrie for same
article with newer ValidFrom, ValidTo is calculates as previous day from
this new Validfrom. When there is no newer normative, TODAY() is returned as
ValidTo value

Other dynamic ranges are defined too:
NormTime=INDEX(NormativesTbl,,2)
NormFrom=INDEX(NormativesTbl,,3)
NormTo=INDEX(NormativesTbl,,4)

On Production sheet, column B is Date, column D is Machine, column H is
Article. The normative time for p.e. row 2 is calculated as:
=IF(OR(B2="",D2="",H2=""),"",SUMPRODUCT(--(NormArt=H2),--(NormFrom<=B2),--(NormTo>=B2),NormTime))

 

[PC]

I disagree. If this is an accounting application (as it appears to be) then
the start/end dates, while implicit, are clearly defined by the overall
process and need not be explicitly stated for each individual rate. Thus
there is no need to add these two pieces of additional information for each
rate. In this case, a separate column in a table with a reference in the
column heading is sufficient.

Thus, while Arvi's approach does provide a much greater level of
flexibility, it is probably more than necessary for this application given
the overall process needs and the OP would lose the benefit of having the
rates laid out in a table for easy reference. (Speaking from experience,
having the FX information in that particular format is very useful)

PC

 

[Arvi Laanemets]

Hi

I disagree. If this is an accounting application (as it appears to be)
then
the start/end dates, while implicit, are clearly defined by the overall
process and need not be explicitly stated for each individual rate. Thus
there is no need to add these two pieces of additional information for
each
rate. In this case, a separate column in a table with a reference in the
column heading is sufficient.

It isn't accounting application. With this application, department's master
is keeping his production log and estimates how much are machines and people
engaged. At same time the Production table serves as a source for production
effiency monitoring system, where normative production time, real production
time, effective machine time, and overall working time are compared, and
according trendlines are presented on chart.

The table serves as production database for some amount of time (there exist
procedures for automatic archiving records older than some fixed date, etc),
p.e. current year's production, and production normatives are sometimes
corrected - but those changes mustn't work backwards. So I needed a design
which doesn't mess up the production table every time, when some normative
is changed.

 

[Arvi Laanemets]

Oops! I didn't read carefully enough - you did speak about Michael's
application ))