pickles
Member of DD Central
Posts: 94 
Likes: 57
|
Post by pickles on Jul 30, 2017 9:55:35 GMT
I'm sure all the information is there, but is there a sensible way of getting it out of the site without drilling down into the financials of every loan one by one and painstakingly extracting the numbers from within a pile of useless/irrelevant carp?
Even the numbers on the dashboard don't add up:surely "payment outstanding" should equal "invoiced" - "payment received"? It's not even close. Then there's the miriad refinancing: why are some refinanced loans shown as the full amount outstanding, but "expired", others are "live" with a zero outstanding. The various reports seem to give pages and pages of the same numbers repeated. The columns in the dashboard views are mostly a waste of screen space filled with zeroes or "--".
It would be nice to find out exactly how much is in each live loan, what the repayment dates are for interest and capital, and the "current" end date. Any tips?
|
|
pickles
Member of DD Central
Posts: 94
Likes: 57
|
Post by pickles on Jul 30, 2017 16:01:22 GMT
Thanks Paul. I thought I might be missing some obvious button hidden in plain sight on the website, but clearly not! I have a PhD in physics and getting on for 20 years in fixed income derivatives, but cannot make head or tail of the figures presented.
|
|
|
|
Post by tybalt on Aug 1, 2017 10:08:28 GMT
By way of example
Run Dashboard
Record Assets total – Third Box Along in green
Record Principal Outstanding – lower right hand grey box
Switch to Loan Part List
Click Export Icon – Forth line down right hand side
Click Export All To Excel
Click on Export Sheet bottom right hand corner
ExportXX will be opened
Sort on State & Principal Remaining
Insert lines and Total at Last Active
Total above should equal Assets Total plus Principal Outstanding
A lot easier to keep to one of the two ThinCats Forums
|
|
|
|
Post by anotherinvestor on Sept 8, 2017 10:45:21 GMT
at one stage i created an excel spreadsheet to parse the nominal ledger and use pivot tables to provide comprehensive reporting.
my latest approach is to use Python to pre-process the nominal ledger and read the data into my favourite reporting app (both excel or libreOffice have pivot tables etc).
the following script will take the data and make it usable if you install Python 3 - nowadays I don't have much time for ThinCats and I already know which of the borrowers aren't going to honour their agreements so I don't have time to make this user friendly but here it is - all you need to do is download the nominal ledger in xlsx format and Save the Debtors Control Sheet as a csv. (Another Python script here https://gist.github.com/julianthome/2d8546e7bed869079ab0f409ae0faa87will do this if you run it with the nominal ledger file and specify the "Debtors Control" sheet.)
# %%
import csv
# %%
examplefile = open('Debtors Control.csv')
outputFile = open('output.csv', 'w', newline='')
outputWriter = csv.writer(outputFile)
exampleReader = csv.reader(examplefile)
# %%
for row in exampleReader:
# print('row #'+str(exampleReader.line_num) + ' ' + str(row))
# print(str(row[4]))
if exampleReader.line_num == 1:
row[1] = 'AccDate'
row[3] = 'LoanRef'
# print('...' + str(row))
outputWriter.writerow(row)
continue
s = row[4]
suffix = ")"
prefix = "#"
while s.endswith(suffix):
s = s[:-1]
if s.startswith(prefix):
s = "Reconciled(" + s
words = s.split('(',maxsplit=2)
t1 = words[2].split(" [")
words[2] = t1[0]
row = row + words
# print(words)
# print(row)
outputWriter.writerow(row)
#
outputFile.close()
|
|
|
|
Post by anotherinvestor on Sept 8, 2017 11:03:50 GMT
yes that's correct! I was just looking for an upload facility which might make it easier... Attachments:parse_csv.py (873 B)
|
|
