Stop Doing Manual Reconciliations in Excel: Use Power Query

Summary

In this video, Jeff demonstrates how to automate bank reconciliations using Excel's Power Query. He walks through a process of comparing multiple lists—such as checks, deposits, and bank activity—to identify cleared checks, outstanding checks, deposits in transit, bank charges, and bank additions. By utilizing Power Query to automate these comparisons, users can streamline their reconciliation process. Jeff emphasizes the efficiency of this method, which requires minimal updates after setup, saving time for future periods. The tutorial is useful for anyone comparing two lists in Excel, not just for bank reconciliations.

Takeaways

• 😀 Power Query is a powerful tool in Excel that can automate list comparisons, such as bank reconciliations, saving time and reducing manual effort.
• 😀 The first step in using Power Query for bank reconciliation is importing data from various sources like accounting systems and bank websites into Power Query.
• 😀 Creating descriptive names for each query (e.g., book checks, book deposits, bank activity) helps maintain clarity and organization within the data.
• 😀 Cleaning up and transforming data (e.g., filtering non-check rows, removing unwanted columns) is essential before comparing data from different sources.
• 😀 Left outer joins in Power Query allow you to compare two lists and bring in matching rows, making it easy to identify cleared checks and reconcile balances.
• 😀 Power Query provides different join options, such as inner join (for matching rows) and left outer join (to include all rows from the first list), depending on the reconciliation goals.
• 😀 Comparing lists with identical formats and data types, like check numbers, ensures that the data can be matched correctly between the two lists.
• 😀 Identifying discrepancies in amounts (e.g., checks written for one amount but cleared for another) can be done easily by adding a difference column in Power Query.
• 😀 Once the initial setup is done, future reconciliations can be completed by simply refreshing the queries to pull in new data, making the process much quicker.
• 😀 Power Query can handle multiple types of data sources, including CSV, Excel, or text files, and can be set up to look at external files as well as internal data.

Q & A

What is the main objective of using Power Query in this reconciliation process?

The main objective is to automate the comparison of financial lists (checks, deposits, bank charges, etc.), identify discrepancies, and streamline the reconciliation process, reducing manual work and errors.

Why is it important to clean up the data before comparing the lists?

Data cleaning ensures that the information is in a consistent format, allowing for accurate comparisons between the lists. For example, removing unnecessary columns and transforming data types ensures that checks and deposits can be correctly matched.

How does the 'Merge' function in Power Query help in reconciling data?

The 'Merge' function combines two lists based on a common column (e.g., check number), allowing users to identify matches, unmatched records, and discrepancies between the two lists. It offers various join types, like left outer join, to customize the comparison.

What is the difference between a 'left outer join' and an 'inner join' in Power Query?

'Left outer join' returns all records from the first list and the matching records from the second list. An 'inner join' only returns rows where there is a match in both lists.

How does Power Query help when new data is added in subsequent periods?

Once the queries are set up, new data can simply be pasted into the relevant tables. After that, clicking 'Refresh All' in Power Query updates the lists automatically, applying the same transformations and comparisons without needing to redo the work.

What are 'cleared checks' and how are they identified in this process?

Cleared checks are checks that appear in both the book of records and the bank's data. They are identified by comparing the check numbers and amounts between the two lists and filtering out non-matching rows.

How can discrepancies in check amounts (e.g., a check cleared for a different amount) be detected?

Discrepancies in check amounts can be identified by comparing the amounts from the book and bank lists. If the amounts differ, a new 'difference' column can be created to quickly highlight non-zero discrepancies.

What are 'outstanding checks' and how are they identified in the process?

Outstanding checks are checks that are listed in the book of records but have not yet cleared the bank. They are identified by performing a 'left outer join' to find checks in the book that do not appear in the bank's list.

What are 'bank charges' and how can they be filtered out from the bank's activity list?

Bank charges are negative values (charges or fees) that appear on the bank's activity list. They can be filtered by selecting rows with amounts less than zero and excluding rows that correspond to checks or deposits.

What are 'deposits in transit' and how are they handled in this reconciliation?

Deposits in transit are deposits that are recorded in the book of records but have not yet appeared in the bank's records. They are identified by comparing the amounts and filtering out those already present in the bank's list.

How does Power Query's ability to aggregate data improve the reconciliation process?

Aggregating data, such as summing deposit amounts by date, ensures that records match the bank's totals, especially when multiple transactions are combined in the bank's data. This helps in accurately comparing deposits and handling discrepancies.