CRSP

From Wiki
Jump to: navigation, search

CRSP and CompuStat (merged) access

Contents

Installation

download/update CRSP Products

Use the xfer (provided by CRSP, java based) tool to download content.

  • interactive
./xfer.sh -e:on  https://crsp.moveitcloud.com
  • or with answer-script xfer.in
./xfer.sh -s:xfer.in https://crsp.moveitcloud.com
# == INI-file ==
# this is ~/cronjob/xfer.in
#
my_CRSP_username
my_CRSP_password
# server-side cd
cd ./CRSP-Compustat_Merged_ANNSUB/
#local cd
lcd ~/CRSP/Product_Downloads/CRSP-Compustat_Merged_ANNSUB
mget *_cadb.zip
 
# server-side cd 
cd ../Stock_1925_ANNSUB/
# local cd
lcd ~/CRSP/Product_Downloads/Stock_1925_ANNSUB/
mget *_cadb.zip
quit
#

transfer *_cadb.zip

#mv ~/cronjob/faz201612_cadb.zip ~/CRSP/Product_Downloads/Stock_1925_ANNSUB/
cd ~/CRSP/Product_Downloads/Stock_1925_ANNSUB/
unzip faz201612_cadb.zip
cd crspdata
# set links accordingly (to fit Nils programm)
ln -s inz201612 inz201412
ln -s maz201612 maz201412
ln -s daz201612 daz201412
# make file accessible
chmod 755 daz201612
chmod 755 inz201612
chmod 755 maz201612
 
#mv ~/cronjob/cmz201607_cadb.zip ~/CRSP/Product_Downloads/CRSP-Compustat_Merged_ANNSUB
cd ~/CRSP/Product_Downloads/CRSP-Compustat_Merged_ANNSUB
unzip cmz201607_cadb.zip
cd crspdata
ln -s cmz201607 cmz201501
chmod 755 cmz201607
# chown ... (maybe)

combined: download/update, transfer, set links & perms

#! /bin/bash
# download DBs, extract, set links and perms
# backup CRSP backup
cp -a ~/CRSP ~/CRSP.`date +"%Y-%m-%d_%H-%M-%S"`.bak
# download
./xfer.sh -s:xfer.in https://crsp.moveitcloud.com
# extract
cd ~/CRSP/Product_Downloads/Stock_1925_ANNSUB/
unzip `ls faz* | sort -r | head -1`
 
cd ~/CRSP/Product_Downloads/CRSP-Compustat_Merged_ANNSUB
unzip `ls cmz* | sort -r | head -1`
 
# set links (to fit hardcoded program), change perms (maybe chown)
cd ~/CRSP/Product_Downloads/Stock_1925_ANNSUB/crspdata
rm inz201412
ln -s `ls -d  inz* | sort -r | head -1` inz201412
rm maz201412
ln -s `ls -d  maz* | sort -r | head -1` maz201412
rm daz201412
ln -s `ls -d  daz* | sort -r | head -1` daz201412
chmod 755 `ls -d  inz* | sort -r | head -1`
chmod 755 `ls -d  maz* | sort -r | head -1`
chmod 755 `ls -d  daz* | sort -r | head -1`
 
cd ~/CRSP/Product_Downloads/CRSP-Compustat_Merged_ANNSUB/crspdata
rm cmz201501
ln -s `ls -d  cmz* | sort -r | head -1` cmz201501
chmod 755 `ls -d  cmz* | sort -r | head -1`

Setup CRSP @Linux

(prerequisite java) install from /Utility_Downloads/CUPL

setupLinux64.bin
-> tools /var/crsp/accbin64
-> environment vars /var/crsp/accbin64/crsp.kshrc

add lines in /etc/bashrc

PATH=/var/crsp/accbin64:$PATH
source /var/crsp/accbin64/crsp.kshrc

make /var/crsp/log writeable for users

chmod 3777 /var/crsp/log

Set Environment

Xfer Data as described above and unzip it.

ll /var/crsp/crspdata/
->
lrwxrwxrwx 1 root root    9 Dec  5 11:07 cmz -> cmz201907
drwxr-xr-x 2 root root 4096 Jul  9 13:27 cmz201807
drwxr-xr-x 2 root root 4096 Dec  5 10:51 cmz201907
lrwxrwxrwx 1 root root    9 Dec  5 11:07 daz -> daz201812
drwxr-xr-x 2 root root 4096 Jan 29  2019 daz201812
lrwxrwxrwx 1 root root    9 Dec  5 11:11 inz -> inz201812
drwxr-xr-x 2 root root  108 Dec  5 11:10 inz201812
lrwxrwxrwx 1 root root   10 Dec  5 11:11 maz -> maz201812/
drwxr-xr-x 2 root root 4096 Dec  5 11:11 maz201812
drwxr-xr-x 2 root root 4096 Dec  5 11:11 saz201812_ascii
drwxr-xr-x 2 root root 4096 Dec  5 11:12 saz201812_sas
lrwxrwxrwx 1 root root   15 Dec  5 11:12 saz_ascii -> saz201812_ascii
lrwxrwxrwx 1 root root   13 Dec  5 11:12 saz_sas -> saz201812_sas


