QSTK.qstkutil.DataAccess

26 OPEN="open" 27 HIGH="high" 28 LOW="low" 29 CLOSE="close" 30 VOL="volume" 31 VOLUME="volume" 32 ACTUAL_CLOSE="actual_close" 33 ''' Compustat label list pulled from _analyze() in compustat_csv_to_pkl.py ''' 34 COMPUSTAT = ['gvkey', 'fyearq', 'fqtr', 'fyr', 'ACCTSTDQ', 'ADRRQ', 'AJEXQ', 'AJPQ', 'CURRTRQ', 'CURUSCNQ', 'PDQ', 'PDSA', 'PDYTD', 'SCFQ', 'SRCQ', 'UPDQ', 'ACCDQ', 'ACCHGQ', 'ACCOQ', 'ACOMINCQ', 'ACOQ', 'ACOXQ', 'ACTQ', 'ADPACQ', 'ALTOQ', 'AMQ', 'ANCQ', 'ANOQ', 'AOCIDERGLQ', 'AOCIOTHERQ', 'AOCIPENQ', 'AOCISECGLQ', 'AOL2Q', 'AOQ', 'AOTQ', 'APOQ', 'APQ', 'AQAQ', 'AQDQ', 'AQEPSQ', 'AQPL1Q', 'AQPQ', 'ARCED12', 'ARCEDQ', 'ARCEEPS12', 'ARCEEPSQ', 'ARCEQ', 'ARTFSQ', 'ATQ', 'AUL3Q', 'AUTXRQ', 'BCEFQ', 'BCTQ', 'BDIQ', 'CAPCSTQ', 'CAPR1Q', 'CAPR2Q', 'CAPR3Q', 'CAPRTQ', 'CAPSQ', 'CAQ', 'CEQQ', 'CFBDQ', 'CFEREQ', 'CFOQ', 'CFPDOQ', 'CHEQ', 'CHQ', 'CHSQ', 'CIBEGNIQ', 'CICURRQ', 'CIDERGLQ', 'CIMIIQ', 'CIOTHERQ', 'CIPENQ', 'CIQ', 'CISECGLQ', 'CITOTALQ', 'CLTQ', 'COGSQ', 'CSH12Q', 'CSHFDQ', 'CSHIQ', 'CSHOPQ', 'CSHOQ', 'CSHPRQ', 'CSTKEQ', 'CSTKQ', 'DCOMQ', 'DFPACQ', 'DFXAQ', 'DILADQ', 'DILAVQ', 'DITQ', 'DLCQ', 'DLTTQ', 'DOQ', 'DPACREQ', 'DPACTQ', 'DPQ', 'DPRETQ', 'DPTBQ', 'DPTCQ', 'DRCQ', 'DRLTQ', 'DTEAQ', 'DTEDQ', 'DTEEPSQ', 'DTEPQ', 'DVPDPQ', 'DVPQ', 'DVRREQ', 'DVTQ', 'EPSF12', 'EPSFIQ', 'EPSFXQ', 'EPSPIQ', 'EPSPXQ', 'EPSX12', 'EQRTQ', 'EROQ', 'ESOPCTQ', 'ESOPNRQ', 'ESOPRQ', 'ESOPTQ', 'ESUBQ', 'FCAQ', 'FEAQ', 'FELQ', 'FFOQ', 'GDWLAMQ', 'GDWLIA12', 'GDWLIAQ', 'GDWLID12', 'GDWLIDQ', 'GDWLIEPS12', 'GDWLIEPSQ', 'GDWLIPQ', 'GDWLQ', 'GLAQ', 'GLCEA12', 'GLCEAQ', 'GLCED12', 'GLCEDQ', 'GLCEEPS12', 'GLCEEPSQ', 'GLCEPQ', 'GLDQ', 'GLEPSQ', 'GLPQ', 'GPQ', 'HEDGEGLQ', 'IATIQ', 'IBADJ12', 'IBADJQ', 'IBCOMQ', 'IBKIQ', 'IBMIIQ', 'IBQ', 'ICAPTQ', 'IDITQ', 'IIREQ', 'IITQ', 'INTACCQ', 'INTANOQ', 'INTANQ', 'INTCQ', 'INVFGQ', 'INVOQ', 'INVRMQ', 'INVTQ', 'INVWIPQ', 'IOBDQ', 'IOIQ', 'IOREQ', 'IPQ', 'IPTIQ', 'ISGTQ', 'ISTQ', 'IVAEQQ', 'IVAOQ', 'IVIQ', 'IVLTQ', 'IVPTQ', 'IVSTQ', 'IVTFSQ', 'LCABGQ', 'LCACUQ', 'LCOQ', 'LCOXQ', 'LCTQ', 'LLTQ', 'LNOQ', 'LOL2Q', 'LOQ', 'LOXDRQ', 'LQPL1Q', 'LSEQ', 'LSQ', 'LTMIBQ', 'LTQ', 'LUL3Q', 'MIBNQ', 'MIBQ', 'MIBTQ', 'MIIQ', 'MSAQ', 'MTLQ', 'NCOQ', 'NIITQ', 'NIMQ', 'NIQ', 'NITQ', 'NOPIOQ', 'NOPIQ', 'NPATQ', 'NRTXTDQ', 'NRTXTEPSQ', 'NRTXTQ', 'OEPF12', 'OEPS12', 'OEPSXQ', 'OIADPQ', 'OIBDPQ', 'OPEPSQ', 'OPROQ', 'OPTDRQ', 'OPTFVGRQ', 'OPTLIFEQ', 'OPTRFRQ', 'OPTVOLQ', 'PCLQ', 'PIQ', 'PLLQ', 'PNC12', 'PNCD12', 'PNCDQ', 'PNCEPS12', 'PNCEPSQ', 'PNCIAPQ', 'PNCIAQ', 'PNCIDPQ', 'PNCIDQ', 'PNCIEPSPQ', 'PNCIEPSQ', 'PNCIPPQ', 'PNCIPQ', 'PNCPD12', 'PNCPDQ', 'PNCPEPS12', 'PNCPEPSQ', 'PNCPQ', 'PNCQ', 'PNCWIAPQ', 'PNCWIAQ', 'PNCWIDPQ', 'PNCWIDQ', 'PNCWIEPQ', 'PNCWIEPSQ', 'PNCWIPPQ', 'PNCWIPQ', 'PNRSHOQ', 'PPEGTQ', 'PPENTQ', 'PRCAQ', 'PRCD12', 'PRCDQ', 'PRCE12', 'PRCEPS12', 'PRCEPSQ', 'PRCPD12', 'PRCPDQ', 'PRCPEPS12', 'PRCPEPSQ', 'PRCPQ', 'PRCQ', 'PRCRAQ', 'PRSHOQ', 'PSTKNQ', 'PSTKQ', 'PSTKRQ', 'PTRANQ', 'PVOQ', 'PVTQ', 'RATIQ', 'RAWMSMQ', 'RCAQ', 'RCDQ', 'RCEPSQ', 'RCPQ', 'RDIPAQ', 'RDIPDQ', 'RDIPEPSQ', 'RDIPQ', 'RECCOQ', 'RECDQ', 'RECTAQ', 'RECTOQ', 'RECTQ', 'RECTRQ', 'RECUBQ', 'REITQ', 'REQ', 'RETQ', 'REUNAQ', 'REVTQ', 'RISQ', 'RLLQ', 'RLTQ', 'RRA12', 'RRAQ', 'RRD12', 'RRDQ', 'RREPS12', 'RREPSQ', 'RRPQ', 'RVLRVQ', 'RVTIQ', 'RVUTXQ', 'SAAQ', 'SALEQ', 'SALQ', 'SBDCQ', 'SCOQ', 'SCQ', 'SCTQ', 'SEQOQ', 'SEQQ', 'SETA12', 'SETAQ', 'SETD12', 'SETDQ', 'SETEPS12', 'SETEPSQ', 'SETPQ', 'SPCE12', 'SPCED12', 'SPCEDPQ', 'SPCEDQ', 'SPCEEPS12', 'SPCEEPSP12', 'SPCEEPSPQ', 'SPCEEPSQ', 'SPCEP12', 'SPCEPD12', 'SPCEPQ', 'SPCEQ', 'SPIDQ', 'SPIEPSQ', 'SPIOAQ', 'SPIOPQ', 'SPIQ', 'SRETQ', 'SSNPQ', 'STKCHQ', 'STKCOQ', 'STKCPAQ', 'TDSGQ', 'TDSTQ', 'TEQQ', 'TFVAQ', 'TFVCEQ', 'TFVLQ', 'TIEQ', 'TIIQ', 'TRANSAQ', 'TSTKNQ', 'TSTKQ', 'TXDBAQ', 'TXDBQ', 'TXDIQ', 'TXDITCQ', 'TXPQ', 'TXTQ', 'TXWQ', 'UACOQ', 'UAOQ', 'UAPTQ', 'UCAPSQ', 'UCCONSQ', 'UCEQQ', 'UDDQ', 'UDMBQ', 'UDOLTQ', 'UDPCOQ', 'UDVPQ', 'UGIQ', 'UINVQ', 'ULCOQ', 'UNIAMIQ', 'UNNPQ', 'UNOPINCQ', 'UOPIQ', 'UPDVPQ', 'UPMCSTKQ', 'UPMPFQ', 'UPMPFSQ', 'UPMSUBPQ', 'UPSTKCQ', 'UPSTKQ', 'URECTQ', 'USPIQ', 'USUBDVPQ', 'USUBPCVQ', 'UTEMQ', 'WCAPQ', 'WDAQ', 'WDDQ', 'WDEPSQ', 'WDPQ', 'XAGTQ', 'XBDTQ', 'XCOMIQ', 'XCOMQ', 'XDVREQ', 'XIDOQ', 'XINTQ', 'XIOQ', 'XIQ', 'XIVIQ', 'XIVREQ', 'XOBDQ', 'XOIQ', 'XOPROQ', 'XOPRQ', 'XOPT12', 'XOPTD12', 'XOPTD12P', 'XOPTDQ', 'XOPTDQP', 'XOPTEPS12', 'XOPTEPSP12', 'XOPTEPSQ', 'XOPTEPSQP', 'XOPTQ', 'XOPTQP', 'XOREQ', 'XPPQ', 'XRDQ', 'XRETQ', 'XSGAQ', 'XSQ', 'XSTOQ', 'XSTQ', 'XTQ', 'ACCHGY', 'ACCLIY', 'ACQDISNY', 'ACQDISOY', 'ADPACY', 'AFUDCCY', 'AFUDCIY', 'AMCY', 'AMY', 'AOLOCHY', 'APALCHY', 'APCHY', 'AQAY', 'AQCY', 'AQDY', 'AQEPSY', 'AQPY', 'ARCEDY', 'ARCEEPSY', 'ARCEY', 'ASDISY', 'ASINVY', 'ATOCHY', 'AUTXRY', 'BCEFY', 'BCTY', 'BDIY', 'CAPCSTY', 'CAPFLY', 'CAPXFIY', 'CAPXY', 'CDVCY', 'CFBDY', 'CFEREY', 'CFLAOTHY', 'CFOY', 'CFPDOY', 'CHECHY', 'CHENFDY', 'CIBEGNIY', 'CICURRY', 'CIDERGLY', 'CIMIIY', 'CIOTHERY', 'CIPENY', 'CISECGLY', 'CITOTALY', 'CIY', 'COGSY', 'CSHFDY', 'CSHPRY', 'CSTKEY', 'DCSFDY', 'DCUFDY', 'DEPCY', 'DFXAY', 'DILADY', 'DILAVY', 'DISPOCHY', 'DITY', 'DLCCHY', 'DLTISY', 'DLTRY', 'DOCY', 'DOY', 'DPCY', 'DPRETY', 'DPY', 'DTEAY', 'DTEDY', 'DTEEPSY', 'DTEPY', 'DVPDPY', 'DVPY', 'DVRECY', 'DVRREY', 'DVTY', 'DVY', 'EIEACY', 'EPSFIY', 'EPSFXY', 'EPSPIY', 'EPSPXY', 'EQDIVPY', 'ESUBCY', 'ESUBY', 'EXRESY', 'EXREUY', 'EXREY', 'FCAY', 'FFOY', 'FIAOY', 'FINCFY', 'FININCY', 'FINLEY', 'FINREY', 'FINVAOY', 'FOPOXY', 'FOPOY', 'FOPTY', 'FSRCOPOY', 'FSRCOPTY', 'FSRCOY', 'FSRCTY', 'FUSEOY', 'FUSETY', 'GDWLAMY', 'GDWLIAY', 'GDWLIDY', 'GDWLIEPSY', 'GDWLIPY', 'GLAY', 'GLCEAY', 'GLCEDY', 'GLCEEPSY', 'GLCEPY', 'GLDY', 'GLEPSY', 'GLPY', 'GPY', 'HEDGEGLY', 'IBADJY', 'IBCOMY', 'IBCY', 'IBKIY', 'IBMIIY', 'IBY', 'IDITY', 'IIREY', 'IITY', 'INTANDY', 'INTANPY', 'INTCY', 'INTFACTY', 'INTFLY', 'INTIACTY', 'INTOACTY', 'INTPDY', 'INTPNY', 'INTRCY', 'INVCHY', 'INVDSPY', 'INVSVCY', 'IOBDY', 'IOIY', 'IOREY', 'IPTIY', 'ISGTY', 'ITCCY', 'IVACOY', 'IVCHY', 'IVIY', 'IVNCFY', 'IVSTCHY', 'LIQRESNY', 'LIQRESOY', 'LNDEPY', 'LNINCY', 'LNMDY', 'LNREPY', 'LTDCHY', 'LTDLCHY', 'LTLOY', 'MICY', 'MIIY', 'MISEQY', 'NCFLIQY', 'NCOY', 'NEQMIY', 'NIITY', 'NIMY', 'NITY', 'NIY', 'NOASUBY', 'NOPIOY', 'NOPIY', 'NRTXTDY', 'NRTXTEPSY', 'NRTXTY', 'OANCFCY', 'OANCFDY', 'OANCFY', 'OEPSXY', 'OIADPY', 'OIBDPY', 'OPEPSY', 'OPPRFTY', 'OPROY', 'OPTDRY', 'OPTFVGRY', 'OPTLIFEY', 'OPTRFRY', 'OPTVOLY', 'PCLY', 'PDVCY', 'PIY', 'PLIACHY', 'PLLY', 'PNCDY', 'PNCEPSY', 'PNCIAPY', 'PNCIAY', 'PNCIDPY', 'PNCIDY', 'PNCIEPSPY', 'PNCIEPSY', 'PNCIPPY', 'PNCIPY', 'PNCPDY', 'PNCPEPSY', 'PNCPY', 'PNCWIAPY', 'PNCWIAY', 'PNCWIDPY', 'PNCWIDY', 'PNCWIEPSY', 'PNCWIEPY', 'PNCWIPPY', 'PNCWIPY', 'PNCY', 'PRCAY', 'PRCDY', 'PRCEPSY', 'PRCPDY', 'PRCPEPSY', 'PRCPY', 'PROSAIY', 'PRSTKCCY', 'PRSTKCY', 'PRSTKPCY', 'PRVY', 'PSFIXY', 'PTRANY', 'PURTSHRY', 'PVOY', 'RAWMSMY', 'RCAY', 'RCDY', 'RCEPSY', 'RCPY', 'RDIPAY', 'RDIPDY', 'RDIPEPSY', 'RDIPY', 'RECCHY', 'REITY', 'REVTY', 'RISY', 'RRAY', 'RRDY', 'RREPSY', 'RRPY', 'RVY', 'SALEY', 'SCSTKCY', 'SETAY', 'SETDY', 'SETEPSY', 'SETPY', 'SHRCAPY', 'SIVY', 'SPCEDPY', 'SPCEDY', 'SPCEEPSPY', 'SPCEEPSY', 'SPCEPY', 'SPCEY', 'SPIDY', 'SPIEPSY', 'SPIOAY', 'SPIOPY', 'SPIY', 'SPPCHY', 'SPPEY', 'SPPIVY', 'SPSTKCY', 'SRETY', 'SSTKY', 'STFIXAY', 'STINVY', 'STKCHY', 'STKCOY', 'STKCPAY', 'SUBDISY', 'SUBPURY', 'TDCY', 'TDSGY', 'TFVCEY', 'TIEY', 'TIIY', 'TSAFCY', 'TXACHY', 'TXBCOFY', 'TXBCOY', 'TXDCY', 'TXDIY', 'TXOPY', 'TXPDY', 'TXTY', 'TXWY', 'TXY', 'UAOLOCHY', 'UDFCCY', 'UDVPY', 'UFRETSDY', 'UGIY', 'UNIAMIY', 'UNOPINCY', 'UNWCCY', 'UOISY', 'UPDVPY', 'UPTACY', 'USPIY', 'USTDNCY', 'USUBDVPY', 'UTFDOCY', 'UTFOSCY', 'UTMEY', 'UWKCAPCY', 'WCAPCHCY', 'WCAPCHY', 'WCAPCY', 'WCAPOPCY', 'WCAPSAY', 'WCAPSUY', 'WCAPSY', 'WCAPTY', 'WCAPUY', 'WDAY', 'WDDY', 'WDEPSY', 'WDPY', 'XAGTY', 'XBDTY', 'XCOMIY', 'XCOMY', 'XDVREY', 'XIDOCY', 'XIDOY', 'XINTY', 'XIOY', 'XIVIY', 'XIVREY', 'XIY', 'XOBDY', 'XOIY', 'XOPROY', 'XOPRY', 'XOPTDQPY', 'XOPTDY', 'XOPTEPSQPY', 'XOPTEPSY', 'XOPTQPY', 'XOPTY', 'XOREY', 'XRDY', 'XRETY', 'XSGAY', 'XSTOY', 'XSTY', 'XSY', 'XTY', 'DLRSN', 'FYRC', 'GGROUP', 'GIND', 'GSECTOR', 'GSUBIND', 'NAICS', 'PRIUSA', 'SIC', 'SPCINDCD', 'SPCSECCD', 'STKO']

