wilmott.com

Hi, would anyone out there point me in the correct direction?I need to create an database of equities data (static and time series), but am unsure as to what to use as a primary key in my tables as ISINs are non-unique? e.g. same instrument traded upon different exchanges would be a problem....regards

Hi,It depends on what you want to do with and which providers you want to use. Bloomberg and Reuters don't use the same code. But, the ISIN code is a standard and easy to use.

Thanks for your input. The problem I am having is that while ISIN is a unique issue identifier, it is not always a unique security identifier. ISIN alone is not sufficient for unique identification because one ISIN can be shared among different instruments in multiple exchanges for a particular stock.I am trying from a database modelling point of view trying to ascertain what would be the most best solution to create a normalised database based using relationships between different tables, which are indexed by a primary key (or a composite key).regards

Sounds as though you are after a candidate key of ISIN + Exchange. Probably have the primary key as a long for joining purposes. Depends a bit what RDBMS you are using.

yup looks like it is the way to do it, until the powers that be add an exchange identifier to the ISIN to make it unique by exchange.

I wouldn't go down that route for the simple reason is that Isins change. As do sedols, tickers, bloomberg etc. This causes untold amount of fun. The best route to follow I have found is to store the time series using your own internal identifer (which can then stay static) and use a mapping/dimension table to store the external identifier information. As rmax suggests having a unique key in this table on ISIN and exchange should work ( I don't think they recycle isin codes). You will need a process to update the ISIN data as ISINS change - I assume you wish to be able to retrieve long histories of data across id changes.

This has always been a problem with using identifiers for international securities. For US equities, there is the PERMNO for CRSP and the IBESTICK for I/B/E/S data. These two I know are unique identifiers. For foreign issues though, the SEDOLs, ISINs, and CUSIPs need to be checked for other info to ensure that you actually idenfitied the correct security.

Bloomberg has an identifier called Bloomberg Unique Identifier which is different from the Bloomberg ticker. This identifier is unique and doesn't change even when the Bloomberg ticker changes. The most ideal situation is to use the tickers from any of the fundamental databases such as S&P Id in Compustat or the Worldscope Id or from one of the largest index providers such as S&P/Citigroup indices's SSB Identifier. As far as the IBES is concerned its coverage of stocks is far quite lower than most fundamental database, but the best and the most accurate one is of the index providers e.g. Russell Index constituent identifiers (for US), Russell/Nomura (for Japan) or S&P/Citigroup or DJ Global or FTSE World etc ... MSCI can also do, but its a much smaller universe. ISIN is the most junk of all identifiers. It is pretty common for a multiply listed European stock to have a situation such that if it has 6 listings, then 3 of its 6 might share one ISIN, another 2 might have another and the last can have a different ISIN. The fact that the first two digits of ISINs are supposed to help, doesn't actually as many countries follow the rule of incorporation of country rather than listing of the equity. Sedols & Cusips can help in a number of instances. The only issue is that one needs to keep a track of their changes. There are some issues with Sedols where in some of the European countries, the cross-listings across different countries might have the same Sedol (occasionally even a US ADR and its EU listing might have the same Sedol) - in this case like rmax suggested below, Sedols + Country (or Sedol + Exchange) could help in distinguishing.