Welcome to this session.

So this session is about database analysis using Excel.

And I've put on the screen hopefully you can see this. Hopefully it's shared nicely. I've put on my name.

I'm Phil Sparks, and I'm a trainer at Financial Edge.

I've been trainer here for about two and a half years or so.

And basically a tiny little introduction. What are we doing with this session? Well, basically what we're doing is we're doing analysis of data held in Excel.

And sort of just going back to my own history a lot of the time as a finance professional the best way of dealing with financial information is to use a proper database, you know, to use a general ledger to all your analysis and your reports in that.

But of course, inevitably because Excel is such a flexible tool and widely known tool people do one-off analysis, one-off,, sort of queries using Excel.

And pretty much any database in the world will have a facility where you can dump data into Excel, and give you that flexibility.

So, although probably the, the theoretically right answer is almost certainly to,

to use a database to do some of this work.

Basically what we find in a lot of financial environments is that people do an awful lot of this stuff within Excel.

So that's what we're gonna do today. That's what we're gonna be looking at, uh, today.

Now my colleague Agata has put hopefully in the chat box and again, for my convenience zoom has rearranged my chat box.

I can see that. Thanks again.

So basically there should be in the chat box, two links. The first thing is a link to the resources for today.

So that'll take you to Felix our learning platform and enable you to download the spreadsheets that we're gonna look at today.

And also a link to feedback at the end, and to Q&A session, a place where you can ask questions at the end as well.

So do take notice of that chat box.

And as I say, if you it'd be I think it's a great idea to download the date, the spreadsheets.

So you can look at what I'm doing as we go through over the next 25 minutes or so, you can actually have a try do in in real time. And I'm a great believer in learning by doing so practice, have a go at this stuff.

As I'm going through it, or at least downloads the spreadsheets. We've got both the empty and the full versions that aren't, that we haven't used, and then the solution versions as well. So you can have a play with those after today's session.

Again, what happens with Excel often is a great way of learning Excel is just looking over someone's shoulder. And that's really what we're doing today.

You are looking over my shoulder as I show you how to use some of these techniques.

Now, you might, you might not remember it perfectly, you might not pick up every single nuance, but the fact that, you know, you can do this, you know that there is a facility to do this in Excel, means that you can go and, ask someone to help Google it or just come back and look at the recording for today and follow it.

Now you know that, Excel is able to do these things.

So, without further ado, let's jump straight into Excel.

I'm hoping that I've got this waiting for me.

Oh, that one, I think.

Here we are. Yes. So, Let me just Shut.

Okay, so here's my Excel spreadsheet. So if you open up this, this is what you should see, advanced Excel, Excel modeling database analysis, and we're basically going to work our way through these tabs over the next 25 minutes, just looking at four or five different techniques you can use to extract data to sort and sift and analyze data that's in a spreadsheet.

So here we go. Let's jump into the first one, sorting.

And what we see here is a typical dump of data into Excel.

If I click into this table you can basically see it's numbered by transaction number.

It's got dates, it's got their clients, it's got the product, it's got number of units, the sale amount, the location, all that kind of stuff.

And we're basically going to use the same information over our four or five tabs over the next 25 minutes or so.

Just to give you an idea, if I hit hold control and hit down arrow I get right down to the bottom.

And you can see there's nearly 2000 lines of information, For all of these different accounts and dates and products and units and sales and all that kind of stuff.

So how would we sift our way through this? How would we turn this into something a little bit more meaningful? Well, the first thing we can do is, we can try and sort it.

And if we click anywhere in the, anywhere in the table I'm gonna click date.

You can see that the list is in transaction number here, there's some sort of reference number, but that doesn't quite tie up to the date.

You can see I've got September the 12th and February 2014 in here.

So what I'm gonna do is I want to order it, say, in date order.

So what I do is I go up here in my home tab in Excel, and I go to sort and filter.

Now of course you can do this using your mouse, or you can use keyboard shortcuts. If I hold alt I click you'll see that you basically, when you hit alts, you get all of these letters appearing on the menu item.

