Chapter 1: Overview of Daily Bank Transaction Review

This guide introduces a Python code snippet designed to combine two Excel files and perform essential data processing tasks.

First, let's import the necessary libraries. The script utilizes the pandas library, which is crucial for manipulating Excel files.

import pandas as pd

Section 1.1: Function Definition

We define a function named merge_excel_files that takes three parameters: the paths to the two input Excel files and the output file path for the merged data.

def merge_excel_files(input_file1, input_file2, output_file):

Section 1.2: Reading Excel Files

The code reads the input files using the pd.read_excel() function, storing the contents in two variables: checks and bank.

checks = pd.read_excel(input_file1)

bank = pd.read_excel(input_file2)

Section 1.3: Merging Data

The merging process employs the pd.merge() function. An outer join is performed to ensure all records are included, with specified columns for merging.

review = pd.merge(checks, bank, how='outer', left_on='CheckNo', right_on='CheckCleared', indicator='location')

Section 1.4: Data Filtering

The next step involves separating known and unknown data. The records that exist only in the bank's data are stored in a variable called unknown, while those present in both datasets go into known.

unknown = review.loc[review['location'] == 'right_only']

known = review.loc[review['location'] == 'both']

Section 1.5: Calculating Differences

We introduce a new column named Difference in the known DataFrame that calculates the disparity between TotalDue and AmountCleared.

known['Difference'] = known['TotalDue'] - known['AmountCleared']

Section 1.6: Filtering Non-Zero Differences

To further refine the data, we filter out records where the difference is not equal to zero, storing these in the DiffAmount variable.

DiffAmount = known.loc[known['Difference'] != 0]

Section 1.7: Exporting the Merged Data

Finally, the merged results, along with the unknown and known datasets, are written to separate sheets in an Excel file using pd.ExcelWriter().

with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:

review.to_excel(writer, sheet_name='merged', index=False)

unknown.to_excel(writer, sheet_name='unknown', index=False)

known.to_excel(writer, sheet_name='known', index=False)

DiffAmount.to_excel(writer, sheet_name='DiffAmount', index=False)

Section 1.8: Running the Function

To execute the merging and data processing, call the merge_excel_files() function with the paths to the specific Excel files.

merge_excel_files('BankRec_example.xlsx', 'BankRec_BankofAmerica.xlsx', 'merged_result.xlsx')

Chapter 2: Practical Application of Python in Finance

Explore how to automate your finances using Python in this informative video. Learn how to streamline your financial processes effectively.

Join the workshop on analyzing bank statements with Python. Gain practical insights into financial data management.