Comprehensive Bank Loan Data Analyst Portfolio Project Tutorial

hey guys welcome back to my channel so in today's video we are going to see a new data analyst portfolio project and

the name of the project is bank loan report and the domain which we have chosen in this uh project is a financial

domain or we can say a bank domain and this is the most used domain in realtime Industries also most of the data analyst

uh developer works on these particular projects only and these projects are very much important because these are I

can say very much uh critical or I can say they are tricky and they are bit Advanced so in this project we are going

to see each and every step how to build this particular dashboard and we will see or you will be able to you know

learn everything from basic to advance and after completing this project you will be in the position that you have

learned so many functionalities and you will be able to answer so many or we can say variety of interview questions as

well as you will be in the position to work in realtime industry also so in front of you you can see this will be

our final product so this is the first dashboard so we have seen total three dashboard or we will be seeing total

three dashboard in this complete project and we will see everything step by step in every tool and in all the data

analyst portfolio tools also okay so first you can see this is a bank loan report which is we can see a summary of

the report and this Pro this we can see this particular dashboard is more towards kpi dashboard we can see most of

or we can see we are uh you know showing different measures with respect to whatever we have in our data and we are

giving an overall idea of to our business or to our bank client whichever we are building this project to and you

can see this we have operated by using some filters over here and there are different kpis we can say these are the

header kpis or we can say the main kpis and then we have distributed our loan with good loan and the bank loan we will

see everything and with respect to that we have shown some kpis then there are some indicators also over here which are

totally Dynamic again and then we have shown a small grade view with respect to what are the loan status currently and

how are the numbers working with respect to that okay so if you see for different purpose so if I see for bad loan and

good loan also you can see and if you see for grade so for different gr grade if you want to see for grade A if you

want to see for Grade B if you want to see for grade C with respect to that we can see the

values are changing and we can you know slice and dice the data with respect to these filters if you want to see for

different states also if you want to see for Alaska you can see for Alaska if you want to see for California how the

figures are how the customer Behavior are there and how the bank is performing or how the loans are distributed in that

particular State we can say with respect to that so not only these filters you can add n number of filters over here so

for me I have added only this particular filters and with respect to that we will be also uh you can see there are some

navigation buttons over here so right now we are on a summary dashboard so when I click on this overview button so

it will take me to our second uh dashboard that is a bank loan report overview in which we will Deep dive in

the data we will see how the data is looking at granular level or at we can say at the deeper level and we will

generate some insights from there so with respect to that the header kpis will remain constant and we have

analyzed charts with respect to our problem statement which I will be discussing in some time so we can see we

have a line chart or area chart with respect to which we have total loan application then by state then by we

have by term by employee length by purpose and by Home Ownership so here you can see uh we have an option to

change the measure of this entire report okay so we can see we are firstly seeing by total application so let's say we

want to see the total by total amount received Okay so so here you can see we have changed the measure completely here

and we can see the report Now by a different measure that is total amount re and you can see the titles of this

particular chart are also Dynamic so when I see I want to see for total funded amount you can see the total

funded amount with respect to that the figures are changing the values are changing and we can you know see the

chart with different angles similarly we have different loans also here like if you want to see for good loan if you

want to see for bad loan so from here also we can can see different uh you know uh slice and dicing of the data

then we can see this particular by grade also if you want to see you can see by grade if you want to see by state by

state also you can see all right so not only this filters we have also applied some interactive filters so let's say uh

you want to see for um the term loan for 36 months so when I click on 36 month you can see all the datas are changing

right so in this way if I want to see for the employee length who is working more than 10 years so when I click here

the values will be changing and you will be getting the entire result with respect to that similarly for less than

one year similarly if you want to see by purpose okay so the loan has been taken for credit card so you can see with

respect to that we can see the data similarly if you want to see for mod modage so what is the home ownership of

that particular fellow so if he's on modage if he's on rent okay with respect to that the values are changing getting

so this will give us a different angles to look over to the data and the client will be seeing the data for

different different filters different fields which we are having and with respect to that he might be taking some

business decisions so we will see how to create this how to build the data and to make it more dynamic because we don't

want static dashboard dashboard should be dynamic where we can see the data with different angles right so now we

will jump to the next dashboard that is details so when I click on the details dashboard so it will take me to a grid

View and in this grid view we are showing all the Loan Data okay so with respect to a different loan ID uh we can

see different uh angles or different datas for that particular fellow how much is what is his purpose of taking

the loan what is his home what is his grade his subgrade then in that we can find out when the loan was issued then

what is the amount funded to him from the bank what is his interest rate how much is the interest rate with the bank

is giving the loan to that fellow then how much installment he's having monthly and with respect to that how much amount

has been collected till it okay so with respect to that we can analyze and we can obviously add more Fields if you

want here I have just shown 8 to nine Fields over here and you can see this is what how the you know uh the grid view

is performing where we can say details stab is performing and here obviously we can find out which are good loans okay

and we can take a you know grid and we can export it into Excel if you want to see bad loans so bad loans are these are

the total bad loans okay bad loans are nothing but we will see the terminology uh it is nothing but the loan which is

given and the people are not repaying the loan right in this way we will see the different

functionalities all right so this is all about this particular end product which we will be designing and now what we

will see uh let's see what what we will be seeing in this complete entire video step by step so before moving ahead into

