Summary
Highlights
The video introduces the concept of reconciliations using Excel, aiming to compare two lists to identify values unique to each or common to both. A bank reconciliation will serve as the primary example, specifically focusing on automating the identification of outstanding checks, deposits in transit, bank additions, and bank charges. The underlying techniques are applicable to any list comparison task.
The first step involves exporting lists of checks and deposits from an accounting system, and banking activity from a banking website. These data sources are then loaded into Power Query as connection-only queries. This process involves selecting a cell within each table, navigating to 'Data' > 'Get & Transform Data' > 'From Table/Range', naming the query, and saving it as a connection only.
To compare book checks with bank activity, the raw bank activity data needs to be cleaned. This is done by referencing the 'bank all' query, naming it 'bank checks', filtering for rows that represent checks (e.g., descriptions beginning with 'check'), and then splitting the check column to extract just the check number, ensuring it matches the format of the book checks data. This processed data is also saved as a connection-only query.
Cleared checks are those found in both the book and bank records. This is achieved by using the 'Merge Queries' function in Power Query, selecting 'book checks' and 'bank checks', and choosing an 'Inner' join kind to return only matching rows based on the check number. The merged data is then expanded, and an optional difference column can be added to verify that amounts also match. This resulting list is named 'cleared' and loaded into a table in Excel.
Outstanding checks are those present in the book records but not yet cleared by the bank. This is accomplished using 'Merge Queries' again, comparing 'book checks' to 'bank checks', but this time using the 'Left Anti' join kind, which returns rows found only in the first table. This list is named 'outstanding' and loaded into an Excel table with a total row.
Bank charges are deductions made by the bank not yet recorded in the book. By referencing the 'bank all' query, filtering for negative amounts excluding checks, these charges can be identified. This query is named 'bank charges' and loaded into an Excel table.
For deposits, the 'book deposits' query is modified to group and sum deposits by date to account for bank aggregation. A 'bank deposits' query is then created by referencing 'bank all' and filtering for rows starting with 'deposit'. Deposits in transit are found by merging 'book deposits' and 'bank deposits' using the 'Left Anti' join on the amount column, identifying deposits on the book but not yet on the bank. This list is named 'dit' (deposits in transit) and loaded into an Excel table.
Bank additions are positive amounts on the bank statement not yet recorded in the book. By referencing 'bank all', filtering for positive amounts that are not deposits, these additions can be isolated. This query is named 'bank additions' and loaded into an Excel table.
Finally, the calculated values for outstanding checks, deposits in transit, bank additions, and bank charges are linked to the reconciliation summary. The major benefit of using Power Query is highlighted: once set up, new data for subsequent periods can simply be pasted into the source tables, and a single 'Refresh All' command will automatically update the entire reconciliation. This significantly streamlines the process compared to manual methods or even VLOOKUP, making reconciliations much faster and more efficient.