Last active
February 12, 2021 14:52
-
-
Save Lycan47/fe380fa239ad1eafdfa42519158d592c to your computer and use it in GitHub Desktop.
Helps in Reconciling the General Accountings behind Functional Testing of BaNCS Insurance
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 #' | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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