QuickBooks Data in Excel / Power BI

Connect Excel or Power BI to the QBO staging database to access the same data as the Transaction Detail by Account report in QuickBooks Online.

Data Refresh

Staging tables are refreshed daily at 6:00 AM UTC by the automated Prefect flow.

---

Connection Details

Setting	Value
Server	pg-wasteology.postgres.database.azure.com
Database	wasteology_dev
Port	5432
Schema	qbo
Username	qbo_reader
Password	(contact data team)
SSL Mode	require

---

Quick Start — Excel (ODBC Method)

Excel requires an ODBC driver to connect to PostgreSQL.

Step 1: Install the ODBC Driver (one-time)

1. Download psqlodbc_x64.msi
2. Run the installer and accept defaults
3. Restart Excel

Step 2: Create a DSN (one-time)

1. Press Windows key → type ODBC → open ODBC Data Sources (64-bit)
2. On the User DSN tab, click Add
3. Select PostgreSQL Unicode(x64) → Click Finish
4. Fill in:
   • Data Source: QBO_Staging
   • Server: pg-wasteology.postgres.database.azure.com
   • Database: wasteology_dev
   • Port: 5432
   • User Name: qbo_reader
   • Password: (your password)
   • SSL Mode: require
5. Click Test → should say "Connection successful"
6. Click Save

Step 3: Connect in Excel

1. Data → Get Data → From Other Sources → From ODBC
2. Select QBO_Staging from the dropdown
3. Expand Advanced options
4. In the SQL statement box, paste:

SELECT * FROM qbo.v_transaction_detail_by_account
WHERE acct_num = '21000'
  AND txn_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY txn_date

5. Click OK
6. Power Query will prompt for credentials (it manages them separately from the DSN):
   • Select Database on the left
   • Enter your username and password
   • Click Connect
7. Select Import mode → Click Load

tip

Power Query remembers credentials after the first successful connection — you won't need to enter them again.

---

Quick Start — Power BI Desktop

Power BI has PostgreSQL built-in (no ODBC driver needed):

1. Get Data → PostgreSQL database
2. Server: pg-wasteology.postgres.database.azure.com
3. Database: wasteology_dev
4. Expand Advanced options → paste the SQL query above
5. Click OK
6. Enter credentials → select Database authentication
7. Select Import mode (NOT DirectQuery)
8. Click Load

---

Import vs DirectQuery

Mode	Recommendation	Reason
Import	✅ Use this	Data loads into memory — fast visuals, works offline
DirectQuery	❌ Avoid	Queries the database on every click — causes timeouts with 900K+ rows

Symptom of DirectQuery: Only a few rows appear (e.g., 17 rows instead of 22,000).

---

SQL Query Examples

Change the WHERE clause to pull different data:

-- AP (Accounts Payable) for a specific month
SELECT * FROM qbo.v_transaction_detail_by_account
WHERE acct_num = '21000'
  AND txn_date BETWEEN '2026-01-01' AND '2026-01-31'

-- Bank account activity for a year
SELECT * FROM qbo.v_transaction_detail_by_account
WHERE acct_num = '10300'
  AND txn_date >= '2025-01-01'

-- All AP data (~720K rows — larger dataset)
SELECT * FROM qbo.v_transaction_detail_by_account
WHERE acct_num = '21000'

-- Summary by transaction type
SELECT txn_type, COUNT(*) as count, SUM(amount) as total
FROM qbo.v_transaction_detail_by_account
WHERE acct_num = '21000'
  AND txn_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY txn_type
ORDER BY txn_type

---

Available Views

View	Rows	Best For
v_transaction_detail_by_account	904K	Replicating QBO "Transaction Detail by Account"
v_accounts	~230	Chart of Accounts lookup
v_vendors	~4,800	Vendor list with payment history
v_ap_aging	varies	Open AP bills with aging buckets
v_ap_aging_summary	varies	AP totals by vendor
v_ar_aging	varies	Open AR invoices with aging buckets

---

Key Accounts

