Analyze QuickBooks Expense Trends and Vendor Creep from CSV

How to export vendor and account expense transactions from QuickBooks

For full drill-down by both GL account and vendor, export Transaction Detail by Account (or Transaction List by Vendor).

1. In QuickBooks Online, open Reports and select Transaction Detail by Account (or Expenses by Vendor Summary).
2. Set Report period to Last 12 Months (or This Year-to-date) and click Run report.
3. Click Export and export to CSV, or choose Export to Excel. Drop a .csv above for a first chart, or import the .csv or .xlsx file into the xmetric app to build the expense view.

QuickBooks Transaction Detail CSV columns used

• Date (or Transaction Date): date of the bill, expense, or credit card charge.
• Transaction Type: such as Expense, Bill, Credit Card Expense, Vendor Credit, or Journal Entry.
• Name (or Vendor): the payee or vendor name.
• Account / Distribution Account (or Split): the GL expense category.
• Amount: signed amount of the transaction.

Tableau Table Calculations, LODs, and SQL for Expense Creep

You can test each formula below in the formula playground.

1. Month-over-Month Vendor Spend Delta (Table Calculation)

In the xmetric app, place DATETRUNC('month', [Date]) on Columns, [Name] on Rows, and compute the jump against the trailing 3-month average:

// [Spend vs Trailing 3M Avg]
SUM([Amount]) - WINDOW_AVG(SUM([Amount]), -3, -1)

2. New Vendor Flag (FIXED LOD)

Isolate vendors paid for the very first time this month:

// [First Paid Month for Vendor]
{ FIXED [Name] : MIN(DATETRUNC('month', [Date])) }

// [Is New Vendor This Month]
DATETRUNC('month', [Date]) = [First Paid Month for Vendor]

Equivalent SQL

WITH vendor_monthly AS (
  SELECT
    COALESCE(NULLIF(TRIM("Name"), ''), '(Unassigned Vendor)') AS vendor_name,
    TRIM("Account") AS gl_account,
    DATE_TRUNC('month', CAST("Date" AS DATE)) AS spend_month,
    SUM(CAST(REPLACE(REPLACE("Amount", ',', ''), '$', '') AS DOUBLE)) AS monthly_spend,
    COUNT(*) AS tx_count
  FROM quickbooks_transactions
  WHERE "Date" IS NOT NULL
    AND TRIM("Date") <> ''
    AND "Transaction Type" IN ('Expense', 'Bill', 'Credit Card Expense', 'Check', 'Vendor Credit', 'Journal Entry')
  GROUP BY 1, 2, 3
),
with_window AS (
  SELECT
    *,
    AVG(monthly_spend) OVER (
      PARTITION BY vendor_name, gl_account
      ORDER BY spend_month
      ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS trailing_3m_avg,
    MIN(spend_month) OVER (PARTITION BY vendor_name) AS first_vendor_month
  FROM vendor_monthly
)
SELECT
  spend_month,
  vendor_name,
  gl_account,
  tx_count,
  ROUND(monthly_spend, 2) AS monthly_spend,
  ROUND(trailing_3m_avg, 2) AS prior_3m_avg_spend,
  ROUND(monthly_spend - COALESCE(trailing_3m_avg, 0), 2) AS spend_spike_dollars,
  CASE WHEN spend_month = first_vendor_month THEN 'New Vendor' ELSE 'Existing Vendor' END AS vendor_status
FROM with_window
ORDER BY spend_month DESC, spend_spike_dollars DESC;

Common QuickBooks expense export traps

• Blank Name on credit card feeds: When bookkeepers categorize bank feed rules without assigning a Payee (Name), large amounts of SaaS and ad spend land under (Unassigned Vendor). In the xmetric app, filter to [Name] = '(Unassigned Vendor)' and group by Memo/Description to uncover the real merchants.
• Duplicate vendor spellings: Bank feeds and bills often create separate vendor rows like AWS, Amazon Web Services, and AMZN MKTP. Use UPPER(TRIM([Name])), or a CASE calculated field that maps known aliases to one name, to consolidate fragmented vendor names.
• Balance Sheet accounts mixed into Transaction Detail: If you export Transaction Detail by Account without filtering account types, bank transfers and credit card payments appear alongside P&L expenses. Filter to Expense, Cost of Goods Sold, and Other Expense accounts.

Audit opex and SaaS sprawl with AI agents

Every filter and formula in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your assistant will be able to scan a QuickBooks export and list every vendor whose monthly bill jumped by more than 20%. Live connections to Postgres and BigQuery are also coming.