Welcome, welcome.

Uh, what we're gonna do, uh, so my name's Jar Kelly, welcome to this session, brought to you by Financial Edge Training, the things we're gonna be coming today in database, uh, analysis.

We're gonna be looking very briefly at each of these 'cause we've only got half an hour.

We've only got half an hour unfortunately.

So we're gonna be looking very briefly out sorting, spend about 30 seconds on that.

Then we can look at filtering some myths.

Defuncts and pivot tables. Pivot tables. Pivot tables is my favorite here.

I'm gonna spend a little bit of time on that.

Show you how you can take a great big data base and all of that data and content and actually find some stuff out from it.

Okay? Actually get something meaningful from a humongous, very big database.

Cool. Fantastic. Now we've got lovely Enum.

Who's helping us out? Who's assisting us? Gonna be putting the materials into the chat. So you guys have everything that you need.

Let me get straight into it.

I'm gonna be opening up this file here.

So database analysis work out one empty at version two.

Ah, someone's saying that my, that my, yeah.

What? Pardon me? People are getting some issues from my, do you know what? I have noise canceling. Lemme turn that off. Maybe that's help hurting us. Whoops.

Turn that one off. Get myself a new microphone.

auma. How's that? Is that sounding any better? Has it made any difference at all? Better? Hey guys, I'm so sorry. Let's start that again. I'm Gerard.

Hey, we got people in the chat telling me it's better as well.

Cool, cool, cool. It's all due to the settings.

So welcome, welcome. My name's Gerard.

So I've been a trainer here at Financial Edge ever since we started out.

I'll be here forever. Uh, I teach in all the big investment banks and I'm teaching you guys today what we're gonna be looking at, database analysis. We're gonna be focusing on these things for about the next 25 minutes.

We're gonna start with sorting only for a few seconds on that.

It's very simple. Then filtering little bit on some Fs, ands, but I really want to get into pivot tables if we think what a database is, it's an enormous and enormous set of data and it's too big for any human being to make any sense of it unless we've got tools to help us.

So we're going to filter things down.

We're going to be using pivot tables to make sense of it.

So my focus is gonna, in this session's, gonna be on filtering and filtering and pivot tables.

If we've got any extra time, then of course I'll go into the others, I'll go into defuncts, et cetera.

Okay, so the file that we need is this database analysis workout, one empty that's in the chats and I can see more people turning up as well.

So we'll keep putting that in the chats so that you guys have access to that.

All the new joiners keep, um, can see that file.

I'm gonna start in the sorting tab.

So let's go to the sorting Harden me.

Let me just make sure that I've got all my shortcuts working.

So I go to the sorting tab and in the sorting tab all we've got here is a great big humongous database. Let's have a quick look. I'm gonna jump to the bottom and it's going to be in row I karumba.

We've gone down to row one thousand eight hundred and one thousand eight hundred and ninety five. That's a long way. So just a little bit on sorting and filtering here.

If I go up to my ribbon and I go to the data ribbon and we've got this very simple, I can sort A to Z or Z to A, but I can also then just click on this sort and we get this little popup and the popup allows us to sort by one thing.

Then another thing and then another thing.

So let's get it sorting firstly by the client account.

We've got lots of client accounts here.

Client like account two bets and they have made lots of sales on their accounts from Vari on various locations and products and dates.

I'd like to sort them.

So I'm going to sort first of all by client accounts.

I'd like to go A to Z, but then within the client account I'd like to sort them by date.

So I'm going to add a level date and then if we've got anything, if we've got multiple transactions happening on the same date, I'd like to have them sorted by product.

So add another level, then buy product.

And if I press okay, I get all of account one ash first and that's great. That was our first thing we wanted to sort on.

Then I wanted to sort on dates and I can see the March transactions before the April transactions.

And then I wanted to sort on product and I've got equities, then liability driven, then money markets.

So that sorting, sorting iss awesome. We love sorting.

It's kind of useful, it's kind of simple and it actually kind of makes sense as you click on it.

It just makes sense. Everything you see there, nice and straightforward.

Okay, I'd, let's go to filtering now guys, let's go to the filtering tab.

See what we find there in my filtering tab, I'm going to make sure that I've clicked somewhere into this data just somewhere at all.

And up in the data ribbon next to sort we've got filter.

So I'm gonna click on filter and these dropdowns now appear.

Now there's some cool things that we can do in these.

So I'm gonna go to the client account.

I'm gonna click on that dropdown.

You can sort just sort that. So that's amazing.

Sort eight to Z and Z to eight.

But you've also got the ability to turn on or off some of those accounts.

I'm gonna turn off selector, so unclick that and I just want to look at account two bets.