set environment var in /etc/profile

export CRSP_DSTK=/var/crsp/crspdata/daz
export CRSP_MSTK=/var/crsp/crspdata/maz
export CRSP_CCM=/var/crsp/crspdata/cmz
export CRSP_ENV_ROOT=/var/crsp/crspdata/daz
printenv | grep -i CRSP
--> 
   CRSP_ROOT=/var/crsp
   CRSP_LIB=/var/crsp/acclib64/
   CRSP_ENV_ULOG=/var/crsp/log/
   CRSP_SAMPLE=/var/crsp/sample64/
   CRSP_INCLUDE=/var/crsp/include64/
   CRSP_ENV_ELOG=/var/crsp/log/
   PATH=/var/crsp/accbin64/crsp_print:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/var/crsp/accbin64:/home/su/.local/bin:/home/su/bin
   CRSP_WORK=/var/crsp/work/
   CRSP_BIN=/var/crsp/accbin64
   CRSP_MSTK=/var/crsp/crspdata/maz
   CRSP_DSTK=/var/crsp/crspdata/daz
   CRSP_CCM=/var/crsp/crspdata/cmz
   CRSP_LOG=/var/crsp/log

so database_name for CRSP-monthly becomes $CRSP_MSTK,database name when using CRSP-daily becomes $CRSP_DSTK and for CRSP COMPUSTS Merged the database_name is $CRSP_CCM

Usage

Reporting Tools

The reporting tools can extract time-series output, stock event data, and decile-level index data. Data are accessed from the reporting tools through identifier keys. The primary identifier key, and the one the CRSP recommends for the CRSP Stock databases, is PERMNO, CRSP permanent security level identifier. Other identifier keys that may be used include PERMCO, CRSP’s company level identifier, current and historical CUSIPs, Tickers, and SIC codes. INDNO, CRSP’s index identifier, is used to access index data through ts_print and ind_print. [1]

old tools, old shortcuts
old tool description new tool
ts_print To extract time-series data over fixed or relative date ranges ts_print <request file>

crsp_print /d1 $CRSP_CCM /ky permno

stk_print To extract event histories including name and identifier changes, delisting information, and distributions, as well as time series data extraction crsp_print
dstk_print daily stock print crsp_print /d1 $CRSP_DSTK /s1 10
mstk_print monthly stock print crsp_print /d1 $CRSP_MSTK /s1 20
ind_print To extract index and decile-level returns, levels, counts, and weights data our subscription only has the sample index data - best to review the flat files in the directory /crspdata/inz201312
ccm_print Compustat crsp_print /d1 ccm database name
ccm_ref_print /d1 $CRSP_CCM
  • ts_print
ts_print sample1.requestfile

where sample1.requestfile is

# Sample request file for price, volume, total return,
# shares outstanding for a security
# IBM <-> PERMNO 12490
 
ENTITY
LIST|PERMNO 12490|ENTFORMAT 3
END
 
ITEM
ITEMID prc
# ITEMID vol
ITEMID tvol
ITEMID ret
ITEMID shr
END
 
DATE
CALNAME weekly | RANGE 19950101-19950201|CALFORMAT 4 
END
 
OPTIONS
X ITEM,YES|Y DATE,YES|Z ENTITY,YES,1|OUTNAME finsamp.out|REPNAME Sample One
END

and finsamp.out is

Sample One
IBM 
                Prc          Tvol         Ret        Shr   
01/06/1995     75.12500       8474000    0.022109    587172
01/13/1995     76.37500      11890300    0.016639    587172
01/20/1995     75.37500      11202200   -0.013093    587172
01/27/1995     72.50000      16767301   -0.038143    587172
  • crsp_print: to display header information [instead of the commands of the old guide just use crsp_print]

put in 10107, which is the PERMNO for IBM.

crsp_print /d1 /home/su/CRSP/Product_Downloads/CRSP-Compustat_Merged_ANNSUB/crspdata/cmz201407 /ky permno
or
crsp_print /d1 $CRSP_CCM /ky permno