our project I would like to introduce you to my website and this is available on OPM where I give different offerings

so in that one is you can reach out to me personally for one to one call for you know career guidance in data analyst

or you can reach out to me for interview preparation tips in both powerb and T and also you can reach out to me for one

onone mentorship or mock interview as well and mock interview both in powerb and table I will be able to help you in

all of this and in this particular mock interview I will give you I will take your interview as well and I will give

you the feedback also what you should improve and how was your interview with me getting or not and there is available

options for recording as well if you want to record uh you can select the options and you can just have an option

where you will get the recording of this particular video as well all right and also I will provide some digital

material as well here you can see the power by material is also available which includes all these topics which

are which you can see in front of your screen and all these particular topics uh those are already created by me and

designed by myself okay and all these notes are very much helpful let's say you have a power interview two or 3 days

later and you want to do a revision of all whatever you have learned so these are the goto notes and PPS are available

where you can just go through all of them interview questions are available of all rounds like technical

non-technical any non-technical managerial rounds client rounds right and also in technical level one level

two scenario based questions are also available and I will also provide you the power B two to three projects also

right with all the dashboard files I will provide which you can add in your resume right uh the raw material I will

share with you the problem statement of that particular project as well right similarly you you can find the same for

table for SQL as well and all this Consolidated is also available in data analyst complete material where you will

you will get to see all the data analyst tools which are available and in this also I will be Prov in you with all the

handwritten notes printed uh PDF notes will be also available with all the important topics

and in this the main important thing is I will provide you the road map okay and the road map is designed with respect to

current 2024 year with the latest technical we can say techniques are added in that and the we can say what

you should what topics you should learn that has been added and for all those topics I have mentioned the links of

different courses of YouTube videos of UD me which are best for that particular topic so you don't have to search and go

around and look for those particular videos and which one to see you what to see so these are handpicked by myself so

it will it will not uh you can see it will not waste your time in searching those you just have to click and you can

just start learning those particular topics and with this also projects are also included uh in each tool so you can

just click you can see you can read everything whatever you need over here and then uh if you find it helpful you

can go ahead and purchase it and you can see so last 3 months only I've started and you can see more bookings I have got

and you know so I was the top rated in data uh for uh we can say 2023 Year all right so similarly uh right now 30%

offer is going on only on data analyst material and is only valid up to 15th March 2024 okay and you can also read my

different feedbacks also you can read about me me as well over here getting or not so at the end I will add the link in

the description box of this particular video you can go ahead and visit if you find it helpful uh you can just go ahead

and buy it and also you can just click here and all the description is mentioned whatever is available in this

material it is mentioned and if you have any questions you can reach out to me at this particular WhatsApp number all

right so let's move ahead with our video okay so uh so this is a complete a data analyst portfolio project and in the

first part of this particular video we will see the mssl server okay so we are not only going to connect our powerb to

Flat files but we will be connecting it to our mssql server which we do in real time if you don't have mssql server if

you are using any other database like post gra or MySQL not to worry you can import the data into that and then you

can connect it to powerb if you don't have any server you can just connect it to flat file you can directly connect to

Excel file or a CSV file and you can start performing the dashboard okay you you don't have to you know uh if you

don't have the servers installed it is not that it will stop you from designing okay so first step which we will be

seeing in mssql server is we will import the data first okay then we will create a database okay with respect to that we

will write some SQL queries with respect to the problem statement which we are having we will generate the result we

will store that result we will create a document and at last what we have to do is firing some SQL queries with respect

to the different business problem we will compare our results with all the dashboards which we are creating in

powerbi or in t or in Excel and you can see this database queries can be worked in all other SQL tools or SQL databases

just there are some few functions there are inbu functions it kind it might be some date functions which you have to

change but all other some NC standard functions or standard functions which you are going to use will work in other

databases also okay then next we will be seeing the second part in this project is the powerb part okay and where in

powerbi we will be connecting our powerbi to mssql server we will see how to connect it how to bring the data in

mssql from mssql server to powerb and then we will build the report all right and then obviously I just showed you the

dashboard which we are going to build so this is the first dashboard that is the summary which we will be building from

start to end this will be our second that is over view dashboard and the third one is the grid view we will be

building from start to end okay and the most important thing next is the problem statement so for each and every

dashboard we have a different problem statements and with respect to these problem statements we are going to solve

our business problem so we have the first problem statement for our dashboard that is first dashboard is a

summary dashboard and in this dashboard as I showed you we are going to analyze mostly the key performance indicators or

we can say requirements for key performance indicators which gives us the overall summary of the business how

it is performing at higher level all right and the first key performance indicator which uh the client want us to

analyze is the total loan applications okay so he want us to understand and find the total number of applications

that are received during that particular period and in addition to the total loan applications he want us to identify how

many are the month to dat applications so in current month or in latest month how many applications have been received

and to find out the month or month growth or we call that as M that is nothing but by with respect to last

month how many percentage of increase or decrease is there in the loan applications okay so this is the first

kpi indicator second is the total funded amount the funded amount is nothing but how much amount of uh the loan is given

to that particular customer how much disbursment of that loan has been done to that customer okay and the same we

have to find it for month to date as well as for month on month third one is total amount received total amount

received is nothing but after funding the amount to that particular customer he have to pay or repay the back or

