Excel and Power Query

Connect Excel to QuickBooks Desktop with an ODBC driver

Load invoices, customers, and account balances into Excel, then refresh your workbook without repeating a CSV export. Syntra’s QuickBooks ODBC driver connects Excel’s Power Query to QuickBooks Desktop through a reusable data source.

This guide covers Excel on Windows and QuickBooks Desktop Pro, Premier, or Enterprise 2018+. Syntra does not connect to QuickBooks Online.

Before you connect

• Install Syntra on the Windows machine running QuickBooks and authorize your company file. Allow the initial sync to finish.
• Use Excel for Windows with Data → Get Data → From Other Sources → From ODBC. The bundled driver supports both 32-bit and 64-bit Excel.
• For the steps below, run Excel on the same machine as Syntra. A remote workstation needs the driver, a DSN pointing to the Syntra host, and configured network access.

1. Match the driver to 32-bit or 64-bit Excel

In Excel, open File → Account → About Excel to check Office’s architecture. Match the ODBC driver to Excel, even when Windows itself is 64-bit.

• 64-bit Excel: use C:\Windows\System32\odbcad32.exe.
• 32-bit Excel: use C:\Windows\SysWOW64\odbcad32.exe.

The installer includes both drivers and creates a System DSN named Syntra QuickBooks. If it is missing, follow the ODBC driver setup guide.

2. Choose the Syntra QuickBooks data source in Power Query

1. Open Excel and select Data → Get Data → From Other Sources → From ODBC.
2. Choose Syntra QuickBooks from the DSN list.
3. When prompted, choose database authentication and enter the credentials from Syntra’s config.toml. The installer’s default username is qbconnect; use your configured password.
4. In Navigator, choose a table such as invoices. Select Load for a worksheet or Transform Data to filter and shape it.

The DSN connects to 127.0.0.1:5433, database qbconnect. You do not need to install PostgreSQL to use the bundled ODBC driver.

3. Import open invoices with SQL

To select specific columns, expand Advanced options in the ODBC connection dialog and enter a SQL statement. This example returns one row per unpaid invoice:

SELECT
  ref_number AS invoice_number,
  customer_ref_full_name AS customer,
  txn_date,
  due_date,
  balance_remaining
FROM invoices
WHERE balance_remaining > 0
ORDER BY due_date, ref_number;

Load the result into an Excel table, then use a PivotTable to summarize outstanding balances by customer. For invoice-line reporting, keep line amounts separate from invoice totals so joins do not multiply the totals.

4. Refresh the workbook and understand data freshness

Use Data → Refresh All to rerun the query. Syntra normally returns cached data from its most recent sync. Refreshing Excel does not itself guarantee an uncached QuickBooks read.

The Pro plan supports direct Enterprise live reads for supported tables when configured. Review Syntra’s caching behavior when reports need to reconcile with a recent edit.

For unattended reporting, test the refresh host, driver, credentials, and access to Syntra. Saving a workbook to OneDrive or SharePoint alone does not configure an unattended ODBC refresh.

Common Excel ODBC connection problems

• DSN not listed: check Excel’s bitness and the matching ODBC administrator. Confirm the entry is a System DSN.
• Connection refused: start Syntra and confirm that the host and port in the DSN match your configuration.
• Authentication failed: use Syntra’s configured credentials, rather than your QuickBooks login.
• Older values after a refresh: check the last Syntra sync and the configured read source.

Continue with the Excel connection reference, additional Excel SQL examples, or Microsoft’s Power Query documentation.