Mapping alternative data to a security master

Joining company-level data to a security master looks like a lookup and behaves like a many-to-many problem. One company can have several securities, one security can trade on several venues, a brand belongs to a parent, and identifiers change. This guide sets out which identifiers Fokals carries and on which rows, the order in which to try them, how to treat brands, delisted names and check digits, and how to measure the match before you build a signal on it.

What each identifier identifies

Every listed company carries its ticker, MIC, ISIN, LEI and share-class FIGI, and its SEC CIK where it has one. The company index holds equity listings in 79 countries, and every dataset that names a company carries its identifiers when the company or its parent is listed: Technology Stack, Job Postings, Hiring Activity, Intent Scores, Company News and the rest. The comparison of ISIN, LEI and FIGI covers who issues each one, and the guide to the identifiers that make company data joinable covers how they fit together.

The levels differ, and that is where most mismatches start. A company-level signal such as hiring belongs to the issuer, while your master holds securities. The join fans out from one company to every security it has, and it fans in when several rows share an identifier.

The order to try keys

Try the most specific key first: isin where your master has one, then the share-class FIGI, then ticker with mic. Record which key matched each security in your own bridge table, so that you can measure and audit the match.

create table company_bridge as
select security_id, company_id, basis
from (
  select
    k.*,
    row_number() over (partition by security_id order by rank) as pick
  from (
    select m.security_id, l.company_id, 1 as rank, 'isin' as basis
    from security_master m
    join listed_securities l on l.isin = m.isin
    where l.company_id is not null
    union
    select m.security_id, l.company_id, 2, 'figi'
    from security_master m
    join listed_securities l on l.share_class_figi = m.share_class_figi
    where l.company_id is not null
    union
    select m.security_id, l.company_id, 3, 'ticker_mic'
    from security_master m
    join listed_securities l on l.ticker = m.ticker and l.mic = m.mic
    where l.company_id is not null
  ) k
) ranked
where pick = 1;

If your master holds listing-level FIGIs, join them to listed_securities.figi instead. Normalise tickers before you compare them, because the separator in a share-class ticker varies: ACME.B, ACME-B and ACME/B are one ticker. Where two keys point to different companies for the same security, review the pair and do not let the rank decide. A security that trades on several venues has one primary listing, the home market's where the issuer's country is known. If your master holds a different listing, match on isin or the share-class FIGI and not on ticker and mic.

Brands, subsidiaries and parents

A brand or subsidiary carries the identifiers of its listed parent. The rows of the brand and the rows of the parent therefore share an isin and differ in company_id. Two joins give two answers, so choose deliberately.

• Join on the identifiers the rows carry. Hiring Activity joined on isin to your master brings in the parent's own rows and those of its brands, so the group's postings add up under one security. Use it for questions about the group.
• Join on company_id through listed_securities. This reaches only the listed company's own row, because a listing is tied to one company. Use it for questions about the listed entity alone.

select m.security_id, h.day, sum(h.open_postings) as open_postings
from company_hiring_daily h
join security_master m on m.isin = h.isin   -- brand rows carry the parent's ISIN
group by m.security_id, h.day;

Acme Robotics is an invented listed company with a brand, Acme Home, whose website has its own job board. The figures are illustrative.

A join on isin returns 52 open postings for the security. A join on company_id through listed_securities returns 40.

Keep company_id as the grain of your own tables so that you can do either, and aggregate to the security in a separate step. A row carries one ISIN, so a second share class of the same issuer will not join on it. Where your master links securities to an issuer, join the data to the issuer on lei, let each security inherit the result, and never add the two classes together in a portfolio total.

Delisted names stay

listed_securities keeps a listing once it is in the index. status is active, or delisted once the listing has gone, and the row stays, so a join from an old holding still finds its company. The status says that a listing has gone and not when, so keep delisting dates in your master. Your filter also decides the list: status = 'active' gives today's, while leaving it off keeps the names that left, so do not filter on active for any history.

GET /api/v1/listed?status=delisted&exchange=XNAS&limit=200

The table holds the equity of each issuer: common and foreign shares, depositary receipts, REITs and partnership shares. If your master also holds funds, exchange-traded products, rights, warrants or preferred stock, remove those instrument types before you compute a match rate, or the share left without a match will be inflated by instruments that belong to no issuer's equity row.

Check digits

Every ISIN and LEI Fokals delivers passes its check digit, so a key in your master that fails its check digit cannot match. Run the test on your master before you join. Once every key passes, a failed join becomes a coverage question and not a typing error.

An ISIN is 12 characters: a two-letter country code, a nine-character national number and one check digit, computed with the Luhn algorithm once each letter is replaced by two digits. An LEI is 20 characters, the last two of which are check digits, and the number built from its letters and digits leaves a remainder of 1 when divided by 97.

import re

def digits(s):
    # a letter becomes two digits (A=10 ... Z=35); a digit stays as it is
    return "".join(str(int(c, 36)) for c in s)

def isin_ok(s):
    if not re.fullmatch(r"[A-Z]{2}[A-Z0-9]{9}[0-9]", s):
        return False
    total = 0
    for i, ch in enumerate(reversed(digits(s))):
        n = int(ch)
        if i % 2 == 1:
            n *= 2
            total += n // 10 + n % 10
        else:
            total += n
    return total % 10 == 0

def lei_ok(s):
    return bool(re.fullmatch(r"[A-Z0-9]{18}[0-9]{2}", s)) and int(digits(s)) % 97 == 1

For a US ISIN the CUSIP is characters 3 to 11, so a master keyed on CUSIP can still join through the ISIN.

Measuring the match

Count the securities matched by each key, then explain what remains.

A listing that carries a company_id brings the company's data with it, and the share of listings that do varies by country. Measure it by country before you decide which markets to build on. The coverage page describes the companies covered.

select
  country,
  count(*)                                       as listings,
  count(company_id)                              as with_company,
  round(100.0 * count(company_id) / count(*), 1) as pct_with_company
from listed_securities
where status = 'active'
group by country
order by listings desc;

Expect keys to move. A provisional FIGI is replaced when the final one is available, and corporate actions change ISINs and tickers. Treat company_id as the permanent key and identifiers as attributes dated by the row.

Join each row to your master as of the row's date, using the effective dates of your own identifier history, because the ISIN or ticker a security carries today may not be the one it carried then.

select h.day, i.security_id, sum(h.open_postings) as open_postings
from company_hiring_daily h
join security_identifiers i
  on i.id_type = 'ISIN'
 and i.id_value = h.isin
 and h.day >= i.valid_from
 and (i.valid_to is null or h.day < i.valid_to)
group by h.day, i.security_id;

Refresh the bridge on a schedule. Rebuild it each time you load new data, keep each version with its date, and report the match rate by key every time, so that a drop is visible before it reaches a signal. The guide to point-in-time datasets for back-tests covers the dating of everything else. The page for investors describes identifier mapping in more detail, and the data dictionary defines every column named here.

Extending the mapping

• Other instruments. To attach company data to a fund, an exchange-traded product, a warrant, preferred stock or debt, go through its issuer, on lei where your master has one.
• Private companies. A verified private company carries a company ID like every other company, with no listing to map. Bring it into your own tables on company_id, and match it to your records by website.
• Group and issuer views. The identifiers on every row let you report the same data for the group, the listed entity or the issuer, as the question needs.

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.