BI & Spreadsheets

Excel + QuickBooks Desktop

Connect Microsoft Excel to QuickBooks Desktop using ODBC or Power Query. Build live spreadsheets, pivot tables, and reports with real accounting data.

Method A

ODBC Data Source

Use the traditional ODBC driver with Excel's Data tab. Works with both 32-bit and 64-bit Excel.

Method B

Power Query

Use the built-in PostgreSQL connector in Power Query. No extra driver install needed.

Method A - ODBC Data Source

  1. Create a DSN. Open ODBC Data Sources (use the 32-bit version for 32-bit Excel, or 64-bit for 64-bit Excel). Click Add, select PostgreSQL Unicode.
  2. Configure the DSN. Set Server to localhost, Port to 5433, Database to quickbooks, User to syntra, and Password to the value from your config.toml.
  3. Test the connection. Click Test to confirm connectivity, then click OK to save.
  4. Import in Excel. Go to Data → Get Data → From Other Sources → From ODBC. Select your DSN and choose tables to import.

Method B - Power Query (PostgreSQL Connector)

  1. Open Power Query. In Excel, go to Data → Get Data → From Database → From PostgreSQL Database.
  2. Enter connection details. Server: localhost:5433, Database: quickbooks.
  3. Authenticate. Select Database authentication. Enter syntra as user and your config.toml password.
  4. Select tables. Browse the Navigator pane, pick the tables you need, and click Load or Transform Data to shape the data first.

Example: Pull Invoice Data

Use this SQL in Power Query's advanced mode or as a custom ODBC query to pull recent invoices with customer names:

SELECT
  i.txn_date,
  i.ref_number   AS InvoiceNumber,
  c.full_name    AS Customer,
  il.item_ref_full_name,
  il.quantity,
  il.rate,
  il.amount
FROM invoices i
JOIN customers c ON i.customer_ref_list_id = c.list_id
JOIN invoice_lines il ON i.txn_id = il.txn_id
WHERE i.txn_date >= '2025-01-01'
ORDER BY i.txn_date DESC;

Tips

  • ✓ Use Refresh All in Excel to pull the latest QuickBooks data on demand.
  • ✓ For 32-bit Excel, make sure you create a 32-bit DSN (use odbcad32.exe from SysWOW64).
  • ✓ Power Query supports scheduled refresh if you publish to SharePoint or OneDrive.

For the full walkthrough with screenshots, see the Excel integration docs →

Ready to connect Excel to QuickBooks?

Download Syntra ODBC and start building spreadsheets with live QuickBooks data in minutes.

Download Free Trial