Okay, so the bets, these are people's names, so, so we've got, you know, the the Ashley family, we've got the bets family.

So I press okay and I've now got just count two bets.

But I want to filter again, I want to make my huge database a little bit smaller.

So I'm gonna look at just certain products that have been transacted here.

So I go to my product again, I click on that drop down or I can press alt down arrow.

And we've got all of the different products available to us here.

I'm going to Untick select all and I want to just look at equity transactions by this client.

So I tick that one press, okay, amazing.

I've now got a much smaller list of items and you might notice on the left hand side we've got these blue numbers, the blue items, the blue I, the blue numbers show me that certain items are being hidden from us.

So that's really cool. Great.

If you've just joined guys, the materials are all in the chat, so please feel free to jump on them.

So we'd like filtering Filterings, pretty cool. You could do some good stuff here.

The one to point out is the date.

There are some special date filters here.

If you go into the dates, lemme just share that one more time.

Go into this date dropdown and then we've got these special date filters here.

Things like next week, last week, last year, year to date.

And you can even do custom.

Okay, there's loads and loads of good stuff here in the dates.

One last thing that's really cool that you can do with filters and people think, oh, filters, yeah, they're cool, but you know, how cool can they get the answer is really cool.

So lemme show you, I want to sum up the sale amount, okay, so I'm gonna uh, just for account two bets and just for equities.

Uh, and so let's do one more.

Let's change the location, let's change the location.

So it's just on the east so we get a much smaller set.

Let's press ok. Hey, that's much nicer.

So I've done account, client account.

So account two bets, product, just equities and location east.

Now I'd like to sum those items above here.

So I'm gonna press a favorite shortcut in the world.

Alt equals to sum them up, if you're on a Mac, it's command shift T to sum them up, say, oh yeah, just do old school, some function like that.

But I want to particularly do alt equals or command shift T.

'cause look what happens, instead of summing everything in the huge list in the 1900 items.

Instead we've got the subtotal function that's happened and the subtotal function is very particular 'cause it only sums the items on show.

Oh, we love this, we love this.

Okay, it's not gonna be summing the hidden items.

If I had done a sum function manually, so equals sum, that just sums everything in the list, including the hidden items.

So that's the subtotal function.

And one extra thing to point out there, this number nine, if you delete that number nine, you can see, you can actually find the average of the subtotals.

You can count the number of items there. So let's just, let's do count, let's do number two, press center.

And it tells you I've got 8, 1, 2, 3, 4, 5, 6, 7, 8.

Yes, very good. There's loads of cool things you can do with databases and filters.

And then use the subtotal function to help you out to get rid of all those hidden lines. Awesome. Cool. This is pretty good so far. Excellent.

Let's continue. I want to go on to a little bit of pivot tables.

Love a bit of pivot tables.

So let's go to the pivot data section.

And in our pivot table section we've got loads and sorry, we've got the same database, we've got loads and loads of line items.

Excuse me for just a moment. I'm just going to blow my nose.

Always happens in the middle of a presentation.

So just gimme two seconds.

Pardon me. The curse of allergies during hay fever season.

Okay, so let's go back up to the top.

So I've got this very large data set, lots of items I'd like to start understanding and using that information better.

What I'd love to do is I'd like to find maybe the list of my clients that we've got here and then do something with their transactions.

So I'd like to basically summarize the information.

Pivot tables are gonna help us do that.

So to get to my pivot tables, I'm gonna go up to, pardon, make sure we're looking at the right thing.

I'm gonna go up to the insert ribbon and the left hand side of the insert ribbon, we've got this pivot table here.

If I click on the dropdown for that, it says from table range or from external data source.

So I'm gonna make sure that I've just got myself clicked somewhere in the data, then got to pivot table from table range and it automatically selects the whole dataset. That's great. I then check select a table or range from this that's been selected.

Yep, perfect. Works it out. All right.

And then it asks me, where do you want your pivot table to be placed? Do you want it in a new worksheet? For this particular session, I want to put this in this existing worksheet.

I wanna see both of them together.

So I'm gonna say existing, but then I need to choose a cell where it's going to appear. So I'm gonna click on this little arrow here.

It's gonna choose the cell to put it in enter.

And then, okay, now I'm gonna zoom out a little bit just to make sure that we can see this.

And what I've got is this kind of blank table.

Now you guys might look at this and you might suddenly find that you've got this sidebar pop up. That's great, we want the sidebar, but if I click outta this pivot table, oh the sidebar disappears.

Ah, we've start to freak out. Where's it gone? All you need to do to get the sidebar back is click somewhere in the pivot table.

