def bhav_fetch(request):
# for quick debugging
#
# import pdb; pdb.set_trace()
#
# breakpoint()
debug_level = 1
amfi_rank_dict = {}
dematsum_list = []
print("load amfi")
# load rank
amfi_load_rank(amfi_rank_dict)
print("load dematsum")
dematsum_load_stocks(dematsum_list)
# declaring template
template = "bhav/bhav_list.html"
url = bhav_url()
print(url)
response = urllib.request.urlopen(url)
status_code = response.getcode()
# successful
if status_code == 200:
print('deleted existing Bhav data')
# delete existing data
Bhav.objects.all().delete()
df = pd.read_csv(io.BytesIO(response.read()), compression='zip', sep=',', header=None)
data_set = df.to_csv(header=False, index=False)
io_string = io.StringIO(data_set)
next(io_string)
print('first record', io_string)
skipped_records = 0
for column in csv.reader(io_string, delimiter=',', quotechar='"'):
bhav_ticker = column[0].strip()
bhav_series = column[1].strip()
bhav_open = column[2].strip()
bhav_high = column[3].strip()
bhav_low = column[4].strip()
bhav_close = column[5].strip()
bhav_last = column[6].strip()
bhav_prevclose = column[7].strip()
bhav_tottrdqty = column[8].strip()
bhav_tottrdval = column[9].strip()
bhav_timestamp = column[10].strip()
bhav_totaltrades = column[11].strip()
bhav_isin = column[12].strip()
if bhav_series == 'EQ':
if comm_func_ticker_match(bhav_ticker, amfi_rank_dict, dematsum_list):
_, created = Bhav.objects.update_or_create(
bhav_ticker=bhav_ticker,
bhav_price=bhav_last,
bhav_isin=bhav_isin
)
else:
skipped_records += 1
lastrefd_update("bhav")
print('Skipped records ', skipped_records)
print('Completed updating Bhav data')
# context = {}
# render(request, template, context)
#
return HttpResponseRedirect(reverse("bhav-list"))
def corpact_upload(request):
# for quick debugging
#
# import pdb; pdb.set_trace()
#
# breakpoint()
amfi_rank_dict = {}
dematsum_list = []
debug_level = 1
# declaring template
template = "corpact/corpact_list.html"
data = Corpact.objects.all()
# GET request returns the value of the data with the specified key.
if request.method == "GET":
return render(request, template)
print("load amfi")
# load rank
amfi_load_rank(amfi_rank_dict)
print("load dematsum")
dematsum_load_stocks(dematsum_list)
print('Deleted existing Corpact data for years: ')
# delete existing records
Corpact.objects.all().delete()
# req_file = request.FILES['file']
corp_act_stock_list = []
corp_act_year_list = []
corp_act_bonus_stock_year_dict = {}
corp_act_buyback_stock_year_dict = {}
corp_act_dividend_stock_year_dict = {}
ca_score = {}
for req_file in request.FILES.getlist("files"):
# let's check if it is a csv file
print(req_file)
if req_file.name.endswith('.xls') or req_file.name.endswith('.xlsx'):
# get worksheet name
# print('temporary file path:', req_file.temporary_file_path)
print(req_file)
if True:
wb = openpyxl.load_workbook(req_file)
print(wb.sheetnames)
sheet_name = wb.sheetnames[0]
print(sheet_name)
ws = wb[sheet_name]
df = pd.DataFrame(ws.values)
else:
xl = pd.ExcelFile(req_file)
if debug_level > 0:
print(xl.sheet_names)
# single worksheet - Data
sheet_name = xl.sheet_names[0]
df = xl.parse(sheet_name)
# can be 'Data'
# can be 'Average MCap Jan Jun 2020'
if sheet_name != 'Data':
print("sheet name changed to", sheet_name)
# ignore top two line : Average Market Capitalization of listed companies during the six months ended
# remove top two line from dataframe
df = df.iloc[2:]
if debug_level > 0:
print("old columns : ")
print(df.columns)
# change column name of data frame
columns_list = ['tl_stock_name', 'tl_isin', 'tl_bat', 'tl_der', 'tl_roce3',
'tl_roe3', 'tl_dpr2', 'tl_sales2', 'tl_profit5', 'tl_icr',
'tl_pledge', 'tl_low_3y', 'tl_low_5y']
df.columns = columns_list
if debug_level > 0:
print("new columns : ")
print(df.columns)
# Keep only top 1000 entries
df = df.iloc[:1000]
# round avg_mcap
# df = df.round({'avg_mcap' : 1})
# covert to numeric
# df[["avg_mcap"]] = df[["avg_mcap"]].apply(pd.to_numeric)
df[["avg_mcap"]] = df[["avg_mcap"]].astype(int)
# drop columns that are not required
# skip_columns_list = ['bse_mcap', 'nse_mcap', 'mse_symbol', 'mse_mcap']
# df.drop(skip_columns_list, axis=1, inplace=True)
data_set = df.to_csv(header=True, index=False)
if req_file.name.endswith('.csv'):
data_set = req_file.read().decode('UTF-8')
if not (req_file.name.endswith('.csv') or req_file.name.endswith('.xls') or req_file.name.endswith('.xlsx')):
messages.error(request, req_file.name + ' : THIS IS NOT A XLS/XLSX/CSV FILE.')
return HttpResponseRedirect(reverse("corpact-list"))
# setup a stream which is when we loop through each line we are able to handle a data in a stream
io_string = io.StringIO(data_set)
next(io_string)
for row in csv.reader(io_string, delimiter=',', quotechar='"'):
[security_code, security_name, company_name, ex_date, purpose,
record_date, bc_state_date, bc_end_date, nd_start_date, nd_end_date,
actual_payment_date] = row
security_name = security_name.strip()
purpose = purpose.strip()
ex_date = ex_date.strip()
if debug_level > 1:
print(security_name)
try:
if purpose == '-' or purpose == 'Purpose':
continue
if ex_date != 'Ex Date':
# sometime the space is separator and sometime hyphen is separator
if re.search('-', ex_date):
split_char = "-"
else:
split_char = " "
date_arr = ex_date.split(split_char)
if debug_level > 2:
print('date: ', date_arr)
current_year = int(date_arr[2])
# note: sometime date is stored as 17 and sometime as 2017
# to fix the year.
if current_year >= 2000:
current_year = current_year - 2000
if debug_level > 2:
print('year : ', current_year)
if security_name not in corp_act_stock_list:
corp_act_stock_list.append(security_name)
if current_year not in corp_act_year_list:
corp_act_year_list.append(current_year)
if re.search('Bonus', purpose):
if (security_name, current_year) not in corp_act_bonus_stock_year_dict:
corp_act_bonus_stock_year_dict[security_name, current_year] = 1
elif re.search('Buy Back', purpose):
if (security_name, current_year) not in corp_act_buyback_stock_year_dict:
corp_act_buyback_stock_year_dict[security_name, current_year] = 1
elif re.search('Dividend', purpose):
if (security_name, current_year) not in corp_act_dividend_stock_year_dict:
corp_act_dividend_stock_year_dict[security_name, current_year] = 1
else:
print('wrong purpose', purpose)
except IndexError:
print('IndexError', row)
traceback.print_exc()
except NameError:
print('NameError', row)
traceback.print_exc()
except AttributeError:
print('AttributeError', row)
traceback.print_exc()
print(corp_act_stock_list)
print(corp_act_year_list)
print(corp_act_bonus_stock_year_dict)
print(corp_act_buyback_stock_year_dict)
print(corp_act_dividend_stock_year_dict)
for security_name in sorted(set(corp_act_stock_list)):
buyback_found = 0
bonus_found = 0
dividend_found = 0
for current_year in sorted(set(corp_act_year_list), key=int):
try:
if (security_name, current_year) in corp_act_buyback_stock_year_dict:
buyback_found += 1
if (security_name, current_year) in corp_act_bonus_stock_year_dict:
bonus_found += 1
if (security_name, current_year) in corp_act_dividend_stock_year_dict:
dividend_found += 1
if debug_level > 2:
print(security_name, current_year)
except KeyError:
print('KeyError failed lookup :', security_name, current_year)
# do not include stock split as that doesn't mean giving back
total_give_back = buyback_found + bonus_found + dividend_found
ca_score[security_name, 'bonus'] = bonus_found
ca_score[security_name, 'buyback'] = buyback_found
ca_score[security_name, 'dividend'] = dividend_found
ca_score[security_name, 'total'] = total_give_back
for security_name in corp_act_stock_list:
# only top 500 to meet heroku limits of total 10k rows
# also cover any additional non top 500 stocks holding
if comm_func_ticker_match(security_name, amfi_rank_dict, dematsum_list):
_, created = Corpact.objects.update_or_create(
ca_ticker=security_name,
ca_total=ca_score[security_name, 'total'],
ca_bonus=ca_score[security_name, 'bonus'],
ca_buyback=ca_score[security_name, 'buyback'],
ca_dividend=ca_score[security_name, 'dividend']
)
# context = {}
# render(request, template, context)
lastrefd_update("corpact")
#
print('Completed loading new Corpact data')
return HttpResponseRedirect(reverse("corpact-list"))
def bhav_upload(request):
# for quick debugging
#
# import pdb; pdb.set_trace()
#
# breakpoint()
amfi_rank_dict = {}
dematsum_list = []
debug_level = 1
# declaring template
template = "bhav/bhav_list.html"
data = Bhav.objects.all()
# GET request returns the value of the data with the specified key.
if request.method == "GET":
return render(request, template)
print("load amfi")
# load rank
amfi_load_rank(amfi_rank_dict)
print("load dematsum")
dematsum_load_stocks(dematsum_list)
req_file = request.FILES['file']
# let's check if it is a csv file
skipped_records = 0
if req_file.name.endswith('.xls') or req_file.name.endswith('.xlsx'):
# get worksheet name
# print('temporary file path:', req_file.temporary_file_path)
print(req_file)
if True:
wb = openpyxl.load_workbook(req_file)
print(wb.sheetnames)
sheet_name = wb.sheetnames[0]
print(sheet_name)
ws = wb[sheet_name]
df = pd.DataFrame(ws.values)
else:
xl = pd.ExcelFile(req_file)
if debug_level > 0:
print(xl.sheet_names)
# single worksheet - Data
sheet_name = xl.sheet_names[0]
df = xl.parse(sheet_name)
# can be 'Data'
# can be 'Average MCap Jan Jun 2020'
if sheet_name != 'Data':
print("sheet name changed to", sheet_name)
# ignore top two line : Average Market Capitalization of listed companies during the six months ended
# remove top two line from dataframe
df = df.iloc[2:]
if debug_level > 0:
print("old columns : ")
print(df.columns)
# change column name of data frame
columns_list = ['SYMBOL', 'SERIES', 'OPEN',
'HIGH', 'LOW', 'CLOSE', 'LAST',
'PREVCLOSE', 'TOTTRDQTY', 'TOTTRDVAL', 'TIMESTAMP',
'TOTALTRADES', 'ISIN']
df.columns = columns_list
if debug_level > 0:
print("new columns : ")
print(df.columns)
# Keep only top 1000 entries
df = df.iloc[:1000]
# round avg_mcap
# df = df.round({'avg_mcap' : 1})
# covert to numeric
# df[["avg_mcap"]] = df[["avg_mcap"]].apply(pd.to_numeric)
df[["avg_mcap"]] = df[["avg_mcap"]].astype(int)
# drop columns that are not required
# skip_columns_list = ['bse_mcap', 'nse_mcap', 'mse_symbol', 'mse_mcap']
# df.drop(skip_columns_list, axis=1, inplace=True)
data_set = df.to_csv(header=True, index=False)
if req_file.name.endswith('.csv'):
data_set = req_file.read().decode('UTF-8')
if not (req_file.name.endswith('.csv') or req_file.name.endswith('.xls') or req_file.name.endswith('.xlsx')):
messages.error(request, req_file.name + ' : THIS IS NOT A XLS/XLSX/CSV FILE.')
return HttpResponseRedirect(reverse("bhav-list"))
# delete existing records
print('Deleted existing Bhav data')
Bhav.objects.all().delete()
# setup a stream which is when we loop through each line we are able to handle a data in a stream
io_string = io.StringIO(data_set)
next(io_string)
for column in csv.reader(io_string, delimiter=',', quotechar='"'):
bhav_ticker = column[0].strip()
bhav_series = column[1].strip()
bhav_open = column[2].strip()
bhav_high = column[3].strip()
bhav_low = column[4].strip()
bhav_close = column[5].strip()
bhav_last = column[6].strip()
bhav_prevclose = column[7].strip()
bhav_tottrdqty = column[8].strip()
bhav_tottrdval = column[9].strip()
bhav_timestamp = column[10].strip()
bhav_totaltrades = column[11].strip()
bhav_isin = column[12].strip()
# skip some rows
# retail investors series : EQ and BE
# EQ - intra day trade allowed (normal trading)
# BE - trade to trade/T-segment : (no intra day squaring allowed : (accept/give delivery)
if bhav_series == 'EQ':
if comm_func_ticker_match(bhav_ticker, amfi_rank_dict, dematsum_list):
_, created = Bhav.objects.update_or_create(
bhav_ticker=bhav_ticker,
bhav_price=bhav_last,
bhav_isin=bhav_isin
)
else:
skipped_records += 1
# context = {}
# render(request, template, context)
lastrefd_update("bhav")
#
print('skipped records: ', skipped_records)
print('Completed loading new Bhav data')
return HttpResponseRedirect(reverse("bhav-list"))
def ftwhl_fetch(request):
# for quick debugging
#
# import pdb; pdb.set_trace()
#
# breakpoint()
debug_level = 1
amfi_rank_dict = {}
dematsum_list = []
print("load amfi")
# load rank
amfi_load_rank(amfi_rank_dict)
print("load dematsum")
dematsum_load_stocks(dematsum_list)
# declaring template
template = "ftwhl/ftwhl_list.html"
data = Ftwhl.objects.all()
url = ftwhl_url()
print(url)
http = urllib3.PoolManager()
response = http.request('GET', url)
print(response.status)
if response.status == 200:
print('delete existing ftwhl data')
Ftwhl.objects.all().delete()
resp_data = response.data
# response data
io_string = io.StringIO(resp_data.decode('utf-8'))
# skip first three rows
next(io_string)
next(io_string)
next(io_string)
for column in csv.reader(io_string, delimiter=',', quotechar='"'):
if debug_level > 1:
print(column)
column[0] = column[0].strip()
# ignore column[1] Series : EQ
column[2] = column[2].strip()
column[3] = column[3].strip()
column[4] = column[4].strip()
column[5] = column[5].strip()
if comm_func_ticker_match(ftwhl_ticker, amfi_rank_dict, dematsum_list):
_, created = Ftwhl.objects.update_or_create(
ftwhl_ticker=column[0],
ftwhl_high=column[2],
ftwhl_high_dt=column[3],
ftwhl_low=column[4],
ftwhl_low_dt=column[5])
#
lastrefd_update("Ftwhl")
return HttpResponseRedirect(reverse("ftwhl-list"))