Author Topic: Automatically downloading notes.csv file: anyone know how to  (Read 16725 times)

Kombinator

  • Full Member
  • ***
  • Posts: 193
    • View Profile
Automatically downloading notes.csv file: anyone know how to
« on: August 07, 2013, 04:34:55 PM »
I am looking to have the file programmatically downloaded into Excel, does anyone have any experience doing it, and if so can you please share whether it can be done, as well as possibly ideas / specific code that enables this download.

Many thanks in advance.

viking

  • Sr. Member
  • ****
  • Posts: 370
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #1 on: August 07, 2013, 05:13:03 PM »
You can use a free Macro add-on such as iMacros (for FF, IE & Chrome) and create a simple Macro to "automatically" download the file. The CSV file can then be opened in Excel.

Kombinator

  • Full Member
  • ***
  • Posts: 193
    • View Profile
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #2 on: August 08, 2013, 03:11:35 PM »
Very interesting, can you say a bit more about this method please...?

viking

  • Sr. Member
  • ****
  • Posts: 370
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #3 on: August 08, 2013, 03:28:00 PM »
Very interesting, can you say a bit more about this method please...?
1. Install the add-on
2. Start the recorder
3. Do the manual steps to download the file
4. Stop the recorder.
When you want to download again, play the script and the file will be downloaded. You will need to open it manually in Excel.

loan_trader

  • Newbie
  • *
  • Posts: 19
    • View Profile
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #4 on: March 11, 2014, 02:33:33 PM »

My question is along the same lines as this thread.
I have figured out how to automatically download the file to excel so I can run my own queries on it.

However, my problem is that I don't know how to detect that a new set of notes has come out so I can download the most updated file.  Normally I'll refresh the page until I see a new one is out then manually run my scripts...but I'd like to have this automatically download only when new notes are issued (it seems like they are at random times up to 2 or 3 minutes past the hours they are suppose to come out). Anyway to do this without the API?

also, is there a glossary for all the additional fields that are in the file?

thanks,
dan

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #5 on: March 11, 2014, 02:41:00 PM »
also, is there a glossary for all the additional fields that are in the file?

Perhaps this is what you are looking for:
https://www.lendingclub.com/fileDownload.action?file=LCDataDictionary.xlsx&type=gen

Keltset

  • Full Member
  • ***
  • Posts: 176
    • View Profile
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #6 on: March 11, 2014, 03:03:01 PM »

However, my problem is that I don't know how to detect that a new set of notes has come out so I can download the most updated file.  Normally I'll refresh the page until I see a new one is out then manually run my scripts...but I'd like to have this automatically download only when new notes are issued (it seems like they are at random times up to 2 or 3 minutes past the hours they are suppose to come out). Anyway to do this without the API?

You can hit the json itself.....

https://www.lendingclub.com/browse/browseNotesAj.action?method=getResultsInitial&startindex=0&pagesize=1&r=14646

Field 'totalRecords' is the field that returns the current number of available loans on the platform.

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #7 on: March 13, 2014, 11:11:30 PM »
You can hit the json itself.....

https://www.lendingclub.com/browse/browseNotesAj.action?method=getResultsInitial&startindex=0&pagesize=1&r=14646

Field 'totalRecords' is the field that returns the current number of available loans on the platform.

Thank you very much for the link. It is much convenient and fast than what I am using. I currently use code to ping browse notes page and get the total # of loans from screen.

For getting LC account cash balance:
   https://www.lendingclub.com/browse/browse/cashBalanceAj.action
« Last Edit: March 14, 2014, 12:55:08 AM by RaymondG »

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #8 on: March 18, 2014, 10:38:12 PM »
You can hit the json itself.....

https://www.lendingclub.com/browse/browseNotesAj.action?method=getResultsInitial&startindex=0&pagesize=1&r=14646


Is there any web address like this to use for downloading *select* subset of browseNotes.csv? It takes much longer time to download the whole browseNotes.csv because there are increasingly more loans available on LC platform recently. Thanks.

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #9 on: March 19, 2014, 03:01:06 AM »
You can hit the json itself.....

https://www.lendingclub.com/browse/browseNotesAj.action?method=getResultsInitial&startindex=0&pagesize=1&r=14646


Is there any web address like this to use for downloading *select* subset of browseNotes.csv? It takes much longer time to download the whole browseNotes.csv because there are increasingly more loans available on LC platform recently. Thanks.

There is; however, the URL becomes unwieldy.  Basically, you need to include &method=search&filter JSON parameter in the browseNotesAj.action? request.

Here is a filter example to limit "browsenotes" to only show loans with "Review Status"=Approved:

