QSTK.csvconverter.csvapi

30 - def __init__(self):

31 self.filt_list=[] 32 self.timestamps=[]

33

34 - def getSymbols(self, listOfPaths, fileExtensionToRemove):

35 ''' 36 @bug: This might not work if the path contains a folder whose name has .csv, or is some random file that has a .csv in it.. 37 So, lets assume that whoever is using this is not going to "cheat" us 38 ''' 39 listOflistOfStocks=list() 40 for path in listOfPaths: 41 stocksAtThisPath=list () 42 stocksAtThisPath= dircache.listdir(str(path)) 43 #Next, throw away everything that is not a .csv And these are our stocks! 44 stocksAtThisPath = filter (lambda x:(str(x).find(str(fileExtensionToRemove)) > -1), stocksAtThisPath) 45 #Now, we remove the .csv to get the name of the stock 46 stocksAtThisPath = map(lambda x:(x.partition(str(fileExtensionToRemove))[0]),stocksAtThisPath) 47 48 #Then add that list to listOflistOfStocks 49 listOflistOfStocks.append(stocksAtThisPath) 50 return listOflistOfStocks

51 #getSymbols done 52 53 #build the array

54 - def getData(self, listOfListOfStocks, listOfInputPaths, startDate, endDate, listOfOutputPaths):

