Securities Reference Data Software Problems: The 7 That Break Trading, Risk and Reporting, and How to Avoid Them

Why does the golden copy project try to master everything at once?

The characteristic scope failure in reference data is breadth. Because instrument data is the join between every system you own, every team has an interest, and the requirements gathering produces a list covering listed equities, fixed income, over the counter derivatives, structured products, loans, private assets, legal entity hierarchy, pricing and corporate actions. Each of those is a distinct attribute model, and none of them looks like the others. Eighteen months later the platform is comprehensive on paper and feeding nothing in production.

The reason it happens is political rather than technical. Narrowing the scope means telling a desk their asset class is in phase three, and nobody wants that conversation during a funding round. So the scope stays wide, the delivery date slips, and the teams that were promised relief keep maintaining their local copies, which is exactly the behaviour the programme existed to end.

The fix is to pick the asset classes carrying most of your positions and the two downstream systems that break most often, and to build for those only. A golden copy feeding two consumers reliably is a working platform. A golden copy feeding nobody is a data warehouse with ambitions. Nobody has regretted a narrow first release in this category and plenty have regretted a wide one, which is worth saying plainly because the pressure in the room always runs the other way.

What goes wrong when you load history from existing systems?

The load is where identity problems become visible, and it is unforgiving. You are pulling instrument records from several systems that each hold their own copy, each of which has been locally corrected over years, and you are trying to establish which of them describe the same instrument.

Four failures recur. Identifiers were reused, so two genuinely different instruments share a CUSIP across different periods and a naive load merges them into one record with a nonsensical history. Local overrides were applied without provenance, so a maturity date that differs from every vendor may be an operations correction that was right or a typo that was never caught, and nothing distinguishes them. Private placements, internal funds and bespoke instruments exist only in one system with no external identifier at all, so they either fall out of the load or get duplicated. And attributes were overwritten rather than versioned, so there is no way to reconstruct what any instrument looked like on a date last year.

What works is loading with explicit provenance and refusing to invent certainty. Every value arrives tagged with the system it came from and the date it was observed, conflicts become exceptions for a data steward rather than being resolved by a rule nobody wrote down, and pre migration values are marked as such so they are never mistaken for a mastered position. Accept that point in time history begins at go live for most attributes. Retrofitting effective dating onto years of overwritten fields is a project people describe as impossible for good reason.

Why do downstream distributions break after launch?

They break because consumers keep their own copies and the copies drift. The pattern is well known and firms still walk into it. Each downstream system needed a slightly different shape, a different arrival time or a subset of fields, so each one took a file, transformed it locally and stored the result. Within a year those stores have diverged and the master is one more source rather than the source.

The second break is timing. A nightly full reload means a downstream system spends part of every day holding yesterday's view, which is fine until a corporate action changes an instrument intraday and trading and risk disagree for six hours. Event driven publication removes that, and it also removes the reload window that quietly constrains everything else.

Vendor licensing is the third break, and it is a commercial one rather than a technical one. Redistribution rights differ by vendor and by data type, and some content cannot legally flow to every internal consumer or to any external party. Model permissions as properties of attributes by source so the platform will not distribute what you are not entitled to distribute, because discovering the limits during a vendor audit is an expensive way to learn them. Then insist on the piece most often skipped: an automated reconciliation proving each consumer's copy still matches the master. Publishing a golden copy nobody verifies is faith rather than architecture.

What happens when overrides and audit trail are not designed properly?

You get a platform full of manual corrections nobody can explain and everybody is afraid to remove. Every mature reference data estate we have worked on had them: an override applied in a hurry three years ago to fix a broken report, still in force, still suppressing the vendor value, with no record of who applied it or why. Removing it risks breaking something. Keeping it means the golden copy is quietly wrong in a way that will surface at the worst moment.

The cause is treating an override as a field edit rather than as a governed object. If overriding is just typing over a value, then it leaves no owner, no reason, no expiry and no review, and the accumulation is inevitable rather than a sign of poor discipline.

