Author Topic: LendingClub account-activity Payment & Fee Details spreadsheet  (Read 896 times)

lascott

  • Hero Member
  • *****
  • Posts: 1423
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
LendingClub account-activity Payment & Fee Details spreadsheet
« on: February 02, 2019, 12:27:50 AM »
Below prompted me to share a spreadsheet (Do a File/Copy of it!) and technique I came up with a while back to look at note payment activity. 
I used the length of all concatenated IDs to determine a small formula to extract out the Loan ID,  Order ID, Note ID. This seems to work for the various lenghts (24 to 27).
I was prompted to share this based on my interaction with Fred93 below.

All the payments for a day show up as a single total, and when you click on the total, a window pops up showing the note by note detail.  For me, that popup window is larger than my screen, and it has NO SCROLL BAR, and no way to download, so there's no way to ever see the numbers.
FYI, I can use the mouse scroll wheel to move down but I can not use the page scroll bar or that popup dialog box goes away. Crazy they design this stuff with one person or something.
UPDATE: You can also just click your mouse in that dialog box and then use up/down arrow keys or page up/down keys.
Quote
Visual separator

Here are some directions to populating a Google spreadsheet I came up with to peek at note payments and then take the hyperlink to the Lending Club page for a given note. AFAIK you can only look at these if you own the note so experiment (follow LC link in spreadsheet) with your own.

Steps.
1) Got to Activity page: https://www.lendingclub.com/investor/account/1/activity/account-activity
2) Click on Investor Fee Amount or PAYMENT Amount to get a popup dialogue.  Either one brings up the same info AFAIK. Often the PAYMENT one shows up many minutes after the Investor Fees one.
3) Highlight all the IDs and values
4a) Paste into Windows notepad or equivalent
4b) Change all $ to <tab>$ --- this will make pasting in a spreadsheet go to multiple columns (tabbed data pasted)
5) You can delete range 3A:G beforehand.  (3A:G without a number following G means as many rows as there is per G-sheet syntax).
6)  Then cut-n-paste those lines into a spreadsheet at 3A. 
7a) Highlight all cells. Click the area to the left of A and above 1. Not sure of the official name of this 'select all cells' area.
7b) Use menu option Data, then Sort Range.  Pick Data has header row. 
7c) Sort by column named 'COLLECTION FEE (S1Z)' 1st and select Z to A (S1Z) *AND*
7d) Sort by column named 'PRINCIPAL (S2Z)' 2nd and select Z to A (S2Z).
8.) Click on the URL for any note you are interested in reviewing (but make sure you are logged into your LC account).

Shared sheet: https://docs.google.com/spreadsheets/d/1j29i8N7T454BFOkS9IaZl7L43Qsg2qOC8km76tEH-gQ/edit?usp=sharing
Shared Excel: see attachment

Example below. This is just for the heck of it so see what some transactions are doing (pay off, pay late, collections, etc). 

I built a URL in the Google sheet that I can then go look at that loan. 

Obviously, I added the columns on the right (following '.' column) to break the IDs up so I could build the URL. Cut-n-pasting scrunched them all together :(  Automatic 'Conditional Formatting' also made numbers stand out.

Related to 2 above:


Final output: (update -- Note that N5 should be a value of 8. I added a forumula to that column but it didn't affect anything else. 1:03am CTS)
« Last Edit: February 06, 2019, 09:26:28 PM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

lascott

  • Hero Member
  • *****
  • Posts: 1423
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: LendingClub account-activity Payment & Fee Details spreadsheet
« Reply #1 on: February 05, 2019, 01:41:40 PM »
Has anyone tried the steps and spreadsheet?  Did it work as expected? When I parsed the concatenated IDs I think my formulas covered all the possible lengths.
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

arcee49

  • Newbie
  • *
  • Posts: 30
    • View Profile
Re: LendingClub account-activity Payment & Fee Details spreadsheet
« Reply #2 on: February 05, 2019, 11:15:29 PM »
I have not but thank you for taking the time to do this.  A few months (or more) ago when LC made that horrific visual change and removed some Download button, they also screwed up the Account Activity page.  I used to be able to simply copy and paste that popup-box into Excel and add up all Principal, Interest, Fees, etc. for every day.  I emailed them about the changes and they basically said they didn't care that copy and paste no longer worked.

lascott

  • Hero Member
  • *****
  • Posts: 1423
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: LendingClub account-activity Payment & Fee Details spreadsheet
« Reply #3 on: February 06, 2019, 07:49:28 PM »
I have not but thank you for taking the time to do this.  A few months (or more) ago when LC made that horrific visual change and removed some Download button, they also screwed up the Account Activity page.  I used to be able to simply copy and paste that popup-box into Excel and add up all Principal, Interest, Fees, etc. for every day.  I emailed them about the changes and they basically said they didn't care that copy and paste no longer worked.
You're welcome.  The above would end up being very similar except for the step of doing the change all $ to <tab>$.

I think the shared Google sheet could also be downloaded to Excel easily enough. UPDATE: Added Excel spreadsheet to the original post.
« Last Edit: February 06, 2019, 09:26:57 PM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y