45 ''' 46 @summary: This class is used to access all the symbol data. It readin in pickled numpy arrays converts them into appropriate pandas objects 47 and returns that object. The {main} function currently demonstrates use. 48 @note: The earliest time for which this works is platform dependent because the python date functionality is platform dependent. 49 '''

50 - def __init__(self, sourcein = DataSource.NORGATE):

51 ''' 52 @param sourcestr: Specifies the source of the data. Initializes paths based on source. 53 @note: No data is actually read in the constructor. Only paths for the source are initialized 54 ''' 55 56 self.folderList = list() 57 self.folderSubList = list() 58 self.fileExtensionToRemove=".pkl" 59 60 try: 61 self.rootdir = os.environ['QSDATA'] 62 except KeyError: 63 #self.rootdir = "/hzr71/research/QSData" 64 raise KeyError("Please be sure to set the value for QSDATA in config.sh or local.sh") 65 66 if (sourcein == DataSource.NORGATE)|(sourcein == DataSource.NORGATElc) : 67 68 self.source = DataSource.NORGATE 69 self.midPath = "/Processed/Norgate/Stocks/" 70 71 self.folderSubList.append("/US/AMEX/") 72 self.folderSubList.append("/US/NASDAQ/") 73 self.folderSubList.append("/US/NYSE/") 74 self.folderSubList.append("/US/NYSE Arca/") 75 self.folderSubList.append("/US/OTC/") 76 self.folderSubList.append("/US/Delisted Securities/") 77 self.folderSubList.append("/US/Indices/") 78 79 for i in self.folderSubList: 80 self.folderList.append(self.rootdir+self.midPath+i) 81 82 #if ends 83 elif (sourcein == DataSource.YAHOO): 84 self.source= DataSource.YAHOO 85 self.midPath= "/Processed/Yahoo" 86 #What if these paths don't exist? 87 self.folderSubList.append ("/US/NASDAQ/") 88 self.folderSubList.append ("/US/NYSE/") 89 self.folderSubList.append ("/US/AMEX/") 90 91 for i in self.folderSubList: 92 self.folderList.append(self.rootdir+self.midPath+i) 93 94 95 #if DataSource.YAHOO ends 96 elif (sourcein == DataSource.CUSTOM) : 97 self.source = DataSource.CUSTOM 98 self.folderList.append(self.rootdir+"/Processed/Custom/") 99 elif (sourcein == DataSource.COMPUSTAT): 100 self.source= DataSource.COMPUSTAT 101 self.midPath= "/Processed/Compustat" 102 #What if these paths don't exist? 103 self.folderSubList.append ("/US/NASDAQ/") 104 self.folderSubList.append ("/US/NYSE/") 105 self.folderSubList.append ("/US/AMEX/") 106 107 for i in self.folderSubList: 108 self.folderList.append(self.rootdir+self.midPath+i) 109 110 111 #if DataSource.Compustat ends 112 else: 113 raise ValueError("Incorrect data source requested.")

