Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Curency Conversion Dilemma
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 |
#2
|
|||
|
|||
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("transactio ndate")/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 "Michael" wrote in message news:agCle.364$xu3.202@lakeread02... 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 |
#3
|
|||
|
|||
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="",C2TODAY()),"",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(Norm Art),),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)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Michael" wrote in message news:agCle.364$xu3.202@lakeread02... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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="",C2TODAY()),"",IF(ISERROR(MATCH (A2,OFFSET(A2,1,,COUNTA(No rmArt),),0)),TODAY(),IF(OFFSET(C2,MATCH(A2,OFFSET( A2,1,,COUNTA(NormArt),),0) ,)0,OFFSET(C2,MATCH(A2,OFFSET(A2,1,,COUNTA(NormAr t),),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),--(N ormTo=B2),NormTime)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Michael" wrote in message news:agCle.364$xu3.202@lakeread02... 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 |
#5
|
|||
|
|||
Hi
"PC" wrote in message ... 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 ( My real mail address: arvil<attarkon.ee ) 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" wrote in message ... 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="",C2TODAY()),"",IF(ISERROR(MATCH (A2,OFFSET(A2,1,,COUNTA(No rmArt),),0)),TODAY(),IF(OFFSET(C2,MATCH(A2,OFFSET( A2,1,,COUNTA(NormArt),),0) ,)0,OFFSET(C2,MATCH(A2,OFFSET(A2,1,,COUNTA(NormAr t),),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),--(N ormTo=B2),NormTime)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Michael" wrote in message news:agCle.364$xu3.202@lakeread02... 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 |
#6
|
|||
|
|||
Oops! I didn't read carefully enough - you did speak about Michael's
application :-))) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import csv without automatic date conversion | Excel Discussion (Misc queries) | |||
conversion to qif or ofx file format | Excel Discussion (Misc queries) | |||
Conversion of actual fig. in Lakhs / Crores | Excel Discussion (Misc queries) | |||
Quattro to Excel File Conversion | Excel Discussion (Misc queries) | |||
Conversion lookup formula | Excel Worksheet Functions |