How To Create A Personal Financial Planner In Excel [FREE Download + Masterclass]

hello this is randy with excel for freelancers  and welcome to the personal financial planner in   this training i'm going to show you how to create  this incredible personal financial planner along   with automated financial entries dynamic admin  screen and a comprehensive dashboard showing   income spending and network it's a training  you won't want to miss so let's get started all right thanks so much for joining me the  personal financial planner this is gonna be   a really incredible training we're gonna cover  spending income net worth and most importantly   how do we enter those transactions in fact we can  enter expenses income payments and transfers we   can also enter multiple entries even with just  a click of a button we can automatically enter   unlimited types of entries for the future  and of course we've got a dynamic admin   screen where we can enter our own income  expense accounts along with initial balances   we can even set some monthly and overall goals so  it's going to be great and then this dashboard is   going to tie it all together with our net worth  planner we're going to have income here spending   and of course we will also have a great timeline  where we can click and automatically adjust   the graphs and charts here we're going to show  you that we're going to create this really cool   menu and i'm going to show you a whole lot more  so i hope you'll stick with us it's going to be a   great training i do bring these to you each and  every week and i hope you do appreciate them i   put a lot of time and effort in because i want to  make you not only great with excel but successful   with excel and that is my goal here all you need  to do of course is just click on the subscription   below in youtube and of course don't forget to set  the notification icon bell that'll ensure that you   get these trainings and alerts sent to you every  single tuesday when i create these it's absolutely   free if you want to download this you can using  the links down in the description however if you   do want to support the channel there are  so many ways to do that first of which is   by grabbing my 250 template pack that's 250 of my  best templates and it's got one low price in fact   i'm going to put in a mini accounting application  for no additional cost and i'll have that for   those who view who want to purchase the full 250  with pdf another great way is our patreon platform   each and every week i take these templates  then i take on your suggestions your ideas   your comments and i add on to that i create  an additional training and an updated workbook   and make that available to you based on what you  want so whether it's a feature you want you want   to fix something or maybe you want me to focus on  something also available on our patreon platform   is unreleased features and trainings we've got pdf  code books downloadable early bird discounts we've   got also got supporting trading resources like the  icons that are used in this pictures of course so   you can quickly assemble your own workbooks and of  course full video downloads that way you can watch   these trainings anywhere anytime even without  internet full video downloads and of course   members only discount that's only available on our  patreon platform i'll include the links down below   and i'd appreciate your support for just a few  dollars a month all right thanks let's get started   on this so basically we're going to break this up  into three major components and the first of which   is to be able to enter data we want to quickly  and easily enter data whether we're entering   expenses income or payments we want to be able to  quickly add an entry save an entry and have that   entry updated or saved or new entry so we want  to be able to enter expenses income payments or   transfers very quickly and very easily we also  want to be able to access prior payments we want   to maybe show only income or maybe we only want to  show income or expense or payments so we want to   quickly be able to get to those previously entered  transactions and have them display so that's one   point the next major component is we want to be  able to work with the data and calculate the data   so we've got all of our data but we need to  be able to calculate it right we need to to   present that in a way but before we can do that  we need to do that we'll be doing that with some   pivots here we've got some calculations we'll be  focused on the net worth how do we calculate that   to get the total uh available using all those  transactions what's the current balance of each   individual account what are the total expenses  and the total income based on the data entered   so working with that data is the second component  and then lastly we're going to focus on displaying   that data we want to display it in a very  user-friendly way so users can quickly see where   they are in their financial plans how far they've  reached have they reached their goals yet if not   how far their way and what is their breakdown  right and we wanted to be able to present it   in a very easy and familiar manner just in this  three tab so they can see a lot of data in a very   short period of time and it's easy to consume and  they understand everything as far as their monthly   income and expenses and then of course their net  worth at a glance they can do that so those are   the three areas we're going to be focused on and  the first of which is the financial entry so how   do we create this financial entry we got also  got a menu here we can click from the dashboard   the entries and the admin that's a very simple one  line macro and i'll be showing you how to do that   one single line of code to do this so we're going  to be going into that so let's go over this entry   how do we arrive that we've got entries by type  right we can have different types of entries this   is our entry type we've got four different types  of entry income expense payments and transfers   right so what i want to be able to do is i want to  be able to simply click an expense or simply click   an income i want to be able to locate those very  very easily what income types are they now those   income types all originate from the admin screen  so inside the admin screen we have four different   income types right we have paycheck bonus workbook  and you can add anything you want and of course   we've got some additional expenses we can add  that i've also got some accounts right what kind   of bank or asset accounts bank or asset accounts  or something that you have right something that   you you know is an asset something and it could  be anything it could be tangible like cash right   or an account or it could be intangible such  as like maybe a home equity where it's equity   you have built in your home or it could be stocks  that you have that have a somewhat intrinsic value   although maybe not a cash value at the moment but  there is value to it an ira maybe you can't touch   it yet but it does have built-in equity and it is  considered an asset right also maybe retirement   so we can keep track of all those assets and of  course we also want to know the liabilities what   are the credit cards what do we owe right what  type of money do we owe is it a car loan do we   owe a friend a loan do we owe credit cards right  what is our liabilities currently money we want   to always make sure that our assets of course are  greater than our liabilities and basically our net   worth is simply our assets minus our liabilities  and that's going to get it as we can see here in   the dashboard our total assets minus our total  liabilities is our net worth so we're going to   be able to put that together that's really a  basic i also want to be able to create i want   to know what i want to reach a certain level of  income per month let's say maybe i want to set my   income goals to maybe 7 500 we should probably  call these white because they're user entered so   i'm going to go ahead and give these a color white  to make sure that we denote that that is available   for the user to make those changes a little bit  clearer there so what is our goals for our income   what do we want to reduce expenses maybe our  expenses are 3 000 or 3 500 we're going to try to   reduce them down to around 2 500. this is going to  help us know how close we are to our goals i also   want to know we want to increase our assets maybe  to 500 000 and we want to decrease our liabilities   what we owe other people or other credit cards  or other entities we want to re reduce them down   to certain levels so that means in our dashboard  when we see here we want to make sure that we're   we're currently our assets are 328 000. we  want to increase them to 500 000 we need 171   000 more to do it our current liabilities are  72 000 we want to reduce it to 50 000 so we've   got 22 000 more and of course our goals that  combined right we know that we've currently got   256 000 in assets we want to increase that to 450  and therefore we've got 193 left to do that so we   got an eye on that so we want to make sure that's  why so in our admin screen we can create these   goals and our admin's relatively simple that's it  each of our accounts whether it's asset accounts   or whether our liabilities account we have initial  balance right if you're starting this today right   what is the balance on your credit cards currently  if you put in all the future transactions when   you know what the current balance is just like  with the checking account if you're going to be   adding income to your checking account and  paying bills what is that initial checking   account balance so you can put that in here and  that's automatically going to get calculated   based on the additional entries that you put in  what is the current balance of that it's going   to start out with that initial balance so that's  the basics that covers the basics there and so   once we have these financial entries once we've  entered them into our database here here's all   of our financial entries here what we want to do  is we want to be able to quickly locate a specific   entry so the best way to do that is to simply  use a filter an advanced filter so first thing   you may want to do is you want to separate it  by income so if we want to look up only expenses   and i only want to look up grocery expenses and  then maybe i say okay this is a lot of grocery   expenses i've had in last year maybe i want to  just cut it to the last six months so i can just   take a look at this six month of groceries and we  see that it starts at in june 4th and goes all the   way to the december 24th so we can quickly locate  a previously entered expense item just by using   these filters and then of course we can select one  when we select one it is going to load that entry   if we want to make if our budget amount is 100  but we actually went a little bit over we can make   that adjustment here and then save that entry if i  decide that i want to create a brand new expense i   can click new entry and maybe i want to just put  in a reference or maybe you can leave this bank   it's not so it's not so important so let's just  say we do one two three and then we want to enter   an expense type right maybe we want something  let's say fitness right we're gonna pay for the   gym uh we already have that let's choose something  we don't have gifts and donations maybe we make   a charity donation every month and we're gonna  make it from our let's say our checking account   automatic and we've budgeted a hundred dollars and  then we also set the actual amount as 100 right   and now we can just put gift to charity right now  let's say we do this every month right i don't   want to create this same transaction every month  i want to do it in the future so i'm going to put   create gifts every one month starting on saturday  let's say make this the 20 22 the current year   and i want to create a total of 12 entries so now  we can see this button says create 12 entries this   is going to create one and it's going to start  on january 16th so when i click that button it's   automatically going to create those 12 entries  just like that and then they're automatically   created now when we click on 12 gifts haven't  added and see that notice it was real quick i   can select here and we can see that they have  all been added now if we change this filter down   to the first of january you're going to see that  all 12 have been added if i select on one we can   then make it edit to that one so it's very very  flexible right we can just save and update so now   we see we've we've made a change to this one where  our actual amount was 120 and the difference is 20   we've budgeted 100 our action amounts 120.  so we can quickly go in and make changes to   any previously created transaction this allows us  to create and fill the entry database very quickly   on all of our regular types and we can of course  put it whether it's one time or recurring this is   kind of helpful more of a note it's not a critical  important but i had an extra thing so we can let   us know what kind of type is it is it a frequency  is it a reoccurring type or is it a one time that   might help us in the future so i've added that in  so we can quickly create one or more entries we   can also look it up and very quickly edit one  just for the few clicks so let's go ahead and   see exactly how we did that of course every time  we save it it's going to get saved inside this   database each entry comes with a unique id a type  what is it an income expense transfer or is it   a payment what is the frequency type is there a  check or bill number we can update that an entry   date the date that it was entered an income or  expense type what type of that and what account   was associated with that now if it's a transfer it  could be two different accounts keep that in mind   and also if it's a payment it could be two  different accounts too if you're paying a   credit card bill from your checking account you're  going to have from the checking account here and   then you're going to have the credit card here  okay what is the budgeted amount what is the   actual amount and then some notes so we're going  to tie all that into a single database here called   financial entries okay so how are we going to do  that well let's get into vba and start out with   the left missed an icon here i think it's just in  back let's go ahead and bring that to the front   right picture format and we'll bring that  to the front here bring to the front okay   there we go that looks better all right so  we've got that here and what i want to do is   i want to go over we'll start out with this  filter here right when we make a change to   any type when we want all types i want to make  sure that all those types are listed here right so   we can use an advanced filter to do that of course  we're going to be filtering out this database so   the first thing we want to do is filter this data  based on the entry type and we need some criteria   for that and i've got some criteria right here  notice it says entry type we're filtering it by   entry type now if i want to filter it if i look in  here and i want to filter by only income accounts   i want to make sure that that criteria inside  there is only income if we take a look here we see   this only income so we know that this particular  is tied with a formula to that cell if the if it   the financial entries f4 which is the cell that we  just changed equals all types then just show does   not equal empty otherwise show whatever's in there  and this way it's just going to show it's going   to list all types if it's if the user has selected  all types otherwise it's going to show whatever it   is so if i have income and then i want the results  to come here so that i know that only income types   these income types these are the results so this  is where we're going to get our results right here   entry type of income and then all the associated  the unique income types here if i select all types   we're going to show all types and we want the  income so we know we've got them here all right   so we can see that now it is does not equal empty  and it's going to load all that all right so how   do we do that of course that's in a macro right  you can go into the developers and go into the   visual basic and we're going to go right into  their alt f11 shortcut and we're going to focus   on the entry macros we've got three different  modules one with some admin macros one with the   dashboard macros and one with entry magnets entry  is the one we're going to be focusing on first   and the macro that does that is called entry types  right the macro that does that filtering is called   load entry types now if we notice right that  macro when does that macro run that macro runs   when we make any type of a change to f4 f4 so that  way when we pull up this sheet this entry sheet   this is our entry sheet when we make a worksheet  change to f4 that's when we're going to have it   so if we take a look right here inside the code on  show entry name change run macro to populate list   if on if not intersection target range f4 is  nothing then run this macro right if we want to   locate that macro right click go to the definition  right so we just click definition and that's going   to go right to this macro which is located in  our entry macros first thing what i want to do   is i want to make sure we clear out any of the  initial data right i'm going to take all this   data here i want to clear it out because i'm going  to be bringing new data in here so e6 all the way   through f and down i want to clear that out so  that's just what we're going to do now there's   also a row that's associated with that when i make  a selection of a row i want to make sure that the   row that i've selected also gets cleared out that  row that's been selected is located right here   inside a let's unhide some admin columns i'm going  to right click and unhide it and we're going to   see that the selected row is located right here  in b6 b6 is where so notice as i select a row   b6 changes we'll get into the selection change  event in just a moment on another macro but we   want to make sure that when i clear this out  when i change this i want to make sure that it   gets cleared out because we don't know what the  user like so i also want b6 and b7 now b7 is the   specific entry that's been selected so it's  b7 so not only do i want to clear out b6   which is the row here i also want to clear  out b7 so those two cells get cleared out   along with all the data so we could do  that with just one line of code right here   we're going to focus on the entry database that's  going to work to take our focus because that's   going to run our advanced filter we're going  to determine the last row of data we're using   and excel up that's going to be row 310. once i  know the last row i'm going to run an advanced   filter and we're going to have specific criteria  that criteria is going to be o2 through o3 if   you've watched my videos before you've seen me  do advanced filters hundreds of times probably   and they're really powerful so you know the  repetition helps so we're going to have of course   the results come through q2 through r2 and that's  just what we're going to do here first we're going   to determine the last row of that database if it's  less than three that means we have no that no data   right actually we're going to put less than four  actually look our first row data's on four so   we want to make sure it's less than four so if  it's less than four we're going to exit this up   then we're going to run that advanced filter  we're going to start out with those header rows a3   through f and you certainly want to make sure that  your header rows a3 all the way through f is fine   because we're only we don't need to know all this  data we're not filtering this data at this time   so we're only going to focus on this up to the  income and expense account here we want to make   sure that the criteria when we set that criteria  most of the issues entry type this header must be   exactly the same and so are these as your original  data so make sure when you're adding that criteria   or you're adding the results what i just do is i  copy this and i just simply paste it up here just   to make sure that we have the right exact text  right i want to make sure it's exactly right so   we're going to run that criteria and we're going  to get those results we're going to determine the   last row of the results based on column q and  that's what we're going to do inside the code   so our criteria is o2 through o3 our results  are q2 through r that's we're going to have   our results and then we're going to determine the  last results row based on column q right once we   get that into variable we want to make sure that  we actually have data so if the last results row   is less than 3 that means we have no data and we  can exit the sub if we do have data what i want   to do is i want to take all the results of that  data and i just simply want to bring them directly   over inside this area right here and we have  conditional formatting that will automatically   cover that give it that alternating color along  with the selected row so this particular line of   code is going to bring all the data over bring  it from the database here into our entries row   starting on row 6 here and the last results row  now since this starts on row 6 and we're pulling   our data from row 3 we need to compensate for  the difference in rows so we're going to need to   add 3. that is it for the database then what i  want to do is i want to see now let's say i clear   this data let's say i rerun this and i want to  find out if this has been selected i want to see   if there's a particular been selected and i want  to select a row so what we want to do in this case   i want to cl if n10 does not equal empty and 10 is  right here let's take a look at this what i want   to do is i want to select expenses and i want to  select let's say this it should be n9 let's take   a look at that and 9 and nine because it was  not working but i'm going to show you exactly   why i just got that wrong and and nine up here see  here and nine so what i want to do when i run this   there i want to sit there you see not working i  want to select whatever's been notice that fitness   is here right if i change this to utilities right  and we see that we have utilities i want to look   up utilities and i want to find utilities here if  it's found i want that row whatever row that is   row 10 i want that 10 to be put directly in  b10 because why is that because let me just   stop the code right here okay and now because  i don't want it to run i don't all the way   so what happens is it's the code stop but i want  to know what row should be selected notice we have   utilities loaded here right we have utilities  loaded here so i want to select utilities here   i want to show that utilities is the one that  we're developing these are utilities right   these are this is utility so i want to make sure  that row 10 is selected so what i want to do is i   want to grab the value here and i want to look  it up here if it's been found i want to take   whatever row has been found and i want to place  that directly in b6 so as we continue on with   the code that's exactly what's going to happen  that 10 is going to appear here it's going to   trigger that conditional formatting if we want to  take a look at that conditional formatting we can   notice that that shouldn't happen see when i  select it we don't want that so let's fix that   issue when i select more than one cell i don't  want anything to happen so on entries right let's   take a look at the selection change event i'm  going to put in a little bit of code right here   if target dot count large is greater than one  meaning the user selects more than one cell then   exit the sub exit sub okay so that's kind of  important and then what we want to do because when   i was showing you something i want to select more  than one i don't want anything to happen right now   nothing's going to happen because i've selected  more than one cells so i've got a utility selected   here i want to make sure that when i run this code  i want to make sure that utilities automatically   gets selected here continuing on with code that's  what's going to happen we can undo that so how are   we going to do that well the first thing what i  want to do is i want to make sure it's not empty   if it's empty like let's say it's a new entry  we wouldn't know what to select so that's fine   but if it's not empty we need to know what select  so what we're going to do is wherever it's found   we're going to place it in b6 however if it is not  found it could create an error bug so what we want   to do is we're going to wrap it in on air zoom  next and on air go to zero however if it is found   we want to take whatever row it's been found and  we want to place it in b6 so where are we going to   look we're going to look in f6 through f999 what  are we going to look for we're going to look for   whatever's in n9 we're going to look in the values  and whole and we want to extract the row so i want   to take that row that it's been found i want to  put it in b6 if it's found that's it that's all we   have to do so that way notice if we have weekend  work here we've selected a specific weekend work   and then i go ahead and clear that out i want  to make sure that it is weekend work that gets   selected again very good so i'm glad i got to show  you that part now when i make a selection here   we want this list to load up i want to know all  the entries based on utilities between this date   and this date and i want to load them all in here  and that comes with a macro so that happens on   selection change notice when i make a selection  change this we're going to load up all of that   items that are fit that description so how do  we do that well the first thing that happens is   on selection chain so we go back into the entries  entry sheet here we're focused on selection change   right and we see that we're making a selection  change anywhere from e6 all the way through f   and then down okay so we're going to focus on that  e6 through f and down make a selection we want to   make sure that e contains a value right if there's  no value in e we don't want anything to happen   so assuming that there is a value then what i want  to do is i want to know inside t3 into days t3 i   want to place whatever's this what's our criteria  how we only want to know close so i want to take   whatever's in f and the selected row i want to  place it inside a criteria right we're going to   run another advanced filter and it's going to be  called here entry criteria right so what i want   to do is i only want to know close and i only  want to know between these two specific dates   then we want to know all of the items all of the  entry items that have closed between those two   dates and i want those results to appear here  once i get those results i want to then creep   take those results and bring them directly inside  here and so that's exactly what we're going to do   so it is that selection change event that happens  everything so the first thing what i want to do   is take get that criteria inside t3 of our entry  database t3 is very important because i need to   know what we're going to filter on so t3 is going  to take on what it's going to take on whatever is   an f in the target row so if i have selected a  row i know the target row is 9 i know that f and   the target row weekend work is where exactly where  i want to place it inside t3 that's where i want   to place it okay so moving on we know we've  got that so continuing on so what else do i   once i've got that criteria placed i also want to  trigger conditional formatting right conditional   formatting let's just go over that is based on  that so i want to take whatever row that's been   selected and i want to take that row and i want to  put it directly inside b6 inside b6 so b6 is going   to take on that target row that's going to trigger  the conditional formatting if we go in here home   go to conditional formatting we're going to see  three different rules first which is for that   selected row and basically all we're going to do  if b6 equals the selected row we're going to color   it this dark background with this white bold font  i also want to call the alternating row so we can   use this formula for our odd rows and we want to  make sure that e6 make sure there's no dollar sign   before 6 because we wanted every row below that  and we want to know the odd rows mod of row 2.   that formula is going to give us that green that  little light green formula it's going to apply   to e6 to f99 for even rows we're going to go with  even a lighter color nearly a white and even rows   the only difference in this one is the mod equals  zero meaning even rows this equals one meaning   address so odd rows are going to get colored  this and we also want to make sure that again   there's no dollar sign before the six we want to  make sure it's not absolute covering every row   applying to that's it that's the same conditional  formatting we're going to use basically for here   and here it's exactly the same the only difference  is this is going to be focused on row 7 while   this is going to be focused on row 6 okay the  selected row notice the selected row will change   to whatever we want it to when we select it okay  so after we've placed that row and the only macro   that we're going to run is called load entries  that is the next macro we're going to go into   so entry we've already covered load entry types  now the next macro is called load entries and   it's going to be very very similar right the first  thing what we're going to do is clear out all the   data now we do have some hidden data here i want  to know that entry id remember inside our database   our database in column a starts with an  entry id this is a unique id for every   single entry i want to place that but i want it  hidden so how can we do that well there's two   ways to hide it really one is to change the  font color the same as the background color   another one is simply to change it to a custom  format so if we look in the more number formats   we've got three semicolons here if i were to clear  them out maybe to put or put something general now   we can see that those entry ids right so one this  is important because when i select on something i   want to take whatever entry id is here i want to  place it directly inside b3 b3 is where our entry   id is that's going to help us determine the row  and i'll go over that in a moment so we want to   make sure but we don't need to see these entry ids  so all we need to do to hide them is just go into   the custom format and just change it with custom  and just make it three semicolons so one two three   semicolons and that will hide them all keep in  mind if we are going to use the find to look for   something let's say we're looking for something  find using xl value let's say remember using   when we use a find often we use something like xl  values right but when we're looking for a specific   number that's hidden we need to use excel formulas  that'll find it even if the column is hidden or   it's hidden or it's hidden through some formatting  okay formulas will help us do that find what when   it's hidden values will not alright so that keep  that in mind so let's continue on with that macro   now so loading the entries the first thing we want  to do is clear any data i want to clear one the   selected entry row which is in b7 i also want to  clear remember starting in g6 all the way through   k and down we're going to clear out any data that  might be here and that's going to handle it right   here entries range b7 through g6 k99 that'll  clear that out then again we're going to focus   on that entry database we're going to do just like  we did determine the last row we're going to get   some criteria we know this has already been placed  now the dates here for the criteria are linked to   the dates here if it's empty here we want it to  just show empty show all the dates however if it's   date i want to show only this this date greater  than or equal to this date and less than or equal   to this date so we can do that right here with  a formula now we want to use the dates in number   format that's very important because regardless of  the date format when it's in number format it'll   work properly so if the financial entries does not  equal empty then we're going to put greater than   or equal the financial entries i4 that's that  from date otherwise we're just going to show   doesn't that equal empty and we're going to put it  there and the same for the entry date except we're   going to focus on k4 if k4 is not empty we're  going to show less than or equal to whatever is   in k4 otherwise we're going to show it's empty  and this is going to provide that necessary   criteria for based on the specific income or  expense type and the dates associated with that   entry so we can do that so our criteria is going  to be from t2 to v3 we're going to pull those and   we're going to pull those results directly in  here all the way down here i want i want the   difference between our budget amount our action  amount a formula is going to take care of that   this formula making sure that we have values both  in z3 and a3 as long as they're both available   that i want to subtract them so if there's any  difference there's no differences in these i'm   going to subtract out that difference and show  it because i want that difference to show up   here inside it but that difference is not part of  our database so we want to use a formula to help   us to do just that okay so that's basically what  we're going to be doing and so again we're going   to i want to clear the prior results i want to  clear this x through a b and because this formula   wouldn't necessarily be cleared out automatically  by vba so we want to make sure to do it starting   in x3 all the way down clearing out any data that  might have been there before determining the last   row of our original data if it's less than four  then we're going to exit the sub we're going to   run our advanced filter just like this should not  be jf our advanced filter should go all the way   to the last and we're going to call that j okay so  it should be j although it still works and then we   want our results to come t2 through v3 t2 through  v3 these were this is where the criteria is going   to come t2 through v3 our results are going to  come in through x through a a x2 through a2 and   that's just what we have here x2 through a a  the last results row is going to be based on   column x if it's less than three we're going  to exit the sub that means there's no results   then what we're going to be doing is i want to  copy down that formula that formula is always   going to stay in ab1 so all we need to do is take  the formula in ab1 and copy it down from ab3 all   the way to the last row we don't really want to  keep formulas in cells unless we're actually using   them right so we certainly don't want formulas  all the way down here that'll slow up our workbook   we don't want that so we can use vba to bring down  those formulas having some kind of calculation on   demand or formulas on demand only when we need  them that keeps our workbook light and fast so   once we have bringing over those formulas remember  formula to formula we're going to bring in all the   results taking these results from x3 all the  way through a b and bringing them directly   inside here all starting in g all the way through  k and that's what we do here so g6 through k and   the last resolution again compensating for the row  difference by adding three going to bring it all   the way through x3 through a b and the last result  so this is going to bring over our entry details   and again just what i want to do before if i  run this filter let's say i run this filter   let's say i'm running this filter notice i just  ran the filter remember when i run this filter   we're clearing out that selector i'm going to  run it halfway to right here so when i run this   filter here it's it's stopped notice there's no  selected line right we don't know what line is   selected because we could have added or updated  so it gets completely reset but what i want to do   is i want to select whatever entry is here i want  to select that now remember we have entry ids here   so if i i know that it's entry id here so let's  take a look at this here we see in g6 that's   uh g6 row six we see that there's a 66 there  and we also so what i want to do is i want to   look up the entry id which is our selected entry i  want to look it up directly in g6 all the way down   if it's found on whatever row i want to place  whatever row and i want to place it directly   inside b7 so if we continue on with the code  that's exactly what's going to happen b7 is   going to take on that row 6. and then when it  does that triggers our conditional formatting   selected row so that we automatically know which  one has been selected because we gets cleared out   so and remember since it's hidden we're going to  use formulas so the first thing what i want to do   is i want to make sure that there's a value in  e b3 if there is then i know i want to look it   up to see if it's found inside column g so we're  going to do that here if b3 does not equal empty   then again b7 that selected row is going to take  on whatever is located in b3 we want to look for   b3 and where am i looking i'm looking in column  g6 to g9 if it is found we're using the formula   is very very important because it's hidden using  using formatting right so it's not going to be   able to find it if it's not using the xl formulas  we want to extract the row then that row is going   to then take on and go inside b7 and of course  if it is not found it could create an error   therefore we always want to wrap using when we're  using find bones we want to wrap that on our zoom   next and on error go to zero in case it's not  found great so now that we have that we don't   need this additional so it's automatic that'll  automatically trigger that condition forming   and that's it and that's all we have to do so  every time we refresh this or not automatically   any entry that's already here is automatically  going to be selected notify select this   it's cleared out and then it'll automatically be  selected once we refresh that if it's found if   so notice it's not found right it's not found here  because i have it selected notice we're in weekend   work but we're focused on checking however as soon  as i select it here and i refresh it or if i check   the same one it's going to be found notice it's  found here or i refresh this it's automatically   that row is going to automatically found and  that's really important because we automatically   know and can see exactly what entry has been  selected so it's very very easy to up make those   updates if needed great so we're glad i got that  so now the next step when i make a selection here   we see that what that does is it automatically  loads this entry this transaction here it   automatically loads the data and that's relatively  easy with just a few lines of code actually   because we're using data mapping so on selection  change of anything between h6 and k and all the   way down assuming that there's a value in h i  want to run a macro that's automatically going to   load it now we need to know we know it's coming  from this database but we need to know exactly   where we're going to replace it we can use data  mapping to do that if you've seen my videos before   you're very familiar with data mapping and so  basically we know that the entry type is located   in n5 we take a look at n5 here we see the data  types here and we see that our frequency type here   is in bp5 and so all we've done is simply map  the cells on our entry screen with whatever the   columns are associated here then all i need to do  is loop through columns 2 through 10 and basically   whatever is located in the row that's been found  we can then load it up how do we know what row   it's been found well we can calculate that we know  we know we've already placed this entry id here   right as soon as we we don't know that yet as soon  as i make a selection i'll show you what happens   when we go to the entries here on selection  change we're focused on this now we're going to   be focused on entry selection h k through k as i  mentioned and we want to make sure that h actually   we can use g or h g contains of course that  entry id is not empty meaning there is a entry   there first thing we want to do is take here it is  right here b3 is going to take on whatever is in g   in the target row that's going to set that entry  id once we set that entry id and then we set the   selected row it's automatically going to calculate  the row it's how do we know that that calculator   row is going to be located right here how do i  know what row is located on well of course i can   use a match for that because i've got a name range  called entry id if we look inside the formulas   name manager and we take a look at entry id i've  got many of them we'll just focus on entry id   and that's basically a dynamic named range  in column a that's going to set that entry id   what that's going to do of course it starts  on row 4 what that's going to do is get us to   extract the row and that's what i want i want to  know the row entry id 131 is located on row 134   using this match formula we're looking up b3 which  of course is our entry id we're going to look up   inside that name branch called entry id we're  going to add 3 to that because i want to know   the row number right if the first one is found  i want to know it's on row 4 and therefore we're   going to add three okay we're adding three because  they don't start on row one they start on row four   so we always need to add three on that once i've  got the row i then know where to load it up right   if i know the row and i loop through the columns  i know that on row 11 right income is going to go   into n5 we know that one time's going to go into  p5 so we you can use data mapping to do just that   i also want to know the next entry id right if we  know all the entry ids and making sure that they   are actually valued numerical values no letters in  there no alpha numeric we can use the max formula   adding one is going to get us the next entry id  so that way when we create a brand new entry id   we know which is the next one to assign to that  brand new transaction okay again so when we make   a selection on here that's what we want something  to happen so the next thing that happens on that   selection is the entry load that is the action  that is the macro that's going to run this   so we go to entry macros and we're going to see  the entry load so that's going to be i'm going to   go down here skip a few and go right to entry  load so notice it's just a few lines of code   first thing we want to do is clear all the  contents right when i'm loading an entry i want   to make sure that all of these cells are cleared  out so we can do that with this line of code here   clearing out all of the cells associated with  that entry next up i want to make sure that we   have calculated a row without this bro here in b4  we cannot load that entry so we want to make sure   that b4 does contain a value so if b4 is empty let  the user know to select a correct entry and exit   the sub there's nothing we can do without a row  if there is a row then of course we're going to   put that into a variable called entry row that is  a long variable and then what we're going to do is   just as i mentioned we're going to loop through  columns 2 to 10 based on that entry database   right entry database what we're going to do is  we're going to extract whatever's in that row   and whatever's in that column we're going to  extract whatever is in that cell and we're going   to place it where we're going to place it inside  the entries but where are we going to get that   cell that cell that we're going to place in is  located in row 1 of the entry database right here   we're going to place that in here that's all  we need to do to load that entry now that we   understand how to load those entries let's go  back up and see the macros that we skipped right   we covered load entries i want to go to entry new  that is the macro that's been signed to this when   i click new entry i want to make sure that we  clear out all the associated ones maybe we can   keep the entry type payment in case they use up we  don't necessarily declare i want to set the entry   date to the current date june 30th and i just want  whatever perhaps whatever the user has said so we   don't necessarily need to clear out those we can  keep those defaults and that way if the user is   going to be entering a lot of payments they don't  need to keep entering it again and again again   i want to set them clear out everything else to  say that we can keep this in case they want to   create multiples but i want to clear out at least  these fields associated fields and i also want to   clear out this the entry id right once we clear b3  is very important because it's a brand new entry   notice the entry row is empty and that's because  there's an error because there's no match found   because there's no particular entry id located in  b3 therefore it isn't going to result in an error   so we have iver going to be blank okay so we can  make sure that b4 is blank so b3 must be cleared   out right along with all the other associated  fields we're going to set p7 that's our date field   we're going to set it to the current date and  we're going to set n7 we're simply going to select   n7 that's going to allow the user to automatically  enter a bill a reference id here we're going to   do that so continuing on so that's all that we  need to do so next up is going to be the save or   update if i want to save it i want to make sure  that all the information is there so if i have   information here i want to make sure however  keep in mind it's a little bit tricky because   let's go for a new entry if we're entering an  income here let's say we're entering income i   want to make sure that this says income type so  however if we're entering an expense i want this   to say expense type if we're entering perhaps  a payment i want this to be payment from and   if we're entering a transfer i want to know to be  transfer from so we want the label to be dynamic   and change based on the entry type here so we can  do that with just some lab so if n5 equals income   n5 equals expense if either one of those then  show n5 and type right so either it's going to   show expense type or income type otherwise it's  going to show n5 from and from so basically it's   going to be transfer from or it's going to show  payment from or it's going to show income type   or expense type so we can use that formula there  also if we're click if we're creating an expense   then i want to make sure that we're only having  expenses notice that these are only expenses right   however if they've selected income i want to  make sure that all the income targets are the   only those associated income types we've created  named range inside our admins notice we have   four income types notice we have several expense  names now we've created a named range for those to   help us out so if we go into the formulas and then  the name manager we see that we can have multiple   forms so the first one is our income types right  income type here is using a dynamic named range   based on that we also have the expense types here  too also another dynamic name bridge likewise we   also have account and asset accounts right so  that's going to be very important so we have   account asset accounts so if we take a look at  the asset accounts i've got a dynamic name range   for asset accounts and likewise i have exactly  one for the liability account here so i've got   four different name drains income names expenses  count bank account asset accounts and of course   liability accounts so we have all four of those  and that's really going to be important if we take   a look inside our finance back in our finances if  we decide to make a transfer right we don't know   what account they're going to be transferring  from it could be any account so it could be   whether it could be liability account right they  may may transfer from their bank account to pay   off their credit card so it could be a checking  account and then it could be to account perhaps it   could be their um american express card to make  a payment right so we don't know exactly what   so we need to make sure that the data validation  located right here in cell n9 changes based on   the entry type so when we make again whenever we  change it to income i want to make sure that it's   income accounts or expense accounts so how do  we do that well that's going to be on change of   n5 when we make a change to n5 we want something  to happen okay well let's let's take a look and   see exactly how we're going to do that it's  going to be on a worksheet change event right   when i'm making a change to something a specific  cell here worksheet change that's what i want   to happen and what cell are we going to focus on  when the user makes a change to n5 so right here   if the user makes a change to n5 not intersect  target nothing and we want to make sure n5 does   not equal empty then we want to make some changes  right so we're going to dimension the entry type   as a string the entry types can be whatever the  user entered okay also rate i want to delete   anytime we change validation anytime we make  a change we must first delete the validation   that's here any validation issue must be deleted  so that's the first thing to do regardless of what   they change this to we're then going to delete  the validation here so the first thing we do   is delete that validation and then we're going  to use select case select case is helpful because   there's multiple options here and it's going to  be based on that entry type now if the case is   income what do i want to do i want to take n9 and  i want to set it to that income type formula right   that formula is going to be that named range so  i know that when it's changed income type if i go   into the data and data validation here we want to  make sure that it is set up as income type however   if they make the change to expense i want to make  sure that it is back to expense type right so here   we have a list of expenses so we want to make sure  that it's automatically set to those expense type   and likewise if it's either a payment or a  transfer we want to set n9 to that account right   so if we take a look i've got another one let's  go into the data data validation here and then we   see that is all accounts type now we have another  one called all accounts type what is that let's   just go over briefly on that and that's going  to be in the admin screen so we have something   called all accounts type and i believe that it  is right actually i got two of them i've got   here we go all accounts right so i've got one  that's going to be all accounts now i've got   a particular macro that's going to help us with  that but we'll go over that in just a little bit   later but basically there's a single list and that  that's important because if we add a card right   if i if i had a card mastercard right i want to  make sure that that automatically gets added in   there when we run that macro so that's going to  be very important when we add it in and this is   important because if we decide to add one let's  add in mastercard right i want to make sure that   that list gets updated automatically with that new  credit card automatically and that's done through   a macro which we'll go over and so what i want  to make sure is that we have a list of all the   accounts and that was going to simply mean all the  bank accounts and all the liability or credit card   accounts i want to make sure we have a single list  and then of course we're gonna have a name range   that handles that you just saw that it's called  accounts all so we got that all account types   so it is here called all account types and that's  the dynamic name range so this particular list is   going to be used for either payments or transfers  we want this setup to all account types so going   to the data once again we see it as set with all  account types how do we do that well that's going   to use select case so if it's an income type we  want to set that data validation to the formula   income type if it's an expense we want to set it  to expense type and if it is either a payment or   a transfer we want to set it to all account types  that's going to automatically set the validation   based on what the user has entered okay great so  that's really important that way they can set it   up here and we want to make sure that two account  is also going to list all the account types right   so the two accounts always going to be the account  that is always going to be static and it's going   to always be the all account type so the user  has the freedom to make a transaction to any   from or to any account the only difference is  the label here right we want to make sure if it   is an expense or payment account i want to be two  account otherwise it's going to be from account   right so that's what i want to say it's either  going to be payment account or to account so   so let's go over this label here if we see income  right we're sending money we're getting a check   we're going to send that money to an account  right if it's going to be american express   of course it's going to be paying it off however  if it is from a checking account it's going to   increase that checking account so it's going  to be the 2 account right we're getting money   and we're putting that money somewhere we're  either paying down debt or increasing in   assets such as the checking account or our  cash right however if it is an expense right   i want to make sure that is the payment account  right if we're if we have an expensive type and   we see let's say we've got a restaurant and we're  paying that restaurant with our checking account   debit card maybe i want to make sure that we  understand that this is the payment account   which we're paying it from we're paying that  restaurant bill from it so this is the payment   account right if we're making a transfer a payment  or transfer we're transferring it to this account   or payment to account or we're transferring it to  an account so we want to make sure this allows us   to use just two entries and we can easily allow  the user to really give a more personal touch to   that so they can understand exactly what is going  on these dynamic labels really help us to do that   although this particular is always going to be  the same account we have a budget amount and we   have an actual amount and then some notes great so  continuing on so we understand how we're changing   that validation to this and that's on change of n5  so when we go back inside the entry macros here we   understand that it's new we're setting that up and  now what we want to do is we want to run a macro   that's going to save or update and that means if  we make a change here if i set this to 50 and 50   i want to know is it a new transaction or is it an  existing transaction that's very important how are   we going to know that well we know that through  this row right here before we know that there's no   row associated with this so if we set this to just  something and we can now we see that we've created   transferring from probably wouldn't be a transfer  on this one but if we decide we're going to   expense account we want to expense fifty dollars  for dinner and come from our debit card i want to   save that single entry we know that it's going to  be a new transaction because that is there's no   row associated with it so we need to assign a row  and we also need to assign a brand new id to that   so when i save that entry and then automatically  we get this entry save this fade out message here   we take a look inside our entry database at  the last row that we just created we see that   we created three online and expense a one time  e45 on 630 restaurants the checking account with   a budget amount and an actual amount of 50 and  we did not put any notes in there so we can see   that that transaction automatically was able to  handle that in a new transaction and that's going   to come with this macro right here called save  entry now if i decide i want to make it update   to that and our budget mine was 50 but we went  over budget because we ordered some extra food   saving that entry we want to make sure that now it  is updated we know it's updated because we already   have a row that's associated with that so when we  go down to the bottom here we see that our update   is automatically handled we see the actual amount  is 60. so we know that update so this single macro   will handle both new transactions and existing  transactions and we can do that very very simple   based on a single b4 value the first thing we want  to do is we want to make sure that the user has   filled out all the necessary fields right there  are very important fields we need to make sure   we've got an expense type we need to make sure  that both of these are filled out we need a we may   not necessarily need a budget or actual amount of  amounts probably could be blank but the entry date   is certainly important we got to have that and we  definitely need the accounts so what i want to do   is i want to make sure that certain fields get  filled out now the required fields i've set them   to six and what i'm going to do is simply count  the values count those cells that contain any type   of a value or any type of text and we're going  to use count a for that so n5 p5 n7 p7 n9 and n11   making sure that they all contain values right i  want to make sure that everything contains values   at least all the important ones not necessarily  p10 i didn't i didn't add in because we may not   we may have a budget amount but no actual we may  have an actual no budget so these are not required   so what i want to do is if i know that six fields  have been filled out if it's anything less if i   create a new entry and i don't then i forget to  put an account in there i want to make sure that   we cannot save that entry and i want a message to  come up says please make sure to add an entry type   entry date entry type i put interest type  twice here and account before saving this   entry so what we really need is the associated  accounts so how do we know that well because we   see that the required fields are four but it's  less than six so we need to make sure that we   have all that if b8 is less than six we need to  let the user know and that's just what we did   here if ba is less than six please be sure to add  an entry type entry date let's just put account   that's and let's put an income or expense income  or expense okay i'll just put that in okay before   saving this entry okay so that's it so we're going  to exit the setup because we know the user has   not filled out all the required information okay  but assuming that they have then we can continue   on now all we need to do is determine is this an  existing entry or is it a new entry b4 will tell   us that b4 is that associated row we know that  if b4 is empty is a brand new entry however if   b4 contains a value we know there is a row that's  associated with that so b4 will help us determine   if it is a new entry because b4 is empty we can  then set the row up that row is going to be the   first available row based on the database we can  also set that next entry id that next enter id is   going to come from b5 using that max formula it's  going to go directly into cell b3 and then all we   need to do is assign that brand new entry id to  column a of that entry database and that's also   going to come from b3 however if it's an existing  entry all we need to do is take whatever's in b4   that database row and place it inside this long  variable called entry row because we have data   mapping it makes it a lot easier again just  like we did when we're loading it this time   we're going to do reverse we're also looping from  two to ten we don't need to use the first column   because that first column is that database id we  already have that there so we don't need to save   it it's already in column a so all we need to do  is simply take whatever is inside this particular   here whatever's inside this cell this is the row  one right i'm looking inside this cell i'm looking   in n5 p5 and 7. when i find whatever's there i'm  going to place it in column 2 3 4 and just move   it on because i already know the row associated  and that's exactly what we're going to do here so   once that all that data is basically brought over  from these fields here into that database here as   we loop from two all through the last column 210.  that's it that's all we have to do so once that's   done we can then i want to run two macros i want  to reload the entry types and reload the entry why   is that important right if we take a look inside  here and we added brand new let's say we have   expenses here okay and we have all these expenses  i don't know if we have let's say let's look in   the admin i think we have a transportation we  don't have any transportation expenses right so   if i decide i'm going to add in a transportation  expense i want to make sure that that also gets   listed here right so if i had a new entry and i  decided it is an expense right and i decide that   that expense is a transportation here and we'll  scroll down and we'll add that in transportation   and we need the payment account let's say we  used our credit card for that or let's say our   mastercard for that here i want to make sure that  we put an actual maybe we didn't have a budget 15   i want to make sure that notice there's no  transportation here but as soon as i save that   entry oops better add in that uh reference  or build number that probably shouldn't be   necessary i don't think i don't think n7 is really  necessary there so i'm going to save that up   so there we go so now transportation automatically  added in now transportation is automatically added   in okay let's take away n7 to do that what we're  going to go is taking the required fields i'm   going to take n7 out here because i don't think  that should be required okay now and now what we   want to do is now we notice that we only have uh  let's say let's clear that out where we don't say   we don't have one it's 5. i want to make sure that  less than 5 is okay so we're going to go back up   to the save and update and we'll just change this  to five that's sufficient enough for the required   fields so now we can save that entry so the point  was that i want to make sure that transportation   gets added here and notice we have a single  entry of transportation right that's why i want   to reload this list because if we added a brand  new expense that was never before i want to make   sure that that expense gets loaded into this list  therefore we are going to re-run that macro that   reloads those entry types i also want to run the  macro that's going to load these entries so we're   going to run the macro that reloads this list and  we're going to run the macro that loads this list   that way it's automatically done and let me show  you that one more time let's go ahead and new   entry and we'll add in a another transportation  expense okay here and let's just add in bus   tickets and we'll use the payment account we can  use a different payment account if we want in   this case it could be cash and then let's add the  count amount of let's say 10 okay so now we have   that now we're saving that entry we don't need the  build for so notice that because we reloaded this   list up automatically the right one got selected  you see how nicely that works we know that it's   automatically selected it works really well it's  very very user friendly so they know exactly   that brand new one got listed that's why we  have to run both the types and the entries   and then what i want to do is i want to make  sure that we're going to run that saved message   now that saved message is going to come up when i  save it you see that pop-up save message the only   time i don't want that saved message is when i  create let's say i create multiple entries i want   to create this i don't want the same message to  come up 12 times so what we're going to be doing   is when i create it 12 times i need to set a  difference so we're just going to use create   multiple this so when i create multiple i'm going  to set this to true when b11 is true and i'll go   over that macro with you certainly we don't want  to have that saved messages pop up 12 times so   what we're going to do before we run that saved  message i'm going to make sure that b11 has to   be false right if b11 is false meaning we're not  running multiple entries then we can run the macro   that's going to save that message that macro all  we're going to be doing is simply taking a shape   that shape that you just saw right that faded out  and i'm going to be taking the transparency and   it's going to set it from 1 to 100 we're going to  reduce that transparency and then as soon as it's   almost completely transparent we're  going to hide it that's it for that macro   okay the entry load we've already went over so  that was either creating multiple entries now   this is a great macro right if i want to create  if i know that we're going to be taking the bus   tickets every single week or let's say every day  let's say every that's every week since we don't   have day i guess i could put that in there so  let's say we're going to take that bus once a week   right every week and we want to take it for let's  say eight times i want to create that bus ticket   automatically i want to create eight entries so i  want to do that right here clicking that button is   going to automatically create that those eight  entries and we see that eight entries have now   been so we see that now every single week that  bus ticket has automatically been added in here   so we didn't add a budgeted amount if we wanted  to add a budget amount we could just do that and   what i like about this as soon as we save it it's  automatically going to be saving i really like   that that feature that we have there okay great so  we see that we certainly don't want to save that   that saved entry notice that message didn't come  up there so all i really want to do is determine   how many that we're going to be creating how  often it is and then automatically create those   notice that we have this on 6 30 right we created  this one initially and then we started our   on this one 116 we created two of these and  now with january 16 23 so every single week   another one got created for every date okay so  we can do that with this macro that is the macro   now notice that it's kind of nice notice this  particular button text you see it is dynamic   right which is kind of nice create eight entries  if i change this to 10 that button text is going   to be automatically updated so how do we do  that well the button text if we take a look   inside here is tied to a specific cell called b10  b10 is where it's tied to and b10 is here so take   a look in here and we see that b10 is simply  equal to create and then a space whatever's   in p17 which is the number of entries that we have  and entries so we can create a dynamic button text   when we link it to a cell the cell is there so  when we see that the cell here and we escape   out of there here and we look at that we see this  the cells called create 10 entries so we've tied   the text of that button to cell b10 and we can do  that so now the macro that's typed that is where   we're going to go over now so we can create  a dynamic button it kind of helps helpful so   the user knows exactly what they're going to be  creating continuing on so this particular macro   is the one that will run the first one thing i  want to know is the entry frequency string and i   want to put that as a string that entry frequency  is going to be either weeks months or years so we   need to put that inside a variable i also want  to know the dimension the frequency quantity the   entry quantity how many we're going to be putting  in how frequent is it and we need to keep track as   we move through this if we're going to be entering  10 we need to keep track from 1 to 10 so we need   a variable that's going to keep track of what  entry that we are on i also need to know several   dates i need to know what is the entry date the  date that we're entering what is the start date   what date are we starting on we're starting on of  course this date right here and 14 and i also want   to know the next date as we loop through that okay  we're going to focus on the entries i want to make   sure that again let making sure that b9 is less  than 4 please make sure to fill out this shouldn't   be let's just update that here i also want to make  sure that it's going to be exactly the same as we   did before let's do this i'm just going to copy  this b8 is less than 5 that's what it should be   and we want to make sure that we also have the  required fields as we did before so i'm going   to just paste this in here because that's going  to update that if b8 is less than 5 please make   sure to add so we want to make sure we're saving  the entry i want to make sure that the required   fields are there okay we're going to set the  frequency quantity to whatever's in o16 that   frequency quantity right here i need to know  that it's going to be one how often are we going   to be doing it i also want to know the entry  frequency how many are we going to be doing p   16 is the entry frequency right is it  weeks month that's a string variable here   p16 is that frequency okay how often weeks months  or years and i also want to know that start date   what is the date that we're starting on that's  going to be located here inside n17 that's going   to let us know b9 is before let's take a look at  that that was kind of important i need to make   sure that we're creating these these four fields  must be filled out that's what i just remembered   these four fields are required right let's take  a look inside b9 required multiple entries these   four fields are very important that was important  it's also important that we do have that so we do   need both here i need to know that those four  fields are done right i want to make sure that   but that will automatically take care when we save  and update it will make sure that we've already   created the required fields so i want to make sure  that there's a value here a value here value here   and value here right four different fields b9  will tell us how many of those fields have it   using b9 counting o16 p16 p17 right we're going to  count all the text if that's less than 4 then we   have not filled out the required fields that's why  it's important i just remembered okay so we need   to know all those when we create multiple entries  i need to know it right if the user leaves one of   these blank we know that we cannot save multiple  entries i need to know that please make sure to   fill in all four multiple entries i need to add  an e at the end of that okay so that's very very   important we need to make sure they can't do  that okay so that's exactly what we're going   to be doing and of course we could also place this  right here just as we did right in the save here   we certainly want to make sure that we also have  the correct number of fields in the original   transaction right if we're duplicating something  and we need to duplicate multiplying we still need   to make sure that we have all the required fields  that's also important so both are important so we   can put both down here we need to make sure that  it does have the required fields okay so both is   good continuing on so the next date i'm going to  set the initial next date as the start date right   so the first our first transaction is going to be  this and then as we add more that next day it's   going to go up one month or one week or one year  depending upon what we have set here or two or   every depends on that and so this is where we're  going to go first of all we're going to run a loop   and then let's say we're going to so here's we're  going to set multiple entries to true i want to   set multiple entries to do this is going to make  sure that that popups entry save doesn't come up   i only want that when we're doing a single entry  at a time not when we do multiple entries this is   the differentiator here b11 when we set that to  true that fade out message will not be appearing   okay so continuing on setting that to be 11 that  will make sure we're going to set the entry number   we need to loop through all the entries we need  how many we're going to create if we're creating   10 different entries we're going to go from 1 to  10. first thing what i want to do is in p7 i want   to set that next day that date of that transaction  located right here in p7 we need to set it there   once we do that i want to make sure that we're  clearing the assigned id this is very important   why is that important it's important because  we're creating a brand new transaction so   we want to make sure that any entry id here  gets cleared out that'll trigger a brand new   particular entry because entry world would be  left blank okay so as soon as we clear out entry   id we know that it is blank okay so that's very  important all right continuing on we want to make   sure that we then save that we're going to run  the macro once we've cleared out the selected row   once we have cleared once we have set the date  we're just going to save that transaction it's   going to run that mac that's actually going to  save it once that mac or run the record gets   saved automatically and then all we need to do is  secure the next date right the next transaction   so we can do that using select case we've already  determined the entry frequency whether it's months   weeks or years if it is weeks all we need to use  is the date add function right we're going to add   that next date is simply using the date function  we're going to be adding weeks so ww is the code   for that we're going to be adding the frequency  quantity how many weeks and based on the next   date so based on the current next date we're going  to be creating a brand new next date based on the   frequency so it's a one week two week three weeks  right we can do that and that's automatically   going to calculate the next date very powerful  of course if it's months we're going to do the   same thing the code for months is months whether  it's every one or two or three months we're going   to take the current next date and we're going  to add on to that based on the number of months   that we need to add same thing for years except  the years of code yyyy so it's four wise that's   automatically going to calculate the next date  that's all we need to do is as we loop through   this it's simply going to be taking that next date  placing it a piece p7 clearing out the existing id   which will automatically trigger a new id and then  clicking save update so that's how we can create   many unlimited entries very very easily okay  so all we need to do is let the user know once   it's completed once we've exited the loop we've  created all of them for all the particular entries   that the user wanted created we're going to let  them know based on that entry quantity that so   many uh types of transactions what is that type  of interaction is it income is expenses is it   transfers based on whatever is located in n5 or  actually sorry n9 based on this expense type here   transportation whatever is located here letting  the user know that they've already been created so   let's say it was uh transportation expenses let's  just say transportation entries have been added   okay and then what we're gonna do is we're gonna  set b11 back to false this is gonna make sure   that it's set back to false so that that pop-up  message will appear here as long as b11 is false   to save that entry and that's going to that fade  out message there so that's all we need to do to   create multiple entries well the last macro for  entries is the delete one and we kind of skipped   it over but it's right here and that's the same  one that's tied to this if i want if i've created   something and i want to delete it i want to make  sure to do to delete it so we can click delete   entry and we're going to get a pop-up that says  are you sure you want to delete this entry if yes   that entry is going to be deleted we are going  to reload this list up and of course nothing is   going to be selected we're going to go to the  new entry allowing the user to either enter an   entry or select an existing one delete entry  we're going to do that the only difference   is we want to make sure that determinant hasn't  been saved before right so let's take a look at   let's say we have a new entry and we enter an  expense type but we decided we want to delete   this right now it has not been previously saved  right so deleting this entry if we say yes is   simply going to clear out the existing there's  no database row to exist so when you do need to   differentiate between has an entry bin saved  or not b4 is going to tell us that before if   there's no row associated with that we know that  we don't need to delete any database row because   it has not been previously saved so first thing  what we're going to do on the deleted tree we're   going to let using are you sure you want to delete  this entry if it's no we're going to exit this up   if b4 equals empty that means it has not been  previously saved we're going to go to not save   simply going to skip these two rows and go right  to here where we run the macro to create the   brand new it's going to clear out all the existing  cells however if it has been saved and b4 is not   empty we're going to set that entry to row to  whatever is in b4 we're then going to go to   the entry database and we're going to delete  the associated row using entire row delete   all right and of course after we clear that new  entry out we're going to reload that list of   entries there that's simple that's all we need to  do to create the entries all right great so we've   gone over the first part of it right the most  important part right how do we quickly easily add   entries to the database multiple entries or single  entries so we've done that and of course how do we   show these entries the next part is how do we take  that data that we've now added in and create these   financial and put it in a position where we can  then work with it right so what i want to do is   i want to create some pivot charts that's really  going to help us out and create those budgets   that's going to allow us to create this really  dynamic dashboard okay there was one thing i did   quite skip over a little bit briefly and that  was here now we may well there are times we may   want all types of income and or expenses or we  may want all accounts notice when we add that   account it got added to this list and that's going  to happen on change event so when i make a change   to any of these expenses or income i want that  list to show up here if i make a change to any   account name i want that list to appear so if i  change this to iras right maybe we have multiples   i want to make sure that it gets changed in the  list of all accounts and that's going to happen on   change event so let's take a quick look at that  inside the admin here when we make a change to   either e8 through f34 meaning here e8 through f we  might affect change in income name or expense name   i want that list to be updated here right so  if i add an expense here and i call it let's   say water expense i want to make sure that that  gets here and i also want to make sure that it's   automatically alphabetized so that it's easier to  find and we're going to do the same thing here and   here right so if i decide we're going to add in a  brand new asset maybe we have a let's say equity   any kind of equity account right just put it in  there i want to make sure that it gets added in   here and it's also alphabetized great because it  is a dynamic range that will handle these accounts   so how are we going to do that well if the  user makes a change on any one of these   g8 through i34 well that would be for our  accounts here user makes change now yeah i   noticed that it will have it'll automatically run  here but it's probably not necessary but that's   fine too just an easier way to do that okay so  how do we do that well the best way to do that   is simply using it on change event so we're  going to create two macros both the ones called   entry type list and what's called account list  refresh so inside our module admin is where we   have both of those so this one's for go to  sheet i'll cover that in just a moment okay   that's simply the single macro that's going to  help us do that because use this navigation here   okay so for the entry type list the first thing  what i want to do is clear out a8 through 899   i want to clear all these out right making sure  that those are cleared out if we're going to be   creating or updating that list we certainly want  to clear it out now we want to keep in all types   and i want to keep in all accounts okay so what  we're going to do is the last income row and the   last expense i want to put those in two variables  i need to know that last income row which is 11   and i also need to know that last expense row  which is 19 and putting those in so variables   right here i also want to know the list row the  list row is going to be the last income row plus   one we're going to set up that first right so if  i the first thing what i'm just going to load this   ink i'm going to bring them over here i'm going to  bring them right here then what i'm going to do is   i'm going to get the next row available which is  b13 so i just want to put the last list row then   i want to bring this so basically it's going to  go like this i'm going to inside vba i'm going to   first i'm going to clear these out okay this is  how it works if we do it manually right then what   i'm going to do is i'm going to bring these  in i'm going to copy these in and bring those   paste those values in here then i'm going to bring  these in i'm going to copy those i need to know   the next available row which is 12. i'm going to  paste those values in then i'm going to take all   these and i'm going to sort them alphabetically  and that's going to happen automatically when i   make changes to anything here all right so the  first thing we'll do is get that list row then   what we're going to be doing is going to bring  over this income type just as i mentioned to you   we're going to take these income types here we're  going to bring them right in here so that's with   this line of code then the next we're going to  bring over the expenses i'm going to take over   all the expenses from fa throughout from the last  expense row and i'm going to bring them over to   the list row remember we've updated this list row  that list rows that first available row after the   income and we're going to bring it all the way  over into the last row so list row plus the list   row plus the last expense row minus state right  i need to know the last expense of course minus   eight we only want to know the total number of  expenses right if we know the last row is 19 right   minus eight we know that there is 11 or 12 plus  one right right so we want to bring over all those   and bring them over directly inside here to make  sure that they fit so we're going to bring those   expenses over any income once we brought them  over i want to update that last row just using   the last row then what i'm going to do if the last  row is less than 9 we're going to exit the sub y   if it's less than 9 because if there's only one  row of data or anything less we know there's   nothing we don't need to sort it but however  if it's larger then we do need to sort it we're   going to base that key on a8 after we clear the  sort fields we want it in ascending order we're   going to sort on normal and we want to set that  range to a8 and the last row and that's going to   automatically sort them very very similar for the  account refresh i'm going to do the same thing i'm   going to basically clear out all of the accounts  here bring in all of our banker asset accounts   bring them in here then bring in our credit  or liability accounts and bring them in here   then we're going to sort them accordingly exactly  the same as we've done here determining the last   asset row the last liability row determining the  list row bringing over our assets first bringing   over our liability second determining again that  last row in column b this time then sorting them   based on b8 so sorting them again async and that's  all we have to do so every time we make a change   here or anything right if we want to make sure  that we want to change it to mc right we want to   make sure that it's automatically updated in here  and again making a change here and that's going   to be really helpful for us when we do this it's  automatically going to create that list so we can   crab nice and updated lists of all accounts based  on what the user enters okay also in the admin you   saw another macro just a single line called go  to sheet and that's going to enable us to create   this particular really cool menu that's going to  allow us to go to individual sheets now the best   way to do that to use a single line macro is to  make sure that the shapes themselves have the same   name as the sheet when i click on the selection  here we can see that this particular shape and   this particular shape both have the name of  admin this one's called financial entries and   so is this one so you see the icon has the same  name as the shape right making sure that the icon   and the shape both have the same name when  we do that we can then use the name of the   shape that we've selected to automatically go to  whatever sheet by that name therefore we can then   have as many buttons as we want and as many  shapes as we want we can use a single macro   with a single line of code to create this really  incredible menu and that's all we have to do so   to do that we just use a single mat inside the  admin that single line of code is called go to   sheet we have assigned a single macro to all  these shapes inside this particular menu here   one single macro so if we look inside any  particular either any shape within that   we click a slime macro and we see that it is that  called go to shape right so it is this one and all   we need to do is take the name of the shape now  the name of the shape is called application color   all we need to do is click this workbook sheets  application color activate and all we need to do   is make sure that the name of the shape is the  same as the name of the sheet notice that this   name dashboard or entry right financial entries  here's the name of the sheets same here same as   down here dashboard financial entries or admin we  just need to make sure that they're exactly the   same and if they are then there's no issue with  that it'll automatically go to the correct sheet   okay very good so it's just one now keep in  mind that when you do run this macro from vba   you're going to get an error and that's simply  because there is no macro that actually call it   we called it from here not by clicking a shape so  make sure to run anytime we use application color   it is important to actually click the shape that  is tied to that right that shape must have a name   okay that's it so we've gone over go to sheet  entry last and lasher fest and then also we have   account entry list refresh this one is going to  account both of them so then i thought maybe we   might use it i don't think i used it though i've  got a single list here with all accounts and all   we didn't really use that but it could use it  in the future all accounts in all one so this   is all account entries so i created another macro  to combine all the accounts and all the expense   and all the income into a single list so but i  don't think we used it but there's a macro that   runs it here okay great that's it that's all we  need now what we want to do is we want to focus on   how do we get the results that we want now our  goal is to get the results inside this dashboard   especially in the net worth where we have some  calculations i need to know the total assets i   need to know the total liabilities and i need to  know the net worth now how am i going to get that   total assets right i want to make sure basically  i want to know all the assets in here i want to   know all of the assets associated with this we  can then let's go we can either high call keep   i'll keep invisible i want to know basically  based on all the transactions that we've entered   and the initial balance i want to know the balance  of the savings account so our initial balance is   25 505. based on all the transactions i want to  calculate the balance of that savings account   i'm going to use it in this sheet called pivots  and so we're going to be going over most of this   data but if we scroll over here we see that we  have something called assets and liabilities   and what i want to know is the bank assets i want  to know the total assets associated on this bank   so what i want to do is i want to link i want  to create a link so i've got a link here so   that means any notice that we change this so  any change based on the admin g8 all the way   down to admin g32 is simply linked so if i go  inside our admin screen and i look in our assets   g8 all the way through g34 actually should be  34 but it's automatically linked down there so   how do we do that so we're just going to use a  link so what that's going to do is any changes   that we make it's automatically going to be  linked then once inside the pivots i need a   formula i need to know what is the balance of that  the first thing what we want to do is of course   add in the initial balance which is an  admin h8 admin h8 taking a look inside here   inside our admin h8 this is the initial balance  h8 so i want to add that in plus what i want to do   well if we take a look at the financial entry  screen when we're using i want to determine   what are we going to add it right if i know  if it's an expense if i make an expense here   and i decide let's focus on that checking account  here if i make if i have a checking account i know   that's going to deduct right i know it's going to  be deducted right we have an expense we're using   our checking account that's going to reduce the  value of that checking account if we have a 100   expense as entertainment it's going to reduce that  balance by a hundred dollars however what if we   have an income account what if we have an income  type transaction and we get paid by the work bonus   i know that that's going to increase by 100  or whatever the transaction amount is here   what if we have a payment right if  i make a payment from a specific it   could be from anything right let's say we  make a cash payment to our checking account   i know that's going to increase our to account  i also know that if it's a transfer let's say   we make a transfer from one account let's  say from our let's take a look at from our   let's say we get cash let's transfer from a car  another channel savings account right down here   let's say we transfer from our savings account to  our checking account i know that that's also going   to increase so what does that tell us that tells  us that a single type expenses is going to deduct   from the account however income payments and  transfers all increase the value of that checking   account we're only focused on the actual amount  right some named rates are going to help us so   let's go over that one more time every transaction  that is dealing with expenses is going to decrease   our checking value however the three others income  payments and transfers are going to increase the   balance of that account so knowing that and  understanding that we've created some named   ranges based on this right it's going to be very  important i'm going to need to know the entry type   i'm going to need to know the account that's been  associated i need to know the income or expense   and i need to know the budge the actual amount  the budget's not going to help us for a balance so   the actual amount so we've created some  named ranges that are going to help us   quickly turn those into formulas that will allow  us to calculate that so we go into the formulas   and then name manager and we go into entry let's  sort by name here and we see they all start with   entry date we have one for entry date we have  one for budget amount which is not going to use   actual amount here so let's we have one name range  for actual amount and that's basically going to be   all the actual amounts here let's see entry  actual amount here i want to know what the   named range is for those actual amounts and so  we can see that there's dancing ants around this   actual amount let's go in here okay so we can see  here we go the dancing ants around we know that   that's actual amount i also want to know what  is the entry account what is the account that's   associated so here this one's called entry account  that's the account and i have another one called   income or expense right entry income or expense  type so these are very very important okay i want   to make sure so if i'm looking for something  i know that an income right if the income is   associated with a bank account i know we need to  increase that if it's transferred to that account   it's going to be and of course another one for  the account right we got that covered okay great   so knowing all that knowing that we have those  named ranges knowing that for asset accounts right   decrease with expenses increase with transfers  increase with payments and increase with income   we can then take a look inside our pivots enter  the formula that is going to help us calculate so   if we take a look at the savings account we've  got a formula that's associated with that now   let's take a look at that first of all we always  want to add in the initial balance from a and h   then what we're going to do is we're going to use  a sumif right i want to sum if the entry account   amount that's what we're going to be summing we're  going to summon excuse me the actual amount we're   summing the actual amount right not of course we  don't want to know about the budget amount we only   want to focus on the actual amount so we're going  to sum it based on this the account that account   must be what's in r right so that's the account  has to be that account i also want to know if it   does not equal expenses remember if it does not  equal expenses we're going to increase it we're   increasing it if it's not in expenses however  we're decreasing it for everything else so   minus right because this is the increase so  basically we're taking increase it if it does not   equal expenses we're going to increase it plus  whatever our initial balance is however we are   then going to decrease it if it's if it's expenses  we're going to decrease it if it's payments we're   going to decrease it and if it's transfers we're  going to also decrease that amount so each one of   these are sumifs based on the r5 and based on  that entry type whether it's expense transfer   or payment those are going to decrease it however  we could put equals income we could just put equal   to income here you know that's fine it wouldn't  matter so we could do just that it's probably a   little bit less confusing equal right so we could  just do income i think that's be a little bit   more clear same thing though you get the point  it's income if your income we're increasing it   and otherwise we're going to decrease it okay  so that's fine that's going to automatically   handle that and you can see that that's going to  take care of it's the same amount whether we've   done it or not and of course because r5 is not  absolute all we need to do is simply drag this   formula down here and that's going to cover us for  every asset type so for every single asset that's   how we get the balance making sure we're starting  with that initial balance and then adding it in   the total assets is simply the sum of the total  columns okay great so we've covered total assets   but what about liabilities now let's take a quick  look inside our transfer here now a liability is   something we owe right we know if it's an ex let's  say an income right let's say we make a transfer   right let's take a look here liability accounts  an increase in a liability is something that we   owe right so if we have an expense account  we'll start out with the expense account   and let's say we are going to buy groceries and  we buy it with our credit card let's take a look   mastercard we know that that's going to increase  the balance of our credit card increase right for   if we have the payment for paying our groceries  with our mastercard that will increase the balance   that will increase the liabilities of what we owe  right okay great what about if it's an income i   don't know necessarily think an in income right  if we have an income and let's say we have a work   bonus and we pay that we send the money to our  account credit card well that's going to reduce   the balance right it's going to reduce the balance  if we transfer from our bank let's go ahead and go   to transfer here if we make a payment that makes  a little more sense if i make a payment with my   checking account and i pay that mastercard  bill that's also going to reduce the balance   however if i also make a transfer we can use the  transfers if i transfer money from my checking   account to my mastercard that's also going to  reduce the balance right so basically again just   the opposite if we have income if we have payment  or if we transfer that will reduce the mastercard   if we have an expense that will increase the  liability okay so we have three different ones   three different transaction types that reduce it  and one transaction type expense that increases   the balance increasing the balance is what you owe  so knowing that understanding that we can then go   into our balances and we can take a look at that  now keep in mind that gets a little more tricky   because we have additional types right so we'll  go over that but basically what we want to do is   i want to look here we're going to use of course  we're going to start out with that opening balance   this opening balance is in j8 for our first  account inside admin we see that j8 contains   that opening balance for that car loan that's  what it started off to be as we make payments   it will reduce right if we take out more money  from the car loan that will increase okay so what   we're going to be doing here we're going to use  sum if again we're using sum if we're entering the   actual amount right and we're going to be based  on the account in which account the common beat v5   if it's inexpensive we know we're adding it right  so we're simply if it dispense right we're adding   let's say we're buying dinner with our credit card  that's going to increase it it's an expense that   we're adding to that balance however we're going  to reduce it if it's an income as we mentioned   we're going to reduce it if it's a payment and  we're also going to reduce it if it's a transfer   however right there's a few we're going to add  to it if it's a payment right remember if it's   a payment and we also want to make sure that is an  income expense right if the income expense equals   v5 we want to make sure we're making a payment  on that right or if we make another transfer   we're going to increase that and that means the  income expense account is b5 what does that mean   well if we take a look inside the financial  entries here and we take a look inside here   if we decide we're going to put this account  here we want to make sure that we understand   let's go ahead and transfer use the transfer  payment payment let's see let's say transfer   right but what if we're using this account  to transfer right that mastercard right   we're transferring it let's say we're taking money  out of a car loan let's say let's say we have a   home equity right and we're transferring money  from that to our checking account we're taking   more money out of it right more money and we're  putting it inside our checking account maybe we   need additional money so this also will increase  the balance right notice that the transfer   when this is located here in our income types  this type of transaction will increase right   increase so i want to make sure to cover that too  when we're using it on a transfer and it's here   home equity this we're taking money out of that  this will increase the debt which will increase   the balance on that particular liability  account because we're taking money out   now we owe more because we put more money in our  account so i want to make sure in these instances   we're actually growing increasing that balance  so that is exactly what we're going to cover   inside this formula right here we're going to  increase that balance so if it's transfer type   here and our income right here here it's starting  here if it's payment or if it's transfer and the   income or expense type is v5 meaning we we're  using the car loan as that income or expense   tax we know that we're increasing the balance  whether it's a transfer payment so we're using   this transaction payment payment would be the  same here if we're using this payment exactly   the same there's no different here if we're  paying with our home equity into our savings   account or paying somebody else that's going to  increase that liability count because we're taking   money out let's say we have equity so we're  going to owe even more so those instances it   also increases good i'm glad we got that covered  so then again all i need to do is simply copy down   it copy down it because we know that v5 is not  absolute it's relative and it's going to copy down   the total liabilities is simply going to be the  sum of all that in that specific column now that   we understand how we calculate the total assets  and the total liabilities we want to determine the   net worth well the net worth is simply our total  assets minus our total liabilities so we have here   it's simply going to be s 3 minus w3 that is going  to be our our basically our net worth now what is   our goal our network goal is going to be located  in m15 what is the goal that we're trying to reach   well we set that up directly inside here our net  worth goal is located right here our net worth   goal is basically we know we want to increase  assets to 500 000 we want to decrease liabilities   what we owe to 50 000. therefore the difference of  it is our net worth goal of 450 000 located in m15   so we know that our net worth goal is going to be  located right here and we know that the difference   is we need 194 119 to reach that goal and it's  simply the difference between the net worth goal   and the net worth what about our total assets  right our total assets are basically linked to   here we've already calculated how what about our  asset goal what is the asset goal it's 500 000   right we're trying to reach assets of 500 000.  we want to reach it by january 1st 2023 so this   is our goal here located in m13 so if i know our  goal we can simply link to that located here m13   and we need to know we need 172 000 to reach that  goal likewise our liabilities are basically linked   to our total liabilities here we want to bring our  liabilities down we want that lower amount we want   to owe less money to do that we know we want  to bring them down to 50 000 we need to reduce   our liabilities by 22 000 and of course this is  linked to m14 which is our liabilities right here   we want to link we want our decreased liabilities  to 50 000. so we need to reach goal we need 22 000   just simple math to get that so we can set some  very clear goals okay so that's everything on this   section now also it's going to help us inside out  we've created some pivot tables i've got these are   associated with expenses and if we scroll way down  here these are associated with our income income   down here so we created some pivot charts now  of course i have a pivot table i've got a table   that's been set up here inside our entry database  if we take a look at the table design we see that   we've got entry data entry data is the data that  the table that has been created automatically that   will help us create some of these pivot charts so  all i'd simply do is just insert some pivot charts   pivot tables here along here and i've placed them  directly inside here so let's take a look at the   first one the first one here is our particular one  for expenses i want to know the difference between   actual and budget expenses right so if i  take a look inside here our show a field list   we see that we want to know these expenses so  i'm going to bring down i want to know the entry   type and the entry date right into filters right  because i want to know only expenses right i only   want to focus on expenses so if we're going  to choose all i'm really focused on that so   we're going to choose just expenses for this so  i've got to filter it out to just expenses how   do we do that we'll just making sure that we've  selected expenses on those filter types i don't   want income i just want expenses okay all right so  we've got that and i also want to know the entry   dates all will set all for now but having those  entry dates can help us focus on the timeline   we want to build out this dashboard i want this  really cool timeline and having that data to be   able to filter is going to help us okay so i want  basically columns of actual and budget right so   our values are actual and budget right so i've  just brought down here we see the budget amount   just bringing down the budget amount here and the  actual bring down the actual amount okay going to   bring that down here now i want the rows to be the  income and expense type right i want to know those   types that's going to come directly from our  data here i want to know all the equipment   expense types here based on that data and i want  to show them of course only four expenses here so   that's going to bring in all the expenses based  on only those expenses for the period it's going   to sum up i want to sum up right using the sum  right here again looking down here we want it sum   right so if we take a look inside here oops sorry  it's all in the value setting sorry it's off that   but i want to sum up the actual right and we're  going to do the same thing with budget we want to   sum that so that's going to get us our sum values  both on the actual and the budget and it's going   to give us a grand total great moving on i want  to set some expenses right so we're going to tie   this to some charts very very soon but we might as  well go over everything here on the screen right i   also want to know some june expenses this is going  to be important because when we take a look inside   our spending i want to know the total expenses for  june right i want to know them so how do we know   that well we can take a look inside our pivots  right here and i want to know the monthly budget   first of all what's our monthly budget right  our monthly budget is located however we set our   monthly budget to what do we know for our budget  well our budget is based on all of the items that   we've currently budgeted right but that's going  to be based on our budget amount remember we've   got a named range for budget amount based on  our data so we're going to use sum if again   and i want to sum if all of the budget items this  is we're going to be summing the budget amounts   and it's going to be based on entry date greater  than or equal to the year of today right greater   than the current year the current month first so i  want to notice on the current year i want to know   the monthly budget right so how do we know the  monthly budget based on the entry date what is   that entry date let's take a look inside that and  the entry date is going to be basically the end   of the month so what i want to know is the monthly  month but i only want to know for june right only   for june and then basically this i want to set  because this is the current date so whatever the   current month is i want to show that up i want to  let the user know okay whenever they're looking   at it it's going to show that current month so  i want to know what is the current month budget   what is the current month right so we're going  to set it so how do we set that current month   we can simply use the date entry the date  function here the current year the year of   today the current month the current month of today  right and then one meaning the first day of the   year so i want it to be greater than or equal that  entry date then the first day of the current month   all the transactions that are associated with  that current greater than or equal the first   day of the month and also less than or equal  to the last day of the month and we can use the   end of month to do that so end of month again  setting the year to the current year setting   the month to the current month and then zero  meaning i want that exact month no months before   no months after and also we want to make sure that  we're just focused on expenses nothing else so   entry type must be expenses and that's going  to know all of the budgeted expenses so we know   that our budget expenses are 21.45 and now what i  want to know is the difference i want to see how   we have we gone over the budget or not right so  our current expenses are 22.45 we've gone over the   expenses now this is almost the exact same formula  except this time we're summing the entry the   actual amount whereas this one was the budgeted  amount right everything else is the same right so   the difference is simply the difference so that's  we understand that our monthly budget is 21.45   our current expenses but we also have some goals  for june right so we know what our budget amount   what is our goals right if we take a look at  our admin we want to make sure that we have the   reduce the total expenses to 2500 right i want  to make sure that our monthly expenses are less   than 2500 to reduce them too so our goal is  to get our expenses less than 2500 and to   do that all i want to do is i want to know our  expense goal this is simply linked to the admin   and 8. just simply linked to here m8 great so  if we continue on here we want to know what our   current expense is on this is the same formula  is here no difference on these two farmers   so we know that we're allowed to spend 255 dollars  more this month right and it's still going to be   under our goal so that's it and what about our  expenses for this is just another exactly the   same formula i've done these a few things june  expenses because i've linked them there so this is   our current month expenses our current month goals  and where we are are we under the budget remember   the budgeted is simply a sum of all of these we're  focused on these budgeted amounts here what we've   added in the budget amounts and so we can just  tell the difference between our budgeted and   also between our actual and our goals for that so  we set some initial goals i also want to create a   insight inside our expenses here i also want to  create a monthly expenses so i know the monthly   expenses and we're going to need to know a pivot  for that too well that's kind of important all we   need to do inside this let's bring this up save  a little bit more space here and all i want to   do is i want to know basically by date right so  the first thing we want to do is we're going to   be running a filter both by type and by date  again the reason we want to filter out by type   is because we're still focused on expenses i still  want to make sure that filter is set for expenses   only okay if we were to clear that filter it  would show i don't want to show the clearly filter   clicking okay it would show all of course that's  more but i really want to focus on expenses so   by selecting only expenses we only going to show  those expenses and i want to show them by month   right so we've got the rows in the year and the  date is months all i want to show is the months   associated right i want to show the values in the  columns and i want to show both the values of the   actual and the budget and the columns actual in  the budget this is going to allow us to create a   basically a line chart for that so if we want to  insert a line chart we would just do insert a line   chart like this and insert a line chart and that's  going to create basically a simple line chart and   then we customize it to fit into our dashboard so  this data is going to be used for our line chart   inside our dashboard right here and then we've  customized it from monthly expenses okay great so   moving on so we understand how we're going to get  the pivot data so we understand both how we can   display both the actual and the budget on a per  month basis the dates are going to be all unless   we select the slicer which is going to update and  i also want to know what about the year i want   the annual information for those expenses inside  this i'm going to and i want to show them directly   inside here so how are we going to do that right  so we can do that very easily using our pivot data   but this time i want to summarize by year so here  we're going to have the same thing we have actual   i only want to know the actual in this case not  the budget we could easily add the budget as well   simply if we decide we're going to be putting the  budget in we could just drag the budgeted down   here and that would show the budgeted and then of  course that would make we'd make adjustments now   they're going to show budget and we'd update that  but i just figured the annual expenses and are   fine enough right so notice it would show both if  we want budgeted here so what we want to do is we   can remove that just right click and then sorry  it's off the field just remove that so that we   only show those actual right so we know then 2020  so that's going to allow create the data and then   of course i would want to do is insert right and  we could just click maybe we want to insert one   of these 2d column little column chart not the  bar but column right maybe i just want to enter   a simple column and then i would want to customize  that based on this filter so color we'll go over   that in a little bit but i'm going i'm using this  theme right down here and then customizing that   accordingly so how would we do that well of course  we only want to show certainly i want to show some   access titles maybe i want to show the chart title  but certainly i don't want to show the grid lines   so we can get rid of those we may not need  to show the legend we can get rid of that   we may want to hide right so we may want to go  to home and i want to format that accordingly   let's go to the format and maybe hide that right  i don't want to fill on that so no fill on that   and we also so we can hide that a lot so we've got  some customization we'll go over a little bit more   when we go over to the dashboard how are customers  but basically that's how we create that particular   chart so we can easily based on the pivot table  data very very easily great now that's it for   that the expenses now for the income we have it  here income is relatively simple it's exactly   the same pretty much except now we're the  filter is going to be based on income type   june income here we're focusing on only those  with their income right so here we're going to   use the type as income here the current income  this is for the budgeted amount we only want to   know the income for the budget amount we only  want to know the current income for the actual   amount so we want to differentiate and then the  budgeted difference here income goals are going   to be based on m7 our current income same thing  just what we added in here current current income   oops forgot to customize that that should say  income right here not expense right copy that over   okay so that's income there we go that makes a big  budget of difference eight thousand right if we're   income goal 70 we did very well this month right  we have a difference of 10 700 so we're on that um   probably we should equal the budget of difference  right it's for if we're over budget or our current   income minus our income goal so if we know we're  under or over we can create that would be a   positive effect right so we have 10 000 positive  effect great so i like that that looks really good   so we're going to simply going to add the current  income based on the actual amount based on income   types or based on the budgeted amount here here's  our budget amount based on the income types okay   but our goal set so what are we different we're  ten thousand seven hundred above our goal which   looks good and we've also set our june income  just in another one june income great so again   we also have the monthly i want to know the month  income per month exactly the same as we did with   the expenses except this one we're filtering  it by income and not expensive same thing with   the annual here just focused on the income on an  annual basis just like we did with the expenses   great so that's it for all the pivot data right  so now all we did is simply create charts based   on this pivot data and that's exactly what we're  going to do now into the dashboard so our third   our second part oh i forgot one more thing inside  our asset and liabilities we did create some pivot   tables to show this and i created some pivot  tables based on the data here so here i created   another table and we see this is called the asset  data and we see that this is called the liability   data so i've got two tables associated with the  data creator and then i created two pivot charts   here based on the total so basically i just want  a very simple pivot chart based on this so that   we can create different charts on the on our let's  see on our income here if we go into the dashboard   we see that we have inside our net worth  we're going to have two assets by accounts   and liabilities by account so i need to show those  so these pivots are going to help us both with the   assets by account and our liabilities by account  that's it okay so that's it that's all we have on   this sheet great so now we've covered of course  how to enter the data how to evaluate the did it   calculate the data now we're going to go into the  third and final part which is presenting the data   and that's going to come inside this dashboard now  i notice that we have this three tab support this   is very very cool so in this case if we view the  headings here we can see that we're not actually   changing any rows or any columns everything stays  the same and that's because we're not really using   cells to present the data we're using shapes and  we're using charts and graphs so we can simply   group them together and hide or unhide  them using this tab effect when we select   on cell d2 we're going to show income when we  select either on icon this icon or this cell   we are going to show this group of shapes and when  we select now we're going to net worth we're going   to show this group of shapes great so how do we  do that well it's going to be on the selection   change event so if we go inside here we typically  inside our dashboard we're going to just a little   bit based on the selection if the user selects  more than one cell we're going to exit this up if   the user selects from d2 through f2 we are going  to then run macros now if they select d2 i want a   specific macro that is going to show up directly  let's take a look at that if if they select d2 we   want to show a specific group or we want to set a  group or specific group here okay so how do we do   that we do that with macros obviously so if they  select d2 meaning the income i want to run this   macro dashboard income if they select e2 dashboard  expense f2 dashboard net worth if we take a look   inside this module called dashboard macros we  have just those three very very simple macros so   what are we going to do well we've created a group  of charts and shapes for income i've got a group   called income group for spending i've created a  group called expense group and for net worth i   have a group called net worth group okay now if  we take a look here we also see some really cool   effects we see that this tab effort we see that  the border is then gone and then we also see the   background of this cell change to lighter right so  the non-selected cells are changing and we can do   that through conditional formatting and based on  another cell so if we let's unhide columns a and b   and they're just reduced and so a and b here we  see that the selected column is 6. so i've created   some conditional formatting that's going to help  us if i change this to 4 it's going to show income   if i show changes to 5 based on the column it's  going to show spending and if i show 6 it's going   to go change that sorry i know i got that off  whack but we can move that back here let's go   ahead and hide this so we are i'm going to reduce  these down just so we can see that we should not   have this move inside those cells i'm going to  fix this right we don't want this shape this   should not be moved i'm going to again hide these  here and i'm going to adjust this this particular   background this one here we certainly wanted  we don't want it sizing with the cells but we   certainly don't we don't want it moving so i'm  going to select on this background here if i can   grab that got so many different shapes around here  inside the properties what i want to do is i want   to then select that and just make an adjustment on  that here going into the format shape or size and   properties either one would work right here what  we want to do is move but don't size or so this   is the one i want to select to make sure that  that shape now when i expand those two columns   right we want to make sure that is going to move  automatically with it which is exactly what i want   okay so let's take a quick look back in here again  now that we've got that set up and we see that the   selected column we've got conditional formatting  set so when i make a selection vba is going to   set whatever column in b2 if i select income it's  going to set that to 4. spending 5 and net worth   6. so conditional formatting is going to help us  out with that if we take a look inside the home   and we manage rules we see a single  conditional formatting based on b2 the column   and what that's going to do is going to set that  offset color that fade out color when we click   fill it's going to set that fill effect from  a darker to a lighter green it is that lighter   green that we want to blend it to i also want to  remove the bottom border that bottom border is   going to be for that selected cell naturally has a  border but conditional formatting will remove the   border it naturally has this darker color on the  background but it is conditional formatting that   is going to add it automatically in okay great  let's move this over here bring this back over if   it's sometimes if it's moved a little too far over  the left it'll move in size with the cells okay   and so now okay so we have some different  information here now we're going to move that over   so we're going to run these macros and all that  macro does is simply very simple for the income   macro all we're going to be doing is displaying a  group called income group for the spending we're   going to just show this group and hide the other  two groups called expense group and that's all we   did inside this macro so we're going to set the  column to b4 for the income we are going to show   the income group and we're going to hide the  other two expense we're showing column 5 in b2   we're going to show the expense group and we're  going to hide the other two likewise net worth   6 is the column we're going to set and then we're  going to show net worth and then hiding both the   income and expense that is it for the macros very  very simple on that okay so that's going to get   us this really cool tab effect very very cool  and it's going to show a lot more information   as we needed to okay so what do we want to show  let's start with net income right i want to show   that june income here so i've created just a  little bit of shape and i want to know this june   income remember we've calculated it automatically  and i want to show this title but i want this   title to be dynamic so when the month changes  to april may june july whatever the month it is   i wanted that month to show up here whatever that  current month is here so we want this text box to   be dynamic and i've linked it to pivots f61 so if  we take a look down here inside pivots we scroll   down to f61 we see that it's going to be linked to  right here f61 and how do we get the current month   right we're going to tag use the text because  i want to format it i want the current date so   that's going to show the date but i want this cell  formatted right now i want this value formatted   based on only the month in other words i don't  want to show the day i don't want to show the   year i only want to show the month and i want  to show it in the long format which is 4 m's   after that i want to show a space and then income  that's going to give us the current month and the   income and then i also want to get that total  remember we had that total here which is total   balance so basically if i link those two values  to this cell inside the dashboard so notice this   value is that inked to pivots g61 pivot g61  is this one right here so we've linked that   and then just formatted it accordingly and just  given a large font and put it on this background   great so that's how we get the income now what  about income by category well this is going to   be based on set amount of data if we have it  it's going to be automatically linked to this   data here this data right here we bought both  the actual budget so we were able to create that   based on that so we just created remember a bar  chart if we were to do it now we would insert   right what type of chart do we have let's say this  bar chart right here i want to enter a bar chart   and basically customize it according to that so if  we want to color it give it a specific theme color   remove the background we can remove these things  by hiding this and we get a lot closer to what we   already have inside that so it's very very easy to  customize that right here so we don't need that we   can remove that and so that's how we got it right  in this dashboard and that's all we did then we   just formatted this accordingly and given it so  we've got budget versus actual income okay i'm   going to use the same exact pivot chart but this  time i want to show it in a pie chart right so if   we go back into the pivots here we've got them and  we decide we're going to enter a pie chart i want   to enter a pie chart for that again we've got the  pie chart again all i want to do is hide all those   fields and buttons we have information we can set  the color to the theme that we're currently using   fourth one down and we get an idea and we can  customize the header we get an idea exactly how   once we have that pivot data it's very very easy  to add in and we just placed that directly in here   and added some leader lines and then did a little  bit of fun okay very very cool but now we've got   some information here i want to show the budget  difference i want to know how are we doing on our   income right i noticed that our current income is  18 000 and i know this our monthly budget is 13.   so we're doing very well with the income on this  month we've made a lot more right then we have 14   4 435 more based on our budget right our monthly  budget so our income is doing very well and as   far as our goal it's doing really good we've only  our goal is only 7 500 a month but we're way above   that so notice that we have that information right  so we have the june income again here again we've   got june income here we've given a thing this is  also based on f51 so we have that automatically   dynamically linked right and it goes we have gene  income f56 and f51 also linked to the pivots here   f56 here f51 and f56 you're again linked again  using the same formula using the long arch and   then income and then also you have june income  goals here again the long month and then income   goals so this will change based on the month when  the month change this will automatically change   so will this automatically because we're using  these formulas which we already went over   okay so we know the monthly budget we have all  the information so all we have to do is simply   link this notice that this difference is this  difference is linked to g54 we already have that   difference here g54 is linked to there we know how  to calculate that so we understand the difference   and i've just created a bar chart based on this  information here and that bar chart's been placed   here great and we've done the same thing with  goals goals of course is linked to g59 and of   course we went over this this is linked to g56 and  created a bar chart based on the results located   right here so we created a bar chart based on  this so if we were to insert a bar chart again   just using that bar chart here entering a 2d bar  chart we we get that we get the income total and   we can see we can customize it from there  relatively simple on that once we have the   data in a place that we wanted so we put that  directly inside here notice they're all grouped   into one group making it easier to hide and show  this entire income group when we change tabs then   again we got a monthly income we went over the  sample for this how to create that that's again   based on this pivot data directly inside  here this monthly income we've got both   actual and budgeted here and then we have that  linked directly to here lastly we have the   annual income annual income again based on this  annual income here so we can show it here once we   tie these to a specific timeline which we have  if we take a look at this timeline here oops i   move this over a little bit much and we'll bring  it back over let's extend this about to there   okay so now i've got a timeline if i want to show  these based on the timeline i want to show based   on a specific year if i select on this timeline  they're already linked so if i right click here   this timeline right and i decided we're  going to create the report connections   i want them connected to the expense details  the income details the monthly expenses and the   monthly income right so i want them both this  so they're all for exchange right so that way   they're automatically changed so if i select 20  select year it's going to show only for that year   if i select a specific month right i know that  it's only going to show me for a specific month   just the reports that i'm right this is for total  june's current month but this income breakdown an   income by category i wanted to very specifically  only for a specific month okay so again this one   this particular timeline also tied to and of  course we're only showing the muscle and that   also tied to the same four different graphs here  these particular pivots are both also tied to the   pivots right so we've created these pivots here  and they're tied specifically to these pivots so   that they can all right so if i change the entry  date here the pivot's also going to change right   so if i change it here it's automatically  going to change but if i want to select all   i can do that and i can clear the pivot data here  now i've removed a lot of the information here   but if i want to select an entire year i can  select just the entire year and it's automatically   because they're linked to the same exact pivot  tables all right very very cool so we understand   the timeline and we got that we've got our sold  items and we've got our information but this is   covered up a little bit so we need to bring this  up we can add more information here there we go so   it's just let me just a little more data a little  more space there okay great now spending is almost   we don't need to spend a lot of time on spending  because it is almost identical right june expenses   june income everything is exactly the same except  we're using the pivot table information if we look   up in the pivots and we scroll up everything is  exactly the same remember the only difference   is we're filtering by expenses here we're summing  expenses the monthly expenses the annual expenses   so we've created exactly the same charts but  we've linked it to the expense data here so   inside the dashboard everything else is linked  to the expense data exactly easy net worth okay   so expenses there's no difference other than  we're just using the expense data instead of   the income data very simple except it's that net  worth quite a little bit different there's a lot   of good things going on now we understand how we  got all the net worth data and now the data that   we've calculated got we're now going to link it to  our net worth tab here inside our net worth here   so what i want to know is i get some text values  total assets total liabilities and net worth now   the total assets we've already calculated that  this is just the text field located to pivot s3   so when we go into here to pivot and we take a  look at s3 we see that that's our total assets   right total liabilities located in w3 net worth  located in u2 so inside the dashboard located here   w3 and our total assets to u2 so we can easily  just link some text boxes to that so we understand   that all right but i also like to show these  really cool bars right this timeline this is   going to really help us so how do we get this  really cool graph here well simply this is if   we take a look and we were to insert this based on  that so we've got our asset goals here these are   asset goals and our liability goals and our net  worth goals so inside our pivot i've added them   here remember our net worth goal here and our net  worth here so how would we do that well if our net   worth here and our net worth goes here and we want  to create as a 3d stack bar so we're going to do   insert right here and we want to look inside the  stack right and we want to do a 2d right or 3d   stacked bar right so we've got 3d 100 stack bar  okay so this is the one we want to create and then   we've got some options here so we want to format  what kind of do we want to do so the chart design   we've got different options here so what kind  of things are we taking a look at if we just go   ahead and format that and take a look at the chart  area we've got some different information here so   we're going to look at here and what i want to  do is i want to create basically a tubular bar   cylinder is what i want to do and then we want  to remove the depth on that so i want to remove   all the gap and i want to overlap them so how do  we do that so notice that they're not overlapped   yet then what we want to do is we can select  the data right we need to combine them right   selecting the data here we see we've got one  series here we really need two series we're going   to focus on two so if we add another series here  let's call it net worth goal right select here   what is that series value i want to select here  if we take a look at that then we now we have   two series so we see that we have that but we need  we got too many series now so we can go inside the   select data again and just remove one of them or  edit the other one we can edit it and say we have   series name this is going to be the net worth and  we see we want a single point of interest right   it's going to be this point right here we can  clear us clear all that out here and select there   okay great so now we have both and we see  that they're now combined here in this   and basically all we need to do is then just  customize it according to our color so we've   got it here and then we just set our color of  course we're using the theme here that theme of   course is going to be that fourth color down here  and that's the theme and so basically then we can   of course we can color them independently if we  want to so that's how we get this cylinder and we   can go back into the dashboard so we understand  how we got this cylinder i think i've colored   this one a little bit lighter here just  giving that that one a little bit lighter here   giving that a solid feel if we go to automatic  we'll see that it's that darker color but i wanted   to go with a solid fill that's going to set up  that lighter color of course we can change the   color as we want here so we understand that's  it and then i've created these labels these   labels here are automatically based on the  information located right here so we notice   that our asset goes 327 is current our goal is 500  000 that's going to come directly from our pivot   table right so we see that our assets current  assets our goal is 500 000 linked directly to here   i've done exactly the same both with our liability  right link created a bar here we can close this   out now created this bar here this stacked here  one and it's of course 3d with the tubular effect   here and the liability is also based on that now  liability we want to make sure that our goal is 50   000 so reversing our goal but our current is 72  000. so notice that they're reversed and that's   going to be based directly from the data available  right here these two information total liabilities   72 000 liability goal 52 000 so here and then  lastly we have our net worth goal of course it's   our goal is 450 000 we're currently at 255 000  that data is going to come directly from here   okay very very good so we've covered that  and now also we have some information we got   assets by account and assets breakdown that's why  we created the additional pivots down here below   the assets here so we can create both of course  bar charts we've shown you how to do that   right the bar charts very very simple in  here based on that very very quickly and   then customize it accordingly along with the pie  chart also with the same right inserting right the   pie chart here based on the same information  and then just customizing according to color   and then theme right okay so we've shown that to  there and that's all i've done so this four assets   this for our liabilities that's going to allow  us to create these two this one here and the   liabilities here that's it that's all we have  done so we've created the liabilities and then   just updated them giving them a nice look  and feel very very cool that is exactly how   we get this incredible dashboard income spending  and network simply by hiding and showing these   in this particular training we covered three major  parts of this we first covered how are we going to   create a very very user friendly that allows us  to quickly enter not only a single data entry   but multiple data entries with just a click of  a button and have that organized in a very quick   manner an easy user friendly manner where we're  filtering by the entry type or anything and then   a single click to get us to all the transactions  associated with that very very quickly and   in whether it's entering a single entry or  multiple entries based on date or very different   frequencies whether it's weeks months or years  we then also showed you how to work with the data   creating this table inside this and then creating  additional pivots so that we can quickly and   easily create a very powerful dashboard using all  the data that we've calculated inside this pivot   screen generating this really incredible dashboard  this multi-tab dashboard allowing a user to very   very quickly know exactly where they are on their  goals for financial plans i hope you have enjoyed   this training i bring these to you each and every  week please don't forget to smash the like button   comment below i do answer every single comment  i'd love to hear from you what are your ideas   did you like this training what would you like  to see in the future and of course don't forget   to join our patreon platform where i'm going to  have a lot more and of course additional trainings   additional resources downloads and discounts  thanks so much and we'll see you next week

Channel: Excel For Freelancers

Views: 93,338

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, Personal, Free Financial Planner, Excel Financial Planner, Free Finance planner, Free Excel Financial Planner, Excel Financial Planning Application, Finance Excel, Excel Financial Manager, Excel Budget Manager, Budget Expense In Excel

Id: 5jxe1ui-VLQ

Length: 123min 12sec (7392 seconds)

Published: Tue Jul 05 2022