recurring-amounts-csv.md

How to Read Recurring Amounts CSV Output

Overview

The detect_recurring_amounts tool analyzes bills to identify which amounts recur and how often they recur. The CSV output contains two orthogonal dimensions of analysis:

1. Amount Consistency - How consistent are the dollar amounts?
2. Timing Cadence - How regular are the dates/timing between bills?

Key Columns Explained

Pattern-Level Columns (Repeated for each bill in the pattern)

amount_consistency - Amount Pattern

Measures how consistent the dollar amounts are across recurring bills.

Values:

• fixed: Amounts are essentially identical (variance < 0.01)
  • Example: All bills are exactly $5,560.00
• variable: Amounts vary slightly (variance 0.01 - 0.10)
  • Example: Bills are $5,500, $5,600, $5,550 (small variations)
• trending: Amounts vary significantly or show a trend (variance ≥ 0.10)
  • Example: Bills are $5,000, $6,000, $7,000 (increasing trend)

inferred_cadence - Timing Pattern

Measures how regular the dates/timing are between bills.

Values:

• monthly: Bills come roughly every 28-35 days
• bi-weekly: Bills come roughly every 14-21 days
• weekly: Bills come roughly every 6-10 days
• quarterly: Bills come roughly every 85-95 days
• irregular: Timing doesn't match any standard pattern

Other Pattern Columns

• occurrence_count: How many times did this amount recur?
• amount_variance: Coefficient of variation (stddev/mean) - lower = more consistent amounts
• avg_days_between: Average days between occurrences
• days_variance: Standard deviation of days between bills - lower = more regular timing
• amount_min/max/avg/median: Statistics about the recurring amounts
• first_seen / last_seen: Date range when this pattern was observed

Bill-Level Columns (One row per bill)

• bill_id: Database ID of the bill
• bill_number: Vendor's bill number
• bill_date: When was this bill issued?
• due_date: When is payment due?
• amount: How much was this specific bill?
• currency: Currency code (e.g., USD)
• payment_status: paid, open, payment_not_found
• paid_date: When was this bill paid (if applicable)
• days_since_prev: Days since the previous bill in this pattern

Example Interpretation

Row from CSV:

amount_consistency = "fixed"
inferred_cadence = "irregular"
occurrence_count = 22
amount_variance = 0.0
avg_days_between = 7.67
days_variance = 3.45

Interpretation:

• ✅ Amounts are fixed: All bills are exactly the same amount ($5,560)
• ❌ Timing is irregular: Bills come every ~7.67 days on average, but variance is 3.45 (doesn't match weekly/monthly patterns)

This is a recurring amount but with irregular timing.

How to Use This in Excel

Finding Ideal Accrual Candidates

Filter for patterns that have both:

• amount_consistency = "fixed" AND
• inferred_cadence = "monthly" (or bi-weekly, weekly)

These are bills with fixed amounts and regular timing - perfect for accrual accounting.

Other Useful Filters

• amount_consistency = "fixed": Find recurring amounts that are consistent
• inferred_cadence = "monthly": Find bills that come monthly
• occurrence_count >= 4: Find patterns with sufficient history
• days_variance < 3: Find patterns with very regular timing

Understanding the Two Dimensions

These are orthogonal (independent) dimensions:

Amount Consistency	Timing Cadence	Use Case
fixed	monthly	✅ Ideal for accrual - Fixed amount, regular timing
fixed	irregular	⚠️ Recurring amount but unpredictable timing
variable	monthly	⚠️ Regular timing but amounts vary
variable	irregular	❌ Hard to predict - not good for accrual

CSV Structure

• One row per bill with pattern context repeated
• Pattern-level columns are the same for all bills in the same pattern
• Bill-level columns vary per bill
• Use Excel's Remove Duplicates on pattern columns to see unique patterns
• Use Pivot Tables to aggregate by vendor/account/consistency/cadence