Skip to content

Instantly share code, notes, and snippets.

@Lycan47
Last active February 12, 2021 14:52
Show Gist options
  • Save Lycan47/fe380fa239ad1eafdfa42519158d592c to your computer and use it in GitHub Desktop.
Save Lycan47/fe380fa239ad1eafdfa42519158d592c to your computer and use it in GitHub Desktop.
Helps in Reconciling the General Accountings behind Functional Testing of BaNCS Insurance
import pandas as pd
import numpy as np
import openpyxl
from openpyxl.styles import PatternFill, Font
# Drops Events which are If applicable to policy
def remove_if_applicable_events(df_example):
i = 0
while i < len(df_example['Event']):
if pd.isna(df_example.iloc[i, 0]):
i += 1
elif 'if' in df_example.iloc[i, 0]:
df_example.drop([i, i + 1], inplace=True)
df_example.reset_index(drop=True, inplace=True)
else:
i += 1
return df_example
# Fills the Policy, Products & benefits names in the evidence sheet
def fill_policy_details(df_accounting, sheet_td):
benefits = list(set(df_accounting['ATN_BEN'].dropna()))
policy = list(set(df_accounting['POLICYNUMBER'].dropna()))
product = list(set(df_accounting['ATN_PRD'].dropna()))
if len(benefits) > 1:
sheet_td.cell(row=3, column=3, value=' & '.join(benefits))
else:
sheet_td.cell(row=3, column=3, value=benefits[0])
if len(policy) > 1:
sheet_td.cell(row=2, column=2, value=' & '.join(policy))
else:
sheet_td.cell(row=2, column=2, value=policy[0])
if len(product) > 1:
sheet_td.cell(row=3, column=2, value=' & '.join(product))
else:
sheet_td.cell(row=3, column=2, value=product[0])
# Main starts = >
example_no = input("Enter the Example Number(as 123) : ")
rulebook_sheet = 'Example '+example_no
df_example = pd.read_excel('MLCL Accounting Rules v25.0B5.xlsx', sheet_name=rulebook_sheet, dtype="string")
i = 0
while True:
if pd.isna(df_example.iloc[0, i]):
i += 1
else:
i += 1
break
# Clean Rulebook data
df_example = df_example[df_example.columns[:i]]
while True:
if not pd.isna(df_example.iloc[0, 0]):
break
df_example.drop([0], inplace=True)
df_example.reset_index(drop=True, inplace=True)
df_example = remove_if_applicable_events(df_example)
# Keeps all the entries that have some transaction amount in them
df_example.dropna(subset=['Scenario'], inplace=True)
# Takes out all the Events name
events = df_example['Event'].dropna()
events.reset_index(drop=True, inplace=True)
# df_evd_td = pd.read_excel('Evidence_Sheet_Format.xlsx', sheet_name='Test Data', dtype="string")
wb_evd = openpyxl.load_workbook('Evidence_Sheet_Format.xlsx')
# wb_rule = openpyxl.load_workbook('MLCL Accounting Rules v25.0B5.xlsx',
# keep_vba=False, keep_links=False, read_only=True)
sheet_td = wb_evd['Test Data']
row_n = sheet_td.max_row+1
# Puts Events names to the evidence sheet
for i in range(len(events)):
sheet_td.cell(row=row_n+i, column=1, value=events[i])
sheet_td.cell(row_n+i, 1).fill = PatternFill('solid', fgColor='00B300')
sheet_td.cell(row_n+i, 1).font = Font(name='Calibri', size=11, bold=True)
# # Read the Accounting extracts with the name in format 'Ex-#_GL.xlsx'
# file_name = 'Ex-' + example_no + '_GL.xlsx'
# df_accounting = pd.read_excel(file_name, dtype='string')
# fill_policy_details(df_accounting, sheet_td)
# Saves the Evidence file with proper file name
example_no = 'Ex ' + example_no
test_reference = 'Test-'
df_reference = pd.read_excel('Evidence_Sheet_Format.xlsx', sheet_name='Test Ref', dtype="string")
for i in range(len(df_reference['Test No'])):
if df_reference.iloc[i, 1] == example_no:
test_reference += df_reference.iloc[i, 0]
break
test_reference += '_' + example_no + '.xlsx'
sheet_ref = wb_evd['Test Ref']
del wb_evd['Test Ref']
wb_evd.save(test_reference)
print('Evidence sheet created successfully : ', test_reference)
# File saved in format of 'Test-#_Ex #'
import pandas as pd
import numpy as np
import openpyxl
from openpyxl.styles import PatternFill, Font
EVIDENCE_FILE_NAME = 'Test-34_Ex 166.xlsx'
GL_FEED_FILE_NAME = 'BaNCSMLCL_9_29092020025553.CSV'
# POLICY_NO = '4017680'
# PRODUCT_CODE = 'LU_UL_SP_MLC'
df_data = pd.read_excel(EVIDENCE_FILE_NAME, sheet_name='Test Data', dtype='string')
POLICY_NO = str(int(df_data.iloc[0, 1]))
PRODUCT_CODE = df_data.iloc[1, 1]
print(POLICY_NO, PRODUCT_CODE)
df_evidence_entries = pd.read_excel(EVIDENCE_FILE_NAME, sheet_name='Results Review', dtype='string')
df_evidence_entries.dropna(subset=['Account Name'], inplace=True)
# print(len(df_evidence_entries))
df_evidence_entries = df_evidence_entries[df_evidence_entries.columns[:13]]
df_gl_entries = pd.read_csv(GL_FEED_FILE_NAME, dtype='string')
df_gl_entries = df_gl_entries[df_gl_entries.columns[:95]]
# print(df_gl_entries.iloc[1, 38],df_gl_entries.iloc[1, 39])
df_gl_policy_entries = df_gl_entries[df_gl_entries['Attribute19 POLICY NO'] == POLICY_NO].copy()
df_gl_blank_entries = df_gl_entries[pd.isna(df_gl_entries['Attribute19 POLICY NO'])].copy()
df_gl_blank_entries_product = df_gl_blank_entries[df_gl_blank_entries['Attribute16 Value for Captured Information DFF']
== PRODUCT_CODE].copy()
df_gl_blank_entries = df_gl_blank_entries[pd.isna(df_gl_blank_entries['Attribute16 Value for Captured Information DFF'])].copy()
frames = [df_gl_blank_entries, df_gl_blank_entries_product, df_gl_policy_entries]
df_gl_entries = pd.concat(frames).copy()
df_gl_entries.reset_index(drop=True, inplace=True)
df_evidence_entries['Check'] = 'Not Found'
# GL INDEX 38 - Debit and 39 - Credit
# EVD -ve value = Credits
col_list = list(df_gl_entries.columns)
df_result = pd.DataFrame(columns=col_list)
EVENT_NAMES = ['PREM_DUE', 'COMMISION_DUE', 'PREM_RECEIPT', 'COMMISION_RCPT']
for i in range(len(df_evidence_entries)):
evd_bal = float(df_evidence_entries.iloc[i, 7])
evd_account_code = int(df_evidence_entries.iloc[i, 0])
evd_event = df_evidence_entries.iloc[i, 12]
for j in range(len(df_gl_entries)):
gl_account_code = int(df_gl_entries.iloc[j, 82])
gl_event = df_gl_entries.iloc[j, 74]
if evd_account_code in [10201, 10200, 10100, 10203, 35200, 73012] and evd_account_code == gl_account_code:
df_evidence_entries.iloc[i, 13] = 'Found'
df_result.loc[df_gl_entries.index[j]] = df_gl_entries.iloc[j]
break
if evd_event in EVENT_NAMES and evd_account_code != 17200:
if evd_event != gl_event:
continue
# Credit
if evd_bal < 0:
if not pd.isna(df_gl_entries.iloc[j, 39]):
if abs(evd_bal) == float(df_gl_entries.iloc[j, 39]) and gl_account_code == evd_account_code:
df_evidence_entries.iloc[i, 13] = 'Found'
df_result.loc[df_gl_entries.index[j]] = df_gl_entries.iloc[j]
break
# Debit
else:
if not pd.isna(df_gl_entries.iloc[j, 38]):
if abs(evd_bal) == float(df_gl_entries.iloc[j, 38]) and gl_account_code == evd_account_code:
df_evidence_entries.iloc[i, 13] = 'Found'
df_result.loc[df_gl_entries.index[j]] = df_gl_entries.iloc[j]
break
df_result.to_excel("GL_Result_Entries.xlsx",sheet_name='GL_Entries', index=False)
df_evidence_entries.to_excel("Evidence_Entries.xlsx", sheet_name='Check', index=False)
file_name = POLICY_NO+'_'+'.xlsx'
df_gl_entries.to_excel(file_name, sheet_name='Entries', index=False)
print('Check Complete !!!')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment