def fetch_scrip_data(scrip,start,end):
end_date = ""
if end:
end_date = " AND timestamp <= '"+end+"'"
pr("I","Fetching data for scrip "+scrip,1)
ret = s.sql_hash(scrip,"timestamp","open:low:high:close:volume","WHERE timestamp >= '"+str(start)+"'"+end_date+" ORDER BY timestamp")
return ret
def sanitize(data_map,scrip):
c.pr("I","Sanitizing Data For "+scrip,1)
final_map = []
db_data = s.sql_hash(scrip,"timestamp","volume","")
for tk in data_map:
if tk not in db_data:
final_map.append(tk)
return final_map
def fix_missing_entries(scrip):
c.pr("I","Fixing Missing Entries For Scrip "+scrip,1)
uniq_dates = s.sql_array("SELECT DISTINCT CAST(`time` AS DATE) AS dateonly FROM `"+scrip+"`","dateonly")
for date in uniq_dates:
dp_req = fetch_dp_req(str(date),scrip)
db_dp = s.sql_hash(scrip,"timestamp","close","WHERE `time` BETWEEN '"+str(date)+" 09:16:00' AND '"+str(date)+" 15:30:00'")
dp_cur = len(db_dp)
dp_mis = (dp_req - dp_cur)
dp_map = {}
if dp_mis > 1:
c.pr("I","DATE --> "+str(date)+" DP REQ --> "+str(dp_req)+" DP CUR --> "+str(dp_cur)+" DP MIS --> "+str(dp_mis),1)
#Here We attempt to fix DP
dp_min = int(c.get_timestamp(str(date)+" 09:16:00"))
dp_max = int(c.get_timestamp(str(date)+" 15:30:00"))
#c.pr("I","DP MIN ---> "+str(dp_min)+" DP MAX ---> "+str(dp_max),1)
dp_chk = dp_min
ctr = 1
dp_last = 0
while dp_chk != (dp_max+60):
if not str(dp_chk) in db_dp:
#If MIN AND CHK Are Same
if dp_chk == dp_min:
c.pr("I",str(dp_chk)+" ---> MIN MISSING",1)
#exit()
else:
if str((dp_chk - 60)) in db_dp:
#Case Where Previous Data point exists
dp_prev = db_dp[str((dp_chk - 60))]['close']
#print(str(dp_chk)+" ---> PREV PRESENT"+" DP PREV ---> "+str(dp_prev))
dp_map[str(dp_chk)] = process_missing(dp_prev,dp_chk)
else:
#print(str(dp_chk)+" ---> PREV MISSISNG"+" DP PREV ---> "+str(dp_last))
if dp_last:
dp_prev = db_dp[str(dp_last)]['close']
dp_map[str(dp_chk)] = process_missing(dp_prev,dp_chk)
#print(str(dp_chk)+" ---> PREV MISSISNG"+" DP PREV ---> "+str(dp_prev))
else:
dp_last = dp_chk
dp_chk = (dp_chk+60)
if len(dp_map):
store_data(dp_map,scrip)
return
def load_scrips():
scrips = {}
pr("I","Loading All Scrips",0)
scrips = s.sql_hash("scrips","scrip","sector:status:is_fetch:search","WHERE scrip LIKE 'BA%'")
return scrips
def display_stats(st_id):
#Step 1 Get the list of stocks traded with strategy
sim_data = s.sql_hash("sim_tracker","sim_id","scrip:capital:type:transaction:capital","WHERE strategy_id='"+st_id+"' ORDER BY transaction")
sim_ids = list(sim_data.keys())
sim_ids_str = str(sim_ids).replace("[","(").replace("]",")")
sim_map = {}
query = "SELECT * FROM sim_results WHERE sim_id IN "+sim_ids_str
res_map = {}
sum_map = {}
db_obj = s.sql_conn()
cursor = db_obj.cursor()
try:
cursor.execute(query)
results = cursor.fetchall()
for row in results:
if row[0] not in sum_map:
sum_map[row[0]] = {}
sum_map[row[0]]['SC'] = sim_data[row[0]]['scrip']
sum_map[row[0]]['TR'] = sim_data[row[0]]['transaction']
sum_map[row[0]]['ST'] = sim_data[row[0]]['type']
sum_map[row[0]]['EP'] = "9999999999" #MIN OF ALL DP
sum_map[row[0]]['XP'] = "0" #MAX OF ALL DP
sum_map[row[0]]['T1H'] = {}
sum_map[row[0]]['T1H']['EP'] = 0
sum_map[row[0]]['T1H']['XP'] = 0
sum_map[row[0]]['T1H']['VL'] = 0
sum_map[row[0]]['T2H'] = {}
sum_map[row[0]]['T2H']['EP'] = 0
sum_map[row[0]]['T2H']['XP'] = 0
sum_map[row[0]]['T2H']['VL'] = 0
sum_map[row[0]]['SLH'] = {}
sum_map[row[0]]['SLH']['EP'] = 0
sum_map[row[0]]['SLH']['XP'] = 0
sum_map[row[0]]['SLH']['VL'] = 0
sum_map[row[0]]['SQF'] = {}
sum_map[row[0]]['SQF']['EP'] = 0
sum_map[row[0]]['SQF']['XP'] = 0
sum_map[row[0]]['SQF']['VL'] = 0
sum_map[row[0]]['PL'] = 0
sum_map[row[0]]['VL'] = 0
if int(sum_map[row[0]]['EP']) > int(row[1]):
sum_map[row[0]]['EP'] = row[1]
if int(sum_map[row[0]]['XP']) < int(row[2]):
sum_map[row[0]]['XP'] = row[2]
sum_map[row[0]]['PL'] += row[6]
sum_map[row[0]]['VL'] += row[5]
sum_map[row[0]][row[7]]['EP'] = row[3]
sum_map[row[0]][row[7]]['XP'] = row[4]
sum_map[row[0]][row[7]]['VL'] = row[5]
if row[0] not in sim_map:
sim_map[row[0]] = {}
if row[7] not in sim_map[row[0]]:
sim_map[row[0]][row[7]] = {}
sim_map[row[0]][row[7]]['EN'] = row[1]
sim_map[row[0]][row[7]]['XT'] = row[2]
sim_map[row[0]][row[7]]['EP'] = row[3]
sim_map[row[0]][row[7]]['XP'] = row[4]
sim_map[row[0]][row[7]]['VL'] = row[5]
sim_map[row[0]][row[7]]['PL'] = row[6]
except (sql.Error, sql.Warning) as e:
print("-E- Query Failed")
print(e)
db_obj.rollback()
for sim_id in sim_data:
scrip = sim_data[sim_id]['scrip']
capital = sim_data[sim_id]['capital']
stype = sim_data[sim_id]['type']
trans = sim_data[sim_id]['transaction']
if scrip not in res_map:
res_map[scrip] = {}
res_map[scrip]['ACT'] = {}
res_map[scrip]['RAN'] = {}
if trans not in res_map[scrip][stype]:
res_map[scrip][stype][trans] = {}
res_map[scrip][stype][trans]['CP'] = 0
res_map[scrip][stype][trans]['TD'] = 0
res_map[scrip][stype][trans]['SR'] = 0
res_map[scrip][stype][trans]['PL'] = 0
res_map[scrip][stype][trans]['WN'] = 0
res_map[scrip][stype][trans]['LS'] = 0
res_map[scrip][stype][trans]['CP'] = capital
res_map[scrip][stype][trans]['TD'] += 1
trade_stat = 0
for ts in sim_map[sim_id]:
res_map[scrip][stype][trans]['PL'] += sim_map[sim_id][ts]['PL']
if sim_map[sim_id][ts]['PL'] > 0:
trade_stat += 1
else:
trade_stat -= 1
if trade_stat > 0:
res_map[scrip][stype][trans]['WN'] += 1
else:
res_map[scrip][stype][trans]['LS'] += 1
res_map[scrip][stype][trans]['SR'] = round((res_map[scrip][stype][trans]['WN']/res_map[scrip][stype][trans]['TD']) * 100,2)
print("----------------------------------------------------------------------------------------------------------------------------------------")
print("| Simulation Summary |")
print("----------------------------------------------------------------------------------------------------------------------------------------")
print("| Scrip | Simulation | Transaction | Capital | Sims | Wins | Losses | Success % | P/L | Exit Capital |")
print("----------------------------------------------------------------------------------------------------------------------------------------")
for scrip in res_map.keys():
for sim in res_map[scrip].keys():
for trans in res_map[scrip][sim].keys():
#print(trans)
#c.dump(res_map[scrip][sim])
msg = "|"+gs(scrip,21)+"|"+gs(sim,12)+"|"+gs(trans,15)+"|"+gs(str(res_map[scrip][sim][trans]['CP']),12)+"|"
msg = msg+gs(str(res_map[scrip][sim][trans]['TD']),9)+"|"+gs(str(res_map[scrip][sim][trans]['WN']),9)+"|"
msg = msg+gs(str(res_map[scrip][sim][trans]['LS']),8)+"|"+gs(str(res_map[scrip][sim][trans]['SR'])+"%",12)+"|"
msg = msg+gs(str(round(res_map[scrip][sim][trans]['PL'],3)),12)+"|"
msg = msg+gs(str(round(res_map[scrip][sim][trans]['PL'] + res_map[scrip][sim][trans]['CP'],2)),15)+"|"
print(msg)
print("----------------------------------------------------------------------------------------------------------------------------------------")
print("\n--------------------------------------------------------------------------------------------------------------------------------------------------------")
print("| Detailed Summary Actual |")
print("--------------------------------------------------------------------------------------------------------------------------------------------------------")
print("| Scrip | Date | Entry | Exit | Trans | Vlm | T1 | T2 | SL | SQ | P/L |")
print("--------------------------------------------------------------------------------------------------------------------------------------------------------")
#c.dump(sum_map)
STC = ['T1H','T2H','SLH','SQF']
sel_act = ""
sel_ran = ""
ranmsg = ""
for sim in sum_map:
msg = "|"+gs(sum_map[sim]['SC'],11)+"|"
msg += gs(c.get_date(sum_map[sim]['EP'])[0:10],12)+"|"
msg += gs(c.get_date(sum_map[sim]['EP'])[11:-3],7)+"|"
msg += gs(c.get_date(sum_map[sim]['XP'])[11:-3],8)+"|"
msg += gs(sum_map[sim]['TR'],7)+"|"
msg += gs(str(sum_map[sim]['VL']),5)+"|"
for ST in STC:
if sum_map[sim][ST]['VL']:
tst = str(sum_map[sim][ST]['VL'])+" "+str(sum_map[sim][ST]['EP'])+" "+str(sum_map[sim][ST]['XP'])
msg += gs(tst,20)+"|"
else:
msg += gs("NONE",20)+"|"
msg += gs(str(round(sum_map[sim]['PL'],2)),10)+"|"
if sum_map[sim]['ST'] == "ACT":
if sum_map[sim]['TR'] == "BUY":
print(msg)
else:
sel_act += msg+"\n"
else:
if sum_map[sim]['TR'] == "BUY":
ranmsg += msg+"\n"
else:
sel_ran += msg+"\n"
print(sel_act[0:-1])
print("--------------------------------------------------------------------------------------------------------------------------------------------------------")
print("\n--------------------------------------------------------------------------------------------------------------------------------------------------------")
print("| Random Walk Summary Actual |")
print("--------------------------------------------------------------------------------------------------------------------------------------------------------")
print("| Scrip | Date | Entry | Exit | Trans | Vlm | T1 | T2 | SL | SQ | P/L |")
print("--------------------------------------------------------------------------------------------------------------------------------------------------------")
print(ranmsg[0:-1])
print(sel_ran[0:-1])
print("--------------------------------------------------------------------------------------------------------------------------------------------------------")
cursor.close()
del cursor
db_obj.close()
return
def load_scrips():
global scrips
c.pr("I","Loading All Scrips",0)
scrips = s.sql_hash("scrips","scrip","sector:status:is_fetch:search","")
return