def update_scrip_master(scrip_code_id_list):
try:
scrip_code_id_list = tuple(scrip_code_id_list)
connection = db.prod_db_conn()
c = connection.cursor()
# print(scrip_code_id_list)
update_query = '''update scrip_master
set isactive ='t'
where isactive ='y' and id in {id}
'''.format(id = scrip_code_id_list)
c.execute(update_query)
# commit the transaction
connection.commit()
# close the database communication
c.close()
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
close_db_connection(connection)
def get_screener_list():
try:
connection = db.prod_db_conn()
c = connection.cursor()
get_screener_list_query = ''' SELECT data_table_id, sector, screener_file,
output_colomns, csv_output_path, screener_name
FROM public.screener_master sm
where sm.isdeleted = 'n'
'''
c.execute(get_screener_list_query)
screener_list = c.fetchall()
# close the database communication
c.close()
return screener_list
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(connection)
def update_empty_file_status(scrip_code_id_list):
try:
print(scrip_code_id_list)
scrip_code_id_list = tuple(scrip_code_id_list)
connection = db.prod_db_conn()
c = connection.cursor()
# print(scrip_code_id_list)
update_query = '''update scrip_master
set isactive ='e'
where id in {id}
'''.format(id = scrip_code_id_list)
c.execute(update_query)
# commit the transaction
connection.commit()
# close the database communication
c.close()
print(str(scrip_code_id_list) + ":: Scrip code staus updated with flag e for empty file")
logging.info(str(scrip_code_id_list) + " ::Scrip code staus updated with flag e for empty file")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
close_db_connection(connection)
def save_data_error(scrip_code_id_list,error_text,exchange,function_name):
try:
data_error_tuple = [(scrip_code_id_list,error_text,exchange,function_name)]
connection = db.prod_db_conn()
c = connection.cursor()
records_list_template = ','.join(['%s'] * len(data_error_tuple))
insert_query = '''insert into data_error_log (scrip_code_id_list,error,exchange,functin_name)
values {r_l_t}
'''.format(r_l_t= records_list_template)
c.execute(insert_query, data_error_tuple)
# commit the transaction
connection.commit()
# close the database communication
c.close()
print(exchange + " Data error record inserted successfully in :: data_error_log table")
logging.info(exchange + " Data error record inserted successfully in :: data_error_log table")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
close_db_connection(connection)
def save_scrip_master(scrip_master_ex_list, exchange):
try:
connection = db.prod_db_conn()
c = connection.cursor()
records_list_template = ','.join(['%s'] * len(scrip_master_ex_list))
insert_query = '''insert into scrip_master (scrip_code, company_name,
market_type, exchange,timeframe,file_name,isactive, created_timestamp,
last_updated)
values {r_l_t}
ON CONFLICT (scrip_code,file_name)
DO UPDATE SET
market_type = excluded.market_type,
exchange = excluded.exchange,
company_name = excluded.company_name
'''.format(r_l_t= records_list_template)
c.execute(insert_query, scrip_master_ex_list)
# commit the transaction
connection.commit()
# close the database communication
c.close()
print("Records inserted successfully in :: scrip_master for exchange " + exchange)
logging.info("Records inserted successfully in :: scrip_master for exchange " + str(exchange))
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
close_db_connection(connection)
def check_table_exists(table_name):
try:
connection = db.prod_db_conn()
c = connection.cursor()
query = ''' SELECT EXISTS
(
SELECT 1
FROM pg_tables
WHERE tablename = '{t_n}'
);'''.format(t_n = table_name)
c.execute(query)
result = c.fetchone()[0]
# close the database communication
c.close()
return result
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(connection)
def insert_to_screener_value_table(data_tuple):
try:
connection = db.prod_db_conn()
c = connection.cursor()
screener_value_insert = ''' insert into screener_value_table (screener_name,date_time,data_json,last_updated)
values {d_t}
on conflict (screener_name, date_time)
do update set
data_json = excluded.data_json,
last_updated = excluded.last_updated
'''.format(d_t=data_tuple)
c.execute(screener_value_insert)
connection.commit()
# close the database communication
c.close()
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(connection)
def get_datasource_list():
try:
connection = db.prod_db_conn()
c = connection.cursor()
get_datasource_list_query = ''' select id,exchange,timeframe_m,market_type,path,
handler_path,resample_tf
from datasource_master where isactive = 'y' '''
c.execute(get_datasource_list_query)
datasource_list = c.fetchall()
# close the database communication
c.close()
return datasource_list
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(connection)
def get_scrip_list(exchange,market_type,timeframe):
try:
connection = db.prod_db_conn()
c = connection.cursor()
get_scrip_list_query = ''' select scrip_code, company_name,sm.file_name,
dm.timeframe_m as tf,sm.id
from scrip_master sm
inner join datasource_master dm on dm.exchange = sm.exchange
and dm.timeframe_m = sm.timeframe and dm.market_type = sm.market_type
where dm.timeframe_m = {tf} and dm.exchange = '{ex}'
and dm.market_type = '{mt}' and sm.isactive in ('y','t')
'''.format(ex=exchange, mt=market_type, tf=timeframe)
c.execute(get_scrip_list_query)
scrip_list = c.fetchall()
# close the database communication
c.close()
return scrip_list
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(connection)
def drop_table(table_name):
try:
connection = db.prod_db_conn()
c = connection.cursor()
if table_name is not None:
drop_old_table = """drop table if exists {table};""".format(table = table_name)
c.execute(drop_old_table)
connection.commit()
print(table_name + " : table deleted successfully")
logging.info(table_name + " : table deleted successfully")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
close_db_connection(connection)
def rename_table(from_table,to_table):
try:
connection = db.prod_db_conn()
c = connection.cursor()
if from_table is not None and to_table is not None:
rename_current_table_query = """ALTER TABLE IF EXISTS {frm_table}
RENAME TO {to_table};""".format(frm_table = from_table,to_table = to_table)
c.execute(rename_current_table_query)
connection.commit()
print("""Table rename from {f_t} to {to_t}""".format(f_t =from_table,to_t = to_table))
logging.info("""Table rename from {f_t} to {to_t}""".format(f_t =from_table,to_t = to_table))
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
close_db_connection(connection)
def get_ticker_names():
connection = ds.prod_db_conn()
cursor = connection.cursor()
record = return_record(
cursor,
"select scrip_code,count(*) as c from ohlcv_bo_equity_1440 group by scrip_code order by c desc"
)
pass
def disable_scrip_code(timeframe,exchange,mt,tn):
try:
r_conn = db.prod_db_conn()
c = r_conn.cursor()
c.callproc('disable_scrip_code', [ap.mark_inactive_days,timeframe,exchange,mt,tn])
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(r_conn)
def create_current_ohlcv_table(d_t_n):
table_name = """{tn}_new""".format(tn = d_t_n)
drop_table = """DROP TABLE IF EXISTS {table}""".format(table = table_name)
command = """
CREATE TABLE if not exists {table}
(
id serial PRIMARY KEY,
scrip_code character varying(20) NOT NULL,
datetime timestamp,
Open real,
high real,
low real,
close real,
volume real,
UNIQUE (scrip_code, datetime)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
""".format(table = table_name)
try:
r_conn = db.prod_db_conn()
c = r_conn.cursor()
c.execute(drop_table)
r_conn.commit()
c.execute(command)
# commit the transaction
r_conn.commit()
# close the database communication
c.close()
print(table_name + " : table created successfully")
logging.info(table_name + " : table created successfully")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(r_conn)
def benchmark_data(exchange, market_type, res, data_len):
try:
connection = db.prod_db_conn()
c = connection.cursor()
# Get name of benchmark datas for the exchange sectors
c.execute('''select benchmark from benchmark_master
where exchange = '{ex}' '''.format(ex=exchange.lower()))
benchmark_id = c.fetchall()[0][0]
# Data fetch required candles from DB by query
df = pd.read_sql_query('''Select * from (
select datetime,open,high,low,close,volume
from ohlcv_index_{mt}_{r}
where scrip_code = '{bid}'
ORDER BY datetime DESC limit {c} ) as c_d
order by datetime ASC
'''.format(bid=benchmark_id,
mt=market_type,
r=str(res),
c=data_len),
con=connection)
# Create a Data Feed
data = btfeeds.PandasData(dataname=df,
timeframe=bt.TimeFrame.Days,
compression=1,
datetime=0,
high=2,
low=3,
open=1,
close=4,
volume=5,
openinterest=-1)
benchmark_name = benchmark_id.split('.')[1]
return data, benchmark_name
except Exception as e:
logging.exception(str(e))
finally:
dao.close_db_connection(connection)
def benchmark_name(exchange):
try:
connection = db.prod_db_conn()
c = connection.cursor()
c.execute('''select benchmark from benchmark_master
where exchange = '{ex}' '''.format(ex=exchange.lower()))
benchmark_id = c.fetchall()[0][0]
benchmark_name = benchmark_id.split('.')[1]
return benchmark_name
except Exception as e:
logging.exception(str(e))
finally:
dao.close_db_connection(connection)
def create_ohlcv_table(exchange,market_type,timeframe):
try:
table_name = """ohlcv_{ex}_{mt}_{tf}""".format(ex=exchange,mt=market_type,tf=timeframe)
command = """
CREATE TABLE if not exists {table}
(
id serial PRIMARY KEY,
scrip_code character varying(20) NOT NULL,
datetime timestamp,
Open real,
high real,
low real,
close real,
volume real,
UNIQUE (scrip_code, datetime)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
""".format(table = table_name)
r_conn = db.prod_db_conn()
c = r_conn.cursor()
c.execute(command)
# commit the transaction
r_conn.commit()
# close the database communication
c.close()
print(table_name + " : table for Exchange", exchange,"created successfully")
logging.info(table_name + " : table for Exchange", exchange,"created successfully")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(r_conn)
def resample_and_insert_data(from_table,to_table,resample_tf):
try:
r_conn = db.prod_db_conn()
c = r_conn.cursor()
if check_table_exists(from_table) and resample_tf is not None and resample_tf==10080:
print('Resampling Daily to Weekly')
c.callproc('resample_daily_to_weekly', [from_table,to_table])
elif check_table_exists(from_table) and resample_tf is not None and resample_tf==43200:
print('Resampling Daily to Monthly')
c.callproc('resample_daily_to_monthly', [from_table,to_table])
else:
print(from_table +": table does not exists for resampling")
logging.info(from_table +": table does not exists for resampling")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(r_conn)
def get_scrip_id_name():
try:
connection = db.prod_db_conn()
c = connection.cursor()
scrip_id_name = ''' select scrip_code, company_name from scrip_master
'''
c.execute(scrip_id_name)
scrip_id_name_list = c.fetchall()
# close the database communication
c.close()
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
finally:
close_db_connection(connection)
return scrip_id_name_list
def data_feed(scrip_code, ex, m_t, t_f, cerebro, min_data):
try:
connection = db.prod_db_conn()
c = connection.cursor()
print(
f'Fetching data for {scrip_code} scrip(s) from exhange {ex}, timeframe {t_f} & market {m_t}'
)
table_name = su.get_table_name(t_f, ex, m_t)
if scrip_code == 'all': ## For Ticker list Handling
try:
p_s_f_amt = ap.penny_stock_filter[ex]
except KeyError as e:
print('Exchange', e,
'Does not exsist in Application Properties')
print(
'Ensure Penny Stock filter Exchange amount pair in Application Properties'
)
# Data fetch required candles from DB by query
c.execute(
'''with scrips_with_close_filter AS (select scdtc.scrip_code from (select distinct on (scrip_code) scrip_code,datetime,close
FROM {t_n}
order by scrip_code, datetime DESC) as scdtc
inner join scrip_master sm on scdtc.scrip_code = sm.scrip_code
where scdtc.close > {sf} and sm.isactive in ('t' , 'y'))
SELECT scrip_code, array_agg(Array[datetime::text,open::text,high::text,low::text,close::text,volume::text])
FROM (SELECT n.datetime,n.open,n.high,n.low,n.close,n.volume,n.scrip_code,RANK () OVER (PARTITION BY n.scrip_code ORDER BY n.scrip_code,datetime DESC) sc_rank
FROM {t_n} as n
inner join scrips_with_close_filter cf on n.scrip_code = cf.scrip_code
ORDER BY n.scrip_code, n.datetime ASC) as ol
where ol.sc_rank <= {candles}
GROUP BY ol.scrip_code;'''.format(t_n=table_name,
sf=p_s_f_amt,
candles=min_data))
scrip_data_list = c.fetchall()
for scrip in scrip_data_list:
try:
scrip_name = scrip[0]
print(scrip_name)
df = pd.DataFrame.from_records(scrip[1],
columns=[
'datetime', 'open',
'high', 'low', 'close',
'volume'
])
df2 = df.astype({
'datetime': 'datetime64[ns]',
'open': 'float64',
'high': 'float64',
'low': 'float64',
'close': 'float64',
'volume': 'float64'
})
# print(df2)
if len(df2) < min_data:
print('Minimum candles not present, skipping',
scrip_name)
continue
# Create a Data Feed
data = btfeeds.PandasData(dataname=df2,
timeframe=bt.TimeFrame.Days,
compression=1,
datetime=0,
high=2,
low=3,
open=1,
close=4,
volume=5,
openinterest=-1)
# cerebro.resampledata(data, timeframe=bt.TimeFrame.Weeks,
# compression=1, name=scrip_name)
cerebro.adddata(data, name=scrip_name)
except Exception as e:
# err.error_log(str(e),data_feed.__name__,'bt_run')
logging.exception(str(e))
elif isinstance(scrip_code, tuple):
for scrip in scrip_code:
try:
df = pd.read_sql_query('''Select * from (
SELECT datetime,open,high,low,close,volume
FROM "{t_n}"
WHERE scrip_code = '{sc}'
ORDER BY datetime DESC limit {c} ) as c_d
order by datetime ASC
'''.format(t_n=table_name,
sc=scrip,
c=min_data),
con=connection)
if len(df) < min_data:
print('Minimum candles not present')
continue
# Create a Data Feed
data = btfeeds.PandasData(dataname=df,
timeframe=bt.TimeFrame.Days,
compression=1,
datetime=0,
high=2,
low=3,
open=1,
close=4,
volume=5,
openinterest=-1)
cerebro.adddata(data, name=scrip)
except Exception as e:
# err.error_log(str(e),data_feed.__name__,'bt_run')
logging.exception(str(e))
else: ## For Single Ticker Handling
df = pd.read_sql_query('''
Select * from (
SELECT datetime,open,high,low,close,volume
FROM "{t_n}"
WHERE scrip_code = '{sc}'
ORDER BY datetime DESC limit {c} ) as c_d
order by datetime ASC
'''.format(t_n=table_name,
sc=scrip_code,
c=min_data),
con=connection)
# print(df)
if len(df) < min_data:
print('Minimum candles not present')
return
# Create a Data Feed
data = btfeeds.PandasData(dataname=df,
timeframe=bt.TimeFrame.Days,
compression=1,
datetime=0,
high=2,
low=3,
open=1,
close=4,
volume=5,
openinterest=-1)
cerebro.adddata(data, name=scrip_code)
## Add Benchmark Data to Cerebro
b_data, benchmark_name = benchmark_data(ex, m_t, t_f, min_data)
cerebro.adddata(b_data, name=benchmark_name)
print('Benchmark Added')
return cerebro
except Exception as e:
logging.exception(str(e))
finally:
dao.close_db_connection(connection)
def ohlcv_csv_to_db(exchange, market_type, timeframe, path):
scrip_list = dao.get_scrip_list(exchange, market_type, timeframe)
if len(scrip_list) > 0:
if cu.isDirectoryAndPathExists(path):
if cu.isDirectoryEmpty(path):
print(path + ":: Directory is empty")
logging.info(path + ":: Directory is empty")
else:
fromDate = ap.fromDate
r_conn = db.prod_db_conn()
c = r_conn.cursor()
scrip_code_id_list = []
empty_file_scrip_code_id = []
error_text = ""
dynamic_table_name = cu.get_table_name(timeframe, exchange,
market_type)
temp_ohlcv_query = dao.create_temp_ohlcv_table()
c.execute(temp_ohlcv_query) # Temp table session start
logging.info("ohlcv_temp table created successfully..")
print("ohlcv_temp table created successfully..")
dao.create_current_ohlcv_table(dynamic_table_name)
table_name = '{d_t_n}_new'.format(d_t_n=dynamic_table_name)
insert_ohlcv_query = """insert into {t_n}(scrip_code,datetime,open,high,low,close,volume)
select scrip_code,datetime::timestamp,open,high,low,close,volume
from ohlcv_temp where datetime::timestamp >= '{frm_date}';
""".format(t_n=table_name,
frm_date=fromDate)
# print(insert_ohlcv_query)
logging.info(
"Copying CSV data to ohlcv_temp table is in Progress..")
print("Copying CSV data to ohlcv_temp table is in Progress..")
for scrip_data in scrip_list:
scrip_code_id = scrip_data[4]
# scrip_code = scrip_data[0]
# company_name = scrip_data[1]
file_name = scrip_data[2]
# scrip_csv_name = scrip_code + '#' + company_name +'.csv'
csv_complete_pth = path + file_name
try:
if cu.isFileExists(csv_complete_pth):
if not cu.isEmpty(csv_complete_pth):
with open(csv_complete_pth, 'r') as f:
next(f) # Skip the header row.
c.copy_from(f,
'ohlcv_temp',
sep=',',
columns=[
'scrip_code', 'interval',
'datetime', 'open', 'high',
'low', 'close', 'volume'
])
else:
empty_file_scrip_code_id.append(scrip_code_id)
print('File is Empty', file_name)
logging.info('File is Empty' + str(file_name))
continue
else:
scrip_code_id_list.append(scrip_code_id)
error_text = "File Not Found"
print('File not found for scrip', file_name)
logging.info('File not found for scrip' +
str(file_name))
continue
except psycopg2.IntegrityError as e:
stack_trace = traceback.format_exc()
print('Possible Duplicate Entries', file_name)
logging.exception(str(stack_trace))
print(str(e) + stack_trace)
continue
except psycopg2.DataError as e:
scrip_code_id_list.append(scrip_code_id)
error_text = "DataError"
print('Possible DataError', file_name)
logging.exception(str(e))
print(e)
continue
except psycopg2.Error as e:
stack_trace = traceback.format_exc()
print('General Error in', file_name)
logging.exception(str(stack_trace))
print(str(e) + stack_trace)
continue
except Exception as e:
stack_trace = traceback.format_exc()
print(str(e) + stack_trace)
print('General Error in111', file_name)
logging.exception(str(stack_trace))
continue
try:
logging.info(
"Started transfering data from ohlcv_temp table to main table..."
)
print(
"Started transfering data from ohlcv_temp table to main table..."
)
c.execute(insert_ohlcv_query)
r_conn.commit()
print(
"Records inserted successfully from temp_table to ::" +
table_name)
logging.info(
"Records inserted successfully from temp_table to ::" +
table_name)
old_ohlv_table_name = """{d_t_n}_old""".format(
d_t_n=dynamic_table_name)
dao.drop_table(old_ohlv_table_name)
from_table_name = dynamic_table_name
to_table_name = old_ohlv_table_name
dao.rename_table(from_table_name, to_table_name)
from_table_name = table_name
to_table_name = dynamic_table_name
dao.rename_table(from_table_name, to_table_name)
if len(scrip_code_id_list) > 0:
dao.save_data_error(scrip_code_id_list, error_text,
exchange, ohlcv_csv_to_db.__name__)
dao.update_scrip_master(scrip_code_id_list)
if len(empty_file_scrip_code_id) > 0:
## update the scrip_code status to 'e' if any file is empty
dao.update_empty_file_status(empty_file_scrip_code_id)
### update isactive='n' in scrip_code table if we did not
### receive data for any scrip_code in last 7 Days
### update isactive='t' in scrip_code table if we did not
### receive data for any scrip_code within 7 days (temporarily in active)
dao.disable_scrip_code(timeframe, exchange, market_type,
dynamic_table_name)
print("scrip_code disabled successsfully..")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
## finally block Alwasy execute whether exception occured or not
## to close db connection
finally:
c.close()
r_conn.close() # This closes temp_table session
else:
print(path + " :: Directory don't exists")
logging.info(path + " :: Directory don't exists")
else:
print("All scrip code is in active for exchange :" + exchange + "," +
timeframe)
logging.info("All scrip code is in active for exchange :" +
str(exchange) + "," + str(timeframe))