for basic daily stock information

crsp_print /d1 /home/su/CRSP/Product_Downloads/Stock_1925_ANNSUB/crspdata/daz201312  /s1 10
or
crsp_print /d1 $CRSP_DSTK  /s1 10

for monthly stock information

crsp_print /d1 /home/su/CRSP/Product_Downloads/Stock_1925_ANNSUB/crspdata/maz201312/ /s1 20
or
crsp_print /d1 $CRSP_MSTK  /s1 20

Access Syntax Summarized:

CRSP 1925 AND 1962 US STOCK AND STOCK & INDEX DATABASES:

  • ts_print access:
ts_print <request file> 
  • stk_print access:

Full syntax:

crsp_print /d1 database_name /s1 10  –> daily 
crsp_print /d1 database_name /s1 20  –> monthly 

Old CUPL version syntax:

stk_print /d1 database_name     –> daily 
stk_print /d1 database_name /fm –> monthly 
  • ccm_print access:

Full syntax:

crsp_print /d1 ccm_database_name –> full syntax 
ccm_ref_print /d1 $CRSP_CCM      –> full syntax

Old CUPL version syntax:

ccm_print  /d1 ccm_database_name –> full syntax

Search and Inquiry Tools

CRSP provides header files for each CRSPAccess database. These name lists are useful for finding identifiers and name histories of securities when only partial information is known. The identifiers can then be used as input to other CRSP reporting utilities or programs. The files are fixed format text files and be accessed with system utilities or other tools. CRSP provides search utilities for header files.

old tools, old shortcuts
old tool description new tool
dstksearch To search the daily data header files  ?
mstksearch To search the monthly data header files  ?
dindsearch To search the daily index header files  ?
mindsearch To search the monthly index header files  ?
crsp_show_db_info To display parameters associated with a specific database  ?
crsp_set_db_info To change parameters associated with a specific database  ?
  • dstksearch
  • mstksearch

The interactive cli dstksearch and mstksearch are ready, fill in your search request e.g. ibm .

Subsetting Tools

These utilities can be used to create copies of CRSP databases, restricted for example on the basis of exchange and share codes, or a select group of PERMNOs.

old tools, old shortcuts
old tool description new tool
stk_partial Creates a stock database from an existing one or to append

securities from one existing database to anothe

 ?
ind_partial Creates an index database from an existing one or to append indexes

from one existing database to another

 ?
crsp_stk_subset Creates a stock database from an existing one by subsetting data.  ?
crsp_ind_subset Creates an index database from an existing one by subsetting data  ?

Moving Database and Datafile Tools

old tools, old shortcuts
old tool description new tool
rewrite_crspdb Copies a CRSPAccess database to a new directory or converts data from one binary type to another  ?
crsp_stk_scd_load Creates secondary indexes or keys for a database  ?
crsp_stk_headall Creates a header file with user-specified options  ?
crsp_ind_headall Creates a header file for an index database, used primarily for a subset database  ?
crsp_crlf2lf Removes carriage returns  ?
crsp_lf2crlf Adds carriage returns  ?
crsp_cutc Select columns from fixed-width text files can be written to an output file  ?

Tool Chain

available ENVIRONMENT Variables

PATH=/var/crsp/accbin64:$PATH
source /var/crsp/accbin64/crsp.kshrc
 
CRSP_ROOT=/var/crsp
CRSP_LIB=/var/crsp/acclib64/
CRSP_ENV_ULOG=/var/crsp/log/
CRSP_CCM=/var/crsp/2019-07/crspdata/cmz201807
CRSP_SAMPLE=/var/crsp/sample64/
CRSP_INCLUDE=/var/crsp/include64/
CRSP_ENV_ELOG=/var/crsp/log/
PATH=/var/crsp/accbin64:/var/crsp/accbin64/crsp_print:/var/crsp/accbin64/crsp_print:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/var/crsp/accbin64:/home/meyer/.local/bin:/home/meyer/bin
CRSP_ENV_ROOT=/var/crsp/2019-07/crspdata/daz201812
CRSP_WORK=/var/crsp/work/
CRSP_BIN=/var/crsp/accbin64
CRSP_MSTK=/var/crsp/2019-07/crspdata/maz201812
CRSP_DSTK=/var/crsp/2019-07/crspdata/daz201812

available commands

/var/crsp/accbin64

ccm_ref_print  
ccmsearch.sh   
crsp.kshrc  
crsp_print
crsp_setup.csh
crsp_setup.sh
crsp_show_db_info
dindsearch.sh
dstksearch.sh
indsearch.sh
mindsearch.sh
mstksearch.sh
mycrsp.kshrc
stksearch.sh
ts_print



