I’ve been working with financial ledgers a lot recently. The python code below shows an automated workflow to import, process and report on the reconciliation of a Trial Balance (TB) to a General Ledger (GL).
I’m using fake data, but the script would work fine with real data if the fields were renamed appropriately. A real data set would have additional fields that needed to be considered, but these vary depending on the size and type of business being analysed. Therefore the fake GL and TB used here are simple and generic. Additional fields, such as entity code, transaction status, approver, time stamps, etc can be added quickly and simply.
Set-up the notebook and import data:
# Import libraries
import pandas as pd
import random
# Import (possibly incomplete and/or inaccurate) data
gl = pd.read_csv('glx.txt', sep = '|')
tb = pd.read_csv('tbx.txt', sep = '|')
Create reconciliation report:
Calculate net movement for each account in the ledger data:
gl_move = gl[['Account','Functional_Amount']]
gl_move = gl_move.groupby(['Account']).sum().round(2)
gl_move.reset_index(level=0, inplace=True)
gl_move.columns = ['Account','GL_Movement']
gl_move.sort_values(by = 'Account')
gl_move.set_index('Account', inplace = True)
Calculate the movement for each account in the trial balance:
tb['TB_Movement'] = ( tb['Balance c/f'] - tb['Balance b/f'] ).round(2)
tb.set_index('Account', inplace = True)
Compare each accounts movement in the ledger data and the trial balance and write the result to a report containing the reconciliation results for all accounts:
Rec_report = tb.merge(gl_move, how = 'outer', left_index = True, right_index = True)
Rec_report['difference'] = Rec_report.TB_Movement - Rec_report.GL_Movement
Rec_report['difference'] = Rec_report['difference'].astype(float)
Rec_report['Balance date'] = Rec_report['Balance date'].astype(str).str[0:8]
Rec_report.to_csv('All_accounts.txt', sep='|', encoding='utf-8', header=True, index=True)
Put the accounts which do not reconcile into a seperate report:
Unreconciled = Rec_report[Rec_report.difference != 0]
Unreconciled = Unreconciled.reindex(Unreconciled.difference.abs().sort_values(ascending = False).index)
Unreconciled.to_csv('Unreconciled_accounts.txt', sep='|', encoding='utf-8', header=True, index=True)
Unreconciled
Create a report containing summary details:
# Accounts not in the TB but in the GL
Accounts_not_in_TB = Unreconciled[Unreconciled.TB_Movement.isnull() == True]
# Accounts in the TB where TB_Movement isn't matched in the GL
Missing_journals = Unreconciled[(Unreconciled.GL_Movement.isnull() == True)|(Unreconciled.difference.isnull() ==False)]
Total_Accounts = len(Rec_report)
Num_Rec_Accounts = Total_Accounts - len(Unreconciled)
Num_Unrec_Accounts = len(Unreconciled)
Rec_Fraction = Num_Rec_Accounts / Total_Accounts
Unrec_Fraction = Num_Unrec_Accounts / Total_Accounts
with open("Report.txt", "w") as text_file:
print('The data contains',Total_Accounts, 'accounts', file=text_file)
print(Num_Rec_Accounts, 'accounts reconcile (', round(Rec_Fraction*100,2), '%)\n', file=text_file)
print(Num_Unrec_Accounts, 'accounts do not reconcile (', round(Unrec_Fraction*100,2), '%)', file=text_file)
with open("Report.txt", "a") as text_file:
print('There are', len(Accounts_not_in_TB), 'accounts in the GL and not in the TB. (', round(100*(len(Accounts_not_in_TB)/Num_Unrec_Accounts),2), '% of unreconciled accounts)', file=text_file)
print('There are', len(Missing_journals), 'accounts with journals missing (', round(100*(len(Missing_journals)/Num_Unrec_Accounts),2), '% of unreconciled accounts)', file=text_file)
net_diff = round(Unreconciled[Unreconciled.difference.isnull() == False].difference.sum(),2)
with open("Report.txt", "a") as text_file:
print('The net of all the differences is', net_diff, file=text_file)
# Does the TB balance?
with open("Report.txt", "a") as text_file:
print('\nTB opening balance is unbalanced by', "%.2f" % tb['Balance b/f'].sum(), file=text_file)
print('TB closing balance is unbalanced by',"%.2f" % tb['Balance c/f'].sum(), file=text_file)
print('***If these are not 0 then the TB is certainly wrong and receiving a \nbalanced TB is the first step to reconciling all accounts***', file=text_file)
diffs = Unreconciled.difference.abs().round().tolist()
frequency = {}
for w in diffs:
frequency[w] = frequency.get(w, 0) + 1
pairs = {x for x in frequency if x > 1} # dict comprehension to filter for pairs
with open("Report.txt", "a") as text_file:
print('\nThere are', len(pairs), 'unreconciled accounts with equal and opposite differences', file=text_file)
with open('Report.txt', 'r') as fin:
print(fin.read())