Author Topic: Additional Statistics Payment File Down  (Read 1778 times)

cramsey

  • Newbie
  • *
  • Posts: 1
    • View Profile
Additional Statistics Payment File Down
« on: December 31, 2018, 03:29:17 PM »
Curious if anyone has noticed or also run in to the payment history files at additionalstatistics.lendingclub.com being down since the December 2018 update. I contacted LC mid month to ask about it and was told there is a technical issue with the csv that they are working on.

A technical issue with a csv is pretty laughable, so I'm wondering if anyone has theories or insight on what might actually be happening with that data publication?

My theories if it is in fact down for everyone:

1. Something silly actually broke, but they don't care and won't allocate 30 minutes to fix it
2. They decided publishing it offers no value to them, so they silently pulled it to decongest the network from the 1 GB downloads, note the small files on that page still work.
3. They discovered a data quality/integrity/accuracy issue in what they were recording/publishing and silently pulled it while they try to figure it out and hope no one notices under the guise of a technical issue.
4. They wanted to kiss up to the institutional clients and pulled the file from retail but supply the data on the side to the big players to give them a leg up on the primary/secondary markets over the small guys
5. Laplanche snuck in the back door and packaged up every LC owned loan with payment history and sold them to his cousin in exchange for a sailboat.

I would really love to see it back in place soon. I had just started to really refine a data model on payments that was killing it... now that data is nearly 2 months stale.

liquidp2p_dev

  • Newbie
  • *
  • Posts: 8
    • View Profile
Re: Additional Statistics Payment File Down
« Reply #1 on: January 01, 2019, 10:07:31 PM »
It's down for everyone. Someone actually posted about it on the forums earlier but that post seems to have disappeared.

I have a couple of thoughts:

1. I have noticed encoding issues with these CSV files in the past. Given the size of the files and the fact that a misplaced character or two can really mess up a CSV, a technical issue isn't out of the question.
2. I have also noticed data quality/integrity issues when working with these files so this seems like a very plausible theory as well.

Whatever the issue is, I can't imagine that fixing it would take very long if they were making it a priority.

Fred93

  • Hero Member
  • *****
  • Posts: 2142
    • View Profile
Re: Additional Statistics Payment File Down
« Reply #2 on: January 07, 2019, 03:15:37 PM »
Looks like it is fixed now.

mark78

  • Newbie
  • *
  • Posts: 29
    • View Profile
    • Email
Re: Additional Statistics Payment File Down
« Reply #3 on: March 26, 2019, 01:48:45 AM »
I've just started using the additional statistics in my automated buying, and the first such note I bought was an epic fail.

I databased the additional statistics "payments made to investors" file. For Loan ID 68447465, the additional statistics file said it was late just once, many months ago. So when a note for that loan came up for sale at a good discount, my app bought it.

After my app bought the note, I manually inspected the payment history at https://www.lendingclub.com/foliofn/loanPerf.action?loan_id=68447465&order_id=96430784&note_id=109387362. This shows a completely different story- the loan has been late not once but three times, plus many IGPs that were not in the additional statistics.

The data integrity looks ok, nothing funny with encoding, etc. Has anyone verified the additional statistics matches the payment history on the Folio site?

« Last Edit: March 26, 2019, 01:55:44 AM by mark78 »

AnilG

  • Hero Member
  • *****
  • Posts: 1089
    • View Profile
    • PeerCube
Re: Additional Statistics Payment File Down
« Reply #4 on: March 26, 2019, 02:17:19 AM »
You are misunderstanding the data included in payment history file. Payment history file doesn't contain all payment transactions that occurr with each loan. It only contains the aggregate monthly payment received/not received, status and amounts at the end of month for each loan. There could be multiple transactions in a month for a loan, but the payment file will aggregate them and present them as that month's data. Similarly, if loan status changes during the month and then reverts again within same month, payment file will not show that change.

