Getting ChatGPT to categorize clients expenses [AI Bookkeeping Workflow]

Hey, this is Isaac from Oso.co, and in this video I'll share how to build an automation from scratch to pull uncategorized expenses, how to use AI to categorize those expenses for you, and how to replicate that across entities so you can run this process if you have 10, 50, 100 clients as an accounting firm, for example. So stay tuned to see if AI is capable of doing this job well, doing it right, and how you can set this up for yourself. So let's get into it. We are going to start as One of the tools for this demo, as well as make.com on the automation side. If you're familiar with Xero, you're familiar with this interface where you come in and reconcile a particular bank account and you would see certain transactions that don't have any matches. Like for example, this Smart Agency, this Hecaranda, this Corporation Fee, 7-Eleven Bakery, et cetera, et cetera. So what we've built in Make goes ahead and actually I'm gonna run it for you, goes ahead and find those different Transactions. As you can see here, it picked up Smart Agency, which is on the 20th of March, and is using AI to try to understand where it came from, in what category it should put it in, and what's the code of that transaction so that it then creates a transaction. And then we should be able to see the match of here. If you notice, it probably takes like 10 to 15 seconds per transaction, so it's gonna take a little bit to finish the process. But if I refresh the page, I should already see the match for this particular Smart Agency transaction that I should just go ahead and click OK to keep going through the process. Let's just quickly check what it has already done. As I said, it kind of takes its time, but if I look here, I should be able to see Cooper Bakery being run already. I should be able to see, uh, 7-Eleven. I should be able to see the Ridgeway Banking Corporation as well as the Truxton Property Management. So if I go ahead and refresh this page again just to be able to see if other transactions have run already, I can see the Truxton Property Management, the corporation fee here, 7-Eleven, looks like there was a rule here which maybe prevented that from coming through automatically. That maybe it's a topic for another day. But Cooper, like it's running it for every single transaction that it finds that hasn't been categorized yet. I'm gonna stop it for those demonstrations for to kind of walk you through how we built this and we'll go ahead and do that one step at a time. So let's look at the end. Let's start with the end in mind. You can see here that the last step, it's really creating a bank transaction that then we can match to. If you haven't worked with Make before, you really just have to create this little bubbles. Each bubble represents a step, and within each step you can add or connect your account. So if you haven't done that yet, you would simply click on add, click on save, and it will prompt you to go into a page where you would log in and provide access to the different companies you have under management in your CEO account. This is where if you would have, if you want to do this for 3 clients, you can. If you do want to do this for 50, you would simply select all of them and provide access for Make to have access to those transactions or to those accounts, I should say. Now, as you can see here, we're using the create a bank transaction step or action. And what's required is an organization, a type, if it's spend, if it's a receive, et cetera, a contact and a line item about the description that has the amount, that has the account code, and that also has the bank account. The reason I like to start with the last step, because it informs what we need and we can reverse engineer from there. For example, if I know I'm gonna need an account code, well, first I want to give that to ChatGPT to tell me which category that transaction is for, right? So I'm gonna need 2 things. One, a ChatGPT module. We're gonna see that in a second. And I'm gonna need a way to get all the accounts of that entity, give those as options to ChatGPT. That's one. Number 2, I noticed that also I'm having to provide a bank account. So I probably also need the list of bank accounts and which bank account is that transaction on? And number 3, as you saw earlier, I also need a contact. So I need to either search for or create the contact if it doesn't exist. By looking at the last step, now I understand what are the components or information I need to effectively reach the end goal, and I can reverse engineer from there. So if you look at this, actually these steps here after ChatGPT are effectively transforming or getting me the code. And creating the contact. These previous 5 steps are getting all the bank accounts and all the uncategorized transactions within those bank accounts. And these first 3 steps are actually getting all the accounts, specifically the expense or direct cost accounts for ChatGPT to know the options they have to select. So with that in mind, let's go and now work, you know, from the beginning to the end to better understand what this is doing. So the first step here, as we discussed, is getting all the accounts. Unfortunately, Xero doesn't have a direct module that says it's called like list accounts. Maybe they do and I'm not aware of it. But if you look for Xero, you'll see a list of options you have available to you. If I put, for example, here accounts, I can create, update, get, or search for specific accounts. So maybe this could work. But in this particular case, we decided to make an API call just to get— make sure we get all the accounts we need. There's possibly easier way to do it. This is how we've chosen to do it for the particular demo. After we have all those accounts, We're going in and using this little make module, which is called an iterator, which is effectively going through a list. And I'm giving it that list. What does that mean? If I go in and look at the accounts, there are a total of 58 accounts. I don't need all of them, right? That includes bank accounts, that includes equity accounts, revenue accounts, expense accounts. And because we're looking to categorize expenses specifically, I only need or want in this case those that are type direct costs. Or type expense. So what I'm doing here is going through that list of each account, filtering only give me direct cost or expense, and returning a text that has all those simply separated by a comma. Purchases, cost, and you can see that here I'm putting not only the name but also the code because this will come in handy. This is effectively what I'm going to give ChatGPT to work with, and this will be unique per entity, right? Because not all clients, not all entities will have the same accounts. In this case, you can see like heating, office expenses, repairs. Pay your maintenance, advertising, bank fees, cleaning, consulting, et cetera. So those first 3 steps, as we saw, that we're gonna need all the accounts, that's what those first 3 steps are doing, getting all the accounts. These next 5 steps are actually getting all the bank accounts specifically, as you can see by the specification here of where type equals bank. And for each bank account, it's getting a bank statement report. This is one of the actions we have available as long as I give it the bank account, which is gonna come from the ID of that list of bank accounts. And I have I have a date, which in this particular case I have a dynamic date that says, hey, from 30 days ago up until today, give me that, the bank statement report of the last 30 days. Okay? Now if I just click in just to better understand what this returns, we'll see we have it in a much easier to read format later. But I can see here that I have a bank statement report. If I click on rows and dive into some of these things, let me just open some of these up for you just so you can see that I have effectively a header, quote unquote, and then the rows. So the header would tell me, imagine this as a spreadsheet and these are the different columns this little bubble returns me. Like it's giving me date, description, reference, whether it's reconciled or not, which is gonna come in handy to know which categories to allow to go through this process. And then all of these things are effectively rows, individual rows. So if I click on one of those, for example, I'll see a particular date. I'll see a particular description, right? Which is the second row. And then the fourth value, I should see whether it's reconciled or not. This one looks like it is. Let's see if we can find an example of one that is not very briefly. All right, so as an example here, one of the transactions that's not reconciled, 'cause the fourth column has value of no, it's as we saw earlier, Smart Agency, right? So again, this is a much more harder to read format, but ultimately what we're doing then, it's going through row by row, as that report that, that reports give us. And specifically, I want to make sure that I ignore anything that's opening balance or closing balance. But specifically, I want to get the 4th value is not yes. In other words, just like we went through that list and saw some of those transactions have a no in the reconcile value. And actually, let's look at this so you can more easily understand what I'm talking about. This is one of the values that was able to go through the process Because it's not an opening balance or closing balance and it is not reconciled. So all of this is doing, it's getting all the bank accounts and for each bank account is gonna give me a list of the transactions that have reconcile as no, they're unreconciled over the last 30 days. Now that I have that information, I can create a prompt and you'll notice that I have 2 Zapier steps here. Okay? And there's a reason for that. The first step goes ahead and tells the system what to do. The second step is going to structure that so that I can use it later. Here's what I mean. The first step simply has a prompt. Hey, you're a bookkeeper. Analyze this transaction and return the following. Give me the transaction where it took place, what the expense is, the code of this transaction, including the— remember I give it both the name of the expense and the code. So I want both the name and the code. Give me the payee name. Remember, I'm going to want that for a transaction. Sorry for a to create a contact later, and how confident you are of your determination of the above. And then I just give it the description, the reference, and the amount in case that's useful for it at all. In the next step, though, you notice that this says transform text to structured data. So this is actually where I tell it, hey, give me exactly these things in a more structured way. So I'm telling it, hey, make sure to give me the pay name, the country of origin, the category, the code, and the confidence percentage. But then give them to me in a way that I can use them later rather than this like giant block of text. Text that's going to give me in this first step. Because you know when you're chatting with ChatGPT and you ask it that, it might give that to you in different format. This is effectively standardizing that format so that I know exactly how to use those moving forward. So this is effectively the ultimate outcome. I have things that I can easily reference in it in the following steps for payee name, expense category code, and, and the expense category name as well. So in the next step, what I'm going to be doing, as we saw earlier, to create the benchmark section I need a contact. However, I could create the contact, but that contact might already exist. And that's why here you see that we have like a little bifurcation, if you like, of saying, hey, first search for the contact to see if you can find it. If it doesn't exist, create it and then create the event transaction. If it exists already, well, you have it, don't need to create it. Go ahead and create the transaction. And that's essentially how we go from Getting all the accounts, getting all the bank accounts and the uncategorized expenses within those accounts, running that through GPT to give us the account code to put it on and the P name so that then we can create the contact, create the transaction, and then be able to reconcile that on Xero. Now, how would you do this if you wanted to do this for other entities? You want to replicate this across clients. That's something that we regularly see with the clients we work with because we work exclusively with accounting firms for the most part. And here's where having just a simple list in a Google Sheet or any other data storage solution comes in handy. Now let me show you what that list looks like within Google Sheets. So as you can see here, I have just 3 simple columns. I have a client name, their organization ID, and a status. Now you might ask yourself, well, I'm not sure how I would get this organization ID. I'll talk about that right now. If I go into Make again, you'll notice that when I click on any of the Xero modules, the organization is one of the variables I have to put there. But right now it is fixed, meaning this is only running for the demo company. If I click on it, however, I'll notice that I personally have access to 2 other quote unquote entities. If you were an accounting firm and you have access to 30, 50, 100 clients, you could connect and see all of their organizations as well. Now the beautiful thing here is that if I click on map, Make shows me what it's effectively reading this as on the backend, and that is the organization ID. So by selecting the organization from the dropdown, clicking on map, I'm able to get the organization ID and I'm able to create this list for myself. And just to demonstrate that this works, let's actually go ahead and make this first step the trigger. This first step is looking at that sheet and specifically the clients that are active. Let's actually test that particular module so we can see the, what it returns. You see it says, hey, this is the demo company and this is the ID for that company, but this is Opsur and this is the ID for Opsur. These are the only 2 companies that are active in your sheet. If I had again more than that, it would give me more than that and it will run all the other steps for each one. of those entities. Now the next step would be for me to, rather than have the organization selected, I would click on map, I would remove the existing one and I will tell it, hey, look at that organization ID from the first step instead of having a static value. Now I have a dynamic value that's gonna run for multiple, multiple entities. I would have to do that exact same thing of other zero steps. So let's go ahead and do that quickly here. Okay, so I'm gonna run this. I'll disable this cuz I don't wanna mess up with my current account. And before I do that, actually, let me add to the variables of the readable format we created. I'm just going to add this kind of like client or entity. And I'm going to add here whatever comes from the first step, but on the first column, on the client A column. And this will allow us to see, actually, let me just remove the ChatGPT steps for the purposes of this demonstration. This is going to increase the delay a lot. And I just want you to see that this is running for all clients. You see that it run 20 first and then it ran a lot more. The first 20, as you'll see, it's for the demo company. But if I scroll down and look at the other ones, I'll start to see, guess what, for Opsur. And specifically this is like, this is my name. This is like the credit card that we're using. This is Pipedrive, the Pipedrive transaction that came from our Xero account. So effectively what we've done here, if we've replicated quote unquote this across entities, and as long as I connect those accounts to Make and add them to this list and mark them as active, I'll be able to run this exact automation For as many entities as I want. But let's do this. Now, there are other things you can automate with Xero and AI. And if you want another powerful use case, I invite you to check the video on the screen that's showing up now where I turn a spreadsheet into as many invoices as you want in Xero with just one click. So check that video and I'll see you on the next one. And until then, stay efficient.