Automate Your Financial Reports with Make.com: Step-by-Step Guide

Hey, this is Isaac from Opsa.co, and in this video I'll teach you how to automate your financial reports in a way that's accurate, fully customizable, and automated, so no one on your team needs to learn a new process. We're going to be using QuickBooks Online as the source of the financial information and Make.com as the automation tool. Let's get into it. So let's assume you are an accounting firm, an accounting practice, and you're introducing a new service, which is a weekly management report, which you can probably charge for. We've seen clients that ask a service line and have a revenue stream associated with it. That weekly management report, for the sake of this example, let's just say they're just getting a snapshot every week of in that week, what was revenue, what was expenses, what was profit, and what was cash balance. Let's go into Make just so that you can see the resulting automation. Relatively simple, 4 little steps. And in this particular example, we're assuming that The client is just going to receive that via email. You can probably get more fancy. I'll talk about that in a bit. But in this particular case, it's pulling the P&L from QuickBooks, the balance sheets from QuickBooks, it's doing some calculations, and then it's sending that via email. In this particular case, I should have a weekly management report in my inbox that says the total income, the gross profit, the other income, the total expenses, net, and cash balance for that particular— or at the end of that particular week. That's it. It's as simple as that. Let's actually build it from scratch so we can see what are the components that are required here for you to build this yourself. All right, so I'm gonna go to make.com. You can sign up for free without a credit card. They have a free plan that you can get started with if desired. And I'm gonna log in to our account and create a scenario from scratch. Here I can see create a new scenario, and a scenario, it's essentially what make.com calls a small automation that runs on a regular basis. The first thing you'll see is that it's asking me for an app. In this particular instance, what we want is, as we mentioned earlier, we want to pull the data first before we do the calculations and before we can send it to anybody. So that's going to be our first step. We're going to look— I'm going to look for QuickBooks specifically. Now traditionally, I would ask that you first look through their available steps to see if there's anything available that you're interested in. In this particular case, I'm going to get a report, specifically the P&L. So if I scroll through this, I'll see that they have actions for invoice, for bills, from sales receipts, journal entries, payments, and on and on and on. But they don't have anything, at least at this point in time, about giving me a particular report. However, at the very bottom, they do have what they call an API call. If you're not familiar with what an API is, it's fundamentally— I just like to call it a login page for a robot. Just like you log into QuickBooks and you can download or look at a profit and loss report, there are ways to automatically, in this case via Make, get that P&L, but they just don't happen to have a module or a step for it. So what we're going to do is we're going to click on API call. And if you really don't know anything about this and you're getting started, what I would do, and I will just do right now so we can see, it's I want to get a profit and loss report. Oops. Profit and loss report from QBO API. Tell me what's— as you've noticed here, I actually need a couple of things. What's the URL I need and the method to get it? Fundamentally, when you're working with the API calls of the Make modules, and this is applicable as well within Zapier, you just need a URL and a method. You might need other things like certain parameters But I'm gonna, I'm not gonna like dive into that in this very moment. Technically, you could also visit the official API reference, but we've got ChatGPT these days that often does a pretty good job at getting it right most of the time. So what you'll notice here is that it's telling me that the GET method is the one that gets me the report, and it ends in /reports/profitandloss with the company ID, like /v3/company/company ID. But I do know that in Make specifically, they tell me, hey, enter a path relative to all of that base that we just talked about, the version, the company, and the company ID. So in this case, I actually only need the last few, the last few words, in this case, the last few slashes of this process to actually get this done. Going one step back, do know that I already have my account connected. We have like a demo account that we use for the purposes of these videos, but if you don't, you would just click on Add, call this, you know, Account X, right? You would put maybe the email address or the company name that you're working with at the moment, and it's going to redirect you to effectively log in and sign into your Intuit account. And it's a relatively easy process for you to connect it. I already had it connected, as such I didn't need to do it again, but that's something that if you're doing it for the first time, you'll need to do. Now, as I said, reports, uh, /reports/profit-and-loss. And let's test this step. So I got something. I'm not going to dive into what I got specifically, how I'll get into the numbers that I want. But I do remember now that we're going to want to also have in the query string the start and the end date. Let's see if that's something that ChatGPT picked up. Here it is. Query parameters that you'll often need to customize the report. Accounting method, if it's accrual versus cash. I believe they do cache by default. So that's something if you're interested in that you might want to look at. If you want to summarize the column by month or quarter or year, in this case, I'm not interested in that because I'm just going to be looking at a week. But I do have a start date and an end date that I can add on to that request to get a specific timeframe. And it's actually giving me even an example of what that looks like. Start date, An end date, and it's telling me the format it needs it in, which is 4-digit year, 2-digit month, and 2-digit day. So for the purposes of this example, just because I'm using a demo account that has data in a specific period of time, what I'll be doing is I'll be adding a query string, right? And let's say this is the start date, and in this particular case, it's actually the 1st of June. From the 1st of June, and if we wanted a week, That would be up until the 8th of June. Let's rerun that module and let's actually now take a deeper look into what it has. Now within Make, you get these little bundles and this ability to toggle to see more. If I click on body, for example, notice that there are headers, there are columns, and there are rows. The way to think about this is that what QuickBooks is returning me, it's almost like a spreadsheet but in a different format that's easier for the computer to read than a spreadsheet, right? So if I look at header, I'll notice there I have several things here: the start and the end period, the report basis, the report name, and the time that this was pulled on. Summarized by columns, by total. So I can see some of the details of the metadata, the information about the report itself. That's not necessarily what I'm interested in at this time. I actually would like to instead Get the total income, the gross profit, the expenses, and the net earnings from this particular step. I'll dive deeper into how to get that specifically. For now, however, let's work on the next step, which is actually a balance sheet instead. So I'm gonna be— couple ways to do this. I can just clone this step, and in this particular case, guess what? Rather than reports balance sheet, I'm going— rather than reports profit and loss, I'm gonna want to do reports balance sheet. To be able to get this information, and I'm not sure. We'll ask ChatGPT right now, but I believe we just need an end date. So let me verify that real quick. All right, so let's verify here. What parameters and URL do I need for a balance sheet report instead? So I already know this, but it's reports balance sheet. I do want it to tell me looks like. I could also do start date and end date, so I'll just leave it like that for the purposes of this example. And I can run that one separately as well to see if it is successful. I see a little checkbox and some results. I'll dive into how to get to the specific numbers I want in a bit. Actually, I'm noticing it still says profit and loss. Looks like I have it— I didn't save that properly. Let's do that again. Balance sheet, save, run this module, and then we got it. The balance sheet. Okay, now as you saw in the example we provided earlier demo, we got a step that's doing some of the calculation or specifically accessing this specific information from those previous steps. Okay, this is where it gets a little bit more technical, but I'm going to walk you through it hopefully in a way that's super simple to understand. So for the sake of this, let's just say that we want total income, we want gross profit. We want total expenses. We want net earnings. Those are coming from the P&L. And we also want cash balance, which will come from the balance sheet. Okay? Now, this is where I'm going to start looking at the result of this step and trying to think about how would Make access that specific value. 2 concepts worth remembering. As I do this, every time I click a plus and a numbers come up, that tells me that I can access that in just like object notation and programming language with a dot. What do I mean by that? Body.rows would effectively get me to this place, right? And body.rows.row would get me to this place. Now, the moment I see numbers, now I know that the appropriate notation for that is to actually brackets and the number in question that has the information I need. Let's translate that into simple language. Every time I get a result, I'm going to look into where do I ultimately have the specific value that I want, which in this particular instance is right here, total income. And I'm gonna reverse engineer from the very first value. What is a text, which would be a dot, and what is a number, which would be a bracket, so that I know exactly where I have to get to. Super fancy, but I'll show you what that means right now. Pay attention: body, rows, row, first value, summary, column data. Second value and the text value. What does that mean? If I come here as total income, I know that I'm gonna want not from this second step, which is the balance sheet, but from the first step, body.rows.row, right? Then we talked about summary, column data, and value. However, you'll notice that there's this space in between row because what row is a list as we saw. And column data also a list. Going back to what I mentioned earlier, I know that when it is a number, I'll have to put in that number, which I know for the row it's the first value and for the column data it's the second value. What that means is that if I put a little 1 here and a 2 here, this tells MAIX to access the total income from that first step. Okay, again, maybe a little fancier than you'd like, but let's actually test this and see if that number comes up. There you go, 4,034.31. We would repeat this logic again for these other ones, and if you look at the result, it's actually very similar. The way after you get the first one, the other ones are super easy because the second row, it's the one that has The value of the gross profit. The 3rd row is the one that has a value of other income, and all of them are in the 2nd value of column data. Same thing for expenses, as you can see here, and same thing for net income, or they also call it net earnings. What that means is I'm going to copy this exact thing that I have here, and now I know that the 2nd row is the one that has gross profit. The 4th row is the one that has total expenses because the 3rd one was other income. And the 5th row is the one that has net income. If I run that again just to test and see if that's coming out correctly, I can see that indeed I have income, gross profit, expenses, and net earnings as expected. If we do the same thing for the cash balance, we have again body.rows. .row list. So I know I have to know if this is the first or is it the second one that has the value I want. In this case, I see that this is net assets and this is net liabilities. Because I want cash balance, that tells me this is probably the first row that has that information. If I go to Summary, Column Data, I can see the value again of total assets. So very similar structure to the one I just mentioned, but rather than coming from the first step, I want that to come from the second step. So if I go again, body rows row summary column data and value, knowing that this is coming from the first row and from the second column, I should be able to run that. And as we saw here, the cash or the net, the total assets for this particular example where 20,786. If I run that now, I should be able— this little— on this little bubble, which is the outcome of those calculations, I should be able to see that right here. If I wanted to do margins, I could now add another value and make the calculations off of that. If I wanted to calculate something else, maybe within net liabilities, like, you can get creative. The point here is that we have all the information we need. And we have the means to calculate it and automate it with this tool. Lastly, let's just do a little final step here to send an email, in this case via Gmail, which is what we use internally. And you could do that email, it could be to— in this case I'm sending it to myself, but you could actually create another step that gets the email associated with that particular QuickBooks account if you desired. And I'll just call it weekly Management Report. And in the content, I'll just say total income. And what do I do here? This is fixed, this is static, but the values can change for every email. And that's where I go to the first to the last step where I make those calculations to make sure that I, uh, where I access that information from the tool, from the QuickBooks module, so that I can make sure that that number changes Over time, so total expenses, let's do net earnings, and let's do cash balance. This is effectively a replica of of the demo I did earlier. There are several things that can be done to improve this process. One of them, it's being right now these dates are static. So there are ways to make sure that these dates change every week to make sure that this it's running and sending new information every time. Just because this is a demo, this is the week that has information, so that's the week I decided to do it for. Also, you'll notice that this, at least in the emails coming all together, but this tells you that you can use HTML tags. Not to get technical on you, but BR, it's the HTML terminology for simply like a line break, look, or hitting enter. So if I do this just to do a little bit of a different example for this particular use case, I should be able to see an email now, but now with spaces, for example. There are certainly many other things we could do to improve this process. You could edit the email, make it more visually appealing. Rather than being an email, maybe you create a document first, or you add charts, and maybe you just attach that document in the email instead. You can really get as complex as you'd like, because as we said, we get that information in full detail from QuickBooks, and we have the means to automate it and imagine what that could look like. If you like this example, you're going to love this other AI bookkeeping workflow that we built using Make that helps you suggest or categorize the expenses for your clients. I'll see you on the next one, and until then, stay efficient.