Excel

Microsoft Excel is one of the most popular tools for working with QuickBooks data. Syntra ODBC lets you pull live QuickBooks data directly into Excel spreadsheets using standard SQL queries.

Prerequisites

• Syntra installed and running. The installer registers a System DSN named Syntra QuickBooks the first time it runs; you don’t need to configure an ODBC data source by hand. See ODBC Driver Setup if the DSN isn’t present.
• Microsoft Excel 2016 or later (any version with the modern Get Data ribbon).

Method 1: ODBC Data Source (Get Data)

This method uses Excel’s built-in data connection wizard.

Step 1: Open the Data Tab

1. Open Excel and go to the Data tab.
2. Click Get Data > From Other Sources > From ODBC.

Step 2: Select Your DSN

1. Choose your Syntra ODBC data source from the dropdown.
2. If prompted for credentials, enter your Syntra username and password.
3. Click OK.

Step 3: Choose Tables or Write a Query

• Navigator view: Browse available QuickBooks tables (customers, invoices, item_inventories, accounts, bills, etc.) and select the ones you want.
• Advanced options: Enter a custom SQL query for more control.

SELECT name, balance, phone, email
FROM customers
WHERE balance > 0
ORDER BY balance DESC

Step 4: Load or Transform

• Click Load to import data directly into a worksheet.
• Click Transform Data to open Power Query Editor for filtering, renaming columns, or combining tables before loading.

Method 2: Power Query via ODBC (Recommended)

Power Query gives you a richer experience with automatic refresh, parameterised queries, and data transformation. Go through the ODBC connector (the same one Method 1 uses) — Power Query lives under the same menu.

Step 1: Open Power Query

1. Go to Data → Get Data → From Other Sources → From ODBC.
2. Pick the Syntra QuickBooks DSN from the dropdown.
3. (Optional) Expand Advanced options to paste a specific SQL query instead of loading an entire table — see the “Writing Custom Queries” section below.
4. Click OK.

Step 2: Authenticate

Excel prompts for credentials on first connect:

1. Click Database on the left.
2. Enter the username and password from the [auth] section of config.toml (installer defaults are qbconnect / changeme).
3. Click Connect.

Step 3: Select Tables

The Navigator pane shows every QuickBooks entity. Tick the tables you need, or click Transform Data to land in the Power Query Editor for filtering, renaming, or merging before you load.

Step 4: Refresh Data

Once loaded, you can refresh the data at any time:

• Manual: Click Data > Refresh All
• Automatic: Right-click the query in the Queries pane, select Properties, and set a refresh interval.

Writing Custom Queries

For complex reporting, use the Advanced options section to write SQL directly:

SELECT
    i.ref_number AS InvoiceNumber,
    c.name,
    i.txn_date,
    i.balance_remaining
FROM invoices i
JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.txn_date >= '2025-01-01'
ORDER BY i.txn_date DESC

Tips

• Large datasets: Add LIMIT clauses to avoid pulling excessive data into Excel.
• Stale data: If you need real-time data, prefix your query with SET QB_MAX_STALENESS = 0; to bypass the cache.
• Named ranges: Load data into Excel Tables for easier use in formulas and charts.
• Pivot tables: Load data via Power Query, then create PivotTables on top of the connection for dynamic reporting.