Author Topic: April 16 has arrived  (Read 3205 times)

Rob L

  • Hero Member
  • *****
  • Posts: 2057
    • View Profile
April 16 has arrived
« on: April 16, 2014, 08:12:00 AM »
Per the post LC put on their blog,  today's the day the contents of your account's notes.csv file (obtained using the download all button at the bottom of your LC website Notes page or by https://www.lendingclub.com/account/notesRawData.action) is to be expanded.
This is the file you upload to 3rd party sites for analysis of your portfolio so it will no doubt impact them.

Daily loan updates of the all loans since 2012 https://www.lendingclub.com/fileDownload.action?file=LoanStats3b_securev1.csv.zip&type=gen are also to stop today and be replaced by quarterly updates. This also impacts analysis of historic loan performance by 3rd party sites.

No change to notes.csv as yet tpday (as of 8:00am EDT).

Speculation was that these changes are pre-IPO driven to limit loan issuance data available to stock investors and traders. Since one can still easily monitor browsenotes (fractional and whole loans) and this is very tightly correlated with loan issuance I'm not sure the change accomplishes much in that regard.

Rob L

  • Hero Member
  • *****
  • Posts: 2057
    • View Profile
Re: April 16 has arrived
« Reply #1 on: April 16, 2014, 04:52:58 PM »
The old version is still available at https://www.lendingclub.com/account/notesRawData.action

The new expanded version is now available as promised at https://www.lendingclub.com/account/notesRawDataExtended.action
There is a new link to the expanded version at the bottom of the notes page.

hrseymour

  • Newbie
  • *
  • Posts: 13
    • View Profile
Re: April 16 has arrived
« Reply #2 on: April 16, 2014, 10:46:06 PM »
I have informed LendingClub that the notesRawDataExtended.action as it is currently implemented has a bug and an issue. 

The bug is that the "desc" field is not escaped (with double quotes) when it contains a comma.  This throws off Excel or any other program opening the downloaded file as a CSV.

The issue is that notesRawDataExtended.action is missing these fields which would be nice to have:

member_id
loan_amnt
funded_amnt
funded_amnt_inv
grade
sub_grade

Rob L

  • Hero Member
  • *****
  • Posts: 2057
    • View Profile
Re: April 16 has arrived
« Reply #3 on: April 17, 2014, 11:14:15 AM »
I'll confirm the "commas in descr" bug is certainly there. Same result with LibreOffice Calc spreadsheet program.

The first 20 columns (A-T) of the extended file are the same as the old version.

Unfortunately Member ID wasn't in the old version and hasn't been added.
The good news is that the first new field provided is installment. From installment,  interest rate and term you should be able to work backwards and compute the loan amount.
Sub Grade is provided in the field LoanClass.name, so you have Grade and Sub Grade there.

I wish they had added projected_default_rate from browseNotes to be able to measure the quality of LC's predictions in the future.

Guess you have to save a copy of browseNotes containing the loans as you buy them to have everything.
Trouble with that is the loan may be gone after you buy it and before you download browseNotes depending on your purchase method. Bummer.


kbenson99

  • Full Member
  • ***
  • Posts: 111
    • View Profile
    • Email
Re: April 16 has arrived
« Reply #4 on: April 17, 2014, 12:32:46 PM »
It's not only in the description field, it will occur in any free form field (such as emp_title).  I have a loan https://www.lendingclub.com/browse/loanDetail.action?loan_id=795869 where the emp_title contains a comma (LaVan & Neidenberg, P.A.).

The proper thing that LC should have been done is to escape each field with double quotes, as it does in their history files.  Spreadsheets will not handle correctly until LC fixes.  For us programmers, to handle this "malformed" csv, the change is relatively trivial (I added 4 lines of code to detect and cleanup). 

hrseymour

  • Newbie
  • *
  • Posts: 13
    • View Profile
Re: April 16 has arrived
« Reply #5 on: April 17, 2014, 06:48:08 PM »
@Rob L
I had overlooked LoanClass.name.  Thanks for pointing that out.  Also, you are right that loan_amnt can be calculated from the other fields.  Here is the (C#) code I am using for this:

double loan_amnt = 5.0 * Math.Round(Financial.Pv(int_rate / 12.0, term, -installment, 0.0, PaymentDue.EndOfPeriod) / 5.0);


Rob L

  • Hero Member
  • *****
  • Posts: 2057
    • View Profile
Re: April 16 has arrived
« Reply #6 on: April 17, 2014, 10:04:54 PM »
It appears LC has fixed the commas not delimited in quotes bug.
Interesting; they did not put quotes around every string in every loan that might possibly have an embedded comma; only the specific strings in the specific fields in the specific loans that do.
However, portfolio names are always enclosed in quotes; commas or not. Legacy code I suppose.

On another note, the sort feature (click on column name) on the My Notes page of the LC web site now appears to be broken.
The columns "investment", "Outstanding Principal", "Accrued Interest", and "Payments Received" do not sort correctly. The other columns appear to be okay.
Anybody out there in LC Land reading this?


brycemason

  • Hero Member
  • *****
  • Posts: 801
    • View Profile
    • P2P-Picks.com
    • Email
April 16 has arrived
« Reply #7 on: April 18, 2014, 04:22:36 PM »
Here is some sed code to fix commas in quotes. I had to deal with this in loan title once for browseNotes.

sed 's/"\(\([^,"]*\)"\)\{1,\}\([^,"]*\)"/"\2\3"/g'