Lendingclub Real Historical Data Analysis

I do not trust various online lending analysis tools any more because their results are too different (actually I do not know which one I should believe in). Their magic analysis methods are not public (I am too stupid to guess them out). So today I tried doing some simple analysis with Excel on the data I directly download from lendingclub.com

Analysis Results
I applied my filter to the historical data from 2012-1-1 ~ 2013-6-30 (1.5 years duration).

The total issued loan comply with my filter conditions were 2722. And the bad loans among them were 148 so far. In terms of how I count bad loans, I query the load_status are Charged off, Default or Late 31~120 days.

I learned from lending academy there are some interesting data inside the downloaded loan data which you could not see on lendingclub's webpage and I found them.

- mort_acc: how many mortgages the borrower had when apply loan.
- total_bal_ex_mort: total mortgages balance

I checked those two data average for loans/defaulted under my filter conditions.

Total average mort_acc: 2.27
Total average total_bal_ex_mort: 33800

Default average mort_acc: 2.4
Default average total_bal_ex_mort: 35815

It's not obvious those two fields are useful for loan filtering.

Following is how the loan distribute by grade using my load condition. Looking at that, I should exclude F- from my filter conditions.

Grade	Total	Bad	Bad Rate
C	1886	82	4.3%
D	527	40	7.6%
E	230	13	5.7%
F	75	13	17.3%
G	4	0	0.0%

Next Steps
It's really not convenient to use Excel to do analysis. I will create a database to do data analysis in the next couple of months.

I have not had time to look into how to count loan profit and loss for different status. I should research how it works over time.

There are some data columns inside the downloaded tabular data file I still do not understand what their meaning is. I will try to figure them out over time.