So I hit H for home and over, on sort and filter, I get sum, and if I just simply click, you can see it automatically knows that probably what I want to do is to reorder by column D because that's where I am.

And it says sort from oldest to newest.

Well, I'm not, I don't wanna do do that.

I wanna do the other way around. I want to see the newest at the top.

So I move down with my mouse.

I hit return, and you can see jump it automatically.

Reorders very easy.

It goes right from March 15, through February 15 and so on, all the way down.

And you can, and it orders all of the rows. It doesn't just order the dates.

It obviously order keeps all of the data together.

If I hit Control Z to jump back to where I was in this unordered fashion, I can also use I can also be a bit more sophisticated, with sorting.

Now I'm gonna go to the top and I'm gonna highlight my entire table.

Because what I've just done is one simple sort which basically just do, does it by date order, but what if I wanted to be a little bit more sophisticated? But what I need to do is to highlight the entire table.

So I hold down shift, I use my arrow keys to highlight all of the heading.

And it's useful to, to highlight the heading of the table.

Because that will give me the ability to sort by these labels.

Rather than just trying to remember what's in column F and what's in column G, and so on, I hold down shift, I hold down control, I click down arrow, and it's highlighted the end entire table.

Now obviously I could do that with my mouse, I could drag but it's, you know, with 2000 lines, it's gonna take a fairly long time.

So those sort of keyboard shortcuts control arrow up and down shift to select are really useful.

Now, if I go into the sort and filter menu again, you can see that I've just got simple, sort smallest to largest or largest to smallest.

If I click into custom sort, I can start to be a little bit more, a little bit more precise, a little bit more sophisticated about this, and I can set up sort of multiple or nested sorts.

So what I'm going to do is, the first thing I'm going to do is I'm gonna sort by, uh, let's sort by dates, No, let's, sorry, let's sort by, client accounts.

So we're gonna go with client account first, and it says sort by sell values.

And it's going from A to Z, so it's increasing.

And then what I do is I can click add level here, and I can get a multiple sort.

So it's gonna first of all, group everything together into the different accounts.

And then what I want to do is perhaps, let's sort by number of units and let's do that in a decreasing way.

So I click here, you can see because I highlighted the headings, and because over here on the right hand side, it says, my data has headers.

Excel has automatically realized that I've got those labels in the top line.

So what I can do is I am going to go with units, and I want, here, I'm gonna go with, I wanna flip it round.

So instead of going smallest to largest, I want my biggest numbers at the top.

So what are my biggest sales? So I'm gonna go largest to smallest, I click okay.

And then you can see that basically what it does is it puts everything, this is my, my first account account, one ASH.

And as I scroll down, you'll see that eventually that changes to my next accounts. There were, there I go account two, BET.

And then within each section, it then orders them in units from the number, the highest number down.

And of course, this is infinitely flexible. You can do this anywhere you want.

If you then wanted to say, well, what is all of my, uh, what's my, um, what's the total that I've sold, uh, by account to account one ASH, what I do is I hold down shift and I'm gonna do page down.

And I'm just, because these are now in order, I can simply go all the way down to the bottom of ASH, which is there, and it even has some credit notes at the bottom.

If I want the total.

Then I can see over here, in the bottom right hand corner of my screen, I've got a total a sum of 25,947. So this is a rather brute force way of doing it, but it enables me to sift and sort my information.

Okay, let's go a little bit further.

Let's be a bit more sophisticated.

So if we jump into the next tab, we have the filtering tab, we have the filtering, tab.

So we're gonna do the same thing.

If I'm in the middle of my table I'm going to click into my table here, and I go to the same sort and filter menu item up here.

And of course, I could do this with keyboard shortcuts.

And if you do this a lot, if you use these a lot, then these keyboard shortcuts will, get reasonably memorable. So what was it? Alt HS gets me to, source and filter.

and then filter is F. So I'm gonna hit F.

And what does it do? Well, it doesn't seem to do very much, except if you look at these headings at the top Excel has realized, has realized that these, this great big long list of data, this is a great big long list of data.

And basically it's got different items that appear multiple times.