repay the amount which he has been received back to the bank and we mostly do it by you know uh with respect to

that interest rate he will be repaying that particular amount each monthly or we called it as Emi with respect to

installments each month he will be repaying that amount to the bank so we will also calculate how much is the

total amount received back from that customer and we want it overall okay not for that customer but we will find it

overall amount which has been received same for month to date and we will find month- on- month change for that then

what is the average interest rate okay so overall what is the average interest rate that we are charging to our

customers and what was the month on month and month uh we can say month to date interest rate where that also we'll

be finding out then we have to find out depth to income ratio that is DTI so this is a gauge or it is a measure from

where the bankers identifies that how is the customers Financial Health okay so DTI is nothing but we called it as de to

income R and from borrowers or that customers Financial Health is identified from here and the Bank decides whether

we should give the loan to that particular phow or Not by seeing the DTI of that particular phow the same we are

being uh you know uh we will be creating a kpi for this particular kpi or we can say measure second in this same

dashboard we are going to find out good loan and bad loans okay so good loans are those actually uh which the people

who take the loans and they are repaying it at time to time or they are repaying uh they have repayed completely fully

they have played fully or they account is current account that is the loan is ongoing and they are paying their

installments correctly and bad loan are those bad loan those customers who have taken the loan but they are not paying

their installments they have not paid the bank yet okay these are called as bad loan and with respect to that we

will see how the good loan versus bad loans are performing and with respect to that we have to find out how many are

the total bad loans or we can say total good loans that application percentage how much amount we have funded as a good

loan how much amount we have received as back from as a good loan that is same we are going to do for bad loan and at the

third in this particular dashboard only we are going to find out the loan status grid view okay so in this grid view what

we are finding out is with respect to loan status we are going to find out and chart of total funded amount total

applications with respect to how what is the loan status okay and with respect to this problem statement I have created

one more document here you can see in this I have in detailed mention uh what is how what is the problem statement

that we have received so I have not added everything in the uh PP but from here you can go ahead and you can learn

this document or you can go through this document and then you will understand that what is the actual requirement for

us so I will add this document in the description box you can download it is completely free not to worry about that

okay so this is a problem statement of the first dashboard the second one we are going to go is through overview okay

that is the second dashboard is overview dashboard in which we are going to Deep dive in the data and we are going to

find out for different charts how the or we can see we for different uh data at different granularity we have to find

out some uh insights for our bank okay and in that we are going to design some different charts and the first chart

which we are going to design is monthly Trends by issue date okay so monthly Trends by issue a date we have to create

a line chart and from this the customer will identify or the stakeholders will get an Insight of seasonality and

long-term trends in uh lending activities lending is nothing but the bank which we or bank which is giving

the loan to the customer we call it as lending the loan to that particular F okay so these terms are actually a

banking terms not to worry much about that I will be adding a domain knowledge document in uh the description

or I will provide you the domain knowledge document to use from where you will learn all the domain knowledge of

the this particular project uh so what are the different terms used how the bank disb this the loan what are the key

measures they take in account before giving the loan to that particular fil okay so not to worry about that all the

terminologies and all the domain knowledge document I will provide it to you and you will learn everything from

that the second which we are going to design in this is the regional analys this is by state and here we are going

to design a field map so with respect to different state how the activity is done then the third one is loan term analysis

then here we are going to find out the donut chart so in this we are going to allow the client to understand how the

distribution of loan is done across various loan lengths okay so at a loan length is nothing but the people are

taking loan for 36 month or they are taking the loan for 60 months so with respect to that how is the trend of the

loans okay then employee length analysis okay with respect to employee length is nothing but some people or employee who

are working from last one year 2 year 3 year 4 year more than 10 years so with respect to their employee length in that

organization or in that total career how much loan how much loan they are taking and how much loan bank is also giving to

them so with respect to that they will understand that uh with respect to that particular fellow or we can say that

particular fellow who is actually working how much amount of loan can be given to the right then the loan purpose

so why we are taking the loan okay we have to provide a reason to the bank that what is the purpose of taking the

loan okay and with respect to that purpose also we are going to design some bar charts and do some Metric analysis

then home ownership so that particular fellow who is taking the loan whether he is owning his own home or it is on

mortgage or it is his uh or he's rented in that particular home or he's a tenant in that right so with respect to that we

are going to analyze everything and for all these charts The Matrix which we have to show is the loan application

total loan applications total funded amount and the total amount received okay I mean to see that on all this

three all all in the six charts whichever we are going to design the metrics to be shown are this three which

are at the bottom of this particular PPD and I have already shown you how we are going to show we are going to design a

parameter and with respect to that whenever we click all the values will be changing with respect to that particular

metric okay so this was our second dashboard the third dashboard which we are going to design is a grid view or a

details stab and in this we are going to show a comprehensive detail dashboard which provide a Consolidated View and

from that uh we can see you know we will take a snapshot of key uh metrics and the data points of that particular

customer and we can generate a report from that and we can provide it to a higher management okay so these are all

the problem statements and I have just showed you the document from that document you will be learning uh you

know uh what is the Deep dive or we can say a comprehensive loan uh problem statement which we have I've just

mentioned a few points here but from there you will learn everything okay so you can go through the document for

problem statement then next whatever the functionalities you are going to learn in this complete project so we are going

to use SQL and we are going to use powerbi so in front of your screen you can see these are few I'm saying few

