Financial Databases

On This Page	Other Pages
- intro - Security Master - Format of storing prices, rates, amounts - xxx - xxx	- xx

Intro ------------------------------

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:

• Reference database (Security Master).
• Pricing database - may contain "End Of Day" prices, or with more granularity
• Transaction Database (or specialized system to store time series data).
• CRM (Customer relationship Management) database (and may be Account Master database).
• Accounting/reporting database

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

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:

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:

• an integer value (I12 or I20)
• a fraction indicator (gives number of digits to the right of decimal point)

Most databases I worked with used just one field/column.
For example:

• decimal(15,8) - may cause rounding problems
• decimal(25,8)
• decimal(28,12) for everything
• float for everything (may return 0.99999999 instead of 1.00, also may lack precision)

If I were to design a new database today - I would go with decimal(28,12) for everything.