A while back I released my Account Balances Database for Microsoft Access. It wasn't intended to replace proper bookkeeping software or accounting software. I still download all of my transactions from my financial institutions every quarter and import them into a different database for budgeting, taxes, expense tracking, and all that fun stuff. This database serves a completely different purpose.

It actually started life as nothing more than a spreadsheet. I listed all of my credit cards, checking accounts, savings accounts, investment accounts, and loans in one place. A couple of times a week I'd log into each website, jot down the balances, see what transactions were pending, and make sure everything looked the way I expected. It was nothing fancy, but it worked well enough that I eventually expanded it into a full Microsoft Access database.

Today that database tracks recurring transactions, pending charges, payments that have cleared, expected deposits, and all kinds of other information. The goal isn't accounting. The goal is simply to spend a couple of minutes every few days making sure everything looks right. Did that membership I canceled actually stop billing me? Did my mortgage payment clear? Is there some mystery charge that shouldn't be there? Those are the kinds of questions I'm trying to answer. How much can I spend on penguin, Trek, and Rush merch this month? LOL

The obvious next step was automation. I already have VBA code that can launch a browser, navigate to a website, switch to the browser window, press Ctrl+A to select everything, press Ctrl+C to copy the page, and then read the clipboard back into Access. I've covered all of those techniques in previous videos, so getting the page data into Access wasn't the difficult part.

The difficult part was figuring out what all that text meant. Every financial institution formats its pages differently. Capital One is different from American Express. American Express is different from Discover. Discover is different from Fidelity. My local bank is different from all of them. Every page required its own custom parser. Find the word "Balance." Capture everything between this marker and that marker. Strip off the dollar sign. Convert it to Currency. Find the Pending section. Figure out where the transaction table starts. Repeat for every single website.

Then the real maintenance nightmare began. Every few months one of those institutions would redesign its website just enough to break my parser. Maybe they changed a heading. Maybe they inserted another column. Maybe they moved the transaction list somewhere else on the page. Whatever they changed, I was back in the VBA editor rewriting string parsing logic. Reading transaction data was even worse. Some sites put the date first. Others put it last. Some clearly identify pending transactions. Others don't. Every institution seemed to have its own idea of how financial data should be presented.

Then I had one of those moments where I realized I was solving the wrong problem. I've already integrated OpenAI into Microsoft Access. Why am I trying to teach VBA to understand dozens of different webpage layouts when I have a tool that's designed to interpret messy information?

So I changed the entire approach. Instead of trying to parse the page with hundreds of lines of VBA, I simply collect the page text and send it to the AI with one instruction: "Figure out what all this means and return it in my XML format." That's exactly what it does. It sends back my current balance, pending balance, available credit, payment due date, and a nicely formatted list of transactions containing the date, description, amount, transaction type, and anything else my database needs.

The beautiful part is that my VBA no longer has to understand Capital One, American Express, Discover, Fidelity, or any other financial institution. My VBA only understands my XML format. The AI handles the messy part by translating whatever webpage it receives into one consistent format that my database can process.

This is why I think this is one of the best practical uses of AI I've found so far. I'm not asking it to think for me. I'm not asking it to make financial decisions. I'm not asking it to be creative. I'm asking it to look at a big pile of unstructured information, understand what it's looking at, and normalize it into structured data. That's something large language models are remarkably good at.

I've made a couple of small prompt adjustments and a few account-specific overrides for oddball cases, but those changes are tiny compared to maintaining custom VBA parsers for every financial institution. A huge time savings. And talking about savings, I did the math. Even if I checked ten different financial accounts every business day for an entire month, the total OpenAI API token cost would still be under one dollar. I'll happily spend a dollar a month if it means I never have to chase another webpage redesign again.

I've been using the new system for a while now, and honestly, it's working beautifully. Even the smaller, faster AI models have had no trouble returning exactly the XML format that my database expects. My Access application now has a very easy job. It simply imports the XML, matches the transactions against the ones I'm already tracking, updates my balances, and lets me know if anything unexpected shows up.

This project has also given me another idea. Over the past year I've built quite a collection of AI-powered tools into my Access applications. I've integrated chatbots into my database and website, database-aware assistants that can answer questions about my databases and generate SQL and VBA, automatic image generation for the slide decks I use in my videos, and now this financial data extraction system. Every month I seem to discover another practical way to combine Microsoft Access with AI.

I'm seriously considering putting together a completely separate course dedicated to using Microsoft Access with AI. I don't want to mix it into the regular Developer classes because I know many of you have told me you have no interest in AI, and that's perfectly fine. I don't want people sitting through lessons they'll never use. Instead, this would be its own course that I could continue expanding as I discover new techniques and new ways to integrate AI into Access applications. This technology is changing far too quickly for a one-time seminar, and I expect I'll keep finding new uses for it.

If that sounds like something you'd be interested in, let me know in the comments below. If enough people are interested, I'll start putting together a dedicated Microsoft Access with AI course.

Before anyone panics, though, don't worry... I'm not shelving the SQL Server course. That's still my top priority, right behind my regular Developer lessons, TechHelp videos and Extended Cuts. I'm actively working on it, and I want to get a few more SQL Server classes finished before I tackle another major project.

In fact, I don't see SQL Server and AI as competing topics at all. I think they're complementary. Microsoft Access is an incredibly powerful front end. SQL Server gives you a robust, scalable back end. AI adds an entirely new layer of capabilities that simply wasn't practical a few years ago. Put those three together, and I think you've got one of the most powerful development toolsets available to the independent developer. Especially since AI can generate HTML on the fly. More on that later.

The reason I'm considering making AI its own course is because it's changing so quickly. Every few weeks I seem to discover another practical way to integrate it into an Access application. Rather than squeezing those lessons into the Developer series, I'd rather give AI its own home where I can continue adding new lessons as the technology evolves.

So let me know what you think. If enough of you are interested, once I get further along with the SQL Server course, I'll start building a dedicated Microsoft Access with AI course. I have a feeling this is only the beginning of what's possible.

LLAP
RR