functionalities which you will be learning at you which are important but apart from this you will be also

learning many more functionalities okay so I I didn't have space to add all of those and I didn't remember which I will

be adding but more than this you will be learning so I request you to go through this complete video and I'm sure that

you will be at the end of the video you'll be very much uh you will be taking so much of knowledge with you and

so much of experience with you okay so these are the functionalities you can pause the screen and you can see what

functionalities we are going to learn all right next what softwares we are going to use in this particular entire

project the first we are going to use is MS office and in that we are going to use Excel which the version which I'm

going to use is 2021 version the server version which I'm going to use for mssql server is

19.0 and also we are using a SQL Server management Studio which is 19.02

29.0 okay and the powerbi version is the June 2023 version which was the latest version which was released uh by powerbi

okay or you can use old version also but in this latest version we have a new addition called kpi card and we are

going to use or we are going to make use of that kpi card in this particular power vi video okay so whatever the new

updates are coming with respect to that we are going to design our dashboard so you will be updated what new

functionalities are coming in the market for powerb all right next and the most important thing guys I know you like my

video and you people enjoy I see your comments those are very positive comments which give me motivation to

create such videos but most of you just watch the video and you do not subscribe so I request you to please subscribe the

channel so it is free for you but it will give you uh give me more motivation to create such videos and it will help

me a lot and you people also it will help to reach out to more data enthusiasts okay so I request you to

please like this video subscribe the channel and share it with your friends who are learning data analytics or who

are in the uh process of learning the data analytics all right so before starting with our first part that is

with SQL so I will give you a quick data walkth through so this is the data which we are going to use for our analysis

which is a financial Loan Data we called it as a bank loan date also and here you can see the total rows which we are

having is 38 ,000 almost yeah 38577 and the fields which we have in the rows are 24 so first we have is the

ID then we have address dat so ID is nothing but the loan ID of that particular fellow then we have address

dat so that particular individual is belonging from which particular State the application type whether he's an

individual application or he's a joint particular fellow then the employee length like he's working in an

organization or he's in his career from how many years okay so for 9 years 10 years or 10 plus years or 3 years then

what is his title okay in that particular company what is his particular title title or what is he uh

you know uh working as an okay that is MKC accounting or an contractor or whatever it is then what is the grade it

is like a b c d what is the grade of that particular loan and the home ownership so in that particular what is

he living he's living in mortgage home or he is living in a rented home or there are some other home also like we

can say uh let's see so I would just apply a filter over here so there are some other also like

he is living on other or he it is his own home or we don't have any information of his own home ownership

right then when was the date issued for that particular loan okay that is issue date and then what was the last credit

pull date then what was was the last payment dat which he have done then what is his loan status whether the loan has

been fully paid or whether it is a current loan or it is been charged off okay so charge off loan is nothing but

it is a bad loan because if you are charged off is nothing but you are not paying your installments correctly and

you are not repaying your loan which you have taken from your bank fully paid is nothing but that the loan has been fully

paid and uh the third one is the current is nothing but the loan is in ongoing process but you are paying that loan

installments regularly right then what is the next payment date so next payment date this is the next payment date right

so you can see the next payment date then we have a member ID then we have a purpose of loan why the loan has been

taken for purchasing car or for purchasing any other things right you can see car the Deb consolidation or

medical purpose or some another major purpose or business vacation wedding whatever it is then we have a subgrade

then the terms for how many months the loan has been taken 60 month months or 36 months then the verification status

of that fellow whether that particular is verified not verified or uh we can say Source verified okay and what is his

annual income okay how much does he make monthly with respect to that uh his DDI has been to income ratio it it has been

decided and with respect to that only his loan is being given then we have installments like how much installments

he is paying monthly okay like we can see Emi and what is his interest rate how much interest rate has been charged

to that particular fellow for taking that loan okay then how much loan amount he have taken how much loan that is been

given to that particular fail okay we can say uh you can see here the loan which is given is you know like $5,000

$4,500 then we have total loan repayment like how much loan has been uh you know uh or how much amount has been repaid

from that particular pH and you can see if it's given 4,5 $500 he's repaying 4,911 with respect to the interest rate

which has been charged to him some people also pay some lumpsum amount and close the loan so with respect to that

how much the loan has been taken and how much repayment has been done from that customer and from this the business

actually the bank make the profits from the interest only so with respect to that we are going to see entire thing

and the terminology or we can say how what is the actual uh uh we can see meaning of each and every

fields which we are using here we have created one more document for that that is the terminology that are field using

data we can see what is the employee length what is the purpose of this uh and what is the use of this for Bank

okay and the title why we are taking the title and what bank understand from that title okay so we can see Home Ownership

the home ownership indicates the borrowing houses housing status what is is there and they use this field for

collateral availability and borrowable stability and home owners may have lower defaulter rates right the people who are

actually owners of that home so it means that they have they are owning that house and the house they might have

taken by taking some another loan and then they are repaying that loan so they mostly prefer to give home loan to or we

can say loan to from the bank to home owners right the same with respect to loan status next payment that you can

learn everything from here why that field is been shown in that data and what is the purpose of that field to be

present in that data and how the bank you know uh utilize those data for their knowledge okay so this is the

terminologies which are used I will provide you this document not to worry about that also okay so these are some

