Skip to content

Monthly Transaction Spreadsheet Export Documentation

Menu Location: Reports > Order Data Exports > Monthly Transaction Spreadsheet Export-XLSX

Access Level: Administrator and above

Last Updated: 2026-03-01

Overview

The Monthly Transaction Spreadsheet Export generates a comprehensive Excel workbook containing all financial transactions for a selected month. This is the primary tool for monthly bookkeeping, financial reconciliation, and accounting software integration.

Primary Functions:

  • Export all monthly transactions in Excel format
  • Generate accounting-ready financial reports
  • Reconcile revenue, payments, refunds, and credits
  • Track payment method breakdowns
  • Create monthly financial summaries for board reporting

Page Layout

Header Section

  • Page Title: "Monthly Transaction Spreadsheet Export"
  • Month/Year Selector: Dropdown to choose export month
  • Export Button: Generate Excel workbook
  • Quick Links: Access previous months' exports

Export Options

  • Standard Export: All transactions with default categories
  • Detailed Export: Includes itemized order breakdowns
  • Summary Only: High-level monthly financial summary
  • Custom Date Range: Select specific date range within month

Export History

List of previously generated monthly exports with download links (stored for 90 days)

Excel Workbook Structure

Sheet 1: Transaction Summary

Monthly overview with key financial metrics:

  • Total Revenue
  • Total Payments Collected
  • Total Refunds Issued
  • Total Credits Applied
  • Net Revenue
  • Outstanding Balance
  • Payment Method Breakdown

Sheet 2: All Transactions

Complete transaction log with columns:

  • Transaction Date
  • Transaction Type (Sale, Payment, Refund, Credit)
  • Customer Name and ID
  • Order Number (if applicable)
  • Amount
  • Payment Method
  • Status
  • Notes

Sheet 3: Daily Totals

Daily revenue breakdown:

  • Date
  • Number of Orders
  • Gross Revenue
  • Payments Collected
  • Refunds
  • Net Revenue
  • Running Total

Sheet 4: Payment Methods

Breakdown by payment method:

  • Credit Card
  • ACH/Bank Transfer
  • Cash
  • Check
  • Account Credit
  • Other

Sheet 5: Refunds & Credits

Detailed list of all refunds and credits issued:

  • Date
  • Customer
  • Amount
  • Reason
  • Issued By (admin name)
  • Related Order

Sheet 6: Charts & Visualizations

Pre-built charts:

  • Daily revenue trend
  • Payment method pie chart
  • Revenue vs. payments collected
  • Week-over-week comparison

Using the Monthly Transaction Export

Generating a Monthly Export

Steps:

  1. Select month and year from dropdown
  2. Choose export type (Standard, Detailed, Summary)
  3. Click "Generate Export"
  4. Wait for processing (30-120 seconds depending on transaction volume)
  5. Excel file downloads automatically
  6. Export also saved to Export History

Best Time to Generate:

  • First business day of new month for previous month's data
  • Ensures all late transactions and adjustments are included
  • Allows time for month-end reconciliation

Opening and Using the Export

Steps:

  1. Open downloaded .xlsx file in Excel or Google Sheets
  2. Review Summary sheet for monthly overview
  3. Check daily totals for anomalies
  4. Review refunds/credits for accuracy
  5. Use Charts sheet for visual analysis
  6. Export or copy data to accounting software

Excel Tips:

  • Freeze top row for easier scrolling (View > Freeze Panes)
  • Apply filters to transaction list (Data > Filter)
  • Create pivot tables for custom analysis
  • Save with descriptive name: "Transactions_YYYY-MM.xlsx"

Common Use Cases

Use Case 1: Monthly Bookkeeping

Goal: Import monthly financial data into QuickBooks

Steps:

  1. Generate Standard Export for completed month
  2. Open Excel file
  3. Review Summary sheet for overall accuracy
  4. Navigate to All Transactions sheet
  5. Copy transaction data
  6. Import into QuickBooks using "Add/Edit Multiple Entries"
  7. Map columns: Date→Date, Amount→Amount, Customer→Customer
  8. Review import summary
  9. Reconcile imported totals against bank deposits

Verification:

  • Check that total revenue matches Summary sheet
  • Verify payment deposits match bank statement
  • Confirm refunds match refund log

Use Case 2: Monthly Board Reporting

Goal: Create financial presentation for board meeting

