Quantitative finance collector
C++ Matlab VBA/Excel Java Mathematica R/Splus Net Code Site Other
Aug 5

Isin Cusip Conversion

Posted by abiao at 21:38 | Code » VBA/Excel | Comments(3) | Reads(16848)
Long time no blog. Just to let you know I am still alive, busy with my own PhD research, collecting & cleaning data, programming, making my hands dirty...

Data massaging is not fun, what makes us more upset is different data providers have their own data format, name, code, etc., matching the data from several sources is not so easy, for example, WRDS includes CUSIP code while Datastream provides ISIN. I didn't understand why they do business like that but now I get it, similar as those cell phone manufacturers have distinct chargers and plug-in, not because it's hard to standardize, but a way to impose customers to use always their own products.

Anyway, you can convert ISIN code to CUSIP easily once you understand the rule, ISIN is a 12-digit number while CUSIP is a 9-digit one (at least the case for US corporate bond), so what you need to do is to first strip off the first 2 characters representing country code and then remove the last digit which is a check digit for catching error.

Suppose your ISIN code is in cell A1, ISIN CUSIP conversion can be done easily in Excel as "=left(right(A1, 10), 9)", for instance, ISIN US885797AB65 equals CUSIP 885797AB6.

As always, I have been looking for guest writers.


Tags: ,
5764429ad64
Thanks !! this saved me !
If you do not have Excel, and want to do an online conversion of ISIN to CUSIP, you can also go to http://sites.google.com/site/isincodestools (ISIN to NSIN convertor) !
Pages: 1/1 First page 1 Final page
Add a comment
Emots
Enable HTML
Enable UBB
Enable Emots
Hidden
Remember
Nickname   Password   Optional
Site URI   Email   [Register]