different terminologies and they have one more document which is called domain knowledge document okay so from here

banking domain is something like not all people have the knowledge of about this particular project or we can say domain

so I have created a document you can read the document how the loans are given how the bank process the loan how

what measures they take before giving the loan to that particular fellow okay you can see process of granting the loan

how the process is given or how the process is there for granting the loan and why the people or the reason been

for analyzing the bank loan data why the bank is analyzing this data which is been provided to us okay with respect to

that all of the things are present over here and with respect to that you can you know learn everything and you will

acquire some do domain knowledge so while we start actual analysis you will understand why we are analyzing these

what terms we are using what we can say terminologies we are using okay so I hope you are understanding so this is

the complete about the data and the domain knowledge of this particular data so now we will start with our mssql

server so for that I will open my mssql server so if you don't have mssql server installed there are many videos

available on YouTube where you can search and you can see how to install the mssql server and the mssql server

management Studio as well okay so both are required so first you can just type here server management studio so you can

see you will uh get an mssql server management Studio I have just click on that so it will take some time to open

and when it opens it will ask you some authentications you can see it is asking you a server name okay so this server

name is automatically been taken so I have seen many people who are using the same server name which I have applied

over here don't use the same server name this is the server name for my system okay you will uh get your own server

name when you install your uh mssl server in your own PC or in your own laptop or your own system so this is for

me when I have installed you will get your own name when you install it okay so I will just click on connect so you

the other options you have to keep as it is you don't have to apply any password or anything because this has been

installed in local so whenever it is hosted by your client they will provide you the username and password but if it

is present in your own local system you have installed it you don't have to provide any password we just have to

click on connect so when you connect there are different uh functionalities available here so you can see this is my

server name and there there are different folders available the first one is the database okay so when I click

on this plus icon and when I expand this you can see there are different database which I have already created and which

are available over here so what we have to do first is we will create a database first okay so now to create a database I

will right click here and I will click on new database so as soon as I click here it is asking me what should be the

name of the database so I will name as bank loan DB okay so I will name it as bank

loan database okay so you can name anything uh you can either name uh loan database or Bank database whatever you

want okay so it is not compulsory that you should mention this name only then I will just click on okay

okay so you can see the bank loan database has been created over here when I click on the plus there are different

folders available over here also but now we have to import the data here you can see there are different tables available

here but right now we don't have any tables there are just system tables which are autocreated by creating this

database but we have to you know import our external file into this particular database so how to do that so first I

will click on this then I will right click here and I will create a new file okay so for that I will go in tasks okay

right click go in task and you have an option called as import flat file okay so I will just click on this import flat

file then I will just click on next and then here you have an option called browse okay so first I will browse my

file from uh here so I will be using a CSV file okay this CSV file I will provide you you can download in the

description box the link will be there and always remember it should be a CSV file you cannot import the Excel file

into our SQL Server management okay so I will just click on this and then I will click on open right so then what this is

the new table name so what should be the name of the table so I will name it as Bank

Loan Data so I will rename the name of our file which is a bank which should be new name which will be shown into our

database the file will be the Financial loan but the new name which will be importing or we will be adding into our

database this will be bank loan data and then I will click on next so it is saying that the name cannot be different

so when we are trying to import the file into our MSS server we are getting actually this error and this error is

showing that please check if the if it is running by another application means what the file which we have or which we

are trying to import here it is already opened in Excel we can we I will show you yeah so it is already open here so I

will just close this file I will first save and then I will close this file and I will click on okay okay and then we

will try again so I will just click on next and now it has been importing here and here it is giving us some preview of

50 rows that in this type the file will be imported into our data okay or into our database so next I will just click

on next and then we have got list of fields which are there into our uh we can say CSV file and the data type which

uh by default the SQL have you know managed to show us so here we will do some changes we will try to change some

data types as per our requirement so first you can see ID so as in our data always remember the ID field which is

there in our data is a primary key so each row will be having an unique key for that so I will just click it as a

primary key and next you can see employee title it is allowing us null null means what in this employee title

there are already some null values are present so don't worry about that that the data is not clean so there is an

information or there are some information collected from bank that for few few of the customers they don't know

what is the employe title so it doesn't matter okay so what we will do wherever you see nare 50 we are going to change

to Ware okay so wherever you see and Ware I will just drop it down and I will choose this as Ware 50 so wherever you

see it you have to change it so I will just do it quickly so for employee title also we

will change it next here also we will change it and here as well so for few more we

have to change it again okay you can scroll down and for all of them we have changed to nare so

from nare 50 you have changed it to Ware 50 50 is nothing but the length of that particular seal so how many text can be

taken by that field so up to 50 text it can be taken okay now what we will do I will just click on next and then I will

click on finish okay so now it it is giving us error for inserting the data so let's see what is the error so when

you click here you are seeing that error is there in column 23 and it is for total payment and it is giving us an

error that the type string for the data source cannot be converted to small integer okay means this is taking as a

small intt we have to convert it into something else okay because the value is too large to take it as a small integer

okay that is for column 23 total payment so I will just go okay I will go back I will go back again and it is for total

payment here so we will change this small int and we will take it as int only so for integer it will take maximum

value for us okay so we will do this for loan amount also because loan amount also have big values so I will do it for

loan amount also as end and and then I will just click on next then again I will click on finish so

