Generate Your Tax Invoice PDF for Research and Development Effortlessly

Tax invoice pdf for Research and Development

in this video I'm going to show you an incredibly practical invoice parsing automation that will be able to take any amount of PDFs and parse and extract the data from those and add them as new roles into a Google sheet we also have a second automation that will watch for emails within your account and automatically grab the invoice PDFs from emails and add them to this folder and those will then be picked up by this automation automatically we're using invoice paring technology in addition to open ai's gbt 40 to help to really refine the output and get it in the correct format because invoices can be in all sorts of different formats and we may need to infer certain data from those invoices from there it's really easy then to create pivot tables so you can then create reports based on those invoices such as you can sum up everything based on your vendor name or the vendor country then you can use Google Sheets internal functions like convert currencies and whatever else you want I'm going to go through this entire process but just one explanation of why there are two separate routes here and this is because we want to be able to handle both machine Redal PDFs and scanned PDFs for example in this sample invoice this is a PDF which has been exported and you can actually click and drag and copy out some of that data this is a machine readable PDF however let's say we have an invoice like this which is not machine readable so it could have been printed and scanned and you cannot select that data we need to use a vision model to be able to pick this up and that's what we're doing in the second flow here and if you want to import support this blueprint into your own make.com account then check out the link in the description to our community we'll get access to all of our automation templates including the one in this video let's start from the very start I have a blank make.com scenario which I've called invoice parsing this is going to be looking out for PDFs within this Google Drive and then process those and then add them to the spreadsheet then after that I have this other blank scenario which is to process emails with invoices and this is where we're going to be looking out for emails within this email account either which contain invoice in the email and in other cases we can manually add a label to the emails so that they also get picked up in the Automation and we'll do that after because the core of the automation is within this invoice paring after this this processing emails is just a way to make the process more efficient we have quite a mix of invoices that we're going to be processing here between PDFs that have readable texts like so and these ones that are more like scanned images we're using sample data for these invoices but they are in the same format as real invoices first off we're going to select Google Drive and then watch files in a folder we're going to watch files by created time so this is going to watch out for just new PDFs within this so choose a drive so I've selected my folder select all files typ to watch and for now I'm just going to limit it to one file at a time and I'm going to right click choose where to start I'm going to select all which means that we're going to pick up everything that was previously added to this okay so I'll press run once and that has picked up this first PDF which is good next up we want to download that file so I'll go to Google Drive download a file and then just enter manually file ID is this file ID as you can see up here which is good and press okay I'll press okay and I'll press run once again and that should download this file which is good next up we need to use an invoice parsing service that can process those invoices there are lots of different services that you could use for this but we're going to use pdf. it's really easy to integrate with make.com and it will work within a few minutes you can start with a month free trial for that and we're not sponsored by them in any way but it's very easy to integrate this with make.com you can also use other services such as mind.com which gives you 250 Pages free per month there's no official make.com app for this there is a community one which I believe you have to pay for so other than that you would need to use the HTTP module and use web hooks so it's a little bit more complicated we're going to go with this pdf. process for this but the same kind of logic of processing the fields will apply to lots of other Solutions in order to try and be consistent with our testing what I'm going to do is I'm going to create a new folder and I've just written other invoices I'm going to move most of the invoices all of these invoices to this folder and then from then on whenever we're testing this we'll be testing it with the exact same PDF every time then after that we'll move on to the other ones all right so next up I want to update this Google sheet with the data from pdf. after that we're going to add another step which is to use AI to to try and add extra information to this beyond what this pdf. Co is providing so Google sheets I'm going to add a row okay so I've selected this invoices accounts payable spreadsheet sheet name is sheet one table contains headers yes and all right so we go to invoice select invoice number there invoice date is this vendor name and scroll down to vendor address so vendor street address at a comma and I'm going to select city after that country is here we do not have postal code I'm just going to add it to the vendor address as well contact information phone email then from there we're just getting the tax amount total due date which is here zoom in a bit more currency we do not have from here we do not have tax rate or tax type from this I'll press okay press save and I'll just run this again so it's going to take that document it's going to go through and pass the data from that perfect looking good I'll just clean this up a little bit and select this box up here unbold it then select this first row and then bold that so if have the invo number date we have some information missing but we have plenty to work with for the moment when it's been processed I want to move that PDF to the process folder so I'll go move a file move a file folder invoices and then the processed folder and then the file ID should be the file ID it picked up at the very start of the process click okay so select okay okay looking good save that should work I'm going to manually move this to process for the moment for the next invoice that we try I'm going to test to make sure that move works I'm going to right click and choose where to start I'll select all because we want it to process and select run once okay it's picked up the file downloaded the file now let's going to CL through and process that and we'll just wait for the result okay so this data is pretty good but it's not in the exact structure that we want for example we have currency signs here we don't have the tax rate or tax type and we don't have a country I'm going to now call open AI pass through some of the data from this and then further enhance the results that we're getting here so I'm going to add a module in between here going to select open AI I'm going to create a chat completion prompt I'm going to use GPT 40 latest if you really want to get the best results you can use the 01 models which have Chain of Thought built in so you can add that in as well for the gbd4 all models you add in a system Ro to give it context to what you do you do not do that for the all1 model so I've gone to chat gbt and actually asked it to come up with a good prompt to use here and it's come up with this pretty good example prompt which is extract the structure of the following data in Json format the system role is your data extraction assistant design to process and structure data that's enough as a system role I think to start with next we add a message select user and this is where we're going to provide a lot more information so we're going to be explicitly providing some of the data from pdf. Co and then the rest they may need to infer currency infer and monitor symbols or contacts we also want a nose output add so add any additional text here if you are unsure of the answers you provided let's just see how that goes so we have Ireland currency e subtotal we also want to get dates as well so we have invoice date and due date we want to get those responded as well and let's just say we want to get it to respond in this exact daytime format you can change it to whatever format you want of course I'm going select du days day 12 12 okay this is the correct format and that should be enough for now so we're based on OCR text below okay I'm going to copy this directly into my prompt within here going to add another module transform to Json I'm going to pass in the entire body of this message back into Json and then the OCR text will be I'm going to press save save anyway go down to here then under this OCR text going to actually refresh the page entirely here make sure to press save before doing that because that Jason the transform to Json did not show up for me okay Json string perfect now let us provide so this is the example output we really want for this we're going to explicitly provide certain values we have tax we've total and we have sub total invoice date is this and the due date is under payment is that due date so if data is not provid or not inferred then respond with the data field but value should be value should be an empty string okay so they wanted to infer the country the currency for Country okay we're going to leave that as it is we could just pass in all of the data in OCR form in here and not provide this detail I'm just doing this to try to minimize hallucinations a little bit so currency country tax rate so the tax rate should be percentage so only provide this if the percentage has been included within the document and we'll see our string and press okay press save and from there we're going to unlink this for the moment and just let it run through that we're going to bring this invoice back out again and let it pass through this again so run once so I'm going to choose where to start all click run once okay let's have a look at the data that we got the result is a long string country Ireland invoice J all right this is looking pretty good that got almost everything I believe 1150 265 14 yeah that's pretty good the tax type is that look pretty good and currency is looking good too right I'm happy enough with this there's one other thing we need to do which is to go down to the very bottom go to show advanced settings and to change this to Json response we want to do that but also I noticed that it is not actually picking up the tax rate so we'll go back to the user percentage vat or tax rate so that should be included within the document press okay press save and we will run that again run once let's pick up the file from Google Drive again right that's that is processed let's look at the result country invoice date due date currency right everything's looking good again we do not have the tax rate I'm going to update the prompt to call this tax percentage let's be a little bit less prescriptive on this and just say percentage V or tax rate and it should get that from the document we totally could calculate this based on the subtotal and tax but I'm just trying to get the information that's been included within the invoice here okay finally we have currency which we forgot to select there and I think we now have everything populated we should okay that looks good you might be wondering why I'm using a combination of pdf. logic and open Ai and that is just that you could feed the entire feed into open AI I'm just taking the response from pdf. and if that's giving us a good response for certain data items that I'm happy to use those and then try and use open AI gbt 40 directly to try and infer other data from that you could of course just feed the entire thing into open Ai and come up with that I'm happy to use a combination of both here so I'll press okay now we still have this within this folder so I'm going to choose where to start select all and I'm going to now click run once and get it to try and do this process end to end of getting the file and then updating the row and then moving that to the other folder that's been processed now we have much cleaner data we have the invoice number the date in that format the due date is empty with currency here are subt total tax total okay there we go that looks pretty good I'm going to now take all of the other invoices and try and process every single one of those going through this exact system okay now I'm going to choose to start select all and then press run once okay we process the next invoice 8835 the Judit an invoice number looking okay there and yeah that's good we're going to just process the rest go down to limit here limit of 10 click okay run once then this will just go through and process all of the remaining ones within this so there will be five more to process okay we've now created a few more of these so let's have a quick look through go to this processed field okay the second one there so this one here is a Belgian one and vendor name looks okay invoice number is correct 1815 yeah and this is a good example of where this works nicely so this common delimiter is what they use in Germany and we have the Euro sign and here we have a decimal place instead so that's pretty good we have the taxable amount as that and 21% that okay let's go to the next one this make invoice vender address is looking good there we have the correct date vendor name is correct again yeah subtotal total tax amount yeah all good and I write a not here currency is listed as USD which conflicts with the customer billing country but that's fine so that's okay right I like the fact that we have lots of notes here to review just to check the rationale just to see if there are any issues you can go back to this prompt and then get open AI to be not as verbose in its responses but I like having this because then you can have a human in the loop to kind of clean these things up because the results from these are often not perfect okay we've gone through and processed all of those documents there is an issue that in these last two documents they're actually scanned PDFs even though they are PDF I cannot select the text whereas I can select the text for these ones for example and this poses a kind of a unique challenge here because this pdf. Co is not responding with anything now what I'm going to do in this case is check to see if the body respond is empty and if it is that means that pdf. is not processing it and then we'll feed this to open ai's Vision API to then try and get it to extract the relevant data from that so let's get it to do that what we're going to do is we're going to add a router here going to add a new section here so if condition if body exists okay and if there's no response I'm just going to set this route as the fallback option so that means if it does not go pass through this it will pass through that so in this case what I'm going to do is I'm going to select open Ai and then create a completion prompt then feed in that PDF image okay it's just hit me that we're working with PDFs and this open AI Vision mode of the API only works with image files so we're going to need to convert the PDF to an image file First Press okay going to add in a new module here there are a bunch of different Services we could use to convert from PDF into images but in this case we're just going to use this pdf. Co again to do that we're going to pass in the data and convert PDF to PNG and we're going to just use the first page then the export type will be to download the file and then from there we're going to pass that into open AI so the image file will be the convert from PDF into images just okay press okay for the moment okay I'm going to go back to open AI again here and create a prompt for GPT 40 where I will pass it an image and it will attempt to extract data from the invoice we'll pass in an image of an invoice and we'll tempt it in the image respond I want it to respond in Json format here is an example of the data and we're going to pass in an example of data like so within the prompt provide the expected and with example okay then it's going to create a prompt for us okay now it's going to provide some information for the prompt to give some more examples of what format we want and examples and so on okay this is pretty good it's what we're looking for here and it's mentioning about null going to copy this into notepad here and return an empty string for that field so if there's if there's no if the information is missing on clear return an empty string for that field and popular Jon with extracted data if the data is not accy use an empty string right this is pretty good I think now we're going to go back into text content this is the system R so that gives total context of what we wanted to do and then within the user role here just going to ask it to extract from the provided image PDF cool now finally last few things here show advanc settings response format is Json object and very much par the Json response now we need to go back to here we're going to need to bring one of these printed PDFs so the scanned PDFs back in and we'll press choose where to start all and now run once I was going to go to this pdf. Co and if it does not read from this that has processed the response was actually empty but it actually went through this process I realized that this body in this case it actually does exist but it's a collection so what we need to do is go to body and go all the way down to array length greater than zero it there was no response from pdf. Co so now went down back to this fallback route which is this and let's have a look at the result from here that's not a bad response from open AI here the tax subtotal all of that is looking pretty good the invoice number is correct so even though we have a pretty pixelated invoice here it picked those up pretty well you know technically you could put all of your invoices through this process and just completely ignore what you're getting from pdf. Co in terms of it structured data but if you can get structure data from your PDF it's better to do so in general right so let's right now we're going to clone these two modules press copy and press paste there looks good and now we're going to just populate the values that we got from open AI because we got it in structured Json output so we can select the invoice number the invoice date due date vendor name currency subtotal tax total tax rate tax type vendor address vendor country vendor email vendor phone and notes okay and this should continue to move that to the process folder excellent now we're going to start from the start again choose where to start select all click run once okay so that's worked let's have a look at that excellent so now we have populated data from that which looks pretty good excellent now we just I'm just going to delete the roles that were not relevant there and I'm going to delete the first two rolls here as well so this is looking pretty good now we can handle machine readable PDF files and image files this process is only going to work for PDFs but now that we're actually reading images you could create a separate route here which is to check the file type of the attachment and then have a separate route down here which then goes through this so we're not using the PDF functionality at all and then we're just using the vision capability of open AI I'm not going to do that for now but you can effectively just copy the logic that we're doing here and just have an extra router here and then that will perform the same function so this is in pretty good shape we have a lot of the data we need you can customize this however way you want now I'm finally going to very simply create a watch emails trigger within a new scenario within make.com which is going to look out for new emails that contain the word invoice or where we've assigned the label invoice to it it will automatically pick up emails that have the word invoice in it if not you can look through your emails and then manually add that label to your emails so I'm going to go and select Gmail and watch email going to choose a folder going to select where these are in the Inbox and I'm going to use a Gmail filter I have a filter within Gmail here which is it's going to automatically pick up either emails that have the word invoice in them or where you've gone through the emails and manually added invoice as the label for that and it's also only going to pick up emails that have an attachment so has attachment those Gmail filters are really useful if you want to to use Gmail in an advanced way and then the maximum number of results for now we can select one and I'm going to Mark email messages as R when fetched which is yes the invoices will come into your Gmail inbox and then read those take the attachments and then Mark the emails as red so that means that you don't really need to worry about them you can of course Mark that as yes or no by the way this entire process is not mandatory for this automation we already have this entire invoice paring system if you are client want to just use that by itself you can just drag and drop all of your invoices into this folder and then away you go this extra automation we have here is just to make things even more efficient so I press okay and then I'm going to only process emails from now on so I'm about to send an email to this mailbox and it's going to watch emails and from there I'm going to get it to iterate through the list of attachments going to select attachments and then so I'm going to select Google Drive upload a file to Google Drive then select my connection invoice processing invoices so that new attachment should be dropped straight into this folder now going to select map and that should map as we need already press save going to select run once now it's picked up that email it saved the email to this which is looking good now we're going to go back to this and run that automation so that that took almost no time at all to set up that automation right there and you can just have this running on a schedule then after that have this also running on the schedule and then get this to just pick up your invoices throughout the day so you can get it to run possibly multiple times at regular intervals okay so we now have a roll here but I realized that the this route actually did not work that was machine readable text I realize this filter condition is not correct this is a collection and I've asked to check if the array length is greater than that so in order to get around that problem we have a body response when there's a response we have this collection and otherwise it's an empty collection so what we could do is to convert it to an array cuz we were checking to see here if array length is greater than zero so let's go with that click save We Go Again choose to start select all cck run once so this time because the filter is now fixed then it's actually going to go through the correct flow which is to go to this so that body is greater than zero which is good and that looks good excellent so now we have our completed line here so this first attempt at this was using this Vision flow the second attempt was using the flow where we're getting the structured information via pdf. as you see here we're getting slightly better results when we use the invoice parsing option then we're using this finally just to triple check that the printed version is working here I'll go back and I'll just check that again choose where to start select all click run once excellent so that's working correctly there was no response because it was not a machine readable PDF so that has gone through this route and now it's going through the vision model and should work from there excellent so there we go there's plenty of other things you can do with this by the way for example you could select all of these and then insert a pivot table into a new sheet some of the subtotal free trender name of course in this case we have multiple currencies for example you could use the Google Finance function here and get it to convert the currencies to your base currency so let's say your base currency is dollars then you could get it to do that by itself and then within this pivot table you could then use that conver currency within your pivot table and then you have a pretty good report and then every time you update this document then this pivot table gets updated you can include lots of different things there let's select all of the data here within this and then go to insert and then select pivot table new sheet for example you could select vendor country in the columns and the sum of total for each vendor country which is that so almost everything here is in the US of course again keep in mind what I was saying there about converting base currencies if you're dealing with lots of different currencies of course course finally just to clean two things up this was a live build throughout this process so after recording I just did two separate things which is to add this filter after the iterator when processing emails so that it will only pick up PDF files in some cases there might be PDFs and jpegs and all sorts of other files so add that in so it'll only move PDF invoices to Google Drive if you're also separately updating the automation to process images then you can update that filter as well secondly I've added a document type field to spreadsheet the reason for this is that you will often get invoices and receipts sent to you separately in the same email and this update will try and figure out if it's an invoice or if it's a receipt or another type of accounting document I've simply updated the prompt here to get open AI to infer the document type from the document and then I added it to the Google Sheets like so if PDF co could not pass it then when we're going down through this route we do the same thing we're just also getting it to figure out the document type and then we've also passed that into the example data that means that when you're in this document type here you can add in filter so data Crea a filter and then filter out any documents that are not invoices such as receipts or you can manually review them and delete them and by the way if you want to import this blueprint into your own make.com account then check out the link in the description to our community we'll get access to all of our automation templates including the ones in this video you can also get your questions answered via our active Community or via your weekly events we you can talk to in real time