And the clever thing is if I click into, say, location into this little dropdown arrow in location, it basically has gone through and it said, I've only got 1, 2, 3, 4, 5 alternatives in here.

I've only got five alternatives.

I've got East, Midwest, south, and west, and therefore I can just dis And at the moment, I'm showing everything. So I've got select all selected, but if I click on select all, it dis everything disappears, and then I click, perhaps just east, I click okay.

And then it just filters. It basically stops displaying everything else.

It only displays those rows that have got east in here.

And of could, of course, I could decide that when I go to product, I want to say, well, what am I sales of just one product in the east location? So what if I just decide, do the same thing, select all, and I can, and you can click multiple.

So I can say money market and liability driven, I click okay.

And you can see money market and liability driven is all that it displays here.

Just for the east, just for east now, just need to mention one thing, which is, if you look here, it's not actually deleted rows, it's just hiding the rows.

So it's just filtering out the things that I didn't click, the things that I didn't want.

So it's only showing rows that have got money market liability driven and east in column H, um, and everything else, it's still there, but it's just not displayed. And you can see that if you look down at the bottom here in terms of these rows, my last row is 1864.

The one above that is 1862. So there must be an 1863 in between, but Excel is hiding it.

Now, one thing, if I basically say I want to add up everything, if I add up from some, I'm just gonna go to 1864, and then I'm gonna do hold down shift and do page up, go all the way to the very top, and I hit return.

Now that's, I go down to the bottom.

That's given me a total here of about 200,000.

Now I'm actually just gonna manually change this.

If I go to the very top control up, you'll see that my heading row is row four, so therefore the first row of real data must be row three.

So if I go down to my total here and I say I want some G from G3 to G1864, that should give me everything, absolutely everything that's on this table.

But it's also adding all of the hidden rows as well, so that sum isn't representing what's actually there displayed on the screen.

Now, to do that, what we use is we use something called subtotal, and excel is really clever.

If we use our good friend alt equals, which is the auto sum, I hold down Alt, I hit equals alt equals will basically add up everything above.

But you'll see instead of doing a normal sum, what it's done is it's done a subtotal, it's done a subtotal and subtotal with a function of nine is basically adding everything up, but only the things that are displayed, not the things that are hidden.

So if I hit return, I'll get a much smaller number than that 200 and 35.

So these filters are really powerful, really, really powerful.

But you have to remember not to use a sum, you need to use a subtotal if you want to actually have something that looks accurate, something that is just displaying, what you've got in, actually displayed on the screen.

So really super powerful, very, very flexible. And as I say, you can do nested queries, nested queries.

I guess one of the other points just to make on this just before we leave this and I'm a big fan of these, I think these are really incredibly incredibly powerful, is if I look at say the locations, it's always worth keeping an eye on these, because you can see I've got East, Midwest, south, and west.

Keep an eye on these because if we spelled something incorrectly, so if we said East with, with say two S's instead of one, it would appear as another item on here.

So it's always worth having a little review of, the filter, uh, just to check that you are being consistent with the data that you've got in your list.

Okay, let's jump forward. Let's go to some if and sums.

Again, you might, some of you might have seen this before.

So some if comes, there's a range of different there's a range of different there's a range of different, uh, functions which will basically add up subject to a criteria.

And so this is another way of identifying the same data.

So if we wanted to add up everything that had east, we could do a sum if and the lo with a location of East.

And that would basically just add everything up, uh, with, an east in the location.

So, I'm gonna start with I'm gonna start, by just putting in a reference.

I'm gonna say equals account to BET, because we're going to use that to drive our some ifs.

Now, I'm actually gonna start here with some, if I'm gonna say equals some if, and the great thing about Excel is basically once you start typing a function, it gives you a helping hand, it gives you a hint as to how to actually use that particular function.

So it says some if, what do I want to add up? What do I want to select? So what we want to do is we want to, we're gonna answer this question here, what is the value of account? Two bets total sales.

So we're gonna, we want to add up if only if the client account equals account to BET.

So what we need to do is to be careful of the way that this is laid out. So it says some if range.

Well, the range is what we're trying to find, what the range is, the area that we're trying to find that client account in.