Steps:

  1. Generate export for month being reported
  2. Open Charts & Visualizations sheet
  3. Copy revenue trend chart to PowerPoint
  4. Copy payment method breakdown to presentation
  5. Add Summary sheet metrics to slides:
    • Total revenue with % change vs. last month
    • Net revenue after refunds
    • Outstanding balance trend
  6. Highlight notable transactions or anomalies
  7. Compare to budget or forecast

Key Metrics to Present:

  • Month-over-month growth
  • Average transaction value
  • Payment collection rate
  • Refund rate as % of revenue

Use Case 3: Revenue Reconciliation

Goal: Ensure all revenue is accounted for correctly

Steps:

  1. Generate Detailed Export for the month
  2. Sum total revenue from All Transactions sheet
  3. Compare to dashboard monthly revenue total
  4. Check Daily Totals sheet for any unusual spikes or dips
  5. Investigate discrepancies:
    • Check for cancelled orders not reflected
    • Verify late payments credited to correct month
    • Confirm refunds properly recorded
  6. Add notes to workbook documenting findings
  7. Save reconciled version

Common Reconciliation Issues:

  • Orders placed in one month, paid in another
  • Refunds processed after month-end
  • Adjustments or manual credits
  • Cancelled orders not properly reflected

Use Case 4: Tax Preparation

Goal: Provide accountant with monthly transaction records

Steps:

  1. Generate exports for all 12 months of tax year
  2. Create folder: "2026_Financial_Exports"
  3. Save each month's export with consistent naming
  4. Generate annual summary:
    • Sum all 12 Summary sheets
    • Calculate quarterly totals
    • Breakdown sales tax collected (if applicable)
  5. Include Payment Methods sheet for income categorization
  6. Provide to accountant with bank statements

Accountant Needs:

  • All transaction details
  • Breakdown by payment method
  • Refund and credit documentation
  • Month-end balances

Use Case 5: Payment Method Analysis

Goal: Understand customer payment preferences

Steps:

  1. Generate export for desired month
  2. Open Payment Methods sheet
  3. Review breakdown:
    • % of revenue by payment type
    • Number of transactions per method
    • Average transaction value by method
  4. Compare to previous months
  5. Identify trends:
    • Shift to/from credit cards
    • ACH adoption rate
    • Cash/check decline
  6. Make strategic decisions:
    • Consider discouraging high-fee methods
    • Promote lower-cost options
    • Update payment options offered

Report Data & Columns

All Transactions Sheet Columns

Column Description Example
Transaction ID Unique transaction number TXN-789456
Date Transaction date 2026-02-15
Type Transaction type Sale, Payment, Refund, Credit
Customer ID Customer account number 12345
Customer Name Full customer name John Smith
Order Number Related order (if applicable) ORD-98765
Amount Transaction amount $75.50
Payment Method How paid Credit Card, ACH, Cash
Status Transaction status Completed, Pending, Failed
Notes Additional details Refund for damaged item
Processed By Admin who processed [email protected]

Daily Totals Sheet Columns

Column Description Calculation
Date Calendar date 2026-02-01
Orders Number of orders Count of orders
Gross Revenue Total sales before refunds Sum of sale transactions
Payments Cash collected Sum of payment transactions
Refunds Money returned Sum of refund transactions
Net Revenue Gross minus refunds Gross - Refunds
Cumulative Running monthly total Sum of net revenue to date

Export Options & Customization

Standard Export

Includes:

  • All transaction sheets
  • Pre-built summary
  • Daily totals
  • Payment method breakdown
  • Charts

Best For:

  • Regular monthly bookkeeping
  • General financial analysis
  • Board reporting
  • Most common use case

Detailed Export

Additional Data:

  • Order line items (products purchased)
  • Discount codes applied
  • Shipping fees breakdown
  • Tax details
  • Customer account balance changes

Best For:

  • Deep financial analysis
  • Product profitability analysis
  • Audit preparation
  • Detailed reconciliation

Summary Only Export

Includes:

  • Transaction Summary sheet only
  • Monthly totals
  • Key metrics
  • No individual transactions

Best For:

  • Quick monthly overview
  • High-level reporting
  • Executive summaries
  • Fast processing

Custom Date Range

Flexibility:

  • Select specific start/end dates
  • Not limited to calendar month
  • Useful for fiscal periods
  • Compare equal-length periods

Best For:

  • Fiscal month reporting
  • Custom period analysis
  • Week-by-week comparison
  • Seasonal analysis

Filters & Analysis Tools

Built-in Excel Features