114 115 #__init__ ends 116

117 - def get_data_hardread(self, ts_list, symbol_list, data_item, verbose=False, bIncDelist=False):

118 ''' 119 Read data into a DataFrame no matter what. 120 @param ts_list: List of timestamps for which the data values are needed. Timestamps must be sorted. 121 @param symbol_list: The list of symbols for which the data values are needed 122 @param data_item: The data_item needed. Like open, close, volume etc. May be a list, in which case a list of DataFrame is returned. 123 @param bIncDelist: If true, delisted securities will be included. 124 @note: If a symbol is not found then a message is printed. All the values in the column for that stock will be NaN. Execution then 125 continues as usual. No errors are raised at the moment. 126 ''' 127 128 ''' Now support lists of items, still support old string behaviour ''' 129 bStr = False 130 if( isinstance( data_item, str) ): 131 data_item = [data_item] 132 bStr = True 133 134 135 # init data struct - list of arrays, each member is an array corresponding do a different data type 136 # arrays contain n rows for the timestamps and m columns for each stock 137 all_stocks_data = [] 138 for i in range( len(data_item) ): 139 all_stocks_data.append( np.zeros ((len(ts_list), len(symbol_list))) ); 140 all_stocks_data[i][:][:] = np.NAN 141 142 list_index= [] 143 144 ''' For each item in the list, add to list_index (later used to delete non-used items) ''' 145 for sItem in data_item: 146 if( self.source == DataSource.CUSTOM ) : 147 ''' If custom just load what you can ''' 148 if (sItem == DataItem.CLOSE): 149 list_index.append(1) 150 elif (sItem == DataItem.ACTUAL_CLOSE): 151 list_index.append(2) 152 if( self.source == DataSource.COMPUSTAT ): 153 ''' If compustat, look through list of features ''' 154 for i, sLabel in enumerate(DataItem.COMPUSTAT): 155 if sItem == sLabel: 156 ''' First item is date index, labels start at 1 index ''' 157 list_index.append(i+1) 158 break 159 else: 160 raise ValueError ("Incorrect value for data_item %s"%sItem) 161 162 if( self.source == DataSource.NORGATE ): 163 if (sItem == DataItem.OPEN): 164 list_index.append(1) 165 elif (sItem == DataItem.HIGH): 166 list_index.append (2) 167 elif (sItem ==DataItem.LOW): 168 list_index.append(3) 169 elif (sItem == DataItem.CLOSE): 170 list_index.append(4) 171 elif(sItem == DataItem.VOL): 172 list_index.append(5) 173 elif (sItem == DataItem.ACTUAL_CLOSE): 174 list_index.append(6) 175 else: 176 #incorrect value 177 raise ValueError ("Incorrect value for data_item %s"%sItem) 178 #end elif 179 #end data_item loop 180 181 #read in data for a stock 182 symbol_ctr=-1 183 for symbol in symbol_list: 184 _file = None 185 symbol_ctr = symbol_ctr + 1 186 #print self.getPathOfFile(symbol) 187 try: 188 file_path= self.getPathOfFile(symbol); 189 190 ''' Get list of other files if we also want to include delisted ''' 191 if bIncDelist: 192 lsDelPaths = self.getPathOfFile( symbol, True ) 193 if file_path == None and len(lsDelPaths) > 0: 194 print 'Found delisted paths:', lsDelPaths 195 196 ''' If we don't have a file path continue... unless we have delisted paths ''' 197 if (type (file_path) != type ("random string")): 198 if bIncDelist == False or len(lsDelPaths) == 0: 199 continue; #File not found 200 201 if not file_path == None: 202 _file = open(file_path, "rb") 203 except IOError: 204 # If unable to read then continue. The value for this stock will be nan 205 print _file 206 continue; 207 208 assert( not _file == None or bIncDelist == True ) 209 ''' Open the file only if we have a valid name, otherwise we need delisted data ''' 210 if _file != None: 211 naData = pkl.load (_file) 212 _file.close() 213 else: 214 naData = None 215 216 ''' If we have delisted data, prepend to the current data ''' 217 if bIncDelist == True and len(lsDelPaths) > 0 and naData == None: 218 for sFile in lsDelPaths[-1:]: 219 ''' Changed to only use NEWEST data since sometimes there is overlap (JAVA) ''' 220 inFile = open( sFile, "rb" ) 221 naPrepend = pkl.load( inFile ) 222 inFile.close() 223 224 if naData == None: 225 naData = naPrepend 226 else: 227 naData = np.vstack( (naPrepend, naData) ) 228 229 230 #now remove all the columns except the timestamps and one data column 231 if verbose: 232 print self.getPathOfFile(symbol) 233 234 ''' Fix 1 row case by reshaping ''' 235 if( naData.ndim == 1 ): 236 naData = naData.reshape(1,-1) 237 238 ''' We open the file once, for each data item we need, fill out the array in all_stocks_data ''' 239 for lLabelNum, lLabelIndex in enumerate(list_index): 240 241 ts_ctr = 0 242 243 ''' select timestamps and the data column we want ''' 244 temp_np = naData[:,(0,lLabelIndex)] 245 #print temp_np 246 num_rows= temp_np.shape[0] 247 248 symbol_ts_list = range(num_rows) # preallocate 249 for i in range (0, num_rows): 250 251 timebase = temp_np[i][0] 252 timeyear = int(timebase/10000) 253 254 ''' Quick hack to skip most of the data ''' 255 if timeyear < ts_list[0].year: 256 continue 257 elif ts_ctr == 0: 258 ts_ctr = i 259 260 261 timemonth = int((timebase-timeyear*10000)/100) 262 timeday = int((timebase-timeyear*10000-timemonth*100)) 263 timehour = 16 264 265 #The earliest time it can generate a time for is platform dependent 266 symbol_ts_list[i]=dt.datetime(timeyear,timemonth,timeday,timehour) # To make the time 1600 hrs on the day previous to this midnight 267 #for ends 268 269 270 #now we have only timestamps and one data column 271 272 273 #Skip data from file which is before the first timestamp in ts_list 274 275 while (ts_ctr < temp_np.shape[0]) and (symbol_ts_list[ts_ctr] < ts_list[0]): 276 ts_ctr= ts_ctr+1 277 278 #print "skipping initial data" 279 #while ends 280 281 for time_stamp in ts_list: 282 283 if (symbol_ts_list[-1] < time_stamp): 284 #The timestamp is after the last timestamp for which we have data. So we give up. Note that we don't have to fill in NaNs because that is 285 #the default value. 286 break; 287 else: 288 while ((ts_ctr < temp_np.shape[0]) and (symbol_ts_list[ts_ctr]< time_stamp)): 289 ts_ctr = ts_ctr+1 290 #while ends 291 #else ends 292 293 #print "at time_stamp: " + str(time_stamp) + " and symbol_ts" + str(symbol_ts_list[ts_ctr]) 294 295 if (time_stamp == symbol_ts_list[ts_ctr]): 296 #Data is present for this timestamp. So add to numpy array. 297 #print " adding to numpy array" 298 if (temp_np.ndim > 1): #This if is needed because if a stock has data for 1 day only then the numpy array is 1-D rather than 2-D 299 all_stocks_data[lLabelNum][ts_list.index(time_stamp)][symbol_ctr] = temp_np [ts_ctr][1] 300 else: 301 all_stocks_data[lLabelNum][ts_list.index(time_stamp)][symbol_ctr] = temp_np [1] 302 #if ends 303 304 ts_ctr = ts_ctr +1 305 306 #inner for ends 307 #outer for ends 308 309 ldmReturn = [] # List of data matrixes to return 310 for naDataLabel in all_stocks_data: 311 ldmReturn.append( pa.DataFrame( naDataLabel, ts_list, symbol_list) ) 312 313 314 ''' Contine to support single return type as a non-list ''' 315 if bStr: 316 return ldmReturn[0] 317 else: 318 return ldmReturn

