553 lines
12 KiB
SQL
553 lines
12 KiB
SQL
CREATE TABLE ZacksRank
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
date DATETIME NOT NULL,
|
|
zacks_rank VARCHAR(25),
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol, date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE InsiderTransaction
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
filing_date DATETIME NOT NULL,
|
|
transaction_date DATETIME NOT NULL,
|
|
insider_name VARCHAR(255),
|
|
ownership_type VARCHAR(55),
|
|
securities VARCHAR(255),
|
|
nature_of_transaction VARCHAR(255),
|
|
number_or_value_acquired_disposed NUMERIC(19,2),
|
|
price NUMERIC(19,2),
|
|
form VARCHAR(55) NOT NULL,
|
|
sec_accession_number VARCHAR(55) NOT NULL,
|
|
form_row_number VARCHAR(55) NOT NULL,
|
|
modified DATETIME,
|
|
PRIMARY KEY (symbol, form, sec_accession_number, form_row_number)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Headlines
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
asof DATE NOT NULL,
|
|
headline VARCHAR(255) NOT NULL,
|
|
source VARCHAR(25) NOT NULL,
|
|
modified DATETIME NOT NULL
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE ETFHoldings
|
|
(
|
|
etf_symbol VARCHAR(15) NOT NULL,
|
|
holding_symbol VARCHAR(15) NOT NULL,
|
|
holding_symbol_sc VARCHAR(15),
|
|
pcnt_of_assets NUMERIC(19,2),
|
|
company VARCHAR(255),
|
|
modified DATETIME
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE DividendHistory
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
dividend_type VARCHAR(25),
|
|
cash_amount NUMERIC(22,2),
|
|
div_ex_date DATETIME NOT NULL,
|
|
declaration_date DATETIME,
|
|
record_date DATETIME,
|
|
payment_date DATETIME,
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol, div_ex_date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE CompanyProfile
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
description VARCHAR(255),
|
|
pricing_source VARCHAR(10),
|
|
can_roll_previous BOOL NOT NULL DEFAULT false,
|
|
freeze_pricing BOOL NOT NULL DEFAULT false,
|
|
PRIMARY KEY (symbol)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE AnalystRatings
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
date DATETIME NOT NULL,
|
|
company VARCHAR(255),
|
|
brokerage_firm VARCHAR(50) NOT NULL,
|
|
type VARCHAR(20),
|
|
ratings_change VARCHAR(50),
|
|
price_target NUMERIC(19,2),
|
|
PRIMARY KEY (symbol, date, brokerage_firm)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE AnalystPriceTarget
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
date DATETIME NOT NULL,
|
|
mean_target NUMERIC(19,2),
|
|
median_target NUMERIC(19,2),
|
|
high_target NUMERIC(19,2),
|
|
low_target NUMERIC(19,2),
|
|
PRIMARY KEY (symbol, date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE YieldCurve
|
|
(
|
|
date DATETIME NOT NULL,
|
|
1Mo NUMERIC(19,2),
|
|
3Mo NUMERIC(19,2),
|
|
6Mo NUMERIC(19,2),
|
|
1Yr NUMERIC(19,2),
|
|
2Yr NUMERIC(19,2),
|
|
3Yr NUMERIC(19,2),
|
|
5Yr NUMERIC(19,2),
|
|
7Yr NUMERIC(19,2),
|
|
10Yr NUMERIC(19,2),
|
|
20Yr NUMERIC(19,2),
|
|
30Yr NUMERIC(19,2),
|
|
modified DATETIME,
|
|
PRIMARY KEY (date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Valuations
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
valuation_date DATETIME NOT NULL,
|
|
company VARCHAR(255) NOT NULL,
|
|
next_earnings_date DATETIME,
|
|
long_term_debt NUMERIC(22,2),
|
|
revenue NUMERIC(22,2),
|
|
debt_load VARCHAR(15),
|
|
roic_avg NUMERIC(22,2),
|
|
roic_slope NUMERIC(22,2),
|
|
roic_dates VARCHAR(50),
|
|
avg_equity_growth NUMERIC(22,2),
|
|
avg_equity_growth_2y NUMERIC(22,2),
|
|
avg_equity_growth_4y NUMERIC(22,2),
|
|
eps_dates VARCHAR(50),
|
|
avg_eps_growth NUMERIC(22,2),
|
|
avg_eps_growth_2y NUMERIC(22,2),
|
|
avg_eps_growth_4y NUMERIC(22,2),
|
|
revenue_dates VARCHAR(50),
|
|
avg_revenue_growth NUMERIC(22,2),
|
|
avg_revenue_growth_qtr NUMERIC(22,2),
|
|
avg_revenue_growth_2y NUMERIC(22,2),
|
|
avg_revenue_growth_4y NUMERIC(22,2),
|
|
avg_freecashflow_growth NUMERIC(22,2),
|
|
avg_operating_cashflow NUMERIC(22,2),
|
|
avg_working_capital NUMERIC(22,2),
|
|
bvps NUMERIC(22,2),
|
|
bvps_dates VARCHAR(50),
|
|
price_to_book NUMERIC(22,2),
|
|
eps NUMERIC(22,2),
|
|
pe NUMERIC(22,2),
|
|
peg NUMERIC(22,2),
|
|
implied_earnings_growth NUMERIC(22,2),
|
|
low_pe NUMERIC(22,2),
|
|
trailing_pe NUMERIC(22,2),
|
|
avg_low_trailing NUMERIC(22,2),
|
|
current_stock_estimate_price NUMERIC(22,2),
|
|
price_estimate_10y NUMERIC(22,2),
|
|
todays_price_for_required_return NUMERIC(22,2),
|
|
mos NUMERIC(22,2),
|
|
mos_80 NUMERIC(22,2),
|
|
intrinsic_value NUMERIC(22,2),
|
|
rgv NUMERIC(22,2),
|
|
latest_price NUMERIC(22,2),
|
|
upside_pcnt NUMERIC(22,2),
|
|
downside_pcnt NUMERIC(22,2),
|
|
mean_target_price NUMERIC(22,2),
|
|
low_target_price NUMERIC(22,2),
|
|
high_target_price NUMERIC(22,2),
|
|
bargain_mos BOOL,
|
|
bargain_mos_80 BOOL,
|
|
modified DATETIME,
|
|
beta90 NUMERIC(19,2),
|
|
beta2Y NUMERIC(19,2),
|
|
market_cap NUMERIC(22,2),
|
|
earnings_yield NUMERIC(23,2),
|
|
ebit NUMERIC(22,2),
|
|
enterprise_value NUMERIC(22,2),
|
|
fundamental_value NUMERIC(22,2),
|
|
net_current_asset_value_per_share NUMERIC(22,2),
|
|
return_on_capital NUMERIC(22,2),
|
|
roic_latest NUMERIC(22,2),
|
|
sector VARCHAR(255),
|
|
industry VARCHAR(255),
|
|
debt_to_equity NUMERIC(22,2),
|
|
sec_13 TINYINT,
|
|
sec_13_filing_date DATETIME,
|
|
tlb_rank_roic NUMERIC(22,2),
|
|
roc_latest NUMERIC(22,2),
|
|
tlb_rank_roc NUMERIC(22,4),
|
|
operating_earnings NUMERIC(22,4),
|
|
am_rank NUMERIC(22,4),
|
|
acquirers_multiple NUMERIC(22,4),
|
|
dividend_yield NUMERIC(22,4),
|
|
shares_outstanding NUMERIC(22,4),
|
|
operating_cashflow NUMERIC(22,4),
|
|
PRIMARY KEY (symbol, valuation_date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Splits
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
effective_date DATETIME NOT NULL,
|
|
ratio VARCHAR(10),
|
|
applied TINYINT,
|
|
applied_least_recent DATETIME,
|
|
applied_most_recent DATETIME,
|
|
modified DATE,
|
|
created DATETIME,
|
|
PRIMARY KEY (symbol, effective_date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE SecurityMaster
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
company VARCHAR(255),
|
|
cik VARCHAR(15),
|
|
sector VARCHAR(255),
|
|
industry VARCHAR(255),
|
|
security_type VARCHAR(15),
|
|
create_date DATE NOT NULL DEFAULT =now()),
|
|
PRIMARY KEY (symbol)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE SECFilings
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
filing_date DATETIME NOT NULL,
|
|
form VARCHAR(25) NOT NULL,
|
|
form_text BLOB NOT NULL,
|
|
description VARCHAR(255),
|
|
sec_filing_url VARCHAR(255) NOT NULL,
|
|
sequence BIGINT NOT NULL,
|
|
sec_accession_number VARCHAR(50) NOT NULL,
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol, filing_date, sequence, sec_accession_number)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Prices
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
date DATE NOT NULL,
|
|
open NUMERIC(19,2),
|
|
high NUMERIC(19,2),
|
|
low NUMERIC(19,2),
|
|
close NUMERIC(19,2),
|
|
volume BIGINT,
|
|
adjclose NUMERIC(19,2),
|
|
source CHAR(1),
|
|
PRIMARY KEY (symbol, date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE PriceIndex
|
|
(
|
|
index_code VARCHAR(50) NOT NULL,
|
|
index_name VARCHAR(50) NOT NULL,
|
|
index_value NUMERIC(19,2),
|
|
as_of DATETIME NOT NULL,
|
|
source VARCHAR(50) NOT NULL,
|
|
modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (index_code, as_of)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Premarket
|
|
(
|
|
market VARCHAR(25) NOT NULL,
|
|
change_value NUMERIC(19,2) NOT NULL,
|
|
change_percent NUMERIC(19,2) NOT NULL,
|
|
timestamp VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (market, timestamp)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Options
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
expiration_date DATE NOT NULL,
|
|
option_type CHAR(1) NOT NULL,
|
|
strike NUMERIC(19,2) NOT NULL,
|
|
last_price NUMERIC(19,2),
|
|
change_price NUMERIC(19,2),
|
|
bid NUMERIC(19,2),
|
|
ask NUMERIC(19,2),
|
|
volume INTEGER,
|
|
open_interest NUMERIC(19,2),
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol, expiration_date, option_type, strike)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE MStarSecurityId
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
security_identifier VARCHAR(25) NOT NULL,
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE MarketHolidays
|
|
(
|
|
Date DATE NOT NULL,
|
|
Description VARCHAR(50),
|
|
PRIMARY KEY (Date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Lexicon
|
|
(
|
|
word VARCHAR(55) NOT NULL,
|
|
sentiment VARCHAR(10) NOT NULL,
|
|
part_of_speech VARCHAR(15),
|
|
PRIMARY KEY (word, sentiment)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Indices
|
|
(
|
|
index_name VARCHAR(55) NOT NULL,
|
|
symbol VARCHAR(15) NOT NULL,
|
|
company VARCHAR(255),
|
|
cik VARCHAR(15),
|
|
sector VARCHAR(255),
|
|
industry VARCHAR(255),
|
|
PRIMARY KEY (index_name, symbol)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE IncomeStatement
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
asof DATETIME NOT NULL,
|
|
total_revenue NUMERIC(19,2),
|
|
cost_of_revenue NUMERIC(19,2),
|
|
income_tax_expense NUMERIC(19,2),
|
|
gross_profit NUMERIC(19,2),
|
|
net_income NUMERIC(19,2),
|
|
sga NUMERIC(19,2),
|
|
net_income_applicable_to_common_shares NUMERIC(19,2),
|
|
ebit NUMERIC(19,2),
|
|
operating_expenses NUMERIC(19,2),
|
|
research_and_development NUMERIC(19,2),
|
|
interest_expense NUMERIC(19,2),
|
|
period INTEGER NOT NULL,
|
|
modified DATETIME,
|
|
PRIMARY KEY (symbol, asof, period)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Historical
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
asof DATETIME NOT NULL,
|
|
type VARCHAR(55) NOT NULL,
|
|
value NUMERIC(19,2),
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol, asof, type)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Fundamentals
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
asof DATE NOT NULL,
|
|
source VARCHAR(15),
|
|
next_earnings_date DATE,
|
|
beta NUMERIC(19,2),
|
|
beta_calc_36 NUMERIC(19,2),
|
|
beta_calc_06 NUMERIC(19,2),
|
|
low52 NUMERIC(19,2),
|
|
high52 NUMERIC(19,2),
|
|
volume BIGINT,
|
|
market_cap NUMERIC(19,2),
|
|
pe NUMERIC(19,2),
|
|
eps NUMERIC(19,2),
|
|
peg NUMERIC(19,2),
|
|
return_on_assets NUMERIC(19,2),
|
|
return_on_equity NUMERIC(19,2),
|
|
total_cash NUMERIC(19,2),
|
|
total_debt NUMERIC(19,2),
|
|
average_volume_3m NUMERIC(19,2),
|
|
average_volume_10d NUMERIC(19,2),
|
|
shares_outstanding NUMERIC(19,2),
|
|
revenue NUMERIC(19,2),
|
|
revenue_per_share NUMERIC(19,2),
|
|
qtrly_revenue_growth NUMERIC(19,2),
|
|
gross_profit NUMERIC(19,2),
|
|
ebitda NUMERIC(19,2),
|
|
ebit NUMERIC(19,2),
|
|
net_income_available_to_common NUMERIC(19,2),
|
|
book_value_per_share NUMERIC(19,2),
|
|
operating_cashflow NUMERIC(19,2),
|
|
leveraged_free_cashflow NUMERIC(19,2),
|
|
trailing_pe NUMERIC(19,2),
|
|
enterprise_value NUMERIC(19,2),
|
|
PRIMARY KEY (symbol, asof)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE EconomicIndicators
|
|
(
|
|
country_code VARCHAR(25) NOT NULL,
|
|
country_name VARCHAR(255),
|
|
indicator_name VARCHAR(128) NOT NULL,
|
|
indicator_code VARCHAR(50),
|
|
indicator_value NUMERIC(19,2),
|
|
year INTEGER NOT NULL,
|
|
source VARCHAR(50) NOT NULL,
|
|
modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (country_code, indicator_name, year)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE EarningsAnnouncements
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
date DATETIME NOT NULL,
|
|
period_ending DATETIME NOT NULL,
|
|
estimate NUMERIC(19,2),
|
|
reported NUMERIC(19,2),
|
|
surprise NUMERIC(19,2),
|
|
surprise_pct NUMERIC(10,2),
|
|
time VARCHAR(25),
|
|
modified DATETIME NOT NULL,
|
|
PRIMARY KEY (symbol, date)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Dividends
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
payment_date DATETIME NOT NULL,
|
|
account VARCHAR(50) NOT NULL,
|
|
amount NUMERIC(16,4) NOT NULL,
|
|
PRIMARY KEY (symbol, payment_date, account)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Dictionary
|
|
(
|
|
word VARCHAR(55) NOT NULL,
|
|
part_of_speech VARCHAR(15) NOT NULL,
|
|
PRIMARY KEY (word, part_of_speech)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE CurrencyConversion
|
|
(
|
|
asof DATETIME NOT NULL,
|
|
source_currency VARCHAR(10) NOT NULL,
|
|
destination_currency VARCHAR(10) NOT NULL,
|
|
destination_currency_name VARCHAR(255),
|
|
units_per_source DOUBLE NOT NULL,
|
|
source_per_unit DOUBLE NOT NULL,
|
|
PRIMARY KEY (asof, source_currency, destination_currency)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE CashflowStatement
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
asof DATETIME NOT NULL,
|
|
depreciation_and_amortization NUMERIC(19,2),
|
|
deferred_income_taxes NUMERIC(19,2),
|
|
stock_based_compensation NUMERIC(19,2),
|
|
accounts_receivable NUMERIC(19,2),
|
|
inventory NUMERIC(19,2),
|
|
accounts_payable NUMERIC(19,2),
|
|
accrued_liabilities NUMERIC(19,2),
|
|
operating_cashflow NUMERIC(19,2),
|
|
free_cashflow NUMERIC(19,2),
|
|
period INTEGER NOT NULL,
|
|
modified DATETIME,
|
|
PRIMARY KEY (symbol, asof, period)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE Cash
|
|
(
|
|
account VARCHAR(25) NOT NULL,
|
|
date DATETIME NOT NULL,
|
|
description VARCHAR(255),
|
|
credit NUMERIC(19,2),
|
|
debit NUMERIC(19,2),
|
|
balance NUMERIC(19,2),
|
|
transaction_id INTEGER NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (account, date, transaction_id)
|
|
)
|
|
;
|
|
|
|
|
|
CREATE TABLE BalanceSheet
|
|
(
|
|
symbol VARCHAR(15) NOT NULL,
|
|
asof DATETIME NOT NULL,
|
|
long_term_debt NUMERIC(19,2),
|
|
other_liabilities NUMERIC(19,2),
|
|
deferred_long_term_liabilities NUMERIC(19,2),
|
|
minority_interest NUMERIC(19,2),
|
|
negative_goodwill NUMERIC(19,2),
|
|
total_stockholder_equity NUMERIC(19,2),
|
|
inventory NUMERIC(19,2),
|
|
property_plant_and_equipment NUMERIC(19,2),
|
|
intangible_assets NUMERIC(19,2),
|
|
accumulated_amortization NUMERIC(19,2),
|
|
goodwill NUMERIC(19,2),
|
|
period BIT NOT NULL,
|
|
total_current_assets NUMERIC(19,2),
|
|
total_assets NUMERIC(19,2),
|
|
total_current_liabilities NUMERIC(19,2),
|
|
total_liabilities NUMERIC(19,2),
|
|
cash_and_cash_equivalents NUMERIC(19,2),
|
|
modified DATETIME,
|
|
PRIMARY KEY (symbol, asof, period)
|
|
)
|
|
;
|