Pull QuickBooks bills into Microsoft Excel
Many finance teams keep accounts payable in Excel. Load QuickBooks into your data warehouse, join bills to what was paid against them, and let the workbook pull a checked Bills Data Mart instead of a monthly export.
%20scale(1.25)%22%20fill%3D%22%23fff%22%20d%3D%22M12%200A12%2012%200%200%200%200%2012a12%2012%200%200%200%2012%2012%2012%2012%200%200%200%2012-12A12%2012%200%200%200%2012%200zm.642%204.1335c.9554%200%201.7296.776%201.7296%201.7332v9.0667h1.6c1.614%200%202.9275-1.3156%202.9275-2.933%200-1.6173-1.3136-2.9333-2.9276-2.9333h-.6654V7.3334h.6654c2.5722%200%204.6577%202.0897%204.6577%204.667%200%202.5774-2.0855%204.6666-4.6577%204.6666H12.642zM7.9837%207.333h3.3291v12.533c-.9555%200-1.73-.7759-1.73-1.7332V9.0662H7.9837c-1.6146%200-2.9277%201.316-2.9277%202.9334%200%201.6175%201.3131%202.9333%202.9277%202.9333h.6654v1.7332h-.6654c-2.5725%200-4.6577-2.0892-4.6577-4.6665%200-2.5771%202.0852-4.6666%204.6577-4.6666Z%22%2F%3E%0A%3C%2Fsvg%3E%0A)
QuickBooksConnectedA/P Aging Summar…A/R Aging Summar…AccountsBalance Sheet re…
Microsoft ExcelRealmReportReportDaily 09:00QuickBooks Endpoints
26 QuickBooks endpoints you can import. Each lands in its own table in your data warehouse.
A/P Aging Summary report
A/P Aging Summary as of report_date (window end): one row per vendor with aging buckets.
11 fieldsTime seriesA/R Aging Summary report
A/R Aging Summary as of report_date (window end): one row per customer with Current, 1-30, 31-60, 61-90, 91+ and Total columns.
11 fieldsTime seriesAccounts
Chart of accounts, active and inactive.
20 fieldsBalance Sheet report
Balance Sheet as of the run window end (Assets, Liabilities, Equity by account).
12 fieldsTime seriesBill payments
Payments against vendor bills; joins to Bill through Line[].LinkedTxn.
23 fieldsBills
Vendor bills (payables).
25 fieldsCash Flow report
Statement of Cash Flows for the run window.
12 fieldsTime seriesCompany info
One row per company: names, country, fiscal-year start.
15 fieldsCredit memos
Credits issued to customers (reduce receivables; negative revenue in reconciliations).
23 fieldsCustomers
Customers, including inactive ones and sub-customers, with open balance and a flattened billing address.
28 fieldsDeposits
Bank deposits, including the ones that group undeposited customer payments.
21 fieldsGeneral Ledger Detail report
General Ledger: every posting line per account for the run window, with opening balance and totals.
12 fieldsTime seriesInvoice changes (CDC, incl. deletes)
Change data capture for Invoice: every invoice created, updated or deleted since the run window start, full payload, deleted ones with status = Deleted.
9 fieldsTime seriesInvoices
Invoices with header amounts, customer and Line[] as JSON (SalesItemLineDetail.ItemRef, Qty, UnitPrice, Amount).
36 fieldsItems
Products and services with price, purchase cost and the three account links.
25 fieldsJournal entries
Manual and adjusting journal entries; the postings the transaction tables miss.
15 fieldsPayments
Customer payments.
21 fieldsPreferences
One row per company: home currency, multi-currency flag, fiscal-year and tracking settings.
17 fieldsProfit and Loss report
Profit and Loss per run window, one column per month (summarize_column_by=Month), rows nested by account group.
12 fieldsTime seriesPurchases
Expenses paid directly (cash, check, card) without a bill.
26 fieldsRefund receipts
Money refunded to customers.
26 fieldsSales by Customer report
Sales by Customer Summary for the run window.
12 fieldsTime seriesSales receipts
Sales paid at the time of sale (no receivable).
27 fieldsTransfers
Money moved between balance-sheet accounts.
16 fieldsVendor Expenses report
Expenses by Vendor for the run window.
12 fieldsTime seriesVendors
Vendors, including inactive ones, with open payable balance.
19 fields
Why QuickBooks bills do not go straight to Excel
Bills can be exported from QuickBooks to Excel, but not reliably, at scale or business-ready. An export is one company at one moment, with no payments to the vendor beside it. The data warehouse joins bills to bill payments and vendors first.
- A bill shows what is owed, not what was already paid against it
- The data analyst joins bills to bill payments on vendor_id
- The workbook reads the same payables as the close
QuickBooksBills, due datesMicrosoft Exc…
Data warehouseJoined by idStep 1: Load QuickBooks into your data warehouse first
Create a Data Mart with QuickBooks as the source and choose where the data lands. The QuickBooks connector is a custom connector built in OWOX Data Marts, and the data goes into any popular data warehouse.
- Bills is one of 26 endpoints; the workbook reads it
- Authorize QuickBooks Online with OAuth and give the company (realm) id
- Open the QuickBooks connector for your data warehouse below
QuickBooksData MartGoogle BigQuery
AWS Athena
Snowflake
AWS Redshift
Databricksquickbooks.billStep 2: Link QuickBooks bills to vendors and bill payments
In your data warehouse the model joins Bills and Bill payments on vendor_id, so the amount still owed to each vendor comes from the bills and the payments together.
- Bills and bill payments join on vendor_id
- A bill payment names the bills it settles, so open items are findable
- balance means the same in the workbook and the close
Step 3: Pull QuickBooks data into Excel with the add-in
In Excel, add the OWOX Data Marts add-in and sign in. Choose the published QuickBooks Data Mart (Bills), tick the columns you want and press Create & Run.
- Pick vendor_name, txn_date and due_date; balance and total_amt follow
- Excel reads your data warehouse, so a QuickBooks login never sits in the file
Microsoft ExcelYour columns stayvendor_nametxn_datedue_dateStep 4: Refresh the QuickBooks workbook when you need it
Refresh before a payment run or at month end. Imported columns update; your own columns and formulas stay.
- New bills arrive as new rows below the ones you imported
- A refresh is a click, with no job to run
Microsoft ExcelYour column staysvendor_na…txn_datedue_dateYoursStep 5: Reuse QuickBooks bills beyond the workbook
Bills in this Data Mart also show up in OWOX, where your assistant for data can read them. Ask for any report, then get the result in Google Sheets or Excel in one click.
- Ask which vendors have the largest open balance
- balance matches in chat and workbook
Google SheetsOpenExcelOpen
- 0.41.0Dates from Google Sheets arrive as dates, not text
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.40.0Connecting a plan from Google Sheets takes fewer guesses
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.39.0Long Facebook Ads imports no longer fail on a corrupted response
Why OWOX for QuickBooks in Excel
A QuickBooks export gives Excel one list at a time. OWOX gives it the payables side of the books, linked to payments.
- Owed and paid together. Bills join bill payments and vendors in your data warehouse, so open items are computed, not retyped.
- Excel stays the front end. The finance team keeps its workbook; the numbers come from the Data Mart.
- Your own columns stay. A refresh rewrites only the imported columns, so accruals and notes you added survive.
- One place to fix. When QuickBooks's API changes, you fix the connector once, not every macro.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.