Skip to main content

Author Topic: LC loan number comparison

h
  • Posts: 54
LC loan number comparison
OP: July 02, 2014, 11:00:00 PM
I have recently downloaded all 3 of LC's historical loan files, merged them all to get a loan count of 275066 thru 3/31/2014. When I backfilter LC loans on NS and cut off loans after 3/31/2014 I get a loan count of 277814. I know the difference is small and unlikely to affect my statistics significantly but I'm nervous when I can't even get a stat this simple to agree with what I see in what I thought was the same original data set?
Are there LC loans that have been purged for some reason that NS captured in their regular downloads and has preserved?
I'm confused.
  • IP logged

  • *****
  • Hero Member
  • Posts: 602
LC loan number comparison
#1: July 02, 2014, 11:00:00 PM
My database, built from the 3 file download from LC, contains 277814 loans.
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#2: July 02, 2014, 11:00:00 PM
Thanks, maybe I screwed up the merge or had some other kind of problem with excel. I'll double check.
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#3: July 02, 2014, 11:00:00 PM
OK Fred,

Help me some more if you can. When you just append all the files you get 296879 loans. If you sort by status you find 19065 with no status issued from 7/12/2013 all the way through 3/31/2014. Removing these gives you the correct number of loans 277814.

Can you tell me what these loans are or were?

Anyway I had removed these. My missing loans arose because I also removed loans whose status begins "Does not meet the credit policy. Status:" followed by current, or paid in full, etc because I didn't know how to interpret this status. That is where my missing 2748 loans went.

These are not quiet period loans. Can you tell me how this "does not meet ..." status arises and what it means?
  • IP logged


T
  • Posts: 6588
LC loan number comparison
#5: July 03, 2014, 11:00:00 PM
Thanks so much. You my hero!
  • IP logged

R
  • Posts: 484
LC loan number comparison
#6: July 22, 2014, 11:00:00 PM
I have some minor discrepancies with NSR and my numbers regarding sub-grade. Grade numbers are all the same.
NSR has the following differences;  A1 +2 loans,  A5 -1 loan, B2 -1 loan, B5 +1 loan, C2 -2 loans and finally C2 +1 loan.
Total the differences and it's 0 so the total loan count agrees.

If you go to NSR back testing analytics and filter on A1 sub-grade only, the results show all the A1's and also one B grade and one C grade.
Clearly something is amiss.
  • IP logged

r
  • Posts: 10

T
  • Posts: 6588
LC loan number comparison
#8: July 22, 2014, 11:00:00 PM
Interesting. Mystery solved. We are using the same data source, however...
I only use sub-grade and never look at the grade column. Always figured an X3 loan was grade X.
Grade and sub-grade contain redundant information, not a good thing to have in a database.
Somehow it always gets out of sync.
BTW, thanks for your fantastic web site. Very nice piece of work!
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#9: July 22, 2014, 11:00:00 PM
LC must have cleaned up the LoanStats3b_securev1.csv file since you downloaded it.
In my copy loan 6300518 is Grade B and Sub_grade B2 and loan 5628625 is Grade C and Sub_grade C4.
I scanned the entire data base (LoanStats3a_securev1.csv, LoanStats3b_securev1.csv and LoanStats3c_securev1.csv) and found no inconsistencies between Grade and Sub_grade.
Nothing out of sync.
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#10: July 23, 2014, 11:00:00 PM
Something weird is going on.  I downloaded the file again at noon today and it is still showing the inconsistencies for me.
  • IP logged

b
  • Posts: 43
LC loan number comparison
#11: July 23, 2014, 11:00:00 PM
I just downloaded the files on 7/24/2014 at 8:10pm Pacific time. I concur with rocco.g. The inconsistencies are still there.

In file LoanStats3b_securev1.csv on row 77685, id = 6300518 has grade = B and sub_grade = A1.
In the same file on row 97444, id = 5628625  has grade = C and sub_grade = A1.
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#12: July 24, 2014, 11:00:00 PM
My existing database must be older:
From my existing database:

"5628625","7011021","20000","20000","20000"," 60 months"," 16.29%","489.45","C","C4","Froedtert Memorial Lutheran Hospital ",
"6300518","7831802","24000","24000","23950"," 36 months"," 10.64%","781.65","B","B2","Quest Diagnostics"

From a newly loaded database:

"5628625","7011021","20000","20000","20000"," 60 months"," 16.29%","489.45","C","A1","Froedtert Memorial Lutheran Hospital ",
"6300518","7831802","24000","24000","23950"," 36 months"," 10.64%","781.65","B","A1","Quest Diagnostics"

I'm not making this up. How could it have happened?
I can understand the database being fixed in later releases, but it was corrupted in the later release.
As you said; weird!
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#13: July 24, 2014, 11:00:00 PM
A scan of the newly downloaded database reveals 6 Grade / Sub_grade mismatch errors:

"8957001","10749045","15000","15000","15000"," 36 months"," 15.61%","524.48","C","A5","Senior Marketing Manager"
"8494880","10247000","15000","15000","15000"," 36 months","  8.90%","476.3","A","B4","Paralegal"
"6300518","7831802","24000","24000","23950"," 36 months"," 10.64%","781.65","B","A1","Quest Diagnostics"
"5977589","7450018","16000","16000","16000"," 36 months"," 12.35%","534.11","B","C2","Nationwide"
"5628625","7011021","20000","20000","20000"," 60 months"," 16.29%","489.45","C","A1","Froedtert Memorial Lutheran Hospital "
"2895818","3528124","6000","6000","6000"," 36 months"," 14.33%","206.03","C","B5","Active Motif"

Must be the original 6 count differences I posted a couple of days ago.
Doesn't inspire a lot of confidence, does it.
Could be plenty of other diffs that don't show up;  example  is B  B1    should be     B  B4
Have to cross check with the interest rate (at that date) to be sure the Grade and Sub_grade are correct.
  • IP logged

T
  • Posts: 6588
LC loan number comparison
#14: July 24, 2014, 11:00:00 PM
Are you reporting these finds to LC?
  • IP logged