55 ''' 56 @summary: This is where all the work happens 57 @attention: Assumption here is that past data never changes 58 @bug: The exchange is currently set pretty randomly 59 ''' 60 61 #Finding no. of stocks 62 noOfStocks=0 63 for stock_list in listOfListOfStocks: 64 noOfStocks+= len (stock_list) 65 #for stock in stock_list: 66 #print str(stock) 67 68 print "No. of stocks: " + str(noOfStocks) 69 print "No. of timestamps: " + str(len(self.timestamps)) 70 71 72 listIndex=-1 73 ctr=1; 74 for inputFileFolder in listOfInputPaths: 75 listIndex+=1 76 outputFileFolder= str(listOfOutputPaths[listIndex]) 77 stocks_list= listOfListOfStocks[listIndex] 78 for i in range(0, len(stocks_list)): # - self.count_of_non_existent_stocks): 79 print str(stocks_list[i]) +" "+str(ctr)+" of "+ str(noOfStocks)+" "+ str(time.strftime("%H:%M:%S")) 80 ctr= ctr+1 81 82 beginTS= startDate 83 84 #Check if the file exists 85 if (os.path.exists(str(outputFileFolder) + str(stocks_list[i]+".h5"))): 86 87 #Checking the last timestamp in the hdf file 88 h5f=pt.openFile(outputFileFolder + str(stocks_list[i]+".h5"), mode = "a") 89 print "Updating " +str(outputFileFolder + str(stocks_list[i]+".h5")) 90 table= h5f.getNode('/StrategyData', 'StrategyData') 91 beginTS= int(time.strftime("%Y%m%d", time.gmtime(table[table.nrows-1]['timestamp']))) #+ 1 #POSSIBLE BUG? 92 if (str(beginTS) >= self.timestamps[len(self.timestamps)-1]): #if (os.path.getmtime(str(outputFileFolder)+str(stocks_list[i])+".h5") > os.path.getmtime(str(self.dirname+ "/"+ str(stocks_list[i]+".CSV")))): 93 #The hdf5 file for this stock has been modified after the CSV file was modified. Ergo- no changes need to be made to it now.. 94 print str(stocks_list[i])+".h5 already is up to date. "+ str(time.strftime("%H:%M:%S")) 95 h5f.close() 96 continue 97 else: 98 #File is present but not upto date 99 beginTS= int(time.strftime("%Y%m%d", time.gmtime(table[table.nrows-1]['timestamp']))) 100 else: 101 #The only foreseeable reason why there might be an exception here is that the hdf file does not exist. So, creating it. 102 print "Creating file: " + str(outputFileFolder) + str(stocks_list[i]+".h5")+" "+ str(time.strftime("%H:%M:%S")) 103 104 h5f = pt.openFile(str(outputFileFolder) + str(stocks_list[i]+".h5"), mode = "w") 105 group = h5f.createGroup("/", 'StrategyData') 106 table = h5f.createTable(group, 'StrategyData', StrategyDataModel) 107 beginTS= startDate 108 #else done 109 110 f=open(str(inputFileFolder)+str(stocks_list[i]+str(".CSV"))) 111 jk=f.readlines() 112 f.close() 113 jk.pop(0) 114 115 self.filt_list=list() 116 filt_list_temp=filter(lambda x: (int(x.split(',')[1])> int(beginTS)) ,jk) #Because we only want timestamps strictly greater than the last timestamp currently in the file. 117 filt_list_temp=filter(lambda x: (int(x.split(',')[1])<= int(endDate)) ,filt_list_temp) 118 filt_list_temp=map(lambda x:(x.split(',')[0],x.split(',')[1],x.split(',')[2],x.split(',')[3],x.split(',')[4],x.split(',')[5],x.split(',')[6],(x.split(',')[7]).strip()),filt_list_temp) 119 120 self.filt_list.append(filt_list_temp) 121 122 if (table.nrows > 0): 123 #we are appending to an old file and not creating a new file.. 124 125 tsStartIndex= np.array(self.timestamps).searchsorted(beginTS) +1 126 127 else: 128 #creating a new file... 129 tsStartIndex =0 130 #if (table.nrows > 0) done 131 132 k = 0 133 for j in range(tsStartIndex, len(self.timestamps)): 134 if (k< len(self.filt_list[0])): 135 136 if((self.timestamps[j])< (self.filt_list[0][k][1])): 137 row=table.row 138 row['exchange'] = 'NYSE' 139 row['symbol'] = self.filt_list[0][k][0] 140 row['adj_open'] = np.NaN 141 row['adj_close'] = np.NaN 142 row['adj_high'] = np.NaN 143 row['adj_low'] = np.NaN 144 row['close'] = np.NaN 145 row['volume'] = np.NaN 146 parseddate = time.strptime(self.timestamps[j],'%Y%m%d') 147 # row['date'] = self.timestamps[j] 148 row['timestamp'] = time.mktime(parseddate) 149 row.append() 150 151 elif(self.timestamps[j]==self.filt_list[0][k][1]): 152 row=table.row 153 row['exchange'] = 'NASDAQ' 154 row['symbol'] = self.filt_list[0][k][0] 155 row['adj_open'] = float(self.filt_list[0][k][2]) 156 row['adj_close'] = float(self.filt_list[0][k][5]) 157 row['adj_high'] = float(self.filt_list[0][k][3]) 158 row['adj_low'] = float(self.filt_list[0][k][4]) 159 row['close'] = float(self.filt_list[0][k][7]) 160 row['volume'] = int(self.filt_list[0][k][6]) 161 parseddate = time.strptime(self.timestamps[j],'%Y%m%d') 162 # row['date'] = self.timestamps[j] 163 row['timestamp'] = time.mktime(parseddate) 164 row.append() 165 166 k=k+1 167 else: 168 print"###############Something has gone wrong. A stock had a timestamp which was not in the timestamp list..." 169 print "TS: " + str(self.timestamps[j]) + ", Stock: " + str (self.filt_list[0][k][1]) 170 k=k+1 171 #should stop executing here? Naah 172 # sys.exit() 173 174 else: 175 row=table.row 176 row['exchange'] = 'NYSE' 177 row['symbol'] = stocks_list[i] #self.filt_list[0][len(self.filt_list[0])-1][0] ####NOTE. POSSIBLE BUG? 178 row['adj_open'] = np.NaN 179 row['adj_close'] = np.NaN 180 row['adj_high'] = np.NaN 181 row['adj_low'] = np.NaN 182 row['close'] = np.NaN 183 row['volume'] = np.NaN 184 parseddate = time.strptime(self.timestamps[j],'%Y%m%d') 185 # row['date'] = self.timestamps[j] 186 row['timestamp'] = time.mktime(parseddate) 187 # row['interval'] = 86400 188 row.append() 189 190 #for j in range(len(self.timestamps)) ends 191 table.flush() 192 h5f.close() 193 #for i in range(0, stocks.size) done 194 195 print "Writing data done. "+ str(time.strftime("%H:%M:%S"))

