dh1
Member of DD Central
Posts: 373 
Likes: 383
|
Post by dh1 on Jul 15, 2017 18:18:03 GMT
I've had a problem getting a simple list of my Collateral Loan parts that I can manipulate. The website doesn't currently allow a simple .csv (or whatever) export, although some such is on the - probably very long - "to do" list. After tinkering, I've discovered that Excel 2016 does a pretty good job importing text copied from the website (eg the "my loans page"). The result is a bit messy but crucially, most of the key bits are separated into individual cells so you can work with them directly in Excel. I ended up with 9 cells per loan part. 11 would be ideal but... All you need to do to achieve this is to go to the webpage you are interested in, select everything (CTRL & A in Windows 10 or whatever); admire the pretty blue colours; copy (CTRL & C or right click "copy"); go to a blank Excel workbook and select (eg) cell A1; then paste AS PLAIN TEXT. Clean up as desired. If anyone has discovered a simpler solution, please don't hesitate to share  |
|
|
|
Post by dan1 on Jul 15, 2017 18:32:02 GMT
I've had a problem getting a simple list of my Collateral Loan parts that I can manipulate. The website doesn't currently allow a simple .csv (or whatever) export, although some such is on the - probably very long - "to do" list. After tinkering, I've discovered that Excel 2016 does a pretty good job importing text copied from the website (eg the "my loans page"). The result is a bit messy but crucially, most of the key bits are separated into individual cells so you can work with them directly in Excel. I ended up with 9 cells per loan part. 11 would be ideal but... All you need to do to achieve this is to go to the webpage you are interested in, select everything (CTRL & A in Windows 10 or whatever); admire the pretty blue colours; copy (CTRL & C or right click "copy"); go to a blank Excel workbook and select (eg) cell A1; then paste AS PLAIN TEXT. Clean up as desired. If anyone has discovered a simpler solution, please don't hesitate to share The resulting multiple lines per loan is a PITA requiring far too many uses of offset() for my liking. Unbolted is much the same except recently they introduced a download for your loan parts.
|
|
elliotn
Member of DD Central
Posts: 3,063
Likes: 2,681
|
Post by elliotn on Jul 16, 2017 2:58:21 GMT
I've had a problem getting a simple list of my Collateral Loan parts that I can manipulate. The website doesn't currently allow a simple .csv (or whatever) export, although some such is on the - probably very long - "to do" list. After tinkering, I've discovered that Excel 2016 does a pretty good job importing text copied from the website (eg the "my loans page"). The result is a bit messy but crucially, most of the key bits are separated into individual cells so you can work with them directly in Excel. I ended up with 9 cells per loan part. 11 would be ideal but... All you need to do to achieve this is to go to the webpage you are interested in, select everything (CTRL & A in Windows 10 or whatever); admire the pretty blue colours; copy (CTRL & C or right click "copy"); go to a blank Excel workbook and select (eg) cell A1; then paste AS PLAIN TEXT. Clean up as desired. If anyone has discovered a simpler solution, please don't hesitate to share The resulting multiple lines per loan is a PITA requiring far too many uses of offset() for my liking. Unbolted is much the same except recently they introduced a download for your loan parts. Also, any change to the layout can make it redundant, say, adding lines of text about 14%, now fully funded etc. I pull the data from my transaction history excell export now and once set up is much easier to maintain and the recent inclusion of Borrower IDs saves multiple visits to the website for new loan parts.
|
|
theshape
Member of DD Central
Posts: 153
Likes: 109
|
Post by theshape on Jul 16, 2017 11:54:53 GMT
I tried the method set out by the OP but ended up with what looked like a confusing mess in excel (my excel skills are very poor).
What is the best way to work out my total holding for each borrower on Collateral? Is it a case of manually entering each purchase and sale into excel and then sorting the results or is there a better way?
|
|
|
|
Post by dan1 on Jul 16, 2017 12:33:00 GMT
I tried the method set out by the OP but ended up with what looked like a confusing mess in excel (my excel skills are very poor). What is the best way to work out my total holding for each borrower on Collateral? Is it a case of manually entering each purchase and sale into excel and then sorting the results or is there a better way? As elliotn indicated the easiest way is to download your transactions (enter your first transaction date and hit Export to Excel). This includes a column for the borrower ID. You'll need to sum transactions for each borrower ID, possibly with a sumif().
|
|
dh1
Member of DD Central
Posts: 373
Likes: 383
|
Post by dh1 on Oct 1, 2017 8:53:32 GMT
Not sure if anyone else has spotted this yet but Collateral have introduced an "Export to Excel" button on the "My loans" (loans funded) page. It's the last tab on the right, immediately below the "Available funds" icon/link. Works, too. I've just downloaded mine and everything seems to add up with one caveat. Changing the cell format in the "amount" column from the default to "currency" didn't change all the formats in that column. A quick look through and the insertion of £ signs in appropriate places fixed things. Happy days....
|
|
SteveT
Member of DD Central
Posts: 6,873
Likes: 7,918
|
Post by SteveT on Oct 1, 2017 9:32:51 GMT
Not sure if anyone else has spotted this yet but Collateral have introduced an "Export to Excel" button on the "My loans" (loans funded) page. It's the last tab on the right, immediately below the "Available funds" icon/link. Works, too. I've just downloaded mine and everything seems to add up with one caveat. Changing the cell format in the "amount" column from the default to "currency" didn't change all the formats in that column. A quick look through and the insertion of £ signs in appropriate places fixed things. Happy days.... The "Amounts" for the BL property loans are currently being exported as Text entries, not Numbers. Collateral Rep, it would be handy if both the Amount and Interest columns can be formatted as Currency (£) by default. Thanks.
|
|
