How To Create A Fully Automated Expense Tracker With PDF & Image Parsing & OCR In Excel + Download

hello this is randy with excel for freelancers and welcome to the pdf and image parsing training in this episode i'm going to show you how you can automatically take pictures of any types of receipt parse the data inside their seat including date total receipt number and a host more automatically have that data come into excel and presented in a beautiful format with nothing more than just a little bit of code and some automation it's going to be an incredible training i cannot wait so let's get started 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 which we're going to go over okay so just a little bit of information about this workbook and what we've got going on we only have three main folders i've got an expense data folder this is where all of our data is going to go so basically we want a folder like this best thing to do is set up a dropbox account i've got my dropbox account dropbox is free up to two gigabytes and you're probably not going to use that dropbox if you don't have it yet it's also available of course that's an app on your phone whether you're for android or apple phone or whatever you want or on your computer so i've got that here i've got a dropbox account and dropbox of course everything we put in that folder gets backed up online so it syncs all of our devices very cool so basically 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 i want to put it in a txt file i want to get the data and i'll put it in a txt file and i want to look something like this so i wanted to make it very very clear so i want the date easy to read by excel i want it separated by an asterisk then i want to have the vendor or the information the name right then i want to have in this case the type the expense type then i want to have in this case the reference number or receipt number then i want to have the amount then i want to put the file name right it's the same exact order as we see on here the date the purchase from expense everything in this exact order because then it's going to be very easy to put it inside excel so i want all that information and i want to bring that in to a txt file so i want that txt file i want that to be created by dropbox and i want to bring it all in here so we see we have a few in here that's what i want so how are we going to do that well we can do that again with creating a txt file so let's click on dropbox here and this time i want to upload a file and i want to choose the folder where do i want that to go it'll connect your dropbox it's already connected right i'm going to select again expenses here and then i want to put it in a specific folder called expense data so i'm going to select on that and i don't want the entire document i want to map a source folder right so what do i want to call it well i want to give it that name i'm going to use this name here and i want to make sure it is in a txt format so i'm going to do txt what kind of data do i want in here well the data again is going to match that map date purchase from expense type all of those fields so let's put that inside there let's get that data in there and then we'll put a delimiter in there so if we want to find the data once you've run the scenario once it should appear here it should appear here once we've run it at least once so you may want to run this scenario by making a change by simply putting a file in that folder it will make that change and it's going to go ahead and get that date so the first thing what i want to do is look into objects this might be close so look at objects value and then we're going to click on date i want that date but i want to separate it i'm going to separate it with an asterisk and the next field that i want to put in if we take a look at this we want to put purchase from and then expense type so that's just what we're going to do we're going to put inside the value here we're going to put in purchase from then we're going to separate it we'll scroll down here a little separate that by an asterisk and then we're going to put in expense type okay then right after that we've got a few more fields to put in we also want to put in our receipt or reference number and then the amount so we're going to put those in right there so then we're going to put in here we see in the values receipt number here another asterisk then the total that's our amount then what else do we want to put in then i want to put in one more field i want to put in the name of that file where are we going to get that name of the file well that's going to come inside dropbox so we can put that in here it is right here if we take a look inside we want this it's called this name here that's the one we want or we can use a dropbox let's take a look at this dropbox home i want to put the name of that file because we're going to need to put it in so there that's the dropbox name it's that original name of that jpg file that picture file i want to put that picture file right in here for example here putting that name is going to come that's going to come from dropbox it'll come from dropbox right here to upload a link it's also available here in the folder here so it's also available in the watch files too but we've got the data available right here in name okay so that's it so i'm going to click ok all right good so now what i've done but what i also want to do one more thing once i put that information in expense date i want to come into json.txt document i also want to do one thing i want to take whatever that receipt is here that receipt and we've already run it i want to take it and i want to place it directly in our archived receipts right here in archive receipts so how do we do that well we can do that with one more little component here on dropbox so we're going to click here dropbox and this time what we're going to do is we're going to move a file so we look move a file or folder so what do we want to move well i want to certainly i want to map i want to map it right so what is that original file where do i want to do it it's that original file name and we can scroll down and refine it right we look when we're uploading a file or are we going to use our creator share link so we've got different dropbox or we're going to watch files what we want to do is we want to pull in this file creator subject where is that original file located well i'm looking for that original file right that's where we have the watch files right watch files it is this right here right we want that path that lower path that's our path that lower path is over that original location and where do we want to place it we're going to click on here and again i want to put it inside the expenses and i want to put it in my archived receipt so i'm going to click here i want to put it in archive receipts and i don't necessarily need to give it a new name but i do want to make sure that we're going to auto rename if it's a duplicate i want to auto rename it i'm going to click ok all right great so that's it so again let's review we're going to watch files in a specific folder if there's files that are changed we're going to upload we're going to create a shared link for that folder we're going to use that shared link in our pdf we're going to parse that document right just as based on our template of 1649 right we're going to put it in a json format i also want to put it there and also i just added one generate csv csv headers false generate csv this may helpful i've it's worked both ways with and without but i want to put this in there bring this to your attention that there was a i had a few issues so this could be helpful generate csv headers false we don't need the headers in there don't need those i just need the data so basically we don't want the csv headers and then the json output okay we're going to take that information we're going to bring it inside here and we're going to bring it into a txt file creating a brand new file and what we want the contents of that file we want something like this the date purchase from expense type the receipt number the total and the name all separated by asterisks and click ok and then dropbox then we have dropbox we're going to simply move that receipt to an archive folder because that folder where we want all the receipts to go to we're going to save our work we're going to turn this scenario on make sure it's on now it's ready for go okay so now what we need to do is all we need to do is drag and drop a specific file into that so we go to our expenses here and let's see they're already located i'm just going to bring them out of my put them in my expenses and then i'm going to bring it directly in and i'm going to make sure that we have no data so there's nothing else in here i'm going to clear out all the data here deleting that data and now i'm just going to take one simple receipt and i'm going to bring it directly inside home depot okay great now if we take a look inside expense data and taking a look inside our expense data here we see we've got that inside of txt file if we double click on that and we take a look at it say we've got the date we've got the name we've got the expense type we have our receipt number here we've got the amount and we've got the picture file and we'll take a look inside here in the expenses file now in our archive receipts we should have that home depot and here it is right here it's been now moved to our archive sheet so everything we're good if you want to see inside our diagram here's the history of it we take a look at the history we can see all the information we click on the details here and we see that everything went through here so we can see the information here we have initial operation and we can go through every step and see exactly what happened here everything got generated and we have the information that got brought in here so everything everything happened all right looking good okay so now we know exactly and basically all we need to do is create a different scenario for each one of these now you like this but you got to create it all again for each one well it's really easy all you need to do is click options and then clone it once you clone it you can create all you need to do is change a few things all you need to do in the clone one of course change the folder here and then all you need to do again is change the document parser template here so remember you've got different ones here let's take it here we've got 1653 for whatever you want just change the id that's all you need to do oh and one other thing and make sure you change the name i've updated i created a name here change the name here so then in your scenarios you've got three different ones and that's all i did so you would do that for each one of your receipts and you only need to do it once so when you look here in a folder i've got here this was just a test that we did so we can remove this and then all right there was an error so i fixed that that's it there's an air it'll tell you here now everything's fixed that we just had to duplicate one of the errors that has let me just go in through that i want to edit this one of the errors i had i had a duplicate receipt number so i want to make sure that when we create those here show advanced settings make sure you ought to rename it because that did it'll tell you what the problem was though it'll always tell you if there's a receipt so make sure and then save any of your changes so we just want to make sure that we're always uh you know in case there was a duplicate because i'm using the same receipts over and over and over again so that's part of the problem why okay great so we've got that so now we know how to create scenarios we know how to parse our documents we know how to get it into the folder but how once we get this data here how do we get it into excel okay well that's what i'm going to go over right now okay so inside excel here so basically what i want to do is i want to every time there's a new data in the folder i want to add a brand new row here so we're going to go over the macro that just does exactly that okay so inside our dashboard here we've got a dash expense data here and inside our vba of course developer and visual basic or alt f11 will get you there and i've just got three macros it's relatively simple so one's going to be called the module called expense data and macros okay and expense check and add okay expense checking out that's the macro that we're going to run through so we need to know the expense row we're going to put that into a variable long i'm also going to loop through all the columns so i'm going to loop through column 1 all the way to the last column right so we need to we need to keep track of what column we're on so we need that in a long variable and i want to know how many expenses have been added so i'm going to keep track of those i need to know the data file path what is the file path and the folder where the data is i also want to know the archive path right because once i get through with a receipt right once i'm done i want to put all those inside our archive data i want to have it here that data so in case we need to go back we can refer back to the data so all that data is going to go in here okay so i want to put it there and how are we going to know what folder goes where well we've got it here we've got our expenses data where our expense is going to go we have our archive data and we have our receipt archive folder so we're going to do that so we've also got all these in a string variable and also we're going to need an array we're going to need to loop through all the expenses in that txt file so we need to use an array to create that and we have expense data as a string okay we need to found expense we don't need that phone that one we don't need so data folder that's going to be our admin d3 we need to get that into a variable so that's the first thing we're going to do inside d3 i need to know where we're going to get those expenses from where's it going to come from i've got to know the folder path of this file this expense data and we've just taken this folder path here and all i've done is place it directly inside d3 so we're going to place that inside a variable called the data folder okay i'm also going to add a backslash on the end of that that's very important i want to check that to make sure it exists so to do that we need to check on that and so we need to just make sure that it exists and make sure that it's an accurate folder to do that all we're going to do is just if the directory of the data folder vb directory if it equals blank or if the data folder itself is blank then we need to let the user know with a message box please select the correct dropbox folder for the expense data folder we're going to do the exact same thing for the archive but this case is located in d4 adding that backsplash and just double checking to make sure that that is correct using vb directory if not letting the user know it exiting the sub okay assuming that our folder structures are correct what i want to do is i'm going to basically loop through all the files in the folder and to do that all we need to do is just get the file name what is the file name well basically what i want to know is any single file in that specific folder that ends in txt so to do that it's going to be file name is going to be the directory of the data folder and asterisk.tst that means for any file that ends in txt any file that instant and we're going to do something called do while we're going to run a loop called do while we're going to do while the length of the file name is greater than zero as soon as it's not we can stop the loop okay so the first thing what i want to do is i want to get that full path of that file now full path we're going to need to open it in a txt file so the data file path is going to be the data folder and the file name right this is only the file name here just the file name itself so when i combine the folder and the file name i get a full file path and i want to open that file as a txt now this happens very very fast you don't even see it right open the data file for input as number one okay we just want it and then what we want line input one expense data so basically everything on that line i'm going to put inside a variable string very book called expense data that's all i want to do is take all the contents of that txt file right here taking all of that and basically putting it into a string variable what i'm going to do then now is i'm going to split it based on this asterisk this is going to be our delimiter it's going to help me separate the data so that i can put that individual data in the cells so we're going to do that we're going to create an array i'm going to put that split data in an expense array we're splitting that data here's the string based on that delimiter that asterisk okay now once we have it then what i want to do is i want to determine what row are we going to place it i want the first available row based on the expense database the first available row is the last row with data plus one that's the first available row first available row once we have that we're going to run a loop from column one to six why are we running a loop from one column starting in column one i want to go all the way to column six equals column this is column six okay so i want to run so basically we're gonna loop and we're gonna place that data in here and the last thing is i want just in case i'm gonna put that row although we're not using in this specific training i want to put a formula row and allows me that could help us and the reason is once we go here we might want to make changes if we want to make an update we could do that if we have the row so that's why i added it just for the future so or perhaps i'll do that in our patreon account if we want to make a change to here how could we make a change and save those changes so that's something good for patreon if you'd like to see that i'll put that in our patreon all right so what we're going to do is we're going to loop through that now all we have is our we have our columns one through six we have a row it's based on there so dot cells because we're already in expense database equals what expense array and the expense column minus one why are we subtracting one when it comes to an array the first value is zero so it's going to be in this case expense array zero so the first value that date value is zero so expense column if our first column is one minus one is zero so our first value inside an array is always on zero so we're going to add that date we're going to loop through every single based on that delimiter so first is going to be date then two are going to be our vendor name then purchase from then our expense type here then we're going to have our receipt number then our value then our picture we're going to put all that inside there once it's done once we've added all that data last thing i want to in column g i want to add that row formula you saw that row format on column g i want to add that okay that's it so all we need to do is clear out that file name by using directory file name right that clears out the current file name and then all i want to do is then i want to copy that file in other words i want to copy you can copy that file here if we uncomment that out it's going to copy that to the data file path i want to copy that take that receipt and copy there but we now we're using excel if but now we don't need to do this step because why because actually integra mat is doing it for us right where copy integramat takes that file we can get rid of that right i'll keep it there but i'll comment it out but there were some issues but we don't need to copy it why don't we need to copy it because we're doing it right here i'm already taking the receipt in this step and i'm moving it over to the receipt file so we don't need to have vba do it for us because it integrates going to be doing that okay i'm going to run that count i want to keep a count of all of the expenses that we're adding so this variable expense will do that and we're just going to kill that's why we can delete that file because integromat's already moved it over for us so that's it now we just run that loop and then what i want to do is i want to run a macro to refresh the dashboard and then and basically that macro which i'll go over is simply going to refresh all the data great so now that i have that now we understand how to add it up now what we would do is we want to take this macro here copy that and i want to run that on a workbook open event when the workbook opens i want to run it so i click on here on workbook and i want to click open and i'm just going to paste that here i'm going to comment it out because when you get this file i don't want it to run it but this is the macro that's simply going to run when the workbook opens so that means any new expenses are automatically of course we can also add it to a button here on our screen we could just say refresh data we could add it anywhere on here into a button so that would not be a problem here so we could do something like this insert shapes here insert shapes and we could do something like check for new data check for new expenses something like that new expenses and we'll give it something a little bit closer to our theme color here under the format and we're using this theme here and i'll do something like this okay that's going to be better here and then we can make it a little bit bigger and bolder here so let's take a look inside here and then we'll bold it make it center bring this down a little bit while i'm formatting okay so and then all up so we're going to check for new expenses there we go that's nice and then so what we do is just right click in here and then i'll right click here and then assign that macro here and they're just going to assign that macro only three macros in this workbook and then expenses check and add click ok so now if we do that simply going to check and automatically three expenses have been added perfect so it works great and then they're automatically added okay very very good i like that now let's we can change the date so now we've got just two other macros that i wanted to go over with you we've got some a little bit of data here some very cool and we got some named ranges to make this really work quick and i want to be able to show you how we're going to show these receipts too that's what i'm going to go over now let's show a little bit more data here i'll change it the month 5 1 and that'll show a little bit more data in here okay great so we've now we know how to do that but we also see that we loaded in that brand new data also came in here so how are we going to bring this data over and bring it into this dashboard we'll take a look in the macro here now that's under the dashboard macros here so the first thing what i want to do is something called expenses filter and load that is the macro that's automatically going to run when i make a change to f13 or i make a change to h13 or it's also the macro that's going to run at the end of this remember that is the same macro that we run here called expenses filter and load when we just add that macro so that macro is a relatively simple macro so the first thing what i want to do is that macro is going to basically bring all the over data but it's going to be based on these dates all the expenses between these two dates so we're going to run that through an advanced filter that advanced filter is going to be right here that's the criteria for that so basically what i'm going to do is i want it greater than or equal in fact the dashboard f13 in fact i created some named ranges which we can use if we take a look at this click on here we see that's the from date and we take a look at here we see that's the two date so we can also add those in just like this and from date right just from date just like that or and then enter an or to date now we want to see those numbers we don't necessarily want to see a date on that because the numbers are better because any format will work regardless of what your date format is so we want to see those numbers so it's going to be greater than or equal to from date and less than or equal to date that's the criteria that we're going to be running this through we're going to determine the last row of the data here in this case 43 we're going to run it through our criteria here we're going to have those results appear here once the results appear i want to bring in all those data and i want to put it in right here and i also have it in a hidden column notice there i do have the row numbers here if i were to change the font color on these we can see that we also have the row this could help us if i want to make a change i know automatically what road to place it in so great so we have all that that's good no receipts there to put in so how do we do that so let's take a look at the macro it's very simple so first thing what i want to do is i want to clear the contents clearing b4 to d16 why b4 b4 is our selected row when i select a row we're going to use conditional formatting and b4 is going to keep track of that and it's going to be right here in 19. so notice when i check on that selection change event is going to change that so i want to clear out the selected row and i also want to clear all the data all the way from d16 all the way through j and all the way down so that's we're going to d16 through j and all the way down okay so determine the last row based on our expense dave the last row we're going to get that into a variable if it is less than 3 we're going to certainly exit out of that sub and then of course i want to delete the criteria why do i want to do that well i want to make sure that i also want to get those unique cells so not only do i want to do that but i also want to get the unique expense types based on all the expense types here i want to get the unique that's going to be super helpful because i want to create a graph this graph here based on those expense types to do that i want the unique expense type so not only do i want to get that data i want to get unique ones but when we get unique we can't we're not using any criteria all i'm going to do is take all these expense types and get the unique ones through an advanced filter but that won't be any just the unique values but it won't contain a criteria so to make sure that we don't have any issues on that because there already is a criteria created on this sheet here i want to delete the criteria if it exists if it doesn't exist it would create an error therefore i'm wrapping it in on air resume next and on air go to 0. so we're going to delete that criteria i'm going to run an advanced filter notice only on column c only on that expense type starting in c2 and all the way down so c2 through c in the last row advanced filter there's no criteria no criteria here copying it to range k2 what that's going to do is going to give me some results k2 is going to be those that only the list of four unique expense types those expense types are very helpful coming up a little bit later on once i have that i'm ready to run my main advanced filter that is the advanced filter that's going to get us these results based on the criteria here so again this time we're going to go all the way through g and our criteria is going to be m2 through n3 so all the way through g based on make sure you include your header row to our criteria m2 through m3 we want those results to come in p2 through v2 p2 here all the way through v then once i got the results here i'm going to get the last row based on what's called p if it's less than three then we're going to exit out but as soon as we have data all i need to do is just bring all this data over and bring it directly inside here starting in d16 so that's just what i do with the rest of the macro so determining the last row based on column p if it's less than three exit the sum and then all we can do is bring in d16 through j and the last row plus 13 why is that important because our original data starts on row 16 here while our original data here starts on row 3 so we need to compensate 13 rows to make up the difference so now that we're bringing data inside here i now have the ability to bring it all the way over that's it so that one single macro does all of that okay very very good next up i want to display the picture right let's take a look at that if i display this picture i want to display a picture here let's bring this back up here i want to display the picture of the receipt and even if it is a pdf i want to display it so how can we do that well very cool with just this one little macro is going to do that first thing i want to do is i want to take that file name put it in a string and i want to put the receipt folder in a string and the picture path right what i simply need to do is i know where those receipts are they're located here in this folder and if i know the folder and i know the name of the receipt the name is located in column i if i combine the folder combine the name then i know the entire the correct file path of that picture so we're going to need that and i also want to know the file type is it a picture or is it a pdf because we need to do something a little bit different if it is we're going to focus on the dashboard okay what i want to do is if if i'm bringing a new one in when i select something that's when i want this macro to run when do i want this macro display receipt picture to run i want it to run when i select something within a table so we go into the dashboard here selection change this is what i want if the user makes a selection anywhere from d16 through 199 and d in the target row doesn't equal empty right i want to make sure that there's actually a value here right they select on here nothing's going to happen so i want to do two things i want to take whatever row they've selected i'm going to put it in b4 next thing what i want to do is i want to make sure that we run that macro so b4 is equal to the target rule and then we're going to run this macro it is that macro that we're going to go over right now okay so inside that macro here we're going to delete the picture if there's an existing picture it's always going to be called rec pick receipt picture we're going to delete it if it doesn't exist it would create an error so we want to wrap that around okay and i want to get that folder into a variable located in d5 remember that's on the admin screen okay that folder doesn't contain a backslash so i want to make sure to add it inside our string then all i need to do is simply combine it i do want to check to make sure that that folder is an accurate path and we can do that using vb directory or if it's empty we're just going to make sure that there's a proper correct receipt folder in the archive admin screen okay and then exit out okay if b4 value b4 is our row b4 is very important if there's nothing here we need to know why is b4 value because i need to pull that picture name that picture name is going to come from i and whatever is in b4 i18 in this case so we will need to make sure if b4 is empty then we can exit out of the sub the expense row is located in b4 and the file name is located i and the expense row that's the file name so now we have the file name right so all we need to do is combine the file name and the receipt folder for the full file path but i want to know inside that file name does it contain dot pdf because if it contains dot i want to display the pdf right i want to display a thumbnail i freeze them let's call it let me unfreeze those because it's a little bit hard so you can see but you can do you can do that and you want okay so let's just unfreeze those and now we can scroll up a little bit easier for us but either way so what i want to do is i want to show those pdf or pictures so i need to know does this file name contains dot pdf so we can use the in string command to check for that if in string of the file name dot pdf is greater than zero that means if the file type is equal to pdf otherwise the file type is picture now i can combine those two the folder and the name of course we can actually we can put this to file name because we already did put that inside a variable here file name okay that's the full picture path there okay we have that there and now once we have that we want to make sure that it is accurate again we're going to use the vb directory if the directory picture path v directory vb directory or the picture path equals empty then exit the sub if the file type equals picture then do this right we've already set the file type for this if the file then what i want to do is i want to insert a picture dot pictures insert based on that picture path name is going to give it the name for example but what if it's a pdf on pdf i want to do something slightly different o l e objects dot add file name equals picture path so basically we're adding an object we don't want to link to that object i don't want to just i don't want to display it as an icon i want to display it as a full picture and i want to give it the name now that name is going to be exactly the same as the name that i had given it if it was a picture that makes it very easy when we need to delete it because we know the name okay so that pdf's gonna have the same picture everything else is exactly the same regardless if it is a picture or pdf with the shapes uh receipt picture the width we're giving it a width of 250. i want to place it the left basically i will need to call out that sheet name again because we're inside the width here j and the expense row plus left plus 10 right just a little bit over on the right of that of column j and then also top dashboard this could be j or i wouldn't matter j and expense dot top so i want to put it on the top of whatever the row they've selected right here great that's all we need to do very very easy that's all we need to do is simply display that picture right here super simple okay fantastic so those are only the three macros everything else is pretty much in the dashboard let's just go over some of these real cool dashboards so basically what i want to know is i want to know some totals well the best way to use totals we create some named ranges so inside the formula i've done just that let's select out of that and then formulas and then name manager and take a look at some of it here i've got expense amount using the offset formula using all the expense amount it's an offset formula dynamic means it's going to grow as the data grows in the offset the expense date here located here that's the base again based on the expense date i've got the expense type here right here and that's pretty much it that's all we needed those three for the formulas right we have an expense list expense type data a few other things but basically that's it for now okay so we have those three so now what i want to do is i've created a month i've got a list of months here i want to create this particular graph so to do that we need to get some information in and i want to base it on using some if so i want to get all of the summits based on if it is inside this date or not right i want to know how much for january so how do i know how the totals are for january we can use sum if so that we're going to use the expense amount because that's the amount that we're summing we're going to use the expense date right we've just created that must be greater than or equal to the year of the from date right remember that firm date the row this the row then i need the month right we're using the date right so the first thing is the year the second thing is the month here so how do i know the month of january well it's based on the row row eight minus one right so that way i can simply copy down this formula it makes it a lot easier so the row 8 currently minus 7 is equal to 1. and then i want the first day row so it has to be greater than or equal to january 1st in this case and also i want this expense date less than or equal the last day of the month of january to get that we're going to use the end of month features so in this case eo month end of month again with the same date the same year the same month and the same day but this time we're using eo month i want the end of the month and then zero means that that's the exact month not the month before or not the month after so that is going to get us the totals for january if i copy that formula down just like this it's going to be actually for all the months very simple now i've created just a simple bar chart here and when i want to select the data and click select data right and we click edit we see that the series here the dashboard values are based on the amounts of those okay it's simply just based on the amounts and also we have the edit we want the months we have based on the months here and that's our access labels and then all i've done is just give it some colors and looks we won't go over in too detail okay great so we understand basically for the most part how to create it is simple it's not a complex now we got a little bit of a pie chart here just something basic and here what i've done is i've created basically a link to the unique now i've created this notice that this is a link to k3 if we look back on the expense data we see it's a link to k3 so as our unique expense types are built all the way down here so is our links here and now what i've created a formula now what i want to do is i want to create a formula based on those dates but based on only this specific expense to do that again we're using the sumifs we're summing the amounts the expense type is going to be equal to a21 okay basically the left the expense date must be greater than or equal to from date and the expense date must be less than or equal to due date that's going to give us all of the expenses based on this so i'm just going to bring that forward here bringing it all the way down and all you do is bring this down then all i've done is just created a simple pie chart based on that so when we could select data we see and we edit the series here we see that it's based on the values of b21 to b23 actually just create that i'll create that more obviously we need to go down further but we can create you know as much as we want we'll just do a simple just for our purposes now just do 30. okay and we just simply that but basically what we want to do if we don't want to see this all we need to do is just create we can use a named range too that's another way to do it okay so keep that in mind that if we want to do that we want to edit it we can do that based on that named range so let's take a look inside that named range here and then we enter the formulas and we let's get out of that cell there and then go into the formulas name manager and we have here inside our expense type data here we have that data here in an offset right offset dashboards21 right our data is now in automatically offset okay so we can keep that here based on this offset we're going to use two columns in this okay so we want to create a new one so for example insert a pie chart if i want to create a pie chart here and i want to select the data all i need to do is just add in that data there select the data and all we just need to do is paste that data in here and then boom where we have it right there everything's set there and while we need to just refresh the data we can use vba to do that a few other ways that's how we get our data in here very very very cool all right it's been a great great training we have learned a lot this week we have learned how to parse data in pdf and images how to simply drag those pictures of those pdfs into a folder automatically extract that data using parsing and integramatt to create scenarios where we can automatically get that data into excel and have it all brought in in just a single click it's been an incredible training i hope you do appreciate this all i ask from you is that if you could just simply subscribe to our channel click the notification icon bell that's going to help us out also i've got 200 of the best workbooks for just 77 i'll include the link down below that'll really help us out all right thank you much until next week we'll see you again thanks so much [Music]

Channel: Excel For Freelancers

Views: 36,261

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, PDF To Excel, Convert PDF To Excel, PDF Parsing Excel, Excel PDF Parsing, Excel Expense Tracking, Track Expenses In Excel, Excel Expense Tracker, Picture OCR Excel, Parsing Pictures in Excel, Parsing Data in PDF, Receipt Parsing, Receipt Parsing Excel, PDF Excel Conversion, PDF

Id: ZWMzgwQqr-A

Length: 68min 37sec (4117 seconds)

Published: Tue Oct 12 2021