Helps in Reconciling the General Accountings behind Functional Testing of BaNCS Insurance

Create_Evidence.py

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 #'

GL_Feed_Search.py

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 !!!')