def insert_fund_type(nav_fund_type_df, nav_date):
print('insert_fund_type for nav_date: {nav_date}')
dc = DbConfig()
con = dc.get_engine()
new_fund_type_df = nav_fund_type_df.drop_duplicates()
existing_fund_type_df = existing_fund_type()
if not existing_fund_type_df.empty:
existing_fund_types = existing_fund_type_df['fund_type_name'].values
new_fund_type_df = new_fund_type_df.loc[
~new_fund_type_df['fund_type_name'].isin(existing_fund_types)]
if not new_fund_type_df.empty:
no_of_rows = new_fund_type_df.shape[0]
print(
f'{no_of_rows} new fund_type to be added for nav_date: {nav_date}',
)
new_fund_type_df['added_on'] = get_np_array(no_of_rows, nav_date,
'object')
new_fund_type_df.to_sql('fund_type',
con=con,
if_exists='append',
chunksize=1000,
index=False)
print(
f'{no_of_rows} new fund type added in fund_type table for nav_date: {nav_date}...'
)
def insert_company_info(nav_company_names_df, nav_date):
print(f'insert_company_info for nav_date: {nav_date}')
new_company_names_df = nav_company_names_df[['company_name'
]].drop_duplicates()
existing_company_names_df = existing_company_info()
if not existing_company_names_df.empty:
existing_company_names = existing_company_names_df[
'company_name'].values
new_company_names_df = new_company_names_df.loc[
~new_company_names_df['company_name'].isin(existing_company_names)]
# print(f'**new_company_names_df: {new_company_names_df.shape}')
if not new_company_names_df.empty:
no_of_rows = new_company_names_df.shape[0]
print('No of new company info to be added: ', no_of_rows)
added_on_arr = get_np_array(no_of_rows, nav_date, 'object')
new_company_names_df['added_on'] = added_on_arr
dc = DbConfig()
con = dc.get_engine()
new_company_names_df.to_sql('company_info',
con=con,
if_exists='append',
chunksize=1000,
index=False)
print(
f'{no_of_rows} new company info added for nav_date: {nav_date}...'
)
def existing_company_info():
dc = DbConfig()
con = dc.get_engine()
existing_company_sql = '''select distinct company_name,company_id from company_info'''
existing_company_names_df = pd.read_sql_query(sql=existing_company_sql,
con=con,
params=None)
return existing_company_names_df
def existing_scheme_detail():
dc = DbConfig()
con = dc.get_engine()
sql = '''select scheme_id, scheme_code, scheme_name,isin, is_active from scheme_detail '''
existing_scheme_detail_df = pd.read_sql_query(sql=sql,
con=con,
params=None)
return existing_scheme_detail_df
def existing_fund_type():
dc = DbConfig()
con = dc.get_engine()
existing_fund_type_sql = '''select distinct fund_type_name,fund_type_id from fund_type'''
existing_fund_type_df = pd.read_sql_query(sql=existing_fund_type_sql,
con=con,
params=None)
return existing_fund_type_df
def existing_scheme_type():
dc = DbConfig()
con = dc.get_engine()
existing_scheme_type_sql = '''select distinct sch_type_name,sch_type_short_name,sch_type_id from scheme_type '''
existing_scheme_type_df = pd.read_sql_query(sql=existing_scheme_type_sql,
con=con,
params=None)
return existing_scheme_type_df
def last_daily_nav_insert_date():
nav_dt = None
dc = DbConfig()
con = dc.get_engine()
# nav_date_sql = '''select max(nav_date) from daily_nav '''
# for Sqlite because date column does not work with max function
nav_date_sql = '''select nav_date from daily_nav where nav_id in ( select max(nav_id) from daily_nav)'''
date_df = pd.read_sql_query(sql=nav_date_sql, con=con)
if not date_df.empty:
nav_dt = date_df['nav_date'].values[0]
print(f'last daily nav insert date: {nav_dt}')
return nav_dt
def insert_scheme_type(nav_scheme_type_df, nav_date):
print(f'insert_scheme_type for nav_date: {nav_date}')
nav_scheme_type_df = nav_scheme_type_df.drop_duplicates(
subset=['sch_type_name', 'sch_type_short_name'])
existing_scheme_type_df = existing_scheme_type()
if existing_scheme_type_df.shape[0] != 0:
existing_scheme_type_df['sch_type_nm_short_nm'] = existing_scheme_type_df['sch_type_name'] + \
existing_scheme_type_df['sch_type_short_name']
new_sch_types = []
for sch_type_row in nav_scheme_type_df.itertuples(index=False,
name='st_iter'):
sch_type_name = getattr(sch_type_row, 'sch_type_name')
sch_type_short_name = getattr(sch_type_row, 'sch_type_short_name')
sch_type_nm_short_nm = sch_type_name + sch_type_short_name
if sch_type_nm_short_nm not in existing_scheme_type_df[
'sch_type_nm_short_nm'].values:
new_sch_types.append({
'sch_type_name': sch_type_name,
'sch_type_short_name': sch_type_short_name,
'added_on': nav_date
})
new_scheme_type_df = pd.DataFrame(new_sch_types)
else:
new_scheme_type_df = nav_scheme_type_df
new_scheme_type_df['added_on'] = get_np_array(
new_scheme_type_df.shape[0], nav_date, 'object')
if not new_scheme_type_df.empty:
no_of_rows = new_scheme_type_df.shape[0]
print(
f'{no_of_rows} new scheme_type to be added for nav_date : {nav_date}'
)
dc = DbConfig()
con = dc.get_engine()
new_scheme_type_df.to_sql('scheme_type',
con=con,
if_exists='append',
chunksize=500,
index=False)
del new_scheme_type_df
print(
f'{no_of_rows} new scheme type added in scheme_type table for nav_date : {nav_date}'
)
def close_scheme(tr_dt):
today = datetime.strptime(tr_dt, '%d-%b-%Y')
last_nav_date = AppUtils.get_prev_date_str(
today_dt=today, no_of_days_back=EXPIRY_DAY_LIMIT, format='%d-%b-%Y')
date_tuple = AppUtils.get_prev_dates_str(today, EXPIRY_DAY_LIMIT)
print(
f'calling close_scheme for nav_date: {tr_dt}, last_nav_date: {last_nav_date}, expiry day_limit: {EXPIRY_DAY_LIMIT},\n dates:{date_tuple}'
)
dc = DbConfig()
engine = dc.get_engine()
count_sql = f'''select scheme_id , count(scheme_id) count_present
from daily_nav
where nav_date in {date_tuple} and nav_date != last_nav_date
and scheme_id in (select scheme_id from scheme_detail where is_active='Y')
group by scheme_id'''
session = dc.get_session()
try:
session.begin()
print(f'find scheme ids not present in last {EXPIRY_DAY_LIMIT} days.')
scheme_count_df = pd.read_sql_query(con=engine, sql=count_sql)
if not scheme_count_df.empty:
expired_scheme_df = scheme_count_df.loc[
scheme_count_df['count_present'] == EXPIRY_DAY_LIMIT]
print(
f'Nav date: {tr_dt}, No of scheme ids expired [{expired_scheme_df.shape[0]}] in last {EXPIRY_DAY_LIMIT} days.'
)
if not expired_scheme_df.empty:
print(f'Nav date: {tr_dt}, close schemes one by one...')
for ex_sch_row in expired_scheme_df.itertuples(index=False,
name='ex_iter'):
scheme_id = getattr(ex_sch_row, 'scheme_id')
sch_updt_qry = f'''update scheme_detail set is_active ='N', updated_on='{last_nav_date}'
where scheme_id = {scheme_id} '''
del_nav_qry = f'''delete from daily_nav where scheme_id = {scheme_id}
and carry_forward='Y' and last_nav_date='{last_nav_date}' '''
session.execute(sch_updt_qry)
session.execute(del_nav_qry)
print(
f'{expired_scheme_df.shape[0]} scheme ids closed on {last_nav_date}....'
)
session.commit()
except Exception:
session.rollback()
print(f'Exception in close_scheme for nav_date: {tr_dt}..')
traceback.print_exc()
def carry_forward_nav(tr_dt):
today = datetime.strptime(tr_dt, '%d-%b-%Y')
prev_dt_str = AppUtils.get_prev_date_str(today)
print(
f'carry_forward_nav for nav_date: {tr_dt}, previous date: {prev_dt_str}'
)
sql = f'''insert into daily_nav (scheme_id,nav_value,purchase_amt,sell_amt,nav_date,last_nav_date,carry_forward)
select scheme_id, nav_value,purchase_amt,sell_amt,'{tr_dt}' nav_date, last_nav_date, 'Y' carry_forward
from daily_nav where nav_date = '{prev_dt_str}' and scheme_id not in
(select scheme_id from daily_nav where nav_date = '{tr_dt}')'''
dc = DbConfig()
session = dc.get_session()
try:
session.begin()
session.execute(sql)
session.commit()
print(
f'carry_forward_nav executed for current_date: {tr_dt}, previous date: {prev_dt_str}'
)
except Exception:
session.rollback()
print(f'Exception in carry_forward_nav for nav_date: {tr_dt}..')
traceback.print_exc()
def insert_daily_nav(no_of_days=1):
print(f'insert_daily_nav for no_of_days: {no_of_days}')
msg = "Nav deta not found in nav_details table !!!!!!!"
last_nav_date = None
success = False
try:
last_nav_date = last_daily_nav_insert_date()
dc = DbConfig()
con = dc.get_engine()
no_of_days_nxt = 1
if not last_nav_date:
print('Data not available in daily_nav table')
no_of_days_nxt = 0
print('find starting tr_date in nav_detail table')
data_repo = NavDataRepository()
last_nav_date = data_repo.get_last_tr_date()
print(f'last_nav_date: {last_nav_date}')
if last_nav_date:
while no_of_days_nxt <= int(no_of_days):
last_nav_dt = datetime.strptime(last_nav_date, '%d-%b-%Y')
tr_date = AppUtils.get_next_date_str(
last_nav_dt,
no_of_days_nxt=no_of_days_nxt,
format='%d-%b-%Y')
sql = '''select sch_code as scheme_code, mf_name as company_name, sch_name as scheme_name, isin_payout,isin_reinv,nav_value,
purchase_amt,sell_amt,fund_status_type as sch_type_name, scheme_type as sch_type_short_name,fund_type as
fund_type_name,tr_date, 'N' carry_forward from nav_details where tr_date = :tr_date '''
print(f'find nav_details for tr_date: {tr_date}')
nav_detail_df = pd.read_sql_query(sql=sql,
con=con,
params={'tr_date': tr_date})
print(
f'No of NAV_DETAIL rows found [{nav_detail_df.shape[0]}] for tr_date: {tr_date}.'
)
if not nav_detail_df.empty:
populate_master_data_tables(nav_detail_df, tr_date)
daily_nav_df = pd.DataFrame()
daily_nav_df[['nav_value', 'purchase_amt', 'sell_amt', 'nav_date', 'carry_forward']] = \
nav_detail_df[
['nav_value', 'purchase_amt', 'sell_amt', 'tr_date', 'carry_forward']]
daily_nav_df['last_nav_date'] = nav_detail_df['tr_date']
existing_scheme_detail_df = existing_scheme_detail()
sch_ids = []
for nav_row in nav_detail_df.itertuples(
index=False, name='daily_nav_iter'):
sch_nm = getattr(nav_row, 'scheme_name')
sch_nm = sch_nm.strip()
sch_id = existing_scheme_detail_df.loc[
existing_scheme_detail_df['scheme_name'] == sch_nm,
'scheme_id']
if sch_id.empty:
sch_id = existing_scheme_detail_df.loc[
existing_scheme_detail_df['scheme_name'] ==
sch_nm + PAYOUT, 'scheme_id']
if sch_id.empty:
sch_id = existing_scheme_detail_df.loc[
existing_scheme_detail_df['scheme_name'] ==
sch_nm + REINVEST, 'scheme_id']
sch_ids.append(sch_id.values[0])
# sch_cond = (existing_scheme_detail_df['scheme_name'].isin(nav_detail_df['scheme_name']) |
# existing_scheme_detail_df['scheme_name'].isin(nav_detail_df['scheme_name'].str.cat(PAYOUT)) |
# existing_scheme_detail_df['scheme_name'].isin(nav_detail_df['scheme_name'].str.cat(REINVEST))
# daily_nav_df['scheme_id'] = existing_scheme_detail_df.loc[sch_cond,'scheme_id']
daily_nav_df['scheme_id'] = sch_ids
del nav_detail_df
print('insert_daily_nav Nav date: {tr_date}')
daily_nav_df.to_sql('daily_nav',
con=con,
chunksize=1000,
index=False,
if_exists='append')
print(
f'Nav date: {tr_date}, No of Nav rows inserted: {daily_nav_df.shape[0]}'
)
del daily_nav_df
if no_of_days_nxt > 1:
print(f'carry_forward_nav for nav_date: {tr_date}')
carry_forward_nav(tr_date)
print(
f'close_scheme for nav_date: {tr_date}, EXPIRY_DAY_LIMIT: {EXPIRY_DAY_LIMIT}'
)
close_scheme(tr_date)
no_of_days_nxt = no_of_days_nxt + 1
msg = f"Daily Nav details populated for {no_of_days} days starting from {last_nav_date} !!!!!!!"
else:
print('Nav Detail Table is empty!!!!!!!!!!')
success = True
except Exception:
msg = f"Failed to populate daily Nav details for {no_of_days} days starting from {last_nav_date} !!!!!!!"
print(f'Exception in insert_daily_nav for nav_date: {last_nav_date}..')
traceback.print_exc()
return {'message': msg, 'success': success}
def __init__(self):
self.db = DbConfig()
class NavDataRepository:
def __init__(self):
self.db = DbConfig()
def save_nav_history_data(self, search_param=None):
print('save_nav_history_data for search_param: ', search_param)
msg = None
success = False
frmdt = None
try:
if search_param:
frmdt = search_param[
'frmdt'] if 'frmdt' in search_param else '01-Apr-2006'
else:
last_tr_date = self.get_last_tr_date()
frmdt = last_tr_date if last_tr_date else '01-Apr-2006'
print(f'save_nav_history_data From Date: {frmdt}')
dt_obj = datetime.datetime.strptime(frmdt, '%d-%b-%Y')
today = datetime.datetime.today()
prevday = today - datetime.timedelta(days=1)
no_of_days = 0
while dt_obj <= prevday:
frmdt = dt_obj.strftime('%d-%b-%Y')
search_param = {'frmdt': frmdt, 'todt': frmdt}
sql = 'select sch_code from nav_details where tr_date= :frmdt'
result = pd.read_sql_query(sql=sql,
con=self.db.get_engine(),
params={'frmdt': frmdt})
if result.empty:
print('save_nav_history_data for date: ', frmdt)
pr = ParseNavData()
nav_details_df = pr.get_nav_history(search_param)
print(
f'Nav Date: {frmdt}, no of rows collected : {nav_details_df.shape[0]}'
)
nav_details_df.to_sql('nav_details',
con=self.db.get_engine(),
chunksize=2000,
if_exists='append',
index=False)
print(
f'Nav Date: {frmdt}, no of rows saved : {nav_details_df.shape[0]}..'
)
del nav_details_df
print(f'nav data collected for : {frmdt}..')
no_of_days += 1
else:
msg = f'Nav data already available for date: {frmdt}'
dt_obj = dt_obj + datetime.timedelta(days=1)
if no_of_days > 0:
msg = f'Nav data collected for {no_of_days} starting from {frmdt}.'
success = True
except Exception:
msg = f'Failed to collect Nav data for nav_date: {frmdt}'
print(msg)
traceback.print_exc()
print(msg)
return {'message': msg, 'success': success}
def find_nav_history_data(self):
sql = 'select * from nav_details'
result_df = pd.read_sql_query(sql, con=self.db.get_engine())
result_df.to_csv('E:/office work/NavHistory.csv', index=False)
def get_last_tr_date(self):
# tr_date_sql = '''select min(tr_date) nav_date from nav_details'''
tr_date_sql = '''select tr_date as nav_date from nav_details where nav_data_id in
(select min(nav_data_id) from nav_details)'''
date_df = pd.read_sql_query(sql=tr_date_sql, con=self.db.get_engine())
last_nav_date = None if date_df.empty else date_df['nav_date'].values[0]
return last_nav_date
def insert_scheme_detail(nav_scheme_detail_df, nav_date):
print(f'insert scheme detail for nav_date: {nav_date}')
existing_company_names_df = existing_company_info()
existing_fund_type_df = existing_fund_type()
existing_scheme_type_df = existing_scheme_type()
existing_scheme_detail_df = existing_scheme_detail()
has_value = lambda value: True if value and len(value) > 0 else False
rows = []
for nav_sch_row in nav_scheme_detail_df.itertuples(index=False,
name='nav_sch_iter'):
isin_payout = getattr(nav_sch_row, 'isin_payout')
isin_reinv = getattr(nav_sch_row, 'isin_reinv')
sch_code = getattr(nav_sch_row, 'scheme_code')
sch_name = getattr(nav_sch_row, 'scheme_name')
company_name = getattr(nav_sch_row, 'company_name')
sch_type_name = getattr(nav_sch_row, 'sch_type_name')
sch_type_short_name = getattr(nav_sch_row, 'sch_type_short_name')
fund_type_name = getattr(nav_sch_row, 'fund_type_name')
tr_dt = getattr(nav_sch_row, 'tr_date')
# comp_id = company_info_df.loc[company_info_df['company_name'] == company_name, 'company_id'].values[0]
comp_id = get_company_id(existing_company_names_df, company_name)
fnd_typ_id = get_fund_type_id(existing_fund_type_df, fund_type_name)
sch_ty_id = get_sch_type_id(existing_scheme_type_df, sch_type_name,
sch_type_short_name)
# print('fund_type_name: ', fund_type_name, ' fund_type_id: ', fnd_typ_id)
if has_value(isin_reinv) and has_value(isin_payout) and (
(PAYOUT not in sch_name) or (REINVEST not in sch_name)):
p_sch_name = sch_name + PAYOUT
p_row = create_scheme_detail_row(sch_code, p_sch_name, isin_payout,
comp_id, sch_ty_id, fnd_typ_id,
tr_dt)
rows.append(p_row)
r_sch_name = sch_name + REINVEST
r_row = create_scheme_detail_row(sch_code, r_sch_name, isin_reinv,
comp_id, sch_ty_id, fnd_typ_id,
tr_dt)
rows.append(r_row)
else:
isin_val = isin_payout if has_value(isin_payout) else isin_reinv
row = create_scheme_detail_row(sch_code, sch_name, isin_val,
comp_id, sch_ty_id, fnd_typ_id,
tr_dt)
rows.append(row)
if len(rows) > 0:
print(
f'No of new schemes to be inserted {len(rows)} for nav_date: {nav_date}'
)
scheme_detail_df = pd.DataFrame(rows)
scheme_detail_df = scheme_detail_df.drop_duplicates(
subset='scheme_name', keep=False)
# print('******* scheme detail Before: ', scheme_detail_df.shape[0])
if not existing_scheme_detail_df.empty:
print(
'Remove existing schemes to avoid duplicate rows in scheme details table....'
)
existing_sch_names = existing_scheme_detail_df[
'scheme_name'].values
scheme_detail_df = scheme_detail_df.loc[
~scheme_detail_df['scheme_name'].isin(existing_sch_names)]
no_of_rows = scheme_detail_df.shape[0]
scheme_detail_df['added_on'] = get_np_array(no_of_rows, nav_date,
'object')
print(
f'{no_of_rows} new scheme detail will be added for nav_date: {nav_date}'
)
dc = DbConfig()
con = dc.get_engine()
scheme_detail_df.to_sql('scheme_detail',
con=con,
if_exists='append',
chunksize=1000,
index=False)
print(
f'{no_of_rows} new scheme detail is added for nav_date: {nav_date}...'
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, INTEGER, String, DATE, FLOAT, DATETIME, BIGINT
from src.config.db_config import DbConfig
dc = DbConfig()
Base = declarative_base()
class nav_details(Base):
__tablename__ = 'nav_details'
nav_data_id = Column(INTEGER, primary_key=True)
tr_date = Column(DATE, nullable=False)
sch_code = Column(String(50))
mf_name = Column(String(200))
sch_name = Column(String(250))
isin_payout = Column(String(50))
isin_reinv = Column(String(50))
nav_value = Column(FLOAT, nullable=False)
purchase_amt = Column(FLOAT)
sell_amt = Column(FLOAT)
fund_status_type = Column(String(200))
scheme_type = Column(String(200))
fund_type = Column(String(200))
class scheme_type(Base):
__tablename__ = 'scheme_type'
sch_type_id = Column(INTEGER, primary_key=True)
sch_type_short_name = Column(String, nullable=False)
sch_type_name = Column(String, nullable=False)