196 197

198 - def makeOrUpdateTimestampsFile(self, fileName, listOflistOfStocks, listOfInputPaths, startDate, endDate):

199 ''' 200 @bug: Formerly did not take care of DST 201 @attention: fixed DST bug. No known DST problems now. 202 ''' 203 204 DAY=86400 205 206 if (os.path.exists(fileName)): 207 print "Updating timestamps" 208 h5f = pt.openFile(str(fileName), mode = "a") 209 table=h5f.getNode('/timestamps','timestamps') 210 211 lastTSFromFile= str(time.strftime("%Y%m%d", time.gmtime(table[table.nrows-1]['timestamp']))) 212 213 if (str(startDate)<= lastTSFromFile): 214 startDate=str(time.strftime("%Y%m%d", time.gmtime(table[table.nrows-1]['timestamp']))) # TO FIX DST BUG 215 216 else: 217 print "Creating new timestamp file" 218 h5f = pt.openFile(str(fileName), mode = "w") 219 group = h5f.createGroup("/", 'timestamps') 220 table = h5f.createTable(group, 'timestamps', TimestampsModel) 221 222 print "start: " + str(startDate)+", end: "+ str(endDate) 223 224 tslist=list() 225 ctr=1 226 if (str(startDate) <= str(endDate)): 227 listIndex=-1 228 for path in listOfInputPaths: 229 listIndex+=1 230 for stock in listOflistOfStocks[listIndex]: 231 #print str(stock)+" "+str(ctr)+" "+str(time.strftime("%H:%M:%S")) 232 ctr+=1 233 234 f=open(str(path)+str(stock+str(".CSV"))) 235 j=f.readlines() 236 j.pop(0) #To remove the "header" row 237 f.close() 238 239 filt_list_temp=filter(lambda x: (int(x.split(',')[1])> int(startDate)) ,j) # To fix DST bug 240 filt_list_temp=filter(lambda x: (int(x.split(',')[1])<= int(endDate)) ,filt_list_temp) 241 242 if not (filt_list_temp): 243 print str(stock.split('.')[0]) + " didn't exist in this period\n" 244 #ENHANCEMENT- CAN ALSO REMOVE STOCK FROM THE STOCKLIST 245 #This can not be done right now- because if a stock did not exist- but another stock did exist then NaNs have to be added to the stock that did not exist. 246 else: 247 #it existed and now we need the timestamps 248 filt_list_temp=map(lambda x:(x.split(',')[1]),filt_list_temp) 249 filt_list_temp= map(lambda item:(time.mktime(time.strptime(item,'%Y%m%d'))), filt_list_temp) 250 251 for item in filt_list_temp: 252 try: 253 tslist.index(int(item)) 254 except: 255 tslist.append(int(item)) 256 257 258 if (len(tslist)>0): 259 if (self.continueChecking(tslist, startDate, endDate)== False): 260 break #All dates are covered.. 261 262 #for stock in stocks_list done 263 264 tslist.sort() #this should all fit into memory 265 266 for ts in tslist: 267 row= table.row 268 row['timestamp']= ts 269 #print "Adding timestamp " + str (ts) 270 row.append() 271 #for ts in tslist ends 272 273 table.flush() 274 h5f.close()

275 276 #makeTimestampsFile ends 277 278

279 - def continueChecking(self, tsList, beginTS, endTS):

