Pairing inter-account transfers across bank statements with pandas

To pair inter-account transfers across bank statements, match each outflow with an inflow of the same absolute amount in a different account within about three days, using each row only once. Work with integer cents. Left unpaired transfers can indicate missing accounts in bookkeeping or undiscovered accounts in divorce proceedings. When dealing with transactions from multiple accounts belonging to one household or business, many rows represent the same money changing hands. Pandas can handle the complexities involved.

The input for this script is one DataFrame, with one row per transaction, where money out is represented as a negative number. This format is typically achieved by downloading several converted statements into a single file, including an account column.

To find candidate transfer pairs in pandas, separate the outflows and inflows into two DataFrames. Convert the date column to datetime format, and multiply the amount column by 100, round, and convert it to integer cents. This ensures accurate comparisons. Name the outflow DataFrame 'outs' and the inflow DataFrame 'ins'.

Create a 'key' column to identify matching pairs. Set 'outs[key]' to the negative of 'outs[cents]' and 'ins[key]' to 'ins[cents]'. Match outflows with inflows using the 'key' column. The most crucial rule is that the money must change accounts. Any matches between accounts should be discarded. Calculate the lag (difference in days) between the outflow and inflow dates, and keep only those pairs with a lag between 0 and 3 days.

Using the last two sample rows as an example, a 650.00 card payment and a 650.00 deposit made two days apart in the same account will be paired. However, without the account rule, this could result in an unexplained card payment disappearing from the list. The rule enforcing a change in accounts is essential to ensure accurate findings. Lastly, sort the candidate pairs by lag and outflow date to ensure each row is paired only once.