319 320 #get_data_hardread ends 321

322 - def get_data (self, ts_list, symbol_list, data_item, verbose=False, bIncDelist=False):

323 ''' 324 Read data into a DataFrame, but check to see if it is in a cache first. 325 @param ts_list: List of timestamps for which the data values are needed. Timestamps must be sorted. 326 @param symbol_list: The list of symbols for which the data values are needed 327 @param data_item: The data_item needed. Like open, close, volume etc. May be a list, in which case a list of DataFrame is returned. 328 @param bIncDelist: If true, delisted securities will be included. 329 @note: If a symbol is not found then a message is printed. All the values in the column for that stock will be NaN. Execution then 330 continues as usual. No errors are raised at the moment. 331 ''' 332 333 # Construct hash -- filename where data may be already 334 # 335 # The idea here is to create a filename from the arguments provided. 336 # We then check to see if the filename exists already, meaning that 337 # the data has already been created and we can just read that file. 338 339 # Create the hash for the symbols 340 hashsyms = 0 341 for i in symbol_list: 342 hashsyms = (hashsyms + hash(i)) % 10000000 343 344 # Create the hash for the timestamps 345 hashts = 0 346 347 # print "test point 1: " + str(len(ts_list)) 348 spyfile=os.environ['QSDATA'] + '/Processed/Norgate/Stocks/US/NYSE Arca/SPY.pkl' 349 for i in ts_list: 350 hashts = (hashts + hash(i)) % 10000000 351 hashstr = 'qstk-' + str (self.source)+'-' +str(abs(hashsyms)) + '-' + str(abs(hashts)) \ 352 + '-' + str(hash(str(data_item)))+ '-' + str(hash(str(os.path.getctime(spyfile)))) 353 354 # get the directory for scratch files from environment 355 try: 356 scratchdir = os.environ['QSSCRATCH'] 357 except KeyError: 358 #self.rootdir = "/hzr71/research/QSData" 359 raise KeyError("Please be sure to set the value for QSSCRATCH in config.sh or local.sh") 360 361 # final complete filename 362 cachefilename = scratchdir + '/' + hashstr + '.pkl' 363 if verbose: 364 print "cachefilename is:"+cachefilename 365 366 # now eather read the pkl file, or do a hardread 367 readfile = False # indicate that we have not yet read the file 368 369 #check if the cachestall variable is defined. 370 371 # catchstall=os.environ['CACHESTALLTIME'] 372 try: 373 catchstall=dt.timedelta(hours=int(os.environ['CACHESTALLTIME'])) 374 except: 375 catchstall=dt.timedelta(hours=1) 376 377 # Check if the file is older than the cachestalltime 378 if os.path.exists(cachefilename): 379 if ((dt.datetime.now()-dt.datetime.fromtimestamp(os.path.getctime(cachefilename)))<catchstall): 380 if verbose: 381 print "cache hit" 382 try: 383 cachefile = open(cachefilename, "rb") 384 start = time.time() # start timer 385 retval = pkl.load(cachefile) 386 elapsed = time.time() - start # end timer 387 readfile = True # remember success 388 cachefile.close() 389 except IOError: 390 if verbose: 391 print "error reading cache: " + cachefilename 392 print "recovering..." 393 except EOFError: 394 if verbose: 395 print "error reading cache: " + cachefilename 396 print "recovering..." 397 if (readfile!=True): 398 if verbose: 399 print "cache miss" 400 print "beginning hardread" 401 start = time.time() # start timer 402 if verbose: 403 print "data_item(s): " + str(data_item) 404 print "symbols to read: " + str(symbol_list) 405 retval = self.get_data_hardread(ts_list, 406 symbol_list, data_item, verbose, bIncDelist) 407 elapsed = time.time() - start # end timer 408 if verbose: 409 print "end hardread" 410 print "saving to cache" 411 try: 412 cachefile = open(cachefilename,"wb") 413 pkl.dump(retval, cachefile, -1) 414 os.chmod(cachefilename,0666) 415 except IOError: 416 print "error writing cache: " + cachefilename 417 if verbose: 418 print "end saving to cache" 419 if verbose: 420 print "reading took " + str(elapsed) + " seconds" 421 return retval

