Stage 1: Exporting Data from QuickBooks
Migrating five years of data starts with getting all QuickBooks records out in a usable format. QuickBooks Desktop and QuickBooks Online have different export options:
1.1 QuickBooks Desktop – Export Options
IIF (Intuit Interchange Format): QuickBooks Desktop can export lists (like chart of accounts, customers, vendors) to .IIF text files. In QuickBooks Desktop, go to File → Utilities → Export → Lists to IIF, then select the lists you need (e.g. Chart of Accounts, Customers, Vendors). This yields a text file that includes account names, types, and list data. IIF is a proprietary but plain-text format that’s relatively easy to parse. Use it to capture your Chart of Accounts and contact lists for reference in Beancount.
General Ledger/Journal via CSV: For transaction data, QuickBooks Desktop doesn’t provide a one-click full export, but you can use reports. The recommended method is to export the General Journal (all transactions) over the desired date range. In QuickBooks Desktop, open Reports → Accountant & Taxes → Journal, set the Dates from the earliest transaction to today, and click Export → Excel. Save the result as CSV after removing any report headers/footers and empty columns. Make sure the numeric data is clean: include cents (e.g. 3.00 not 3), no extra quotes, and no currency symbols or double negatives in the CSV. The CSV should have columns like Date, Trans #, Name, Account, Memo, Debit, Credit, Balance (or a single Amount column depending on the report format).
Tip: QuickBooks Desktop 2015+ can also export transactions via the Find dialog. Use Edit → Find → Advanced, set the date range for five years, then export results to CSV. Warning: Some versions cap the export at 32,768 lines. If you have very large data, export year by year (or smaller chunks) to avoid truncation, then later combine them. Ensure date ranges don’t overlap to prevent duplicates.
Other Formats (QBO/QFX/QIF): QuickBooks Desktop can import bank transactions via .QBO (Web Connect) or .QFX/.OFX files, but for exporting from QuickBooks, these aren’t typical. If your goal is to extract bank transactions only, you might already have them in QBO/OFX from your bank. However, for a full ledger export, stick to IIF and CSV. QuickBooks Desktop cannot directly export to QIF (Quicken Interchange Format) without third-party tools. If you do find a way to get QIF, note that some ledger tools (older Ledger 2.x) could read QIF, but it’s better to work with CSV in our pipeline.
1.2 QuickBooks Online – Export Options
Built-in Excel/CSV Export: QuickBooks Online (QBO) provides an Export Data tool. Go to Settings ⚙ → Tools → Export Data. In the export dialog, use the Reports tab to select data (e.g. General Ledger or Transaction List) and the Lists tab for lists (chart of accounts, etc.), choose All dates, and export to Excel. QuickBooks Online will download a ZIP containing multiple Excel files for the selected reports and lists (for example, Profit and Loss, Balance Sheet, General Ledger, Customers, Vendors, Chart of Accounts, etc.). You can then convert these Excel files to CSV for processing.
Transaction Detail Report: If QBO’s default export doesn’t include a single General Ledger file, you can manually run a detailed report:
- Navigate to Reports and find Transaction Detail by Account (or General Ledger in some QBO versions).
- Set Report period to the full five-year range.
- Under Report options, set Group by = None (to list individual transactions without subtotals).
- Customize columns to include at least: Date, Transaction Type, Number, Name (Payee/Customer), Memo/Description, Account, Debit, Credit (or single Amount column), and Balance. Include any class or location if used.
- Run the report and then Export to Excel.
This yields a detailed ledger of all transactions. Save it as CSV. Each line will represent one split (posting) of a transaction. You’ll later need to group lines by transaction for conversion.
Chart of Accounts and Other Lists: QuickBooks Online can export the chart of accounts via Accounting → Chart of Accounts → Batch Actions → Export to Excel. Do this to get account names and types. Likewise, export Customers, Vendors, etc., if you want to carry over names for metadata.
QuickBooks Online API (Optional): For a programmatic approach, Intuit provides a REST API for QBO data. Advanced users can create a QuickBooks Online app (requires a developer account) and use the API to fetch data in JSON. For example, you could query the Account endpoint for the chart of accounts and the JournalEntry or GeneralLedger report endpoints for transactions. There are Python SDKs like python-quickbooks that wrap the API. However, using the API involves OAuth authentication and is overkill for a one-time migration unless you prefer automation. For most cases, the manual export to CSV/Excel is simpler and less error-prone.
Once you have QuickBooks data in CSV (and/or IIF), the next step is to convert it into Beancount’s plain-text ledger format. This involves parsing the exports, mapping QuickBooks accounts to a Beancount chart of accounts, and formatting transactions in Beancount syntax.
2.1 Parsing QuickBooks Exports with Python
Using Python will ensure accuracy and reproducibility for the transformation. We’ll outline scripts for two key tasks: importing the chart of accounts and converting transactions.
Accounts Import and Mapping: It’s crucial to set up your accounts in Beancount before adding transactions. QuickBooks accounts have types (Bank, Accounts Receivable, Expense, etc.) which we will map to Beancount’s hierarchy (Assets, Liabilities, Income, Expenses, etc.). For example, we can use a mapping like:
AccountTypeMap = {
'BANK': 'Assets',
'CCARD': 'Liabilities',
'AR': 'Assets',
'AP': 'Liabilities',
'FIXASSET': 'Assets',
'OASSET': 'Assets',
'OCASSET': 'Assets',
'LTLIAB': 'Liabilities',
'OCLIAB': 'Liabilities',
'EQUITY': 'Equity',
'INC': 'Income',
'EXP': 'Expenses',
'EXINC': 'Income',
'EXEXP': 'Expenses',
}
Using the QuickBooks Desktop IIF export or QBO’s account list CSV, we retrieve each account’s name and type. Then:
-
Create Beancount account names: QuickBooks sometimes uses colons (:) in account names to denote subaccounts (e.g., “Current Assets:Checking”). Beancount uses the same colon notation for hierarchy. You can often reuse the name directly. If the QuickBooks account names do not start with a category, prepend the mapped category. For example, a QuickBooks account of type BANK named "Checking" will become Assets:Checking in Beancount. An EXP (expense) account "Meals" becomes Expenses:Meals, etc.
-
Ensure valid naming: Remove or replace any characters that might confuse Beancount. QuickBooks allows characters like & or / in names. It’s wise to strip out or replace special characters (e.g., replace & with and, remove slashes or spaces). Also, ensure all account names are unique after transformation – QuickBooks might have allowed same subaccount name under different parents which is fine, but in Beancount the full name (with parents) must be unique. If needed, rename or append a qualifier to distinguish them.
-
Output account openings: In Beancount, every account used must be opened with an open directive. You can pick a date before your first transaction (e.g., if migrating 2019–2023 data, use 2018-12-31 or an even earlier date for all opens). The script will write lines like:
2018-12-31 open Assets:Checking USD
2018-12-31 open Expenses:Meals USD
for each account (assuming USD is the main currency). Use the appropriate currency for each account (see multi-currency notes below).
Transaction Conversion: The main challenge is converting the QuickBooks transaction export (CSV) into Beancount entries. Each QuickBooks transaction (invoice, bill, check, journal entry, etc.) can have multiple splits (lines) that must be gathered into one Beancount transaction.
We will use Python’s CSV reader to iterate through the exported lines and accumulate splits:
import csv
from collections import defaultdict
rows = []
with open('quickbooks_exported_journal.csv', 'r', encoding='utf-8') as f:
reader = csv.DictReader(f)
for line in reader:
rows.append(line)
transactions = defaultdict(list)
for line in rows:
trans_id = line.get('Trans #') or line.get('Transaction ID') or line.get('Num')
transactions[trans_id].append(line)
Now transactions is a dictionary where each key is a transaction ID/number and the value is the list of splits for that transaction. Next, we convert each group to Beancount:
def format_date(qb_date):
m, d, y = qb_date.split('/')
return f"{y}-{int(m):02d}-{int(d):02d}"
output_lines = []
for trans_id, splits in transactions.items():
splits = sorted(splits, key=lambda x: x.get('Line') or 0)
first = splits[0]
date = format_date(first['Date'])
payee = first.get('Name', "").strip()
memo = first.get('Memo', "").strip()
output_lines.append(f"{date} * \"{payee}\" \"{memo}\"")
if first.get('Num'):
output_lines.append(f" number: \"{first['Num']}\"")
for split in splits:
acct_name = split['Account'].strip()
beancount_acct = account_map.get(acct_name, acct_name)
amount = split.get('Amount') or ""
debit = split.get('Debit') or ""
credit = split.get('Credit') or ""
if amount:
amt_str = amount
else:
amt_str = debit if debit else f"-{credit}"
amt_str = amt_str.replace(",", "")
currency = split.get('Currency') or "USD"
amt_str = f"{amt_str} {currency}"
line_memo = split.get('Memo', "").strip()
comment = f" ; {line_memo}" if line_memo else ""
output_lines.append(f" {beancount_acct:<40} {amt_str}{comment}")
output_lines.append("")
This script logic does the following:
-
Formats the date to YYYY-MM-DD for Beancount.
-
Uses the payee (Name) and memo for the transaction narration. For example:
2020-05-01 * "ACME Corp" "Invoice payment"
(If no payee, you might use the QuickBooks transaction Type or leave the payee empty quotes).
-
Adds a number metadata if there's a reference number (check #, invoice #, etc.).
-
Iterates through each split line:
- Maps the QuickBooks account name to the Beancount account using a dictionary
account_map (populated from the chart of accounts step).
- Determines the amount. Depending on your export, you might have a single Amount column (with positive/negative values) or separate Debit and Credit columns. The code above handles both cases. It ensures credits are represented as negative amounts for Beancount (since in Beancount, a single number with sign is used per posting).
- Attaches the currency (assuming USD unless a different currency column is present).
- Writes the Beancount posting line with the account, amount, and a comment with the line memo. For example:
Assets:Checking 500.00 USD ; Deposit
Income:Sales -500.00 USD ; Deposit
This reflects a $500 deposit (from Income into Checking).
-
After listing all splits, a blank line separates the transaction.
Multi-currency handling: If your QuickBooks data involves multiple currencies, include the currency code on each posting (as shown above). Ensure that accounts that are in foreign currencies are opened with that currency. For example, if you have a bank account in EUR, you’d output open Assets:Bank:Checking EUR and the transactions in that account will use EUR. Beancount supports multi-currency ledgers and will track implicit conversions, but you might need to add price entries for exchange rates if you want conversion to a base currency in reports. It’s also recommended to declare your main operating currency at the top of the Beancount file (e.g., option "operating_currency" "USD").
Running the conversion: Save the Python script (for example, as qb_to_beancount.py) and run it on your exported files. It should produce a .beancount file containing all accounts and transactions.
2.2 Handling Edge Cases and Data Cleaning
During transformation, be mindful of these common gotchas and how to address them:
-
Account Name Mismatches: QuickBooks might have account names that clash with Beancount’s hierarchical names. For instance, QuickBooks could have two different parent accounts each with a subaccount named "Insurance". In Beancount, Expenses:Insurance must be unique. Resolve this by renaming one (e.g., "Insurance-Vehicle" vs "Insurance-Health") before export or map them to unique Beancount accounts in your script. Consistent naming conventions (no special characters, and use of hierarchy) will save headaches. Use the remapping file approach if needed: maintain a CSV or dictionary of old name → new Beancount name and apply it during conversion (our example code uses an account_map and could load overrides from a file).
-
Dates and Formats: Ensure all dates are consistently formatted. The script above normalizes M/D/Y to ISO format. Also, watch out for fiscal year vs calendar year issues if your five-year span crosses a year-end. Beancount doesn’t care about fiscal year boundaries, but you might later want to split files by year for convenience.
-
Numerical Precision: QuickBooks handles currency with cents, so working in cents is usually fine. All amounts should ideally have two decimal places in the CSV. If any amounts turned into integers (no decimal) or have commas/parentheses (for negatives), clean those in the script (strip commas, convert (100.00) to -100.00, etc.). The CSV export if done correctly (as per instructions) should already avoid those formatting issues.
-
Negative Amounts and Signs: QuickBooks reports sometimes show negatives as -100.00 or as (100.00) or even --100.00 in certain Excel exports. The cleaning step should handle these. Ensure that each transaction’s debits and credits balance out to zero. Beancount will enforce this (if not balanced, it will throw an error on import).
-
Transaction Duplicates: If you had to export transactions in batches (e.g., year by year or account by account), be careful to merge them without overlap. Check that the first transaction of a year isn’t also the last of the previous batch, etc. It’s easy to accidentally duplicate a few transactions at the boundaries. If you suspect duplicates, you can sort the final Beancount entries by date and look for identical entries, or use Beancount’s unique transaction tags to catch them. One strategy is to include QuickBooks transaction numbers as metadata (e.g., use the Trans # or invoice number as a txn tag or quickbooks_id metadata) and then ensure no duplicates of those IDs exist.
-
Unbalanced Splits / Suspense Accounts: QuickBooks might have odd cases like a transaction with an imbalance that QuickBooks auto-adjusted to a “Opening Balance Equity” or “Retained Earnings”. For example, when setting up initial account balances, QuickBooks often posts differences to an Equity account. These will appear in the exported transactions. Beancount will require explicit balancing. You might need to introduce an Equity account for opening balances (commonly Equity:Opening-Balances) to mirror QuickBooks. It’s good practice to have an opening balance entry on the first day of your ledger that establishes starting balances of all accounts (see Stage 5).
-
Multi-currency Edge Cases: If using multi-currency, QuickBooks’s export might list all amounts in home currency or in their native currency. Ideally, get the data in native currency for each account (QuickBooks Online’s reports usually do this). In Beancount, each posting carries a currency. If QuickBooks provided exchange rates or a home-currency conversion, you might ignore those and rely on Beancount’s price entries. If QuickBooks did not export exchange rates, you may want to manually add price records (e.g., using Beancount’s price directive) for key dates to match valuation. However, for basic ledger integrity, it’s enough that transactions balance in their original currencies – unrealized gains/losses don’t need to be explicitly recorded unless you want the same reports.
-
Accounts Receivable / Accounts Payable: QuickBooks tracks invoice and bill details (due dates, paid status, etc.) which won’t fully transfer in a plain ledger. You will get the A/R and A/P transactions (invoices increasing A/R, payments decreasing A/R, etc.), but not the invoice documents or customer balances per invoice. As a result, after migration, you should verify that your A/R and A/P account balances in Beancount equal the open balances of customers/vendors in QuickBooks. If you need to track invoices, you might use Beancount’s metadata (e.g., include an invoice tag or link). The QuickBooks invoice numbers should have come through in the Num or Memo fields – our script preserves the Num as number: "..." in the transaction metadata.
-
Inactive or Closed Accounts: The IIF export might include inactive accounts (if you chose to include them). It’s fine to import them (they will just have no transactions and a zero balance if truly inactive). You may mark them as closed in Beancount after the last transaction date with a close directive. This keeps your ledger tidy. For example:
2023-12-31 close Expenses:OldAccount ; closed after migration
This is optional and mostly for cleanliness.
By carefully cleaning and mapping the data as above, you’ll have a Beancount ledger file that structurally mirrors your QuickBooks data. The next step is to verify that it also numerically mirrors QuickBooks.
Stage 3: Data Validation and Reconciliation
Validation is a critical stage in an accounting data migration. We need to ensure the Beancount ledger matches the QuickBooks books to the penny. Several strategies and tools can be used:
3.1 Trial Balance Reconciliation
A trial balance report lists ending balances of all accounts (with debits and credits or positive/negative indicated) and should net to zero. Running a trial balance in both systems for the same date is the fastest way to confirm overall accuracy.
-
In QuickBooks: Run a Trial Balance report for the last day of the final year (e.g., December 31, 2023). This report shows each account’s balance. Export it or note down key figures.
-
In Beancount: Use Beancount’s reporting to generate a trial balance. One easy method is via the command line:
bean-report migrated.beancount balances
The balances report is a trial balance listing all accounts and their balances. You can also open the file in Fava (Beancount’s web UI) and look at the Balances or Balance Sheet section. Every account balance in Beancount should match the QuickBooks trial balance. For example, if QuickBooks shows Accounts Receivable = $5,000, then Beancount’s Assets:Accounts Receivable account should total $5,000 (debit). If Sales Income = $200,000, the Income:Sales in Beancount should show $200,000 (credit, which might display as -200,000 if using a trial balance that presents credits as negatives).
If there are discrepancies, pinpoint them:
- Check if an entire account is missing or extra (did we forget an account or include one that was already closed before the migration period?).
- If a balance is off, drill down: QuickBooks can run an Account QuickReport or ledger detail for that account, and you can compare with Beancount’s register for that account (
bean-report migrated.beancount register -a AccountName). Sometimes differences come from a missing transaction or a duplicate.
Also verify the sum of all accounts is zero in Beancount’s trial balance (it prints a total that should be zero or very close to zero). Beancount enforces double-entry, so if you have any non-zero imbalance, it means assets minus liabilities-equity didn’t net to zero, indicating an issue (which QuickBooks would not normally allow either, but could happen if some data was dropped).
3.2 Account Balance Comparisons
Beyond trial balance, you can compare specific financial statements:
-
Balance Sheet: Run a QuickBooks Balance Sheet for the final date and a Beancount balance sheet (bean-report migrated.beancount balsheet). This is similar to trial balance but organized by Assets, Liabilities, Equity. The numbers should align category-wise. For a more granular check, compare major account totals: cash, A/R, fixed assets, accounts payable, equity, etc.
-
Profit & Loss (Income Statement): Run a Profit & Loss for the five-year period (or year by year) in QuickBooks and in Beancount (bean-report migrated.beancount income for a full-period income statement). The net income from Beancount should equal QuickBooks for each period. If you migrated all five years, the cumulative net income should match. You can also compare individual revenue and expense totals to ensure no category was missed or doubled.
-
Random Transaction Sampling: Pick a few random transactions (especially from each year and each major account) and verify they migrated correctly. For instance, find an invoice from 3 years ago in QuickBooks and then search for its amount or memo in the Beancount file (since all transactions are text, you can open the .beancount file in a text editor or use search tools). Check that the date, amounts, and accounts match. This helps catch any date formatting issues or mis-mapped accounts.
3.3 Automated Integrity Checks
Leverage Beancount’s own validation tools:
-
bean-check: Run bean-check migrated.beancount. This will parse the file and report any errors in syntax or balancing. If the script missed something like an account not opened or a transaction not balanced, bean-check will flag it. A clean pass (no output) means the file is at least internally consistent.
-
Balance Assertions: You may add explicit balance assertions in the ledger for key accounts as an extra check. For example, if you know the bank account balance on a certain date, add a line:
2023-12-31 balance Assets:Bank:Checking 10000.00 USD
Then bean-check will ensure that in the ledger, as of that date, the balance is indeed $10,000. This is optional but can be useful for high importance accounts. You could take ending balances from QuickBooks (e.g., end of each year) and assert them in the Beancount file. If any assertion fails, Beancount will report a difference.
-
Trial Balance Rollforward: If you prefer, you can do a period-by-period check. For each year, compare the net change. For example, net income in QuickBooks 2020 vs Beancount 2020, etc., to ensure each year closed properly into equity (QuickBooks automatically rolls net income into Retained Earnings each new year; in Beancount you’ll just see cumulative equity). If you see differences, it might indicate an issue in a specific year’s data.
-
Transaction Counts and Duplicates: Count the number of transactions in QuickBooks vs Beancount. QuickBooks doesn’t show a direct count easily, but you can gauge by counting lines in the CSV (each transaction header vs splits). In Beancount, a quick way is to count occurrences of txn or * " in the file. They should be equal to or slightly above QuickBooks (if you added opening balance transactions or adjustments). A significant mismatch means something might have been omitted or duplicated. Our use of unique IDs in metadata can assist: if you suspect duplicates, search the Beancount file for the same check number or invoice number appearing twice when it shouldn’t.
-
Reconciliation status: We included a rec: "y" or "n" metadata based on QuickBooks’ cleared status in our script (as rec in the example). This isn’t a standard Beancount feature (Beancount doesn’t track cleared/pending in the same way as Ledger), but it can be helpful metadata. You might verify that all transactions that were reconciled in QuickBooks are present. Ultimately, reconciling bank accounts in Beancount anew (using your statements) could be the final proof that nothing is missing.
By performing these validations, you build confidence that the migration preserved the data. Take your time with this stage – it’s easier to fix anomalies now than months later when books might be relied on. Common issues if validation fails: an account’s opening balance missing, a transaction dated outside the range, or a sign inversion on an entry. All are fixable once identified.
Stage 4: Committing to the Beancount Ledger
After cleaning and validating, it’s time to formalize the data into your Beancount ledger structure. “Committing” here means both finalizing the ledger files and potentially checking them into a version control system for auditability.
4.1 Organizing Ledger Files and Configuration
Decide how to structure the Beancount ledger files. For five years of data, you can keep everything in one file or split by year or category. A common, clear structure is:
- Main Ledger File: e.g.,
ledger.beancount – This is the entry point that can include other files. It might contain global options and then include yearly files.
- Accounts File: Define the chart of accounts and opening balances. For example,
accounts.beancount with all the open directives (as generated by the script). You might also list commodities (currencies) here.
- Transactions Files: One per year, e.g.,
2019.beancount, 2020.beancount, etc., containing transactions for that year. This keeps each file a manageable size and lets you focus on a year if needed. Alternatively, you can split by entity or account, but time-based splitting is straightforward for financial data.
Example main file:
option "title" "My Business Ledger"
option "operating_currency" "USD"
include "accounts.beancount"
include "2019.beancount"
include "2020.beancount"
...
include "2023.beancount"
This way, all data is aggregated when you run reports, but you maintain order.
Beancount doesn’t require multiple files – you could have one big file – but the above structure improves clarity and version control. According to Beancount best practices, it’s good to use clear section headers and group related entries logically.
4.2 Setting Opening Balances and Equity
If your migration is not from an absolute zero start, you’ll need to handle opening balances. Two scenarios:
-
Books starting from scratch: If the five-year period starts at inception of the business (e.g., you started using QuickBooks in Jan 2019 with all accounts zeroed out except initial equity), then you might not need a separate opening balance transaction. The first transactions in 2019 (like initial funding to a bank account) will naturally establish beginning balances. Just ensure any initial capital or retained earnings prior are accounted for via equity transactions.
-
Books mid-stream (partial history): If you began QuickBooks earlier and 2019 is a mid-point, then as of 1 Jan 2019 each account had a balance brought forward. QuickBooks would have those as opening balances or retained earnings. In Beancount, it’s typical to create an Opening Balances entry on the day before your start date:
- Use an equity account called
Equity:Opening-Balances (or similar) to offset the sum of all opening amounts.
- Example: if on 2018-12-31, Cash was $10,000 and A/R $5,000 and A/P $3,000 (credit), you’d write a transaction:
2018-12-31 * "Opening Balances"
Assets:Cash 10000.00 USD
Assets:Accounts Receivable 5000.00 USD
Liabilities:Accounts Payable -3000.00 USD
Equity:Opening-Balances -12000.00 USD
This leaves Opening-Balances with the negative sum (–$12k) which balances the entry. Now all asset/liability accounts start 2019 with correct balances. This should mirror any QuickBooks “Retained Earnings” or carry-over balances.
- Alternatively, use Beancount’s
pad and balance directives: For each account, you can pad it from Opening-Balances and assert the balance. This is a more automated way. For example:
2018-12-31 pad Assets:Cash Equity:Opening-Balances
2018-12-31 balance Assets:Cash 10000.00 USD
This tells Beancount to insert whatever entry needed (to Opening-Balances) so that Cash equals 10000 USD at that date. Do this for each account. The result is similar, but writing an explicit transaction as in the first method is straightforward too.
-
Retained Earnings: QuickBooks doesn’t explicitly export a “Retained Earnings” transaction – it just computes it. After migration, you might notice Equity:Retained Earnings is zero if you didn’t create it. In Beancount, retained earnings are just prior years’ profit. You can choose to create a Retained Earnings account and transfer prior profits into it on the first day of each new year, or simply let equity be the sum of all income/expenses (which appears under Equity section in reports). For transparency, some users journal closing entries annually. This is optional and mainly for presentation. Since we migrated all transactions, the profit for each year will naturally roll up if you run a report per year.
-
Comparative Checks: After setting opening balances, run a balance sheet on the start date to ensure everything is correct (it should show those opening balances vs Opening Equity netting to zero).
4.3 Finalizing and Version Control
Now that the data is in Beancount format and structured, it’s wise to commit the files to a version control repository (e.g., git). Each change to the ledger can be tracked, and you have an audit trail of all modifications. This is a major advantage of plaintext accounting. For example, in QuickBooks changes might not be easily diffable, but in Beancount, you can see line-by-line differences. As some users note, with Beancount you get transparency and the ability to revert changes if needed – every entry can be tied to a change history.
Consider tagging the commit of this initial migration as v1.0 or similar, so you know it represents the state of books as imported from QuickBooks. Going forward, you’ll enter new transactions directly in Beancount (or import from bank feeds, etc.), and you can use normal software development practices (committing monthly or daily, using branches for experiments, etc.).
Setting up Fava or other tools: Fava is a web interface for Beancount that makes it easy to view reports. After committing, run fava ledger.beancount to browse the financial statements and compare them with your QuickBooks reports one last time. You might spot small differences more easily in a UI (for example, an account that should be zero but shows a small balance indicates a missing closing entry or a stray transaction).
Naming conventions and consistency: You have full control now, so ensure consistency:
- All accounts should have clear names, starting with capitalized category names (Assets, Liabilities, etc.). If any look odd (e.g.,
Assets:assets:SomeAccount due to a case mismatch from QuickBooks), rename them in the accounts file and update the transactions (a quick find/replace on the file can do this, or use Beancount’s bean-format or editor multi-cursor).
- Commodity symbols (currency codes) should be consistent. For USD, use
USD everywhere (not $ or US$). For others, use standard codes (EUR, GBP, etc.). This consistency is important for Beancount’s price lookups and reports.
- Remove any temporary or dummy accounts that might have been created (for example, if you used
Expenses:Miscellaneous for unknown accounts in the script as a catch-all, try to eliminate those by properly mapping all accounts).
Closing QuickBooks: At this point, you should have parallel books in Beancount that match QuickBooks. Some choose to run both systems in parallel for a short period to ensure nothing was missed. But if validation is solid, you can “close” the QuickBooks books:
- If this is a corporate environment, consider exporting all QuickBooks source documents (invoices, bills, receipts) for records, since those won’t exist in Beancount unless you attach them manually.
- Keep a backup of the QuickBooks data (both the company file and the export files).
- Going forward, maintain the Beancount ledger as the primary system of record.
By committing the data into the Beancount ledger, you have completed the migration pipeline. The final step is to perform an audit and demonstrate consistency of financial statements, to satisfy yourself (and any stakeholders or auditors) that the migration was successful.
Stage 5: Post-Migration Audit and Examples
To illustrate the success of the migration, prepare a before-and-after comparison of financial statements and possibly a diff of transactions. This provides evidence that the books are consistent.
5.1 Verifying Financial Statements
Produce key financial reports from both QuickBooks and Beancount for the same dates and compare:
-
Balance Sheet as of Dec 31, 2023: Compare Assets, Liabilities, and Equity totals line by line. They should match. For example, if QuickBooks showed Total Assets = $150,000 and Total Liabilities + Equity = $150,000, the Beancount balance sheet should show the same totals. If you structured accounts slightly differently (say you merged some subaccounts), adjust for that in comparison or break down to the next level to ensure sums are equal.
-
Profit & Loss 2019–2023: Ensure total Income, total Expenses, and Net Profit for each year (or the whole range) are identical. Minor differences could arise if QuickBooks did some rounding on reports, but transactions usually carry cents exactly so net profit should be exact. If any year’s profit differs, drill down into that year’s data – often an indicator of a missing or duplicate entry in that period.
-
Trial Balance Differences: If possible, create a spreadsheet where you list each account and the balance from QuickBooks vs Beancount. Since we expect them to match, this might be an all-zero difference column. This is essentially the trial balance cross-check we discussed, but writing it out helps document it.
5.2 Example Comparison (Before vs After)
Below is an example snippet demonstrating data consistency. Let’s say our QuickBooks trial balance for Dec 31, 2023 was:
| Account | QuickBooks Balance (Dec 31, 2023) |
|---|
| Assets | |
| Assets:Bank:Checking | $12,500.00 (debit) |
| Assets:Accounts Receivable | $3,200.00 (debit) |
| Liabilities | |
| Liabilities:Credit Card | $-1,200.00 (credit) |
| Liabilities:Loans Payable | $-5,000.00 (credit) |
| Equity | |
| Equity:Opening-Balances | $-7,500.00 (credit) |
| Equity:Retained Earnings | $-2,000.00 (credit) |
| Equity:Current Year Profit | $0.00 |
In Beancount, after importing and posting all transactions up to 2023, a bean-report balances (trial balance) outputs:
| Account | Beancount Balance (Dec 31, 2023) |
|---|
| Assets | |
| Assets:Bank:Checking | 12,500.00 USD (debit) |
| Assets:Accounts Receivable | 3,200.00 USD (debit) |
| Liabilities | |
| Liabilities:Credit Card | -1,200.00 USD (credit) |
| Liabilities:Loans Payable | -5,000.00 USD (credit) |
| Equity | |
| Equity:Opening-Balances | -7,500.00 USD (credit) |
| Equity:Retained Earnings | -2,000.00 USD (credit) |
| Equity:Profit (2019-2023) | 0.00 USD |
(Note: Equity sections might be structured differently; the key is totals align. Here, “Profit (2019-2023)” in Beancount plays the role of current year profit/retained earnings combined, showing zero because profit was closed into Retained Earnings.)
As shown, every account matches to the cent. The sum of debits equals sum of credits on both sides.
Additionally, if we run a Profit & Loss for 2023:
- QuickBooks: Income $50,000, Expenses $48,000, Net Profit $2,000.
- Beancount: Income $50,000, Expenses $48,000, Net Profit $2,000 (which then got closed to Retained Earnings or appears under Equity in year-end balance sheet).
You can create a diff of transactions if needed, but since QuickBooks data isn’t in ledger form, it’s more effective to rely on reports. One could sort both the QuickBooks CSV and the Beancount transactions by date and compare key fields as a final check (this can be done in Excel or with a script). However, given that we trust our earlier validation, the financial statements check is usually sufficient.
5.3 Auditing Tips
- If an auditor or stakeholder needs reassurance, present the before-and-after financial statements side by side. The transparency of Beancount can actually simplify audits because you can trace every number from a statement back to the source entry quickly (especially using Fava’s drill-down functionality).
- Keep the QuickBooks backup and exported CSVs as part of your audit trail. Document any adjustments made during migration (for example, “Renamed account X to Y for consistency” or “Split transaction Z into two entries for clarity” if you did such changes).
- Going forward, implement regular checks in Beancount. For instance, monthly reconciliation of bank accounts and an assertion of their ending balance helps catch any data issues or mistakes in entry. The migration gives a good baseline; maintaining discipline in the new system will ensure continued accuracy.
Finally, celebrate the migration completion: you have successfully transferred five years of accounting data from QuickBooks to Beancount. The data is now in a lightweight, version-controlled text format with full double-entry integrity. You’ve exported the data, transformed it with Python scripts, validated the integrity through trial balances and reports, and committed it into a well-organized Beancount ledger. This comprehensive process ensures that the Beancount ledger is an accurate, faithful replica of your QuickBooks books over the five-year period, setting you up for streamlined accounting going forward.