Account #	Account Name	Description
21000	Accounts Payable	All vendor bills and payments
10300	Stock Yards Checking	Main operating bank account
10350	Stock Yards AR Checking	AR deposits
11000	Accounts Receivable	Customer invoices

---

Understanding the Data

Transaction Types

Type	What It Is	Effect on Account
Bill	Vendor invoice received	Increases AP (+)
Bill Payment (Check)	Payment to vendor via check	Decreases AP and Bank (-)
Bill Payment (Credit Card)	Payment to vendor via CC	Decreases AP and CC (-)
Vendor Credit	Credit from vendor	Decreases AP (-)
Transfer	Money moved between accounts	+/- depending on direction
Journal Entry	Manual accounting adjustment	Varies
Deposit	Money deposited to bank	Increases Bank (+)
Invoice	Customer invoice sent	Increases AR (+)

Amount Sign Convention

• Positive amounts (+): Increase the account balance
• Negative amounts (-): Decrease the account balance

This matches QBO's "Transaction Detail by Account" report.

---

Validation

Reference Totals

Account	Date Range	Rows	Total
21000 (AP)	Jan 2026	21,954	-$218,388.74
21000 (AP)	All time	719,809	$2,886,726.33

How to Validate

1. In QBO: Reports → search "Transaction Detail by Account" → set date range and account → note the TOTAL
2. In Excel/Power BI: After loading filtered data → SUM the amount column → should match QBO

---

Troubleshooting

Connection stopped working after server outage

After a prolonged server outage (hours or days), Power Query's cached credentials and connection state can become stale — even after the database is back online. The DSN may test fine, but Excel/Power BI refuses to connect.

Fix:

1. In Excel: Data → Get Data → Data Source Settings
2. Find all entries for QBO_Staging or pg-wasteology
3. Click Clear Permissions on each, then Delete
4. Close the dialog
5. Open ODBC Data Sources (64-bit) → select QBO_Staging → Configure → Test to confirm the DSN still works
6. Back in Excel, reconnect: Data → Get Data → From Other Sources → From ODBC
7. When prompted, select Database authentication and re-enter your credentials

Why this happens

Power Query caches connection tokens separately from the Windows ODBC DSN. During a long outage, repeated failed connection attempts can corrupt this cache. Clearing Data Source Settings forces Power Query to establish a fresh connection.

"PostgreSQL Database" option not available in Excel

Excel doesn't have a built-in PostgreSQL connector — you need the ODBC driver.

Fix: Install the PostgreSQL ODBC driver and use From ODBC instead.

"Only a few rows returned"

Usually caused by DirectQuery mode timing out.

Fix:

1. Delete the current data source
2. Re-add using the SQL query method (see Quick Start)
3. Select Import mode, not DirectQuery

"Can't connect to server"

• Check your network / VPN connection
• Verify the server name: pg-wasteology.postgres.database.azure.com
• Contact IT to whitelist your IP address

"Access denied" or "Authentication failed"

• Verify username and password are correct
• Select Database authentication, not "Windows"
• Contact the data team for credentials

DSN works but Excel still fails with "Authentication failed"

Power Query caches credentials separately from the Windows DSN.

Fix:

1. In Excel: Data → Get Data → Data Source Settings
2. Find entries for QBO_Staging or pg-wasteology
3. Click Clear Permissions or Delete
4. Try connecting again — enter fresh credentials when prompted

"No data returned"

• Check your filters — they may be too restrictive
• Verify the account number exists (use v_accounts)
• Try a broader date range

"Query timeout"

• Use a SQL query with filters instead of loading the full view
• Reduce the date range
• Make sure you're using Import mode, not DirectQuery

---

Tips

1. Always filter first — never try to load all 904K rows
2. Use Import mode — avoid DirectQuery for large datasets
3. Use SQL queries — faster and more reliable than browsing/filtering
4. Refresh data — data updates daily at 6 AM UTC
5. Date format — use YYYY-MM-DD in SQL queries
6. Power BI Desktop — easier setup than Excel (no driver needed)

---

Need Help?

Contact the Data Engineering team for:

• Database credentials
• IP whitelisting
• Data questions
• Custom queries