Comparing security identifiers for business databases Phil Reed

Comparing security identifiers for business databases Phil Reed The University of Manchester @Phil. Reed. Data phil. reed@manchester. ac. uk

Overview 1. 2. 3. 4. 5. 6. 7. Introduction to me and The University of Manchester The problem of company and other security identifiers Suggestions Going further What’s next? What do you do? Summary

1. Introduction to me and Manchester

Library restructure 2012, from subject -based to service area-based Teaching, Learning and Students 2016 review Research and Learning Services Academic Engagement Business Data Service Research Services

Convergent evolution? Subject expert Technica l expert Library: Data Specialist Business School: Research Asst. Me By John Romanes (1892): Darwin and after Darwin - Picture from John Romanes': Darwin and after Darwinhttp: //www. talkorigins. org/faqs/precursors/images/homology. jpg, Public Domain, https: //commons. wikimedia. org/w/index. php? curid=1324636

2. The problem of company and other security identifiers So many competing products!

Many kinds of security identifiers GVKEY Vendor’s own Native Many kinds CUSIP 6, 8, 9 -digit SEDOL PERM NO CIK Ticker ISIN

Other things to consider Codes can change with time Codes can be recycled Uniqueness not guaranteed Private and sovereign codes may differ Formatting hell in Excel Apple’s CUSIP 037833100 ≠ 4 E+07

3. Suggestions A light, perhaps a bit dim By Sun Ladder - Own work, CC BY-SA 3. 0, https: //commons. wikimedia. org/w/index. php? curid=9684092

Suggestions: conversion techniques List of tools and what they convert from and to Tool name From To Found Translate to PERMNO Ticker, CUSIP PERMNO WRDS CRSP Tools Capital IQ template Name (loose), ISIN CIQ ID, Ticker Capital IQ Excel Capital IQ handcoding CIQ ID, ISIN, GVKEY, CIK Capital IQ Excel Datastream handcoding CUSIP, SEDOL DS Local Code Datastream Excel TRSL hand-coding CUSIP, SEDOL, ISIN, DS Code, Ticker Thomson ONE Excel CUSIP Converter CUSIP 8/9 CUSIP 6/8/9 WRDS Compustat

CRSP to Datastream e. g. 260543103 7525108 Local Code To From CUSIP e. g. U 26054310 U 00752510 Requires Datastream Advance for Office Excel add-in and some formula editing

CRSP to Eventus PERMNO To From CUSIP

Capital IQ identifier converter Paste Values / Paste Special here If you see “No Result”, try a different identifier type. Capital IQ identifiers (CIQ ID) and Ticker are produced.

Capital IQ can convert… Name CIQ ID ISIN SEDOL CIK GVKEY To From Name (fuzzy) CIQ ID ISIN SEDOL CIK GVKEY Requires Capital IQ Excel add-in and some formula editing

Suggestions: comparison table Green Yellow Red For securities, what codes are known to what databases? = easy to match = requires conversion etc. = not available/usable

4. Going further A case study: Markit CDS & Capital IQ, combining deals info with company info

Markit Map ISIN Markit Ticker Markit CDS Company data CDS data Capital IQ Other data No common ID!

Given an incomplete mapping… The “mapping” provided to us: • Summary of which securities had activity that day, their Markit Ticker and ISIN code • Summary put in multiple XML files in a zip file • Zip file of most recent 8 days put on FTP server …Developed an intermediate database Eventually had enough data to create a partial mapping table Markit Map Markit Ticker ISIN

5. What next? Fuzzy text matching Other reference tables Software Sustainability Institute Digital Humanities

6. What do you do? What are your needs? What are the requirements?

7. Summary • • • Many different databases, many types of security identifier Not to mention other entity types (deals, people, bonds…) Can summarise major identifiers and their common use Can list existing tools to map or convert between them Harder: can write software tools to do more of this Longevity: nothing stays the same, keep systems up to date

EXTRA SLIDES

Future tables

Board. Ex, Bureau van Dijk Board. Ex ID 6, 987 Board. Ex (Europe companies) ISIN [1. . 108] 5, 424 ISIN 2, 849 41% of Board. Ex Bureau van Dijk (Amadeus) (Europe Very Large & Large companies) Bv. D ID and ISIN 12, 605

Zipped XML maps Markit via WRDS Download CDS data identified by ticker, a. k. a. markit. Ticker Custom intermediate database (XML, Java) For each match, write ISIN(s) to new column Markit FTP Server XML maps of 8 most recent days markit. Ticker, red. Entity. Code, name, ISIN(s) Win. SCP (later Java): Load zipped XML maps from FTP Server (weekly) Java application: Unzip and organise XML maps Base. X GUI: Load XML maps into master database Code Converter (Java application) Given file with column of markit. Ticker USER END XML maps Markit converter overview markit. Tic ker ISIN(s) Base. X/Java application/XQuery: For given markit. Ticker, return all ISIN(s) But what about Sovereign or Private firms if given ISINs? What about multiple ISINs?