With excel on a fully automated solution complete with fully automated dashboard transactions and then each account multiple accounts i'm going to show you how to do that today so the end of this training you're going to know how to do that i do appreciate you sticking with us on these trainings i bring you these incredible trainings every week and i hope you'll stick with us through the length of the training i only ask a few things one just go ahead and subscribe if you have not already go ahead and click that subscription button along with the notification icon bell that's going to ensure that you get these trainings delivered to you right away every week of course this workbook is absolutely free all you need to do is click the link down in the description either with your email or facebook messenger and we're gonna get that sent over right to you absolutely free if.
You do like to support this channel there's a many ways to do that i've got 200 of my best workbook templates available to you for just a very low price and you can click the link down below that also includes the optional pdf code books and that means all the code inside each of the workbooks in a beautifully organized printed pdf complete with table of contents index and a whole lot more so i hope you'll pick that up that's a great way to support us all right let's get started because i've got something brand new to show you this week some very very cool plug-ins some brand new.
Third-party options really some cool free options some paid options it's going to be great right because one of the biggest problems we have is how do we work with the data we've got credit card we've got checking accounts we've got savings account all types of accounts how do we combine all those into excel and how do we create this really cool dashboard so that all of our income all of our expense gets combined and how do we view those transactions how do we see all those transactions what if we want to see only a specific account or maybe we only want to see a specific category based on that account it's going to automatically be categorized automatically.
Show up and then of course we're going to be able to update that with a little trick that i'm going to show you and a challenge for you i've got a really cool challenge for you coming up so you make sure you stick with us of course all of those accounts will end up in excel and then we'll be able to work with that data so what i want to do is i want to share with you some really great applications some great platforms to help us do that i've got some really cool things lined up so make sure you'll stick with us on this training all right let's go over an overview of this application and then we're going to get into the fundamentals and how we bring this data into excel how we work with it and how we produce these really cool charts okay so we've got an admin screen and here we're going to list our accounts right so we want to know all the accounts i also want to know what type right because we can have bank accounts because we can have credit card.
Accounts those are very very different ones an asset meaning something you have money you have in that account it's an asset account right and then sometime money you owe like on a credit card or loan or something that is a liability so we definitely want to differentiate between those two accounts so we've got a little data validation basically an asset liability you can create all of your accounts here right just list the name of the account and then of course what i want to do is we have a very specific sheet for each account our chase checking account all the transactions we've got a transaction id the date of the transaction the name the merchant the category subcategory currency and amount of course these columns are completely customizable and i'm going.
Show that all to you so how do we create this and how do we get the data in here how do we work with the data how do we customize it and then how do we create this dashboard so it's relatively simple right we've got an admin that just defines we also want to know the opening balance what is the opening balance on a per year basis so we've got 2022 i want to know what the balances are on those accounts carried over from year to year this way what we can do is we can produce this based on the current transactions of the account we can then produce this account so we know how many total assets we have we know how many total liabilities and we know what our net worth is we also may want to use a slicer to know how many uh food and drink expenses or how much income we had.
For us on our checking account or maybe how many travel expenses and this slicer of course is going to be fully dynamic so the more types you have the more categories which are automatically imported from your actual bank account in a fully secure manner and then of course i want to know what our visa card expenses maybe we have certain expenses and how they go up on a per month basis so we got this little dashboard to show you okay so as far as the transactions are concerned we basically have the information here all we're going to do is just simply select on a transaction you're going to be able to load this one click we'll be able to refresh all of the data from our accounts.
Just with one click it's going to automatically update all the accounts all the accounts you've got listed from the actual bank or credit card account okay all right so how do we do this right so how do we like we said we need to bring that information in now if we were going to tap into each one of our bank accounts in a fully secure manner you know that would be really time consuming because every different bank has a different api but there's really cool products on the market today right with a free version also free and paid that allows us to do that one of them is called money logs money logs is a great application currently available for more.
Than 8 000 banks and institutions in north america currently and it's growing even further in europe and other regions so i want to bring that to your attention i'm going to show you exactly how to use it now this particular application called money logs a great application is only available through google sheets but that's okay because as long as we get it into google sheets i can bring it in excel so we're gonna use another product to bring it into excel a safe and secure product to do that and then we're going to have it in excel so it's going to be a three part so how do we do that well the first thing we want to do is is go into our google drive account make sure you have a google drive if not go ahead and get signed up on a google drive account and then what i want to do is here's my google i want to create a brand new spreadsheet so we're going to create a new.
Google sheet once you have your new google sheet here what we want to do is we want to add some extensions onto this these extensions are going to help us get the data from our bank account and from our credit card account so the first thing you want to do is click extensions and then get add-ons okay so the first one we want to do is money logs a great one it's going to help us connect to our banks and credit cards so we're going to type in money log logz okay hit enter and then once you see it you're going to click on it and install it for me it's already installed okay that's one the other one i also want to sync what's in google sheets with right now we've done this before in excel but with a less secure method i want to focus on a little bit of a more secure method the other one method i'll briefly go over a little bit later but basically we're sharing.
The document we're creating a public link and then that link comes into excel so we're going to do something slightly different we're going to use a third-party app called coupler dot io
You see it right there okay so i want you to install this as well okay it's another free version that's going to work for us both of those have both free and paid versions and we're going to work with the free version today so you're going to ins install this coupler okay once you have them both installed then we're going to focus on our of course looking for our extensions once installed you're going to see them both down here both coupler and monologue so you want to then launch money log and then what you want to do is you want to sign up for one of the free accounts and of course i'm going to include the link down below so the best way to get this is to get the links because i've agreed money logs has given us a 30 discount so when you want.To sign up click the link down below in the video that will ensure that you sign up and get the 30 discount of course there's a free version that we're going to be able to do with a single account but if you want to go ahead make sure you click the link down below they've offered me 30 discount on a full year so that's incredible it's really cheap it's like a few dollars a month so if you want to go over the paid version which allows for more accounts so make sure you get that so once you get signed up using the link below we'll go ahead and log in so you can log in and i'll just sign up with google and i've already created an account so i'm just going to click here and once you've signed up you'll you'll get your confirmation using the link below then you get signed up you get your account and what we're going to be doing is we have our bank.
So as soon as this loads you'll see different accounts so i've already set up two accounts but you won't have any of this what we're going to do is we're going to add a brand new account just so i can show you what it's like so i'm going to add a brand new account you can choose from really thousands of institutions so here we go we can search for a bank or we can do so what we'll do is we're going to continue click continue and of course it's completely secure and then what we'll do is we can search for the bank account or we can just click search okay so i'll just add let's say i want to add a citibank online and i mean using sample data of course so what you'll do is you're going to put in your bank it's completely secure this uses a a third party called played and played is a very secure only of course it says only your data that's transaction data not your account.
Numbers and and there's nothing else that's that is shared so it's extremely secured so what i'll do is i'm going to use put in just a temporary because i've got some sample code here which is going to help a sample user which will help us give us some sample data and i'll just type it in and then okay so once you're logged in just click submit you put in your bank account and then there might be a third party it might be a third party so what we'll do is then it's going to show you all of the checking or accounts that you have for that particular bank so if i for example let's have three or four different accounts with citibank maybe i have a credit card maybe i have a.
Checking account maybe i have a savings account and so on they're all going to be listed here so every account you have will automatically be shown we're going to click continue okay and then once we have continue it's going to connect to our accounts and then what we're going to be able to do is we're going to be able to add an account so it says we've success is connected to our account so that's great that's what we want to do and what we want to do is we want to create an individual report a fully customizable report and i'm going to show you exactly how to do that here we see we have our our three accounts i've had those previously so now we have the new one citibank online so i'm going to go ahead and click that and what that's going to do is then provide us with information that we're going to have so now we have our account so what accounts we have now we may want to add all of the accounts.
Right inside but however what we want to do now is i just want to add a single account meaning i only want to have one single accounts transactions within this okay so we're going to load all those transactions into google sheet so i'm just going to select unselect everything else except for one single account i only want one single account and that we're going to use our play checking account which is our sample account okay so now that i have that right we can scroll down here and we can see additional features so we can go back here and so we you see we have just that played account okay so continuing on what do i want i want both expenses and income so i'm going to select.
Expensive and income and i'm going to set the start date maybe i want all the transactions for the year and i'm going to click january now if i leave the end date blank this is very important if i leave that end date blank it will ensure that all of the newest transactions as long as we have keep it synced right so make sure we said keep it synced save the report include the columns okay so keep it synced that means any new transactions are automatically going to come in okay so now we can decide what columns we have so if i click add a field we're then going to get some fields so let's go ahead i said i don't want account because i know only one account right only our savings account or whichever one we selected so i don't necessarily need account because i don't have multiple accounts but i do want the category i do want the sub category and.
I want to make sure that each one is unique i want a unique id so i'm going to set transaction id okay currency and amount are good okay so we've got all of those that's fine and then what we'll do is we'll just unselect unselect here go out of that so now we have all the information and we can move them maybe i want transaction id to be first so i'm gonna slide this up so i've got transaction id date name merchant currency amount category and subcategory okay that's perfect let's go ahead and put category and then subcategory up here so that way we have currency and amount you could put it in any order you want so i like that that looks good that's what i want to have in here okay.
It's going to be connected to sheet one right you select sheets i only have one single sheet in this workbook so that's fine and i'm going to have that first field on cell a1 include the columns yes save the report yes we want to do that and keep it safe okay we can give it a name so let's give it a name let's just call it citibank and then saving i think that was the one let's just double check so make sure i have a checking account okay so let's put checking account was checking okay so this is our check-in account okay and now all we need to do is just click one button called get report and that's going to import all the data from your checking account based on the dates that you've selected based on the transaction types and it's going to bring them in here okay so now we have transaction id date the name merchant category subcategory currency and account perfect.
That's what we have click continue okay very good now that we hold have all that in there why don't we give this a name test sync okay account okay that's fine i'll give it a name all right so we
Have that located there now what i want to do of course i want to bring that into excel we're going to use another extension i'm also going to try to get a deal on this one as well but we're going to call this coupler io and click the link down below i'll have a link for you as well try to get us a special and then what we're going to do is we're going to click open dashboard now you always want to create an account for that security free account the free account's really cool and that's sufficient for now and of course if you want to grow that you can and so what you want to do is probably going to click connections here right now i noticed that it's a little bit better if we.Have add importer we get a little pop-up where we can add an importer which is which is really cool but we really want to do a little bit more with that then right now that's really good for quick but let's go to the website directly i found a little bit more we got more options with that so we're going to click connections and what that's going to do is going to open up a brand new tab now i've set up several connections and you don't necessarily need dropbox but what we do want to do is we want to add a new one you want to add a microsoft excel connection that's really important so when you click add new make sure you're going to select here the option i've already done that and click here all the way down here all the way when you see actually i've already connected it but you want to click microsoft excel here this is the one you want to connect because we're going to actually be going directly through microsoft excel and make sure you have a one drive account.
One drive is important we're going to bring we're not using dropbox this time we're going to use directly one drive as that provides a lot of very easy sync so microsoft excel once it is synced what you want to do is you then you want to create a brand new excel document in your particular onedrive account so let's take a look inside our onedrive account before we do that great and here is my one drive account it's right here and what i'm going to do is i'm going to create a brand new xlsx excel file that we'll be able to use for sync so what i'm going to do is i'm going to click new here and we'll just call it sheet1 and we'll just leave this blank all i'm going to do is just do file then we'll just do save as i don't i want it directly in my onedrive account and we'll call it.
Test sync file that's sufficient enough and make sure to save it in my personal drive and i'm going to click save and that's it i'm just going to close it there we're done with that for now and as you can see inside my onedrive we now have test sync file okay so that's the one okay so back into our coupler dashboard once you've connected your microsoft excel you'll see it here you've got it here and then what we're going to do is we want to make sure we don't necessarily need a dropbox i could probably disconnect that i'm not going to use that i didn't it was for testing purposes but we really need two connections right google sheets and microsoft excel those are the only two that we're going to use for now so once you have that you're going to click importers and then we're going to create a brand new imports we're going to call it add new first of all what we want is the.
Source we need to know where the source of course source of course is going to be google drive and then what we want to do is we want to save it where do we want to save it we're going to save it to microsoft excel okay so it's got the basic information then we're going to click proceed and where are we going to get okay where's our source account right it's google drive of course i'm going to use my only source account here which is there then i'm going to click continue and then it says what file well we just created a file of course in our google drive and if we look down here it's going to be the first one that we have here under test sync account so that's the one we just created and then we want to know what sheet to apply that of course sheet1 is the only sheet that we have right then we're going to click continue great so now what about the range where do we want to place the data right we don't necessarily need on a specific range.
We're just going to export all the data that means all the data from google sheets directly into excel and that's sufficient and we're just going to click continue okay now we're going to focus on the destination where do we want that data sync of course we've already chosen our specific excel account so we've got our onedrive account we don't need this here we can get rid of that that's just a nice helpful so we're going to continue on that continue and then what we want to we do need a specific workbook so it's going to connect to our onedrive and we're going to select that workbook which is in our file so now i'm going to connect to that workbook which is this testsync file right here that's the one i want to select and click open let's open bring it up a little bit more so you can see the open so now i'm going to click open okay so that's the one i want so now of.
Course we need to know what sheet we only have one sheet which is sheet one then we're gonna click continue and it says import say one continue we just click i a one is fine right that's the that's the cell link that i'm gonna do and we're gonna click continue once more okay now what i wanna do is what's the mode do i wanna replace or append in our purposes i'm just going to use replace fully replaces all previously imported data with the latest information available in your data source so that's what we want i don't want duplicate entries or anything like that i'm just going to click replace now we could use a pen that places your newly imported data under previously imported data suitable for tracking historical data change so we're going to use replace for our purposes okay now switch on if you want to add a column specifically for the date and.
Time this could be helpful but we're going to keep it off for now so that adds an additional column we're going to click continue on that okay now we want to know how often do we want it updated right so when do we want to update so now automatic data refresh we can auto turn this on so we can automate it now maybe we want it every hour every 15 minutes now if we want it every 15 minutes or 30 minutes of course we're going to need to pay for the cap but every hour is sufficient for our purposes okay and what how often what days of the week so maybe every day and what time do we want it preference so that's fine so we can just say click you know specific hours click save and run okay so now it's going to run import has been successfully updated coupler is now running the.
Imported may take a couple of minutes shouldn't take that long because we don't have that much data and then it's going to complete it is now complete it says it's run one 25 rows and zero errors okay so what we're going to do is we're going to pull up excel we're going to pull up that testsync file which is right here and we're going to see that all the data has now been added into the excel file that's fantastic okay great so now we see how to get it into an excel file xls not our main workbook so now all we need to do is write some code to automatically update this so how we gonna update this into our main workbook well that's very very easy so let's go ahead and close that out now we've got our main workbook so let's say i create a new sheet here and we're.
Gonna just gonna call this what would you call it citibank let's give it a name right citibank here city bank and then checking great so we've got our account so now we've got a sheet set up we don't need this so what i'm going to do is i'm going to create a connection so we're going to go into data and we're going to get get data and we're going to get it from a specific file from an excel workbook say what excel workbook of course we have this test sync file click import here so what that's going to do is going to give us a little bit of a pop-up right so now what we want to do is where do we want the information to come sheet one is where we can there's a little preview of the data okay and then what we want to do is click load and then load two sorry it's a little bit off the screen let me shrink this up a little bit so you can see that option here load two okay so low two in the.
Bottom here click load two where do we want it loaded too it's gonna give us this little pop-up we want a table we want it on the existing worksheet because we've already got it open and then we don't need to add it to the data model so a1 is sufficient we're going to click ok that's going to automatically create a table with our data and so here we have all of our accounts and we have a brand new one sheet1 here is our 24 rows of loaded so they have all loaded in so now we've created a connection now all we need to do either through vba or through manual is click data click refresh all and all that data is going to automatically be loaded in.
So i've done this exact thing for of course all of the sheets so we have all the information here ready to go fantastic so now we've seen how we can create this sync data how we can get it into our excel using a data connection and all we need to do is refresh all very very cool okay so we can delete now we understand how we got up to this one so i'm going to delete this sheet i'm going to delete the connection we don't need that and just right click and delete the connection here and then delete okay so i've created i've got four different accounts that i've created on here four different connections four different queries here and so now what i want to do is i want to get that.
Information i want to put it inside a nice screen where i can quickly and easily look up specific transactions based on multiple information or or filter in and then basically select transaction and then be able to display it and perhaps save and update it okay very good so i'm glad we got that so far let's continue on okay but what if we want to show only specific accounts or what if we want to show all accounts or specific accounts or or all how do we do that how do we show that.
Let's go ahead and hide these for now and what i want to do is i want to be able to select specific days or filter accounts or filter transactions by day or by category right and i also but set certain accounts have different categories right so if i take all accounts i want a list of any type of category regardless of account right so we've got a lot of different accounts however some accounts only have certain categories so a savings account may only have payments and transfers right so how do i filter i want to create a dynamic category based on that so.
That's what we're going to focus on first we're going to create this dynamic category but in order to get all of these accounts what are these accounts based on well they're based on actual database right these are sheets right if we take a look at these our counts are the same as sheets right we have four different accounts chase checking visa savings and b of a cd right so these are the same four down here so inside our admin screen what we want to do is we want to make sure to set that up so for each account what we're going to do is we're going to set a give it a name we're going to give it an account type and we're going to assign a database sheet so here i've got all the sheets listed so the first thing what i want to do is create a macro to list all of the sheets that we have inside our application so the user can check which sheet is applied to.
Which account and then i want to set an opening balances this is going to help us for our dashboard so that we know the amounts total for opening balances are going to be used inside our dashboard great so this little uh table here is going to help us set things up so first thing we want to know is we want to create these particular sheets so to do that we need to create a list of sheets so if we take a look on the left we have a list of workbooks sheets so i'm going to create a macro that's going to do that for us so let's take a look at that first thing we're going to do of course is go inside our developers tab and click visual basic if you don't have this developers tab of course you can get that displayed all we need to do is click on the options here go into the customized ribbon here and then just click the developers make sure you select.
The developers that's going to make sure you have it you can also get to the vba also by using alt f11 that will get you there that's shortcut alt11 we're going to click on that and what we're going to do is we're going to have some something called tran we're going to have a modules here so i've got three modules one has to do with a pop-up calendar which we won't go into we're going to focus on the transla transaction list macros because we're going to be creating transactions and the first thing what i want to do is create a macro called list all sheets list all sheets if i run this it is automatically going to list all the sheets here so the first thing what we want to do is we want to dimension the worksheet as a sheet okay worksheet has a sheet and we're going to mention the sheet row is long why because i want to know what row as.
We move all the way from row 7 all the way down i need to track which one so i want them listed here so what we want to do is also we want to first clear out any of the sheets that are there because i want to create a brand new list in case we have added any sheets so to do that all we need to do is just say with the admin sheet row equals seven we're setting that initial row and we're going to clear out any previous worksheets here using this clear contents now what we're going to do is we're going to create a loop i want to loop through every single worksheet inside the workbook.
So we're going to say for each worksheet which is already been dimensioned as a worksheet in this workbook worksheet we're going to set the name of that worksheet that's what i want to focus on i want to put that in column a so a and the sheet row value equals the worksheet name and then we're going to increment the row by one okay that way they don't get placed on top of each other or replace so that's going to simply add the worksheets then that's going to simply create this so if i delete this and i run this macro here you're going to see it's going to list all sheets in the workbook right here okay they're back there okay great so we have that now but what i want to do is i want to create a named range based on that so we go into this formula you'll see that this data validation is based on the named range so when we look on that and we see.
The data validation we see it's something called workbook sheets workbook sheets so formulas name manager and then all the way down at the bottom we see something called workbook sheets and we're going to use an offset formula based on a6 and we're going to count all the way from a6 to 89999 and then and then we're going to simply subtract 1 because we don't want actually i'm gonna don't need that we don't need that subtract one if we start out at a5 a6 a6 is correct a6 is what i want and the reason yeah we do want that a6 of course is our title we don't want that header in there so we're going to offset it one row down and then we're going to count everything from 6 to 99 and.
We're going to subtract 1 because we don't want the header row in here the reason we're including that header row is so that there's no error in case there is no data at all although there shouldn't be but if you clear it out i don't want to err for that named rage okay so that's going to create a name range we are using that named range inside the data validation for these cells that way users can then select which sheet is applied to which table that's going to help us out tremendously because when i create data and information i need to know what sheets to update i need to know where to go so that's very important okay great so we understand that back inside our.
Transactions i then what i want to do is we're going to use them as an accounts right i want to then create those an account so what i have to do is i have to create a new named range including the all accounts so i want all the accounts here plus the all accounts how are we going to do that well we're going to do that with another named range right and that's going to be located right here so if i unhide this columns a and b we see that we have something called account list here and i've added all accounts up there and then i've basically linked here to whatever is our admin f8 f9 and so on and so forth so everything is linked to the admin then what i'm going to do is i'm going to create a brand new named range i'm going to make sure to include this all accounts so when.
I go into the formulas name manager and we see we have something called accounts with all if we tab out that we have used another offset but this one's a little bit different we can't simply count text because they all include formulas so every single one of these cells includes text because they're formula so we need to use it a little bit different i want to use a count if i want to count if it's not zero right if we close this out there's actually a zero here so if we take a look at this right and we go into our options here and we go into the advanced and we scroll down and.
We want to display show a zero in the cells that have zero value click ok you're going to see that there's actually zeros here so obviously i don't want to count anything with a 0. so i want to count only those things with non-zeros non-zeros so how are we going to do that well the best way to do that is simply using account if so let's go i don't necessarily like to see those zeros so we're going to go back into the options go into the advanced scroll down here and then just simply hide those so show a zero in cells that have zero value so we don't wanna show that but we know that.
It is there so we're gonna use a formula to count anything that's not a zero so we can do that using here cancel with all we're going to use the offset starting with a9 right and then what we're going to do is we're going to count use countif we want to count all the transactions from a9 through 826 that do not equal zero right and we want a single column that way we are going to have a name brain just see the dancing app then include all accounts along with all the chasing along with all of the bank accounts or credit card accounts here so that creates a brand new named range it is that named.
Range that we're using inside the data validation here so we click data validation we see we have accounts with all and that's important because when i list all accounts i only want to show those account we have all the accounts here however if i want to filter based on a specific account i can do that and it's only going to show those accounts that have that specific account okay great all right so now we see that but now when i select a specific account let's take a look inside the chase checking account if we take a look inside the chase checking account we see that we have categories travel food and drink travel food and drink so pretty much travel and we have income so we really have three different uh categories here i want to make sure that those three unique categories show up inside this drop down list right here food and drink income and travel if i.
Select on a different account i want to know only those categories that for that specific account food and payment and i want notice that these here these are changing here so based of course if i select all accounts i want all of the categories based on all of the accounts to appear here okay so that so if i only want to show income based on all of our accounts then i can basically create criteria to show only those okay great so how are we going of course we can do all categories.
So how are we going to do that what we're going to do is we're going to create a dynamic list right here based on the specific account and of course we're going to use a macro that's going to help us with that so how are we going to do that let's go back inside the vba the next macro that i want to focus on it's going to be called get unique categories get unique categories this is the first one we did so this get unique category is going to help us do just that all right so the first thing we want to do is clear out any of the data right if we know that our category is going to be coming in here i want to clear out any of the data here i've created a brand new named range called categories with all let's take a look in that named range and see just where we have it so it's called categories with all and of course that's going to use offset here we use count a for.
That we're just going to create basically included all the way from b9 and down categories with all it is this one that we're going to be using inside here so this data validation here is going to be based on that categories with all it is this list that we need to clear out and we need to populate using vba based on whatever was selected here inside the account okay so let's take a quick look inside that the first thing we want to do is i want to clear out e5 along with all the data and that means as soon as we change this let's take a look i'll add a i'll add something in here and then what i want to do as soon as i make a change to here i want to make sure that e5 gets cleared out because it's based on it's dynamically based on whatever was selected here so we want to.