As part of a research project I ran a query on the Mergent FISD database using the WRDS website. The output included the CUSIPs of numerous bond issues (>10000). I am using this data to run event studies and I need to obtain the CUSIP of the stock of the company that issued the bonds. Since I have over 10000 data points, I need to automate this conversion or lookup process. Any programming language or method would be fine with me in this situation.

  • 2
    $\begingroup$ There is no conversion process. You'll need a reference data service to look-up codes for products issued by a given corporation. $\endgroup$ Jul 8 '13 at 18:46
  • $\begingroup$ So you are saying that there is no way to automate the process? $\endgroup$
    – ikemblem
    Jul 8 '13 at 20:04
  • $\begingroup$ The "automated" way is to look-up the issuer of the bond, then find all stocks from that issuer. Again, there's no conversion process. Just look it up in a table from a data vendor. $\endgroup$ Jul 8 '13 at 20:35
  • $\begingroup$ I understand that there is no "conversion process". By automation I meant, is there a way to write a script of some sort or use some software that can output an ID number representing the issuer of the bond so that I can then find the CUSIP for the stock of the company. I can't do this manually, one by one, since I have over 10,000 bond issues. By hand it would take years. $\endgroup$
    – ikemblem
    Jul 8 '13 at 21:35
  • $\begingroup$ As is this question is misleading. What you're asking for is either a mapping(which I wouldn't expect to match your portfolio in the unlikely case someone hands their mapping over) or someone to give you the program they wrote - I don't either happening(for free) given the nature of this industry. $\endgroup$
    – jeff m
    Jul 8 '13 at 23:25

You can use this website here: http://activequote.fidelity.com/mmnet/SymLookup.phtml

I would recommend using a simple data manipulation language such as Python in order to solve your problem. You would need to write a code that read the webpage for specific CUSIP numbers and then find the specific portion of the HTML file that will contain the name of the company and pull that information out of the webpage.

For instance, in order to look up the CUSIP of 037833100, you will get the corresponding URL: http://activequote.fidelity.com/mmnet/SymLookup.phtml?reqforlookup=REQUESTFORLOOKUP&productid=mmnet&isLoggedIn=mmnet&rows=50&for=stock&by=cusip&criteria=037833100&submit=Search

Now your Python program could manipulate the URL by inserting different numbers for the criteria portion and then read the page and pull out the name of the company.

Here's some Python code you would use to get a specific stock:

import urllib2

data = urllib2.urlopen('http://activequote.fidelity.com/mmnet/SymLookup.phtml?reqforlookup=REQUESTFORLOOKUP&productid=mmnet&isLoggedIn=mmnet&rows=50&for=bond&by=cusip&criteria=CUSIPNUMBERGOESHERE&submit=Search')
data_string = data.read()
start = data_string.find("<tr><td height=\"20\" nowrap><font class=\"smallfont\">")
end = data_string[start:].find("</font>")
companyName = data_string[start:][51:end]
  • $\begingroup$ Thanks but I think that you misinterpreted my question. I have the name of the company and I have the CUSIP for each bond issue. I need to somehow get the CUSIP for the stock of the same company that issued the bonds. The CUSIP for the bond issue and the CUSIP for the stock are different. $\endgroup$
    – ikemblem
    Jul 8 '13 at 21:32

if you have access to a Bloomberg terminal, you could use this function in Excel:

=BDP("013926500 CORP","BOND To EQY TICKER")

013926500 is the CUSIP for ABB

It is very easy to automate Bloomberg / Excel functions and a quick Google should show you decent examples. Also Bloomberg terminals carry several such example APIs that should help you automate the download.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.