For deep analysis
Hello my broest, I hope you are doing well. I have very specific, but interesting, but responsible task. I work in a bank, and we provide reports to stakeholders, etc.
Now we are thinking of giving an amazing analysis to them.
The data I have now is as following:
col_names = ['contragent','anketa', 'filial', 'tobo', 'valuta', 'dogovor_sana', 'vidacha_sana', 'department', 'department_number','passport','contragent_name', 'brutto_95_summa', 'brutto_summa', 'protsent_16309_summa', 'protsent_16377_summa', 'protsent_16379_summa', 'spisat_95413_summa', 'spisat_91501_summa', 'reserve_summa', 'vidacha_tekushiy_summa', 'pogashen_tekushiy_summa', 'maks_dni', 'kategoriya_origin', 'kategoriya_npl_90', 'straxovka_summa', 'straxovka_brutto_95_summa','kategoriya_straxovka','sud', 'avto', 'report_date', all_time_given_amount]
here contragent is id of contragent, anketa is id of loan, filial is the name of branch (18 branches), tobo is is the id of minibanks inside branches, valuta is the currencyid (mostly 0, 840, 978), dogovor_sana is the date when agreement is signed (not important or not much reliable), vidacha_sana is when the loan is given to client (reliable), department has three departments, passport is just type of loan (but mostly low-level detailed, has around 30-40 active passports), brutto_95 summa is actual loan amount remained (brutto + 95413 summa), then etc amounts, vidacha_tm summa is the amount given in the report_date, vidacha_alltime_summa is summa given overall, pogashen is the recieved amount for loan payments, maks_dni is the overdue days in number, kategoriya_npl is either 1, 2, 3, 4, 5 or 0 (actually should be just 1), in this 3,4,5 is those who defaulted, 1, 2, 0 is not npl, straxovka_summa is when the client can not pay, then insurance paid amount, straxovka_brutto_95_summa is as if insurance not paid, how much would it have been the total loan amount, kategoriya_straxovka is when an insurance not paid, what would have been the npl category, sud is either norm, official or neofficial, norm is good, official is actually in court, being resolved, neofficial is those which is being prepared to apply for court, avto is either 0, 'vtorichka', 'pervichka', the meaning is if 0 not avto, if vtorichka then the loan given for buying used cars, pervichka for new cars. and finally, very importantly, report date, which is the date column of each month report date, for example for the may, 2026 the report date is 01.06.2026, etc. I have all the correct data all the way from the 01.01.2024 till 01.08.2026. So each report date represents how previous month ended. so I have to use those data, and prepare amazing stuff for the stakeholders, etc. The data is all in one file, I have around 8 mln rows in total.
For now, we have found some templates, but I hope you can do more than that. I will just give for reference, you don't need to follow it, instead use your creativity, problem solving and critical thinking abilities. I use python, django, pandas etc, also I like plotly charts. May be you will give a python script that will analyze those and give in some report format, not sure. Please consider hoe to give, I am still not sure. Maybe in pdf, maybe ppt, maybe python think first.
And also, consider the consistensy, and later use cases, because I wil be adding next month report data to this as well, if possible, the final report should be in russian, but I am against naming column names in russian. Maybe we will change to russian later if you give me now in english, not sure.
Just feel free, improvise, creativity, problem solving, critical thinking, analytical skills, reporting skills, apply whatever you want. Your time has come. Show yourself to the world.
THis is just reference, of course if you have better options, which I belive you have, then just neglect these:
1. Table of contents: based on the followings
2. New Loans/Clients => Number of clients who made a new loan agreement in the quarter, (in mln.sum)
3. New Loans => Loans amount, given on the particular period by month, per department.
4. Loan portfel per month by department
5. Loan portfel dynamics, with portfels that is not npl type but has overdue days between 30-90 at the end of month
6. Number of clients of bank, from them, the number of clients who has not laons
7. Number of loans per active client quarterly
8. Average loan amount per active client quarterly
9. Loans given, and their results per month: Loans given then "GL30+@3mob" means the percentage of loans that have been overdue or past due for more than 30 days, measured exactly at 3 Months On Books (MOB), GL30+@6mob, GL90+@9mob, GL90+@12mob like that., those four types enough, but might be adjusted later.
10. The average Probability of Default (PD)
11: Total Cost of Credit or Full Cost of the Loan.
12: Average months of finishing of loans per month
13: Number of loans per client as the burden, like stacked bar maybe, per month (might be adjusted to yearly or quarterly).
14: Cure rates (on overdue days): like diversifying with entry 0 to 1, not cured 1 to 10, not cured 1 to 30, not cured 1 to 60, not cured 1 to 90, default stuff
15: by dept, passport, branch, etc