Build overrides as first class records with an owner, a reason, an effective period, an expiry and a scheduled review, and report on them continuously so the population stays visible. Alongside that, every mastered value should carry which source produced it and when, because the first question anyone asks about a wrong field is where it came from, and if the answer requires a support ticket to a vendor the design has failed. The same logic applies to point in time reproducibility: store the inputs, the rules version and the result together, so any historic position can be regenerated with its evidence rather than reconstructed from memory.

Should you build custom or configure what you already own?

Do not build if you trade listed instruments in one or two markets from a single vendor feed with a small number of systems. Consume the vendor's data model, nominate one system as the reference and be disciplined about it. A mastering programme at that size is cost with no benefit, and it will occupy the people who currently keep the data clean.

Configuring a commercial platform is genuinely defensible. GoldenSource, NeoXam and S&P Global Markit EDM are capable products, and if you have the budget and the people to run one, buying the engine and configuring it is a legitimate route. Be clear eyed about what you are buying though: the valuable content of any reference data platform is the configuration, meaning which source wins for which attribute for which asset class, how identifiers cross reference in your world, what each consumer needs and what a data quality exception means for your business. Vendors ship a framework. The framework is the cheap part.

Build when two or more hold. You take data from more than two vendors and they disagree in ways that matter. You hold instruments no vendor covers well, such as private placements, internal funds, loans or bespoke derivatives. More than four systems hold their own instrument copy. You cannot answer what an instrument's attributes were on a date last year. Or you have had a reporting rejection, a valuation error or a risk breach traced to reference data in the last twelve months. Our position, stated plainly, is that reference data is infrastructure that outlives several application generations, and owning it outright is a decision firms tend to remain glad about.

How do hidden costs get into the quote?

Five drivers, all knowable before signing.

• Asset class breadth. Over the counter derivatives, structured products, loans and private assets each need their own attribute model, and none of them resembles a listed equity.
• Consumer count. Every downstream projection plus its reconciliation is real work, and reconciliation is the part that gets cut and then has to be added.
• Legal entity hierarchy. Ownership structures with percentages and effective dating are a deeper problem than they look and frequently deserve their own phase.
• Point in time history. Correct to build and it does add cost, and it is the one thing that cannot be retrofitted, so it is worth paying for at the start.
• Vendor permissioning. Modelling redistribution rights by attribute, source and consumer takes longer than teams expect and needs your vendor contracts read by somebody who understands them.

The cost nobody quotes is data stewardship. Survivorship rules, exception queues and override reviews need named people with domain knowledge, not a rota. If that resourcing is not agreed before build, the platform ships and the exception queue grows until somebody starts approving in bulk, which returns you to where you began.

What separates a build that works from one that fails here?

The builds that work define survivorship per attribute, per asset class, with conditions, and they say so in the first design session. Source ranking alone guarantees you are wrong on a large minority of fields, because vendor quality varies by attribute rather than overall: one source is excellent on listed equity terms and weak on fixed income analytics, another has the best issuer hierarchy and lags on new issues, and your own operations team is the only reliable source for private instruments. A developer who proposes a simple vendor hierarchy has not run one of these in production.

The second differentiator is whether distribution is proven rather than assumed. Event based publication, per consumer projections and an automated reconciliation showing each copy still matches the master is the arrangement that keeps the golden copy golden. Without the reconciliation you will not learn that a consumer has drifted until something downstream breaks, and by then the local fix is already embedded.

When choosing a developer, ask how they model identity and reject any answer treating ISIN or CUSIP as a primary key. Ask how a manual override is governed, and listen for owner, reason, expiry and review. Ask how a position from eighteen months ago is reproduced. Ask what happens when two vendors disagree on an attribute for which no rule exists, because raising an exception is the right answer and picking silently is not. Then settle ownership of the code, the survivorship rules and the cloud accounts in writing before kickoff, because the whole argument for building is that you keep the foundation when the applications above it are replaced.