422

423 - def getPathOfFile(self, symbol_name, bDelisted=False):

424 ''' 425 @summary: Since a given pkl file can exist in any of the folders- we need to look for it in each one until we find it. Thats what this function does. 426 @return: Complete path to the pkl file including the file name and extension 427 ''' 428 429 if not bDelisted: 430 for path1 in self.folderList: 431 if (os.path.exists(str(path1)+str(symbol_name+".pkl"))): 432 # Yay! We found it! 433 return (str(str(path1)+str(symbol_name)+".pkl")) 434 #if ends 435 #for ends 436 437 else: 438 ''' Special case for delisted securities ''' 439 lsPaths = [] 440 for sPath in self.folderList: 441 if re.search( 'Delisted Securities', sPath ) == None: 442 continue 443 444 for sFile in dircache.listdir(sPath): 445 if not re.match( '%s-\d*.pkl'%symbol_name, sFile ) == None: 446 lsPaths.append( sPath + sFile ) 447 448 lsPaths.sort() 449 return lsPaths 450 451 452 453 print "Did not find path to " + str (symbol_name)+". Looks like this file is missing"

454

455 - def get_all_symbols (self):

456 ''' 457 @summary: Returns a list of all the symbols located at any of the paths for this source. @see: {__init__} 458 @attention: This will discard all files that are not of type pkl. ie. Only the files with an extension pkl will be reported. 459 ''' 460 461 listOfStocks=list() 462 #Path does not exist 463 464 if (len(self.folderList) == 0): 465 raise ValueError ("DataAccess source not set") 466 467 for path in self.folderList: 468 stocksAtThisPath=list () 469 #print str(path) 470 stocksAtThisPath= dircache.listdir(str(path)) 471 #Next, throw away everything that is not a .pkl And these are our stocks! 472 stocksAtThisPath = filter (lambda x:(str(x).find(str(self.fileExtensionToRemove)) > -1), stocksAtThisPath) 473 #Now, we remove the .pkl to get the name of the stock 474 stocksAtThisPath = map(lambda x:(x.partition(str(self.fileExtensionToRemove))[0]),stocksAtThisPath) 475 476 listOfStocks.extend(stocksAtThisPath) 477 #for stock in stocksAtThisPath: 478 #listOfStocks.append(stock) 479 return listOfStocks