So what I do is I jump into the account clients into this cell here.

I do shift control arrow down.

So it adds, it basically takes me to all of takes, takes me all the way to the bottom, I do a comma, and then what do I want? I want that to be equal.

I want to only pull out where that is equal to account two BET.

So I've typed that already, so I'm just gonna say it's equal to G3.

If I did, if you wanna do it manually, I would just put account two BET in quote marks.

And then what do I want to add? I want to add the sales, I want to add the sales amount.

And it says some range.

This is what I'm gonna add up when the client's account is two BET.

So I do again, shift hold on, shift hold down, control, hold down, down arrow, do a bracket at the end, hit return.

And that basically gives me the value.

Now we know that the total was something like 235,000.

So this is just about what's that 10% or so where it's equal to that.

Now there's another one, there's another item called count If.

So we're gonna do the same thing.

And this just simply does a count of the number of items.

So I'm gonna say count if, how many, how many items do I have where the client is accounts, BET.

So all I do is I look at the range client's accounts, again, all the way down to the bottom.

And I want that account, I want it only where that account is at G3 I account two BETI hit return.

And it just simply counts. It simply counts.

And again, I had note something like 2000 rows here, and this is saying 217 of those I about 10% is account two BET. So it makes sense that the total value of their sales at 28,000 is about 10% of the total.

And we can also nest this as well, but what we're gonna do is a different function instead of some, if we're gonna do some ifs with an S, so equal some ifs with an S.

And this is for when you want multiple criteria, and it's in a slightly different order.

It come, the sum range is first.

So it says, how many units of money market products has to account two BET sale sold.

So the first thing is, this is what we're gonna add up. We're gonna add up the units column.

Again, I go shift control arrow down, which is a really good friend.

And then the next thing is I want, it says the criteria range.

So first of all, the criteria range is client accounts is column C that we've used before, again, all the way down to the bottom.

And we want that to be equal to, it says criteria one.

So we want that to be equal to that, G3.

And then it says, what about the pro? What's our second criteria? Well, the second criteria is that the product equals money markets.

So I'm gonna go and I need to just check.

So it's money market with a capital M and a lowercase M.

So what I want in my second criteria range, is to point to this product column.

I go down and I say money markets, and I put quotes at the end, I hit return.

And that should give me a subset of all of those, of all of those sales with two criteria.

Really powerful some ifs incredibly powerful and use and always worth using some ifs rather than some if, because it enables you to kind of amend, enables you to add criteria to the end.

Now, two of the things that we're gonna use, very cool, very quickly, I'm just gonna dip into d functions very fast.

D functions are basically, and I'm just gonna show you one of these if you have something that is in a sort of database formats, which is what we've got here Excel has got some great facilities, and they all have a D at the beginning, and they basically just operate like a d like a database query.

So I'm just going to put just one of these things over here.

And I'm gonna say equals and what do, what do wants the, the question says, how many units of a loan product has account a SH sold? So, what I want is to look at those units.

So I'm going to do D sum and it says d sum basically is database sum. So it's gonna add up, I open up my bracket and it basically says, first of all, define where your database is.

So my database is basically this whole great big table here.

So again, I just define that.

And then it says, what's my field? Well, my field is basically the number of units.

So I simply need to point to the heading, for number of units.

And then I need to define where my criteria is.

And here's the key with this.

What you need to do is basically have a set of headings that are in the same format, the same text as the database headings that you've got.

I hit return and it basically gives me that number.

Now that 78,707 is basically the total of all of those units all the way down to the bottom.

But what I can do now is I can just simply, drive my database by writing my queries underneath these headings.

So if I want to just pull out account one a SH, I simply type underneath this heading account one a SH, and keep an eye on that 78,707.

As I hit return, it drops.

So those are all of the units of sale to just this particular client.

If I wanted to look at, say, just the loans that we sold to them, then I type loans under here.

And again, the number drops. So it's really powerful, enables you to basically drive a very flexible query.

And of course, what you could have here is you could have little dropdown menus in all of these that would drive the, drive the outputs.

