Case Study: Bookkeeping

In this post, I’ll walk you through how I use Excel Power Query to make bookkeeping faster and easier when working with QuickBooks Desktop. This method isn’t perfect, but it saves a lot of time compared to entering everything manually.

I’ll show you how I take raw bank transaction data and transform it into a batch transaction format that can be imported directly into QuickBooks Desktop.

I. When You Have a Raw Excel Data File from the Bank

The first step is to download a transaction file—usually in CSV format—directly from your company’s bank website.
Let’s call this file Excel A.

To prepare this data for QuickBooks Desktop, we’ll use another Excel file—Excel B—which contains Power Query logic to clean and convert the data into the correct format for batch importing.

🔹 Excel A

This file includes raw transaction data downloaded from the bank. It usually contains all types of transactions: checks, withdrawals, deposits, and possibly credit card payments.

🔹 Excel B

This file is set up with Power Query and transforms the raw data from Excel A into properly formatted tables that can be imported into QuickBooks using batch transaction mode.
👉 You simply copy the data from Excel A and paste it into the “Transaction Table” in the “Original” sheet of Excel B.
Then, when you refresh the Power Query, it automatically updates all the output tables.

You can download Excel B here:

📄 Excel B contains the following five sheets:

• Original – Paste the raw bank transaction data here.
• OUTPUT-OD – For other expenses that are not checks.
• OUTPUT-CHECK – For payments made by check.
• OUTPUT-DEPOSIT – For incoming money, such as deposits.
• OUTPUT-CREDIT CARD – For credit card payments.

By refreshing the query, all four output sheets are updated with clean, formatted data ready for import.

1. Original Sheet: Input

This is where everything begins. The Original sheet includes two main input tables:

(1) Transaction Table

This table holds the raw transaction data copied from Excel A. While the format may vary from bank to bank, a few column labels must follow specific naming rules:

• Date
• Description
• Amount or (Credit & Debit)
• Chkref (if applicable)

The order of columns doesn’t matter, but the column names must match exactly. You can also include any additional columns labeled however you like (e.g., AAA, BBB, CCC).

Example of label adjustment:

(2) Rule Table

This is where automation happens. The Rule Table helps assign Payee names and Account Numbers based on keywords found in the Description field of each transaction.

Example: If the Description contains Walmart, assign:

• Payee = WM
• Account Number = 654

Example

Keyword	Account #	Payee
Walmart	654	WM
Kroger Fuel	687	Kroger Fuel
Kroger	632	Kroger

Tips for Rule Table:

• Start with specific keywords first, then broader ones.
• Avoid duplicate or overlapping keywords. Even a one-letter difference is treated as a new rule.

2. Output Sheets – Transformed Data

After pasting your data and refreshing the Power Query, the system separates transactions into four output sheets. Always double-check the totals before importing anything into QuickBooks.

(1) OUTPUT-OD: Outflows (Non-Check)

This sheet contains all expenses except for check payments.

Note: Some banks mix check payments into this list. Double-check against the Check sheet.

• Sort by Payee to help review and apply rules efficiently.
• I personally delete the memo for transactions that matched a rule and keep the memo for unmatched ones—this helps spot what still needs manual attention.

(2) OUTPUT-CHECK: Check Payments Only

This sheet filters for check-based outflows only.

• Check transactions are formatted using Excel formulas (e.g., VLOOKUP) for batch importing into QuickBooks.
• For more details, refer to the YouTube tutorial linked below.

(3) OUTPUT-DEPOSIT: Inflows

This table lists incoming transactions, organized by name.

• Helps you easily spot non-revenue deposits that need to be booked differently.

(4) OUTPUT-CREDIT CARD: Credit Card Transactions (if applicable)

Some banks show credit card payments as negative values and expenses as positive ones.

• In these cases, use this sheet instead of OUTPUT-OD for cleaner results.

Important Notes

• Some check payments may appear in both OD and Check output sheets—double-check before importing.
• Always confirm that your output totals match the original bank data.
• This Excel setup isn’t perfect—but it really saves time.

II. When We Have a Separate Check Information File

Sometimes, companies provide a separate Excel file that contains check payment details—let’s call this the Check Info File. When this happens, we can use Excel Power Query to merge it with the OUTPUT-CHECK sheet we created earlier, and generate QuickBooks-ready batch transaction data more efficiently.

To automate this, I built an Excel file that handles everything using Power Query.

1. Input Tables (Sheet: Original)

• Check Table: Requires Date and Chkref columns
• Rule Table by Payee: Assigns default Account Numbers based on the Payee
• Rule Table by Memo: Overrides the Payee rule when specific keywords are found in the Memo
  Helps distinguish between different transaction types for the same payee
• Check Info (Provided by the Company): Must include Date, Chkref(must match check numbers from the Check Table), Memo, and Amount

2. Output Tables

• OUTPUT-CHECKS:
  This is the final table used for QuickBooks Desktop’s Batch Transaction Import.
  The Check Table and the Check Info Table are merged using the Chkref (check number).
  Then, two layers of rules are applied:
• Rule Logic
  – Default Rule (by Payee)
  Assigns the default Account Number when a transaction is associated with a certain Payee.
  – Memo Rule (by Keyword Override)
  If a keyword (like reimburse or supplies) appears in the Memo, override the Payee rule and assign a different Account Number.

Example Scenario:

• John is a regular employee who typically receives payroll.
• Occasionally, he’s reimbursed for supplies he purchases.
• The rule logic:
  – If it’s paid to John, assign Payroll Account
  – If the memo says “reimburse supply”, assign Supply Account

Caution & Tips

• Duplicate Check Numbers
  If a check number appears more than once, it will match multiple times, potentially creating duplicate transactions (e.g., 4 instead of 2).
  ➤ Always check for duplicates before importing.
• Mismatched Amounts
  The Amount in the bank data (Excel A) might differ from the Check Info Table.
  ➤ Confirm totals carefully to avoid errors in QuickBooks.

A full walkthrough is available in the YouTube video linked below.

1. For Explanation of Excel B

2. Check Information File