480 #get_all_symbols ends 481

482 - def get_symbols_in_sublist (self, subdir):

483 ''' 484 @summary: Returns all the symbols belonging to that subdir of the data store. 485 @param subdir: Specifies which subdir you want. 486 @return: A list of symbols belonging to that subdir 487 ''' 488 489 pathtolook = self.rootdir+self.midPath+subdir 490 stocksAtThisPath= dircache.listdir(pathtolook) 491 492 #Next, throw away everything that is not a .pkl And these are our stocks! 493 try: 494 stocksAtThisPath = filter (lambda x:(str(x).find(str(self.fileExtensionToRemove)) > -1), stocksAtThisPath) 495 #Now, we remove the .pkl to get the name of the stock 496 stocksAtThisPath = map(lambda x:(x.partition(str(self.fileExtensionToRemove))[0]),stocksAtThisPath) 497 except: 498 print "error: no path to " + subdir 499 stocksAtThisPath = list() 500 501 return stocksAtThisPath

502 #get_all_symbols_on_exchange ends 503

504 - def get_sublists (self):

505 ''' 506 @summary: Returns a list of all the sublists for a data store. 507 @return: A list of the valid sublists for the data store. 508 ''' 509 510 return self.folderSubList

511 #get_sublists 512

513 - def get_data_labels (self):