filter:[{"m_id":31,"m_metadata":{"m_controlValues":[{"value":true,"label":"Exclude Loans with Public Records","sqlValue":null,"valueIndex":0}],"m_type":"SVAL","m_rep":"CHKBOX","m_label":"Public Records","id":31,"m_onHoverHelp":"A record is a claim made by a credit institution on the borrower's credit history. ","m_className":"classname","m_defaultValue":[]},"m_value":[],"m_visible":false,"m_position":0},{"m_id":13,"m_metadata":{"m_controlValues":[{"value":"APPROVED_CR","label":"Approved","sqlValue":null,"valueIndex":0}],"m_type":"SVAL","m_rep":"CHKBOX","m_label":"Review Status","id":13,"m_onHoverHelp":"Use this filter to exclude loans not yet approved by the Lending Club CREDIT Department.","m_className":"classname","m_defaultValue":[]},"m_value":[{"value":"APPROVED_CR","label":"Approved","sqlValue":null,"valueIndex":0}],"m_visible":false,"m_position":0},{"m_id":17,"m_metadata":{"m_controlValues":[{"value":"ANY","label":"Any","sqlValue":null,"valueIndex":0},{"value":"SEVEND","label":"7 Days or less","sqlValue":null,"valueIndex":1},{"value":"THREED","label":"3 Days or less","sqlValue":null,"valueIndex":2}],"m_type":"SVAL","m_rep":"RADIO","m_label":"Listing Expires in","id":17,"m_onHoverHelp":"Specify maximum number of days to loan listing expiration.","m_className":"classname","m_defaultValue":[{"value":"ANY","label":"Any","sqlValue":null,"valueIndex":0}]},"m_value":[{"value":"ANY","label":"Any","sqlValue":null,"valueIndex":0}],"m_visible":false,"m_position":0},{"m_id":37,"m_metadata":{"m_controlValues":null,"m_type":"SVAL","m_rep":"TEXTBOX","m_label":"Keyword","id":37,"m_onHoverHelp":"Type any keyword","m_className":"classname","m_defaultValue":[]},"m_value":null,"m_visible":false,"m_position":0},{"m_id":39,"m_metadata":{"m_controlValues":[{"value":"Year3","label":"36-month","sqlValue":null,"valueIndex":0},{"value":"Year5","label":"60-month","sqlValue":null,"valueIndex":1}],"m_type":"MVAL","m_rep":"CHKBOX","m_label":"Term (36 - 60 month)","id":39,"m_onHoverHelp":"Select the loan maturities you are interested to invest in","m_className":"classname","m_defaultValue":[{"value":"Year3","label":"36-month","sqlValue":null,"valueIndex":0},{"value":"Year5","label":"60-month","sqlValue":null,"valueIndex":1}]},"m_value":[{"value":"Year3","label":"36-month","sqlValue":null,"valueIndex":0},{"value":"Year5","label":"60-month","sqlValue":null,"valueIndex":1}],"m_visible":false,"m_position":0},
...

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #10 on: March 19, 2014, 11:58:50 PM »
You can hit the json itself.....

https://www.lendingclub.com/browse/browseNotesAj.action?method=getResultsInitial&startindex=0&pagesize=1&r=14646


Is there any web address like this to use for downloading *select* subset of browseNotes.csv? It takes much longer time to download the whole browseNotes.csv because there are increasingly more loans available on LC platform recently. Thanks.

There is; however, the URL becomes unwieldy.  Basically, you need to include &method=search&filter JSON parameter in the browseNotesAj.action? request.

Here is a filter example to limit "browsenotes" to only show loans with "Review Status"=Approved:

......

Fred, thank you. But what browseNotesAj.action returns is not what I want. It does not include as many fields as browseNotes.csv file has. My filters are built upon browseNotes.csv. I would like to be able download a smaller browseNotes.csv file which includes less loans, such as only loans with grade C or lower. By the way, I do not use API, but VBA code using InternetExplorer class. I click [Download All] on page [Browse Notes] to download browseNotes.csv. Thanks.
« Last Edit: March 20, 2014, 12:28:45 AM by RaymondG »

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #11 on: March 20, 2014, 12:31:52 AM »
Fred, thank you. But what browseNotesAj.action returns is not what I want. It does not include as many fields as browseNotes.csv file has. My filters are built upon browseNotes.csv. I would like to be able download a smaller browseNotes.csv file which includes less loans, such as only loans with grade C or lower. By the way, I do not use API, but VBA code using InternetExplorer class. Thanks.

RaymondG,  I simply download the whole browseNotes.csv file, which takes about 3 seconds each time, even  with the 1000+ loans nowadays.

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #12 on: March 20, 2014, 12:41:45 AM »
Fred, thank you. But what browseNotesAj.action returns is not what I want. It does not include as many fields as browseNotes.csv file has. My filters are built upon browseNotes.csv. I would like to be able download a smaller browseNotes.csv file which includes less loans, such as only loans with grade C or lower. By the way, I do not use API, but VBA code using InternetExplorer class. Thanks.

RaymondG,  I simply download the whole browseNotes.csv file, which takes about 3 seconds each time, even  with the 1000+ loans nowadays.

As shown in my log file, it took over 20 seconds to download that file in recent days. Fortunately, the order could still be fulfilled in most of time.

[Edit] Just tested. It was fast and less than 3 seconds for downloading after I restarted computer. But, it became slow again after several tries. Not know why :(
« Last Edit: March 20, 2014, 12:54:52 AM by RaymondG »

AnilG

  • Hero Member
  • *****
  • Posts: 1123
    • View Profile
    • PeerCube
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #13 on: March 20, 2014, 02:14:15 AM »
Empty and Disable Cache in your Browser when you are downloading file. The browser is caching file, on repeat requests browser is checking if cached file has changed before initiating download. It most probably taking up a few extra precious seconds.


RaymondG,  I simply download the whole browseNotes.csv file, which takes about 3 seconds each time, even  with the 1000+ loans nowadays.

As shown in my log file, it took over 20 seconds to download that file in recent days. Fortunately, the order could still be fulfilled in most of time.

[Edit] Just tested. It was fast and less than 3 seconds for downloading after I restarted computer. But, it became slow again after several tries. Not know why :(
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Automatically downloading notes.csv file: anyone know how to
« Reply #14 on: March 20, 2014, 09:32:57 AM »
Empty and Disable Cache in your Browser when you are downloading file. The browser is caching file, on repeat requests browser is checking if cached file has changed before initiating download. It most probably taking up a few extra precious seconds.

It must be the reason. I selected "Delete browsing history on exit" from Internet Options of IE last night. The file was downloaded in 3 seconds at 9:03AM in this morning. Thanks!

[Edit] Just find the better way to empty selected items in Cache:
* Clear Temporary Internet Files:
RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8
« Last Edit: March 21, 2014, 12:03:58 AM by RaymondG »