|On This Page||Other Pages|
When going from company to company as a Wall Street consultant, I have noticed the same types of systems and databases used everywhere.
For example, databases:
The databases may be external (provided by some major vendors, like Bloomberg, Reuters, etc.), or internal. If internal, then to maintain it we need feeds of reference data and market data.
Security Master is a database containing reference information about different types of securities. There are millions of securities of different types. As the columns used to describe stocks or bonds or futures or options are different - there will be many tables, for example:
Pricing is not a part of a security master - but often it is included in the same database (for example, End-Of-Day prices).
Example of a structure of the table for Bonds:
|Primary Identifier (e.g. CUSIP)
Asset Sub Type
First Coupon Date
Note: In real life the table will contain much more fields. For example, it will containservice fields, like unique id, dt_created, dt_updated, source, feed, etc. Also, often the same security may have more than one identifier, and one table may contain securities from different countries using different identifiers. So we end up having many different IDs, for example:
CUSIP - Committee on Uniform Security Identification Procedures www.cusip.com - 9-digit code, for US and Canada.
CINS - The CUSIP International Numbering System -
EPIC - UK stock market
ISID - International Securities Identification Directory - cross reference
ISIN - International Security Identification Number - for example " IE0000197834 " - www.anna-nna.com
RIC - Reuters Identification Code
SEDOL - Stock Exchange Daily Official List - for example " 0-019-783 " - the only true unique international ID assigned to all foreign stocks by the International Stock Exchange of London. The code is made up of a 7 digit numeric code. This code is the basis of the ISIN code for UK securities.
Format of storing prices, rates, and amounts
I've seen may ways of storing numeric financial info in databases.
For example, Bloomberg provides values as a combination of 2 numbers:
Most databases I worked with used just one field/column.
If I were to design a new database today - I would go with decimal(28,12) for everything.