What This Is
This guide walks you through building a custom household financial tracking system using Claude AI and Excel. The system takes raw transaction exports from your bank and credit card accounts, categorizes every transaction automatically, and produces a clean monthly workbook with budget tracking, month-over-month trends, and real cash flow visibility - without manually touching a spreadsheet.
It was designed around real household use with multiple accounts and two income sources, and refined over several months into a repeatable, low-effort monthly workflow. Once set up, the ongoing time investment is minimal - most months take under 15 minutes.
More importantly, this guide will help you build an ongoing relationship with your AI model - one that learns your spending patterns, knows your categories, and gets smarter with every month of data you provide.
What You Need Before Starting
- A Claude.ai account (Pro plan recommended for longer conversations and file uploads)
- CSV transaction exports from each of your bank and credit card accounts
- A rough list of monthly budget targets by category - approximations are fine to start
- A list of recurring bills (mortgage, subscriptions, insurance, etc.) - or let Claude find them for you using Prompt 1b
- 15-30 minutes for the initial setup - future months take significantly less
Include Every Account - Including Your Savings
For the most complete picture, include your high-yield savings account in your CSV exports. Tax refunds, insurance reimbursements, and payroll savings splits often land there and will be missed if that account is excluded. Claude will know to handle internal transfers correctly so they are never double-counted as income or spending.
The Prompts
Prompt 1b - Discover Your Recurring Bills First
Before building your workbook, run this prompt to let Claude identify your recurring bills automatically from your transaction history. This eliminates the guesswork of trying to recall every subscription and fixed expense from memory.
Claude will surface what it finds, present a draft list grouped by category, and ask for your confirmation before anything is finalized. You stay in control without doing the tedious legwork.
Before we build my financial workbook, I want you to analyze my transaction
history and identify my recurring bills automatically. I have attached CSV
exports from my accounts: [LIST YOUR ACCOUNTS].
Scan all transactions and identify charges that appear to recur on a regular
pattern - monthly, quarterly, semi-annually, or annually. For each one,
provide:
- Vendor name
- Estimated amount (or range if it varies)
- Billing frequency (monthly, quarterly, semi-annual, annual)
- Category (e.g. Utilities, Streaming, Insurance, Housing)
- Confidence level: High (seen 2+ times), Medium (seen once but clearly
recurring), or Low (irregular - may or may not recur)
Present findings as a draft Recurring Bills table grouped by category.
Do NOT finalize yet - show me the draft and ask me to confirm, correct
amounts, or add anything before we proceed.
Things to watch for:
- HOA and pest control may be quarterly, not monthly
- Insurance premiums are often semi-annual - flag clearly
- Utility charges vary month to month - show the range
- Ignore one-time large purchases even if the amount looks recurring
- If the same service appears across multiple accounts, flag as a
potential duplicate
Once I confirm the final list, format it cleanly for use as the Recurring
Bills sheet in my financial workbook.
Prompt 1 - Initial Setup and First Workbook
Use this prompt to build your workbook from scratch. Paste it into Claude with all your CSV exports attached. If you ran Prompt 1b first, copy your confirmed recurring bills list into the placeholder at the bottom.
I want to build a household financial tracking workbook in Excel. I am
attaching CSV transaction exports from my bank and credit card accounts.
Please do the following:
1. Categorize every transaction using these household spending categories:
Groceries, Dining and Restaurants, Auto and Transport, Shopping,
Home and Garden, Fitness and Wellness, Pharmacy and Medical, Personal Care,
Entertainment, Travel, Education and Kids, Childcare, Utilities,
Insurance, Recurring Bills, Housing, Fees and Charges, Income, and Other.
Add or adjust categories to fit my actual spending patterns.
2. Create a Clean Vendor column that standardizes merchant name variants
so the same store always maps to one consistent name. For example,
'WM SUPERCENTER #4574' and 'WALMART.COM' should both show as 'Walmart'.
3. Build an Excel workbook with these six sheets:
- All Transactions: full ledger with date, bank description, clean
vendor, amount, category, account, and month
- Category Summary: monthly spending totals by category, filterable
by month, with net cash flow (income minus spending)
- Monthly Comparison: side-by-side spending by category across months
- Budget Template: my targets vs. actual spend with variance, with a
month selector dropdown to avoid formatting errors
- Recurring Bills: fixed and semi-fixed obligations with amounts
and billing frequency
- Categories Reference: master list of category names and rules
4. Below the Net Cash Flow line on the Budget Template, add a Savings
Tracker section with three rows:
- Savings Deposits: total of regular automatic transfers to savings
- Savings Withdrawals: total pulled back from savings that month
- Net Savings: the difference - did I actually grow my balance?
5. Track income separately. My income sources are:
[LIST YOUR EMPLOYERS OR INCOME SOURCES]
Also track any alternate income (tax refunds, reimbursements, insurance
payouts) as a separate income line, not as spending.
6. Treat refunds and insurance payouts as offsets to the category they
came from, not as income. For example, a travel insurance reimbursement
should reduce Travel spending, not add to Income.
7. Exclude inter-account transfers from all spending totals.
My accounts are:
[LIST ALL ACCOUNTS - e.g. Chase Sapphire, checking, savings, HYSA]
My approximate monthly budget targets by category are:
[LIST YOUR TARGETS - e.g. Groceries: $600, Dining: $300, etc.]
My recurring bills include:
[PASTE OUTPUT FROM PROMPT 1b, OR LIST MANUALLY]
Build the workbook from scratch and deliver it as a downloadable .xlsx file.
Name it: Household Financial Workbook_[Month] [Year].xlsx
Prompt 2 - Monthly Rebuild
At the end of each month, export CSVs from all your accounts and run this prompt. Always rebuild from scratch - never append to the prior file. This prevents duplicate transactions and ensures every categorization correction is applied to the full history.
Please rebuild my household financial workbook for [MONTH YEAR]. I have attached CSV exports from all my accounts: [LIST ACCOUNTS]. Use the same six-sheet structure, category rules, clean vendor mappings, Savings Tracker section, and budget targets from the previous workbook. Rebuild completely from scratch - do not append to the prior file. Notes for this month: [NOTE ANY ONE-TIME EVENTS: e.g. 'We traveled this month - flag the Travel total separately so it does not distort our normal baseline'] [NOTE IRREGULAR BILLS DUE THIS MONTH: e.g. 'HOA is due this month (quarterly), auto insurance is semi-annual - both are expected'] [NOTE ANY REFUNDS OR REIMBURSEMENTS: e.g. 'The $X deposit from [company] is a travel insurance refund - offset against Travel'] [NOTE ANY NEW VENDORS: e.g. 'Summer camp charges from [school] should go in Education and Kids'] After building, give me a brief summary covering: 1. Total spending vs. budget and net cash flow 2. Categories running significantly over or under 3. Savings Tracker result - did we grow our savings balance? 4. Anything unusual worth flagging Deliver the completed workbook as a downloadable .xlsx file.
Prompt 3 - Mid-Month Check-In
You do not need to wait until month-end to get useful insights. Export partial transactions mid-month for a pulse check. Claude will compare your spending pace against pro-rated budget targets and flag anything that needs attention while there is still time to course-correct.
I want to do a mid-month check-in. I have attached MTD transaction exports from my accounts through [TODAY'S DATE]. Using our standard category rules, compare my spending so far against pro-rated versions of my monthly budget targets based on the number of days elapsed in the month. Give me a conversational summary - no new workbook - covering: 1. Where I stand vs. pro-rated budget by category 2. Any categories already over or on pace to exceed the monthly target 3. Any unusual transactions worth flagging 4. A projected month-end landing point at current pace My monthly budget targets are: [PASTE YOUR CATEGORY TARGETS]
Prompt 4 - Financial Scenario Modeling
Once your workbook has a few months of real data, Claude can use your actual income, fixed costs, and monthly surplus to model major financial decisions. This transforms the system from a record-keeper into a planning tool.
Based on our financial workbook and what you know about my income, spending, and monthly surplus, I want to model a scenario: [DESCRIBE YOUR SCENARIO. Examples below:] HOUSING: 'We are considering buying a home at approximately $X. Our current mortgage balance is $Y with a monthly payment of $Z. Model the impact on monthly cash flow across a few down payment scenarios and show me what changes to qualify comfortably.' CAREER CHANGE: 'I am considering a job that pays $X less per year. Based on our current necessity spending, could my household income alone cover our fixed obligations? What would need to change?' DEBT PAYOFF: 'I have a [loan type] at X% with $Y remaining. Should I pay it off early or invest that surplus given my current mortgage rate? Show me the math both ways.' SAVINGS GOAL: 'We want to save $X for [goal] by [date]. Based on our current monthly surplus and savings habits, are we on track? What would we need to adjust to get there?' Walk me through this conversationally and show the math clearly. Flag any assumptions you are making so I can correct them.
Tips for Getting the Best Results
Build the Relationship - Correct Claude, Not Excel
If a vendor is miscategorized, tell Claude rather than editing the workbook directly. Every monthly rebuild starts from scratch using the categorization engine - any manual edit you make in Excel will be overwritten. Say: "Move all [Vendor] charges to [Category] going forward." Claude updates the logic permanently, and the correction applies to all future rebuilds automatically.
Your True Spendable Income Is Lower Than You Think
If your employer splits your direct deposit between checking and a savings account, or if you make regular automatic transfers to savings, those amounts are real income - but they never touch your spending accounts. This can make your cash flow look tighter than expected even when income appears healthy. Tell Claude about any savings splits or automatic transfers so it can calculate your true spendable baseline accurately.
Your Retirement Contributions Are Invisible - In a Good Way
401k, HSA, and pension contributions come out before your paycheck hits the bank, so they never appear in your transaction data. Your budget will look tighter than your actual savings rate. Mention your pre-tax contribution amounts to Claude when doing savings rate analysis or scenario modeling so you get an honest picture of how much you are actually setting aside.
Flag Irregular Expenses Before They Confuse the Data
Quarterly HOA fees, semi-annual insurance premiums, and annual memberships will spike in the months they hit and look like budget overruns if Claude does not know the pattern. Flag these at setup time and note them each month they appear. This keeps your month-over-month comparisons clean and your trend analysis meaningful.
Handle Refunds as Spending Offsets, Not Income
Large refunds - travel insurance payouts, retailer returns, warranty reimbursements - should reduce the category they came from, not add to your income total. Tell Claude: "The $X deposit from [company] is a [Travel / Shopping / etc.] refund - offset it against that category." This keeps your spending totals accurate and avoids inflating income.
Name One-Time Events in Your Monthly Notes
A vacation, home repair, or large purchase will distort category totals and make normal months look alarming by comparison. Note these when you rebuild so Claude can flag them separately, keep your baseline clean, and make sure they do not skew your spending projections going forward.
Use the Mid-Month Check-In Regularly
The monthly rebuild tells you what happened. The mid-month check-in gives you time to do something about it. Even a quick review on the 15th catches overspending early enough to course-correct. Over time, this habit is what separates households that track finances from households that actually improve them.
Disambiguate Similar Vendor Names Upfront
Banks sometimes record completely different businesses with nearly identical names. A hardware store and a grocery chain might share a name fragment. A gas station charge might actually be an ATM withdrawal. When you spot these, tell Claude the distinction so the categorization engine learns the difference permanently. The more context you provide early, the less cleanup you do later.
How the System Grows Over Time
The real power of this system compounds with each passing month. Here is what that progression looks like:
Month 1
Foundation is built. All accounts connected, categories established, vendors mapped, budget targets set. Your first real snapshot of where your money goes.
Months 2-3
Patterns begin to emerge. You can compare months, spot categories trending the wrong direction, and start separating normal spending from seasonal spikes.
Months 4-6
You have a meaningful baseline. Budget targets can be refined from real data instead of guesses. Scenario modeling becomes grounded in actuals rather than estimates.
Year 1 and Beyond
Full seasonal visibility. You know exactly which months run heavy and can plan ahead. Major decisions - housing, career, investment - are modeled from real numbers, not assumptions.
The categorization engine gets more accurate with every correction you make. The budget targets become grounded in reality rather than optimism. And the relationship you build with your AI model means it already knows your patterns, your accounts, and your goals when you show up each month.
Built Through Real Use. Refined Over Time.
This system was not designed in a vacuum. It was built month by month through actual household use - real spending data, real corrections, real decisions. Start simple, be consistent, and let the data accumulate. The clarity compounds.