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