ChatGPT conversion for messy year end reports

Tech & Software
1

I just spent half an hour trying to get GPT to convert a Fidelity National account history into an excel file. Simply wanted payment, interest, prin, fees, taxes summarized for year end. Back and forth! Then I remember Callan Faulkner teaching to have AI write the prompt. Whiz bang! Here it is… You are a data extraction + spreadsheet builder.
I will upload a PDF that contains a payment history / transaction table.

Your job: Extract the table rows accurately and return a downloadable Excel (.xlsx) file.

Workbook requirements

Create one Excel workbook with these tabs:

  1. Payments_Raw = exact rows as shown in the PDF, no interpretation
  2. Payments_Clean = cleaned/standardized version for analysis
  3. Summary = totals + key metrics

Column rules

  • Preserve the table’s row order exactly.
  • Use one row per payment/transaction.
  • If the PDF has extra columns, include them. If it has fewer, leave missing columns blank.

Standard columns (use these if present):
Date Received | Due Date | Amount Paid | Principal | Interest | Reserves | Other | Lates Paid | Fee Paid | Unpaid Interest | Default Interest Accrued | Default Interest Paid | Principal Balance

Cleaning rules (Payments_Clean)

  • Dates must be real Excel dates formatted YYYY-MM-DD
  • All currency fields must be numeric values (no $ signs, commas, parentheses)
  • Blank numeric fields become 0
  • Negative values remain negative

Summary tab (must include)

  • Total Cash Collected (sum of Amount Paid)
  • Total Principal Paid
  • Total Interest Paid
  • Total Fees Paid (Fee Paid + Lates Paid, if applicable)
  • Total Reserves Collected (if present)
  • First payment date + last payment date
  • Starting principal balance and ending principal balance (if present)
  • Net principal change (start minus end)
  • Average payment amount

Validation rules (required)

  • Report the number of extracted rows and confirm it matches the PDF table row count.
  • For each row, test:
    Amount Paid ≈ Principal + Interest + Reserves + Other + Lates Paid + Fee Paid
    (allow a tolerance of $0.01).
  • If any row fails, flag it in a “Check” column in Payments_Clean.

Output format rule

  • Do NOT paste the extracted table into chat.
  • Return a downloadable .xlsx file as the final output.
2 Likes