chart-simulator/db-queries.rkt at master · evdubs/chart-simulator · GitHub

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
#lang racket/base

(require db
         db/util/datetime
         gregor
         plot
         racket/list
         "cmd-line.rkt"
         "structs.rkt")

(provide get-date-ohlc
         get-date-volume
         get-random-sp-500-symbols
         get-random-symbols-over-price)

(define dbc (postgresql-connect #:user (db-user) #:database (db-name) #:password (db-pass)))

(define (get-date-ohlc ticker-symbol start-date end-date)
  (let ([price-query (query-rows dbc "
select
  date::text,
  open,
  high,
  low,
  close
from
  iex.split_adjusted_chart(
    $1,
    case
      when $2::text::date > (select max(date) from iex.chart) then (select max(date) from iex.chart)
      else $2::text::date
    end,
    $3::text::date,
    false);
"
                                 ticker-symbol
                                 start-date
                                 end-date)])
    (map (λ (row) (dohlc (->posix (iso8601->date (vector-ref row 0)))
                         (vector-ref row 1) (vector-ref row 2) (vector-ref row 3) (vector-ref row 4)))
         price-query)))

(define (get-date-volume ticker-symbol start-date end-date)
  (let ([volume-query (query-rows dbc "
select
  date::text,
  volume
from
  iex.split_adjusted_chart(
    $1,
    case
      when $2::text::date > (select max(date) from iex.chart) then (select max(date) from iex.chart)
      else $2::text::date
    end,
    $3::text::date,
    false);
"
                                  ticker-symbol
                                  start-date
                                  end-date)])
    (map (λ (r) (dv (ivl (- (->posix (iso8601->date (vector-ref r 0))) 43200)
                         (+ (->posix (iso8601->date (vector-ref r 0))) 43200))
                    (ivl 70 (+ (vector-ref r 1) 70))))
         volume-query)))

(define (get-random-sp-500-symbols count)
  (let ([random-symbol-query (query-rows dbc "
select
  c.act_symbol
from
  quandl.wiki_price c
join
  nasdaq.symbol s
on
  c.act_symbol = s.act_symbol and
  c.date = '2000-01-04' and
  s.last_seen = (select max(last_seen) from nasdaq.symbol) and
  s.is_etf = false and
  s.is_test_issue = false and
  s.is_next_shares = false and
  s.security_name !~ 'ETN' and
  s.nasdaq_symbol !~ '[-\\$\\+\\*#!@%\\^=~]' and
  case when s.nasdaq_symbol ~ '[A-Z]{4}[L-Z]'
    then s.security_name !~ '(Note|Preferred|Right|Unit|Warrant)'
    else true
  end
join
  spdr.etf_holding e
on
  c.act_symbol = e.component_symbol and
  e.etf_symbol = 'SPY' and
  e.date = (select max(date) from spdr.etf_holding where etf_symbol = 'SPY')
order by
  random()
limit $1;
"
                                         count)])
    (flatten (map (λ (v) (vector->list v)) random-symbol-query))))

(define (get-random-symbols-over-price price count)
  (let ([q (query-rows dbc "
select
  w.act_symbol
from
  quandl.wiki_price w
join
  (select
    act_symbol,
    mul(new_share_amount) / mul(old_share_amount) as split_ratio
  from
    yahoo.stock_split
  where
    date >= '2000-01-01' and
    new_share_amount != 0 and
    old_share_amount != 0
  group by
    act_symbol
  order by
    act_symbol) s
on
  w.act_symbol = s.act_symbol
where
  w.date = '2000-01-03' and
  w.close / s.split_ratio > $1
order by
  random()
limit $2;
"
                       price
                       count)])
    (flatten (map (λ (v) (vector->list v)) q))))