Data model tables

Bond

BOND

This table stores bond data (e.g., bond type, maturity date, interest rate, etc.). Bond attributes that are common to all types of financial instruments (trading symbol, name, currency, etc.) are stored in the Instrument (INSTRUMENT) table.

Bond History

BOND_HISTORY

This table stores historical data, one record per each trading date. The data includes bonds daily price and yield values (open/close, high/low), trade volume (number of bonds traded), etc.

Bond Quote

BOND_QUOTE

This table stores the real-time (intraday) quote data. Each quote record includes a yield, bid/ask price and size (i.e., a number of bonds offered at a bid/ask price).

Bond Subtype

BOND_SUBTYPE

This table stores a list of definitions that are used to categorize bonds of a particular type. Thus, US Treasury issues can be categorized as Treasury Bonds, Zero-Coupon Bonds, Treasury Notes, etc.

Bond Trade

BOND_TRADE

This table stores real-time (intraday) trade data. Each trade record includes a bonds price and yield and a transactions size (number of bonds traded).

Bond Type

BOND_TYPE

This table stores a list of definitions used to specify a type of a bond (e.g., US Treasury, Municipal, Corporate, etc.)

Capitalization

CAPITALIZATION

This table contains a list of definitions that are used to specify a type of a market capitalization of a financial instruments issuer (e.g., Small-Cap, Medium, Large).

Country

COUNTRY

This table contains a standard list of the world countries (e.g., USA, Japan, France, etc.)

Currency

CURRENCY

This table contains a list of world currencies based on International Organization for Standards (ISO) publication 4217 (e.g., US Dollar, Hong Kong Dollar, etc.)

Dividend Event

DIVIDEND_EVENT

This table stores information on a dividend payment event when a shareholder receives a certain payment for each share of stock in his/her possession. The dividend amount is commonly defined as a certain percentage of a share price but can be also specified as a monetary amount. Monetary or Percentage Indicator (MOP_INDICATOR) column indicates how the dividend amount is defined.

Exchange

EXCHANGE

This table stores a list of exchanges where financial instruments are listed and traded (e.g., New York Stock Exchange, NASDAQ, etc.)

Exchange Traded Fund

EXCH_TRD_FUND

ETF This table stores the Exchange Traded Funds (ETF) data. ETF attributes that are common to all types of financial instruments (trading symbol, name, currency, etc.) are stored in the Instrument (INSTRUMENT) table.

Fund Category

FUND_CATEGORY

This table stores a list of definitions used to characterize an investment style of a mutual fund (e.g., Value, Sector, Growth, etc.).

Fund Family

FUND_FAMILIY

This table stores a list of mutual fund families (e.g., Fidelity, T. Rowe Price, Vanguard, etc.). A fund family is a company offering many mutual funds, for various objectives.

Fund Share

FUND_SHARE

This table stores the data on mutual fund shares of a particular series (class) including a sales load, fee (12-b fee), etc.

Fund attributes that are common to all shares (fund type, family, investment objective type, etc.) are stored in the Mutual Fund (MUTUAL_FUND) table.

Fund Type

FUND_TYPE

This table stores a list of definitions used to characterize a mutual fund based on a type of its financial instruments - stock fund (stocks), bond fund (bonds), hybrid fund (stocks and bonds), etc.

Geographic Group

GEO_GROUP

This table contains a list of definitions used to group financial instruments by a geographical region of their issuers.

Terms commonly used in US are: Domestic (US issuers), International (non-US issuers), Global (can include both domestic and international issuers), Europe (Europe-based issuers), etc.

Index Composition

INDEX_CMPSTN

This association table is used to specify all financial instruments that constitute a market index. Thus, Dow Jones Industrial Average index is based on a stock valuation of the thirty major US corporations that are included in this index.

Index History

INDEX_HISTORY

This table stores the indexes historical data, one record per each trading date. The data includes indexes daily values (open/close, high/low) and trade volume.

Index Intraday

INDEX_INTRADAY

This table stores the indexes real-time (intraday) data that shows its value movements during a trading day. Each data point includes an index value and trade volume.

Instrument

INSTRUMENT

This table stores the financial instruments data that is common to all types of instruments (e.g., trading symbol, name, date of issue, etc.).

Data that is specific to a particular type of instruments (stock, bond, option, mutual fund, etc.) is stored in separate data structures; e.g., stock-specific data is stored in the Stock (STOCK) table.

Instrument Benchmark

INSTR_BENCHMK

This association table specifies a market index that is used as a benchmark for a given financial instrument. More than one benchmark can be used for some instruments.

Instrument Exchange

INSTR_EXCHANGE

This association table is used to specify an exchange where a given financial instrument is listed and traded. Note that some instruments can be listed on more than one exchange.

Instrument Rating

INSTR_RATING

This association table is used to specify rating scores assigned to an issuer of a financial instrument by different rating agencies.

Instrument Type

INSTR_TYPE

This table stores a list of definitions used to specify a type of a financial instrument (e.g., stock, bond, option, mutual fund, ETF, etc.).

Investment Objective Type

INVST_OBJ_TYPE

This table stores a list of definitions used to characterize investment goals of a mutual fund (e.g., Capital Appreciation, Income, Income and Growth, etc.)