Now our final one is something called a pivot table. We're gonna look at a pivot table.

And again, pivot tables are quite well known, as sort of being almost a little bit sexy and, great ways of doing sort of one-off slice and dice analysis.

Now, my way of dealing with a pivot table is always just to use Excel's built-in functionality.

Excel's built in helping hand. So I just click somewhere within this greatly long table.

I've gone to the pivot data tab, and I do insert, and I do recommended pivot tables.

And I don't overthink this. I just say, yep, I'm fine.

And I don't really care which one it's gonna recommend, because I know that's not gonna be the one that i I particularly want.

I click okay. And it basically generates on, the next page, just again, let me make this a little bigger.

So you can see it basically just generates a great bit table of data.

Now all it's doing over here is basically adding up the sum, and it's giving me the number of units as the driver, which doesn't really make much, make much, uh, sense.

So what I'm gonna do, you can drag and drop these things. So I'm gonna click on the units, drag it over to this this field search.

And now what I'm gonna do is I'm going to drag over the location to the rows, and you'll see that it's got that 239 total, which we remember, I've dragged over the rows and I've got sale amount as the total.

And it's basically just analyzed by the four, the four regions, and it's coming up that 239 that we remember.

I'm then gonna say, well, maybe, maybe I'll, maybe I'll be quite interested in products as well.

So I'm gonna drag products over to the rows as well.

And then what does it do? It basically, it splits that 239, first of all, into the regions, into the location.

And then each of those regions, it then subdivides into the various products.

If you can see that it's doing it in this order, if I drag the product above the location, then it'll basically reorder it.

So now it's grouping initially by products, and then within those products it's dividing by the location.

So really super, super powerful.

You'll see that there are a little sort of arrows here, so we can filter.

So if we wanted to say just look at equities and fixed income, it's a little bit like the the filter criteria we had before.

So equities, fixed income, click okay, and it just shows me a snapshots, just the sort of the just the elements. And it's a little bit just the element, those two products.

And again, it's a little bit like filter.

You get a little sort of dots next to the arrow, I go back and I click select all and it all comes back.

I can do this in a matrix format.

So I could put, I can drag the product and put it up into columns and the rows are location, and I get that lovely sort of table.

Again, it's super simple powerful.

And I'm just gonna show you just one element.

One final thing to do before we close up.

I can grab the sale amount and I can put two outputs of sale amounts.

And you'd say, well, why on earth would you do that? That's exactly the same amount of exactly the same thing.

Well, what I can do is if I right click in the second column, um, it says show values as show values as, and at the moment there's no calculation, it's not doing anything.

Well, what I could do is if I do percentage of grand total, and what it gives me is the buildup of each of those, elements building up to a hundred percent.

And you can basically see that I've got 23%, 22%, 26% in the various regions.

And then within those, you've got if I add, um, add up all of those, it says 23.9%, which is the same as the total.

Alternatively, I can basically do this if I, oops, there we go.

If I say summarize values as a percentage of row total, I think it's this one here.

And basically what it does is it still gives me the same breakdown, by East Midwest and so on, adding up to 100, but within East, it shows me how that 23% breaks down the components of that 57,321.

And again, if I highlight all of those, you can see over on the bottom right hand side, it comes out to a hundred percent pivot tables.

Incredibly powerful, incredibly powerful.

So my suggestion, would be make sure make sure you are comfortable with filtering. That's really powerful for one-off checking.

Make sure you are comfortable with some ifs and count ifs, they are.

Use time and time again and pivot tables again, just download this, just have a play.

You can't break it. Just move things around.

Try some of the options.

But, very, very powerful analysis tool and as if by magic, that's exactly half past two.

So I hope this has been useful.

I hope this has been a useful rattle through these various techniques, all using the same pieces of data.

Make sure you download it, make sure you download the the solution to this as well.

And if you wanna catch up on this, the recording will be available online, pretty soon as well.

Okay. Thanks very much for your attention.

Just before I close down, anyone got any questions, jot them in the chat box.

Otherwise, hope to see you on another webinar soon.

Okay, cheers. Thanks very much then. Thanks David. Thanks a lot.