If you look at payment history for your loan carefully, you will notice that only Once loan status carried over from one month to next, so it showed up in the monthly payment file. The other late status were settled within the same month so they never showed up as loan status at the end of the month.

I've just started using the additional statistics in my automated buying, and the first such note I bought was an epic fail.

I databased the additional statistics "payments made to investors" file. For Loan ID 68447465, the additional statistics file said it was late just once, many months ago. So when a note for that loan came up for sale at a good discount, my app bought it.

After my app bought the note, I manually inspected the payment history at https://www.lendingclub.com/foliofn/loanPerf.action?loan_id=68447465&order_id=96430784&note_id=109387362. This shows a completely different story- the loan has been late not once but three times, plus many IGPs that were not in the additional statistics.

The data integrity looks ok, nothing funny with encoding, etc. Has anyone verified the additional statistics matches the payment history on the Folio site?
« Last Edit: March 26, 2019, 02:20:06 AM by AnilG »
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com

Fred93

  • Hero Member
  • *****
  • Posts: 2142
    • View Profile
Re: Additional Statistics Payment File Down
« Reply #5 on: March 26, 2019, 05:20:08 AM »
I databased the additional statistics "payments made to investors" file.

...the loan has been late not once but three times, plus many IGPs that were not in the additional statistics.

There are no "IGP" in the payments file for any loan.  The monthly nature of the records in that file have no way to record IGP, which by its very nature doesn't last a month.

mark78

  • Newbie
  • *
  • Posts: 29
    • View Profile
    • Email
Re: Additional Statistics Payment File Down
« Reply #6 on: March 26, 2019, 06:07:04 AM »
Thanks guys, that makes sense. So the payments history file may be useful for modeling, but not for my use case. If a note comes up for sale which is not never-late (i.e. it has been late), and I want to know the late payment history, including IGPs and late-15-days, then the only way I can see is to screen-scrape https://www.lendingclub.com/foliofn/loanPerf.action?loan_id=X&order_id=Y&note_id=Z, which I believe is against the terms and conditions.

Fred93

  • Hero Member
  • *****
  • Posts: 2142
    • View Profile
Re: Additional Statistics Payment File Down
« Reply #7 on: March 27, 2019, 09:36:58 AM »
Yea, and this is frustrating.  Not only is it against the T&C, but there isn't enough time to do it when you are presented with a loan for sale.  The ones I want to buy go very quickly.

I thought about slowly scraping, over a few weeks, to build a database of this info for all loans, so I'd have it in advance of them being listed for sale, but I'm sure this is precisely the sort of thing that the T&C was written to prevent.

