How to convert all variables with different currencies to one-currency-based variables in a big dataset?

[Jae Li]

Hello everyone!

I have a trouble about cleaning a big dataset that really needs your help now. Would you possibly have a look for me?

Since all variables are recorded by different reporting currencies, what I want to do is to convert all variables recorded by different currencies to one-currency-recorded variables, that is EUR. For example, there are two variables recorded by three different currencies, i.e., GBP, EUR and USD. Since the dataset is quite big, it's impossible for me to convert it by hand. By using the exchange rates, how can I convert all variables recorded by GBP and USD to only EUR based variables in Stata?

The dataset is similar to the following:

id NAV GAV Reporting currency
1 10m 20M GBP
2 15m 23M GBP
3 20m 24M GBP
4 26m 30M GBP
5 28m 32M USD
6 24m 23M USD
7 22m 33M USD
8 24m 30M EUR
9 40m 31M EUR
10 34m 32M EUR
... .... ....


Many thanks for your time, I will be most grateful if you can help me out!

Best,

Jae

[Apoorva Lal]

You could store GBP-EUR and USD-EUR exchange rates (for a specific date - you could get fancy with this and query it from e.g. bloomberg API) as locals/scalars and multiply NAV & GAV (which I'm assuming are the currency-recorded variables).

loc gbp_eur_er = 1.17
loc usd_eur_er = 0.91 // as of 15052017
// add more here as necessary
loc currencies usd gbp
foreach var in gav nav {
   destring `var', gen(`v'_std) ignore("M")
   foreach currency in `currencies' {
       replace `var'_std = `var'_std * ``currency'_eur_er' if reporting == "`=strupper("`currency'")'"
   }
}

[Clyde Schechter]

So something like this:

clear
input id str4 nav str4 gav str3 reporting_currency
1 10m 20M GBP
2 15m 23M GBP
3 20m 24M GBP
4 26m 30M GBP
5 28m 32M USD
6 24m 23M USD
7 22m 33M USD
8 24m 30M EUR
9 40m 31M EUR
10 34m 32M EUR
end

foreach v of varlist nav gav {
    destring `v', gen(`v'_EUR) ignore("mM")
    replace `v'_EUR = `v'_EUR * 0.91 if reporting_currency == "USD"
    replace `v'_EUR = `v'_EUR * 1.17 if reporting_currency == "GBP"
}

Notes: Based on a quick internet search for today's exchange reads between USD-EUR and GBP-EUR. Also I'm not sure if m and M stand for millions or thousands, nor, if that is the case, which is which. I'll leave it to you to work out that part.

Since the dataset is quite big, it's impossible for me to convert it by hand.

Even if it were possible, you should never do that. The chances of error in hand calculations are high. Hand calculations are poor data management practice for anything whose results might ever matter to anybody. They're just for quick-and-dirty exploratory things, if even for that.

In the future, please use the -dataex- command to post example data. That makes it easier for those who want to help you to create a faithful replica of your example in Stata using a simple copy/paste operation. To install the -dataex- command, run -ssc install dataex-. Then run -help dataex- to read the simple instructions for using it.

Added: Crossed with Apoorva Lal's excellent reply. The approach outlined there is flexible in that it will handle any number of currencies. I understood your data to have only USD and GBP (besides EUR which requires no treatment), so I provided a simplified approach that is easier to read. But if there are several different currencies, Apoorva Lal's approach is better.

[Jae Li]

@Clyde Schechter and @ Apoorva Lal Thank you so much for your guidance with the coding! The problem is now solved! :D

[Daniel Akpan]

Dear all, I am conducting a global study on Earnings Management and Compustat is my data source.

However, I need all variables to be converted to USD for 63 countries.

I would be happy if given a started command for use.


Thank you.

[Leonardo Guizzetti]

Dear all, I am conducting a global study on Earnings Management and Compustat is my data source.

However, I need all variables to be converted to USD for 63 countries.

I would be happy if given a started command for use.


Thank you.

Please start a new thread with your question, rather than resurrecting an old one. Also, before posting, please do read the FAQ for how to ask questions.

I doubt that there is a single command to get you what you want. It seems like either of the above approaches will help you solve your problem, but a key step is obtaining those current conversion rates from some source and bringing them into your data.