The identifiers that make company data joinable

A company dataset earns its place when it joins to something you already hold: accounts in a CRM, positions in a portfolio, filings in a research store. Each of those systems is keyed differently, because each describes a different thing: a website, a security, a legal entity, a filer. This post sets out the seven identifiers our data carries, what each one names and which join it serves. After it you can choose the key for each join and say in advance whether the result will fan out.

Seven identifiers and what each one names

Deciding which records describe the same company is entity resolution, and many of its errors come from joining on a key that names something else. So start with what each key names.

The keys sit on different levels: a website, the company behind it, the legal entity that issues securities, a security, and its listing on a venue. Company data is observed at the first of those, on a website or a careers page, and is often wanted at the last, against a position or a price. A join is exact when both sides sit on the same level. It fans out when they differ: one group to several websites, one issuer to several securities, one security to several listings.

The two keys every company has

Each company in the index has one stable company ID, and every dataset that describes a company carries it. Join our datasets to each other on it, and make it the key of your own tables. It is the key of the Fokals index, so the first step is a bridge from your keys to it, built once.

For most systems the bridge is the website. Technology Stack has one row for each company website and technology and carries the website's domain, so a map from website to company, with the listing identifiers beside it, is one query.

create table company_keys as
select distinct
  domain, company_id, company,
  ticker, mic, isin, lei, figi
from company_technologies;

Join your accounts to that key table on a normalised domain, as the entry on domain matching describes, and every other dataset follows on the company ID. The map is many to one: a company can have several websites, and each brand in the index has a website of its own.

The keys of a listed company

When a company or its parent is listed, each of its rows also carries the listing's ticker, MIC, ISIN, LEI and FIGI. A listed company carries its SEC CIK where it has one, and Company Funding carries the issuer's CIK on each private capital raise reported in a regulatory filing. Five properties make a join on these keys work.

• The FIGI is the share-class FIGI. The listed securities table holds one row for every equity listing, with the listing's own FIGI and the share-class FIGI it belongs to. Every other dataset carries the share-class value, which rolls all the listings of a share class up to one line.
• A ticker travels with its MIC. A ticker names a listing on one venue, so each listing carries the ISO 10383 market identifier code beside it. Join on the pair.
• A row names one security. It has one ISIN and one FIGI. To reach a second share class of the same issuer, use the LEI, which belongs to the issuer, or the listed securities table.
• Check digits are validated. Every ISIN and every LEI in the data has passed its check digit.
• Delisted listings are kept. A listing that leaves the market is marked as delisted and stays in the table, so a join from an old position still finds its company.

The comparison of ISIN, LEI and FIGI covers the standards behind three of these keys, and the entry on the Central Index Key covers the SEC's.

How a listing is tied to a company

The listed-company index covers equity listings in 79 countries. It is built from public reference data and regulatory filings, and each listing is resolved to its company in-house.

Each security is tied to a company in a fixed order: by ISIN, then by LEI, then by ticker on the same market, and last by an exact normalised name in the same country. The basis of the match is recorded on every listing, so any link can be audited. The order matters to you because it runs from the strongest evidence to the weakest, and your own bridge should do the same: identifiers first, the domain next, a name with its country last.

How a brand reaches its parent

Three kinds of company are in the index: listed companies, the brands they own and verified private companies. Each brand or subsidiary carries its listed parent's identifiers on its own rows, so a brand's website or careers page can be tied to the security behind it. Acme Robotics is an illustrative listed company that also sells under a consumer brand, which has a website and a careers page of its own.

Grouping by ISIN adds the brand to its parent, which suits a question about the listed group. Grouping by company ID keeps the two apart, which suits a question about one business. Both are right, and the choice belongs to the question. The query below lists every listed parent that has brands or subsidiaries in your key table, so that you know which totals by ISIN are group totals.

select
  isin,
  count(distinct company_id) as companies,
  count(distinct domain)     as websites
from company_keys
where isin is not null
group by isin
having count(distinct company_id) > 1
order by companies desc;

The link from a brand or subsidiary to its listed parent is carried in the identifiers, which is the link a portfolio question needs: from the place where activity is observed to the security it belongs to. The entry on corporate hierarchy covers how records roll up from brand to subsidiary to parent.

Keeping a join sound

Four habits keep a join on these keys sound as your data and the market change.

• Make the company ID the permanent key. Tickers and listings change with corporate actions. Store the company ID as the key of your own tables, and treat the other identifiers as attributes of the row they arrived on.
• Key a private company on its website. A verified private company has the two keys every company has, its company ID and its domain. A brand of a listed group adds its parent's listing identifiers.
• Match the levels before you join. Decide whether the question is about a website, a company, an issuer or a security, and choose the key that sits on that level. Where the levels differ, expect the fan-out and aggregate on purpose.
• Report the match. Count the rows of your own table that found a company and the rows still to be matched, and keep both figures with the result.

For the join to a portfolio in full, with the order of keys and a match report, see mapping alternative data to a security master. Each field named in this post is defined in the data dictionary.

Frequently asked questions

The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.