514 ''' 515 @summary: Returns a list of all the data labels available for this type of data access object. 516 @return: A list of label strings. 517 ''' 518 519 if (self.source != DataSource.COMPUSTAT): 520 print 'Function only valid for Compustat objects!' 521 return [] 522 523 return DataItem.COMPUSTAT

524 525 #get_data_labels 526

527 - def get_info (self):

528 ''' 529 @summary: Returns and prints a string that describes the datastore. 530 @return: A string. 531 ''' 532 533 if (self.source == DataSource.NORGATE): 534 retstr = "Norgate:\n" 535 retstr = retstr + "Daily price and volume data from Norgate (premiumdata.net)\n" 536 retstr = retstr + "that is valid at the time of NYSE close each trading day.\n" 537 retstr = retstr + "\n" 538 retstr = retstr + "Valid data items include: \n" 539 retstr = retstr + "\topen, high, low, close, volume, actual_close\n" 540 retstr = retstr + "\n" 541 retstr = retstr + "Valid subdirs include: \n" 542 for i in self.folderSubList: 543 retstr = retstr + "\t" + i + "\n" 544 545 elif (self.source == DataSource.YAHOO): 546 retstr = "Yahoo:\n" 547 retstr = retstr + "To be completed by Shreyas\n" 548 retstr = retstr + "Valid data items include: \n" 549 retstr = retstr + "\topen, high, low, close, volume, actual_close\n" 550 retstr = retstr + "\n" 551 retstr = retstr + "Valid subdirs include: \n" 552 for i in self.folderSubList: 553 retstr = retstr + "\t" + i + "\n" 554 555 elif (self.source == DataSource.COMPUSTAT): 556 retstr = "Compustat:\n" 557 retstr = retstr + "Compilation of (almost) all data items provided by Compustat\n" 558 retstr = retstr + "Valid data items can be retrieved by calling get_data_labels(): \n" 559 retstr = retstr + "\n" 560 retstr = retstr + "Valid subdirs include: \n" 561 for i in self.folderSubList: 562 retstr = retstr + "\t" + i + "\n" 563 elif (self.source == DataSource.CUSTOM): 564 retstr = "Custom:\n" 565 retstr = retstr + "Attempts to load a custom data set, assuming each stock has\n" 566 retstr = retstr + "a pkl file with the name and first column as the stock ticker, date in second column, and data in following columns.\n" 567 retstr = retstr + "everything should be located in QSDATA/Processed/CUSTOM.\n" 568 else: 569 retstr = "DataAccess internal error\n" 570 571 print retstr 572 return retstr

Source Code for Module QSTK.qstkutil.DataAccess