A Company Is Not a Stock
Published September 26, 2019
A company does not have a price.
A security has economic terms. A listing has a ticker and a trading venue. A quote has a price, currency, timestamp, and source. The company sits above all of them and files financial statements.
Collapsing those objects into one row produces a convenient stock table. It also produces double-counted market capitalizations, filings attached to the wrong share class, ADRs treated as incremental equity, and historical prices that silently jump between listings.
A serious stock screener needs a security master database before it needs another valuation ratio. The minimum useful model has four identities:
- Issuer: the legal or reporting entity.
- Security: a specific financial claim issued by that entity.
- Listing: a venue-specific admission of that security to trading.
- Observation: a source-specific fact recorded for a listing at a time.
Those identities form a graph. A flat company table can cache selected paths through the graph, but it cannot replace the graph.
The identifiers describe different objects
The usual identifiers are individually useful and collectively incompatible as a universal key.
A CIK identifies an SEC filer. It belongs near the issuer. One filer can have several public equity classes, debt securities, depositary receipts, and former listings. A CIK-to-ticker file is a search aid, not a security master.
An ISIN generally identifies a financial instrument. A CUSIP or SEDOL often serves a similar security-level role within its market. Those identifiers do not tell you which venue produced a price. The same fungible security can trade on more than one venue and in more than one currency.
A ticker identifies a local symbol within a venue and a validity interval. NST without a market identifier code is an incomplete key. NST on one venue can refer to a different security from NST on another venue. Even the pair can be reused after delisting.
A vendor instrument ID identifies whatever the vendor decided to model. It can represent an issuer, security, listing, composite quote, continuous history, or proprietary consolidation. The semantics have to be recorded beside the value.
The correct question is never “Which identifier is best?” The correct question is “Which object does this identifier identify?”
Four tables are still one table too few
A normalized schema might start with four tables, but the edges carry facts of their own.
An identifier assignment has a start date, an end date, an authority, and sometimes a confidence level. A primary-listing designation has a validity interval. An ADR-to-underlying relationship has a ratio that can change. A successor relationship can arise from a merger, exchange offer, reincorporation, or share-class conversion.
Treating those relationships as unlabeled foreign keys discards information needed to explain a result later.
The following Python model keeps identity assignments explicit. It uses closed-open intervals, [start, end), because adjacent assignments then meet without overlapping.
from dataclasses import dataclass
from datetime import date
from decimal import Decimal
from typing import Optional, Tuple
@dataclass(frozen=True)
class Interval:
start: date
end: Optional[date] = None
def contains(self, value: date) -> bool:
return self.start <= value and (self.end is None or value < self.end)
def overlaps(self, other: "Interval") -> bool:
left = max(self.start, other.start)
right_candidates = [d for d in (self.end, other.end) if d is not None]
right = min(right_candidates) if right_candidates else None
return right is None or left < right
@dataclass(frozen=True)
class Issuer:
issuer_id: str
legal_name: str
domicile: str
@dataclass(frozen=True)
class Security:
security_id: str
issuer_id: str
security_type: str
voting_votes_per_unit: Decimal
underlying_security_id: Optional[str] = None
underlying_units_per_unit: Optional[Decimal] = None
@dataclass(frozen=True)
class Listing:
listing_id: str
security_id: str
mic: str
local_symbol: str
currency: str
active: Interval
@dataclass(frozen=True)
class IdentifierAssignment:
scheme: str
value: str
entity_type: str
entity_id: str
active: Interval
authority: strThe internal IDs are intentionally meaningless. issuer_id, security_id, and listing_id should be durable surrogate keys generated by the system. A CUSIP should not become security_id. A ticker should not become listing_id. External identifiers are data, and data changes.
The underlying_security_id edge handles wrappers such as depositary receipts. underlying_units_per_unit records the economic ratio. An ADR representing two ordinary shares has a value of Decimal("2"). A reverse ratio uses a fractional decimal. Store the ratio exactly. Binary floating point has no useful role in a security master.
Resolution must be allowed to fail
Most identifier APIs return one row. That behavior is unsafe.
A resolver should return exactly one active assignment or raise an error. Zero matches means the reference data is incomplete. Multiple matches means the reference data is contradictory or the query omitted required context.
class IdentifierNotFound(LookupError):
pass
class IdentifierAmbiguous(LookupError):
pass
def resolve_identifier(
assignments: Tuple[IdentifierAssignment, ...],
scheme: str,
value: str,
entity_type: str,
on_date: date,
) -> str:
matches = [
assignment
for assignment in assignments
if assignment.scheme == scheme
and assignment.value == value
and assignment.entity_type == entity_type
and assignment.active.contains(on_date)
]
if not matches:
raise IdentifierNotFound(
"%s:%s has no active %s assignment on %s"
% (scheme, value, entity_type, on_date.isoformat())
)
entity_ids = sorted({assignment.entity_id for assignment in matches})
if len(entity_ids) != 1:
raise IdentifierAmbiguous(
"%s:%s resolves to %r on %s"
% (scheme, value, entity_ids, on_date.isoformat())
)
return entity_ids[0]Do not resolve ambiguity by sorting on an update timestamp and taking the last row. That converts a detectable reference-data defect into a deterministic financial error.
The exception is part of the API contract. Upstream callers can quarantine the record, request additional context, or fall back to a reviewed mapping. They cannot pretend the join succeeded.
The dangerous join is issuer to quote
The shortest path from fundamentals to price looks attractive:
CIK -> ticker -> close
That path skips the security and listing layers. It assumes one public equity security per filer, one listing per security, one currency, and one active ticker. Each assumption fails in ordinary large-cap data.
The defensible path is longer:
issuer -> issued security -> selected listing -> quote
Every arrow has a policy.
For an issuer-level valuation, select equity securities that represent issued capital. Exclude debt, options, preferred shares unless the metric explicitly includes them, treasury shares if share counts are already net, and depositary receipts that wrap an underlying class. Then select one quote source for each included security.
The selected listing does not have to be the legal primary listing. It has to be an approved price source for that calculation. Liquidity, market hours, stale-quote rules, currency conversion, and corporate-action coverage all matter. Calling the field primary_ticker hides those decisions. Calling it valuation_price_source_listing_id records them.
Validate the graph before loading prices
Reference-data checks are cheap compared with repairing derived histories.
The first invariant is local uniqueness: the same (MIC, local_symbol) cannot identify two listings during overlapping intervals.
The second invariant is identifier uniqueness within its declared entity type: one active (scheme, value, entity_type) should resolve to one internal entity.
The third invariant is referential: every listing points to an existing security, and every security points to an existing issuer.
The fourth invariant is semantic: a wrapper ratio requires an underlying security, and an underlying edge requires a positive ratio.
from collections import defaultdict
from typing import Iterable, List
def validate_security_master(
issuers: Iterable[Issuer],
securities: Iterable[Security],
listings: Iterable[Listing],
assignments: Iterable[IdentifierAssignment],
) -> List[str]:
errors = []
issuer_ids = {issuer.issuer_id for issuer in issuers}
security_by_id = {security.security_id: security for security in securities}
listing_rows = list(listings)
assignment_rows = list(assignments)
for security in security_by_id.values():
if security.issuer_id not in issuer_ids:
errors.append(
"security %s references missing issuer %s"
% (security.security_id, security.issuer_id)
)
has_underlying = security.underlying_security_id is not None
has_ratio = security.underlying_units_per_unit is not None
if has_underlying != has_ratio:
errors.append(
"security %s has an incomplete underlying relationship"
% security.security_id
)
if has_ratio and security.underlying_units_per_unit <= 0:
errors.append(
"security %s has a non-positive underlying ratio"
% security.security_id
)
if has_underlying and security.underlying_security_id not in security_by_id:
errors.append(
"security %s references missing underlying %s"
% (security.security_id, security.underlying_security_id)
)
for listing in listing_rows:
if listing.security_id not in security_by_id:
errors.append(
"listing %s references missing security %s"
% (listing.listing_id, listing.security_id)
)
listings_by_symbol = defaultdict(list)
for listing in listing_rows:
listings_by_symbol[(listing.mic, listing.local_symbol)].append(listing)
for key, rows in listings_by_symbol.items():
for index, left in enumerate(rows):
for right in rows[index + 1:]:
if left.listing_id != right.listing_id and left.active.overlaps(right.active):
errors.append(
"overlapping listing key %r: %s and %s"
% (key, left.listing_id, right.listing_id)
)
assignments_by_key = defaultdict(list)
for assignment in assignment_rows:
key = (assignment.scheme, assignment.value, assignment.entity_type)
assignments_by_key[key].append(assignment)
for key, rows in assignments_by_key.items():
for index, left in enumerate(rows):
for right in rows[index + 1:]:
if left.entity_id != right.entity_id and left.active.overlaps(right.active):
errors.append(
"overlapping identifier assignment %r: %s and %s"
% (key, left.entity_id, right.entity_id)
)
return errorsRun these checks on every reference-data load. Rejecting a batch is preferable to publishing a cleanly formatted valuation for the wrong security.
A production validator should add cycle detection for underlying relationships, controlled vocabularies for security types, currency validation, and source precedence. It should also preserve rejected rows. Deleting an impossible mapping removes the evidence needed to fix the feed.
ADRs expose weak valuation models
Depositary receipts are a useful test because they look like ordinary shares and trade like ordinary shares. Economically, they wrap another security at a stated ratio.
Suppose an issuer has 100 million ordinary shares outstanding. A depositary bank creates ADRs, each representing two ordinary shares. Those ADRs trade in the United States. Adding ADR “shares outstanding” to ordinary shares outstanding invents equity. Multiplying both securities by their market prices and summing them counts the same claim twice.
The issued-capital security belongs in the market-capitalization sum. The ADR can supply a price when the ordinary listing is unavailable, provided the price is converted through the depositary ratio and currency.
from dataclasses import dataclass
from datetime import datetime
from decimal import Decimal
from typing import Dict, Iterable, Optional
@dataclass(frozen=True)
class EquityCapital:
security_id: str
issuer_id: str
shares_outstanding: Decimal
@dataclass(frozen=True)
class Quote:
listing_id: str
observed_at: datetime
price: Decimal
currency: str
def latest_quote_by_listing(quotes: Iterable[Quote]) -> Dict[str, Quote]:
latest = {}
for quote in quotes:
previous = latest.get(quote.listing_id)
if previous is None or quote.observed_at > previous.observed_at:
latest[quote.listing_id] = quote
return latest
def price_in_usd(
security_id: str,
securities: Dict[str, Security],
listings: Iterable[Listing],
latest_quotes: Dict[str, Quote],
usd_per_currency: Dict[str, Decimal],
) -> Optional[Decimal]:
direct = []
wrappers = []
for listing in listings:
quote = latest_quotes.get(listing.listing_id)
if quote is None:
continue
security = securities[listing.security_id]
value_usd = quote.price * usd_per_currency[quote.currency]
if listing.security_id == security_id:
direct.append(value_usd)
elif security.underlying_security_id == security_id:
wrappers.append(value_usd / security.underlying_units_per_unit)
candidates = direct or wrappers
return candidates[0] if candidates else None
def issuer_market_cap_usd(
issuer_id: str,
capital: Iterable[EquityCapital],
securities: Dict[str, Security],
listings: Iterable[Listing],
quotes: Iterable[Quote],
usd_per_currency: Dict[str, Decimal],
) -> Decimal:
latest_quotes = latest_quote_by_listing(quotes)
listing_rows = tuple(listings)
total = Decimal("0")
for equity_class in capital:
if equity_class.issuer_id != issuer_id:
continue
price = price_in_usd(
equity_class.security_id,
securities,
listing_rows,
latest_quotes,
usd_per_currency,
)
if price is None:
raise ValueError(
"no approved price for security %s" % equity_class.security_id
)
total += equity_class.shares_outstanding * price
return totalThe example omits quote-source ranking to keep the mechanism visible. A real engine should rank candidate listings explicitly and reject stale observations. It should never depend on dictionary iteration order or whichever vendor row arrived first.
The important detail is the direction of normalization. The ADR quote is converted into a price for the underlying issued-capital security. The ADR itself is not added to issued capital.
The same pattern handles local shares and global depositary receipts, dual listings of a fungible line, and temporary loss of a preferred venue. It does not handle every cross-listed structure. Some securities are economically related without being fungible. That distinction belongs in the relationship type, not in a comment beside a ticker.
A security master is an append-only explanation
Reference data is often maintained as a current snapshot because the tables are small. That is a category error. The value of a security master lies in its ability to explain past joins.
When a mapping changes, close the old interval and append a new assignment. Keep the source payload, ingestion timestamp, and authority. Record manual overrides as separate assertions with an operator, reason, and ticket. Never overwrite the vendor row that caused the override.
A useful assignment record has two clocks:
- The interval during which the mapping is valid in the market.
- The interval during which the system believed the mapping.
The second clock allows a valuation to be reproduced as originally published and recalculated using corrected reference data. This is broader than price history. Identity itself has revision history.
Do not expose that complexity through every application query. Build reviewed, point-specific projections for common access paths:
- active listings for an issuer on a date;
- approved valuation listing for a security on a date;
- active identifier assignments for an entity;
- security successors and predecessors;
- wrappers and their underlying ratios;
- issuer equity classes included in market capitalization.
Those projections are caches over the graph. They can be rebuilt.
The API should return provenance with the answer
A screener API usually returns a ticker, company name, price, and ratio. That is insufficient for debugging.
For every derived valuation, retain the identity path used to compute it:
from dataclasses import dataclass
from datetime import date, datetime
from decimal import Decimal
from typing import Tuple
@dataclass(frozen=True)
class ValuationInput:
issuer_id: str
security_id: str
listing_id: str
quote_observed_at: datetime
shares_as_of: date
shares_outstanding: Decimal
price: Decimal
price_currency: str
fx_rate_to_usd: Decimal
relationship_path: Tuple[str, ...]
@dataclass(frozen=True)
class MarketCapResult:
issuer_id: str
value_usd: Decimal
inputs: Tuple[ValuationInput, ...]The relationship_path can contain internal edge IDs or immutable assertion IDs. It lets an operator answer a concrete question: which share class, listing, quote, ratio, and foreign-exchange rate produced this number?
Without provenance, incident response turns into archaeology across mutable tables.
Flat exports remain useful
Consumers still want one row per stock. Give them one, with a declared grain.
A useful export grain is:
one row per active listing per evaluation date
That row can include denormalized issuer and security fields for convenience. It should retain issuer_id, security_id, and listing_id. It should also label the listing role, security type, quote currency, and valuation inclusion policy.
Another useful grain is:
one row per issuer equity class per evaluation date
That export supports market-capitalization and enterprise-value calculations. It should select one price source and include the selected listing_id rather than a bare ticker.
A row named company_stock with no declared grain will become both exports at once. Duplicate rows will appear, and someone will remove them with DISTINCT. The resulting number will depend on which columns happened to be selected.
The practical rules
A security master for a stock screener can begin small if the boundaries are strict.
Use separate surrogate keys for issuers, securities, and listings. Store external identifiers as interval-valued assignments. Put filings on issuers, share counts on securities, tickers on listings, and prices on observations. Model ADRs and similar wrappers with explicit ratios. Select one approved price path for each issued equity class. Preserve every mapping change and every correction.
Reject ambiguous resolution. Reject overlapping listing keys. Reject impossible wrapper relationships. Return provenance with derived values.
The database will contain more rows than the company table it replaces. The valuation code will contain fewer exceptions.
A company is the entity that owes the claim. A security is the claim. A listing is where the claim trades. A quote is one observation of that trade.
A fundamental stock screener needs all four.