Great. What we're now gonna do is we're gonna start choosing information from the right hand side and drag those fields or drag those items into the bottom four corners.

Excuse me, just a moment.

Let's start dragging things around a little bit.

I would take the, uh, the client accounts from the top right, excuse me, from the top right here.

And I want that to appear in rows at the bottom.

Now you can see that I've done this already so it appeared in the right place, but you, it may not have appeared in the right place. So if it didn't, you can just pick it up and you can drop it in the right place.

So I've now got my client account in the Excel.

So each name only appears once.

So we've got client one, client two, client three, client four.

That's great. I now want to drop the sale amount.

I want to find about the sales that we've had from each account or how many sales they have done.

So I'm gonna click on sale amounts and I'm gonna drop it into values in the bottom right hand corner.

Hey, check out, I've now got the sum of the sale amounts for account one.

Zoom that in a bit. So account two, count three, let's imagine where an asset manager we're looking, our clients, we're seeing the volume or the value of the transactions they've done with us.

And we can see that we've got some pretty good ones here.

Hey, account eight Jen up at 28,941. That must be a really important client of ours.

I wanna make sure we look after them.

If we have a look at account five though much lower.

Ah, so this huge dataset has now told me maybe we need to give a little bit more love to account five.

They're not putting so many transactions through us at the moment.

Is there something that's going wrong or they're just having maybe holiday and they're chilling out? Great. Really useful information to help us out here.

Okay, let's start doing a couple more.

What I'm gonna do, oh, pardon me, I've got a question.

It says why not the sum of the sale values in columns.

Oh, pardon me. So rather than values go to columns, the only thing is if you're doing columns, it now gives you each individual item in a kind of weird way.

And I'm going to do that in just a second.

I'm gonna be using columns in just a second to help us out.

But I'm just gonna put it back into values for now.

It's a great question.

You're very keen, you're about, you're about two minutes ahead of everyone else, so we'll, we'll, we'll get there. Definitely. So help me out.

I'd like to do another pivot table.

So what I'm gonna do is I'm gonna select all of this pivot table and I'm gonna copy and paste it.

So copy control C, just paste it underneath Control V.

Cool, cool, cool, cool.

Now at the moment it's summing the sale amounts, but Excel can definitely do more than that.

It can give us things like average and count.

And so I'd like to find the average sale amount for each of these accounts.

To get that go down to the bottom right hand corner where it says sum of sale amounts.

Click on the dropdown.

And then we have to click on value field settings, little unusually names. This one because it's doesn't really direct us very well.

Click on value field settings and what pops up at the moment we're using a sum, I'd like to use an average.

So click on average, Chris. Okay, Hey.

And now I see the average size of the transactions that each of these accounts is doing.

And again, if we have a quick look, yeah, account five, account five, still putting smaller transactions through us, maybe we need to give them a bit more love.

Okay, account seven.

Hey, surprising account seven is the largest single uh, largest average transaction must be giving us lower number of transactions.

Let's do a few more. Can we select all of that data again? Let's copy it And let's paste with control C.

So copy with control C and let's paste it underneath to get our third pivot table.

This time I'd like to add the columns. So thank you for that question about the columns earlier.

For our columns, I'd like to look at the products that each account's been selling.

So the products that each account has been selling with us.

Okay, so I'm gonna drag products from the top right here into columns in the bottom right and let's see what happens.

Hey, we've now got our major products appearing here and I can see the average of the sale amount for each one.

I'd like to change it back to the sum rather than the average.

Let's just look at the sum.

So how do I do that bottom right hand corner where it says average of sale amounts.

I click on that, click on value field setting, change it to some.

Fantastic. Now I've got more data being summarized from our huge database.

Okay. And we can have a quick look just with a naked eye.

And what do we notice? So outta the loan products that we've been um, transacting with these customers or these accounts, I can see that account one ash has been transacting the largest number with us. That's great. Cool. Very good for them.

If we have a quick look down some of the others, it can be a little tricky for our naked eye to find the numbers. Which one's the lowest, which one's the highest.

So why don't we apply some conditional formatting to help us understand the data.

I'm going to select just that data there.

Can you guys select the same data or if you're not following, just watch along.

And what I'm then gonna do is apply conditional formatting and that's on the home ribbon.

So I go to the home ribbon, click the dropdown for conditional formatting and I've got lots of options.

I'm gonna go to the color scales.

Okay, and could we just click on this one so the scales of green could go red and blue and et cetera.

Let's go on that one. Let's go with the top left.

And what do we notice? I notice that in my equities column we've got lots and lots of green, so looking really good.

But account for coal.

Account for coal, really not interested in our equities at all.