Major Industry Classification

MAJOR_IDST_CLS

This table contains a list of definitions used to characterize an industry of a company (e.g., Technology, Energy, Healthcare, etc.). Definitions are based on the Standard Industry Classification (SIC).

Market Index

MARKET_INDEX

This table stores a list of market indexes (e.g., Dow Jones Industrial Average, S&P 500, NASDAQ Composite, etc.) that are used in analysis of market trends, as benchmarks, etc.

Maturity Term Type

MTRTY_TERM_TYPE

This table stores a list of definitions used to specify a type of bond maturity terms (e.g., short-term, intermediate, long-term, etc.).

Mutual Fund

MUTUAL_FUND

This table stores the mutual funds data including a fund type (stocks, bonds, hybrid), fund family (e.g., Fidelity), investment objective (e.g., grows and income), expenses, sale load, etc.

Fund attributes that are common to all types of financial instruments (trading symbol, name, currency, etc.) are stored in the Instrument (INSTRUMENT) table.

Mutual Fund History

MUTL_FUND_HIST

This table stores the historical data for a mutual fund, one record per each trading date. The data includes a trade date and price.

Option History

OPTION_HISTORY

This table stores the options historical data, one record per each trading date. The data includes options daily price (open/close, high/low), trade volume (number of contracts traded), etc.

Option Instrument

OPTION_INSTR

This table stores the options data including an underlying instrument (stock, bond or market index), option type (put or call), strike price, etc.

Option attributes that are common to all types of financial instruments (trading symbol, name, currency, etc.) are stored in the Instrument (INSTRUMENT) table.

Option Quote

OPTION_QUOTE

This table stores the options real-time (intraday) quote data. Each quote record includes a bid/ask price, size (number of contracts offered at a bid/ask price), etc.

Option Trade

OPTION_TRADE

This table stores the options real-time (intraday) trade data. Each trade record includes a trade's price, size (number of contracts traded), etc.

Option Type

OPTION_TYPE

This table stores a list of definitions used to specify a type of an option - put or call.

Payment Frequency Type

PYMT_FRQ_TYPE

This table stores a list of definitions used to specify a frequency of interest payments associated with a bond (annually, semi-annually, quarterly, etc.).

Rating Agency

RATING_AGENCY

This table stores a list of agencies that collect information about the creditworthiness of issuers of financial instruments and assign to them a corresponding rating (credit score). Three major rating agencies are Moody’s Investor Service, Standard & Poors Corporation and Fitch Ratings.

Rating Score

RATING_SCORE

This table stores a list of scores that are assigned by rating agencies to issuers of financial instruments to characterize their creditworthiness. Thus, rating scores assigned by Standard & Poors range from AAA (premium) to D (default).

Secondary Industry Classification

SCND_IDST_CLS

This table stores a list of definitions that are used together with major industry classifications (see Major Industry Classification table) to further categorize an industry of a company. Thus, a company with a major classification Technology can be further categorized as Software, Hardware, etc. Definitions are based on the Standard Industry Classification (SIC).

Share Series

SHARE_SERIES

This table stores a list of definitions used to specify a series (class) of mutual fund shares. Shares series indicates whether they carry commissions (sales load) and when these commissions must be paid. Thus, A shares carry a front-end load that must be paid when shares are bought; B shares carry back-end load that must be paid when shares are sold; C shares have no commissions but carry an ongoing fee (12-b fee) that is paid annually in addition to other fund-related expenses; etc.

Split Event

SPLIT_EVENT

This table stores information on a stock split event when the number of outstanding shares of a company’s stock is increased and the price per share is simultaneously decreased so that proportionate equity of each shareholder remains the same. The split is characterized by a split factor; a factor of 0.5 indicates that the number of shares is increased two times and that the share price is decreased two times.

In a less common reverse split, the number of shares is decreased and the price per share is increased in a similar manner; a split factor of 2 indicates that the number of shares is decreased two times and that the share price is increased two times.

Stock

STOCK

This table stores the data on stocks, e.g., stock type (common stock, preferred stock, etc.), dividend amount, number of shares outstanding, etc.

Stocks attributes that are common to all types of financial instruments (trading symbol, name, currency, etc.) are stored in the Instrument (INSTRUMENT) table.

Stock History

STOCK_HISTORY

This table stores the stocks historical data, one record per each trading date. The data includes stocks daily prices (open/close, high/low) and trade volume (number of shares traded).

Stock Quote

STOCK_QUOTE

This table stores the stocks real-time (intraday) quote data. Each quote record includes a bid/ask price and corresponding size values (i.e., a number of shares offered at bid/ask price).

Stock Subtype

STOCK_SUBTYPE

This table stores a list of definitions that are used to categorize stocks of a particular type. Thus, a preferred stock can be categorized as cumulative, non-cumulative, participating and convertible.

Stock Trade

STOCK_TRADE

This table stores the stocks real-time (intraday) trade data. Each trade record includes a transactions price and size (i.e., a number of shares traded).

Stock Type

STOCK_TYPE

This table stores a list of definitions used to specify a type of a stock, e.g., common stock, preferred stock, etc.