All right thanks so much for joining me today i've got an incredible training for you one of the best i'm super excited in this training i'm going to show you how you can take a picture of a receipt or simply drag a receipt into a folder and automatically have the data in that receipt including the total including the amounts including the receipt number and anything else you want automatically come into excel without keying one single word or one single character yourself it's gonna be an incredible training i'm gonna have that data automatically into that and we're to be able to display receipts just like you're seeing here those receipts can be pictures they can even be pdfs we're going to show you how to do that too and a whole lot more so we've got a.
Lot to cover i'm going to go step by step through by the completion of this training you're going to know how to parse your own data regardless of it is how to automatically get that data into excel without doing anything it's going to be a great training but i want to make sure that you're here throughout the entire training the best way to get these training to get notified is to make sure that you do subscribe click on that notification icon bell i create these trainings each and every tuesday just for you here so i hope you will join us there's some great ways to enjoy excel these trainings are free all you need to do is click the link down below and you can download this workbook with either your email or facebook messenger i go way beyond these trainings if you like you want.
To see what else we have in store every week what i'll do is i'll take these trainings and i'll take your suggestions and i'll add on a feature or maybe i'll focus on a specific part or maybe i'll fix a bug it's all up to you and then i'll create a brand new training a brand new workbook and i'm going to throw all that inside our patreon account starts with just a few dollars a month and there's different levels on whatever features you might want including beautiful pdf code book to allow you to download and see all the lists of the code that's inside our patreon account i'll include the link down below i hope you'll join us inside patreon that keeps these trainings absolutely free for you every week all right let's get to it okay so what do i want to do well basically you might.
Have a receipt laying around you might have them in a folder you might want to take pictures of receipts but what we don't want to do is we don't want to type in the name we don't want to type in the date and expense we want all that automated we don't want to do anything we're really lazy at least i am and i want to make things easy and i want everything tracked so when i take pictures of these receipts i want them to come in excel i don't want to do anything all i want to do is just drag it in a folder this training all you're going to be able to have to do is drag a simple picture inside a folder once that happens everything else is set to automation we'll run a macro and we'll automatically be able to change dates and we'll have that data appear here.
Based on the date it's going to be really simple we've got so much to cover so we're going to get started right away on this i'll go over just this is a very very simple workbook you can apply this i'm applying it to an automated expense tracking but you can apply this to almost any kind of tracking so what we'll do is we're going to parse data and inside pdfs or inside receipts we want to extract certain data and we're going to use software to extract it for us and it's going to be done absolutely automatically and it's going to come into our computer automatically so a lot of automation in this but it's not a lot of code even if you're beginning on code we're going to go step by step it's just a little bit of code the magic happens in some third-party applications.
What i want to do is i want to create a folder for every single vendor i've got three of them i've got a one for home depot which in a lot america is basically a home improvement store where you can buy a lot of stuff for your home rent you know if you're you're paying rent or something you want to put the receipts in here and utilities so for each one of those bills i've got a separate folder now the idea is simple all i want to do is to be able to drag one of these inside the folder or from your phone take a picture and put it in this folder remember you've got this on your phone as you can see here you can also have this on your phone okay so basically once i drag this in and i just run a simple macro that would probably we're going to run as soon as we open our workbook.
That information is going to automatically come here in this expense data this workbook is made up of just three sheets we have our main data it's all of the data here we've got a date the purchase from an expense type a receipt or a referral number a reference number we've got an amount and we've got the file name and we have the row that's this is not we're going to use this might be used for the future so i just put it there but and then we have the dashboard so we want to do is we want to take all that data we want to bring it in here based on the dates that we've selected so we only want maybe we only want to show expenses from a certain dates or maybe we only want a single month so we can do that too if we want to that's very very simple so the best way to do that is just.
With a filter which we're going to go over but again the idea is to parse this data so when we have let's take a look at some what i want to do what i want to do is i've got this receipt let's take a look at one of these receipts okay so inside this receipt i want to extract the data automatically from this receipt so how would i do that well we're going to use the third party but basically what i want to do is i want to know it's home depot i want to take the receipt number in this case it's that 654.0016. it's that four digit then five digit then five digit number i also want to determine the total what was the total of this and i want to know the date when was this date so i'm going to extract that email and that i want to do it regardless if it's a receipt or maybe we've got a pdf let's take a look at another one here i've got a utility invoice maybe we've got a receipt that looks like this so regardless of the type of invoice or receipt we've got this one here.
We've got a receipt and i've got another one here a red receipt you know kind of different formats so we can see here regardless of the type i want to get that information i want to bring it in cell so the first thing we want to do is we need to parse the data we need to have software tell us this is the date this is the number here this is the sum here right this is the total here and we need to know which you know what is this rent so we need some information about that so what we want to do is we want to have a tool that's going to help us to do that and i've got a really really great tool that i want to share with you and you know it's free for the most part and there's a great free basically as a free version you get a bunch let's take a look.
Inside that tool and i'll go over some of the benefits and that and then that tool is called pdf.callpdf.co.co so you can do this and they do have a free level which is really cool and we'll be able to use that free level today and basically they have a pricing structure it's relatively simple but you can get up to 5 000 credits which could last you a long time maybe for a pdf it's only 20 or 30. so you if you use like let's say they say if you use a non let's see sign up here if we take a look at sign that's let's say sign up here and if we sign up here we're going to get some information here actually i want to sign out and i want to sign up so log out i'm going to sign up and i want to show you just some details that you might want to look at okay so it says here if you use your email address and get 5 000 credits instantly and so credits are really really.
Cool i did speak with them and i had emailed them and i let them know how much i really like their product and i told them i'd be making a video on this and they said okay hey cool here's what we're
Gonna do we will give you i believe it was 125 000 credits for the first three people so i said okay cool you know you're gonna give it to three of mine so here's what i'm gonna do i'm gonna hold a little contest the first three people that comment their email below or you don't just don't need to put your email i can contact you if you want but let me know that you want the free credits from pdf co and then i'll make sure that the first three people who comment below get those 125 000 credits so that's something they offered and i'm happy so basically you want to sign up and you use another email you get up to 5 000 credits if you use a regular email you got 30 credits but.Basically i've already created an account so let's go into that and i'll go ahead and sign on and then we'll get into that and once you get signed up and signed on you'll see a few different things you've got api keys some api logs files document parser that's the one we're going to want to focus on and pdf viewer so there's a lot of features on this they've got a lot of but we're going to focus primarily on the document parser because that's what we want to do we want to bring our documents in and we want to parse the data within that and then we want that data to come into excel so that's the basic idea so i'm going to click on document parser here and once inside the document parser then what we have here is we got some we can click on a new template or manage our existing.
Templates i'm going to click new template okay we can also drag and drop documents pdf from dropbox so it's kind of handy we're going to click on new template and it's going to bring us to the screen we've got this pop-up's going to let us know some information about it so basically i'm going to walk you through that so no reason for me to read that to you the first thing what i want to do is i want to load a test pdf or image so we're going to click on this loader test and as you see we've got a few different ones here i'm going to focus on expensive let's go ahead into something that i'll focus on let's start out with the one of the easy ones which is this rent here and then so i have an idea okay so we've got this rent invoice so we're going to load that up now remember these are pdf or pictures both will work it's a little bit big so i'm going to.
Reduce it down here so we can see it all okay so what do i want let's go back into excel and take a look at what we actually want i'm going to go in here in our data first i want the date i want the i know it's purchased from i want to know the expense type i want to know the receipt number or the reference number the amount and i want to know the file name okay so i want to put all that now the file name that's going to come from somewhere else but at least from the form itself i want these five fields so how are we going to do that well if we take a look inside our pdf.oh.
First thing what you want to do is run template when that's it may or may not be necessary up to you but let's take a look at what it does to see if it finds anything it didn't find any data it's going to run but that's okay because we're going to tell it what data to find okay so the first thing we want to do is we need to get that date right we want that the dates the first column so we want to add an object okay and what we can do is we can add a field from we can add a bunch of things we can add a field mapped from a virtual grid so if we have a grid we can add a single field we can add a field based on a rectangle selection or we can add a table right if you've got an invoice and you've got a lot of items you can actually add all the items from that table with this type of add a table from a rectangle that's pretty cool.
Too we'll show you that briefly a little bit when we get to the invoice then add a field based on the text search so we can i feel this is pretty cool text search bit and we can add a field based on we can add a table based on text search and add a field with a static value okay so let's click on add a field based on a text cert so what i want to do is i want to add a field here and we're going to call it let's call it date okay and then what i want to do is i want to see if we can insert a macro or want to auto detect the expression right we're looking for date so if we click auto detect maybe it's going to detect it for us maybe it'll find it for us if not no problem but we'll go through that okay see it says smart date right let's scroll down here.
Right and it did find something called a smart date so it knows that we want it and we're going to click test the object what i want to do is i want to see did it find the date and it did it found it right here well that's cool but what if it didn't find it what if it didn't find anything well you could just enter that macro we could tell it to look for a date so if i clear that out we can insert that i want to enter a date i want to look for the date there's a few ways to do it so you can try some different ways we're going to go over them smart date can be found right here if we click on that it's going to enter the same thing we found if we test the object and it doesn't find it we'll maybe we can find it another way right so what's another way to do it we can look up i want to look for the word date then i want to look for maybe some empty spaces and then the date so we can also sometimes do something like this so i want to look for date then what's between.
The date well it's probably some empty spaces so let's insert the macro and look for something like spaces but what do we want are we looking for specific number spaces we don't really know so maybe we'll just it's not a single space so maybe we'll click spaces one or more spaces so we can try that and then what we're doing first we're looking for a date maybe another date so we could click on again date again insert macro what are we actually looking for we're looking for the date again if we want to look at a date in a specific format right or we wanted to figure out what format so we can click smart date there and then we can test the object and see if it is found and if it's not found you know we can try a few different options we can all it was found in that case so that's good so it's also found another thing that you can do is sometimes if you're not.
Sure the spaces or you might there might be some characters we can also try something like anything so we can insert anything it means we don't know there's some mixed things in there so we can do this insert any characters up to the next macro in the expression so it's any characters right so we can do that i'm going to copy that i'm going to go back i'm going to paste it right in here i'm just going to see if it did if it also found the date with this so i'm going to show you some different ways and then we're going to test that object too let's see if that date is also found okay good it's also found here okay so we have date but we're just going to contain with smart data smart data is sufficient for us it's smart enough i'm going to test it one more time and we're good to go on the date right so we know we found it so we know it's been found okay so that's.
The date right it's good there i want to insert another one i'm going to scroll up here what comes next next up we want to know what it's purchased from well we know in this case it's automatic this
Is only for a specific receipt so it's i put in mean landlord on my test so we know we purchased it from us so we want to enter a static field in other words this particular template is only for the rent templates so what we want to do is enter a static field it means this is for rent we're creating a template based on red so what we're going to do is we're going to add an object called add a field with a static value so that's what we're going to have here and we're going to call it we can call it anything we call it vendor we can call it name um in fact just make sure you're consistent so we'll call it purchase from in this case so purchase from and then we're going to.Scroll down we're going to give it that specific name i'm going to give it mean landlord so that's a static and obviously we don't have to test that one because that's automatic and then what we're going to do is we're going to add another one and what is the next one that we want to add i want to add or add the expense type and we know it's a rent payment for this one it's always going to be rent payment so again we can add one more static field so add object add field with static object and of course we're going to call this i'll call this expense type right and also what i want to do in this case we're just going to call let's see what have i used before i used rent payments so we'll use that again rent payments we want to be consistent okay so that's it for the static fields.
We've got the date purchased from and expense type now what i want to do is i want to add another object but what is coming up next well now i wanted to find the receipt number or the reference number right i want to put that and on this particular receipt it's located here 1 1 3 4. so again we're just going to click add object now we can do things like map from a virtual grid or add a field from a rectangle selection then you can pick this from a rectangle selection but i've actually had better luck from the page all just basically telling it what to look for from the entire page and having it fine that works pretty well so that's we're going to do add a picture so we're going to add a field based on a text search so the first thing what i want to do is for that.
Number field i'm going to give it a name so we're going to do receipt number that's going to be the name and now we have to tell it what to look for what is it going to look for well the first thing what i would do is i want to do look for number and oh okay that's what i want to look for and then there's a few spaces around it so i'm going to type in double brackets here spaces because it's more than one spaces so you we can find of course this spaces what i'm typing in click insert macro and then what do i want to find then basically i want to find that number so that number of course can be found in here insert macro under number and that's what we're going to find so i'm going to type in number so again here just like that of course you can add it from the macro but we'll do it manually here number okay there so that's it so we're going.
To basically look for the entire document we're looking for no and period then we're looking for the number of spaces and then we want to find out is the number okay so we're going to test that out and see how this look we're going to test the object and see if it's found and it'll take just a moment and then we look down here it was found perfect okay so we have the receipt number now again what i want to look for is the total right the where it says the sum of 1890. so again we're going to add an object we're going to add a field based on a text and in this case we're going to call this total so i'm going to type in total here that's what we're looking for okay so we pretty much want to get it to look for the sum of so we can do the sum of make sure we type it in.
And then probably something like anything right we can do like if we want to type in we don't know exactly what it is we can try that anything right anything anything in the middle right so they have anything any characters up to the next macro and the subscription anything greedy any characters up to the next macro or to the end of the line that's the greedy version okay so let's just go with anything right and then after that what do we want i want the numbers right so we want numbers whatever numbers are after that numbers if you're done not sure which one you can just of course in click insert macro and then just type in numbers here and you can find it right here or number or number just number actually so that's it that's sufficient not numbers so we have that there that should be sufficient for our purposes.
All we need to do is just click test objects and see if it's found and now look it has been found great okay so we have all the fields we want now all we need to do is give it our name we click save template right and it's going to give it a save and we also give our template a name right let's do this so let's just do test test right just and we click save templates so now we've got a name now i've done just that with them but let's look for another template now what i'm going to do is i'm going to i want to know i want another template so i'm going to basically save and return okay we did this test test where we've got tests and now what i want to do is i want to.
Create a new template because i've got a few tests there and i want to see what it's like on another receipt just want to show you one different thing i'm going to load in now this home depot receipt because it has some unique characteristics that i wanted to show you so there's many things that are the same but we'll take a look at this receipt number i want you to see i want you to zero in on this so we've got a total here we've got a receipt number we've got a date and again what you can do is run the template and see if anything is found on here sometimes some things are found in this case nothing was found but that's okay we can do that so again what we want to do is we want to add the date so we're going to add a field based on a text search right but we don't know what date so.
We can look basically based on a smart date right so we can do that so we'll just enter date here in this case date here right and what do we want so we want in this case we don't need an expression here we can get rid of that and we want to look and date actually this is we can remove that so what do we want to add here well what i want to add here is a basically a field based on a text search so that's what i want here so field based on texture so we'll call it date so again again let's see if it finds the date we're going to insert the smart date insert macro date right scroll to the bottom here look for smart date and click in and see if it's finding the date insert the macro that's enough for now and then what we'll do is we'll test the object right you.
Can also do auto detect which could auto detect it well take a look we're looking for this july 6 of 2010 date that's the one i'm looking for i want to see if it's found does it does it find it and it did find it okay perfect but what what's next of course we know that you've got a next field i've shown you this purchase from an expense type we got that but i want to show you the unique characteristics of this one before we move on so it's the receipt and the referred field this particular field has something unique that i wanted to bring to your attention if we take a look at this receipt number and i looked at all the receipts they all start out with four digit numbers then there's some spaces one or two spaces then there's a five digit number then there's more.
Spaces then there's another five digit number so i need to locate that receipt number so what we're going to do is again we're going to add an object and then we're going to add a field based on a text search and then i'm going to call this receipt number here and then what i want to do is i want to give it a specific expression i want to say what are we looking for we're looking at four digits then we're looking at some spaces then five digits then some spaces then five digits so how do we do that well we insert the macro here and i'm going to look for that four digits number so i'm looking for four digits then what's after that some spaces i don't know if it's really one or two spaces so i'm just going to put in spaces here one or more spaces what am i looking for.
After that then i'm looking for the five digit number five digits then what i'm looking at again we're going to add some spaces right we don't know how many digits and there's one more five digit space so i'm going to put in spaces and then i want to find the fi another five digits so just enter another five digits okay so again let's take a look at that we've got four digits we've got spaces we've got five digits we've got spaces we've got five digits and now what we're going to do is we're going to test to see if it finds that number that's the receipt number that i'm looking for based on that formula so it'll go through that and it'll tell us if it's been found it's looking.
Through the entire receipt to find that pattern if that pattern's found it's going to tell us okay and here it is that number that pattern has been found 7076005 and then 6085 perfect so we can use that pattern okay great so let's take a look so now you understand the ideas of how to get these templates so how to create these templates give them names and that's just what i have here let me pull that back i'm going to discard and return this one and we're just going to delete i want to delete the two tests we can delete a template here and i'm going to delete this template and i've got basically three templates and they're all based on some different tests that i've been doing so.
Let's just take a look at the ones i've got three of them here and then based on the three receipts here rent utilities and home depot those are the three receipt types we're working so we're going to take a look just inside each one of them and basically all we're going to need to do is just we have the date a purchase from which is fixed and we have a expense type and receipt and the total so that's what i've done for you if i were to load it you'd find it out so if i reload in let's say the receipt number here let's do that receipt number here and we're going to see that it will load it in here so that's all we need to do if we run that template it's going to find everything else so let's zoom out this way here okay there so now just to check if you want to load in a few different templates and double check to make sure that it is running all you need to do is just click run the template or you can load additional ones like i've got we.
Saw that one already here let's do june and click open right so basically the same thing here just load in your templates and then we want to check for the data so you might want to try this with one or two receipts to make sure that the data is here so let's take a look at this data that's been found if we scroll down here we see the date six one mean landlord those are static one zero three four is the receipt number that's correct eighteen ninety is the amount that is correct okay so we know that that's correct so we got that so we've got the same thing the same thing from the home depot i'm going to open the visual editor here i'm going to do the same thing i'm going to pull in a home depot receipt like this one here and click open and then i'm going to do exactly the same.
Thing i'm just going to click once it gets loaded i'm going to run this template here and so i've created these three templates and each template has a name and it also has a unique id number okay so now we're understanding we're understanding how to extract the data here it's running templates but next up what i want to do is i want to know how we're going to get it into excel so let's take a look at this the results again here we've got the number we've got the total we've got the right date everything looks just the way it is okay so the same thing with the last one which we'll not go over okay so we understand how to create these templates that's the most important thing and let's take a look inside these templates we've got three different templates each template has a name and each template has an id keep an eye on this id the rent test 1655 the utilities test 1653.
And the home depot 1649. so we understand how to create these templates how to parse the data how to extract the data but we really don't understand how we are going to get these receipts get into into this right we can't we don't want to manually upload these things right every time we have a receipt we're certainly not going to go through this and extract the data what we need is some automation right what i want is i want to put that receipt in a folder and i want that data to come to excel it's that simple that's what i want i want to put that receipt in the folder and i want all the data to come into excel i don't want to do anything else i'm really lazy and that's.
What i want to happen so how we can do that well we use automation to do that right when we talk about automation nothing is better than integromat we've used it before and it's going to help us out tremendously because integromat can tie everything together and do that integromat combined with these third-party apis and applications combined with excel it's extremely powerful we've used it before we're going to use it again today and that's integral matters this one right here and basically if you don't have intake or mat you might want to get signed up let's pull that up there so you can see it if you don't you may want to get set up you can have up to 1 000 automations.
Per month so it's really cool and that's for absolutely free integromat i'm gonna include the link down below go ahead and click the link down below they'll get you set up with some extra free automations if it's coming from me and that's gonna help you out all right so take a look at this and take a minute you've got it once you get signed up it's free you'll get into a dashboard like this if you have any scenarios and scenarios are basically these automations there we go and i've already created some and i'm going to walk you through that and we're going to create one that is uh automatically so how we can do that we're going to call it picture these are the three that i've created for tests and basically what we're going to do so how would we do it from the beginning because that's what i want to show you so we're going to click create a new scenario and.
Basically what do we want to happen well the first thing we want to have is remember when we put a file in a folder that's the start right that's the action start when i take a file from here and i drop it in one of these folders i want something to happen okay so and also remember from our phone right from your phone you can have dropbox on your phone you can take a picture of a receipt you can save it to that folder it's going to be exactly the same action as you would be dragging and dropping right so it's the same action so that's what i want when we make a change.
When there's a brand new file in that folder that's when i want the automation to start so let's start right from the beginning we're going to click on that we're going to look for dropbox it's dropbox that we're going to be using so click on the dropbox and it's going to ask you to connect your account so you'll connect you'll get this pop-up it's going to connect your account once you have a dropbox account so you get that connected up and basically what i want to do is we have to have that trigger what is that trigger we're going to watch these files that's the trigger triggers when a file is added to a folder being watched that's what i want right i want that that's the trigger we're going to use okay so we're going to start out with that what is the folder here's where you're going to click add your account you're going to click here.
You're going to create an account it's going to give you a pop-up right it's going to say what do you want your account like just something like this continue you're going to get this pop-up but i've already done it already so there's no reason to go into that so we can cancel i've already got it right here you've got multiple dropbox so i want to choose a folder what folder am i going to choose well i've got a dedicated folder called expenses so i'm just going to go down here for that folder it's called expenses okay but not any folder right i want to create an automation for a specific i've got three different folders let's focus on that home depot right clicking on home depot when i make a change to that folder that's what i want i want to set a limit i only want one file at a time so that's going to trigger it that's the first thing.
What we want to do okay so i'm going to move that over to the left and then just going to click ok now what do we want it from now on yeah i don't want it to do it in the past you might want to do all specific folder all the files in the folder if you have a lot of them right let's say you have receipts in a folder you already want to map you can do from a specific date or you can do all but we'll do it from now on i'm going to click ok now what i want to happen i need to upload this file to this to this docupar so right basically i need to upload it but how am i going to upload that receipt to docuparcel well i need to get a link right this can read a link it can download it what.
Do i mean by link well if i were to right click on here right and we see the option is we can have one called share or we have actually called copy dropbox link and what that's going to do is going to provide a download link well i want to do the same thing but i want to do it inside the api so i'm going to hit this plus and i'm going to click dropbox so i want to add something more i want to create a downloadable link and i want dropbox to do that for us so how are we going to do that create or update a shared link this is the option i want now we click on here well first of all how are we going to select our files well it's going to be based on map the folder what are we mapping it to well it's going to come directly from this file right from the watch files right we added a.
File now what i want to do is i want to get a link based on the file that we just added i don't want any file i wanted the specific file that we just put in so how do we want that well so i'm going to look in that watch files and i'm going to look for something called the download link so if i look inside here you see that we have the option of download link but to get that downloaded we need to have the original path right so how do we're going to collect this lower path that's going to create that lower path this connection is going to create that link for us so path display that's the path of the file that we just added okay so now we have some information we want public an expiration.
Date which we don't need so we don't need the leaks access level we don't need that as well so we're good to go so basically we're going to take the path that's the full path of the file that we just added we're going to create a link from that okay this module is going to create that link automatically clicking ok so now we have the two dropbox we've added the file of the folder this is going to create that downloadable link now what we can do is we can upload that link in to our pdf called parser so we can click on here now what i want to do is i want to add in that pdf right pdf.co that's the bottom one here dot co that's what i want to add and what do i want to do.
I don't want to convert into a pdf i want to parse the pdf right so how we get it what's the difference what are we going to use well we're going to look something called your parsed document use pre-built extraction templates which we just created to automatically read invoices reports order statements and other documents that's what i want parsing a document so i'm going to click here so the first thing what you want to do is you want to add i've already added mine but you'll add it here it's going to add let's just call this test and it's going to add for your api you need that api so where are we going to find that we're going to go back into here scrolling up and we're going to look in api and view your api key here and i of course i can regenerate this so.
I don't care if you see my ampy i keep just going to copy this here i'm going to go back in here i'm going to paste it directly in here i'm going to click continue so that's going to create a test so that automatically added it we'll go back to the original one okay they're both the same now so then what do i want to do i want to input i want to upload a file i want to import a file from a url or importing a file right this is generating that url what is that url now remember we're looking for this module which created a short link this is where we're going to find that download link that download link is going to come directly from looking for something called download url this one right click download the url so that's going to upload it okay template id now.
What is the template remember we're in home depot right we're going to crew we're watching that home depot file so we need to find that home depot one so that home depot template idea is 1649 1649. that's the template id that i want to put in right here 1649 okay i want to put the output as a json format that's what we want json or json however you pronounce it profiles we don't need to adjust the specific profile we want a json output that's perfect okay good so we've got that clicking okay so now all that's going to do so what do i want to do now what i want to bring it back into a data file right so the first thing what i want to do is i want to go back into dropbox i want this is going to take care of the parsing for us but what i want to do now is i want to get that data and.