Filters: All transaction sheets include filter buttons on headers:

  • Filter by transaction type
  • Filter by payment method
  • Filter by customer
  • Filter by date range
  • Filter by amount (greater than, less than)

Sorting:

  • Sort by date (chronological)
  • Sort by amount (highest to lowest)
  • Sort by customer (alphabetical)
  • Sort by payment method

Pivot Tables: Create custom analysis:

  1. Select All Transactions data
  2. Insert > Pivot Table
  3. Drag fields to analyze:
    • Rows: Payment Method
    • Values: Sum of Amount
    • Filters: Transaction Type
  4. Visualize payment method revenue

Conditional Formatting: Highlight important data:

  • Large transactions (> $500) in green
  • Refunds in red
  • Pending transactions in yellow
  • Failed transactions in orange

Troubleshooting

Export File Won't Open

Symptoms:

  • "File is corrupted" error
  • Excel can't read file
  • Download incomplete

Solutions:

  1. Re-download the export
  2. Ensure download completed fully (check file size: should be 200KB+)
  3. Try opening in Google Sheets instead
  4. Check Excel version compatibility (requires Excel 2010+)
  5. Disable antivirus temporarily during download
  6. Use "Open and Repair" in Excel (File > Open > Browse > select file > dropdown arrow > Open and Repair)

Transactions Missing from Export

Symptoms:

  • Export shows fewer transactions than expected
  • Known transactions not appearing
  • Revenue totals don't match dashboard

Check:

  1. Verify correct month selected
  2. Check if transactions occurred in different month than expected
  3. Confirm transaction status (failed transactions may be excluded)
  4. Check if export type excludes certain transaction types
  5. Verify transactions weren't cancelled/deleted

Common Causes:

  • Transaction occurred on month boundary (last day/first day)
  • Transaction date vs. processing date confusion
  • Cancelled transactions excluded
  • Wrong month selected

Totals Don't Match Bank Deposits

Symptoms:

  • Export revenue doesn't match bank statement
  • Payments collected differ from deposits

Common Reasons:

  1. Timing Differences:
    • Payments processed after month-end
    • Bank deposits delayed to next month
    • Weekend/holiday payment delays
  2. Payment Method Timing:
    • Credit cards: 2-3 day delay
    • ACH transfers: 3-5 day delay
    • Checks: Deposited when received
  3. Fees Deducted:
    • Payment processor fees
    • Stripe/PayPal fees deducted
    • Export shows gross, bank shows net

Reconciliation Steps:

  1. Note export total revenue for month
  2. Review Payment Methods sheet for timing
  3. Check for early-month deposits from previous month
  4. Check for late-month payments deposited next month
  5. Account for payment processing fees
  6. Create reconciliation spreadsheet if needed

Charts Not Displaying

Symptoms:

  • Charts & Visualizations sheet is blank
  • Charts show errors

Solutions:

  1. Update Excel to latest version
  2. Open in full Excel (not Excel Online)
  3. Check if data sheet was modified (charts lose source data)
  4. Regenerate export with fresh download
  5. Manually recreate charts from data sheets
  • Weekly CSV Export (weekly-csv-export.php) - Weekly transaction data in CSV format
  • Billing Revenue Reports (billing-revenue-reports.php) - Revenue analytics and trends
  • Financial Reports Dashboard - Real-time financial metrics
  • Accounting Integration Settings - Configure QuickBooks/Xero integration

Typical Workflow:

  1. Generate weekly CSVs throughout month for ongoing monitoring
  2. Generate monthly Excel export on first of new month
  3. Reconcile monthly export with bank statements
  4. Import into accounting software
  5. Archive export for records (7 years for tax purposes)

Permissions & Access

Required Access Level: Administrator or higher

Access Level Capabilities:

  • Customer Service: No access (view only access to customer transactions)
  • Manager: No access (use weekly exports instead)
  • Administrator: Full access to all export types and history
  • Kiva Admin: All features + transaction debugging tools

Why Administrator Only:

  • Contains complete financial data
  • Includes sensitive customer payment information
  • Required for fiduciary responsibility
  • Used for tax and legal compliance

Best Practices

Regular Export Schedule

  1. Monthly Routine: Generate export on 1st business day of new month
  2. Immediate Reconciliation: Reconcile within 3 business days
  3. Consistent Storage: Save to: "Financials/YYYY/MM_MonthName_Transactions.xlsx"
  4. Backup Exports: Store in secure cloud location (encrypted)
  5. Archive Retention: Keep for 7+ years per tax requirements

