1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
/**
* Stocks table
* The stocks table stores the basic information about each stock.
* This data is scraped periodically from the IBD website.
*/
CREATE TABLE IF NOT EXISTS stocks
(
symbol VARCHAR(16) PRIMARY KEY, -- The stock symbol/ticker
name TEXT NOT NULL, -- The full name of the stock
ibd_url TEXT -- The URL to the IBD page for the stock
);
/**
* Stock Rank table
* The stock rank table stores the rank information for each stock.
* This data is scraped periodically from the IBD website.
*/
CREATE TYPE RANK_TYPE AS ENUM ('ibd50', 'cap20');
CREATE TABLE IF NOT EXISTS stock_rank
(
symbol VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
rank_type RANK_TYPE NOT NULL, -- The type of rank (IBD 50 or CAP 20)
rank SMALLINT, -- The rank of the stock
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- The datetime the rank was scraped/created
);
/**
* Chart Analysis table
* The chart analysis table stores the chart analysis for each stock.
* This data is scraped periodically from the IBD website then analyzed by OpenAI's GPT-4o.
*/
CREATE TYPE CHART_ACTION AS ENUM ('buy', 'sell', 'hold', 'unknown');
CREATE TABLE IF NOT EXISTS chart_analysis
(
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Randomly generated UUID for the chart analysis
raw_analysis TEXT NOT NULL, -- The raw chart analysis text (e.g. "Flat base, 3 weeks tight")
processed BOOLEAN NOT NULL DEFAULT FALSE, -- Whether the chart analysis has been processed by GPT-4o
action CHART_ACTION, -- The action to take based on the chart analysis
price MONEY, -- The price to take the action at
reason TEXT, -- The reason for the action
confidence SMALLINT -- The confidence level of the action (0-100)
);
/**
* Ratings table
* The ratings table stores the ratings for each stock.
* This data is scraped periodically from the IBD website.
*/
CREATE TABLE IF NOT EXISTS ratings
(
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Randomly generated UUID for the rating
symbol VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
composite SMALLINT, -- The composite rating (0-99)
eps SMALLINT, -- The EPS rating (0-99)
rel_str SMALLINT, -- The relative strength rating (0-99)
group_rel_str CHAR(2), -- The group relative strength rating (E-A+) E=0, A+=13
smr CHAR(2), -- The SMR rating (E-A+)
acc_dis CHAR(2), -- The acc/dis rating (E-A+)
chart_analysis UUID REFERENCES chart_analysis (id), -- The ID of the chart analysis for the stock
price MONEY, -- The price of the stock at the time of the rating
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- The datetime the rating was scraped/created
);
CREATE INDEX IF NOT EXISTS ratings_symbol_idx ON ratings (symbol);
/**
* Stock Data table
* The stock data table stores the historical price data for each stock.
* This data should be retrieved from a brokerage API or other data source.
* These should be in the format of minute and daily bars.
*/
CREATE TYPE BAR_INTERVAL AS ENUM ('1m', '1d');
CREATE TABLE IF NOT EXISTS stock_data
(
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Randomly generated UUID for the stock data
symbol VARCHAR(16) NOT NULL REFERENCES stocks (symbol), -- The stock symbol from the stocks table
bar_interval BAR_INTERVAL NOT NULL, -- The interval of the bar data (1m, 1d)
time TIMESTAMP NOT NULL, -- The timestamp of the bar
open MONEY NOT NULL, -- The opening price of the bar
high MONEY NOT NULL, -- The high price of the bar
low MONEY NOT NULL, -- The low price of the bar
close MONEY NOT NULL, -- The closing price of the bar
volume BIGINT NOT NULL -- The volume of the bar
);
|
