Financial Database

Financial Database

The FInancial Database consists of a series of SQL tables that enable paper portfolio trading of S&P 500 companies.

The tables are:

Company - Represents the information regarding the company which issues the security.

Security - the listed security that the company issues

Sector - the GICS sector for which a company is categorized in.

Strategy - Strategies that are created trade securities.

Daily Prices - Holds daily price and volume data of S&P 500 securities.

Transactions - Details of all stock buys and sells from holdings table.

Holdings - Snapshot of holdings for a strategy per day.

Company Table

Field	Data Type	Size	Description
Ticker	VARCHAR	20	Yahoo ticker symbol (primary key)
Comp_Name	VARCHAR	50	Company Name
Comp_Street	VARCHAR	50	Street Address
Comp_City	VARCHAR	50	City
Comp_State	VARCHAR	25	State
Comp_Zip	VARCHAR	20	Zip Code
Comp_Country	VARCHAR	20	Country
Sector	VARCHAR	50	GICS sector for company
Industry	VARCHAR	50	GICS industry for company

Security Table

Field	Data Type	Size	Description
figi	VARCHAR	255	The unique 12-character global identifier. (primary key)
name	VARCHAR	255	Full legal name of the security.
ticker	VARCHAR	20	Local exchange Yahoo ticker symbol
exchCode	VARCHAR	10	ISO Market Identification Code (e.g., XNAS).
securityType	VARCHAR	25	Identifier for the security across multiple exchanges.
marketSector	VARCHAR	50	High-level sector grouping.
shareClassFIGI	VARCHAR	20	Redundant composite identifier for mapping.
securityType2	VARCHAR	20	Specific class of shares (e.g., Class A).
securityDescription	VARCHAR	30	Description

Sector Table

Field	Data Type	Size	Description
sec_name	VARCHAR	50	Gics sector name (primary key)
sec_percentage	DECIMAL	10, 2	current S&P 500 percentage held in sector
sec_desc	VARCHAR	100	Gics sector description

Strategy Table

Field	Data Type	Size	Description
Strategy_name	VARCHAR	50	Unique Name assigned to strategy (primary key)
Strategy_desc	VARCHAR	50	Description of what strategy does
Strategy_formula	VARCHAR	255	Breakdown of formula for this strategy

Daily Prices table

Field	Data Type	Size	Description
ticker	VARCHAR	10	Yahoo ticker (primary key)
date	DATE	-	Date of trading data (primary key)
open	DECIMAL	19, 4	Dollar price at open
high	DECIMAL	19, 4	Highest dollar price for trading day
low	DECIMAL	19, 4	Lowest dollar price for trading day
close	DECIMAL	19, 4	Close of day dollar price
volume	BIGINT	-	Total volume of share traded for day

Transaction Table

Field	Data Type	Size	Description
transaction_id	INT	-	Auto generated number(primary key)
strategy	VARCHAR	50	Strategy name from table
ticker	VARCHAR	10	Yahoo ticker symbol traded
transaction_date	DATE	-	Date of transaction
transaction_type	ENUM	BUY', 'SELL'	Type of transaction (buy or sell)
quantity	DECIMAL	19, 4	Number of shares
price_per_share	DECIMAL	19, 2	Dollar price amount of share transaction

Holdings Table

Field	Data Type	Size	Key
Holding_strategy	VARCHAR	50	Strategy name from table (primary key)
Holdings_date	DATE	-	date of holdings (primary key)
Holdings_ticker	VARCHAR	20	Yahoo ticker (primary key)
purchase_price	DECIMAL	12, 2	Dollar price at time of purchase
holdings_amount	INT	-	Total number of shares held
purchase_date	DATE	-	Date of purchase
purchase_cost	DECIMAL	19, 4	Total dollar cost of purchases