def resample_data_insert(exchange, timeframe, market_type, resample_tf):
ds_table_name = cu.get_table_name(timeframe, exchange, market_type)
for r_tf in resample_tf:
if r_tf <= timeframe:
print(
'''Resample timeframe : {rtf} equal or less than data source timeframe : {tf}
Skipping {tf} to {rtf} resample'''.format(rtf=r_tf,
tf=timeframe))
continue
#Generate Table names
r_t_n_current = cu.get_table_name(r_tf, exchange, market_type)
r_t_n_new = '''{r_t_n}_new'''.format(r_t_n=r_t_n_current)
backup_table = '''{r_t_n}_old'''.format(r_t_n=r_t_n_current)
#create table if not exsists for resampled timeframe
dao.create_current_ohlcv_table(r_t_n_current) # _new table is created
dao.resample_and_insert_data(ds_table_name, r_t_n_new,
r_tf) # Insert into _new table
#Rename current table to backup table
dao.drop_table(backup_table)
dao.rename_table(r_t_n_current, backup_table)
#Rename new table to current
dao.rename_table(r_t_n_new, r_t_n_current)
def scrip_master_setup(exchange, market_type, path, timeframe):
scrip_master_ex_list = []
metastock_csv_list = get_csv_list_from_path(path)
try:
if len(metastock_csv_list) > 0:
for scrip in metastock_csv_list:
file_name = scrip
scrip_code, company_name = cu.get_scripode_company_name(scrip)
scrip_master_row = (scrip_code, company_name, market_type,
exchange, timeframe, file_name, 'y',
datetime.datetime.now(),
datetime.datetime.now())
scrip_master_ex_list.append(scrip_master_row)
if len(scrip_master_ex_list) > 0:
dao.save_scrip_master(scrip_master_ex_list, exchange)
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
return
def strat_to_db_csv(Strat_obj, ex, m_t, t_f, screen_name):
home = expanduser("~")
url_prefix = get_server_url()
scrip_id_name_list = dao.get_scrip_id_name()
scrip_id_name_url_df = pd.DataFrame(scrip_id_name_list,
columns=['sc', 'sn'])
scrip_id_name_url_df['url'] = url_prefix + scrip_id_name_url_df[
'sc'].str.upper() + f':{ex}'.upper()
scrip_id_name_url_df.set_index('sc', inplace=True)
for k in Strat_obj:
Strat_obj[k].update({'Exchange': ex.upper()})
# print(k,data_value_json[k])
df = pd.DataFrame(Strat_obj)
df_T = df.T
df_T = df_T.applymap(lambda x: "{0:.6f}".format(x)
if isinstance(x, (float)) else x)
f_df = df_T.join(scrip_id_name_url_df)
print(f_df)
candle_dt = f_df['Date'].iloc[0].strftime("%d-%m-%Y")
now = datetime.datetime.now()
nowiso = now.isoformat()
df_json = f_df.to_json(orient='split', date_format='iso')
data_tuple = (screen_name, candle_dt, df_json, nowiso)
# dao.insert_to_screener_value_table(data_tuple)
f_df.to_csv(home + '/output_data/{dt}_{s_n}_ScreenOut.csv'.format(
s_n=screen_name, dt=now.strftime("%d%m%Y")))
# f_df.to_csv('{dt}_{s_n}_ScreenOut.csv'.format(s_n=screen_name,dt=now.strftime("%d%m%Y")))
return df_T
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 active_exchange_ohlcv_run():
#save_logs()
datasource_list = dao.get_datasource_list()
try:
if len(datasource_list) > 0:
for d_s in datasource_list:
try:
exchange = d_s[1]
timeframe = d_s[2]
market_type = d_s[3]
path = d_s[4]
handler = d_s[5]
resample_tf = d_s[6]
module = importlib.import_module('data_handlers.' +
handler)
ohlcv_csv_to_db_func = getattr(module, 'ohlcv_csv_to_db')
ohlcv_csv_to_db_func(exchange, market_type, str(timeframe),
path)
if exchange is not None and timeframe is not None and market_type is not None and resample_tf is not None:
resample_data_insert(exchange, timeframe, market_type,
resample_tf)
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
continue
return
else:
print("datasource_master table can not be blank")
logging.info("datasource_master table can not be blank")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
def setup_datasource():
try:
datasource_list = dao.get_datasource_list()
if len(datasource_list) > 0:
for d_s in datasource_list:
try:
exchange = d_s[1]
timeframe = str(d_s[2])
market_type = d_s[3]
path = d_s[4]
handler = d_s[5]
# Build OHLCV Tables
# dao.create_ohlcv_table(exchange,market_type,timeframe)
# Setup scrip_master for daily
# Get func from hadler path
module = importlib.import_module('data_handlers.' +
handler)
scrip_master_setup_func = getattr(module,
'scrip_master_setup')
scrip_master_setup_func(exchange, market_type, path,
timeframe)
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
continue
else:
print("datasource_master table can not be blank")
logging.info("datasource_master table can not be blank")
except Exception:
stack_trace = traceback.format_exc()
print(stack_trace)
logging.exception(str(stack_trace))
def run():
try:
start = timeit.default_timer()
screener_list = dao.get_screener_list()
# screener_list = [
# ('ohlcv_ns_equity_10080', 'all', 'CHH', None, 'output_csv/','FFM_ns'),
# # ('ohlcv_bo_equity_10080', 'all', 'cmOne', None, 'output_csv/', 'cmOne_bo'),
# ]
all_screener_output = {}
with concurrent.futures.ProcessPoolExecutor() as executor:
for instance, df in zip(range(len(screener_list)),
executor.map(main, screener_list)):
if df is not None:
try:
screen_name = screener_list[instance][5]
all_screener_output[screen_name] = df
except Exception as e:
print(e)
# err.error_log(str(e),run.__name__,'bt_run')
logging.info(str(e))
continue
# execution_time logger
stop = timeit.default_timer()
execution_time = stop - start
print("Program Executed in " + str(execution_time))
except Exception as e:
# err.error_log(str(e),run.__name__,'bt_run')
logging.exception(str(e))
return all_screener_output
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))