Bloomberg BDH function with ISIN

Question

I have to download historical end of day data for a huge list of stocks. I found on the bloomberg excel add-in the function BDH that is very useful. That is what I need but there is an issue: my stocks are identified by ISINs and i have tried in many way to put the ISINs in the first field of the function but it doesn't work. The function isn't able to identify the security by the ISIN despite the fact that is reported as security identifier in the bloomberg formula syntax: look at slide 24 here http://lgdata.s3-website-us-east-1.amazonaws.com/docs/205/56376/bloomberg%20excel%20desktop%20guide.pdf

Solution 1

The problem is that an isin does not identify the exchange, only an issuer.

Let's say your isin is US4592001014 (IBM), one way to do it would be:

• get the ticker (in A1):

  =BDP("US4592001014 ISIN", "TICKER") => IBM
  

• get a proper symbol (in A2)

  =BDP("US4592001014 ISIN", "PARSEKYABLE_DES") => IBM XX Equity
  

  where XX depends on your terminal settings, which you can check on CNDF <Go>.

• get the main exchange composite ticker, or whatever suits your need (in A3):

  =BDP(A2,"EQY_PRIM_SECURITY_COMP_EXCH") => US
  

• and finally:

  =BDP(A1&" "&A3&" Equity", "LAST_PRICE") => the last price of IBM US Equity
  

Solution 2

I had the same problem. Here's what I figured out:

=BDP(A1&"@BGN Corp", "Issuer_parent_eqy_ticker")

A1 being the ISINs. This will return the ticker number. Then just use the ticker number to get the price.

Comments

[user3002790]

The stocks are traded in Europe and there is no such fragmentation like in USA, I would simply get data for the main trading venue (that is regulated exchanges). Are you saying that there is no way to obtain directly historical data using BDH directly with ISINs?

[assylias]

It may or may not work depending on the stock. You can try a BDP("US4592001014 ISIN", "LAST PRICE") and see if it works for you. But ISIN identifies a security, not an exchange. In my case, CNDF has FP as main exchange (France), so if a stock has a listing in France, that's what I will get, even if it never trades. Imagine a German stock with a French listing, I will get the French listing. So the problem is not only fragmentation but foreign listings.