Currency conversion for multiple years
Hello everyone!
Each data row has two payments (payment1, payment2) which are not necessarily in the same currency. The corresponding currencies are the variables currency1 and currency2. Within each row, both payments are from the same year, but years differ across rows.
Now what I want to do is to convert all payments in US dollars. Ideally, Stata would get the exchange rate from the corresponding year (maybe from a second data set which would contain just the exchange rates by year). Is there a way to do this?
Using the last row from the example data, Stata should get the 1996 exchange rate between the dollar and the Italian Lira (this was before the Euro) and convert the 24800 Italian Lira into US$. Then, Stata should get the 1996 exchange rate between the dollar and the British Pound and convert the 12.4 British Pounds into US$.
I'm sure that others have had this issue before, but I could not find any solution on Statalist. A similar problem has been discussed, but not for exchange rates from different years (https://www.statalist.org/forums/for...-a-big-dataset).
Here is an example of my dataset:
clear
input double(payment1 payment2) int year str74 currency1 str3 currency2
10 . 1999 "SGD" "."
23.125 24.375 2004 "ILS" "ILS"
2.32 . 2011 "USD" "."
20.5 . 1997 "USD" "."
24800 12.4 1996 "ITL" "GBP"
end
Each data row has two payments (payment1, payment2) which are not necessarily in the same currency. The corresponding currencies are the variables currency1 and currency2. Within each row, both payments are from the same year, but years differ across rows.
Now what I want to do is to convert all payments in US dollars. Ideally, Stata would get the exchange rate from the corresponding year (maybe from a second data set which would contain just the exchange rates by year). Is there a way to do this?
Using the last row from the example data, Stata should get the 1996 exchange rate between the dollar and the Italian Lira (this was before the Euro) and convert the 24800 Italian Lira into US$. Then, Stata should get the 1996 exchange rate between the dollar and the British Pound and convert the 12.4 British Pounds into US$.
I'm sure that others have had this issue before, but I could not find any solution on Statalist. A similar problem has been discussed, but not for exchange rates from different years (https://www.statalist.org/forums/for...-a-big-dataset).
Here is an example of my dataset:
clear
input double(payment1 payment2) int year str74 currency1 str3 currency2
10 . 1999 "SGD" "."
23.125 24.375 2004 "ILS" "ILS"
2.32 . 2011 "USD" "."
20.5 . 1997 "USD" "."
24800 12.4 1996 "ITL" "GBP"
end
Comment