reverse engineering from GUI

ccmsearch
  Apple <enter>
                          COMPUSTAT XpressFeed Records
GVKEY IID PERMNO PERMCO DNUM  CUSIP    SMBL   Company Name                     ANN/QTR range

 1690  01  14593      7 3663  03783310 AAPL   APPLE INC                        19791001-20180629 

So I know "Apple INC" got a GVKEY of 1690, an IID of 01.

Microsoft will have 12141 and 01.

Create a textfile companies.inp with

 1690.01
 12141.01

company audit data for IBM

crsp_print /d1 $CRSP_CCM /ky permno
?
/ua
12490

will retrieve

 PERMNO =  12490, GVKEY = 006066
 
 Company Description
 -------------------
 CIK        EIN        STKO
 0000051143 13-0871985    0 
 
 CONM                                                                            
 INTL BUSINESS MACHINES CORP                                                      
 
 FYRC COSTAT  IPODATE    DLDTE DLRSN    PRIUSA   PRICAN   PRIROW   IDBFLAG FIC
   12 A             0        0          01                         B       USA
 
 LOC INCORP   STATE   
 USA NY       NY      
 
 COUNTY                                                                          
 
 
 CITY                                                                            
 Armonk                                                                           
 
 SIC NAICS  GSECTOR GGROUP GIND   GSUBIND  SPCINDCD SPCSECCD
 7370 519130  45      4510   451020 45102010      190      940
 
 CONML                                                                           
 International Business Machines Corp                                             
 
 WEBURL                                                       PHONE             
 www.ibm.com                                                  914-499-1900      
 
 FAX 
 
 ADD1                                                             
 1 New Orchard Road                                               
 
 ADD2                                                                                                                            
 
 ADD3                                                                                                                            
 
 ADD4                                                             
 
 ADDZIP                  
 10504                   
 
 BUSDESC                                                                         
 International Business Machines Corporation operates as an integrated technology and services company worldwide.
automate for Apple, Microsoft and IBM if you have already retrieved (via ccmsearch) the PERMNOs:

define an input file perms.inp

14593
10107
12490

and in the command line an output file like top_3_tech_companies.out

crsp_print /d1 $CRSP_CCM /ky permno /if perms.inp /of  top_3_tech_companies.out

audits of top 3 companies

crsp_print /d1 $CRSP_CCM /ky permno /ua /if perms.inp /of  top_3_tech_companies_audits.out

Adjust ts_sample2.rqt

...
ITEMID indtret|SUBNO 1000080
...
-> ERROR

1000080 is not a valid index! search for valid index-numbers

mindsearch 
-> 0
 INDNO SETID   Index Name
1000200 420 CRSP NYSE/NYSEMKT/Nasdaq/Arca Value-Weighted Market Index           
1000201 420 CRSP NYSE/NYSEMKT/Nasdaq/Arca Equal-Weighted Market Index           
1000502 420 S&P 500 Composite                                                   
1000503 420 Nasdaq Composite                                                    

replace 1000080 with e.g. 1000502

ITEMID indtret|SUBNO 1000502

programming::add user-formular

session

post - redirect - get paradigm /pattern.

mysqld /mariadb

installation guide

yum install mariadb-server
systemctl start  mariadb
systemctl status mariadb

its status message hints to a log error and that '/usr/bin/mysqladmin' -u root password 'new-password' or '/usr/bin/mysql_secure_installation' should be run:

 ● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2018-02-19 15:04:32 CET; 8s ago
  Process: 71058 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 70978 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 71057 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─71057 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─71220 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error...

Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: To do so, start the server, then issue the following commands:
Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: '/usr/bin/mysqladmin' -u root password 'new-password'
Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: '/usr/bin/mysqladmin' -u root -h crsp-dev.vdc.ac password 'n...ord'
Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: Alternatively you can run:
Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: '/usr/bin/mysql_secure_installation'
Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: which will also give you the option of removing the test
Feb 19 15:04:30 crsp-dev.vdc.ac mariadb-prepare-db-dir[70978]: databases and anonymous user created by default.  This is
Feb 19 15:04:30 crsp-dev.vdc.ac mysqld_safe[71057]: 180219 15:04:30 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Feb 19 15:04:30 crsp-dev.vdc.ac mysqld_safe[71057]: 180219 15:04:30 mysqld_safe Starting mysqld daemon with databases from...mysql
Feb 19 15:04:32 crsp-dev.vdc.ac systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.
mysql_secure_installation
Personal tools
Namespaces

Variants
Actions
Navigation
Tools