again it is giving us an error so invalid employee length Okay so it is giving us invalid employee length uh

column inval column L from bpc for callid five okay so uh so employ column length we have to increase for few so I

will just click on okay go back go back and so if we open our file here so let's open our file

first so okay so this is our file and I will just increase the column size so here you can see there are few employee

titles that might be greater than 50 okay so what we will do for our employee title we will increase the size first

let's do that so for employee title let me increase it to 100 okay so I will just

increase this to 100 okay

similarly let's see if there are for any other also okay it looks good then I will just click on next finish still it

is giving us an error so it has been opened in the another value so I will just save this

so it is opened in another application it cannot be open so I will just click cck on okay again I will go back again

go back then I will just click on next and then I will click on finish okay so it should be not opened in background

and we have to increase some of the L so now you can see we have got an operation complete so many people receive this

kind of errors and they message me in comments and all those things sometime I'm not able to answer the comments but

you have to click on error and then you have to find and you have to read carefully each and every line what is

the error which you are getting okay so with respect to that after reading 50% of your problem is solved so if

sometimes if it is not soled you can reach out to me I will help you okay then I will just click on close okay so

now here you cannot see any table which has been created so I will right click over here and I will click on refresh

okay so after I click on refresh and I drop down this table so it is expanding and you can see the data has been added

over here so let's see if the data is visible in our query or not for that I will right click here and I will select

new query okay so once I do that a new query window will be opened over here and here we will be writing our SQL

query so I will write query to retri all the data that is Select star from the name of the table that is Bank Loan

Data okay this is our bank loan data and I will just run this query so as soon as I run this query you can see we have

received 38576 rows the same rows which we have you know I have shown you in the Raw

data and all the fields which are there we are able to see here correct so these are the data so I will just open the

data and I will again show you so how many data we actually have and how much has been received so here you can see 24

fields are there and if I see the total rows are there 38577 so it is calculating the header also so ID is

also one row which is been calculating so if you neglect that row it is how many

38576 okay so if I calculate from here only so you can see it is 38576 and the same number of rows are

retried here that is 38576 okay so you have to cross check and you have to check if each and every

field has been calculated or it has been present or it has been bought into our calculation or not okay so in this way

we have imported our data the next next task for us is to you know uh start firing the SQL queries with respect to

our uh we can say problem statement okay so now I will will take a new query to write on new line and and I will show

you our problem statement first so let's go to our problem statement so you can see here this is our first problem

statement and in that first problem statement we have to find out the key performance indicatives and here it is

asking us first to find out the total loan applications okay so now to find out the total loan applications the

total loan applications how we can find it out so come to our SQL and here we can find out the total loan application

so we know that ID your ID field is a what we can say primary key and it is different for each and every row so

number of applications is been dra each row is an application okay so here each row is an customer application which

gives an information of that customer who has applied for the loan and the bank has dispersed the loan for that

site okay so we will find it out from ID and for that we will write a query as select and here we have to take count I

will take select count of we have to calculate it for ID so I will just type here

ID from from which data that is Bank Loan Data okay and I will just select this entire statement and then I will

run the query so as soon as I run the query you can see 38576 are the total number of

applications that is nothing but total number of rows in our data and that is nothing but the total number of

applications but at the top you can see no name the column or the out of this particular SQL does not have any column

name so I give you an alas over here alas is nothing but a temporary name which we are giving for an hour output

okay so I will give an as and it is nothing but total applications okay so these are what our

total loan applications I will just modify it as total loan applications and now I will select and you can see an

execute button is there I will just run this now you can see the header has received an uh we can say U column name

and these are the total load applications now guys what you have to do is you have to save this query okay

you have to save this query and you have to also save this result so how to save the result so I will show you the

document which I have created so the query doc you can see here so this is a query Doc and what we are doing here is

for bank loan reports that is the first dashboard for kpis we have determined the total loan ation so I will save the

query over here and then I will save the result over here okay so what you have to do here is I will just show you SQL

so you have to take screenshot of this particular uh we can say uh the value or the result which you are getting so for

that you have you can use a Snipping Tool okay you have a snip tool in every system or if you want you can use any

other tool which you have so take a new and just you just have to select this okay you just have to select this then

if you open here the value has been selected you have to copy it from here and you have to paste it in your

document okay so in this way you have to save your results and you can just select this contrl C and you can copy

the query also and you can save it in your document so why to save this okay why we have why we are saving this

particular query and why we are solving this first in SQL so many people ask me that uh sir why we are doing this why we

are firing the SQL queries and also we are showing it in our dashboards in our powerb reports and again we are doing it

in SQL also so always remember whatever values which we are showing in our powerbi or in our table or when Excel

dashboards it is not compulsorily true that it is showing a correct value okay because table and powerbi is garbage in

and garbage out means whatever we are bringing into visualization it will show some value for that but how we will

prove to our client that the values which are shown in this dashboards are correct or not so for that what we are

doing with respect to the problem statement which we are having we are firing some SQL queries and with respect

to SQL queries whatever result we are getting we are matching those result with our dashboard numbers whichever we

are showing to our client if the numbers are matching correctly then and then we can say that the dashboard developed is

in correct way or the developed dashboard can be given to a Productions uh we can say to our hire management to

our client and it can be pushed to production servers otherwise if the QA or if the testing is not done we cannot

