-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathREADME.txt
74 lines (68 loc) · 4.34 KB
/
README.txt
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
# ktrade_garch
create USE_20xx.sql scripts (along with all others) were lost in the Pycharm/Clion catastrophe August-ish 2021.
The procedure grouped EODData by symbol, tradedate?, ordered by tradedate and calculated pctchg for ranks > 1;
This didn't work in some cases where symbols started trading mid-year, e.g.
<code>select symbol, tradedate, close, volume, pct_close from USEQ_HIST where symbol='SD' and tradedate>'2015-01-01' limit 12;
+--------+------------+-------+----------+-----------+
| symbol | tradedate | close | volume | pct_close |
+--------+------------+-------+----------+-----------+
| SD | 2016-01-05 | 0.17 | 9064500 | -5.55556 |
| SD | 2016-01-06 | 0.15 | 16881400 | -11.7647 |
| SD | 2016-10-04 | 19.50 | 61100 | 12900 |
| SD | 2016-10-05 | 21.10 | 1156200 | 8.20513 |
| SD | 2016-10-06 | 21.52 | 574200 | 1.99052 |
| SD | 2016-10-07 | 21.27 | 384200 | -1.16171 |
| SD | 2016-10-10 | 21.57 | 667200 | 1.41043 |
| SD | 2016-10-11 | 21.22 | 264500 | -1.62263 |
| SD | 2016-10-12 | 21.95 | 391700 | 3.44016 |
| SD | 2016-10-13 | 21.85 | 222900 | -0.455583 |
| SD | 2016-10-14 | 22.61 | 440700 | 3.47826 |
| SD | 2016-10-17 | 22.07 | 116900 | -2.38833 |
+--------+------------+-------+----------+-----------+
Actually pctchg's are all wrong on 1st trading day of the year!!!
MariaDB [test]> select * from USE_2020z where symbol='MS' limit 16;
+--------+------------+-------+-------+-------+-------+----------+
| symbol | tradedate | open | high | low | close | volume |
+--------+------------+-------+-------+-------+-------+----------+
| MS | 2020-01-01 | 51.12 | 51.12 | 51.12 | 51.12 | NULL |
| MS | 2020-01-02 | 51.20 | 52.06 | 51.17 | 52.04 | 7440200 |!!!!!!!!!!!!! rnk=1 missing!!!
| MS | 2020-01-03 | 51.22 | 51.45 | 50.83 | 51.20 | 6706700 |
| MS | 2020-01-06 | 50.67 | 51.04 | 50.46 | 51.02 | 7478500 |
| MS | 2020-01-07 | 51.04 | 51.19 | 50.71 | 50.92 | 4571800 |
| MS | 2020-01-08 | 50.96 | 51.79 | 50.90 | 51.57 | 6185200 |
| MS | 2020-01-09 | 52.17 | 52.25 | 51.82 | 52.06 | 7310200 |
| MS | 2020-01-10 | 52.15 | 52.47 | 51.91 | 52.21 | 7690400 |
| MS | 2020-01-13 | 52.21 | 52.78 | 52.07 | 52.78 | 8335300 |
| MS | 2020-01-14 | 53.06 | 53.24 | 52.67 | 52.86 | 9605000 |
| MS | 2020-01-15 | 52.48 | 52.94 | 52.11 | 52.94 | 11342400 |
| MS | 2020-01-16 | 56.05 | 57.36 | 55.65 | 56.44 | 34590600 |
| MS | 2020-01-17 | 56.26 | 57.57 | 56.18 | 57.51 | 15403800 |
| MS | 2020-01-20 | 57.51 | 57.51 | 57.51 | 57.51 | NULL |
| MS | 2020-01-21 | 55.88 | 56.48 | 55.18 | 55.92 | 20833800 |
| MS | 2020-01-22 | 56.18 | 56.51 | 55.85 | 55.86 | 10276800 |
+--------+------------+-------+-------+-------+-------+----------+
16 rows in set (0.223 sec)
MariaDB [test]> select symbol,tradedate,open,high,low,close,volume from USE_2020 where symbol='MS' limit 16;
+--------+------------+-------+-------+-------+-------+----------+
| symbol | tradedate | open | high | low | close | volume |
+--------+------------+-------+-------+-------+-------+----------+
| MS | 2020-01-03 | 51.22 | 51.45 | 50.83 | 51.20 | 6706700 |
| MS | 2020-01-06 | 50.67 | 51.04 | 50.46 | 51.02 | 7478500 |
| MS | 2020-01-07 | 51.04 | 51.19 | 50.71 | 50.92 | 4571800 |
| MS | 2020-01-08 | 50.96 | 51.79 | 50.90 | 51.57 | 6185200 |
| MS | 2020-01-09 | 52.17 | 52.25 | 51.82 | 52.06 | 7310200 |
| MS | 2020-01-10 | 52.15 | 52.47 | 51.91 | 52.21 | 7690400 |
| MS | 2020-01-13 | 52.21 | 52.78 | 52.07 | 52.78 | 8335300 |
| MS | 2020-01-14 | 53.06 | 53.24 | 52.67 | 52.86 | 9605000 |
| MS | 2020-01-15 | 52.48 | 52.94 | 52.11 | 52.94 | 11342400 |
| MS | 2020-01-16 | 56.05 | 57.36 | 55.65 | 56.44 | 34590600 |
| MS | 2020-01-17 | 56.26 | 57.57 | 56.18 | 57.51 | 15403800 |
| MS | 2020-01-21 | 55.88 | 56.48 | 55.18 | 55.92 | 20833800 |
| MS | 2020-01-22 | 56.18 | 56.51 | 55.85 | 55.86 | 10276800 |
| MS | 2020-01-23 | 55.22 | 55.65 | 54.80 | 55.53 | 10064900 |
| MS | 2020-01-24 | 55.53 | 55.61 | 54.10 | 54.55 | 9752100 |
| MS | 2020-01-27 | 53.00 | 53.69 | 52.86 | 53.11 | 10649100 |
+--------+------------+-------+-------+-------+-------+----------+
16 rows in set (0.003 sec)
This fixed in insert_daily.sql. Leave volume=0 rows in table. Queries
should usually return rows with volume>0.