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")