Data Verification

  1. Summary Check: Verify Summary sheet totals match dashboard
  2. Daily Review: Scan Daily Totals for unusual patterns
  3. Refund Audit: Review all refunds for legitimacy
  4. Payment Methods: Verify breakdown matches payment gateway reports
  5. Outstanding Balance: Confirm matches customer balance report

Accounting Integration

  1. Consistent Mapping: Use same column mapping each month
  2. Chart of Accounts: Map transaction types to GL accounts
  3. Customer Matching: Ensure customer names match accounting system
  4. Reconciliation Report: Generate after import to verify accuracy
  5. Month-End Close: Don't close books until export reconciled

Security & Compliance

  1. Access Control: Limit download access to finance team only
  2. Encrypted Storage: Store exports in password-protected or encrypted location
  3. Secure Transmission: Use secure file transfer if sharing with accountant
  4. Audit Trail: Document who downloads and uses exports
  5. Deletion Schedule: Securely delete after archival period expires

Integration Tips

QuickBooks Desktop

  1. Generate Standard Export
  2. Open QuickBooks
  3. File > Utilities > Import > Excel Files
  4. Select downloaded .xlsx file
  5. Choose "Add/Edit Multiple List Entries"
  6. Map columns:
    • Date → Transaction Date
    • Customer Name → Customer
    • Amount → Amount
    • Payment Method → Payment Method
    • Notes → Memo
  7. Review import summary
  8. Import transactions
  9. Reconcile against export Summary sheet

QuickBooks Online

  1. Generate export
  2. Save All Transactions sheet as CSV
  3. In QBO: Settings > Import Data
  4. Select "Bank Transactions"
  5. Upload CSV
  6. Map columns as above
  7. Review and accept transactions

Xero

  1. Export Detailed version
  2. Navigate to Accounting > Bank Accounts
  3. Import Statement
  4. Upload All Transactions sheet (CSV format)
  5. Map fields to Xero structure
  6. Reconcile imported transactions

Google Sheets Analysis

  1. Open Google Sheets
  2. File > Import
  3. Upload monthly export .xlsx
  4. Select "Replace spreadsheet"
  5. Use Sheets' built-in analysis tools
  6. Share with team (with appropriate permissions)

Quick Reference Card

Task Action/Location
Export last month's transactions Select previous month > Click "Generate Export"
Get data for bookkeeping Generate Standard Export > Open in Excel
Monthly board report Generate export > Use Charts sheet
QuickBooks import Standard Export > Save as CSV > Import to QB
Reconcile revenue Compare Summary sheet total to bank deposits
Find specific transaction Open All Transactions > Use filter
Review refunds issued Open Refunds & Credits sheet
Payment method breakdown Open Payment Methods sheet

FAQs

When should I generate the monthly export?

Generate on the 1st business day of the new month. This ensures all late transactions, payment processing, and month-end adjustments are included.

Can I regenerate an export if I made a mistake?

Yes, you can generate the same month multiple times. Each generation reflects the current state of transactions for that month, so recent corrections will be included.

Why do totals change if I regenerate the same month?

If transactions were added, modified, or deleted after your first export, the new export will reflect those changes. Always use the most recent export for accuracy.

What's the difference between transaction date and processing date?

Transaction date is when the order was placed or action occurred. Processing date is when the payment actually cleared. Exports use transaction date by default.

Can I export multiple months at once?

Not in a single file, but you can use Custom Date Range to span multiple months, or generate separate exports for each month and combine them in Excel.

How long are exports stored?

Monthly transaction exports are stored for 90 days, then automatically deleted. Download and archive exports you need to keep longer.

Can I customize which columns are included?

The standard export includes all essential columns. For custom column selection, use the Detailed Export option or contact support for custom export templates.

Why doesn't my export include sales tax?

Sales tax handling depends on your account configuration. Use the Detailed Export option to see tax breakdowns if enabled for your account.

How do I handle mid-month refunds for previous month orders?

Refunds appear in the month they were processed. Keep this in mind during reconciliation - you may need to adjust previous month's net revenue.

Can I schedule automatic monthly exports?

Not currently available. Set a calendar reminder for the 1st of each month to manually generate the export.

Change Log

2026-03-01

  • Initial documentation created
  • Documented all Excel sheets and columns
  • Added comprehensive use cases
  • Created accounting integration guides
  • Added troubleshooting for common issues

End of Documentation

For additional help, contact your system administrator or Kiva Logic support.