top of page

Build a personal finance tracker with Claude and SQLite

Writer: Alex Teper
Alex Teper
4 days ago
3 min read

Casey paid $300 toward a credit card on October 24. The payment shows up twice. Casey’s checking account shows $300 going out, and the card statement shows $300 coming in. How much did that payment add to Casey’s spending that week?

Zero. The meals and home supplies Casey bought on the card were already in the spending total. Counting the bill payment as another purchase would turn a $356 week into a $656 week.

That’s the first thing I’d want to check in a tracker I built myself. When a number looks high, can I see exactly which purchases went into it?

Start with Casey’s bank and card files

Download the sample bank and card files. Casey and the transactions in the download are fictional.

Unzip the project and open a terminal in the folder containing review.py. With Python 3.10 or later, run:

python3 review.py --db household.sqlite

This creates a file called household.sqlite on your computer. It holds Casey’s 21 transaction records, including the purchases, the refund and both sides of the card payment.

Ask Claude about the $300 payment

Open a new Claude chat and attach review.py. For this step, that’s the only file Claude needs. You’re asking it to write a query you can run against the records on your computer.

Using the database layout in review.py, write a SQLite query that shows both sides of the October 24 credit card payment. Show the account, the merchant description and how much each row contributes to spending. Then write a second query for total spending from October 19 through October 25, 2026. Explain how the grocery refund affects that total.

To check the answer, open the database from the project folder. If you have the SQLite command line tool installed, type sqlite3 household.sqlite. Then paste this query:

SELECT account, merchant,
       spending_minor AS stored_cents,
       CASE WHEN kind IN ('expense', 'refund')
            THEN spending_minor ELSE 0
       END AS counted_cents
FROM transactions
WHERE date = '2026-10-24'
  AND kind = 'card_payment';

You should see “Credit card payment” in checking and “Payment received” on the card. The stored amounts are 30,000 and −30,000 cents. Both rows show zero in the counted column because neither is a new purchase.

Now check the whole week. Count purchases and refunds, and total each currency separately:

SELECT currency,
       SUM(spending_minor) / 100.0
         AS weekly_spending
FROM transactions
WHERE date BETWEEN '2026-10-19'
               AND '2026-10-25'
  AND kind IN ('expense', 'refund')
GROUP BY currency;

The answer should be $356. If you get $386, look at groceries. Casey paid $150 at the grocery market on October 19 and got $30 back the next day. Groceries contributed $120 to the week.

Import the same files a second time

It’s easy to forget where your last bank export ended. If you download two weeks of transactions and one week overlaps, does your tracker count that week twice?

Type .quit to leave SQLite. Run the original Python command again, reopen the database and enter:

SELECT COUNT(*) FROM transactions;

You should still see 21. The tracker recognizes each imported row by its source, account and transaction ID. It also keeps category corrections separately, so correcting a purchase doesn’t require rewriting the original record.

If a bank changes its file layout or sends a different amount under an existing ID, you’ll still need to check what happened. That’s part of keeping a tracker useful after the first import.

What would Casey actually want to know?

Once I trust the $356, I’d ask a different question. If Casey skipped the $88 dinner, would it still have been a more expensive week?

Yes. Casey would have spent $268, still $20 more than the previous week’s $248. Dining tells a different story. Without that meal, it would have come to $56, or $6 less than the week before. Before changing the dining budget, I’d want to know whether Casey expects another dinner like that next week.

If you enjoy building the tracker, keep the database and try another week. Next, use those records to check Casey’s food budget and savings goal. If you came here for the answer about dinner, Cash Coach AI already prepares your Weekly Wrap from your spending records. Open your Wrap and ask, “Which purchase made the biggest difference this week?” See how the Weekly Wrap works.

 
 
 

Comments


bottom of page