I wanted to use the IGP info for another purpose.  I wanted to calculate the rate at which IGP state propagates to worse kinds of late.  I was thinking of my sell algorithm at the time.  (I've since given up on sell algorithms.  Now I never sell.)

I am unable to do that calculation, because the payment history file doesn't contain IGP, and there's no other way for me to get this info for all loans.  The LC web pages have some numbers on this roll rate, but they only show the number for all loans, and I feel sure that loans broken down by various parameters (grade for example) will experience different roll rates.

AnilG

  • Hero Member
  • *****
  • Posts: 1089
    • View Profile
    • PeerCube
Re: Additional Statistics Payment File Down
« Reply #8 on: March 28, 2019, 04:02:21 AM »
There are IGP entries in payment file for some loans. The key here is that IGP status has to last from end of one month to the start of next month for IGP to show up in payment file. I just checked the March 2019 payment file, there are 12,727 payment entries from 12,685 distinct loans with IGP as end of period loan status.

For roll rate, you can always back up payment month on Late (16-30 days) payment entries by 16+ days (aka previous month) to come up with approximate IGP month. Every late loan has to go through IGP status first. Only data you miss out on is the loans that seesaw between Current and IGP and never go Late.

IIRC, LC has an API for payment received and withdrawals/deposits at user account level that is used by likes of Personal Capital but LC decided we minions are not worthy of it.

Code: [Select]
     PERIOD_END_LSTAT        N         pct
1:            Current 41576006 93.94122777
2:         Fully Paid  1066057  2.40876200
3:  Late (16-30 days)   332150  0.75049486
4: Late (31-120 days)   932931  2.10796303
5:        Charged Off   262391  0.59287399
6:            Default    49703  0.11230422
7:    In Grace Period    12727  0.02875673
8:             Issued    25500  0.05761740

There are no "IGP" in the payments file for any loan.  The monthly nature of the records in that file have no way to record IGP, which by its very nature doesn't last a month.

Yea, and this is frustrating.  Not only is it against the T&C, but there isn't enough time to do it when you are presented with a loan for sale.  The ones I want to buy go very quickly.

I thought about slowly scraping, over a few weeks, to build a database of this info for all loans, so I'd have it in advance of them being listed for sale, but I'm sure this is precisely the sort of thing that the T&C was written to prevent.

I wanted to use the IGP info for another purpose.  I wanted to calculate the rate at which IGP state propagates to worse kinds of late.  I was thinking of my sell algorithm at the time.  (I've since given up on sell algorithms.  Now I never sell.)

I am unable to do that calculation, because the payment history file doesn't contain IGP, and there's no other way for me to get this info for all loans.  The LC web pages have some numbers on this roll rate, but they only show the number for all loans, and I feel sure that loans broken down by various parameters (grade for example) will experience different roll rates.
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com

mark78

  • Newbie
  • *
  • Posts: 29
    • View Profile
    • Email
Re: Additional Statistics Payment File Down
« Reply #9 on: March 28, 2019, 04:12:08 AM »
Now that I'm back to civilization, meaning a real internet connection, I looked at the other big file, and it seems to have the level of detail I want. At https://www.lendingclub.com/company/additional-statistics, there are two big files. The first one- "Payments made to investors"- is just a monthly aggregate like you guys said. But the second- "All payments (includes payments made to investors and to LendingClub)"- has each payment, along with status including IGPs and sometimes multiple payments in a month.

Agreed there's no time to scrape in 10 milliseconds, but doesn't this file do the job? I haven't databased it yet, just eyeballing it in Excel. My only complaint is the dates are rounded to the month (as Fred93 already said in another thread).

Fred93

  • Hero Member
  • *****
  • Posts: 2142
    • View Profile
Re: Additional Statistics Payment File Down
« Reply #10 on: March 28, 2019, 08:35:42 AM »
For roll rate, you can always back up payment month on Late (16-30 days) payment entries by 16+ days (aka previous month) to come up with approximate IGP month. Every late loan has to go through IGP status first. Only data you miss out on is the loans that seesaw between Current and IGP and never go Late.

That's precisely what I was after ... wanting to understand of those loans that go IGP, what fraction return to current vs go on to worse status.

AnilG

  • Hero Member
  • *****
  • Posts: 1089
    • View Profile
    • PeerCube
Re: Additional Statistics Payment File Down
« Reply #11 on: March 30, 2019, 08:12:15 AM »
Be careful with your assessment of second file. I have always used the second file for analysis and never even looked at the first one. All my comments were based on my experience with the second file. As an example, as I mentioned in one of the other comments that March 2019 payment file (second file) had only 12,685 loans and 12,727 payment entries with IGP status which is pretty low number in a population of 2M+ loans issued and ~40M+ payment entries.

Now that I'm back to civilization, meaning a real internet connection, I looked at the other big file, and it seems to have the level of detail I want. At https://www.lendingclub.com/company/additional-statistics, there are two big files. The first one- "Payments made to investors"- is just a monthly aggregate like you guys said. But the second- "All payments (includes payments made to investors and to LendingClub)"- has each payment, along with status including IGPs and sometimes multiple payments in a month.

Agreed there's no time to scrape in 10 milliseconds, but doesn't this file do the job? I haven't databased it yet, just eyeballing it in Excel. My only complaint is the dates are rounded to the month (as Fred93 already said in another thread).
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com