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 ODBC installed and running
• An ODBC Data Source (DSN) configured (see ODBC Driver Setup)
• Microsoft Excel 2016 or later

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, Items, 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 (Recommended)

Power Query provides a richer experience with automatic refresh and data transformation capabilities.

Step 1: Open Power Query

Syntra’s built-in server is compatible with Excel’s PostgreSQL database connector.

1. Go to Data > Get Data > From Database > From PostgreSQL Database.
2. Enter the server address: localhost:5433
3. Enter the database name: quickbooks

Step 2: Authenticate

1. Select Database authentication.
2. Enter your username (syntra) and password.
3. Click Connect.

Step 3: Select Tables

The Navigator pane shows all available QuickBooks tables. Select the tables you need, or click Transform Data to customize.

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.