Reconciliation of a trial balance to a general ledger

By John Mathews
In: Data
Jan 03 2017

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:

In [1]:
# Import libraries
import pandas as pd
import random
In [2]:
# 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:

In [3]:
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:

In [4]:
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:

In [5]:
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:

In [6]:
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
Out[6]:
Type Balance b/f Balance c/f Balance date TB_Movement GL_Movement difference
Account
ACP00081 P&L; 0.00 -16279.96 20161231 -16279.96 -17017.89 737.93
ACB00082 BS 760.09 -4041.02 20161231 -4801.11 -4063.18 -737.93
ACP00071 P&L; 0.00 -28547.84 20161231 -28547.84 -29260.31 712.47
ACB00091 BS 628.24 3054.74 20161231 2426.50 3138.97 -712.47
ACP00017 P&L; 654.01 24449.74 20161231 23795.73 23123.06 672.67
ACB00076 BS 768.49 -48456.11 20161231 -49224.60 -48551.93 -672.67
ACB00037 NaN NaN NaN nan NaN 19808.54 NaN
ACP00001 NaN NaN NaN nan NaN -3817.92 NaN
ACP00041 NaN NaN NaN nan NaN -14365.77 NaN
ACP00046 NaN NaN NaN nan NaN 1825.79 NaN
ACP00086 NaN NaN NaN nan NaN -7263.37 NaN

Create a report containing summary details:

In [7]:
# Accounts not in the TB but in the GL
Accounts_not_in_TB = Unreconciled[Unreconciled.TB_Movement.isnull() == True]
In [8]:
# 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)]
In [9]:
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
In [10]:
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)
In [11]:
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)
In [12]:
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)
In [13]:
# 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) 
In [14]:
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)
In [24]:
with open('Report.txt', 'r') as fin:
    print(fin.read())
The data contains 110 accounts
99 accounts reconcile ( 90.0 %)

11 accounts do not reconcile ( 10.0 %)
There are 5 accounts in the GL and not in the TB. ( 45.45 % of unreconciled accounts)
There are 6 accounts with journals missing ( 54.55 % of unreconciled accounts)
The net of all the differences is 0.0

TB opening balance is unbalanced by -2486.28
TB closing balance is unbalanced by 1326.45
***If these are not 0 then the TB is certainly wrong and receiving a 
balanced TB is the first step to reconciling all accounts***

There are 3 unreconciled accounts with equal and opposite differences


Thanks for reading! If you'd like to know when I publish something new, please subscribe!

Twitter     Facebook     Email     Email