push it ahead okay so that's the reason we have to check with our original data that which comes from our databases and

to check it we have to fire some SQL queries with respect to our problem statement which is given to us by our

client so we have to save the results okay so first always remember we have to save the results in This Way in real

time also we have to save the results because uh let's say you are a developer today tomorrow a new developer is

working on that project and he want to see what queries were used to fire what were the you know different function

used to see whether that values were correct or not and this also Second Use is all what that you have to send this

to your client also because whatever dashboard values you are sing those are matching here and this is the proof that

those are matching and that's why they have sent you or you have sent this dashboard to your client for actual use

and taking the business insights from there and taking the business business decisions also from there because with

respect to these reports only big decisions are taken and these big decisions should not be incorrect so

that for that we have to cross check if you are showing the correct values or not okay I hope you are understanding

why we are firing the SQL queries here okay and why we are creating this type of document here all right so the

document format might be different from company to company so I've shown you a simple format but always remember we

have to create a document of testing whatever we are doing the unit testing of our dashboards all right and first we

are doing this in SQL and then we are creating it in powerb sometimes first we create the PowerBar dashboard and then

we fire the SQL queries to check if it is there or not so it depends from company to company and from client to

client how they want it all right so this is for uh total loan applications from Bank Loan Data correct now we will

see our next problem statement in that only he is asking us to find out the month to date okay so now how to find

out the month to date so if we see into our data the data which we have I will show you in the Excel

sheet so I will show the Excel sheet here I will just take it at top and I will apply some filters over

here so whatever we are going to find out the month on month uh total application month to date total

applications we are going to see it with respect to issue date issue date is means when when was that particular

disbursment done for that particular Phil means let's say any fa have or any customer have come to the bank and he's

asking for some loan so issue date is nothing but on that particular date the loan has been disused or it has been

given to that particular field so this is our issue date so on on issue date only we are doing all the calculations

we are keeping it as simple as possible you can do it for other dates also but at this project we are keeping it at

simp as possible so if you see in the issue date we are seeing the data only for 2021 and that all for 12 months so

the latest month which we are having in our data is December that is also for year 2021 okay so what we will be doing

is uh we will be using the same trick so we have a December month over here so December is the last month so for

December month only we have to find out the uh sales so what I will do not sales the total loan application so I will

just copy this and I will paste this over here and now we have to find it for last month so I will check or I will add

a filter condition where month okay where month of issue date because I told you we are going to find it for issue

date is equal to 12 okay so what does this mean so we are finding out the total applications but we have applied a

condition that the month of issue date should be 12 it should be for December but December name of that or we can say

count of December month is 12 so we have found we have taken it as 12 and if there are multiple years okay let's say

we have 2020 year 2021 year also then we have to add one more filter condition over here and okay at where month of

issue date is equal to 12 and year of issue date okay year of issue date should be

equal to what 2021 okay so this is the way we are solving

this so when I run this you are getting an uh the total number of application that is

4314 that is for that month or we can say 12 month that is nothing but December month but it should be named as

month to date so I will rename this as Monto dat loan application and when I run this we can see it is month to date

total loan application is 4314 why I have taken year of issue data is 2021 because if in our data there are

multiple years are there and for multiple years there might be multiple December months but we have to find it

for latest year whichever will be the latest year so if you are going in next year that is for 2022 you have to

mention your 2022 we can also make it Dynamic by you know uh making the maximum value whichever the month is

having so you can do that also in this query if you want to go Advance you can you know instead of hard coding the

values over here you can go ahead and make it Dynamic by taking the maximum month at thematically okay in this query

this can be modified over here all right so next thing what we have to do we have to find out month on month okay so

always remember month on month is nothing but if you see our problem statement here we have to find out month

on month sales so month on month is nothing but how your bank loan uh disbursements are done with respect to

last month and current month so let's say for last month the value is this much for this month the value is this

much so find out the percentage increase or per percentage decrease right so in this way we have to

calculate it but we will only find out the last month that is previous monthes I will just click here and I will click

here as previous month to date alone applications so for previous month is nothing but now is December the latest

month so last month will be what 11 that is nothing but November so when I run this query you will get the value as

4035 that is nothing but previous month to date application that is pmtd so if you know previous month if you know

current month we can find out the month on month as what it is the formula which we have to calculate the month on month

applications are that is nothing but m month toe loan applications minus previous month-to death loan

applications divided by previous month to date loan applications okay so this is the formula which we have to

calculate so we are not going to find out month month on month calculations for each and every we can find out that

that query will be somewhat we can say tricky and I don't want you to get confused by using the large number of

queries or complex queries so keep it as a simple as possible you can do this uh you know manually also like finding we

have one toate value we have pmtd and we have pmtd here also you just you can just calculate the percentage over here

so we will keep at is as simple as possible for now okay so in this way we are going to find out just month to date

and previous month to date right and we have also find it out and we have completed our first first KP all right

so next is what in this way only we will find it out for total funded amount okay so now this was for total loan

applications now we will find it out total for funded amounts I will just close this for now and if you can see I

have recorded here all the results so this is for month to date so you can see I have not changed the column name over

here so this should be what month to date then our output will change over here so I will do that uh when I send

you the query document so you have to take the those snapshots only which I am using okay or which we are showing in

your or I'm showing in this particular video all right so now next what we have to do uh go ahead is find out for the

