Saturdays I create basic VBA training so you can learn your VBA skills and brush up on them and eventually create incredible applications like this and then you can do whatever you want this template is absolutely free all you need to do is click the link down in the description just look for the word download however if you do want to purchase all of my templates I have just made all of them available in fact there's over 300 50 templates available to you all you need to do is just click.
The link look for the 350 workbook it's on a launch promotion now so I hope you'll do pick that up that's a great way to support the channel there are other ways to do that such as YouTube members patreon where there's a whole lot additional training and workbook templates so you help to jump on that all right so this is what we're going to be doing we're going to be creating this incredible dashboard it's going to list all of our accounts here each one has a graph we've got our total assets and our total liabilities we've got a list of our current assets and their values list of the current liabilities we also have.
Some six-month rolling assets and six-month rolling liabilities along with the net worth and how much the percentage we can set our goals on net worth we also have the recent transactions and we have a rolling net worth the last 12 months down here so we're going to be creating all that also we've got a popup user form that's going to allow us to load existing transactions or we're also able to add new transactions in here so that's going to be really great and I'm going to show you everything on how to do that and of course we're going to go over all the code and we're going to be creating this.
Sheet blank all right so let's take a quick look in the admin we've got some asset types this way it's fully customizable you can add in your own asset types your own liability types which is really helpful we have some assets here a list of all your assets and a list of all your liabilities we have some information here that's going to allow us to increase or decrease so we need to understand that cash is going a deposit going to increase and I'll show you exactly what that is in the user form and then we have our net worth goal here let's say we want to reach 1 million it shows us how far along we are.
Sheet and it does have a transaction database but we will be developing the dashboard from scratch during the entire video all right so let's get started on that the first thing what we want to do is I'm going to save the first two columns for admin although actually in this video I'm really not going to use it but there may eventually be a reason for that so I just kind of like to do that now what I would like to do is I would like to color that background black and I've got actually a background image IM for that ready to go and it's just basically a little bit of a faded background that's going to do that so.
I've got Bel background it's PNG right here so that's going to automatically make the screen black which is exactly what we want now we're not going to use grid lines in this so what I'm going to do is I'm just going to set the view here and we're going to go in and we don't need the grid lines and we probably don't need the headings in here actually I'll keep the headings for a while and then we'll eventually remove them great now let's go to put AR title what I'm going to do is I'm just going to actually hide these columns for now now we don't really need to use them in this particular training we're going to go to the admin the only reason is.
Because I've got this logo and this all setup kind of for us so I can just bring that over here and I'm going to paste that in here of course this is going to be our personal wealth management so we can put in here and then I'm just going to write that in here so in capitals personal wealth and then manager very good and we'll bring this over to here this is a little bit of a logo that I made through mid Journey all right so what we have is like sections and we have a button so let's go ahead and to the button first and it's just a basic.
Button I'll use the rounded Corners here that's going to be our theme I want to maximize basically what you're looking at here is the maximum screen so I'm going to put it right about here up on the right here we're just going to call this transactions and then what we'll do is we'll format that so transactions and now what we want to do is I'm going to set the shape here a little bit higher so we'll set the width to let's say 1.3 as we need to have room for a logo I'm going to put in the middle and right justify that as I want room for that middle now what I would like to do is going to enter the icon.
For that so I'm going to do the pictures and then place over cells in this device and then I've got some icons here just one in this training actually and it's here and we'll just set that to point two and that should be sufficient great what we're going to do is when we click that we want that user form to pop up and also we want to format this button according to our theme we'll just set shape format and we can use one of the theme colors here I'm going to pick this one right here so what I'm going to do is I'm just going to group them together like this here and put them in the middle and then group them here and what.
Posts Related:
- Excel Today - Python 3 Intro to Data Types
- Seamlessly scale unattended robotic process automation with hosted RPA bots
- Power BI Dashboard: Employee Benefits Dashboard by Kyle M. Power BI Challenge
We want to do is use control one in case for any reason I do move the columns or rows I don't want to change the size of
That button so I'm going to set move but don't size now what we're going to do is we're going to build the framework out the background image of it so we can get an idea so I'm going to insert the shapes I'm not going to pin it just yet because I kind of want to make sure that I maximize the size because we've got a lot of information so right about oops I don't want to go too far I don't want to scroll beyond that visual area so that's about it like this great so I've got that now in this area we're going to.Make this black using this so the shape fill here is going to be just the solid black color which is going to be this one and I'll put no outline on that so let's oops let's get that to Black and what I want to do here there we go I want to make sure that we have enough space for everything on our and we can bring this in cuz we don't need to occupy that column there so what I want to do is I want to have a bunch of these background images these are our placeholders where we're going to put it so I'm going to cop copy that using contrl C and then I want to put right about here I'm going to put the current.
Assets and we'll size that a little bit more a little bit later on and the current assets are going to go about to like right here what we'll do is we'll go in the format that shape I'm going to do about 9.2 and then we will do about 2.4 on that one so we'll set the width to about 2.4 because we need to make sure inside we're going to be putting our assets and our liabilities in there so we need to make sure that it's large enough to accommodate all of that information and we'll go ahead and pick pin this right now so in the shape and I'll click on this and I'll pin this first because we'll be using it often as we organize.
These shapes this one we're going to make in the width we'll do 5.65 and we're going to be duplicating that and 4.07 I'm just checking my sample that's why it's kind of exact so I'm going to duplicate that this is one's going to be for our current assets actually before I duplicate that let me just set something up I'm going to call this current assets and then I want to Center this one just the way I want it and get it sized right so I'm going to use control1 here and I'm going to go into the text options here and I want to make sure that we have everything that we need for that we don't need any of.
These margins here I want it on the top and I want it in the middle very good so the top in the middle I also want to make it bold and we can increase the font to 12 very good so now that I have it just the way I want I want something for the current liability so I'm going to copy it and paste it now we can just bring it over here so now we can set those in the middle here and this one's simply going to be called current liabilities and this is where we're going to put our bar graphs in here for our assets and our liabilities very good next to that I also want to have one for our Network so.
I'm going to duplicate that using contrl D and we're going to bring it over here and we can slide it over here and then we're going to have that right under about the current transaction so we're just going to bring it up about like that and then we'll go ahead and set the size here we'll go into the shape format and we'll do about 2.4 then 5.9 yeah that's pretty good 5.9 very good I like that the way that looks put our current liabilities is right there I want recent transactions so I'm going to copy this and I'm going to paste it I want the recent transactions to go here and we need this a little bit larger so we'll bring this all the way.
Down to about right here and then here we're going to put in what's called recent transaction so recent and that's going to be inside a list box which we'll be placing there so list box will take on our recent transactions very good so that's kind of the framework we got one more to do as we bring up our tab I got one more that's going to fill in the remaining area so let's go ahead and copy any one of the shapes is fine and bring it down here and obviously it's too big so we're going to bring it something like about right here what I want to do is when I bring this up we're going to check the size show tabs only.
And probably View and then what we'll do is we'll unview the headings because I just want to get the maximum I want to maximize my area but I want to show everything so I want to bring it up just a little bit here so that when we don't have to scroll down to see everything this one's too big we don't need that big so that everything's going to be same great and so we also want to bring it out and this one's simply going to be called let's do net worth last 12 months or rolling 12 months is also can be called months very good so we can see just about everything in one View and that's exactly what I want here bring.
That one up just slightly what we're going to be doing is we want to start filling some of this information in I want to put in my net worth and I want to have a background and we'll do some ads so the first thing what I want to do is I'm going to put the background here so what I'm going to do I'm going to insert a square shape and I want this to be a little bit more visible so we're going to use a gray color on this one we're going to use these little boxes to store our information so the first thing what I want to do is I want to type in net worth here and this one's not going to have a black background it's going to have a kind of a gray background so.
We'll use one of these gray colors here we'll use this one the second to the last one I think that's the color I want
This one right here very good so I like that so now I also want to make this consistent with our font I think we can go with 11 on that and bold we certainly don't want a border around that so we're going to remove the Border we don't want any lines once we get just the way you like it we're going to be duplicating it so we'll do the no outline on that and then I'm going to use for the actual Network I'm going to use a text box so I'm going to insert that text box and.Then we'll be able to duplicate that once we get that just the way we want to and then this text box I'm going to just put in some number for now 354 comma and then of course I'm going to replace it in a minute but I want to get it looking just the way I want so to do that we're going to put in the middle and then I'm going to WR justify it I'm going to do Bol this one I want larger here I want a white font and no background so white font and then shape format will take no fill and then we'll do no outline on that all right so let's.
Take a look at that and this one I'm just going to be placing right oops let's move that this one I'll be placing right up about something like that of course this value is going to be dynamic and it'll change but at least we get the placeholder and we get everything looking the way we want it very good so now that we got this shape here I'm going to duplicate that contrl c and then contrl V and I'm going to bring it right down below it I want it the same size we're we're going to use this shape to demonstrate our assets so what we can do is we won't need this text box we can clear that out we want the same size shape to demonstrate the liability so we'll copy and paste this down here and.
Bring it down here we can use this text here that's no problem I'm going to duplicate that and then I'm going to want something for our assets and for our liabilities but we can create the graph right now so here instead of this one I'm going to left justify this and I'm just going to put in assets and I'll bring the text size down we don't need it that large we're going to keep it consistent with shapes here so if we see this one's going to be at 11 we'll set this one also to 11 or 12 either one's going to be okay and then we'll set this to 11 and I want this to appear right about here but I'm going to have to put.
This on top of the graph we'll keep it down here now and then we'll have another one for liabilities I've got some numbers to help us move things along and the idea is this whatever our current assets or our current liabilities I want to Jose that compared to what our net worth goal is so I want to see the difference how far are we away at least from our assets and our liabilities compared to our net worth now our assets will grow and our liabilities will shrink hopefully if things go well so I've got some information here that's going to help us so our total current assets and I'll go.
Over the formulas in a little bit but let's get the fundamentals down first and our net worth goal is this so I really want these two items to appear here and we're going to use those inside a bar graph so what I'm going to do is I'm going to insert here and we're going to use a clustered 2D bar which is this one right here now that we have that we're going to need to make some adjustments to this chart the first thing what we're going to do is we're going to select the data here and we're just going to switch the rows and columns I also want the series two on top so we're going to put that above it.
Good cuz this is what I really want to focus on I also want to make sure that we are going to start at one so I'm going to select the access options and that minimum I want to set it at one and that's going to be more reflective of the actual data here which is exactly what I want now we're going to place these on top of each other but I don't really need everything or I don't need a chart t underneath the grids so we're going to just select on them individually and then press the delete button as they go away so we don't need any of that and also I'm going to overlap these so what we want to do is.
We're going to shrink this down a little bit we're going to select on one of them and I'm going to go into the formatting the data the series options and I want the overlap to go all the way over like this the Gap width I don't want any Gap width at all so we're going to set that to 100% And then 0% and that's pretty much what I want so we do want a label here so we're going to add the data labels here and that's the data label that I want we're going to bring this into position and this orange is going to be hidden first we want to maximize it so I'm going to go over here and then what we're going to do is we're just going to bring it into position then shrink it and format it that's going to.
Be for our asset so I want to show those assets and bringing them up here so now that we have that in position we do need to make sure that we size it accordingly we also want the plot area to be equal to the total chart area so we need to maximize that completely so it covers the entire chart area then we can reduce the chart area accordingly to fit inside our shape so now that we have that here we also want to maximize this here because we're going to be needing that and now this orange filler we don't need.
That at all so we're going to go into the fill here we're going to put no fill and I also want no line on that so we're going to put no line also for the entire chart we also don't want to fill on that so we can do no fill on that and also no outline on that let's move it off the shape a little bit so we can make sure that we're selecting the right one go to the format here no fill on that and no outline on that I do want to format that white so we're going to said that white we want to increase the font to the size 11 or 10.5 whatever is consistent with.
It and bring it over here very good so now let's maximize the chart area that we have and bring it over here we're also going to set the colors we want to make sure that those are expanded accordingly so we can reduce this a little bit all right very good so things are looking good I've got a color that I'm going to set specific for our asset so I'm going to use a solid fill and I've got one saved up here which is right down here that's going to be used for our solid fill make sure we have enough space so I'm going to drag this over here and then expand it to the.
Right that's going to ensure that we have enough for that all right so it's going to show our assets I don't necessarily need to show the decimal so I'm going to select on that I don't want to check the label options so we're going to go into the number here I'm going to set it to currency which is important but I want to set the decimals to zero very good so that's going to show our total glasses we also need a label now this needs to be brought back on top it's going to be under now so we don't want that so I'm just going to move it right to the top and bring it up about right here very good so we do need to set this one to bold so that they're.
Consistent so it's going to show that our assets are now aligned properly and that we have everything set up I'm going to pretty much do exactly the same thing for our liabilities let's just bring this out a little bit very good so I like the way that's looking now what we want to do is I want something for our liability so I'm going to take both of them contrl D and we're going to bring it down here now our liability is going to be slightly different so what we want to do is first we're going to select the data right inside our chart here selecting the chart and then I'm going.
To select the data what that's going to do is going to bring up the range here now we want to select the individual data for our liabilities so if we scroll over here we can zoom out a little bit here I'm going to select the current liabilities right here so our current liabilities are 442 so I'm going to select on that all the options that we have and I'm going to click okay we're going to go back although it's zoomed in a lot but it's going to help us so now we're going to show our liabilities and just like we did I'm going to select on the individual chart and then we're going to go in here we're going to select that data I'm going to move the series two to the top so I'm going to just select it and move it to the top.
And that's what we wanted on we want that on the top it's going to show those liabilities so now we're going to change this to liabilities so we'll capitalize it liabilities and we're also going to need to show the label on that one so I want to select and show those data labels we can do it right here I'm going to see here data labels and we want to show it on the outside end so putting it right on the outside end we're going to end up moving it anyway so let's bring this down for now so we can format that and also we're not going to be showing here so it's 425 now if we take a look at this one right here selecting inside we.
See we've got a format let's look at that so we can match it here inside there we've got Narrows and bolds so let's do the same thing so we're going to set it up to 11 here we're going to make sure it's bold and we're going to make sure it's white and we're going to expand it now this one we certainly don't need a leader line on that so we can remove that I also don't want to show any background for this particular element so we're going to select on that no fill on that and then also make sure that there's no line on that leaving enough room we need to move this over so we have enough room here and bring in.
Our chart that's a little bit too much just like we did before we also want to format the number so we're going to go into the number we're going to set that to currency here and also the decimal places are going to be set to zero clicking okay all right very good so we have that just like we have and liabilities are going to show up here and these are looking pretty good I'm going to set the color I believe it's very similar to the color that I want but I want to make sure solid fill and we're going to select this orange color here that's the one I want here and I've got that Sav we want to be consistent with our colors for liability so our.
Liabilities are at 442 we can zoom out and have a look to see what we have things are looking pretty good that's 100% for us now what I would like to do now that we have our assets and our liabilities we can make sure that things are lined up here just the way we want so lining them up what I would like to do is I would also like to to show some elements so notice that we have all these accounts we've got our checkin account savings and we've got some account types cash investment real estate other or liability types mortgage what I would like to do is I'd like to combine all the cash accounts let's say.
Savings and checking and combine them together and then I'd like to know basically let's say the last 12 months of every individual asset type such as how did cash through the last 12 months how was our investments or our liabilities are liabilities going up or down and I'd like to see a little bit of a graph for that and each one we're going to put here so that's what I want to have so let's duplicate this background shape so I'm going to contrl c contrl v here and I'm going to bring it right down here very good so what I would like to do also is to be able to.
Show that so how are we going to do that well I have some data that's going to help us here so let's take a look at some of the data that I've created and if you see I've got all the individual accounts here now it is VBA that makes sure that any change on this is going to also change so I'll show you that vbn a little bit but if I were to add some different type it would show up or let's just say I were to change this to mutual fund so if I put in mutual funds it would automatically change if we take a look back in the data we see now it says mutual funds of course there's no data.
Because I don't have any transactions for mutual funds so we're going to put that back to cash but we can see how it works now I've got some formulas here and here but I don't have any data here so I just kind of put in some fake numbers so that we can have some data but here basically the idea is this I've got the month what I I want is the current month and then I want previous months here so no matter what the current month is going to appear here to do that we can use the date function the year would be the current day the month would be the month of today and then I'm just going to choose the first of the month so what this is going to do is going to give us the first day of the month if we wrap that around EO month.
End of month and we don't go any zero months before or back that's going to get us the last day of the month then what I did is I formatted it just to show three digigit months and 2digit years so if we go into the home we look at the custom formats we go into the mor number formats we see just that three digits month Dash and a two-digit year and then I just simply brought the formula down now the second condition is simply similar we're simply going to take the date of the previous month we're going to use the eatee function so I'm going to use the start date and then.
I'm going to subtract one month so it's going to put one month back and then I'm also going to wrap that around the end of the month and that's going to get us the end of the previous month then I just simply take this formula and I drag it down here and that's going to populate all the previous so what I really want is I want the last 12 months now what I also want to do is I want to get the total amount up until this day so that we can see how over the last 12 months if our cash is increasing or decreasing or whatnot so to do that I'm.
Going to create a formula now in this case we're using a table here in our transaction database so if we take a look we see the name is called transactions and we have our various headers so we can simply use a sum ifs so I'm summing all the transaction amount and I also want to use a criteria here the sum ifs is going to be based on the transaction date and I want it less than or equal what's an ap4 now that column is not going to change ap4 I just displayed the column so you could see them so ap4 and I just use some random numbers that's why you see the numbers.
Change here in fact it's a little bit annoying so what I'm going to do is I'm just going to copy and paste the value so we don't get those change all the time and then I'm just going to paste the value so they remain system so what we have here is less than or equal whatever the data is in ap4 and I also want to have the account type is going to be what's in AQ and three and that three is going to remain consistent so we want to make sure the absolute is before the three so that when we drag that formula down the row here doesn't change and the column AP does not change.
So we simply bring this formula down and then we can do the same thing we just bring it across and now we have the Investments here let's open that up Investments same formula here ap4 but this time we're on column AR so we're looking for those that are investments and we're going to bring it down and the rest we have some fake Data before I send this to you I'll make sure to clear this out and put the actual formas in but for our purposes I want to have some data so we can show some nice graphs so what I would like to do is I would like to create a little bit of a chart for our first one so what I'm going to do is.
I'm just going to highlight this data right here and now what I would like to do is I would like insert and we're going to take a look at this and I'm going to probably use this 2D here that should be fine this area chart here we'll be fine to give us a start so we see we've got cash we see that we have some information now we really want to simplify this I don't want to show any of the access information so I'm just going to select it and delete it select it and delete it we don't need to show the mon because we're going to show a very simplified version of it and I want to bring it down here I'm not going to.
Use this I'm going to use one data label but I'm just not going to use this even though I could I'm going to simplify it so I also want to get rid of the grid lines here and bring it down here so what I'm going to do now that I have it kind of primarily that I'm going to bring it all the way over here into our area where we want to place it so we're going to drag it all the way over here and then I want to place that directly above that shape and sized accordingly so we're going to bring it over here now I'm just going to place it also what we want to do is we'll zoom in and we going to set it up so that we can see that.
Increase first thing we want to do is go into the format here and no fill on that and then no outline on that since I want to know what's going on I want to make sure that I maximize the entire area so we're going to bring it up here here and maximize the entire chart to show what we want to show which is going to be that data so we want to maximize the data here we want to show all the information as we can obviously we W really wanted in this blue so what I'm going to use I'm going to use a white color here and then what I want to use is a transparent so I'm going to use control one here that's going to launch.
