Case Study: Bookkeeping

In this guide, I explain how I use Excel Power Query to automate bookkeeping tasks for QuickBooks Desktop. This method is not perfect but saves a lot of time. Below, I will share how I transform raw bank transaction data into a batch transaction data format that QuickBooks Desktop can import.

I. When we have Excel Raw Data File

In this post, I will explain the Excel file I use for QuickBooks Desktop bookkeeping. This process aims to organize bank statements for Company A using two essential Excel files.

Required Excel Files

• Excel A: This file contains transaction data downloaded directly from Company A’s bank website.
• Excel B: This file uses Power Query to transform input data into an output table. The output table is then used for QuickBooks batch transaction import. It has five sheets as below.

Excel B

1. Input – Original Sheet

Excel B consists of two main input tables in Sheet “Original”: the Transaction Table and the Rule Table.

(1) Transaction Table

The transaction table contains raw bank data, which can differ in structure across banks. However, certain column labels must be consistent:

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

Regardless of column order, the column names must follow the above labels. Additional variable columns can be labeled freely (e.g., AAA, BBB, CCC).

Example of label adjustment:

(2) Rule Table

The rule table assigns Payee and Account Number based on keywords found in the Description column.

Example: If the Description contains Walmart, assign:

• Payee = WM
• Account Number = 654

Rule table:

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

Important: Set rules from specific to broad terms. If the order is reversed, all entries may be assigned incorrectly. Avoid duplicate keywords whenever possible. Differentiating keywords by even one letter will yield separate assignments.

2. Output

Transactions are split into three output sheets:

(1) OUTPUT-OD

The table contains Outflows excluding checks. The orders are according to QB Desktop Batch transactions. You can sort transactions by any label. First, sort them by Payee and customize the rules to assign transactions as many as possible. In my case, I delete memo for all assigned transactions and keep memo for not assigned.

(2) OUTPUT-CHECK

The table contains Outflows only checks. Check payments processed using Excel Automation Recall and VLOOKUP to format batch transactions for QuickBooks. Refer to the YouTube video for details.

(3) OUTPUT-DEPOSIT

This table contains sorted inflows by name, making non-revenue transactions easier to identify.

(4) OUTPUT-CREDIT CARD (if applicable)

Credit card payments often display payments as minus and expenses as plus amounts. In such cases, the OUTPUT-CREDIT CARD table is used instead of OUTPUT-OD.

Important Notes

• Bank data might place check payments in both OD and Check outputs. Verify all entries.
• Always check if the total balance matches before transferring data to QuickBooks.

This Excel method is not perfect, but it saves a lot of time. I am not a computer expert, but I believe that coding is no longer an optional skill. With AI assistants, anyone can automate tasks with just an idea. Don’t be afraid—just give it a try!

II. When we have Check Information File

In some cases, companies provide separate Check Info Excel files that summarize check payments. This additional file helps automate the check entry process.

1. Input Tables

(Sheet: Original)

• Check Table: Requires Date and Chkref columns
• Rule Table by Payee
• Rule Table by Memo
• Check Info Provided by Company: Must include Date, Chkref, Memo, and Amount

2. Output Tables

• OUTPUT-CHECKC: Batch transaction data format, where the Check Number from the Check Table is matched with the Check Info file. Two rules are applied:
  • Default Rule: Assign Account Number based on Payee.
  • Memo Rule: Assign a different Account Number if specific keywords (e.g., reimburse) appear in the Memo field.

Example: John is employee. The checks paid to John is payroll. However, sometimes John gets reimbursement after spending supply. In that case, the first rule is “assign payroll account when it is paid to John and the second rule is assign supply account when memo indicate reimburse supply.

Caution:

• If a Check Number is duplicated, the match will occur twice, creating four transactions instead of two. Always verify for duplicate check numbers.
• Sometimes the Amount in the bank transactions differs from the amount in the provided check info. Always confirm whether the amounts match.
• CheckInfo3: Converts the provider’s check info into Batch Transaction Format for QuickBooks.

III. Special Cases

I also use custom Excel files for Bank of America and First IC Bank. Sometimes it is hassel to ask excel transaction files and I try to transform pdf transaction into batch transaction data format using copy and paste from pdf. Please, check youtube video to see how I am using.

1. Bank of America

Bank of America statements categorize transactions into deposits, withdrawals, checks, and service fees.

Input:

• Withdrawal Table (Include service fees!)
• Check Table
• Deposit Table
• Rule Table

Output:

• OUTPUT-OD
• OUTPUT-Checks
• OUTPUT-Deposit

Sometimes, it has wrong transaction out. For example, there is a transaction as following:

In this case, the code consider -85430 as the amount and output has additional transaction as the amount of $85,430. Always double-check totals, especially for service fees.

2. First IC Bank

First IC Bank combines deposits and withdrawals in a single table. When copying transactions, include the” beginning balance” to avoid errors.

Input:

• Data Table
• Rule Table

Output:

• OUTPUT-OD
• OUTPUT-Checks
• OUTPUT-Deposit

For more detailed explanations, please refer to my YouTube videos. Below, you can download the Excel files I use for your own bookkeeping tasks.