280 ''' 281 @summary: This function basically checks if a day that we haven't found any trades on is a weekend. If so- we don't need to keep looking. The converter will work just fine even without this function- but it will take more time- because it will keep looking for timestamps that it is not going to find. 282 @bug: There is a Daylight savings time bug here too- but it won't adversely affect anything because DST always happens over the weekends! Though if the time change happens on a weekday sometime in the distant past/future this function may break. 283 ''' 284 285 index=1 286 DAY=86400 287 288 while (index < len(tsList)): 289 if (int(tsList[index])- int(tsList[index -1]) > DAY): 290 tempTS= tsList[index-1] + DAY 291 while (tempTS< tsList[index]): 292 timeStruct= time.gmtime(tempTS) 293 if not ((timeStruct[6] == 5) or (timeStruct[6] == 6)): 294 #Keep looking 295 return True #if its not a Saturday or a Sunday then keep looking 296 tempTS+=DAY 297 #while (tempTS< tsList[index]) ends 298 index+=1 299 #while ends 300 #Checking from beginTS to start of list 301 tempTS=time.mktime(time.strptime(str(beginTS),'%Y%m%d')) 302 303 while (int(tsList[0])- int(tempTS) > DAY): 304 timeStruct= time.gmtime((tempTS)) 305 if not ((timeStruct[6] == 5) or (timeStruct[6] == 6)): 306 return True 307 #if not... ends 308 tempTS+=DAY 309 #while (tsList[0]- tempTS > DAY) ends 310 #Checking from endTS to end of list 311 tempTS=time.mktime(time.strptime(str(endTS),'%Y%m%d')) 312 313 while (int(tempTS)- int(tsList[len(tsList)-1]) > DAY): 314 timeStruct= time.gmtime(tempTS) 315 if not ((timeStruct[6] == 5) or (timeStruct[6] == 6)): 316 return True 317 #if not... ends 318 tempTS+=DAY 319 #while (tempTS- tsList[len(tsList)-1] > DAY) ends 320 321 print "Smartly figured out that we don't need to continue" 322 return False #stop looking for more timestamps because all the timestamps that can be in the list are now there..

323 #we will not get any more timestamps by looking for more..because there just aren't any left... cool huh? 324 #continueChecking ends 325 326

327 - def readTimestampsFromFile(self, fileName, beginTS, endTS):

328 h5f = pt.openFile(str(fileName), mode = "a") 329 fileIterator= h5f.root.timestamps.timestamps 330 331 tslist=[] 332 for row in fileIterator.iterrows(): 333 temp= str(time.strftime("%Y%m%d", time.gmtime(row['timestamp']))) 334 335 if (temp>= str(beginTS)) and (temp <= str(endTS)): 336 tslist.append(temp) 337 338 if (temp > str(endTS)): 339 break 340 341 h5f.close() 342 343 self.timestamps=tslist

344 #readTimestampsFromFile ends 345

346 - def keepHDFFilesInSyncWithCSV(self, listOfInputPaths, listOfOutputPaths):

347 ''' 348 @summary: This function removes HDF files that correspond to CSV files that existed in the past- but don't exist anymore. Possibly because the stock was delisted or something like that. 349 ''' 350 print "Removing HDF files for which there is no corresponding CSV file" 351 listOfListOfHdfFiles=self.getSymbols(listOfOutputPaths, ".h5") 352 listOfListOfCsvFiles=self.getSymbols(listOfInputPaths, ".csv") #I guess this isn't really necessary, we could just reuse the stock list or something 353 #but let's just keep things "proper" 354 ctr=-1 355 for listofHDFFiles in listOfListOfHdfFiles: 356 ctr+=1 357 for hdfFile in listofHDFFiles: 358 try: 359 #Check if the HDF file exists... 360 listOfListOfCsvFiles[ctr].index(hdfFile) 361 except: 362 print "Removing "+str(listOfOutputPaths[ctr]) + str(hdfFile)+".h5" 363 os.remove(str(listOfOutputPaths[ctr]) + str(hdfFile)+".h5") 364 #if ends 365 #for hdfFile in listOfListOfHdfFiles ends 366 #for listofHDFFiles in listOfListOfHdfFiles ends 367 368 print "Done removing HDF files (if any)"

Source Code for Module QSTK.csvconverter.csvapi