First look here NOTES I want to replicate some of the chart.
I commented out the first failed experiment
Now let’s try with the official channels. Two ways
1.Remote
2.Cloud
Is it possible to connect with
R seems to work
Experiment 2a
It is a SQL code
res <- dbSendQuery(wrds, "select permco,cusip,permno,date,bidlo,askhi
from crsp.dsf
where date between '1960-01-01'
and '1980-01-01'
and askhi > 2500
and bidlo < 2000") #cusip and permno (does not change) identifier, date the day,
data <- dbFetch(res, n=-1)
dbClearResult(res)
data## permco cusip permno date bidlo askhi
## 1 22818 07407710 17953 1960-10-10 48.250 4825
## 2 21446 74271810 18163 1961-05-05 82.250 8275
## 3 20632 28087510 21039 1961-11-08 89.000 9075
## 4 23095 86827310 21709 1960-03-07 1145.000 11700
## 5 23211 92780410 23448 1961-04-13 54.250 5525
## 6 21932 45822P10 25099 1962-01-04 36.625 3825
## 7 21129 55265310 27086 1960-10-04 33.875 3425#CRSP is the “nickname” for the datasets sold by the Center for Research in Security Prices, part of the School of Business at the University of Chicago.
#DSF: daily stock file -->STOCK DATAIT WORKS
Now let’s try to understand. There are:
See here and here for further information
res <- dbSendQuery(wrds, "select distinct table_schema
from information_schema.tables
where table_type ='VIEW'
or table_type ='FOREIGN TABLE'
order by table_schema")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data## table_schema
## 1 ahasamp
## 2 block
## 3 calcbnch
## 4 cboe
## 5 ciq
## 6 comp
## 7 compa
## 8 compb
## 9 compbd
## 10 compg
## 11 compgd
## 12 compm
## 13 compnad
## 14 compseg
## 15 compsegd
## 16 contrib
## 17 crsp
## 18 crspa
## 19 crspq
## 20 dealscan
## 21 djones
## 22 dmef
## 23 doe
## 24 etfgsamp
## 25 execcomp
## 26 ff
## 27 fisd
## 28 frb
## 29 ibes
## 30 information_schema
## 31 iri
## 32 msrb
## 33 optionm
## 34 otc
## 35 pg_catalog
## 36 phlx
## 37 pwt
## 38 risk
## 39 sprat
## 40 tfn
## 41 totalq
## 42 trace
## 43 wrdsappsres <- dbSendQuery(wrds, "select distinct table_name
from information_schema.columns
where table_schema='crsp'
order by table_name")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data## table_name
## 1 acti
## 2 asia
## 3 asib
## 4 asic
## 5 asio
## 6 asix
## 7 bmdebt
## 8 bmheader
## 9 bmpaymts
## 10 bmquotes
## 11 bmyield
## 12 bndprt06
## 13 bndprt12
## 14 bxcalind
## 15 bxdlyind
## 16 bxmthind
## 17 bxquotes
## 18 bxyield
## 19 ccm_lookup
## 20 ccm_qvards
## 21 ccmxpf_linktable
## 22 ccmxpf_lnkhist
## 23 ccmxpf_lnkrng
## 24 ccmxpf_lnkused
## 25 comphead
## 26 comphist
## 27 compmaster
## 28 contact_info
## 29 crsp_cik_map
## 30 crsp_portno_map
## 31 cs20yr
## 32 cs5yr
## 33 cs90d
## 34 cst_hist
## 35 daily_nav
## 36 daily_nav_ret
## 37 daily_returns
## 38 dividends
## 39 dport1
## 40 dport2
## 41 dport3
## 42 dport4
## 43 dport5
## 44 dport6
## 45 dport7
## 46 dport8
## 47 dport9
## 48 dsbc
## 49 dsbo
## 50 dse
## 51 dseall
## 52 dsedelist
## 53 dsedist
## 54 dseexchdates
## 55 dsenames
## 56 dsenasdin
## 57 dseshares
## 58 dsf
## 59 dsfhdr
## 60 dsi
## 61 dsia
## 62 dsib
## 63 dsic
## 64 dsio
## 65 dsir
## 66 dsix
## 67 dsiy
## 68 dsp500
## 69 dsp500list
## 70 dsp500p
## 71 dssc
## 72 dsso
## 73 erdport1
## 74 erdport2
## 75 erdport3
## 76 erdport4
## 77 erdport5
## 78 erdport6
## 79 erdport7
## 80 erdport8
## 81 erdport9
## 82 ermport1
## 83 ermport2
## 84 ermport3
## 85 ermport4
## 86 ermport5
## 87 fbpri
## 88 fbyld
## 89 front_load
## 90 front_load_det
## 91 front_load_grp
## 92 fund_fees
## 93 fund_hdr
## 94 fund_hdr_hist
## 95 fund_names
## 96 fund_style
## 97 fund_summary
## 98 fund_summary2
## 99 fwdask06
## 100 fwdask12
## 101 fwdave06
## 102 fwdave12
## 103 fwdbid06
## 104 fwdbid12
## 105 hldask06
## 106 hldask12
## 107 hldave06
## 108 hldave12
## 109 hldbid06
## 110 hldbid12
## 111 holdings
## 112 mbi
## 113 mbmdat
## 114 mbmhdr
## 115 mbx
## 116 mbxid
## 117 mcti
## 118 mfdbname
## 119 mhista
## 120 mhistn
## 121 mhistq
## 122 monthly_nav
## 123 monthly_returns
## 124 monthly_tna
## 125 monthly_tna_ret_nav
## 126 mport1
## 127 mport2
## 128 mport3
## 129 mport4
## 130 mport5
## 131 mse
## 132 mseall
## 133 msedelist
## 134 msedist
## 135 mseexchdates
## 136 msenames
## 137 msenasdin
## 138 mseshares
## 139 msf
## 140 msfhdr
## 141 msi
## 142 msia
## 143 msib
## 144 msic
## 145 msio
## 146 msir
## 147 msix
## 148 msiy
## 149 msp500
## 150 msp500list
## 151 msp500p
## 152 portnomap
## 153 priask06
## 154 priask12
## 155 priave06
## 156 priave12
## 157 pribid06
## 158 pribid12
## 159 qcti
## 160 qsia
## 161 qsib
## 162 qsic
## 163 qsio
## 164 qsix
## 165 rear_load
## 166 rear_load_det
## 167 rear_load_grp
## 168 rebala
## 169 rebaln
## 170 rebalq
## 171 riskfree
## 172 saz_del
## 173 saz_dind
## 174 saz_dis
## 175 saz_dp_dly
## 176 saz_ds_dly
## 177 saz_hdr
## 178 saz_indhdr
## 179 saz_mdel
## 180 saz_mind
## 181 saz_mth
## 182 saz_nam
## 183 saz_ndi
## 184 saz_shr
## 185 sechead
## 186 sechist
## 187 sfz_dind
## 188 sfz_indhdr
## 189 sfz_mbr
## 190 sfz_mind
## 191 sfz_portd
## 192 sfz_portm
## 193 sfz_rb
## 194 stocknames
## 195 stock_qvards
## 196 tfz_dly
## 197 tfz_dly_cd
## 198 tfz_dly_cpi
## 199 tfz_dly_ft
## 200 tfz_dly_rf2
## 201 tfz_dly_ts2
## 202 tfz_idx
## 203 tfz_iss
## 204 tfz_mast
## 205 tfz_mth
## 206 tfz_mth_bp
## 207 tfz_mth_cd
## 208 tfz_mth_cpi
## 209 tfz_mth_fb
## 210 tfz_mth_ft
## 211 tfz_mth_rf
## 212 tfz_mth_rf2
## 213 tfz_mth_ts
## 214 tfz_mth_ts2
## 215 tfz_pay
## 216 yldask06
## 217 yldask12
## 218 yldave06
## 219 yldave12
## 220 yldbid06
## 221 yldbid12res <- dbSendQuery(wrds, "select column_name
from information_schema.columns
where table_schema='crsp'
and table_name='dsfhdr'
order by column_name")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data## column_name
## 1 begask
## 2 begaskhi
## 3 begbid
## 4 begbidlo
## 5 begdat
## 6 begopr
## 7 begprc
## 8 begret
## 9 begrtx
## 10 begvol
## 11 compno
## 12 cusip
## 13 dlstcd
## 14 endask
## 15 endaskhi
## 16 endbid
## 17 endbidlo
## 18 enddat
## 19 endopr
## 20 endprc
## 21 endret
## 22 endrtx
## 23 endvol
## 24 hcomnam
## 25 hcusip
## 26 hexcd
## 27 hnaics
## 28 hprimexc
## 29 hsecstat
## 30 hshrcd
## 31 hsiccd
## 32 hsicig
## 33 hsicmg
## 34 htick
## 35 htrdstat
## 36 htsymbol
## 37 issuno
## 38 numdel
## 39 numdis
## 40 numnam
## 41 numndi
## 42 numshr
## 43 permco
## 44 permno