🎁 Get the FREE AI Skills Starter Guide β€” Subscribe β†’
BytesAgainBytesAgain
BytesAgain β€Ί Skills β€Ί AR Collections
πŸ¦€ ClawHub

AR Collections

by @samledger67-dotcom

AR Collections & Aging Analysis pipeline for QBO clients. Produces a 7-tab Excel workbook with AR aging buckets (Current/1-30/31-60/61-90/90+), DSO, collecti...

Versionv1.0.2
Downloads839
#devops#data#legal#real-estate#automation
TERMINAL
clawhub install ar-collections

πŸ“– About This Skill

name: ar-collections description: > AR Collections & Aging Analysis pipeline for QBO clients. Produces a 7-tab Excel workbook with AR aging buckets (Current/1-30/31-60/61-90/90+), DSO, collection priority scoring, bad debt reserve, payment patterns, and CDC log. SOP-gated: auto-skips clients with no AR (e.g. POS-based businesses). Use for AR aging reports, collections analysis, DSO tracking, and bad debt reserve calculations. NOT for bank reconciliation, payroll, or tax preparation. version: 1.0.0 tags: - finance - accounting - accounts-receivable - collections - DSO - aging summary: - AR Collections & Aging Analysis pipeline for QBO clients - SOP-gated: auto-skips if client has no AR (e.g. SB Paulson/Willo β€” POS collection) - 7-tab Excel output: AR Summary | Aging Detail | Collection Priority | Payment Patterns | Bad Debt Reserve | DSO Analysis | CDC Log - All Decimal math; CDC tracks aging movement between runs - Trigger phrases: AR aging, collections report, accounts receivable, DSO, bad debt reserve updated: 2026-03-18

AR Collections & Aging Analysis Skill

What This Does

Runs the AR Collections & Aging Analysis pipeline (scripts/pipelines/ar-collections.py) to produce a controller-level accounts receivable workbook from QBO data.

Produces: 1. AR aging bucketed into Current | 1-30 | 31-60 | 61-90 | 90+ days 2. Aging metrics: total AR, weighted average days outstanding, concentration risk 3. Collection priority scoring per customer (HIGH / MEDIUM / LOW / MONITOR) 4. Recommended collection actions per customer 5. Bad debt reserve (percentage-of-aging method) 6. Payment pattern analysis from GL history 7. DSO: current period and rolling 3-month 8. CDC: which customers improved or deteriorated since last run 9. Excel workbook (7 tabs)

When to Use

Use when:

  • Client asks for AR aging report, collections status, or DSO
  • Monthly close includes AR review
  • Need to know who owes money and what to do about it
  • Bad debt reserve needs to be calculated for month-end
  • Auditors or investors request AR aging schedule

    • Do NOT use when:

  • Client SOP says AR is not applicable (pipeline exits gracefully β€” no report needed)
  • Client collects at point of sale (e.g., SB Paulson / Willo Salons)
  • Running for a non-QBO client (no data source)
  • User wants a P&L or balance sheet (use pl-deep-analysis or client-dashboard)

    • SOP Gate

    The pipeline automatically checks the client SOP before pulling any data:

  • sb-paulson β†’ exits gracefully with explanation (POS collection, no AR)
  • Unknown slugs β†’ checks SOP markdown for AR-disabled signals, defaults to AR-applicable

    • To add a new client's AR status, update CLIENT_AR_CONFIG in the pipeline, OR add these markers to their clients/{slug}/sop.md: 

    AR Aging: ❌ Not applicable (POS collection)

    Usage

    # Standard run β€” as of end of month
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31
    With custom output directory
    
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31 --out ~/Desktop/reports
    Skip GL pull (faster, no payment pattern analysis)
    
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31 --skip-gl
    QBO sandbox
    
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31 --sandbox
    Client with no AR β€” exits gracefully
    
python3 scripts/pipelines/ar-collections.py --slug sb-paulson --as-of 2026-03-31

    Output

    Default location: reports/ar-collections/ar-collections_{slug}_{as-of}.xlsx

    Tabs:

    | Tab | Contents | |-----|----------| | AR Summary | Aging snapshot by bucket, key metrics, concentration risk | | Aging Detail | Invoice-level list: customer, date, due date, balance, bucket | | Collection Priority | Sorted action list: HIGH/MEDIUM/LOW/MONITOR with recommended actions | | Payment Patterns | Avg days to pay per customer, vs. terms, reliability rating | | Bad Debt Reserve | Percentage-of-aging reserve calc + suggested journal entry | | DSO Analysis | Current and rolling 3-month DSO, monthly revenue detail | | CDC Log | Changes since last run: improved / deteriorated / new / cleared |

    Collection Priority Logic

    | Priority | Criteria | Recommended Action | |----------|----------|--------------------| | HIGH | 90+ days past due OR balance > $5K | Escalate / demand letter / write-off review | | MEDIUM | 61-90 days OR balance > $2.5K | Follow-up call | | LOW | 31-60 days | Send email reminder | | MONITOR | Current or 1-30 days | Standard review next cycle |

    Bad Debt Reserve Rates (Percentage-of-Aging)

    | Bucket | Rate | |--------|------| | Current | 1% | | 1-30 | 3% | | 31-60 | 10% | | 61-90 | 25% | | 90+ | 50% |

    DSO Formula

  • Current DSO = (AR Balance Γ· Current Period Revenue) Γ— Days in Period
  • Rolling DSO = AR Balance Γ· (3-Month Revenue Γ· 90 days)

    • CDC Cache

    Cached at: .cache/ar-collections/{slug}.json

    Each run saves customer balances and worst buckets. Next run computes:

  • Improved β€” balance decreased or bucket moved earlier
  • Deteriorated β€” balance increased or bucket moved later
  • New β€” first appearance in AR
  • Cleared β€” balance went to zero (collected)

    • Requirements

    pip install openpyxl

    Node.js QBO client must be auth'd
    
node bin/qbo info {slug}  # from your QBO integration directory

    Related Pipelines

  • pl-deep-analysis.py β€” GL drill-down, P&L variance, accrual proposals
  • client-dashboard.py β€” KPI dashboard (includes DSO as a KPI)
  • bank-reconciliation.py β€” Bank rec (not AR-specific)
  • budget-vs-actual.py β€” BvA (revenue-side context for AR)

    • Clients

    Configure AR applicability per client in CLIENT_AR_CONFIG or via clients/{slug}/sop.md.

    ⚑ When to Use

    TriggerAction
    - Client asks for AR aging report, collections status, or DSO
    - Monthly close includes AR review
    - Need to know who owes money and what to do about it
    - Bad debt reserve needs to be calculated for month-end
    - Auditors or investors request AR aging schedule
    **Do NOT use when:**
    - Client SOP says AR is not applicable (pipeline exits gracefully β€” no report needed)
    - Client collects at point of sale (e.g., SB Paulson / Willo Salons)
    - Running for a non-QBO client (no data source)
    - User wants a P&L or balance sheet (use pl-deep-analysis or client-dashboard)

    πŸ’‘ Examples

    # Standard run β€” as of end of month
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31
    With custom output directory
    
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31 --out ~/Desktop/reports
    Skip GL pull (faster, no payment pattern analysis)
    
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31 --skip-gl
    QBO sandbox
    
python3 scripts/pipelines/ar-collections.py --slug  --as-of 2026-03-31 --sandbox
    Client with no AR β€” exits gracefully
    
python3 scripts/pipelines/ar-collections.py --slug sb-paulson --as-of 2026-03-31