Home

CRSPSift USER GUIDE

1. DATA ITEMS 20 DATA GROUP S 22 PORTFOLIOS 23 OUTPUT OPTIONS 23 CHAPTER 5 INDQUERY INDEX DATA ACCESS 26 INDEX SELECTION 26 HEADER IDENTIFICATION
2. OUTPUT OPTIONS 4 7 CHAPTER 9 MUTQUERY SURVIVOR BIAS FREE MUTUAL FUND D BASE ACCESS 50 TUA ATABA E AGCE RE SECURITY SELECTION 50 E E OOPTE COREE E E TOO IE EREE PE E LIITE TOET O EEEE E SI a bus DATA ITEMS 51 2 PEPEE PEETI E S L OEE E EEEE EE TT S OE TT EEE T E E S OEE eT ET ESSI E P EEEN DATA GROUPS 52 M OE OeX 55 n OUTPUT OPTIONS 53 e OUTPUT FILE SUFFIXES 5 A 4 APTER qe ER E CALENDAR FILE WIZARD 55 REFERENCE DATA 56 EEEE E EE SE C S Z6 F LIES Rm CRSPSIFT OPTIONS ENVIRONMENT SCANNER 58 DIN V RINA Jl NIVAIZ2ILN 1 AA 1 NIN U IN e009900909090900999909999909999909099990909999 9 CRSPSIFT OPTIONS SERVICES 6 ICES resin Or bete vest Hi rr 0 CRSPSIFT OPTIONS QUERY QUEUE aaunasstuawawakaspaskayaatasykaqsuqawasaqa 60 CHAPTER 1 INTRODUCTION CRSPSift CRSP s Securities Information Filtering Tool lets you extract data from your S
3. 3 G TREASURY SELECTION 36 DATA ITEM SELECTION 38 TA GROUPS 39 DA OUTPUT OPTIONS 40 CHAPTER 8 RIQUERY RESEARCH INDEX DATA ACCESS 42 SECURITY SELECTION 42 DATA ITEMS 43
4. Variant Data Item Name Customized 0 Effective Float Factor No io of allocated float shares to riefftso 0 Effective Total Shares Outst No tal shares on trading expressed as a percent rifltFetr Float Factor No SS399V 1 0 X3QNI 94032534 Flags Used to globally turn on off Flags For the related data items O ZU 27 2 e When you highlight an item in the Search Results area its attributes and a short text description appear in the window areas to the rieht rm ps ABOUT FLAGS m Parameters in bold black text are set to default values and may be modified In the Research Index Database edit options are limited to the setting of Flags Time series data items and a few others have flags associated with them Because they are largely unpopulated the flag items are hidden from the tree view display Using the search function will return flag items associated with your search To return a list of all available flag items search on flg Flags can be selected as individual items through using the search screen or can be turned on automatically for items in the query that have flag items associated with them Individual items can have flags turned on by clicking on the Flag field in the properties panel and changing from the No default value Flags can be turned on for all items by using the Global Options panel on the lower left hand corner of the screen and switching from the
5. Options Scan Options Environment Details E v v Scan Folder Hint Paths El Environment Details Environments alendarFile RSP DSTK v v Scan Specific Location e CompustatDB Environment Scanner 1 i scan Subdirectories Default False Services Start Scan View Log J IsSample True IsSample rue MutualFundDB SiftComponents Configur Name TRS200912 ResearchIndexL SiftComponents Configur StockSet Resources Finished scanning Query Queue New Environments Found Load Default Environment Name No FIS200912 CMS201108 SupportedProdu Collection TreasuryDB SiftComponents Configure TRS200912 WorkingDirector 9ieuserprofile9io My Di Usage Options Finished uploading For each environment you wish to load click the checkbox in the Load column to the left of its name Click the Add button to load the selected environments When the progress message below the environments list displays Finished uploading click the Close button to close the Startup Environment Scanner window The CRSPSift Welcome screen appears and you are now ready to begin using CRSPSift query tools S Welcome To CRSPSift SD Connection Settings T Environment Sa FIS200812 CMS200907 TRS200812 t New FiZ200908 CMZ200910 9 Edit Getting Started use Choosing a Query Type gt Time Series Access TsQuery Overview quan StkQuery Overview S tk Stock D
6. dino d characters is the default output width Screen output appears in the output area at the bottom of the RiQuery window JFIREIJERJEIAIHLIBEIEIIHEISERHLIHEIRGE EET EODPTCG SODpMETCADp EODMETZCAD 20120321 204 25000 204 69000 236656713500 00 237166524720 09 20120322 z204 69000 205 49000 2437166524780 00 2380334543850 00 20120323 205 49000 205 48000 238093454380 00 238081867760 00 20120326 205 42000 207 77000 238081867760 00 240735203740 00 20120327 207 77000 207 15000 240735203740 00 240051593160 0 E I Hi i Current History i By default the output of the last query execution is shown in the Current tab The Save Output and Clear Output buttons at the top of the output area allow you to save output to a text file or to clear it away Clicking the History tab shows you the output of every query execution since the query was opened When you switch to the query history view the buttons above the output area change to Save History and Clear History Save History allows you to capture the output of an entire session You can select a new security or different data or options related to the same security Each time you click Execute the new output is saved in the History tab By clicking Save History you can save all the output to one text file OUTPUT TO F
7. Delete Environments Load Sample Environment ADDING A NEW ENVIRONMENT To add a new environment click the Load Environment button Provide the environment with a name and check the Set as Default box if you want this to the the default environment for new queries All Stock Databases and CRSP Compustat Merged Databases for Compustat subscribers are detected by CRSPSift and will appear in the lists below When you select a Stock Database The Select CRSP Compustat table below displays Compustat databases compatible with it if any are installed Select a database from the list if you wish to include CRSP Compustat data in your environment Finally click the Browse button to select a working directory where input and output files for this environment will be directed Click the Save button to save the new environment Only the environments that reflect the newest data cuts will automatically be displayed Additional combinations can be created EDITING AN EXISTING ENVIRONMENT You can also edit an existing environment to change its name and working directory add or remove Compustat data or modify the environment s combination of daily and monthly stock databases To modify an environment select it in the environments list and then click the Edit Environment button The procedure for editing an environment is the same as that for creating a new one Refer to the discussion above for instructions ENVIRONME
8. Search By Data ltem Name Header 2 1 E COMPUSTAT Assets Aggregate Miscell AAM N y Search Criteria Assets Aggregate Miscell AAMO El Data Type Contains Current Assets of Discontin ACDO DataLength 8 Enter Keyword Current Assets of Discontin ACDO_DC DataTy pe f Fixedwidth 14 41 Current Assets Other Total ACO El Settings Current Assets Other Tot ACO_DC Alignment Right Align entAsse DataCodes No Add to Query Sens KeyHeader NONE re p Variant Data Item Name Customized Dehndion El Global Options ID Refer to your Compustat Currency Reported documentation for item DataCodes No definitions FootNotes No DATA ITEM PROPERTIES Further details about an individual item are available on the far right of the screen Data Item Settings Data Item Description El Data Item Puraaret ily returns without dividends pounded from the m inning month in the range SubMo ach period in the time series El Data Type ntains a cumulative return DataLength 11 ince the beginning period DataType f Frxedwidth 11 6 El Settings Alignment Right Align Header Cumaret ItemLag 0 lame After selecting a data item if you want to keep its default settings click the Add button to add that item to the hopper below Otherwise click the value of a setting you wish to change modif
9. ee now to save output to a text file or to clear it away Clicking 4 limits and will truncate the results SAS is CRSP s the History tab shows you the output of every query recommended output format for large query results execution since the query was opened When you switch to the query history view the buttons above VIEWING OUTPUT FILES the output area change to Save History and Clear Miho he Pine p np lo History Save History allows you to capture the output Y Y P P if you select a non screen output format MutQuery of an entire session You can select a new security or will generate one or more output files one for each different data or options related to the same security unique data type layout When the query completes Each time you click Execute the new output is saved in y P execution the hopper displays links to the files that the History tab By clicking Save History you can save AUINDLNN 6 u31dVH SS399V VIVA 03 IWALNIN 3344 SVII UOAIAUNS Sd were generated Each file will have the name specified in the Output File Name field with the addition of an extension identifying the type of output it contains Clear Output Multiple cutput files saved click on the links below to open file C Documents and Settangs Janet My Documents CRSPSift funddatea div xisx file C Documents and Settings Janet My Documents CR
10. A 14481214 MS PI PHRPETITST B dPTPSTT PiTTERLT Sette tt s eee S v X Axis Y Axis Z Axis 0 ERE Z Axis Data Flag 3 w Advanced SS399V 3INIL AUINDSL Preview O tems marked with an asterisk are not yet interactive with the above animation however they do correctly affect the output of your query AJ C TU OUTPUT OPTIONS T 4 m 4 F T 5 C sQuery Can save report output Ina variety of file formats By default sQuery Saves reports as ormatted ext files with no field delimiter which is intended to be human readable AJ Choosing a Field Delimiter 0 If you plan to import query output into another program for further processing select a field delimiter from the Field Delimiter menu A field delimiter is a marker that tells the importing program where to split a data record into its constituent data fields Available options are Pipe Comma and Space You can enter your own preferred field delimiter by selecting Custom and typing a value in the text field next to the menu Supported File Formats In addition to Formatted Text TsQuery can save query output directly to any of the following file formats Excel 97 2003 Workbook xls Excel version 8 NOTE Excel version 8 has a row limit of 65536 total rows If a query generates more than that number of rows
11. E aily Stock amp Indices Other Current Assets Exel ACODO onthly Stock amp Indices Il s ompustat Accumulated Other Compr amp COMINC 3 Description and Links Additional Descriptions 3 Annual Data Annual Period Descript Balance Sheet Annual El Data Type Current Assets Other Sun ACOX DataLength 8 Current Assets Other Tot ACOXAR DataType Fixedwidth 14 41 El Settings Alignment Right Align Current Assets Total Accrued Expenses and Def AEDI Income Statement Ann Cash Flow Annual Pension Annual aldo ono term Assets of Disen AL DO Header ACT Other Annual 5 ltemLag 0 3 Quarterly Data era sino KeyHeader NONE On the Data Items screen clicking on the Keyset box opens a table of all existing keysets for the items with statistics about each The default keyset is highlighted upon opening the box and is typically STD or Standard The Keyset number is provided the number of GVKEYs the number of data points the date range populated by the data and a description of the Keyset Keyset Filter Tag HofGVKEYs data Beg Date End Date Key Set Description 388 10633 19941230 20071231 Null Keyset no variations using secondary keys 19461231 Industrial Format Consolidated Information Standardized Pres 1672 19781229 20081231 Industrial Format Consolidated Information Standardized Sum 697 19821231 20081231
12. MAUI LAND PINEAPPLE CO INC Exchange Codes NYSE 2z4mex 3 NASDAQ Use the search form to locate a particular company in the CRSP database Companies matching your search criteria appear in a table initially sorted by PERMNO If your search yields many matches click the column labels to resort the table in the order most useful to you When you have found the company you want click its row in the table and then click the Select button The PERMNO of that company will appear in the identifier field You may also choose to access all issues in the database or issues you specify in an input file To search all issues click All Issues in the Security Selection area To use an input file first create a plain text file using the Microsoft Windows Notepad accessory for instance with an entry for each issue one per row using the active key of your choice For example to search for Apple and Microsoft by PERMNO using an input file create and save the following two line text file 14593 ROR ROM Next select PERMNO from the Active Key menu GRSPSIFT USER GUIDE PAGE 19 C2 AUINDNLS 7 u31dVH SS399V VIVA 201S Click Input File in the Security Selection area and then click the Browse button to locate the file you created HEADER IDENTIFICATION Click the Header tab if it is not the active tab In this screen select the stock header information that you want in your report and how it sho
13. gt i Tl E rri c c pe Tl c P c e c gt gt gt C2 rri e e When you highlight an item in the Search Results area its attributes and a short text description appear in the window areas to the right Select each item you want in your query and click the Add to Query button to add it to the Query Items area below the Search Results EN Items Item ID Variant Data Item Name actual 1 12b Fee Imo fae actual 12bl Management Fee 12 1 Fee Feria Monthly Net Asset Value Items in the Query Items area appear in the order that they will appear in your query s output To adjust the position of an item select it and click the blue up and down arrows to move it up or down the list The other buttons are z Edit Click this to edit an item s attributes Remove the selected item from your query Remove all items from the Query Items list Click the OK button to confirm the addition of the selected items to your query Click cancel to exit the MutQuery Data Items window without making any changes Import Data Item File Clicking this button allows you to import a list of data items into your query from a text file Export to File Use this option to output the Selected Data Items list to a text file containing each item s mnemonic itemid This text file may then be imported into other queries using the Impo
14. through the Data Items tab or as preselected groups of items of interest Browsing and searching result in data logically categorized data by using the Data Groups items appearing in the Search Results area of the window SELECT EDIT DATA ITEMS Clicking on the Select Edit Data Items tab launches the interface for selecting individual items in MutQuery The MutQuery Data Items window is divided into extus 1251 fmgnt fee Management Fee several areas paid in the most recently ompleted fiscal year as reported in Annual Report Statement SS399V VIVA 03 IWALNIN 3344 SVII UOAIAUNS t of ations Represented in decimal O 97 e 7 C Note m A future version of the Mutual Funds database will allow for context when selecting items Depending on the access key that is selected FUNDNO PORTNO or COMPANY KEY only those categories of data items that are applicable to the key will be available for selection Important to note for this release COMPANY KEY access is limited to the Company Info Category located under Holdings Through PORTNO access is available for Holdings detail data FUNDNO allows acces to all mutual funds items summary data time series and Portno mappings C2 2L J gt Jg rri seg 2 c c rri ag Ti C2 e Jg c eo e c E lt e fi OG
15. 2 MAT files mat Matlab version 7 2 S S 1 1 1 GAS Dace ase en Alternatively you can click the View Output button Stata File dta located in the CRSPSift toolbar to view the output lt Stata File dta l l E files generated by your query in Windows Explorer 9 SPSS File sav I o c File Edit View Favorites Tools Help VIEWING OUTPUT FILES Q Bad 3 Folders S Folder Sync N T 2 Address If you select a non screen output format TrsQuery will e Name lt Size Type Date Modified generate one or more output files one for each unique Bl trsquery2_debt xls 6KB Microsoft Office Excel 97 2003 10 19 2009 3 32 PM gt f d d h h Bd trsquery2_Fh xls 6KB Microsoft Office Excel 97 2003 10 19 2009 3 32 PM x output format produced by the query When the query Bitrsquery2 tmstr xls 6KB Microsoft Office Excel 97 2003 10 19 2009 3 32 PM C 2 vieldm xl 6KB Mi ft Office Excel 97 2003 10 19 2009 3 32 PM e completes execution the output area displays links Musae UPC n m E I o to the output files generated Each file will have the d My Computer name specified in the Output File Name field with the addition of an extension identifying the type of output OUTPUT FILE SUFFIXES C2 to it contains T he following table lists output file extensions for each type of treasury data za Qutput Clear Output o Multiple
16. Price and Returns Price and Shares PORIFOLIOS The Portfolios screen is applicable for Stock and Index Databases only It provides a list of predefined portfolios Choose one or more of them to view the performance of an issue or issues over time using the selected portfolios as benchmarks The data frequency daily or monthly will determine the list of available portfolios Header Data Items Portfolio Data Data Groups Portfolios 2 NYSE Amax Cap Aeeignment 3 NASDAQ Cap Assignment 4 NYSE Cap Assignment 5 Amex Cap Assignment 6 Nn rSE Amex Betas 7 NY SE Amex Standard Deviations 8 NASDAQ Betas 9 NASDAQ Standard Deviations OUTPUT OPTIONS You can save the output of an StkQuery in one of several formats Click the Output tab to show the Output Options panel OUTPUT TO SCREEN The default option is text based output formatted to fit the screen width specified in the Width control 80 characters is the default output width Screen output appears in the output area at the bottom of the StkQuery window II Output PERMNO 12491 Stock Identification PERMNO CUSIP Htick PERMCO COMPNO 12491 02407640 196 60000195 Issuno EX SIC Begdt Enddt DEL 264 3 6711 19780217 20020228 231 Latest Company Name Symbol Naics SH Exl Ex2 Tst Sst AMERICAN BANCORPORATION OH AMBC 522110 11 Q A R Current History Query Complete By default the output of the l
17. TERMSTRUCT FAMA TERM STRUCTURE 2000011 Fama 12 Month T Bills 2 Month 2000012 Fama 12 Month T Bills 3 Month 2000013 Fama 12 Month T Bills 4 Month 2000014 Fama 12 Month T Bills 5 Month Fama 12 Month T Bills 6 Month Fama 12 Month T Bills 7 Month Fama 12 Month T Bills 8 Month Fama 12 Month T Bills 9 Month Fama 6 Month T Bills 2 Month Fama 6 Month T Bills 3 Month 2000025 Fama 6 Month T Bills 4 Month 2000026 Fama 6 Month T Bills 5 Month 2000027 Fama 6 Month T Bills 6 Month INDEX FAMILY BONDMAPORT FAMA BOND PORTFOLIO RETURN 2000028 2000029 2000030 2000031 Fama BondPort Return 6Mo Range lt 6Month Fama BondPort Return 6Mo Range lt 12Month Fama BondPort Return 6Mo Range lt 18Month Fama BondPort Return 6Mo Range lt 24Month 2000032 Fama BondPort Return 6Mo Range lt 30Month 2000033 Fama BondPort Return 6Mo Range lt 36Month 2000044 Fama BondPort Return 12Mo Range 60Month 2000045 2000046 Reserved for future use Duplicate of 2000038 Reserved for future use Duplicate of 2000039 INDEX FAMILY DISCBOND FAMA BLISS DISCOUNT BONDS INDEX FAMILY RATES RATES TREASNOX TERM TYPE DESCRIPTION 2000052 1 Month Certificate of Deposit Rate 2000053 3 Month Certificate of Deposit Rate ENTERING A KEY VALUE Identify the treasury or treasuries of interest There are three ways to specify a key value First if you know the key value you want you can t
18. click on the links below to open file C Documents and Settings Janect My Documentsa CRSPSift ri_ xlsx File C Documents and Settings Janet ey Documents CRSPSLit ri_ch xlax file C Documents and Settings Janec My Documents CRSPSift ri rs xlsx file C Documents and Settings Janet My Documents CRSPSiftiri rz xlsx Clicking one of the output files links opens the File Download dialog box Do you want to open or save this file Cal Mame ri rs xlsx Type Microsoft Office Excel Worksheet 5 52 KB From C Documents and Settings Janet My Documents x v Always ask before opening this type of file While files from the Intemet can be useful some files can potentially harm your computer If you do trust the source do not open or save this file whats the nsk C2 J gt rri ze 05 c rri ze E rri e rri ae C2 rri gt lt gt gt J gt C2 C2 rri e e i FdlsdSd ETT Tj Custom vi y Grdlin y Headin M o TT s iro e quem Click Open to open the output file in the appropriate Workbook Views Show ride Zoom E HET application Excel 2007 in the example shown to the A B c D E F 6 left Ci RIACCUMVOL RINUMTRD 12490 3 30 12 340 PM 3 39 21 PM 208 82 100 328327 3 12490 3 30 12 3 41PM 3 40 58 100 350127 12490 3 30 123 42PM 34 56PM 20896 300 331827 s Alternativel
19. 10107 19900930 19961231 Microsoft Optional Parameters The Report On menu allows you to select either a Calendar Basis or Fiscal Basis for reporting This option applies only to Compustat data items controlling whether to report based on a security s fiscal year or the December calendar year Queries accessing only CRSP data report on the December calendar basis The Display field allows you to select specific dates or a range that falls within a fixed date range that will be displayed in the output Optional Parameters Report On Calender Basis Display LAYOUT On the Layout screen you specify the file type save location and physical layout of a query s output c T gt P Tsau TOTE a Entities Data Items te Layout Direct Edit CO Output Options 19980130 is ee MSE oe FAEERE ilor N F n Field Delimiter No Delimiter V Location and File Name Browse Basic Formatting 2 AERE 34 eRe SS FER H HHS E FE THERE Poree PF FREE RR 484111 fte SH HEHEHE SF EHETE HHHHI HHA FEFEFE EE MS FERETE Report Dimensions MSFT X Item Dat Z Entity w MSF dH FERA
20. CORP 28 625 11 10107 12 14 2000 MICROSOFT CORP 27 78125 CHAPTER 4 STKQUERY STOCK DATA ACCESS StkQuery is used to extract CRSP stock header event and time series data items Designed primarily to access data on an individual security it can also access a list of securities supplied in an input file or all securities in the database Locate the StkQuery toolbar below the main CRSPSift toolbar Database Daily gt DateRange 20090101 To 20090331 Daily Date Range 20090101 To 20090331 In the Database field select which CRSP database your query will use either Daily or Monthly Next specify a date range Enter dates using one of the following formats YYYYMMDD YYYYMM or YYYY The following table provides examples of how to use the various formats RESULT 199612 all data from the beginning of September through December of 1996 1990 empty all data in the year 1990 1994 19940615 all data from the beginning of 1994 until June 15 1994 19961231 empty data only on the date December 31 1996 199609 By default StkQuery returns data from the last three months of the CRSP database being accessed SECURITY SELECTION From the Choose Selection Type drop down menu in the Security Selection area specify the key such as PERMNO or Active Ticker symbol your query will use Security Selection Choose Selection Type Active Ticker Active Ticker 51 Header CUSIP Historical CUSI
21. Data Group Settings Code fd Descriptior Company Filing Header Keve et Pos sp ti Factors Filing Dates FI EM a2 Descriptions p EI Descriptions Company History CCh FILEDATE Murmkerse Mame Company History CST Parent arr Additianal Descr Additional Descriptions Company Industry Pres P ees Additional Company Officer Titles Descriptions 3 Additional Filing Dates Descriptions GIC S History Groups provide a convenient way to select multiple related data items at once For example there are five items related to Filing Dates data These items are found under the Filing Dates sub category of the Additional Descriptions category in the data items selection screen There you can add each of the five items to your query one by one That method is best if you want to modify properties such as keyset or output header of some but not all items Alternatively you could add all five items at once by selecting Filing Dates under the Groups tab This is an efficient way of selecting multiple items if you intend to apply the same properties to all items in the group OUTPUT TO SCREEN The default option is text based formatted to fit the screen width specified in the Width control 80 characters is the default width Screen output appears in the hopper at the bottom of the CCMQuery window Save Output Clear Output massa 001690 Company Security List IID IID_SEQ NUM SCUSIP TIC EXCHG
22. Dataltem Item ID i Data Item Name Header Name Return SubNo 0 cumiret Returns on Income Curnul Cumiret El Data Type Navigate Search _ E Databases Daily Stock amp Indices Identification Name History Prices Returns Returns vs Index Returns vs Portfo Shares Volume Dividends So Iter cumaret Returns Without Dividends Cumaret Assign Exchange NASDAQ Market Tiers Returns Cumulative Cumtret DataLength 4 Returns Ret DataType f Fixedwidth 11 6 E Settings Returns Without Dividends Alignment Right Align _ Returns an Trade onlv Eric Toret 2 2 Pri Header Ret ADOR ItemLag 0 oe Variant Data Item Name Customized Returns on Income Exchanges Capitalization Index Levels Nasdaq Others v NYSE Amex C NASDAQ El Global Options Currency Reported DataCodes No Daily change in the total value f an investment using prices or id ask averages if prices not available Dividends are einvested on the Ex date Tuning FootNotes No Keep none if ever invalid Keep all if ever valid Currency The currency used in the ouput for certain data items THE ENTITY HOPPER SEARCHING AND NAVIGATING DATA ITEMS After setting appropriate options click the Add button Data items are grouped in the Navigate tab according to add this entity to the hopper in the lower area of the to the dat
23. Foothotes FootNotes Used to globally turn anlaff Foot Motes forthe related data items It is important to note that the Global Item Options apply only to Compustat data accessed from the CRSP Compustat Merged Database KEYSETS Keysets are CRSP created groups of Compustat secondary keys that are used to further define Compustat data items CRSP has identified a default keyset for each data item Sift allows you to override the default keysets or select multiple keysets in Sift a gate Search Data ltem term ID Variant Data tem Mame Header ji 1 Other Current Assets Excl ACODO E Stock amp Indices ompustat J Description and Links J Additional Descriptions J Annual Data Annual Period Descript Data Type DataLength DiataType f x Fixedidth 14 41 act 1 Current Assets Total ACT Settings Accrued Expenses and Def amp EDI Alignment Right Align uno Header acr A ltermLag dd to F te Add to Q o Favorites 2 Query i Header NONE 1 Accumulated Other Compr ACOMING Current Assets Other Sun ACOX 4 Current Assets Other Tot ACO Balance Sheet Annual Income Statement Ann Cash Flow Annual Pension Annual Other Annual J Quarterly Data C2 On the Data Items screen clicking on the Keyset box opens a table of all existing keysets for the items with statistics about each The default keyset is highlighted upon opening t
24. Format Output to Soesan Defauk Width E E m fs Pipe 0 Delrreted For impart Excel 97 2003 Workbook xs Excel 2007 Workbook xisx MAT file mat SAS Dataset sas7bdat Stata File dia SPSS File sav Notes about supported Formats Many queries on the mutual funds database result in large files Consider output format when extracting data The screen default is appropriate for individual funds Pipe deliimited ASCII text is suitable for importing into databases or software packages not directly supported by CRSPSift This option generates a single output file C2 2L Jg rri seg 2 c rri ag Ti C2 e Jg c eo e c E lt e fi OG gt ns Tl E rri c c pe Tl c P c e gt gt 2 C2 rri e eo 4224292 Yess 199005 vedi ssid Yo IIIS O no 20 xcel 97 2003 version 8 has a row limit of 65536 total Current History E ER rows Ifa query generate more than that number of T rows the output will be truncated at the row limit 7 By default the output of the last tion i WN E 7 r4 dn lo a Set NUES xcel 2007 will be acceptable for many queries but D The S Z shown in the Current tab The Save Output and Clear E E S E O cr und Output buttons at the top of the output area allow you overaulono i ee
25. If your query will access an individual daily index make sure you have selected Single Series from the INDNO Type menu The Find INDNO window will appear as shown below Find INDNO Select an INDNO for an individual daily index from the list below Indices are displayed by Index Groups Change the Index Group Filter to see other indices e EEE Group Filter CRSP WYSE Value Weighted Market Index Select Cancel From the Available Index Groups menu select the index group that contains the index you want Click the table row containing your target index and then click the Select button That index s INDNO will now appear in the Enter INDNO box If you are using the Daily database and you selected Group of Portfolios from the INDNO Type menu the Find INDNO window will instead look like this an INDND for any daily index group from the list below INDNO Description 1000032 CRSP Amex Market Capitalization Deciles 1000052 CRSP NYSE Amex Market Capitalization Deciles 1000072 CRSP Nasdaq Market Capitalization Deciles 1000082 CRSP NYSE Amex Nasdaq Market Capitalization Deciles 1000112 CRSP NYSE Amex Beta Deciles 1000132 CRSP NYSE Amex Standard Deviation Deciles 1000152 CRSP Nasdaq Beta Deciles 1000172 CRSP Nasdaq Standard Deviation Deciles CRSPSIFT USER GUIDE e PAGE 26 Simply click to choose the index group you want and then click the right arrow to move it
26. Index Selection area and then Index returns used counts and values N J Portfalio Keyset Index counts and values COMO Fe ZU click the Browse button to locate the input file you Numbers for Index rTOUps N created c Ex 1 3 6 m c 00 m HEADER IDENTIFICATION G EJ In the Header tab choose what header information PORTFOLIO GROUP OPTIONS you want at the top of your query report Options include standard index header information header The P information with date ranges in YYYYMMDD format he Portfolio Group Options is for use only with a Stock and Index Database Its tab is enabled only or no header information if you have selected Group of Portfolios from the Header Time Series Header Identification range and rebalancing options for your query Portfolio Group Options WN Options only available when Type is Group of Portfolios INDNO Type menu Here you can select portfolio 0 Index Header Identification Information Header Time Series Index Header Data with Date Ranges in YYYYMMDD Format No Header Data Hebalancing Options Enter the associated portfolios you wish to use with the data Ex 1 3 5 TIME SERIES DATA In the Time Series tab you can select the time series data items that are relevant to your query You can select individual data items choose pre defined groups of items or supply a list of da
27. No default Predefined Data Groups do not support flag items Select each item you want in your query and click the Add to Query button to add it to the Query Items area below the Search Results Query Items TemiD Variant Data Item Name Customized ned 0 ve 0 0 riefFFIE Effective Float Factor riefffit_Flg 0 Effective Float Factor Flag Float Factor Total Shares Outstanding Start of Day Price Price End of Day ritso risodprc _ 00 rieodprc Items in the Query Items area appear in the order that they will appear in your query s output To adjust the position of an item select it and click the blue up and down arrows to move it up or down the list The other buttons are Edit Click this to edit an item s attributes C2 gt gt rri ze c rri ad ze rri e rri Jom 29 C2 lt rri gt lt gt gt J gt C2 C2 rri e e Remove the selected item from your query lt Remove all items from the Query Items list A J Sh Lasd a Click the OK button to confirm the addition of the selected items to your query Click cancel to exit the dino H StkQuery Data Items window without making any changes Import Data Item File Clicking this button allows you to import a list of data items into your query from a te
28. Portfolio File accepts a user created input file of securities in a portfolio Actons Entity Type Selection List All List File Portfolio All Portfolio File LIST Once the Entity Type Selection is made a series of options become available Under List a menu of identifier keys is available from the drop down menu Next identify the issue issues of interest If you know the identifier you may type it in or you may click on Find to do a search LIST Entity List PERMNO Kj Find Use the search form to locate a particular company in the CRSP database Companies matching your search criteria appear in a table initially sorted by PERMNO If your search yields many matches click the column labels to resort the table in the order most useful to you Enter Keyword or Number CRSP Company Search Search By Search Criteria Company Name w Contains Imo 226 Company s matched your search Search Historic Data O Search Current Data PERMNO k Ticker CUSIP 10012 DENSE PACIFIC MICROSYSTEMS INC DPAC Exchange EffectiveRange 24871910 19860122 19870 DENSE PACIFIC MICROSYSTEMS INC 24871910 19870804 19871 DENSE PACIFIC MICROSYSTEMS INC 24871910 19871023 19880 DENSE PACIFIC MICROSYSTEMS INC 24871930 19880713 20010 SUN MICROSYSTEMS INC 86681010 19860304 20070 SUN MI
29. Selection area Available keys are TREASNO default CRSP s Unique US Treasury Number Identifier can be used to access individual issues TREASNOs are 6 digit numbers between 200001 and 206341 CUSIP Treasury CUSIP The Committee on Uniform Security Identification Procedures began assigning CUSIP identification numbers in 1968 Issues that matured prior to 1968 are assigned the value OXX The earliest maturity in the file with a CUSIP is February 15 1969 CRSPID The CRSP Issue Identification Number in the format YYYYMMDD TCCCCE where Maturity Year Type of Issue TYPE CCCC Integer part of Coupon Rate COUPRT 100 E Uniqueness Number UNIQ For example 19850515 504250 identifies a 414 callable bond which matures May 15 1985 TREASNOX CRSP s Unique US Treasury Index Number Identifier Index TREASNOX s are 7 digit numbers between 2000001 and 2000069 The complete list of treasuries indexes is below TREASNOX TERM TYPE DESCRIPTION INDEX FAMILY RISKFREE RISKFREE 2000001 Risk Free Rates 1 Month 2000002 Risk Free Rates 3 Month 2000061 Risk Free 4 Week 2000062 Risk Free 13 Week 2000063 Risk Free 26 Week INDEX FAMILY FIXEDTERM FIXED TERM CRSPSIFT USER GUIDE PAGE 36 C2 D gt Jg rm e e e c ze s gt e c N gt gt gt gt C2 e e TREASNOX TERM TYPE DESCRIPTION INDEX FAMILY
30. UE IE The key type that you select will limit your data item selection to those items Choose Key Type that are relevant to the key PERMNO will prevent access to index data items PERMNO e INDNO will prevent access to security data In cases where a query input file ae includes PERMNOs and INDNOs CRSP introduces the selection option of CRSPKEY which allows the selection of any data in the research database INDNO Next identify the issue or issues of interest If you know an identifier you can enter it directly For example the ticker symbol of General Electric is GE Given that knowledge you can set the key to Ticker and type GE as the identifier CRSPSIFT USER GUIDE PAGE 42 2E I OU rri JA 00 rri Ad AJ rri e rri gt 25 rri gt lt a gt Jm I rri e e If you do not know an identifier click the Find button to open the Company Search window Searches for data in the Research Index Database are limited to PERMNOs and INDNOs for use as primary key selections Search By Search Criteria Enter Keyword or Number company None V eed 27 Companys matched your search criteria B T4 GENERAL PITS CO WARR SRS Use the search form to locate a particular company in the CRSP database Results of your search criteria appear in a table initially sorted by key PERMNO for securities INDNO for indexes If your s
31. When the query completes c G u3ldVH AUINDONI SS399V 1 0 X3QNI execution the output area displays links to the output files generated Each file will have the name specified in the Output File Name field with the addition of an extension identifying the type of output it contains Save Output Clear Output Multiple output files saved click on the links below to open file C Documents and Settings nicole My Documents CRSPSift indquery divl xisx file C Documents and Settings nicole My Documents CRSPSift indquery indhr xlsx Clicking one of the output files links opens the File Download dialog box Do you want to open or save this file e Name indquery diwl xlsx FD En Type Microsoft Office Excel Worksheet 9 27 KB From C Documents and Settings nicole My Documents Open See Always ask before opening this type of file harm your computer IF you do not trust the source do not open ar 9 while files from the Internet can be useful some files can potentially save this file V hat s the risk Click Open to open the output file in the appropriate application Excel 2007 in the example shown below EER indquery dlvl yindno 1 2 2009 2708 00122 812 653076 333 25592 9 16E 09 3 1E6 06 1 5 2009 2707 7124 812 558167 333 259308 2190 9 43E 09 4 1E 06 1 6 2009 2735 65283 820 932007 333 263733 2189 9 42E 09 5 1E 06 1 7 2009 2653 48779 795 870361 333 42816
32. by CRSPSift This option generates a single output file Excel 97 2003 Workbook xls Excel version 8 NOTE Excel version 8 has a row limit of 65536 total rows If a query generates more than that number of rows the output will be truncated at the row limit Excel 2007 Workbook xlsx Excel XML MAT files mat Matlab version 7 SAS Dataset sas8bdat SAS version 7 Stata File dta SPSS File sav VIEWING OUTPUT FILES With the exception of the Pipe Delimited option if you select a non screen output format StkQuery will generate one or more output files one for each unique output of the query When the query completes execution the output area displays links to the output files generated Each file will have the name specified in the Output File Name field with the addition of an extension identifying the type of data it contains Multiple output files saved click on the links below to open PSift output dis xlsx file C CRSPSift output dx xlsx file C CRSPSift output grp xlsx file C CRSPSift output hdr xlsx file C CRSPSift output nam xlsx Clicking one of the output files links opens the File Download dialog box File Download E Download Do you want to open or save this file Mame output_dis xlsx En Type Microsoft Office Excel Worksheet 5 62 KB From CU CRSPSifE 0224 m v Always ask before opening this type of File While files
33. date of October 18 2005 with the Daily calendar selected for output We would like to limit the results of our query to information from the five trading days preceding but not including the event date To accomplish this we first set the date range type to Relative Then in the Relative Event Date Range controls we set the first parameter to five trading days before and the second parameter to one trading day before The query will now return information only from October 13 2005 to October 17 2005 Similarly depending on your needs you could set the range to a window of days either surrounding or after a selected event date Helpful hint When using relative dates include Date caldt as an output item in your query Without caldt all that returns is an item s position relative to the event date with the event date always equal to zero For all queries that do not employ event dates the default Fixed Date Range should be selected in the Date screen Date Range T INN v Fixed Date Range Start Date End Date 2008 12 01 2009 02 28 Optional Optional If a date range is specified in the Entities tab Fixed Date Range acts as an umbrella to the beginning and ending dates selected and must cover all dates requested For example if you used the following list entity input file the Fixed Date Range set in the Date screen would need to span 19751230 19961231 12490 19751230 19861001 IBM
34. end of day values CRSP Capitalizaiton Sector and eventually Value Growth indexes constituents and security data can be accessed individually through a user created input file or for the full universe Locate the RIQuery toolbar below the main CRSPSift toolbar S CRSPSift Enterprise File View Tools Options Help P New Que ME Environment RIZ201112 amp V LJ Execute Cancel AX C Bo View Output t i Start Date Time To End Date Time n 128312031 F 03 40 00 PM 123020311 00 00 PM 2 SORE MReeet EE o li ur _ ee The menu bar for RIQuery differs from other Sift tools in that there is a time selection option to accommodate the intraday data The default date that is set is for a single day for the most current valid trading day of the Research Index Database The default time that is set is for the last 20 minutes of the trading day before the market close based on a 9 30am 4 00pm trading day The time setting is relevant for the security Time Series Trading Detail Information and for the Index Value data For daily data time selection becomes irrelevant Dates can be typed in manually or selected by clicking on the calendar icon to the right of the date fields SECURITY SELECTION RiQuery1 From the Choose Selection Type drop down menu in the Security Selection Output area specify the key such as PERMNO or INDNO for use in your query Rasah baler
35. from the Internet can be useful some files can potentially harm Your computer you do nat trust the source do not open or save this file V hats the risk Click Open to open the output file in the appropriate application Excel 2007 in the example shown below output dis xlsx Data Grae Alternatively you can click the View Output button located in the CRSPSift toolbar to view the output files generated by your query Close View Output S If a query generated only one file the target application will open the file directly as shown above If the query resulted in multiple output files instead of opening the files CRSPSift will open a Windows Explorer window showing the folder where you chose to save output C3 CACRSPSift PRI File Edit View Favorites Tools Help a Back gt E A Search Folders fci Folder Sync Address PE Date Modified Microsoft Office Excel Worksheet 5 15 2009 8 53 AM Microsoft Office Excel Worksheet S 15 2009 8 53 AM Microsoft Office Excel Worksheet 5 15 2009 8 53 AM Microsoft Office Excel Worksheet 5 15 2009 8 53 AM Microsoft Office Excel Worksheet 5 15 2009 8 53 AM Mame Type fx output dis xlex Et output _dx xlsx f output _grp xlsx isi output _hdr xlsx fal output _nam xlsx 5 objects 30 7 KB My Computer al OUTPUT FILE SUFFIXES The following table lists output file suffixes and the typ
36. loaded into another program Delisting Returns Default Missing Outputs the default value 88 0 for missing delisting returns for entities that have delisted during the selected dates You must have a Returns data item selected in the Data Items tab in order to include Delisting Returns in your output Custom Missing Outputs user specified missing delisting return codes You may assign missing values for a range of delisting codes for select beginning and ending exchanges To do this create a text input file containing the following fields in the following order begin delist code end delist code begin exchange code end exchange code alternate delisting return value alternate delisting return without dividends value For example 200 299 L 23 99 50 SDS 500 570 3 3 0 40 0 45 SIN 600 3 3 90 35 Note that in this example the first row would assign a 0 50 value to missing delisting returns for securities with delisting codes 200 299 that initially traded on NYSE and ended up trading on NASDAQ and 0 55 for missing delisting returns without dividends If your query includes a security with a missing delisting return that was not included in your input file the default missing delisting return 55 0 would be used instead DIRECT EDIT To use this feature click the Direct Edit tab Click the Edit button above the text area there and then type or paste your query code overwriting the template que
37. of would cause the character save the report in this file l P string field Company Name to be surrounded by asterisks in output ADVANCED LAYOUT SETTINGS Click the Advanced button to access the following Partial Period Data advanced layout settings Includes partial period data in the output If not selected ts_print will not include the last month of Report Name Calendar Format YYYYMMDD data for a company that stopped trading mid month because only months with end of month data are Row Delimiter Partial Period Data v No Fill Compact data normally included This option applies to monthly Character Delimiter No Fill Delisting Returns The No Fill option enabled by default causes rows Items marked with an asterisk are not yet interactive with the animation however they do correctly affect the output of your query outside an issue s date range or the user s date C2 u3ldVH SS399V 314 3INIL AUINDSL N J specification not to print to the output file No Fill is applicable under the following conditions e Item is chosen for the X axis e Date is the Y axis e Entity is the Z axis Z Axis Data Flag is set to 1 or 3 e Fixed Date Range is selected on the Date tab No Fill does not work with relative dates Compact Compresses output by removing all spaces and trailing decimal zeros in numbers Compact is ideal for producing output to be
38. the output will be truncated at the row limit Excel 2007 Workbook xlsx Excel XML MAT Hiles mat Matlab version 7 SAS Dataset sas7bdat SAS version 7 Stata File dta SPSS File sav BASIC FORMATTING Report Name In the default Formatted Text format X Y and A text description that will appear at the top of the o Z correspond to the Data Item Date and Entity E ii information added to the query The preview pane of report Formatted Text output only the Layout screen shows an animated preview of how Calendar Format your report will appear allowing you to visualize it i without first having to execute the query The preview Cor s eon DE ee is best used to visualize the appearance of Formatted e output when date headers are chosen Options ext txt output The appearance of output in other m lt formats such as Excel or Matlab will vary according l to the target application Previewing saves time FORMAT EXAMPLE ES and enhances the readability of your output If for 1 m s as YYYYMMDD default 20071231 o instance your query uses only a few entities you may m YYMMDD 071231 0 wish to place Entity on the report s X axis If it uses x m l MM DD YY 12 31 0 o many you may prefer to place it along the Y or Z axis moe 104109505 a to limit the output s physical width e DD mmm YYYY 3l Dec 2007 o T 7 o he nature of your query wi
39. to the Selected then click the Select button The index group s Items list INDNO will appear in the Enter INDNO box CE L Header Time Series Portfolio Group Options lt Data for Individual Indices or Decile Groups 4 You may also choose to access all indexes in the Individual Pre Defined Data Item File database or those that you specify in an input file i are Port Index Level w o Divs xh To search all indexes click All Indexes in the Index Port Returns w o Divs PortfolioiKeyset Income Return Index Level for Index Selection area To use an input file create a text file area A Total Index Level w Divs and enter the INDNO of each index your query will Total Retum on index 4 g 1 35 otal Value of Index use one per line For example to specify that your S S E query will access the CRSP NYSE Value Weighted Market Index INDNO 1000000 and the CRSP rri E R P E E F E T E gt lt AMEX Value Weighted Market Index INDNO PRE DEFINED ITEM GROUPS gt 1000020 create and save the following two line text Alternatively you can select pre defined groups of data gt file items gt e END X LOO Header Time Series o 1000020 Data for Individual Indices or Decile Groups N Olndividual Pre Defined Data Item File Index Levels used counts and values Click Input File in the
40. to treasury series The table indicates to which category a group applies m SERIES DAILY TREASURY ITEMS a us MONTHLY TREASURY ITEMS p EM wasi wass RATES Rates Header Rates Timeseries TREASURY DESCRIPTIONS Daily Debt Debt Fixed Term Rates Treasury Index X OUTPUT OPTIONS You can save the output of a TrsQuery in one of several formats Click the Output tab to show the Output Options panel OUTPUT TO SCREEN The default option is text based output formatted to fit the screen width specified in the Width control 80 characters is the default output width Screen output appears in the output area at the bottom of the TrsQuery window Clear Output TREASNO 223950 Yield Monthiy ads edi ee Ne OG 8 eee we By default the output of the last query execution is shown The Save Output and Clear Output buttons at the top of the output area allow you to save output to a text file or to clear it away Clicking the History tab shows you the output of every query execution since the query was opened When you switch to the query history view the buttons above the output area change to Save History and Clear History Save History allows you to capture the output of an entire session You can select a new treasury or different data or options related to the same treasury Each time you click Execute the new output is saved in the histor
41. 2 022 7 7 PORTFOLIO GROUP OPTIONS 27 OUTPUT OPTIONS 28 CHAPTER 6 CCMQUERY CRSP COMPUSTAT MERGED DATABASE ACCESS 30 CHAPTER 7 TRSQUERY TREASURIES DATA ACCESS 36 QUERY DATE RANGE SPECIPICATION
42. 2 2189 9 52E 09 6 1E 06 1 8 2009 2666 40942 799 711914 333 442444 2190 9 23E 09 7 1E 06 1 9 2009 2610 29565 782 871887 333 446747 2187 9 27E 09 8 1E 06 1 12 2009 2541 15332 762 133057 333 44754 2187 9 08E 09 9 1E 06 1 13 2009 2549 723838 764 624695 333 482025 2187 8 84E 09 an am Alternatively you can click the View Output button located in the CRSPSift toolbar to view the output a fa a linnan marn Nr enan annar files generated by your query gt Close View Output Qi nnan manra manr mn If a query generated only one file the target application will open the file directly as shown above If the query resulted in multiple output files however instead of opening the files CRSPSift will open a Windows Explorer window showing the folder where you chose to save output CRSPSift Working File Edit View Back gt Search Folders isi Address ip C CRSPSIFt Working indqueryva dind xls xs indquery S_indhh xls Microsoft Excel Worksheet Favorites Tools Help Microsoft Excel Worksheet ER 3 KB EH 6 KB OUTPUT FILE SUFFIXES The following table lists output file suffixes and the type of data they indicate is contained in a file OUTPUT FILE SUFFIX INDIVIDUAL TIME SERIES DAILY MONTHLY Dind mind PRE DEFINED TIME SERIES DAILY MONTHLY Index Levels used counts and values dlvl mlvl Index returns used counts and values dr
43. 32 Associated Index Returns Datalype s Associated Index Returns Curnindtret Fixedwidth 32 32 R Income Cumul TET cumiret eturns on Income Curnul Curniret Alignment Left Align Item Booo Meriber Portfolio Returns Header C ItemL Add to Favorites ancel Save Changes mg EI Company name effective at the m ceomnarm Company Mame Ye lend of the period reported adjask D Adjusted GLOBAL ITEM OPTIONS When selecting Compustat data items using the new CCM databases several global option settings are available The default currency conversion option is to report data items in the currency in which they are reported By clicking on the drop down menu you may change this option to convert all items to US Dollars El Global Options Currency Reported DataCodes USD Foothlotes Ty Currency The currency used in the ouput for certain data items You can also choose to include footnotes and or data codes for the Compustat items in your query that have them The Footnote and Data Code items can also be extracted automatically for all items that have them by choosing Yes for the settings This can also be done manually by selecting the individual data codes or footnotes for select data items in the Data Item Settings NOTE Global options must be selected before choosing data items in order for them to be applied El Global Options Currency Reported DataCodes Yes
44. 4 20061031 Ei 31278 10 31 2007 10 30 2008 0 000168717 0 0 0023 0 207 0 51 20071031 31278 10 31 2008 10 30 2009 0 00022 0 0 0022 0 195 0 37 20081031 31278 10 31 2009 10 30 2010 0 00026 0 0 0027 0 239 0 41 20091031 Ready average 2241784443 Count 99 Sum 2017605998 CSG Done Alternatively you can click the View Output button located in the CRSPSift toolbar to view the output files generated by your query If a query generated only one file the target application will open the file directly as shown above If the query resulted in multiple output files however instead of opening the files CRSPSift will open a Windows Explorer window showing the folder where you chose to save output Name Size Type Date Modified Bs Funddata_hdr xlsx 7KB Microsoft Office Exc 5 21 2012 1 00 PM xl funddata fload xisx 6KB Microsoft Office Exc 5 21 2012 1 00 PM xl funddata fee xlsx 21 2012 1 00 PM xl Funddata div xlsx Worksheet OUTPUT PILE SUPFIAES The following table lists output file suffixes and the type of data they indicate is contained in a file DATA TYPE SS CHAPTER 10 TOOLS CALENDAR FILE WIZARD When using TsQuery you can define your own set of dates or you can select one of several predefined calendars To create a custom calendar with only those dates of interest to you select Calendar File from the Tools menu CRSPSift Calendar File w s c Output File Cho
45. 960603 lo 45661910 3IMTS INERTIAL MOTORS CORP 19740401 19931231 0 AMERICAN MOTORS CORP 196201 31 19620701 0 AMO AMERICAN MOTORS CORP 19620702 19680101 la a lanua untana nnnm 4660604 ON 40020324 _ Select Use the search form to locate a particular company in the CCM database Companies matching your search criteria appear in a table initially sorted by GVKEY then by IID If your search yields many matches click the column labels to re sort the table in the order most useful to you When you have found the company you want click its row in the table and then click the Select button The GVKEY of that company will appear in the identifier field CRSPSIFT USER GUIDE PAGE 30 c Au300IN29 9 4d31dVH SS399V ISVEVLVG 029437 LVLISNdINO09 dSUS Queries can be run against the full universe by selecting All Issues or by creating a list of keys and selecting Input File To use an input file create a text file with an entry for each company one per row using the active key you selected For example to search for Apple and Microsoft by GVKEY using an input file select GVKEY from the Active Key menu Next create and save the following two line text file 1690 12141 If the IID is not explicitly provided the primary issue default will be applied Click Input File in the Company Selection area and then click the Browse bu
46. CROSYSTEMS INC 86681010 20070827 20071 SUN MICROSYSTEMS INC 86681020 20071112 20091 MICROSOFT CORP 59491810 3 19860313 20091 MICROLOG CORP 59507910 19891020 20010 MICRO MEMBRANES INC MEMB 59486110 3 19860527 19880 10 3 19860527 19880 COMPUTER MICROFILM CORP DEL 20523710 19721213 19791 COMPUTER MICROFILM CORP DEL COME MICROFILM CORP DEL COMI 20523710 19791129 19850 COMPUTER MICROFILM G m 20523810 19850711 19880 25962 COMPUTER MICROGRAPHICS INC MICROGRAPHICS INC CMCR 20524310 20524310 19770110 19790 EIP MICROWAVE INC EIPM 1979092819930 26853010 EIPMICROWAVE INC NEW EIPM 19930210 19930 m Select Cancel 26853020 Exchange Codes 1 NYSE 2 Amex 3 NASDAQ When you have found the company companies you want click its row in the table and then click the Select button To select more than one company hold down the Ctrl key while making selections The PERMNO of that company will appear in the identifier field TsGuery1 X Data Items Direct LIST Entity List PERMNO ne 15069 12490 12079 10107 i CRSPSIFT USER GUIDE PAGE 6 C2 2 J ag e e c rri 25 rri e rri rri e gt gt C2 C2 rri e e DATE OPTIONS There are two options on the Entities screen for restricting query output by date Date
47. Financial Services Format Consolidated Information Standard 80 19981231 20081231 Industrial Format Pro Forma Reporting Standardized Presente 993 19581231 20081231 Industrial Format Pre FASB Reporting Standardized Presenta 20001229 20021231 Industrial Format Consolidated Information Standardized Date 10 19821231 19831230 Industrial Format Pre Divestiture Reporting Standardized Pres 1 19991231 19991231 Industrial Format Pro Forma Reporting Standardized Summar Select Hold Ctrl key to select multiple keysets Selected Keysets 1 Choose different or additional keysets and click the Select button to close the window Keyset Display Compustat data items can be qualified by a set of secondary keys For example the data item SALE has secondary keys for industry format data format population source and consolidation level A different value of company sales can be available for different combinations of these keys such as a combination that represents the originally reported sales or the final Compustat Expanded Keyset restated sales from a later filing Each component of the item s keyset is broken out into CRSP calls these collections of keys and values a keyset its own column in output and assigns a numeric code and mnemonic tag to each unique collection Each of these represents different FYRA SALE output series When multiple keysets are available LEN ae 8 D 200 the u
48. ILE RiQuery1 Input Output Output Options Format SPSS File sav Chutput to Screen Default Pipe Delimited For Import Excel 97 2003 Workbook xds Excel 2007 Workbook xlsx I Jw na SAS Dataset sas bdat Stata File dta SPSS File saw You can also save query output in a number of external file formats When you choose a non screen output format the Output File Name field becomes active In order to successfully execute the query you must select a location to store the output file Click the Browse button to do so Notes about supported Formats Pipe deliimited ASCII text is suitable for importing into databases or software packages not directly supported by CRSPSift This option generates a single output file Excel 97 2003 version 8 has a row limit of 65536 total rows If a query generates more than that number of rows the output will be truncated at the row limit VIEWING OUTPUT FILES With the exception of the Pipe Delimited option if you select a non screen output format RIQuery will generate one or more output files one for each unique data type layout When the query completes execution the hopper displays links to the files that were generated Each file will have the name specified in the Output File Name field with the addition of an extension identifying the type of output it contains Save Output Clear Output Multiple output files saved
49. NDNO Security Header Security Header History Security Daily Items Security Time Series Trade Detail minute by minute Index Constituent Daily Data Items access by PERMNO CHAPTER 9 MUTQUERY SURVIVOR BIAS FREE MUTUAL FUND DATABASE ACCESS MutQuery is used to extract data from the CRSP US Survivor Bias Free Mutual Funds Database Access is designed for Fund data cross referencing securities with funds and for holdings company information A subsequent release of Sift will also allow for getting to fund information through a security Locate the MutQuery toolbar below the main CRSPSift toolbar E cRsPSift Enterprise Cm File View Tools Options Help P New Query G ll Environment MFz201203 gt New Query GF Environment MFZ201203 f Verify Execute 4 Cancel X Close D k Execute a Cancel PX Chae View Date Range 20120101 Te 20120331 Edi Gi Reset The menu bar for MutQuery is like that of most other Sift tools The default date range is set for the most recent three months up through the end date of the database Specify a date range by entering any one of the following formats YYYYMMDD YYYYMM or YYYY The following table provides examples of how to use the various formats RESULT 199609 199612 1990 empty 1994 19940615 19961231 empty SECURITY SELECTION From the Choose Selection Type drop down menu in the Mutual Fund Selection area specify the key such
50. NT DETAILS The Environment Details area displays the properties of the environment currently selected in the New Environments Found list Some of these properties may be changed including an environment s name its default status and the working directory where query output files are saved To make an environment the default for new queries first select its row in the list Next in the Environment Details area click the False value next to the Default option d3ldVH dfil3S ANY NOILVTIVISNI dS542 Environment Details Environment Details CalendarFile CRSP CCM CompustatDB CMs 200910 Default False s5ample False Mame FIzz00908_CMzz2009 StockSet FIz_200908 SupportedProdu Collection Treasury DE WorkingDirector Cz CRSPSIFE Select True to make this environment the default for new queries Likewise for the Name and Working Directory properties Click the value next to the property name and change it to the desired setting Note CRSP provides default environment names that we believe to be intuitive We encourage you to edit names so that they are meaningful to you for your purposes LOADING ENVIRONMENTS Once installed databases have been scanned and combined into environments and each environment s properties are set appropriately you can select which environments you wish to load into CRSPSift CRSPSift Options SIFTPRO_3_1 My Workspace Environment Scann
51. P PERMCO All Issues Next identify the issue or issues of interest If you know an identifier you can enter it directly For example the ticker symbol of Apple Computer is AAPL Given that knowledge you can set the key to Active Ticker and type AAPL as the identifier If you do not know an identifier click the Find button to open the Company Search window Search Lritera Enter Keyword or Number Contains ae Search Hetoic Data C Search Curent Data CRSP Company Search SearchBy CompanyName gt Name Contains 32 Companys matched your search crberia PERMNO CompanyName Ticker CUSIP 14593 APPLECOMPUTERINC APL 3 19801212 20070110 14593 APPLE INC 03783310 20020111 20050331 14806 APPLEBAUMS FOOD METS INC 03789710 19721214 17178 HAWAIIAN PINEAPPLE LTD 19281130 19321230 27713 APFLERA CORP 20001130 20080630 37640 THORN APPLE VALLEY INC 1364101119841011 37640 THORN APPLE VALLEY INC 19841012 19990223 19670705 19680101 19680102 19700515 19721214 19960403 13660410 19380430 Effector ange 3 3 03802010 1 8518410 3 8518410 3 44679 APPLE SUPERMARKETS INC 2 2 3 D 2 0898710 57734510 57734510 57734510 57734510 i 44579 BIG APPLE SUPERMARKETS INC 51925 MAUI LAND PINEAPPLE INC 51925 MAUI LAND PINEAPPLE INC 51925 MAUI LAND amp PINEAPPLE CO INC 51925
52. Range and Event Date The Date Options allow you to target date ranges or single dates specific to an entity Date Options selected on the Entities screen are further tied to the Date Range option on the Date tab If Date Range is selected on the Entites tab a Fixed Date Range must be specified on the Date tab Likewise an Event Date must be paired with a Relative Date Range on the Date tab Date Range Date Options Date Range ivl Beginning Date YY YY MM DD Optional End Date YYYY MM DD If given a Date Range must include both beginning and ending dates Dates may be in YYYYMMDD YYYYMM or YYYY format For formats that do not specify months or days the beginning date in the range will start with the first period within the specified range The ending date will be the last period in the range For example the output of a query with Entity Date Range 200605 200703 would include daily data from May 1 2006 through March 31 2007 or monthly data from May 31 2006 through March 31 2007 The valid query output is the union of the security s trading history the Date Range specified on the Entities tab and the Fixed Date Range given on the Date tab The Entity Date Range must fall within the Fixed Date Range Note that the Entity Date Range must also exceed the frequency duration of the calendar selected for your output For example if your calendar is set to report annually the Date Range must span more tha
53. Results E Data Item Ei Databases Item ID Variant Data Item Name Header n inally you may choose to access only certain E Treasury Te CRPI 0 le T wam amar m m treasuries specified in an input file To use an input RT S a Wee n xed Term iwhy 0 Reason For End of Data Why 2 file first create a plain text file using the Microsoft E moy Tey oleae at r Em CN tnippy Number of Interest Payments Per NIP Windows Notepad accessory for instance with an Fixed Term tyalFc 0 TR FPmtval rm T tfcpdt 0 First Coupon Payment Date FPmtDte o entry for each treasury one per row using the key type Ii 4 e F Query Items of your choice For example to access four particular Eo ae eee pene IB gt e e rm e e 200007 200014 O J 200158 27 ES e 200161 The TrsQuery Data Items window is divided into T E several areas Items are divided into logical groups in C S MP 00 ave the file in your data environment s working the Navigate Search area A directory Next Click the Input File option in the G T l Navigate reasury Selection area and then click the Browse S a does button to locate the input file you created The Browse E Treasury Items Master Items a window opens automatically to the working directory rump P ts Now select the preferre
54. SPSift funddata fee xisx file C Documents and Settings Janet My Documents CRSPSift funddata fload xlsx j file C Docunents and Settings Janet My Documents CRSPSift funddata hdr xlsx Clicking one of the output files links opens the File Download dialog box File Download Do you want to open or save this file Name funddaka fee xlsx Eaj Type Microsoft Office Excel Worksheet 5 63 KB From C Documents and Settings JanetiMy Documents pen Save v Always ack before opening this type of file While files from the Intemet can be useful some files can potentialy ham your computer IF vou do not trust the source do rot open or save this fle whats the risk Click Open to open the output file in the appropriate application Excel 2007 in the example shown below Clipboa 5 Font fs Alignment G Number 7 Styles Cells Editing hh TT Al o fe KYCRSP FUNDNO a Gi funddata fee xlsx WP HIE a eed Eo engi eect esi Ec ec Eu KYCRSP FUNDNO FFEBEGDT FFEENDDT ACTUAL 12B1 MAX 12B1 FEXP RATIO FMGMT FEE FTURN RATIO FFISCAL YEAREND Be 31278 10 31 2002 10 30 2003 0 000100589 0 0 0035 0 342 0 41 20021031 3 31278 10 31 2003 10 30 2004 0 000102593 0 0 0032 0 307 0 29 20031031 4 31278 10 31 2004 10 30 2005 0 00012683 0 0 0026 0 246 0 22 20041031 s 31278 10 31 2005 10 30 2006 0 000146495 0 0 0022 0 199 0 28 20051031 6 31278 10 31 2006 10 30 2007 0 000188068 0 0 0023 0 218 0 3
55. TPCI SSECSTAT DLRSNI DLDTEI 01 1 037833100 AAPL 14 0 2 0 IID EXCNIRY ISIN SEDOL EPF SBEGDT SENDDT 01 USA US0378331005 2046251 0 0 IID DSCI 01 COM NEV Current History l X l D H History By default the output of the last query execution is shown in the Current tab The Save Output and Clear Output buttons at the top of the output area allow you to save output to a text file or to clear it away Clicking the History tab shows you the output of every query execution since the session was opened When you switch to the query history view the buttons above the output area change to Save History and Clear History Save History allows you to capture the output of an entire session You can select a different key GVKEY or PERMNO for example or different data or options related to the same key Each time you click Execute the new output is saved in the history Then by clicking Save History you can save all the output to one text file C2 2 Jg rri ad SS399V 3SVaviva GIDYIW 4 09 4 849 AUINDINII OUTPUT TO FILE You can also save data in a number of external file formats When you choose a non screen format the Output File Name field becomes active and is required Click the Browse button to select a location to save the file Output Optons Format Width Output to Screen Default Output to Screen Default Pipe Delimited For Import Ex
56. a Audit Data C2 2 Jg rri ad SS399V 3SVaviva 39338 4 09 4 549 AUINDINII 1111 AJAST lalsdsdJ rm DATA CATEGORY DATA GROUP NAME OUTPUT FILE SUFFIX DN Segment Source Security Dividend FN Security Split Events CHAPTER 7 TRSQUERY TREASURIES DATA ACCESS CRSPSift s TrsQuery tool is used to access CRSP Daily and Monthly US Treasuries Data Individual treasury issues and a variety of treasury series are available QUERY DATE RANGE SPECIFICATION Locate the TrsQuery toolbar below the main CRSPSift toolbar Date Range 20090501 To 20090731 Direct Edit i Reset B Range 20090501 To 20090731 Direct Edit Specify a date range Enter dates using one of the following formats YYYYMMDD YYYYMM or YYYY The following table provides examples of how to use the various formats RESULT 199609 199612 all data from the beginning of September through December of 1996 1990 empty all data in the year 1990 1994 19940615 all data from the beginning of 1994 until June 15 1994 19961231 empty data only on the date December 31 1996 By default TrsQuery returns data from the last three months of the CRSP database included in your query s Environment For more information on Environments see Chapter 8 Environments TREASURY SELECTION SELECTING A KEY TYPE Select what type of key your query will use from the Choose Key Type drop down menu in the Treasury
57. abase to which they belong Daily Stock screen Monthly Stock Compustat available to CCM Database subscribers and Favorites Click the plus are nom S we Ea sign next to a database to open a categorized list of m data items When you select a category a list of the data items from that category populates the Search Portfolio File Input File Path C Documents and Settings nicole My Documents CRSPSiftis Results area in the central area of the screen C2 u3ldVH SS399V 3143 31111 AUINDSL N J In addition to navigating items by category you can search the available data items To find items this way click the Search tab Entities Data Items Date Navigate Search Search By COMPUSTAT Search Criteria Contains w Enter Keyword assets Search Search is limited to the database selected in the Search By menu Additional criteria can be supplied as well such as whether results must be an exact match for the search term begin or end with the term or simply contain the term Both mnemonic codes and full item names are searched and returned for each item Click the column names Item ID Variant Data Item Name or Header to change the order in which items are sorted P TsQuey cs 25555 Date Layout DirectEdit Notes P i E Data Item
58. ange Type Share Type Size Band Label Trading Status DOCKING SUB WINDOWS Sub windows in CRSPSift such as the Batch Queries window and the new Stock and CCM References can be docked within the main application window To dock a window click and drag it by its title bar For instance the Stock Reference title bar As you drag the window the following five icons Stock Reference Stock Reference appear The triangle on each outer icon indicates where in the CRSPSift window the window will be docked with respect to the query area left right above or below Drag the window s title bar over the desired icon to dock it in the desired location Dragging a window over the central icon will cause the window to appear as a tab in the queries area CHAPTER 11 CRSPSIFT OPTIONS CRSPSIFT OPTIONS ENVIRONMENT SCANNER Use the Environment Scanner tool search your computer for CRSP databases and automatically combine them into valid environments FOLDER HINT PATHS By default the Environment Scanner looks for databases in a set of pre defined locations called Folder Hint Paths The Folder Hint Paths provided with CRSPSift are C crspdata the default install location for CRSP databases and C N crspdata Sample 3 21 the location of the sample datasets provided with the CRSPSift software Each time CRSPSift launches the Folder Hint Paths are search for newly installed databases When new databases are
59. as CRSP FUNDNO or CRSP COMPANY KEY for use in your zd m query In the next version of Sift the key type that you select will limit your data item Choose Key Type CRSP FUNDNO E selection to those items that are relevant to the key CRSP FUNDNO CUSIP Next identify the issue or issues of interest If you know an identifier you can enter it directly If you do not know an identifier click the Find button to open the Search window Searches for data in the Mutual Funds Database are limited to CRSP FUNDNO CRSP PORTNO and CRSP COMPANY KEY for use as primary key selections Search Criteria Search By Enter Keyword or Nimber FUND NAME w Baad Search J 4232 Fred s matches your search criteri s Search Historic Data C Search Current Data Use the search form to locate a particular fund portfolio or company in the database Results of your search criteria appear in a table initially sorted by key FUNDNO for funds and CRSP FUND CUSIP FUND NAME PORTNO TICKER zl 2 EE LILLIA 6 Aisne Trasegi Malone Fund Alance Strategic Balanced Fund A E a PS portfolios and CRSP COMPANY KEY for securities If your oo iE search yields many matches click the column labels to resort the T Peer E E table in the order most useful to you When you have found the d ee entity that you want click its row in the table and then click the 000010 Alksnce Pot S
60. ast query execution is shown in the Current tab The Save Output and Clear Output buttons at the top of the output area allow you to save output to a text file or to clear it away Clicking the History tab shows you the output of every query execution since the StkQuery session was launched When you switch to the query history view the buttons above the output area change to Save History and Clear History Save History allows you to capture the output of an entire session You can select a new security or different data or options related to the same security Each time you click Execute the new output is saved in the History tab By clicking Save History you can save all the output to one text file OUTPUT TO FILE You can also save query output in a number of external file formats When you choose a non screen output format the Output File Name field becomes active In order to successfully execute the query you must select a location to store the output file Click the Browse button to do so Output Options Format Excel 2007 Workbook xlsx v 2007 Workbook xlsx Output File Name CACRSPSifouputxix PS ifficutput xlsx C2 d3ldVH 9 VIVA 0015 AUINDNLS Supported File Formats Pipe Delimited For Import pipe deliimited ASCII text suitable for importing into databases or software packages not directly supported
61. ata Access CCMQuery Overview quu IndQuery Overview CCM CRSPICOMPUSTAT Access TrsQuery Overview Data Environments Ind Indices Data Access Reference Data Items Output File Options Trs Treasuries Data Access Batching Queries Calendar File Wizard Query Name uc LIU C ME S Yt sete Cancel J CHAPTER 3 TSQUERY TIME SERIES ACCESS TsQuery is designed to generate output from a combination of database frequencies and varying types of data into different formats and layouts that serve a range of purposes Click the New Query button on the CRSPSift toolbar Select the Data Environment you wish to use and highlight the TsQuery icon Optionally give your query a name in the Query Name field and then click the OK button You may now fill out the screens required to define your query entities data items date information and report layout ENTITIES In the Entities screen you select which securities indexes or portfolios your query will use To add an entity select an entity type from the drop down menu and click the New button List the default is meant for entering a single security or a small number of securities manually List All calls the full universe of securities in the database selected List File accesses securities specified in a user created input file Portfolio All allows the user to create an equal or value weighted portfolio from the full universe
62. ays details for all batch queries as well as any open standalone queries that have S Si 00 been executed at least once during the current CRSPSift session c Scheduled Batrhes Seach Options A Drvne Mame Alo User Restriction Statue Mo Status Restriction I Schedued Date 5222002 m End Date LJ w Using the search options you can filter the list to display only those g p y play y queries that match certain criteria including query owner name and query status as well as the dates relating to when a query is scheduled to execute when it started execution and when execution ended MANAGING BATCH QUERIES A drop down menu at the bottom left of the Query Queue screen contains actions that you can perform on a batch View Batch Details x Perform 4 ction query View Batch Details Delete Batch Cancel Batch Rerun Batch Delete All Batches
63. cel 97 2003 Workbook xla Excel 2007 Workbook xlax MAT files mat SAS Dataset sas bdat Keyset Display CRSP Mnemonic Keyset Tag SUPPORTED FILE FORMATS Pipe Delimited For Import pipe delimited ASCII text suitable for importing into databases or software packages not directly supported by CRSPSift This option generates a single output file Excel 97 2003 Workbook xls Excel version 8 Note Excel version 8 has a row limit of 65536 total rows If a query generates more than that number of rows the output will be truncated at the row limit Excel 2007 Workbook xlsx Excel XML MAT files mat Matlab version 7 SAS Dataset sas7bdat SAS version 7 Stata File dta SPSS File sav VIEWING OUTPUT FILES If you select a non screen output format CCMQuery will generate one or more output files one for each unique output of the query When the query completes execution the output area displays links to the generated output files Each file will have the name specified in the Output File Name field with the addition of an extension identifying the type of output it contains for example fd for Filing Dates data or adjf for Adjustment Factors data Save Output Clear Output output files saved click on the links below to open file C Documents and Settings nicole My Documents CRSPSift ccmquery sample adjf xlsx file C Docum
64. d key type from the Choose Key I Daly Treastuy Issue Items Type menu Fixed Term Monthly Treasury Issue Items Bond Portfolios Fama Blizs Fixed Term DATA ITEM SELECTION jain RA Rates Favorites Treasury Use the Data Items tab to control what individual data items your query will use Any items you have chosen appear in the Selected Data Items table Data Items Data Groups Data Items Select Edit Data Items lmport Data Item File Export To File Selected Data Items ItemiD Keyset ItemName Publicly Held Face Value Outstanding trntotout Face Value Outstanding treasnotype Treasury record type D gt Jg rm e ag e c rmi e 2 29 m Jem e c N gt gt gt gt C2 e e Clicking the name of a group causes its constituent data items to appear in the Search Results area Or rather than browsing by category click the Search tab There you can enter a descriptive keyword e g return to find items of interest Navigate and Search both result in found data items appearing in the Search Results area of the window Search Results Item ID Variant Data Item Name Header tdretadj Adjusted Return RETADI tdretnua Unadjusted Return RETMUA tmaskret Hold ask return ASERET tmaweret Hold average return AVERET Hald bid return BIDRET tmretadj One Month H
65. e Type Filter allows you to restrict the output on the basis of share type for individual securities The selection is based on the CRSP Share Type Code variable C2 u3ldVH SS399V 314 31111 AUINDSL N J Entity Filter Port Assign Exchange Share Type NASDAQ Market Tiers Share Type Group A Select at least one Group A item Ordinary Common Shares Certificates Amencus Trust Components Prime Score and Units American Depository Receipts Shares of Beneficial Interest ETFs and Units Depository Beneficial Interest Group B Select at least one Group B item Securities which have not been further defined Securities which need not be further defined Companies incorporated outside the US ETFs and Americus Trust Components Prime Score and Units Clased end funds Claged end fund companies incorporated outside the US REITs Real Estate Investment Trusts Select All Clear Select at least one item from both Group A and Group B Hold the Control button while clicking to select multiple items from a group NASDAQ Market Tiers When the NMSIND option is used only NASDAQ issue ranges are restricted The filter has no effect on data from issues trading on other exchanges Entity Filter E Port Assign Exchange Share Type NASDAQ Market Tiers All Markets National Market and Global Markets Small Cap and Capital Markets All markets with Price r
66. e for an index or a particular SUBNO value for a portfolio When these items are selected red text instructing you how to refine your selection appears above the Data m Setti Item Settings area Click on the INDNO or SUBNO to open the list of available indexes or portfolios to which the item may be related his item requires a more precise INDNO value Click on the INDNO cell for more details SS399V S3I3S 3INIL AUINDSL El Data ltem IMD RIO ItermlLi indaret Name Associated Index F El Data Type DataLength 4 Datalvpe f Fixed width 11 6 El Settings Alignment Right Align Header Indaret C ItemL ag 0 AJ U Co Indices Filter T I ios hon 7 Daly Monthly CRSP NYSE Market Capitalization Decies m AJ O IHDHD Mame C 1000000 CASP NYSE Value Weighted Market Index C 1000001 CASP NYSE Equal yeighted Market Index 1000002 1000003 1000004 1000005 1000006 1000007 1000008 1000005 1000010 1000011 CASP NYSE Market Capitalization Decile 1 CASP NYSE Market Capitalization Decile 2 CASP NYSE Market Capitalization Cecile 3 CASP NYSE Market Capitalization Decile 4 CASP NYSE Market Capitalization Cecile 5 CASP NYSE Market Capitalization Cecile 6 CASP NYSE Market Capitalization Cecile Z CASP NYSE Market Capitalization Cecile 8 CASP NYSE Market Capitalization Decile 8 CRSP NYSE Market Capitalization Cecile 10 Select Cancel Repeat this process until all the data items inte
67. e of data they indicate is contained in a file OUTPUT FILE SUFFIX DAILY MONTHLY M DATA GROUP CATEGORY CHAPTER 5 INDQUERY INDEX DATA ACCESS IndQuery is used to access index header and time series data Locate the IndQuery toolbar below the main CRSPSift toolbar Database Daily 20090101 To 20090331 JDirect Edit uj Reset First select whether your query will use the Daily or Monthly database from the Database menu Specify a date range for your query Enter dates using one of the following formats YYYYMMDD YYYYMM or YYYY The following table shows some examples of how to use the various formats RESULT 199612 all data from the beginning of September through December of 1996 199609 1990 all data in the year 1990 1994 19940615 all data from the beginning of 1994 until June 15 1994 19961231 empty data only on the date December 31 1996 INDEX SELECTION In the Choose Indno Type drop down menu specify whether your query will access a Single Series an individual index or a Group of Portfolios Note that Single Series is for use only with a Stock and Index Database Input Output Index Selection Choose INDNO Type Single Series Enter INDNO Input File u Browse All Indices If you know the INDNO of an index you can type it directly in the Enter INDNO box Otherwise click the Find button to open the Find INDNO window
68. earch yields many matches click the column labels to resort the table in the order most useful to you When you have found the company you want click its row in the table and then click the Select button The PERMNO of that company will appear in the identifier field You may also choose to access all issues in the database or issues you specify through creating an input file To search all issues click All Issues in the Security Selection area To use an input file first create a plain text file using the Microsoft Windows Notepad accessory for instance with an entry for each issue one per row using the active key of your choice For example to search for Apple and Microsoft by PERMNO using an input file create and save the following two line text file 14593 10107 Next select PERMNO from the Active Key menu Click Input File in the Security Selection area and then click the Browse button to locate the file you created DATA ITEMS Data items Data Groups Data Groups Data Items Select Edit Data Items Import Data Item File Export To File Selected Data Items MemiD iemName Data can be selected as either individual items going through the Data Items tab or as preselected groups of logically categorized data by using the Data Groups tab SELECT EDIT DATA ITEMS a Clicking on the Select Edit Data Items tab launches the interface for selecting individual event and time se
69. ected TREASNOX as your query s key type you can search by partial or complete TREASNONX Index Family or Term Type Description If you have chosen any of the other three key types search the Treasuries Search window by one of the following criteria TREASNO CRSPID CUSIP Date Issued or Maturity Date Treasuries matching your search criteria appear in a table in the main area of the window If your search yields many matches click the column labels to re sort the table in the order most useful to you When you SELECT EDIT DATA ITEMS have found the treasury you want click its row in the Clicking this button launches the interface for treasuries by TREASNO create the following four line mcd interest only at maturity 2 Semi fil 1 nnual interest 4 Quarterly 1 e interest 02 table and then click the Select button Its TREASNO selecting individual items in TrsQuery will appear in the identifier field of the Input tab p ote For complete definitions of data items an Note F lete definit f data items and E T E E T 4 E ACCESSING ALL TREASURIES calculations employed by the CRSP US Treasury To access all treasuries in the database select the All Databases refer to the Treasury Guide at http www AJ T o reasuries radio button crsp chicagobooth edu documentation e c rn T 4 F E USING AN INPUT EIL Su u lt 3 Search Search
70. ed to apply History share factors from rights distributions Choices are 0 Use shares history 1 Recreate shares history ValidExch Valid exchange identifies the Default is all exchanges exchanges of interest to be used Choices can be modified by clicking on the text box and checking unchecking available exchanges Valid exchanges is used in conjunction with the calculated returns items cret and cretx Fields that can be edited are parameters in bold black text associated with adjusted values These are set to default values and may be modified El Data Item ItemID adibidlo Kevset Name Bidlo Adjusted E Data Type DataLength 4 DataType F Fixedwidth 12 5 Parameter Type AdjDate Y TTYHMDD AdiTvpe 1 GapRiule 1 Settings Alignment Right Align Header Adibidla ItemLag Numkeysets Select each item you want in your query adjust its parameters if necessary and then click the Add to Query button to add it to the Query Items area below the Search Results Query Items variant Data Item Mame Customized Price or Bid Ask Average Mo Price Adjusted Mo Bid No Bid Adjusted Mo Ask No U v Items in the Query Items area appear in the order that they will appear in your query s output To adjust the position of an item select it and click the blue up and down arrows to move it up or down the list The other buttons are A Ed
71. ent session is shown by default To view a previous session select a date and time from the Session drop down menu Hew CRSP Compustat Merged History CRSP 1325 Da US Stock amp Indice Onire C New CRSP 1925 Monthly US Stock amp Indices C arspdata M2007 05 CRSP COMPUSTAT Merged Database Sample C crepdatalS ample 2 Ovepsample Mew CRSP 1925 Daly US Stock t Indices Sample E VaspdlataN S ample 2 0 disaenples Mery CRSP 1325 Monthly US Stock amp indices Sample C crepdataS ample 2 Purizamples Known CRSP 1925 Daily US Stock amp Indices CNetepdata Ermm CRSP 1325 Month US Stock Indices nine CVoepdate Known CRSP Compustat Merged History CActepdataNCPZ200705 Databases Found Totat 3 Dab Stock Databases 3 Monthy Stock Databases 3 Compustat Databases 3 Faded m CRSPSIFT OPTIONS SERVICES am pom OKIE Hy Workspace The Services Options screen displays om Coie Cena borsa technical information about various ERSPSR Fi Tener JIC Named Pees DSK2U6 subsystems of the CRSPSift application including Manager Service and Query Engine connections The only editable feature is the Query Engine Connection Account D gt Ev C2 ad e e Tl e Jg e e CRSPSIFT OPTIONS QUERY QUEUE O T The Query Queue lists and displ
72. ents and Settings nicole My Documents CRSPSift ccmquery sample ai xlsx ile C Documents and Settings nicole My Documents CRSPSift ccmquery sample fd xlsx file C Documents and Settings nicole My Documents CRSPSift ccmquery sample secl xlsx Clicking one of the output files links opens the File Download dialog box Do you want to open or save this file File Dow fx Mame ccmquerv sample __adjF x sx EHH Type Microsoft Office Excel Worksheet 5 58 KB From 2 Documents and Settings nicole My Documents Open Save Cancel Always ask before opening this type of file While Files from the Interet can be useful some files can potentially harm Your computer IF you do not trust the source do not open or save this file What s the risk Click Open to open the application and output file to which the data items were written ccmquery_sample_adjf Thrudate Effdate Ad Fygvkey lt 16901 20050225 3 1690 20050228 1 4 12141 20030214 1 5 12141 20030218 99999999 1 OUTPUT FILE SUFFIXES The following table lists output file suffixes and the type of data they indicate is contained in a file DATA CATEGORY DATA GROUP NAME OUTPUT FILE SUFFIX m Additional Descriptions Additional Descriptions Additional Descriptions Additional Descriptions Additional Descriptions Additional Descriptions Additional Descriptions Annual Data Audit Dat
73. eporting Small Cap before June 15 1992 National Market and Global Select Market National Market and Global Market Global Select Market Only SIC SIC issue range restriction is applicable to List and Portfolio Entity Types Each number represents a single SIC Code You can filter the data to output a range of SIC values or an individual SIC value For example 1000 2000 3725 would extract all securities with SIC Codes between 1000 and 2000 and all with and SIC code of 3725 Entity Filter Exchange NASDAQ Market Tiers Port Assign Groups Standard Industrial Classification SIC 2372 I Example 1000 2000 3725 Portfolio Assignment Portfolio Assignments are only available for use with a Stock and Index Database A Stock and Index Database subscriber has access to nine sets of daily and eight sets of monthly portfolios Each entity in the CRSP universe may be a member of a number of CRSP portfolios which are based on combinations of market capitalization and exchange membership or group membership The Port Assign Filter allows you to restrict your query to a subset of the CRSP universe composed of your choice of portfolio sets and the portfolios within them To filter by portfolio assignment select a Database Type Daily or Monthly and then select a portfolio set from the list of portfolio sets Next enter a list or range of portfolios from the set For example to choose po
74. es for a query or you can select one of several predefined calendars To use a predefined calendar click the Calendar Name radio button and select a calendar Daily Weekly Monthly Quarterly or Annually from the menu Calendar File Calendar Name Daly v Brow You may want to create a custom calendar with dates for output that are not available in one of the standard Calendar Name options Click on the Calendar File radio button to select a user generated calendar Refer to the Calendar File Wizard chapter in this guide for information on creating a custom calendar file DATE RANGE Date usage is linked to any date information entered in the Entities screen If an event date is assigned to an issue either in the Entities screen or included in the list entity input file Relative Date Range should be selected 2 ag gt e e c rri ad 25 rri e rri rri e gt gt C2 rri e e Date Range Relative Date Range Day s Davis 10 before s 110 after w Your relative date range will include all the dates which fall within 10 day s before and 10 day s after the dates that you set for each Entity on the Entities Tab A relative date range specifies a window of time in days based on the event date chosen in the Entities screen It is tied to the calendar selected for the output Take for example an event
75. et mret Index counts and values dval mval CHAPTER 6 CCMQUERY CRSP COMPUSTAT MERGED DATABASE ACCESS CCMQuery provides access to Compustat data for all securities in the Compustat universe as well as linking information for those securities also in the CRSP Universe Click the New Query button on the CRSPSift toolbar Select a data environment that includes a CRSP Compustat database Highlight the CCMQuery icon enter a descriptive name and then click the OK button THE CCMQUERY TOOLBAR dia 70503 0 200905 i Range 200603 To 200903 tal Date Display Direct Edit Reset First specify a date range for your query Enter dates using one of the following formats YYYYMM or YYYY The following table shows some examples of how to use the various formats RESULT 199609 199612 All data from the beginning of Septemper through December of 1996 1990 All data in the year 1990 Next from the Date Display menu choose either Fiscal Period the default or Calendar 5 Date Display Direct Edit Calendar Fiscal Period Default COMPANY SELECTION Entities in the CCM Database are accessed by GVKEY company identifier GVKEY IID security identifier GVKEYX index Securities linked with CRSP can be accessed by CRSP identifiers also PERMNO and variations PERMCO Each GVKEY has associated with it a primary issue When using GVKEY with no IID CCMQuery will automatica
76. f Period Ask Adjusted End of Period Adjask Bidlo Minimum in Period Bidlo Bidlo Adjusted Minimum in Period Adibidlo Askhi Maximum in Period Askhi When you highlight an item in the Search Results area its attributes and a short text description appear in the window areas to the right GLOBAL OPTIONS E AdiDate Y TTYHIHDD AdjTvpe 1 AdiTypes BaseAmount 100 BaseDate YTTYHIMHDD GapRule GapWindow 10 RightsRule Global Options StkQuery allows users to modify output data items by changing default parameters for certain types of items Defaults can be modified for all items selected in a query by defining parameters in the Global Options table on the Data Items screen PARAMETER DEFAULT AdjDate Default is to adjust values through the latest date of the database AdjTyp Default is to apply all factors AdjTypes Default is to apply stock splits and dividends Default is 100 BaseAmount GapRule Default is to set values to missing upon exceeding the specified gap GapWindow Default is 10 DEFINITION The adjustment date is the anchor date on which all data are presented as they were reported and are adjusted back in time by selected or default factors from that point The adjustment type value represents the factors that are applied to price adjustments Choices are 1 App
77. found by the Scanner any valid environments based on those databases are presented to you to select and add to CRSPSift Spit Options DSK2116 My Workspace x S Scan Fokker Hei Pathe Sean Specie Locator O Biecursrely Scan Civectionien Cit Hea Ernyo s Furr If your databases are installed outside the default locations you can add your own Folder Hint Paths to guide the Environment Scanner s search Click the Add button next to the list of paths Next click the Browse button and choose the directory where you would like the Scanner to search for databases Click OK to add the directory to the list Enter your new Folder Hint Path To delete a Folder Hint Path select it from the list and click the Delete button SCAN OPTIONS The Environment Scanner s Scan Options allow you to fine tune how its scan is performed The default behavior for the Scanner is to search defined Folder Hint Paths scanning the top level of those folders for valid databases CRSPSIFT USER GUIDE e PAGE 58 If you would like to search a directory without adding it to the list of Folder Hint Paths click the Scan Specific Location checkbox and then the Browse button to locate it SCANNING e After setting the appropriate Scan Options click the Start Scan button to activate the Environment Scanner gt When the scan begins the Start Scan button is replaced with the Cancel Scan b
78. he box and is typically STD or Standard The Keyset number is provided the number of GVKEYs the number of data points the date range populated by the data and a description of the Keyset Keyset Filter E Tag 8 H of GVKEYs H data Beg Date End Date Kep Set Description 2 2 2 2 sss 5 FS PFU SFAS FRE PEI 15941 230 1946123 19781229 13821231 13381231 13581231 20001 225 13821231 2007123 2008123 2001231 2008123 200123 20021221 19831 230 Mull Keset no varnations using secondary keys Industrial Format Consolidated Information Standardized Pres Industrial Format Congolidated Information Standardized Sum Financial Services Format Consolidated Information Standard Industrial Format Pro Forma Reporting Standardized Presente Industrial Format Pre FASB Reporting Standardized Presenta Industrial Format Consolidated Information Standardized Date Industrial Format Pre Divestiture Reporting Standardized Pres 2 4 p n 1 1 13331231 13331231 Industrial Format Pro Forma Reporting Standardized Summary lij gt Select Cancel Hold Ctrl key to select multiple keysets Selected Reysets 1 Choose different or additional keysets and click the Select button to close the window DATE The Date screen allows you to control what dates will be used in the output for the data items selected for each entity CALENDAR OPTIONS You can define your own set of dat
79. he last Thursday of every third month Frequency O None ODay 1 Fond month s Daily v Saturday w of every 1 month s O weekly Monthly O Yearly The First Yearly The yearly frequency offers options similar to those of the monthly A calendar might include March 3rd of every year or the first weekend day in January of every year Frequency None S Every May v Daily Weekly The First Day of every January O Monthly 2 Yearly CUSTOM DATES In addition to or in place of a specified frequency a calendar can include specific custom dates To add specific dates click the menu on the left in the Custom Dates area Use the popup calendar control to find and select a date and then click the Add Date button to add it to the list on the right To remove a date from the list click the Remove Date button Custom Dates Add custom dates to the calendar file by Selected Custom Dates List choosing a date on the left and clicking the Add Date button to add the date to the custom dates list the right 3 5 5 October 21 2007 Saturday March 05 2005 Remove Date When you are finished defining a custom calendar click the Run button at the bottom of the Calendar File window to create the file If you would like to view the generated file click the View File button in the dialog box that appears If not click the Done button The cale
80. icole My ww Documents CRSPSift sp500example txt mu File Type Type Delimited l Delimiter o e c m Using this method add as many entities as your query zs requires If you wish to change the order in which TS entities appear in the hopper click on a row and use m C the arrow buttons to move it up or down 2 Tuning AJ Keep what is valid what is valid T 0 go back and edit an entity click its row in the o Ed ilter Tun hopper and then click the Edit button gt r 5 Filter Tuning When you have finished editing the entity click the y Each entity filter can be further refined by selecting T I T ave button to save your changes To delete an entity one of three tuning flags The flags are from the hopper select its row and click the Remove O T iti gt Keeowhatisvalide Ween ony hending nistonyor button The Remove All button deletes all entities that 2 an issue where the selected filter applies you have added i Keep none if ever invalid Discard all data on an T issue if the selected filter fails to match at any point DATA ITEMS u in the time period of the query 2 Keep all if ever valid If the selected filter matches In the Data Items screen you can select the CRSP data z an issue at any point during the time period of the items relevant to your query query all data on that issue is kept iem Eres Due Layout Toren Toe El
81. it Click this to edit an item s attributes T Remove the selected item from your query Remove all items from the Query Items list Click the OK button to confirm the addition of the selected items to your query Click cancel to exit the StkQuery Data Items window without making any changes Import Data Item File Clicking this button allows you to import a list of data items into your query from a text file The file contains a single column of item mnemonics item IDs Export to File Use this option to output the Selected Data Items list to a text file containing each item s mnemonic itemid This text file may then be imported into other queries using the Import Data Item File button DATA GROUPS The Data Groups tab allows you to select combinations of related event or time series data items Available groups include Event Data Adjusted Delistings Adjusted Distributions Adjusted Shares Delisting History Distribution History Group Inclusion NASDAQ History Name History Name History Short Portfolio History Raw Shares History Shares History C2 2 OU rri JA 3 e rri JA e e A I gt gt c rri e e J Sf dalSdsa dino Time Series Data Adjusted Market Summary Adjustted Price Shares Market Summary Price and Index Levels
82. l it to a location other than the default click the Change button and navigate to the folder of your choice When you have selected a destination folder click the Next button 13 CRSPSift 4 1 5 Professional InstallShield Wizard Destination Folder Click Next to install to this Folder or click Change to install to a different Folde Install CRSPSift 4 1 5 Professional to C Program Files CRSP CRSPSift 4 1 5 Professional Edition InstallShield Cancel The InstallShield Wizard is now ready to install the program Click the Install button to begin 13 CRSPSift 4 1 5 Professional InstallShield Wizard Ready to Install the Program The wizard is ready to begin installation Click Install begin the installation If you want to review or change any of your installation settings click Back Click Cancel to exit the wizard InstallShield Install i Cancel Click the Finish button to exit the InstallShield Wizard is CRSPSift 4 1 5 Professional InstallShield Wizard InstallShield Wizard Completed The InstallShield Wizard has successfully installed CRSPSift 4 1 5 Professional Click Finish to exit the wizard Show the Windows Installer log Cancel LAUNCHING CRSPSIFT FOR THE FIRST TIME The InstallShield Wizard creates a shortcut to the CRSPSift application on your Desktop Double click the shortcut icon to launch CRSPSift Before you can use CRSPSift you must tell it the location of your instal
83. led CRSP databases The first time you run the application you are presented with the Environment Startup Scanner CRSPSift Options SIFTPRO_3_1 My Workspace Environment Scann x Scan Options Environment Details v v scan Folder Hint Paths El Environment Details Environments Si CalendarFile CRSP_CCM Y Scan Spedific Location CompustatDB CMS_201108 Cl Scan Subdirectories Default False IsSample True MutualFundDB SiftComponents Configur Name FIS200912_CMS2011C ResearchIndex SiftComponents Configur StockSet FIS_200912 SupportedProdu Collection TreasuryDB SiftComponents Configure WorkingDirector ouserprofile My D Finished scanning New Environments Found Load Default Environment Name N FIS200912 CMS201108 No TRS200012 Using this tool you create what are known as data environments A data environment is a collection of one or more compatible database products FINDING ENVIRONMENTS Upon launch the Environment Startup Scanner automatically searches the default CRSP database installation directory C Ncrspdata It builds and displays environments for the most recent databases C2 Ta Jg rri E NO dfil3S ANY NOILVTIVISNI Mew Environments Found Load Default Environment Mame m Ma FIZ200908 CM2200910 Ma FIS200812 CMS200907 TRS200812 If your CRSP databases are installed in a directory other than
84. ll determine the proper Cal Based 2007 4 for the report layout Use the controls to the left of the quarterly calendar O preview pane to test various scenarios To see how a ZU P E report will look given particular settings modify the Row Delimiter 72 layout controls and then click the Preview button gt T Controls the number of rows between output lines he contents of the pane will reorganize to reflect uis C Was he first integer is the number of blank lines between co your settings and the axes will briefly flash with color rows when the Z axis value changes and impacts Z flag to identify themselves X is purple Y is green and Z jean Seah 1 her of 02 l options 1 and 3 The second integer is the number o is red It may be helpful to think of X as rows Y as P i 5 blank lines between all data rows The default is 0 0 E columns and Z as page Character Delimit When you have found a suitable layout click the LAC Browse button next to the Location and File Name A character string placed before and after all character field Navigate to where you want to save your report Y pori string fields Company Name for example in report give the file a descriptive name and then click the Save records The default is no character string delimiter button When you execute the query CRSPSift will es For example a value
85. lly attach the IID of the primary isssue most often 01 From the drop down menu in the Company Selection area specify the key type you wish to use L 1 Output Input Company Selection Choose Active Key PERMNO Composite C PPERMNO ICKER Next identify the issue or companies of interest If you know an identifier enter it directly For example the GVKEY of Apple Computer is 1690 Given that knowledge you can set the key to GVKEY and enter 1690 as the identifier If you do not know an identifier click the Find button to open the Company Lookup window Search SearchBy Search Criteria Locate CompanyName gt Contains Re 45 Company s matched your search criteria Compustat Company Enter Keyword or Number s Search Historic Data Search Current Data GVKey ID PERMNO SIC CUSIP Ticker CompanyN ame EffectiveR ange 0 GENERAL MOTORS CORP 19620131 19620701 lo cM GENERAL MOTORS CORP 19620702 196801 01 0 37044210 GM GENERAL MOTORS CORP 196801 02 20090227 5 0 37044210 GENERAL MOTORS CORP 13380430 20030227 0 37044277 GENERAL MOTORS CORP 195001 01 20090227 lo 37044276 HGM GENERAL MOTORS CORP 19500101 20030227 0 37044272 BGM GENERAL MOTORS CORP 19500101 20030227 lo 37044212 GMS GENERAL MOTORS CORP 195001 01 20090227 0 37044240 GME GENERAL MOTORS CORP 19841019 19
86. ly All Factors or 2 Apply Stock Splits and Dividends The adjustment types values represents the factors that are applied to shares and volume adjustments Choices are 1 Apply All Factors or 2 Apply Stock Splits and Dividends The base amount is the amount to be reported on a specified base For use with Time Series items Total Return Index Level and Price Index Level If specifying a base amount a base date must be provided for the date to apply the amount Values before and after the specified date will be indexed to the amount Gap rule defines when and if to introduce missing values when a specified number of periods passed with no new data Choices are 0 Continue data after the gap and 1 set missing values after the gap The gap window defines the maximum number of dates allowed to pass between two dates where the previous value of an item remains valid For example with the default of 10 a price from up to ten trading dates ago is eligible for use in the returns calculation if there are no more current prices Once that 10th day has passed using the default gap rule the price Is considered missing and will no longer be available for use in the returns calculation C2 gt za y N A c rri za lt N e A gt gt C2 e e PARAMETER DEFAULT RightsRule DEFINITION Default is to use Share The rights rule is us
87. ms for those items that have them and to change the default from providing output in its Reported Currency to converting it to US Dollars When items and options are selected click on OK to return to the main CCMQuery screen Selected items appear in the Screen Note Global options must be set prior to selecting data items in order for them to be applied c Au3001IN29 9 u31dVH SS399V ISVEVLVG d3983lN 1V1SfhdIN09 dSH9 Y J LalSdSd dino H 4 Data Items Groups Data Items j Met Income Loss IMPORTING DATA ITEMS FROM A FILE Instead of choosing data items using the item selection screen you can import a set of data items from a text file To do so click Import Data Item File The file should contain one data item per line in the format itemid keyset Keyset number is optional the item s default keyset will be used if it is omitted as for the data item filedate in the following example ofid 0 Oftco ofnm 0 filedate sale 5 Imported items will be added below any items already present in the Selected Data Items list EXPORTING DATA ITEMS TO A FILE Click Export To File to export items in the Selected Data Items list to a text file in the format described above GROUPS Click on the Groups tab to view choices of pre defined groups of Compustat data _Datatems_ Gus Items Groups Groups Data Groups El
88. ms to your query Click cancel to exit the TrsQuery Data Items window without making any changes Import Data Item File Clicking this button allows you to import a list of data items into your query from a user created text file A data item file is a plain text file containing a list of TrsQuery data ItemIDs one per line For example in Notepad or another text editor create a file with the following four lines treasno tname tdbid tdask Save the file as trsimport txt in your CRSPSift working directory Now click the Import Data Item File button and select trsimport txt The data items listed in the file will be added your query Export to File Use this option to output the Selected Data Items list to a text file containing each item s mnemonic ItemID This text file may then be imported into other queries using the Import Data Item File button DATA GROUPS The Data Groups tab allows you to select clusters of related data items saving you the time of selecting and adjusting a group s items individually The following data groups are available in TrsQuery Groups correspond to the item groupings of the tree navigation structure in the TrsQuery Data Items C2 Jem Jg rri e c od rri gt e c rri e c gt gt Jom C2 eo N esr 1315d5do window Some data groups apply to individual treasury issues while others apply
89. n 12 months Event Date Date Options Event Date w Event Date YYYY MM DD The Entity Event Date specifies a single date in YYYYMMDD An Entity Event Date must be paired with a Relative Date Range on the Date tab The Relative Date Range controls the number of calendar periods before and after the Event Date to report in output Event Date does not work with indexes or portfolios For example suppose you enter an Entity Event Date of 19991231 Next in the Date tab you select the Daily calendar and give a Relative Date Range of 2 days before and 2 days after the Entity Event Date The query output will include data for trading days 19911229 though 19920104 including the event date For more information about calendars Fixed Date anges and Relative Date Ranges refer to the section on the Date tab below ENTITY FILTERS Several entity filters are available in TsQuery allowing you to refine your selection of securities by such criteria as stock exchange share type and SIC code These options are available for list and portfolio entity types but will not work with indexes Exchange The Exchange Filter allows you to filter the trading history of issues on the basis of stock exchange This option is available only when using the List and Portfolio Entity Types SIC Grue o Groups Exchange Share Type NASDAQ Market Tiers Exchanges NASDAQ aRCA Vv NYSE v Amex Share Type The Shar
90. n the fund market value shares held 96 of fund TNA Company Data Company Information Security identifiers non equity coupon rates and maturity dates OUTPUT OPTIONS You can save the output of a MutQuery in one of several formats Click the Output tab to show the Output Options panel OUTPUT TO SCREEN The default option is text based output formatted to fit the screen width specified in the Width control 80 characters is the default output width Helpful Hint Many queries on the mutual funds database can result in large files Consider output format when extracting data The screen default is appropriate for individual funds Excel 2007 will be acceptable for many queries but any query that is extracting a large universe of funds over a long date range will quickly exceed Excel row limits SAS is CRSP s recommended output format for large query results Screen output appears in the output area at the bottom of the MutQuery window Clear Output CRSP Fundno 031278 Mgt Fee eves 2781929 Se eS Yo all the output to one text file OUTPUT TO FILE You can also save query output in a number of external file formats When you choose a non screen output format the Output File Name field becomes active In order to successfully execute the query you must select a location to store the output file Click the Browse button to do so MutQuery 1 Input Output Output Options
91. ndar file is now saved in the location you specified To use your custom calendar with TsQuery click on the Calendar File radio button on the Date tab browse to the location of your calendar file and enter the full path and filename Other date options on this tab are superceded by the Calendar File option To dismiss the Calendar File window click the Close button REFERENCE DATA CRSP STOCK AND INDEX DATA CRSPSift features builtin reference tables describing data codes and their values References can be found under References in the Tools menu When selected tables appear as floating panels These panels can be positioned to the side of the main CRSPSift window or docked to the window See the Docking section below for more information The following reference tables are available in CRSPSift for CRSP Stock and Index data Delisting Codes Distribution Codes Missing Return Codes NASD Index Code NASDAQ National Market Indicator NASDAQ Status Code North American Security Exchange and Index Codes Security Status and Valid Exchanges The information in these tables can also be found in Chapter 5 of the Data Description Guide Data Coding Schemes COMPUSTAT XPRESSFEED DATA The following references are available in CRSPSift for CCM Reference Codes Reference Numbers Currency amp Exchange Rates Economic Indicators Reference Codes and Reference Numbers explain codes found in pr
92. ogram output Currency amp Exchange Rates and Economic Indicator data can be displayed within the reference panel or output to a variety of file formats including Excel SAS and Matlab C2 J gt Jg rri ze e e pee e N J LalSdSd dino H CCM Link References include tables identifying codes found in the tables generated by CRSP in the process of linking CRSP and Compustat company and security level identifiers All Reference tables associated with CCM require a valid data environment that includes a full CCM database CRSP TREASURIES DATA The following reference tables are available in CRSPSift for CRSP Treasuries data First Coupon Payment Date Name of Government Security Number of Interest Payments per Year Payment of Estate Tax Code Source Daily Source Monthly CRSP MUTUAL FUNDS CRSP Mutual Funds reference tables are available in CRSPSift for Delisting Codes Lipper Asset Codes Rear Load Type Policy Wisenberger Objective Codes Additional tables and reference information can be found in the Appendices at the end of the CRSP US Survivor Bias Free Mutual Fund Database Guide RESEARCH INDEX DATABASE The following reference tables are available in CRSPSift for the Research Index Database Holding Type Organization Type Price Source Primary Exchange Security Status Share Ch
93. olding Period Return RETADJ tmreknua Unadjusted Return MRE THUS Adjusted Return Embidret Etmreknxs Add to Favorites When you highlight an item in the Search RETNs5 Add to Query Results area its attributes and a short text description appear in the right panel of the screen E Data Item ItemID Fdretadi Kevsek Marne Adjusted Return SubMa El Data Type DataLength 14 DataType F Fixedwidth 143 41 El Settings Alignment Right Align Header RETADJ ItemLag Mumkeyseks Select each item you want in your query and click the Add to Query button to add it to the Query Items area below the Search Results Er Items Item ID Variant Data Item Mame Customized k Empubaut Publicly Held Face Value Qut Yes I Face value Outstanding Yes Treasury record type Yes tdretadj dj des Adjusted Return Yes Items in the Query Items area appear in the order that they will appear in your query s output To adjust the position of an item select it and click the blue up and down arrows to move it up or down the list The other functions available are r Edit Click this to edit an item s attributes Remove the selected item from your query Remove all items from the Query Items list Click the OK button to confirm the addition of the selected ite
94. ome to the InstallShield Wizard for CRSPSift 4 1 5 Professional The InstallShield R Wizard will install CRSPSift 4 1 5 Professional on your computer To continue click Next WARNING This program is protected by copyright law and international treaties i Cancel Read the License Agreement To continue installation click the radio button labeled I accept the terms in the license agreement and then click Next i CRSPSift 4 1 5 Professional InstallShield Wizard License Agreement Please read the following license agreement carefully mum Software Media Agreement Agreement The CRSP Media and the software contained therein collectively Software Media are an information service of CRSP The Center for Research in Security Prices at The University of Chicago Booth School of Business whoae nrineinal nlare nf hnaineaa ia 105 W v idama pint OI do not accept the terms in the license agreement InstallShield CRSPSIFT USER GUIDE PAGE 2 dfil3S ANY NOILVTIVISNI Enter your name and the name of your organization on the Customer Information screen and then click Next ie CRSPSift 4 1 5 Professional InstallShield Wizard Customer Information Please enter your information User Name ERP Organization InstallShield Now choose the destination folder where CRSPSift will be installed on your computer If you would like to instal
95. ose an output file path for the calendar file x Date Range for Frequency Frequency None Daily O weekly Monthly O Yearly Custom Dates Add custom dates to the calendar file by Selected Custom Dates List choosing 8 date on the left and clicking the Add Date button to add the date to the custom dates list on the right 111102009 v Add Date Remove Date CALENDAR OUTPUT FILE In the Calendar File window first specify a location to save the file Type a path into the Output File text box or click Browse to navigate to the desired directory DATE RANGE FOR FREQUENCY Next define the custom calendar s frequency and the date range for the frequency In the Date Range for Frequency area click the start date menu and select a date from the popup calendar Date Range for Frequency Sun Mon Tue Wed Thu Fri Frequency CGCCUEICUES Se Sa an YS 1 5 8 9 ii 12 Daily 15 16 17 18 19 22 23 24 25 26 Q weekly O Monthly O Yearly Then either specify an end date in the same manner or choose to end after a certain number of occurrences Occurrences refers to the frequency you define in the Frequency area below For example say you define a weekly frequency recurring on Wednesdays and choose to end after twelve occurrences The query report will include data only from the twelve Wednesdays subsequent to the start date yo
96. output files saved click on the links below to open DATA GROUP OUTPUT FILE SUFFIX ZU file C XCRSPSOiftirrsquery2 debt xls Treasury Master tmstr G file C CRSPSift trsquery2 fh xls C file C XCRSPSOiftXitrsquery2 tmstr xls zz ile C XCRSPSiftitrsquery2 yieldm zls Treasury Index tidx Treasury Payments tpaymt Yield Daily yieldd Current History Quote Daily quoted Clicking one of the output files links opens the File TE de Download dialog box Yield Monthly yieldm Quote Monthly quotem Index Monthly File Download Do you want to open or save this file Fama Bliss Discount fbdisc Fama Bliss Riskfree fbrfree Pa Mame trsquery2_debt xls HHI Type Microsoft Office Excel 97 2003 Worksheet 5 50 KB From Open Save Cancel w Always ask before opening this type of file Term Struct term Fama Bond fbond Debt debt Daily Debt ddebt Fixed Term fterm Fixed Term Header fh Fixed Term Timeseries Rates rate harm your computer IF you da nat trust the source do not open or 9g While files from the Internet can be useful some files can potentially save this file what s the risk Rates Header rh Rates Timeseries CHAPTER 8 RIQUERY RESEARCH INDEX DATA ACCESS RIQuery is used to extract data from the new CRSP Research Index Database Access is designed for intraday index history and index constituent security data as well as daily start and
97. rest to you appear in the hopper at the bottom of the screen As in the Entities screen use the arrow buttons to the right of the hopper to change the order in which data items appear Iter ID Wariant Data Item Marne Customized cumiret U Returns on Incam Ma No 0 Ask Adjusted Na To remove a single item select its row and click the single document button To remove all items from the hopper click the multiple document button c 2 JA Ps e c rri JA e rri rri e Jm m e e IF Edit Click this to edit an item s attributes N J T Remove the selected item from your query Lt x Remove all items from the Query Items list r1 Click the OK button to confirm the addition of the selected items to your query Click cancel to exit the C StkQuery Data Items window without making any changes Z To modify the settings for a data item in the hopper select its row and click the Edit button Change the data item settings to your liking and then click the Save Changes button When you change an item s settings from the default its entry in the hopper displays a Yes in the Customized column To cancel changes click the Cancel button Search Results Data ltem ItermlDi camnarm Variant Data Item Mame Header Mame Company Name GubMa O Returns without Dividends Data Type Associated Index Returns DataLength
98. ries m items in RIQuery o The RIQuery Data Items window is divided into several areas m lt SS399V VLVG X3QNI HOYVAS AY Items are divided into logical groups in the Navigate Search area RiQuery Data Items E RiQuery Data Items ResearchIndex Search Criteria Contains Enter Keyword Clicking the name of a group causes its data items to appear in the Search Results area Or rather than browsing by category click the Search tab There you can enter a descriptive keyword e g float to find items of interest Browsing and searching both cause the data items found to appear in the Search Results area of the window u r a Navigate Search Search Results ElDataltem ter Search By Een ID Variant Data Item Name Header Imm OS ResearchIndex z riefffit 10 Effective Float Factor EffFloat El Data Type Search Criteria 0 Effective Float Factor Flag EffFloatFlg DataLength D Contains riefftso Effective Total Shares Outstanding EFFTSO c Enter Keyword rifltfctr n Float Factor FloatFctr fot t El Settings a rifltFctr Flg Float Factor Flag FloatFctrFlg Abonment ritsa Float Shares Outstanding F50 Flags No rifso_flg Float Shares Outstanding Flag Flag Add to Favorites Add to Query Query Items
99. rt Data Item File button DATA GROUPS The Data Groups tab allows you to select clusters of related event and time series data items MutQuery1 x Input Output Data items Data Groups Mutual Fund Selection Choose Key Type E Data Group Settings CRSP FUNDNO Enter Key Value a Holdinge onse Pomo Map Find ings ings Ir Find gt gt gt O File 0 Mutual Fund Contact Info nput lems D Mutual Fund Deesdends Items OAI Keys p Mutual Fund Front Load Available groups include Mutual Fund Data Contact Information Management Company contact information Dividends Distribution type and amount reinvestment NAV history Front Load Load amount percentage relevant date ranges Fund Fee Fees and ratios history Fund Header Descriptors and flags Manager Advisor Fund Header History History of Descriptors and flags Manager Advisor Fund Style Flags and classification codes Master Items Identifier codes date ranges for daily and monthly data Rear Load Load type dollar amount withdrawal fees data Annual and Quarterly Summary Snapshots of fund performance related data Fund makeup by percentage Daily and Monthly Time Series Daily NAVs and returns Monthly NAVs TNA returns CRSP Portno Map Identifier mapping of portfolios associated with a fund with begin and end dates Portfolio Data Holdings Detail Listing of fund holdings their ranks withi
100. rt the installation disc into your computer s DVD drive and navigate to it in Windows Explorer Double click the file setup html to open it in your web browser od Enterprise Client Enterprise Server L CRSP Softwarelicense html Professional HTML Document a kB e Setup hkml e HTML Document a KB After reading the installation terms contained in the document proceed to the section titled Go To An Installer and click the Install Professional Edition XP SP3 and Windows 7 Only link to launch the installer program Note Installers for prerequisite software are included on the CRSPSift installation disc CRSPSift 4 1 5 Professional InstallShield Wizard CRSPSift 4 1 5 Professional Setup is preparing the InstallShield will guide you through the program setup process CRSPSift 4 1 5 Professional InstallShield Wizard CRSPSift 4 1 5 Professional requires the prerequisites listed below If you are missing a prerequisite or if the version has not been recognized please exit the installer and verify that the prerequisite is installed and functioning correctly Prerequisite Detected Version Status Windows Installer 4 5 Adobe Flash 4 5 6001 22159 10 1 102 64 For your convenience we have included many of these prerequisites on the installation DVD CRSP Support 312 263 6400 To begin installation click the Next button l3 CRSPSift 4 1 5 Professional InstallShield Wizard Welc
101. rtfolios 1 3 4 5 and 10 enter 1 3 5 10 in the Portfolio List field Refer to Chapter 4 of the Stock and Index Data Description Guide for a list of all portfolios and sets of portfolios Entity Filter e Port Assign Share Type Type Portfolios NYSE AMEX NASDACQ Cap Assignments NYSE AMEX Cap Assignment NASDAQ Cap Assignment NYSE Cap Assignment AMEX Cap Assignment NYSE AMEX Betas NYSE AMEX Standard Deviations NASDAG Betas NASDAQ Standard Deviations Portfolio List Example 1 3 5 Note Be sure to match the database type of the items you select on the Data Items tab to the database type of the portfolio you filter by here For example if you filter by portfolios in the Monthly NYSE Cap Assignment select items only from the Monthly Stock amp Index group on the Data Items tab Groups Currently the only group defined is the S amp P 500 Constituents Selecting this filter limits output to those issues listed in the S amp P 500 during the time period covered by the query Use of Groups is limited Clicking an entity s row in the hopper displays the to Stock and Index Database subscribers options and values you have set for it Entity Filter F soo rg asa Value C Input Field Name Entity PORT FILE m Weight lequal weight weight A File Name Na ae ene ay ae Settings n
102. ry code When you are finished click Save If the syntax of your query is correct CRSPSift automatically populates the appropriate fields in the other screens to reflect your query code EXECUTING A QUERY After executing a query click the View Output button on the CRSPSift toolbar Fy Q Execute 4 Cancel X Close View Output 4 The File Download window opens prompting you to open the output file in the target application or to save it in a different location Do you want to open or save this file File Dow E i Name Sample xlsx ES Type Microsoft Office Excel Worksheet 79 2 KB From C Documents and Settings nicole My Documents Save Cancel Always ask before opening this type of file harm your computer If you do not trust the source do not open or save this file what s the risk 9 While files from the Internet can be useful some files can potentially Click Open to open the output file in the target application Excel 2007 in the example shown below EE Sample Date Company Name 2 10107 12 1 2000 MICROSOFT CORP 28 3125 3 10107 12 4 2000 MICROSOFT CORP 28 21875 4 10107 12 5 2000 MICROSOFT CORP 29 96875 10107 12 6 2000 MICROSOFT CORP 28 375 6 10107 12 7 2000 MICROSOFT CORP 26 5625 7 10107 12 8 2000 MICROSOFT CORP 27 21875 8 10107 12 11 2000 MICROSOFT CORP 29 03125 9 10107 12 12 2000 MICROSOFT CORP 29 1875 10 10107 12 13 2000 MICROSOFT
103. s an access tool for CRSP security data Use it to extract event histories for name and identifier changes distributions delisting It can also perform time series extractions IndQuery is an access tool for CRSP Indexes included in the CRSP US Stock or Stock amp Index Databases CCMQuery enables you to access all Compustat data from the CRSP Compustat Merged Database link history data by using Compustat s GVKEY and IID identifiers TrsQuery is a tool for accessing CRSP Daily and Monthly US Treasury Databases MutQuery provides access to the CRSP Mutual Funds database containing fund and holdings data items RIQuery introduces CRSP s Research Index database built upon CRSP s new investable index series and containing new intra day data items CRSPSIFI MINIMUM SYSTEM REQUIREMENTS FOR IHE PROFESSIONAL EDITION Microsoft Windows XP Professional SP3 or Windows 7 32 and 64 bit Display resolution of 1024 x 768 or higher Powershell 1 0 for Windows XP Windows 7 comes prepackaged with Powershell Adobe Flash Player 11 Note CRSP recommends using CRSPSift Professional with a local system account on databases installed locally CRSPSIFT USER GUIDE PAGE 1 CHAPTER 2 INSTALLATION AND SETUP This chapter shows you how to install the CRSPSift software on your computer and describes the configuration options available when you launch it for the first time INSTALLING CRSPSIFT To install CRSPSift inse
104. ser can specify both the item and keyset to 2006 EN 19315 identify the series of interest or use the default preset 2007 9 24008 combination most commonly used C2 2 Jg rri ad O FAVORITES The Keyset Display drop down menu in CCMQuery s mE If you find that you commonly use a certain subset of Output tab affects how a data item s keyset is items at any time you may highlight them and click represented in query output F eyset Display Compustat Expanded Keyset CLASP Mnemonic Eewset Tag S Kevset Number CRSP stock items as well as Compustat data items the Add to Favorites button Favorites is a user created category designed to house these commonly accessed data items It allows storage of Daily and Monthly Compustat Expanded Keuset CCM Query Data Items Navigate Search E Databases Variant Data Item Name Header Loan Loss Recoveries Credit LLRCR S M K i CCM All Available 4 Options include CR nemonic Keyset Tag Keyset and Links Te isnt ten oca T _ 2 XM TEM 4 Loan Loss Written Off Char LLWOCR Numb er and Compustat xp anded Keys et 1 he Balance Sheet Annuz 14 Life Insurance Surrenders an LST Income Statement A I ed Cash Flow Annual 1 Minority Interest Income Acc MII El Settings 1 i Pension Annual ener T following examples illus
105. sets 10 Askhi Maximum in Period Askhi Item ID Variant Data Item Name Header Customized El Parameter Type BaseAmount 100 e daily closing price of a security f unavailable the number in the BaseDate YYYYMMDD price field is replaced with a bid ask verage marked by a leading dash BaseAmount Amount to be reported on base date D actual price on base date Items are divided into logical groups in the Navigate Search area Navigate Search 3 Databases Search By Daily Stock Items E Event Data Daily Stock Stock Identification Stock Header Ranges Search criteria Name History Contains se Distribution History Adjusted Distribution Enter Keyword keyword Delisting History Adjusted Delistings Shares History Raw Shares History Adjusted Shares NASDAQ History Portfolio History Group Inclusion Time Senes Favorites 4 Clicking the name of a group causes its data items to appear in the Search Results area Or rather than browsing by category click the Search tab There you can enter a descriptive keyword e g NASDAQ to find items of interest Browsing and searching both cause the data items found to appear in the Search Results area of the window Search Results fa prc Price End of Period Frc o Adjusted End of Period 0 End of Period Bid Adjusted End of Period Ask End o
106. ta items in an input file INDIVIDUAL ITEMS Select an item from the Available Items list at left and I Jg rri JA 51 SS399V VIVA X3QNI Ad3nDGNI N J Sf dalSdsa dino OUTPUT OPTIONS You can save the output of an IndQuery in one of several formats Click the Output tab to show the Output Options panel Output Options Format Width Output to Screen Default 80 c Output File Name Po Browse OUTPUT TO SCREEN The default option is text based formatted to fit the screen width specified in the Width control 80 characters is the default width Screen output appears in the hopper at the bottom of the IndQuery window Clear Output Save Output EN 1000000 Index Header Indno Indco Primflag Portnum 1000000 1000000 Indname CRSP NYSE Value Weighted Market Index Groupname NYSE Market Capitalization By default the output of the last query execution is shown in the Current tab The Save Output and Clear Output buttons at the top of the output area allow you to save to a text file or to clear it away Clicking the History tab shows you the results of every query execution since the session was opened When you switch to the query history view the buttons above the output area change to Save History and Clear History Save History allows you to capture the entire session You can select a ne
107. the default click the disclosure triangle next to Scan Folder Hint Paths under Scan Options Options v Scan Folder Hint Paths C crspdata C crspdata Sample 3 1 Add Delete A folder hint path serves as a guide telling the Environment Scanner where to look for CRSP databases The default locations are already listed To add your own click the Add button and navigate to the directory where your CRSP databases are installed Then click the Start Scan button to scan again with the new directory included The Environments screen is where you can manage the environments detected by CRSPSift on its initial launch To manage your environments select Options from the Tools menu to open the CRSPSift Options window Then click Environments in the list of options categories on the left A list of all your saved environments appears in the main area of the screen Nickname Description B CRSP 1925 US Stock amp Indices 200908 FIZ200908_CMZ200910 CRSP CCM Xpressfeed Input 200910 ies In 1925 US Stock amp Indices Sample 200812 CRSP CCM Sample Database 200907 No CRSP US Treasuries Sample 200812 Default Sa FIS200812 CMS200907 TRS200812 And at the bottom of the screen are controls for managing them You can edit or delete existing environments as well as create new ones Perform Action Load Environment Load Environment Edit Environment
108. tifiers and descriptors Index Items Daily index values for index capitalization levels returns counts and more B Member Items Security constituent holdings weights float start and end of day values z Index Value Second by second listing of index values SECURITY DATA Index Constituent Data access by security PERMNO Index membership and holdings weights float start and end of day values for each associated with each index in which security is a member Security Header Current identifiers and descriptor items Security Header History Historic record of identifiers and descriptors Security tems Start and end of day prices and capitalizations volumes returns dividends and more Time Series Trade Information Minute by minute prices volumes cumulative volumes and trade counts Constituent Items Accessible through CRSP PERMNO for each index in which the security is a member holdings weights float start and end of day values OUTPUT OPTIONS You can save the output of a RIQuery in one of several formats Click the Output tab to show the Output Options panel OUTPUT TO SCREEN The default option is text based output formatted to fit the screen width specified in the Width control 80 C2 J gt OU rri E c rri ad E rri e rri ad C2 rri gt lt c gt of gt J gt C2 C2 rri e e X J Sh FalsSdsd
109. tock amp Index CRSP Compustat Merged US Treasury Mutual Fund and new Research Index databases In addition to an updated interface CRSPSift offers many features designed to make accessing CRSP databases easier Some of these features include Data Environments Create a data environment for each set of data you use To use a different data set simply select its environment from a menu and rerun your query Multiple Queries Edit and run up to ten queries simultaneously Direct Edit Type or paste an existing request file from a command line utility into a window and then save it as a CRSPSift query CRSPSift automatically updates its graphical interface to reflect your query options Output Layout Preview TsQuery features an animated report preview that suggests how your output will look This saves you the time of running a query multiple times to find the right format Company Selection A company lookup utility Search and sort results by Company Name PERMNO ticker symbol or other identifier Filtering Options A point and click interface to simplify filtering query results by exchange share type NASDAQ Market Tiers portfolio assignments SIC Code or group membership CRSPSIFT UTILITIES TsQuery is a tool for extracting time series data based in fixed and relative ranges for the CRSP security universe CRSP Indexes and CRSP Compustat linked companies for Compustat subscribers StkQuery i
110. trate these options using Ober Avus ff enema Serves Reverve WSR Index Per Desc Ant 1 Net Income Loss NI E Quarterly Data Net Income Adjusted For Com NIADJ Year to Date Data Micros oft xcel output a E Data 11 Net Income Effect Capitalized NIECI Security Data FH Company Market Data Audit Data P P A T Company Ratings Query Items CRSP Mnemonic Keyset Tag Mnemo nic Ke V set I j n Variant Data Item Name E Global Options cogs 1 Cost of Goods Sold Currency DataCodes No FootNotes No ni Net Income Loss Refer to your Compustat sale 1 Sales Turnover Net jocumentation for item definitions SS399V 3SVaviva q3933IN 4 09 4 849 AUINDINII Output represents the item s keyset using a mnemonic tag assigned by CRSP Currency The currency used in the ouput For certain data items KYGVKEY KEYSET_TAG 1690 STD 2006 19315 To control the order in which your report will display 1690 STD 2007 9 24006 data items use the up and down arrows to the right of the Query Items list Click an item and then click the Keyset Number up or down arrow to reorganize the list To remove an item or group of items select them from the list and 1111 3st lalsdsdJ rm Output represents the item s keyset using a keyset number assigned by CRSP click the X button located above the arrow buttons Select Global Item Options to include Footnotes or KYGVKEY KEYSET Data Code ite
111. trategic Balanced Fundfi Select button The key of that entity will appear in the identifier field AARP Growth Trust AARP Balanced Stock ered Bond Fured ARAP Growth Trifislanoed Shock and Bond Fund CRSPSIFT USER GUIDE PAGE 50 You may also choose to access all issues in the database Items are divided into logical groups in the Navigate or issues you specify in an input file To search all Search area e F S issues click All Issues in the Mutual Fund Selection MutQuery Data Items MutQuery Data Items area To use an input file create a plain text file with Br U f f Navigate Search rm an entry for each issue one per row using the active ad S 3 Mutual Fund Items cO key of your choice Select the key type that from the Master Items D Fund Haader Active Key menu that corresponds to that used in your fa cera History contains c i Fi E input file and click Input on File Click the Browse Shee Enter Keyword e Les c button to locate the file you created A Dividends Summary Annual DATA ITEMS PIRE CERES Clicking the name of a group causes its data items to ep amem appear in the Search Results area Or rather than ON browsing by category click the Search tab There you a Pe can enter a descriptive keyword e g fee to find Data can be selected as either individual items going Ei
112. tton to locate the input file you created DATA ITEMS AND GROUPS Data Items Select Edit Data Items Import Data ltem File Export To File Selected Data Items term ID Keyset Itern Name Compustat data may be accessed either by selecting and combining individual data items in a query or by selecting pre defined groups of data items DATA ITEMS Click on Select Edit Data Items to open a screen that offers two categories Compustat data items and Favorites CCM Query Data Items E Navigate Databases CCM All Available Favorites Expand the Compustat data items category CCM AII Available to see categories of Compustat data items Select Subgroups within the categories and view items within each of the subgroups When you select a subgroup a list of its items appears in the Search Results area Further details about the selected item are shown in the area to the right of the Search Results Highlight items to be added to your query and click the Add to Query button Added items appear in the Query Items section of the screen KEYSETS Keysets are CRSP created groups of Compustat secondary keys that are used to further define Compustat data items CRSP has identified a default keyset for each data item Sift allows you to override the default keysets or select multiple keysets in Sift Search El Dataltem Variant Data Item Name Header
113. u specified FREQUENCY With the date range in place define the frequency of dates your query will access In the Frequency area select a frequency from the list at the left If your query will use only individual dates with no regular interval select None You can then select those dates in the Custom Dates area below the Frequency area If your query s calendar will have some regular frequency select one of the other options Daily Weekly Monthly or Yearly When you select one controls appear to the right allowing you to customize the frequency Customization options are as follows Frequency None Every 3 days s 9 Daily ndar e g every day O Weekly O Monthly O Yearly Weekly Frequency None Recur every i week s on Daily Sunday Wednesday Saturday D Weekly Monday Thursday CO Monthly Tuesday wi Friday CO Yearly Monthly A monthly frequency provides two options One is to select the frequency of months and specify which day of the month to include for instance the tenth day of every other month CRSPSIFT USER GUIDE PAGE 55 C2 J gt Jg rri ze e e pe e The other also allows you specify the frequency of months the type of day to include e g Weekday Weekend Day or Thursday and when that day occurs in the month e g Third or Last For example a calendar might specify the second weekend day of every month or t
114. uld appear Dataltems Data Groups Header Identification No Header Data TTC 1 eY AS mama mmm _ baia mms mda unm is cmm ee Header Identification Informatian Header Data with Date Ranges in YYYMMDD format Header Data with Date Index Values as Date Ranges Supplementary Header Identification Information DATA ITEMS Use the Data Items tab to control what individual event and time series data items your query will use SELECT EDIT DATA ITEMS Clicking this button launches the interface for selecting individual event and time series items in StkQuery The StkQuery Data Items window is divided into several areas El Data Item ItemID prc E Databases n Daily Stock Items Y Name Price End of Period Event Data Stock Identification adipe 0 Price Adjusted End of Period Adipre El Data Type 0 Bid End of Period DataLength Name History DataType Distribution History adjbid 0 Bid Adjusted End of Period ibi ata I Adjusted Distribution Fixedwidth 12 5 Delisting History 0 Ask End of Period El Settings rimi eR Ask Adjusted End of Period Adjask Alignment Right Align Raw Shares History bido Bidlo Minimum in Period Bidlo Header Prc Adjusted Shares ItemLag NASDAQ History jbi Bidlo Adjusted Minimum in Period Adibidlo Portfolio History Group Inclusion Time Series Favorites NumKey
115. utton Click this button at any time during the process to halt the scan r SS i aa Scanning C crspdata _crsplip e o By The time it takes for a scan to complete depends on the number of directories scanned NEW ENVIRONMENTS FOUND When the Environment Scanner completes its search any new valid environments it discovered are displayed in the New Environments Found table at the bottom of the screen Each environment displays its attributes such as its default nickname full dataset name and the folder paths of the databases it includes New Envaonments Found ee O He 200705 Mo Fs 200712 CRSP 1325 US Stock indices Sample Capa ampla Minis De Sum esce H C 00 rm zs 0 7 To add a discovered environments to CRSPSift click the Create checkbox in its row If you would like to modify the environment s settings before adding it click the Edit button See the discussion of CRSPSift Environments for details on how environment editing options When you are satisfied with the environment click the Add button The new environment will now be available for use by CRSPSift query tools THE ENVIRONMENT SCAN LOG To see a log of the databases found by the Environment Scanner click the View Log button in the Scan Options area The Environment Scan Log maintains a list of the databases discovered in each run of the Environment Scanner The log of the most rec
116. w index or different data or options related to the same index Each time you click Execute the new output is saved in the history Then by clicking Save History you can save all the output to one text file OUTPUT TO FILE You can also save data in a number of external file formats When you choose a non screen output format the Output File Name field becomes active In order to successfully execute the query you must select a location to store the output file Click the Browse button to select a location to save the file Output Options Format Excel 2007 Workbook xlsx v Output File Name C Documents and Settings nicole My Docurr Browse Supported File Formats Pipe Delimited For Import pipe deliimited ASCII text suitable for importing into databases or software packages not directly supported by CRSPSift This option generates a single output file Excel 97 2003 Workbook xls Excel version 8 Note Excel version 8 has a row limit of 65536 total rows If a query generates more than that number of rows the output will be truncated at the row limit Excel 2007 Workbook xlsx Excel XML e MAT iles mat Matlab version 7 SAS Dataset sas8bdat SAS version 7 Stata File dta SPSS File sav Viewing Output Files If you select a non screen output format IndQuery will generate one or more output files one for each unique output of the query
117. we ui CRSP Center for Research in Security Prices CRSPSift USER GUIDE Updated June 15 2012 CHICAGO d 8 CRSP Genter for Research in Security Prices 105 West Adams Suite 1700 Chicago 60603 Tel 312 263 6400 Fax 312 263 6430 Email Support crsp ChicagoBooth edu CONTENTS CHAPTER 1 INTRODUCTION 1 CHAPTER 2 INSTALLATION AND SETUP uu u u u a pes nema le 00 0000000000000000000000000000000000000000000000000000000000000000000000000000 OOO OOD LAUNCHING CRSPSIFT FOR THE FIRST TIME 3 CHAPTER S TSOUFEY TIME SERIES AA ESO qa uuu errant nen LAYOU T 02222 16 EXECUTING A QUERY 18 CHAPTER 4 STKQUERY STOCK DATA ACCESS asss a LO SECURITY SELECTION 19 HEADER IDENTIFICATION 20
118. xt file Export to File Use this option to output the Selected Data Items list to a text file containing each item s mnemonic itemid This text file may then be imported into other queries using the Import Data Item File button DATA GROUPS The Data Groups tab allows you to select related event and time series data items Adjustment parameters applied to data groups are automatically applied to applicable items in the group This saves you the time of selecting and adjusting items individually RIMS Q Execute 4 Cancel X Close JE View Output Ch Ka 7 gf Verity O Execute d oe Close View Output 15 End Date Time Edt Reset 3730 2012 l x Input Output Data ltems Data Groups Research Index Selection Groupe Data Girou Gro alingi Choose Key Type B Data Group Se PERMNO S Enter Key Value L Index Data 12490 Find p gt Data Ez input File a ity Data Security Header Security Header Hist OA Security Data Secury ems _ TS Trade Info C2 gt gt J rri ze e c rri E E rri e rri Jem ad C2 lt rri gt lt gt gt J gt C2 C2 rri e e Available groups include A J 2 INDEX DATA Index Header Current identifier and descriptor items T Index Header History Historic record of iden
119. y Then by clicking Save History you can save all the output to one text file OUTPUT TO FILE You can also save query output in a number of external file formats When you choose a non screen output format the Output File Name field becomes active In order to successfully execute the query you must select a location to store the output file Click the Browse button to select a location to save the file Output Optons Format Excel 2007 Workbook xlsx w Output File Name CACRSPSimtioutput xlsx Browse Keyset Display CRSP Mnemonic Keyset Tag SUPPORTED FILE FORMATS Pipe Delimited For Import pipe deliimited ASCII text suitable for importing into databases or software packages not directly supported by CRSPSift This option generates a single output file Excel 97 2003 Workbook xls Excel version 8 Click Open to open the output file in the appropriate NOTE Excel version 8 has a row limit of 65536 application Excel in the example shown below e total rows If a query generates more than that EIUS TRECE TOWSGENUCX number of rows the output will be truncated at the B C v KYTREASNOIKYCRSPID MONTHLY TMPCYLD TMPDINT zm row limit 2 20410719260615 303250 29 May 2009 0 3 204107 19260615 303250 30 Jun 2009 0 E E F Excel 2007 Workbook xlsx Excel XML 204107 13260615 303250 31 Jul 2009 0 n 14 4 0 ieoj21iewgs yieldm 3 ZT
120. y you can click the View Output button 6 12490 3 30 123 433PM 3 42 56PM 20881 300 334027 G 12490 3 30 1234 PM 3 4356PM 20841 100 336320 located in the CRSPSift toolbar to view the output files T 12490 3 30 123 45 PM 3 14 48 PM 208 8 200 335611 38 I 8 12490 3 30 123 416 PM 3 45 48 PM 208 77 147 347827 20081 generated by your query E 12490 3 30 123 47PM 46 39PM 208 79 100 354847 321 3 30 12 3 48 3 37 46 PM CC Ifa query generated only one file the target application will open the file directly as shown above If the query resulted in multiple output files however instead of opening the files CRSPSift will open a Windows Explorer window showing the folder where you chose to save output CRSPSifI a Fie Edk View Favorites Tools Help O O sehe redes E Address IE lt CRSPsat CY My Documents A ri dex TKB Microsoft Office Sf2 2012 1 53 PM 3 8 My Computer amp kB Microsoft Office Exc 5 2 2012 1 53 wh 3 Floppy A Kiri rs xdex GKE Microsoft Office Exc Sf2 2012 1 53 PM OUTPUT FILE SUFFIXES The following table lists output file suffixes and the type of data they indicate is contained in a file DATA TYPE SUFFIX Index Header Index Header History Index Daily Items Index Values second by second Index Member Daily Items access by I
121. y the settings and then click Add Values that appear in bold type are editable Changing default settings affects the item report formatting Data Length CRSP recommends keeping the Data Length field consistent with the Fixed Width so that the item is not truncated Data Length is based on the values in the underlying data items Data Type CRSP recommends that Data Type not be edited What appears in the field is what CRSP has identified to be appropriate for the underlying data item Fixed Width The allotted number of characters that an item will be given in the output file Fixed Width must be greater than or equal to the Data Length for the requested item Note that expanding Fixed Width does not increase a data item s numeric precision Header If column headers are used more descriptive names may replace the default itemids Item Lag Used to either lead or lag a data item A positive number of periods will lag che data by the number of periods designated in the output calendar while a negative number will lead the data item For example given a December 2006 date in an output file prices requested with a two month lag will return the October 2006 values and prices requested with a two month lead will return prices for February 2007 INDNO SUBNO Data items related to an index or portfolio require more specific information before they can be added to e the item hopper a particular INDNO valu
122. ype it directly in the text field Another option is to browse the database sequentially Use the arrow buttons to the right of the Find button to step through the database in the order of the key type you have chosen Finally the Find button opens the Treasuries Search window where you can use a search form to locate a particular treasury in the CRSP database USING THE TREASURIES SEARCH WINDOW Search CRSP US Treasuries Search By Search Criteria Enter Keyword or Number TREASNO v Contains 200 1013 Company s matched your search criteria TreasuryID CRSPID CUSIP Name COUPRT DDATE MATDT EOD 200002 19610622 800000 0 T A BILL 0 19601221 19610622 1 200003 19610623 400000 0xx BILL 0 19601222 19610625 1 200004 19610629 400000 02 BILL 19601228 19610629 1 200005 19610706 400000 Oxx BILL 0 19610104 19610706 1 200006 19610713 400000 025 BILL 19610111 19610713 1 200007 19610715 400000 022 BILL 19600715 19610715 1 200008 19610720 400000 02 BILL 0 19610118 119610720 1 200009 19610727 400000 0x BILL 0 19610125 19610727 1 200010 19610801 303120 052 CTF 3 125 19600815 19610801 1 200011 19610801 904000 DxX NOTE 4 19570801 19610801 1 200012 19610803 400000 BILL 0 19610201 19610805 1 200013 19610810 400000 DxX BILL 0 19610208 19610810 1 200014 19610817 400000 02 BILL 19610215 19610817 1 gt v 5nnnic 1ne1no24 Annnna lavy eni n 10e1ao25 lincinona 14 If you have sel

CRSPSift USER GUIDE

Contents

Download Pdf Manuals

Related Search

Related Contents