So maybe we need to talk. So account for coal, can we interest you in some more equities, maybe account for coal. Maybe we need to make a note on their account.

Hey, this client's not interested in this at all. We need to stop pushing it at them.

Maybe instead we need to be pushing them more towards fixed income and money markets.

But having a look at some of the others guys, look at a cat liability driven.

What a nightmare.

Liability driven is not a good seller for us.

The clients aren't liking it.

Maybe we need to put a bit more advertising or marketing and tell customers how good our cl our um, our transacting is here in this particular market.

Cool, cool, cool. Let's do another one here guys, can we please go back up to the, let's go back up to the top one.

Let's select We are running outta time.

We are running outta time. Before we run outta time, I really just want to have a quick go at something else.

I really want to get you guys just looking ats okay debunks are really, really cool and people dunno about them.

So let's have a quick look at ours.

So can we go to the D function tab? D functions allow us to do database functions and they allow us to alter our inputs really easily.

Let's have a quick look at the first question here.

How many units? Oh cool, just a quick question in the chat.

Can the link to register for repeat sessions please be sent? auma, we've got a great question in the chat if I could uh, just ask you that, have a quick look at that.

So first question here, how many units of loan products has the account one ASH client sold? How am I gonna find that out? How am I gonna find that out? That sounds like an awful lot of work for us to be doing just from this database.

Well luckily Des are gonna help us out here.

What I've got is I've got my database data table down here and I've then got the same, the same headings up here in Orange.

Don't worry, they don't need to be orange.

What I can then do is I can start looking at my question.

So how many units has the account one Ash client sold? So if I go my client account here, I'm just gonna write in account one Ash.

This is gonna help us do some really serious filtering.

It's good summarizing here and then it says how many.

So we've done that bit there. That's great.

Now I need the loan product.

So I'm gonna put that in the product section here.

So loans. So I've now input my two items into this little table.

Let's go to an empty cell.

I'm in F four and I'm going to use D Sum.

So it says how many units has the client sold? So that's a sum function I need to sum all them up.

And a D sum firstly asks us, Gerard, where's the database? Can you find me the database? Yeah, absolutely.

I'm gonna select my database.

I'm gonna go all the way down to the bottom, jump down really quickly, select all of it.

I've just selected my database and I use control shift arrows to do that control shift to select that.

Or you can use your mouse if I press comma Excel now asks me, Hey Gerald, where's the field that you want to sum? Well quick reminder, it's the unit, it's the units I want to sum.

So I'm just gonna click on units.

You don't have to click select the whole column.

That's the big error everyone makes.

So I select the units and then Excel asks me, Hey Gerald, where's the criteria? I will Excel will sum up the units, but it doesn't know which units.

So I'm gonna select the five to H six and what it'll do, it'll find all of the units that account one ash transacted in the loan products and if I close the brackets, press enter.

That's cool, that's very cool.

I've got just enough time to do ano. Another quick one here.

So let's do a D average.

So Daver again, I'm gonna select the whole database.

So I'm gonna go down to the bottom, select everything.

It then asked me, what field do you want? Let's have a quick read. The average units sold by account one ash in the south.

So it's the units that I'm gonna need.

It then says what's the criteria? Now I haven't done my criteria yet, so don't worry it will update. That's the cool thing abouts.

So in just a minute I'm gonna put account one ash.

So what was the average unit sold by account? One ash.

So I'll put account one ash in the client account section and then in location I'll put self.

So close brackets, prepare center, and let's do that.

So account one ash and then in the south and there we've got it. 41.8. Fantastic.

That's awesome. That's fantastic.

So guys, let me very quickly put the answer file to the entire um, file in the chat just so you guys have got absolutely everything and you're not wondering, hey, I wonder what that last one was.

Hey, let's just give you the file so you guys can do it.

Lemme just Cool.

So I guys, I've sent you the full file so that you can have a look at that material.

Oh, we've got someone asking please. Can this be emailed auma? I dunno if it can be emailed.

Do we know if it can be emailed? Ah, lots of people can't access the chat.

Hey, auma is going to email the answer file out to you. Thank you so much for that question. The chat's a really, really good point.

The issue, the issue every week. Yeah, so some of you guys are behind firewalls and you, you, um, and you're not allowed to chat.

So of course that's a really important point. Okay, cool. So we'll definitely email that out to you guys.

I hope you found that useful.

A really swift intro to databases.

So we did sorting, we did filtering, excuse me, we did sorting, we did filtering, we did pivot tables, little bit of conditional formatting and a little bit defunk, all in the space of 32 minutes.

So that was pretty quick guys, I hope you found that useful.

I hope to see you again on another session soon.