total funded amount so I will run our this query again to see the data so total funded amount is nothing but this

is what you can see here the loan amount so loan amount is nothing but amount which is given to that particular

customer that is nothing but the amount which is funded by our bank to that customer so we are going to use this

particular column to find it out so we will make change in this query only I will just write here so we will be

finding at some of sum of loan amount so we have to take sum of loan

amount okay we are not taking count we have to find out the sum of the total amount which has been dispersed that is

nothing but total funded amount and we will name it as total funded

amount from Bank load data so as soon as I run this query so you will get an value over here

that is almost 435 million of uh loan amount has been disused in that particular complete year okay and now so

again we will record this result next we have to find it for month to date okay so to find out month to date again we

will add a query over here that is we will add a filter that is for month okay so month of issue date okay

on that particular date issue date is equal to 12 that is nothing but December and year should be what year of issue

date should be equal to 2021 that is nothing but the latest here and when I select and run

this query you can see 59 53.9 million that is nothing but 54 million amount was disused in this particular current

year month that is nothing but the latest month and this is nothing but month to date so I will just name this

alas of this uh output column as month to date and when I run you can see month to date total funded amount is 53.4

million okay or we can say 54 million next we have to find out for previous mon to dat so we will just modify this

query only or I will just select this query contrl C and I will take a new query here and here we have to write out

pmtd and here we have to just take 11 that is nothing for last and when I run this particular query and

when I execute the you can see it is what 47 or we can say it is 47.7 million okay

so in this way we have found out Monto date and previous Monto dat and from here you can find out the month on month

total funded amount okay so in this way we have calculated for total funded amount okay if you are understanding one

the next are same the query just we have to modify next we have to find out the total amount received so I will just

delete this first and again I will run this query so the total received amount so we are which column you are going to

use so you can see the total payment column so this total payment column is nothing but the payment which was which

is received back from the customer in terms of different different installments monthly yearly and with

respect to that it is nothing but the total amount received to the bank okay and from this only they do the profit

okay so this is this amount is important so with respect to different interest rate the values are or the amount is

taken back from the customer so let's calculate it so for that we will use select again select Su of what total

payment so total payment that is nothing but amount which is received back to theay bank

from from Bank Loan Data okay and this we will name an alas as total total what should it mention total

amount receive okay okay and then we will run this query and when I run this you can see

473 million amount has been received back from the customers okay so you can see in this query also I'm saving

everything okay so you can see the total amount received is 4739 C3 so save the results whatever the

query you are firing you have to save the results so that we can compare later with our dashboard so that whether we

are getting the correct results or not okay and I have told you why we are doing this all right so now we have to

find it for the current month that is for month to date so where where here we'll write as month

of issue date month of issue date is equal to 12 and

year of issue date should be equal to what 2021 because that is the date which we are

having see for currently for our scenario this is not required but this I'm telling you because if the data the

data which we have in our uh database if it is greater than or if is having multiple years of data then we have to

mention from which month we have to retrive the data okay so which year we have to retri sorry sorry so I will just

select this and here you are getting the month to dates I will just mention as month to date as an output alad here you

can see 58 million amount has been received back in this current current month okay if you want to find out for

previous month to dates I will just copy this query and you can run it again here just you have to mention your previous

month to date and this should be 11 that is for November month and when I run this Square it is 50 million so with

respect to current month or we can say previous month uh the current month or we can say the latest

month amount received was greater okay that was almost 58 million and in this only 50 million was there in November

month so in this way we are taking the insights from this particular data and we are saving our results obviously okay

now next what we have to find out is average interest rate okay so I will just close this

everything and I will run this so when I execute this we have an column here called as interest rate so here you can

see intore rate it is nothing but the interest rate so for that we have to find out average interest we don't have

to Pi sum here so we will write select and here we are taking average okay so average interest rate so this is the

name of field in our data same we have to type here that is intore rate and we will name it as

average interest interest

rate okay from Bank Loan Data okay and when I run this you can see we are getting an

average interest rate of 0.12 okay so now we have to convert it into percentage then here we have to multiply

by 100 okay so if you are multiplying this by 100 we will be getting and perfect amount and now when I run this

oops so now when I run this you can see we are getting a value as 12.04 the same value which we will be saving again we

will be comparing so if you want up to two decimal points you can give here a round uh we can see you can use a round

function over here or decimal functions I will not go into that much okay so you can use a decimal function and you can

show the result up to two decimal points okay okay so if still you want I will give you an example on how to convert it

to up to two decimal points so there are different functions available by which you can use you can use uh a decimal

function you can use a format function or you can use a round function also so we will try it by using round function

so round function takes two argument first is the value which we have to convert it into a rounded format and the

second argument is up to how many digits you wanted okay so first I will write a round function over here so first value

is which value you have to convert so this is the value which we have to convert which we have already taken and

second comma second value is up to how many we can say digits you wanted so let's say you want two digits and I will

close the bracket okay so this is for round function which we have closed the bracket and when I select this and when

I run this you can see we are only getting 12 that is only two digits but we want more two digits after the

decimal point so I will mention here as four and now when I run this you can see we are getting an value as 12.05 so in

this way you have to modify your query with respect to the requirement and which is very much easy actually okay so

in this way we have found it out for interest rate and it is nothing but an average interest rate so overall okay