Selectorweb.com New York
home > Financial Databases Email

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:

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:

Equities
Equity Options
Depository Receipts
Indices
Index Options
Futures
Future Options
Corporate Bonds
Convertible Bonds
Corporate Actions
Cash Dividends
Splits

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)
Secondary Identifier
Name
Description
Asset Type
Asset Sub Type
Asset Category
Sector
Sub Sector
Issuer
Issue Date
PAR
Maturity Date
First Coupon Date
Coupon
Frequency
Accrual Type

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.
For example:

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