One of the most useful Excel functions I help students with is VLOOKUP – extremely useful to automatically bring in data from one table to another. If you have company accounts data from Compustat Global (via WRDS) for multiple companies in multiple currencies, you will need to bring in the change rate for each currency to show every field in US Dollars.
|1||Company||Currency||Total Assets Local||Exchange rate||Total Assets USD|
|2||Co. X||AUD||100||=VLOOKUP(B2, currencies, 2, FALSE)||=C2*D2|
|3||Co. Y||PLN||400||=VLOOKUP(B3, currencies, 2, FALSE)||=C3*D3|
Where ‘currencies’ is a named range elsewhere in the workbook:
The VLOOKUP command looks for the currency code in the first column and returns the rate from the second column (use ‘FALSE’ to ensure an exact match). The conversion is simply a multiplication. (Note that you can convert from USD to USD at a rate of 1.0.) But what about making sure that the exchange rate is correct for the date of the data, and what if you have data from multiple years?
Performing VLOOKUP when matching a pair of variables
Thanks to Professor Marie Dutordoir for suggesting this kind of technique!
Usually you will want to look at accounts data over several years and for several companies. If you are working with several currencies then you will need a more complicated solution to this VLOOKUP exchange rate table, considering a day/month/year for each rate-currency pair. You do not need a second lookup table or other Excel functions, you just need to have another variable of date and a variable of currency-date pairs.
The new column F (mapping) is formed from currency and year, so F2 contains “=G2&H2” (see image above). The ‘currencies’ named range now begins with a different first column produced in the same way (see image below). A VLOOKUP always matches on the first column of its range, and now this one pulls the rate from the fourth column, so rate is “=VLOOKUP(F2, currencies, 4, FALSE)” for row 2.
In the ‘currencies’ named range, I have combined the exchange rate calculation and lookup into one formula (column K). Repeat this for each data type that you wish to convert the currency for. Remember to have an entry for USD at rate 1 for each year.
This example only has the average exchange rate for each year, you may wish to extend this approach for monthly or even daily rates. If you do this, you will need to take extra care with the format of the mapping cells, but this exceeds the scope of the post.
Where to find historical exchange rates?
- Datastream (as shown in the screen above).
- The documentation for Compustat suggests using WRDS Cloud, but this requires knowledge of Unix and SAS, similar to the S&P 500 constituents example.
- Other sources of historical exchange rates (last updated July 2015).
A handy summary for those wishing to use a list of US companies Compustat in a Datastream query. In summary, a well-chosen prefix to the ticker symbol can be systematically added in Excel before creating a static or time series Datastream request. All you need to know is if the company is listed on Nasdaq or elsewhere. Thanks to EDSC for this post.
If you have US tickers as output from e.g. Compustat index constituents and you want to get stock prices from Datastream that’s possible. You can use a ticker to create a Datastream mnemonic. You have to add country code, so “U:” would do the trick. You can easily do this in Excel by concatenate U: with the column with ticker.
This solution works fine for all but the Nasdaq firms. Those will need a “@” sign in front not “U:” So just to be sure you have everything and you don’t miss any data do both so a list with “U:” and one with “@”.
The ones that don’t make sense will give errors but this way you won’t miss any. So with Ford U:F will work and give data, @ F won’t it will give an error. Similarly Micosoft @MSFT will work and give data, U:MSFT won’t it will give an error
A new resource has been added to our collection on WRDS called Research Quotient. It collates increase in total revenue with estimated investment in research and development (R&D) between 1974 and 2010. From their publicity materials:
Research Quotient = percentage increase in revenue from a 1% increase in R&D.
RQ is the output elasticity of R&D. RQ offers a universal, uniform, and reliable measure of a firm’s R&D productivity.
It is available now, for researchers at The University of Manchester, for US firms only. It is searchable by Compustat GVKEY and SIC codes, or by looking up companies by name one at a time.
See also: WRDS access and access.
Given a list of North American company ISIN codes from the Markit credit default swap database (via a lookup database), you may want to join in quarterly company fundamentals from Compustat in WRDS. Compustat does not accept ISIN codes; you have to use an intermediate source such as Capital IQ and one or more intermediate data types (GVKEY, CUSIP and Company Name).
Note, you must be using a PC with the Capital IQ Excel add-in set up. This is in the Library Finance Zone.
- Use Capital IQ Office Plug-in (CIQ) with a column of ISIN codes in Excel. Put these in column A.
- In the next column, B, get the values of column A with a prefix of “I_” so CIQ can interpret them as ISINs. Cell B2 contains =”I_”&A2
- Next get the GVKEY code in column C with a CIQ formula. Cell C2 contains =CIQ(B2, “IQ_GVKEY”) — the output will need cleaning up to remove the “GV_” prefix and any multiple matches.
- Then the CUSIP codes in column D. Cell D2 contains =CIQ(B2, “IQ_CUSIP”) — this may not work well, I used the Compustat helpdesk here to get these.
- Finally from CIQ, the company names. Column E. Cell E2 contains =CIQ(B2, “IQ_COMPANY_NAME”)
- Create a new plain text document with the values of column C (GVKEY) and upload this to Compustat in the usual way.
- For the gaps, repeat with the values of column D (CUSIP).
- For any final gaps, use the Manually enter company codes > Code Lookup tool in Compustat, typing in the first part of the names to obtain any extra GVKEY codes one by one and repeat step 6.
- Stitch together the various Compustat output files and check that all the companies are present without duplicates.
Consider that CUSIP codes can change with time, so use them with caution. Remember to keep any column of CUSIP codes with Text format, never General, Number or Scientific.
Most of our users who need North American company financial information need to link it to another data source, often with a different type of company identifier. A tool has appeared within Compustat via WRDS (which may have been there for some time) allowing you to convert either 8 or 9-digit CUSIP codes into 6, 8 or 9-digit ones. This is a bit of a surprise to me, as Compustat works best with GVKEY codes, but the different length of CUSIP codes help it to interface with other databases as follows.
- Bloomberg (9-digit CUSIP)
- Capital IQ (GVKEY has better coverage)
- CRSP (8-digit CUSIP)
- Datastream (via Local Code)
- Eventus (8-digit CUSIP)
- SDC Platinum (6-digit CUSIP)
- ThomsonONE.com (9-digit CUSIP when using the Excel add-in)
The databases you join with may not have the full coverage of CUSIP codes, so take care. There will almost always be gaps when going from one database to another.
Take scientific care of CUSIPs in Excel
A final tip when using Excel. Some CUSIP codes can be interpretted as numbers in scientific format (when the code contains numerals and the letter ‘E’, such as Genuity Inc 37248E202 read as 3.72 x 10206). Some CUSIP codes begin with leading zeroes (Apple Inc is ‘037833AL4‘) which can get lost and change meaning.
You must always make sure that a column of CUSIP codes is formatted as Text not General before you paste them in. When opening from a CSV file, consider using the Data > Get External Data > From Text menu command and explicitly set the CUSIP column as Text format.
See also our earlier post on the CRSP converter tool:
Use WRDS to convert any permno or gvkey to a cusip.
For example, using CCM Security Monthly and eliminating duplicates.
A cusip should be 9 digits and can be converted to a Datastream local code by adding a U at the front and dropping the final check digit.
260543103 (cusip) goes to U2654310 (DS local code).
(Datastream local codes can be used create a Datastream list.)
If the cusip is less than 9 digits the leading 0’s could be missing, and need to be added.
7525108 (cusip) goes to U00752510 (DS local code)
A 6-digit cusip usually indicates a company and 10 is added to give the primary listing.
Converting via a ticker is also possible but seems to be less reliable.
For a cusip 7525108, in Excel cell A2
=CONCATENATE(“0000”,A2) – in B2 adds leading zeros
=RIGHT(B2,9) – in C2 trims to 9 characters
=CONCATENATE(“U”,C2) – in D2 adds leading U
=LEFT(D2,9) – in E2 drops final checkdigit, giving DS local code.
“How can we get details of corporate bonds issued by S&P 500 companies?” This enquiry has prompted this post that concentrates on the issues of linking databases, an essential skill in many research projects.
Mergent FISD (Fixed Income Securities Database) is a comprehensive database of publicly-offered U.S. bonds available on WRDS. It is a relatively new addition to the University of Manchester research database portfolio, but the WRDS web interface follows the same general style for all databases. The key to getting your data is having a list of appropriate identifiers.
For this query an brief review of Mergent FSID on WRDS and “Issuer CUSIP” is a valid identifier.
Step 1 – Getting CUSIPs for S&P 500 companies
This is quite easy in WRDS as Compustat has an Index Constituents query (Select Compustat – North America – Index Constituents). The i0003 identifier for the S&P500 index can be found using the code lookup facility.
You select your date range, index identifier, and check that you include CUSIP, from company information, as one of the variables to include in the results.
The results can be significantly more than 500 companies, depending on your selected date range, as the constituents of the S&P 500 change over time, and some companies have had several CUSIPs over time.
Step 2 – Greeting CUSIPs into format for Mergent FISD
In the ideal world the CUSIPs from Compustat would be exactly those required by Mergent FISD. In fact a little work is needed as the CUSIPs generated are 9 digits (for the stock issue that is in the S&P 500) and FISD requires the 6 digit issues CUSIP.
Therefore we used Excel, or another program, to trim the final 3 digits from the Compustat generated CUSIPs –
931142103 -> 931142 , 00206R102 -> 00206R
This can produce some duplicates that can be eliminated.
Step 3 – Using Issuer CUSIPs to query Mergent FISD
For a WRDS query you need to copy your identifiers into a plain text (.txt) file – one identifier per line. When indicating the variable (id) “to search by” we select the Issuer CUSIP option, and then the Upload a file containing company codes.
Selecting the FISD Bond Issues Query – choosing some test dates and test variables for the results gave 2315 observations – some included in the screenshot below.
One of the strengths of WRDS is that once you have got your file of identifiers it is easy to submit another request if you want to vary the time period, or the variables in the results. You can also choose the format of the results.
CUSIP – 6-digit cusip to 9-digit cusip (posted June 2010) – has more detail on CUSIPs
S&P 500 (Standard and Poor’s 500 Index) (posted December 2011) – has more on the S&P 500 on WRDS
Additional comment on S&P 500 code
When using the “Index Constituents Code Lookup” facility on WRDS there are two potential candidates for the S&P 500 – S&P 500 Comp – Ltd, with Ticker I0003, and S&P 500 Comp – Wed, with Ticker I0010.
A little investigation on the WRDS online help reveals the difference.
“The S&P 500 LTD (last trading day) has the ticker I0003 and S&P 500 WED (last Wednesday of the month) has ticker I0010. Two indices were created because some investors felt that the “last trading day” was too volatile, and preferred the last “Wednesday” methodology. Most people use I0003 (“LTD”)” (WRDS, no date)
The monthly value of an index, or equity, is by convention taken as the last trading date of the month. However, these end of month last trading day values can be more volatile for indices where there is high volumes of options traded on their expiry date. In our query to get the index constituents the choice of index will make no difference.
WRDS. (no date) WRDS (Wharton Research Data Services) Knowledge Base with FAQs – WRDS: S&P 500 Data. [Online]. Available at: WRDS http://wrds-web.wharton.upenn.edu/wrds/ (Accessed: 25 June 2014)