return inrec def initoutrecdata(): outrec = {} for column in outrecColumns: outrec[column[0]] = None return outrec #=============================================================================== db_file = 'StooqDataAnalysis.accdb' #raw string, escape sequences are ignored db_file = os.path.abspath(db_file) conn = dbu.createDBConnection(db_file) c = conn.cursor() begdate = datetime.date(2018, 3, 14) fourpmtime = datetime.time(16, 0, 0) nine35amtime = datetime.time(9, 35, 0) tenamtime = datetime.time(10, 0, 0) ten30amtime = datetime.time(10, 30, 0) elevenamtime = datetime.time(11, 0, 0)
print(sql) c.execute(sql) columns = [column[0] for column in c.description] recs = [] for row in c.fetchall(): recs.append(dict(zip(columns, row))) return recs if __name__ == '__main__': db_file = r'''StooqDataAnalysis.accdb''' #raw string, escape sequences are ignored db_file = os.path.abspath(db_file) dbConnData = dbu.createDBConnection(db_file) conn = dbu.createDBConnection(db_file) # Selective records =========================================================== # field_names = ['Ticker', 'CurDate'] # operands = ['=', '='] # # values = ['A','datetime.date(year=2018, month=3, day=15)'] # values = ['A','#3/16/2018#'] # # in_recs = get_selective_recs(conn, field_names, operands, values) # # for rec in in_recs: # for k, v in rec.items(): # print(k, v) # break #===============================================================================
def get_selected_cross_tab_rec(ticker, date): # print(ticker, date) year, month, date = date.split("-") # print(year, month, date) date = "#" + month + "/" + date + "/" + year + "#" # db_file = r'''C:\TickData2018\StooqDataAnalysis.accdb''' #raw string, escape sequences are ignored db_file = r'''StooqDataAnalysis.accdb''' #raw string, escape sequences are ignored db_file = os.path.abspath(db_file) conn = dbu.createDBConnection(db_file) field_names = ['Ticker', 'CurDate'] operands = ['=', '='] # values = ['A','datetime.date(year=2018, month=3, day=15)'] #values = ['A','#3/16/2018#'] values = [ticker, date] in_recs = sct.get_selective_recs(conn, field_names, operands, values) for rec in in_recs: times = [ datetime.time(hour=4, minute=0, second=0), datetime.time(hour=9, minute=35, second=0), datetime.time(hour=10, minute=0, second=0), datetime.time(hour=10, minute=30, second=0), datetime.time(hour=11, minute=0, second=0), datetime.time(hour=16, minute=0, second=0) ] opens = [ rec['PDayOpen'], rec['CDayOpenOpen'], rec['CDay1000AMOpen'], rec['CDay1030AMOpen'], rec['CDay1100AMOpen'], rec['CDay0400PMOpen'] ] lows = [ rec['PDayLow'], rec['CDayOpenLow'], rec['CDay1000AMLow'], rec['CDay1030AMLow'], rec['CDay1100AMLow'], rec['CDay0400PMLow'], ] highs = [ rec['PDayHigh'], rec['CDayOpenHigh'], rec['CDay1000AMHigh'], rec['CDay1030AMHigh'], rec['CDay1100AMHigh'], rec['CDay0400PMHigh'], ] closes = [ rec['PDayClose'], rec['CDayOpenClose'], rec['CDay1000AMClose'], rec['CDay1030AMClose'], rec['CDay1100AMClose'], rec['CDay0400PMClose'], ] file_name = 'cs2' plotCSChartSingle.plot_chart(times, opens, highs, lows, closes, file_name)
def write_records(gap_file, gaptype, fdate, ftime): def init_outrec(): outrec = { 'Ticker': None, 'TrDate': None, 'ExtractTime': None, 'GapType': None, 'Price': None, 'CurDayChg': None, 'ChgFrmOpen': None, 'Gap': None, 'Volume': None, 'Beta': None, 'AvgTrueRange': None, 'SMV20D': None, 'SMV50D': None, 'SMV200D': None, 'Hi52Wks': None, 'Lo52Wks': None, 'RelStr': None } return outrec # gap_file = "C:\\Users\\karsu\\Downloads\\finviz_gapup.csv" d = str(datetime.datetime.fromtimestamp(os.stat(gap_file)[8])) if (fdate): file_date = fdate else: file_date = d.split(' ')[0] if (ftime): file_time = ftime else: file_time = d.split(' ')[1] gaps = csv.reader(open(gap_file, 'r'), delimiter=',') inrec = [] lineNo = 1 outrec = init_outrec() db_file = 'FinvizGapAnalysis.accdb' #raw string, escape sequences are ignored db_file = os.path.abspath(db_file) conn = dbu.createDBConnection(db_file) table_name = "tblRawFromFinviz" #print('Modified time:', time.ctime(os.path.getmtime(__file__))) for gap in gaps: if (lineNo == 1): lineNo = lineNo + 1 continue for i in range(len(gap)): if gap[i]: pass else: gap[i] = None outrec['Ticker'] = gap[1] outrec['TrDate'] = file_date outrec['ExtractTime'] = file_time outrec['GapType'] = gaptype outrec['Price'] = gap[10] outrec['CurDayChg'] = gap[11][:-1] outrec['ChgFrmOpen'] = gap[12][:-1] outrec['Gap'] = gap[13][:-1] outrec['Volume'] = gap[14] outrec['Beta'] = gap[2] outrec['AvgTrueRange'] = gap[3] outrec['SMV20D'] = gap[4][:-1] outrec['SMV50D'] = gap[5][:-1] outrec['SMV200D'] = gap[6][:-1] outrec['Hi52Wks'] = gap[7][:-1] outrec['Lo52Wks'] = gap[8][:-1] outrec['RelStr'] = gap[9] dbu.insert_recs(conn, table_name, outrec)
def getSingleFileData(filename, output): rawFile = filename removePath = rawFile.split("\\")[-1] ticker = removePath.split('.')[ 0] # format - Date, Time, open, high, low, close, volume # print(data.read()) temp_line = [] total_result = [] single_result = [] lineNo = 1 hoursadj = 0 data = open(rawFile, 'r') for line in data: if (lineNo == 1): lineNo = lineNo + 1 continue temp_line = line.split(',') if (lineNo == 2): olddate = temp_line[0] lineNo = 0 timelist = temp_line[1].split(":") hours = int(timelist[0]) if (olddate != temp_line[0]): hoursadj = hours - 14 olddate = temp_line[0] minutes = int(timelist[1]) seconds = int(timelist[2]) hours = hours - 5 - hoursadj if (hours >= 12): amorpm = "PM" else: amorpm = "AM" if (hours > 12): hours = hours - 12 trtime = str(hours) + ":" + str(format(minutes, '02d')) + ":" + str( format(seconds, '02d')) + " " + amorpm single_result = [] single_result.insert(0, ticker) single_result.insert(1, temp_line[0]) single_result.insert(2, trtime) single_result.insert(3, temp_line[2]) single_result.insert(4, temp_line[4]) single_result.insert(5, temp_line[3]) single_result.insert(6, temp_line[5]) single_result.insert(7, temp_line[6]) total_result.append(single_result) if (output == 'csv'): myFile = open('5MtsFile.csv', 'a', newline='') with myFile: writer = csv.writer(myFile) writer.writerows(total_result) elif (output == 'db'): # db_file = r'''C:\TickData2018\StooqData.accdb''' #raw string, escape sequences are ignored db_file = 'StooqData.accdb' #raw string, escape sequences are ignored # if (os.path.isfile(db_file)) : # print('file present') db_file = os.path.abspath(db_file) conn = dbu.createDBConnection(db_file) c = conn.cursor() for row in total_result: sql = """INSERT INTO tbl5MtsRawFile VALUES ('%s', '%s', '%s','%s', '%s', '%s', '%s', '%s')""" \ % (row[0], row[1], row[2],row[3], row[4], row[5], row[6], row[7]) c.execute(sql) conn.commit() else: print('output argument has to be either csv or db') if (output == 'db'): c.close() del c conn.close() # print("Program Completed")