How i played bank
Hi, Habr!
Summer, everyone is on vacation. All costs. Where to get money? At the bank!
Only this July, the largest bank of Russia issued loans to individuals for 73,545,493 thousand rubles. Mortgage loans of the North-West branch account for 53% of the total portfolio, consumer loans - 32%, according to a press release . The average size of a mortgage loan in March 2014 amounted to about 1.5 million rubles . The average size of a car loan is
not less than 0.5 million rubles . Thank you lany for the links provided.
Based on the above figures, it can be assumed that the bank managed to issue a loan of about two hundred thousand people in a month.
Of course, scoring systems are used in this bank.
I learned about scoring systems a couple of years ago and was shocked. The idea that a soulless machine can make a decision about a loan on the basis of statistical data did not go out of my head. I wanted to play bank and check if the computer would give credits to my friends, just for fun. I have never seen real scoring systems, but I decided that it doesn’t matter, I will write it myself.
No sooner said than done.
I attach the link to the scoring system .
The sources have asked me more than a dozen people. And how many are not asked, even difficult to imagine. I spread the source on my dropbox .
Be careful, many images!
Of course, this is only a model in which you can teach economics students of junior courses,
but all my friends now know that differentiated payments are more profitable than annuity payments.
')
The program was written 2 years ago, 2 weeks ago, in C # + MSSQL.
Now I accidentally dug it out and decided to show it to the habrasoobschestvu. What if some small bank will buy?
I opened the code, got scared, added a little multithreading and closed it.
SQL queries right in the handlers are awful, I know. But it is not interesting to finish, already played enough.
The algorithm from the client’s point of view is something like this:
So, first we need a questionnaire, which is cleverly called a “scoring card”.
On the sites of the 3 largest Russian banks, questionnaires for obtaining consumer loans were found. We collect from three one, we print out and we create an expert commission. 15 people were caught, that was enough. An expert evaluates each scorecard score in the range from 0 to 100 points. There are also the following options:
Then the average score for each criterion is found and the final questionnaire is obtained.
This profile is entered into the system.
In general, there are only 2 obligatory questions - “Average monthly incomes” and “Average monthly expenses”. Without them, there is no point in lending at all. Other questions can be changed, deleted, or add your own.
The “Sort” button will determine the maximum and minimum score of the scoring card, as well as determine the type of question.
When passing the questionnaire elements are created automatically. If a question has one answer, it will be displayed as a field, if more than 1 answer, it becomes a drop-down list. This is how the questionnaire looks from a client’s point of view.
And this client indicated the presence of the vehicle, and described it. The loan officer scored this criterion.
Evaluation system questionnaire.
Net monthly income is the difference between income and expenses. To increase the guarantee of payment, the program takes into account the reduction rate of payment of net profit, for example, 0.7. This means that if the client indicates 100 rubles of net profit per month, the bank calculates up to 70 rubles of monthly payment. Net income is multiplied by this ratio. The coefficient can be changed in the settings.
The questionnaire has an upper and lower limit of passage. The lower limit reduces the number of approved loans, but increases customer solvency. The upper limit cuts off fraudsters who create the ideal profiles for the most profitable loan. The upper and lower limits are set in percent, they can be changed in the settings. If the client’s application form, or rather, the amount of points for the application passes through the window between the upper and lower limits, the loan is considered approved. Now the client can choose one of the credit offers, the amount and duration of payments. Payments can be calculated as annuity or differential payments.
The client wants to receive 230,000 rubles with a payment period of 24 months in differentiated payments at 22.5 percent. For 24 months, the client will pay 283,906 rubles.
Here is the payment schedule.
Sweat from the excitement hands pressed the button "Checkout". The first payment of the client is the largest, it amounts to 13,896 rubles. The client’s monthly solvency is set at 14,611 rubles, so the bank agrees to issue a loan.
Hooray! If the loan is approved, an application, contract and payment table are created.
Pay attention to the status - the client is bad. Statuses of all 2 - good and bad. Why is bad, the client just took out a loan? The fact is that the first payment should arrive today.
The system determines the status of the client according to the payment table. For each payment date is indicated. The payments that are currently due must be paid are determined. The client is considered bad if the amount of non-payment:
Further, the security service of the bank, collectors, etc. should enter the business, but they are not provided for in the program.
The "Pay" button will help you change the status of the payment.
Payment paid, transaction is over!
To be honest, the numbers of accounts, contracts, bids and payment transactions are generated randomly, and for the operation of the system are not needed at all. But I am a solid bank!
But the client has become good.
Dirty hack - you can make a good customer a bad one by canceling his payments.
And finally we got to the most interesting - statistics!
The list of questions is formed from the questionnaires of customers who approved the loan.
You can choose any question and watch the ratio of good / bad customers.
You can create your own credit offers. The editor allows you to set the maximum amount and duration of payments, set the interest rate depending on the size of the loan.
And now I want to take 140,000 rubles a year under the "Good Credit".
Overpaying 303 rubles - a dream!
But all the friends got a loan, the statistics are perfect, the market was full. The bank is in stagnation. What to do? NEED MORE CUSTOMERS! Where to get them? Generate!
The following text files were found:
So, the generator for the standard questionnaire:
Although revenues are 2 times higher than expenses, it still sometimes happens that net income is negative.
If a response with a clarification is selected, the application is canceled, and the signature “It is not possible to generate an application with clarification” appears in the status bar.
If the loan is approved, payments are generated. The probability of default is 1/20, but some customers have no luck and they miss 4 payments in a row.
The generator was moved to the main menu, you can create 1 or 200 applications at once.
Also there is a generator during the passage of the questionnaire.
Points generated by customers are subject to normal distribution. The leftmost column is those who are denied credit.
In the questionnaires of course get stupid, but the statistics immediately became more fun!
Conclusion - the worst customer is a single (unmarried) individual entrepreneur, with several higher educations.
Cited the links above. Play on your health!
May need .NET Framework 4.5 and SQL LocalDB 2012 x86 or SQL LocalDB 2012 x64 .
Thanks for your attention and have a nice Friday everyone!
Summer, everyone is on vacation. All costs. Where to get money? At the bank!
Only this July, the largest bank of Russia issued loans to individuals for 73,545,493 thousand rubles. Mortgage loans of the North-West branch account for 53% of the total portfolio, consumer loans - 32%, according to a press release . The average size of a mortgage loan in March 2014 amounted to about 1.5 million rubles . The average size of a car loan is
not less than 0.5 million rubles . Thank you lany for the links provided.
Based on the above figures, it can be assumed that the bank managed to issue a loan of about two hundred thousand people in a month.
Of course, scoring systems are used in this bank.
I learned about scoring systems a couple of years ago and was shocked. The idea that a soulless machine can make a decision about a loan on the basis of statistical data did not go out of my head. I wanted to play bank and check if the computer would give credits to my friends, just for fun. I have never seen real scoring systems, but I decided that it doesn’t matter, I will write it myself.
No sooner said than done.
I attach the link to the scoring system .
The sources have asked me more than a dozen people. And how many are not asked, even difficult to imagine. I spread the source on my dropbox .
Be careful, many images!
Introduction
Of course, this is only a model in which you can teach economics students of junior courses,
but all my friends now know that differentiated payments are more profitable than annuity payments.
')
The program was written 2 years ago, 2 weeks ago, in C # + MSSQL.
Now I accidentally dug it out and decided to show it to the habrasoobschestvu. What if some small bank will buy?
I opened the code, got scared, added a little multithreading and closed it.
SQL queries right in the handlers are awful, I know. But it is not interesting to finish, already played enough.
Development
The algorithm from the client’s point of view is something like this:
- The client fills out a questionnaire with answer options.
- Next, it selects the loan amount, a request is created in the database
- The system counts points for selected answers and determines whether to issue a loan or not.
- If the loan is approved, a contract and a payment table are created in the database.
- The client makes payments.
- When statistics accumulate, you can see which customer criteria affect its solvency.
- ???????
- PROFIT!
So, first we need a questionnaire, which is cleverly called a “scoring card”.
On the sites of the 3 largest Russian banks, questionnaires for obtaining consumer loans were found. We collect from three one, we print out and we create an expert commission. 15 people were caught, that was enough. An expert evaluates each scorecard score in the range from 0 to 100 points. There are also the following options:
- -1 point - the answer is non-standard and is evaluated by a loan officer.
- -10 - denial of credit.
Then the average score for each criterion is found and the final questionnaire is obtained.
| Indicator | Value of the indicator | Score |
| Surname | -one | |
| Name | -one | |
| middle name | -one | |
| Date of Birth | -one | |
| Place of Birth | -one | |
| Floor | -one | |
| Proof of identity | -one | |
| Serial number | -one | |
| Issued by | -one | |
| date of issue | -one | |
| Phone | -one | |
| Address | -one | |
| Age | Less than 20 years | eight |
| 20-25 years | 21 | |
| 25-30 years | 36 | |
| 30-35 years | 53 | |
| 35-50 years old | 60 | |
| 50-60 years | 37 | |
| 60-65 years old | 15 | |
| More than 65 years | -ten | |
| Accommodation | Own housing | 47 |
| Rental of property | 13 | |
| Hostel | eight | |
| Relatives | ten | |
| Military unit | 9 | |
| Other (specify) | -one | |
| Citizenship | RF | 43 |
| Other (I am a resident of the Russian Federation) (specify) | five | |
| I am not a resident of the Russian Federation | -ten | |
| Family status | Single, not married) | 40 |
| Married) | 65 | |
| Married (but married) but separated | 26 | |
| Divorced | 29 | |
| Widower (widow) | 34 | |
| Having children | No children | 66 |
| One | 57 | |
| Two | 48 | |
| Three | 36 | |
| More than three | 24 | |
| Presence of dependents | Not | 66 |
| One | 57 | |
| Two | 48 | |
| Three | 36 | |
| More than three | 24 | |
| The level of education | Below the average | eleven |
| The average | 21 | |
| Specialized secondary | 33 | |
| Incomplete higher | 39 | |
| Higher education | 58 | |
| Some higher | 77 | |
| Academic degree | 84 | |
| Position occupied (position) | Leader (director, deputy chairman, chief boo.) | 94 |
| PI | 92 | |
| Civil servant | 47 | |
| Serviceman | 56 | |
| Specialist | 46 | |
| Sportsman | thirty | |
| Working | 32 | |
| Student (idle) | 9 | |
| Pensioner (idle) | eight | |
| Unemployed | 0 | |
| Number of jobs over the past 3 years | One thing | 52 |
| Two | 38 | |
| Three | 21 | |
| More than three | 9 | |
| Experience at this place of work | Less than 1 year | 7 |
| Up to 3 years | 24 | |
| Up to 5 years | 40 | |
| More than 5 years | 56 | |
| Total work experience | Less than 1 year | ten |
| Up to 3 years | 20 | |
| Up to 10 years | 37 | |
| More than 10 years | 62 | |
| Type of activity of the organization | Finance, banks, insurance | 37 |
| Consulting services | 32 | |
| Building | 39 | |
| Authorities and management | 42 | |
| Industry and engineering | 37 | |
| Information Technology / Telecommunications | 38 | |
| Wholesale and retail trade | 35 | |
| Transport | thirty | |
| Tourism | 36 | |
| Security activities | 34 | |
| Education | 32 | |
| The medicine | thirty | |
| The science | 33 | |
| Military establishment | 31 | |
| Social sphere | 21 | |
| Other industries (specify) | -one | |
| Average monthly income | -one | |
| Average monthly expenses | -one | |
| Real estate property | Garage | 38 |
| Apartment | 58 | |
| House | 65 | |
| Country house | 51 | |
| Land plot | 63 | |
| Other (specify) | -one | |
| Vehicle availability | Name, brand, year of release (specify) | -one |
| No vehicle | 0 |
This profile is entered into the system.
In general, there are only 2 obligatory questions - “Average monthly incomes” and “Average monthly expenses”. Without them, there is no point in lending at all. Other questions can be changed, deleted, or add your own.
The “Sort” button will determine the maximum and minimum score of the scoring card, as well as determine the type of question.
When passing the questionnaire elements are created automatically. If a question has one answer, it will be displayed as a field, if more than 1 answer, it becomes a drop-down list. This is how the questionnaire looks from a client’s point of view.
And this client indicated the presence of the vehicle, and described it. The loan officer scored this criterion.
Evaluation system questionnaire.
Net monthly income is the difference between income and expenses. To increase the guarantee of payment, the program takes into account the reduction rate of payment of net profit, for example, 0.7. This means that if the client indicates 100 rubles of net profit per month, the bank calculates up to 70 rubles of monthly payment. Net income is multiplied by this ratio. The coefficient can be changed in the settings.
The questionnaire has an upper and lower limit of passage. The lower limit reduces the number of approved loans, but increases customer solvency. The upper limit cuts off fraudsters who create the ideal profiles for the most profitable loan. The upper and lower limits are set in percent, they can be changed in the settings. If the client’s application form, or rather, the amount of points for the application passes through the window between the upper and lower limits, the loan is considered approved. Now the client can choose one of the credit offers, the amount and duration of payments. Payments can be calculated as annuity or differential payments.
The client wants to receive 230,000 rubles with a payment period of 24 months in differentiated payments at 22.5 percent. For 24 months, the client will pay 283,906 rubles.
Here is the payment schedule.
Sweat from the excitement hands pressed the button "Checkout". The first payment of the client is the largest, it amounts to 13,896 rubles. The client’s monthly solvency is set at 14,611 rubles, so the bank agrees to issue a loan.
Hooray! If the loan is approved, an application, contract and payment table are created.
Pay attention to the status - the client is bad. Statuses of all 2 - good and bad. Why is bad, the client just took out a loan? The fact is that the first payment should arrive today.
The system determines the status of the client according to the payment table. For each payment date is indicated. The payments that are currently due must be paid are determined. The client is considered bad if the amount of non-payment:
- ≥ 1/3 of all payments under the contract, or
- ≥ 4 (set in settings).
Further, the security service of the bank, collectors, etc. should enter the business, but they are not provided for in the program.
The "Pay" button will help you change the status of the payment.
Payment paid, transaction is over!
To be honest, the numbers of accounts, contracts, bids and payment transactions are generated randomly, and for the operation of the system are not needed at all. But I am a solid bank!
But the client has become good.
Dirty hack - you can make a good customer a bad one by canceling his payments.
Statistics
And finally we got to the most interesting - statistics!
The list of questions is formed from the questionnaires of customers who approved the loan.
You can choose any question and watch the ratio of good / bad customers.
Loan offer "Good loan"
You can create your own credit offers. The editor allows you to set the maximum amount and duration of payments, set the interest rate depending on the size of the loan.
And now I want to take 140,000 rubles a year under the "Good Credit".
Overpaying 303 rubles - a dream!
But all the friends got a loan, the statistics are perfect, the market was full. The bank is in stagnation. What to do? NEED MORE CUSTOMERS! Where to get them? Generate!
Customer generation
The following text files were found:
- list of settlements of the Russian Federation
- list of male names
- list of female names
- list of names
So, the generator for the standard questionnaire:
| Question | Answer |
| Floor | By chance |
| Name | From the list |
| Surname | From the list |
| middle name | Name from the list plus “ovich” or “ram” |
| Place of Birth | From the list |
| Date of Birth | From 1940 to 1997 |
| Proof of identity | Passport |
| Passport ID | 0000 000000 to 9999 999999 |
| date of issue | Any moment since the client turned 14 |
| Place of issue | From the list |
| Average monthly income | From 4 thousand to 60 thousand rubles. |
| Average monthly expenses | From 2 thousand to 30 thousand rubles. |
| Other questions | By chance |
| Loan offer | By chance |
| Credit amount | From 1 thousand rubles. up to the maximum amount of the loan offer |
| Number of months of payments | From minimum to maximum for a given loan offer |
Although revenues are 2 times higher than expenses, it still sometimes happens that net income is negative.
If a response with a clarification is selected, the application is canceled, and the signature “It is not possible to generate an application with clarification” appears in the status bar.
If the loan is approved, payments are generated. The probability of default is 1/20, but some customers have no luck and they miss 4 payments in a row.
The generator was moved to the main menu, you can create 1 or 200 applications at once.
Also there is a generator during the passage of the questionnaire.
Points generated by customers are subject to normal distribution. The leftmost column is those who are denied credit.
In the questionnaires of course get stupid, but the statistics immediately became more fun!
Conclusion - the worst customer is a single (unmarried) individual entrepreneur, with several higher educations.
Cited the links above. Play on your health!
May need .NET Framework 4.5 and SQL LocalDB 2012 x86 or SQL LocalDB 2012 x64 .
Thanks for your attention and have a nice Friday everyone!
Source: https://habr.com/ru/post/232475/
All Articles