Author Topic: Parting gift for you all  (Read 4563 times)

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Parting gift for you all
« on: March 16, 2019, 05:26:11 PM »
Hey All,

I've barely posted on here over the past 2 years but for a while I was reading every single post by all the regulars and learned a lot. I was forced to sell my entire portfolio of loans back at the beginning of 2018 for compliance reasons for a new job I started (I work in financial industry).

Anyways, I put together a handy tool with excel using VBA to manage the entire investment process of investing in these loans--from downloading the csv file into excel and filtering through potential investments, making the investment decision, tracking credit metrics and returns in my portfolio, tracking payments, defaults, etc, using mostly macros. Obviously this isn't as advanced from the statistics or programming side of things as many of you are much smarter than me from that perspective, but my philosophy was always that I wanted to present myself with relevant statistics from a credit perspective and then let my brain make the investment decisions rather than using purely statistical methods (my background is corporate credit rather than consumer credit, where credit decisions are much less driven by regressions, data mining, etc and more financial statement analysis, etc). However, I did use the NSR analytics tool quite a bit to develop loss rates for the various credit metrics. I also used a lot of interesting demographic data based on zip code using databases from the USPS and US Census Bureau.

I didn't want this tool to go to waste so I thought I might post it on here. It's probably better suited for the beginners on here or those not using one of the online offerings that auto-invest your portfolio for you. I posted a quick sample here in pdf form and if there is any interest I'll figure out how to get you the file (maybe email? I think it's way too big to post at > 5.7 MB).

I'm more than happy to answer any questions and I'd love to hear feedback. Thanks!

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #1 on: March 16, 2019, 05:26:41 PM »
Attachment #2/4

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #2 on: March 16, 2019, 05:27:07 PM »
Attachment #3/4

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #3 on: March 16, 2019, 05:27:33 PM »
Attachment #4/4

Fred93

  • Hero Member
  • *****
  • Posts: 2161
    • View Profile
Re: Parting gift for you all
« Reply #4 on: March 16, 2019, 05:45:52 PM »
Interesting that you mention zip code data.  Several times in past years I looked into freely available data by zip code.  I was thinking specifically about unemployment at the time, tho clearly many variables are available.

I never implemented.  So I wonder looking back, what are your thoughts about the value or not of your inclusion of zip code based data?

What zip code based parameters do you think were the most helpful?

rawraw

  • Hero Member
  • *****
  • Posts: 2767
    • View Profile
Re: Parting gift for you all
« Reply #5 on: March 17, 2019, 08:37:18 AM »
What kind of job causes a conflict with LC? Unless you are working for a firm that just doesn't allow that type of investment

jd

  • Jr. Member
  • **
  • Posts: 65
    • View Profile
Re: Parting gift for you all
« Reply #6 on: March 17, 2019, 01:57:57 PM »
Attachment #2/4

That is really cool of you.  You won't be planting anymore corn in this field but you left some seeds for others.  It's a decent thing to do.


AnilG

  • Hero Member
  • *****
  • Posts: 1094
    • View Profile
    • PeerCube
Re: Parting gift for you all
« Reply #7 on: March 18, 2019, 02:13:27 AM »
Nicely done, interesting way to lay out some of the information. I am inspired to incorporate some ideas in PeerCube. Thanks.
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com

AdamNYC

  • Newbie
  • *
  • Posts: 1
    • View Profile
    • Email
Re: Parting gift for you all
« Reply #8 on: March 19, 2019, 05:35:55 PM »
Hi. Thanks very much for this considerate offer. I believe most people would've just moved on with their own lives, without putting thought or effort into enabling others to benefit from and build on their work, so kudos.

From your screenshots and description, it also looks and sounds to me like this would be quite useful.

I sent you a private message with my email address, if you're willing to send it that way. Again, many thanks.

Adam
« Last Edit: March 19, 2019, 05:44:38 PM by AdamNYC »

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #9 on: March 19, 2019, 09:43:45 PM »
Interesting that you mention zip code data.  Several times in past years I looked into freely available data by zip code.  I was thinking specifically about unemployment at the time, tho clearly many variables are available.

I never implemented.  So I wonder looking back, what are your thoughts about the value or not of your inclusion of zip code based data?

What zip code based parameters do you think were the most helpful?

My thinking was that for me to do better than average (or even better LC's credit model), I would have to consider things that both other investors and LC's credit and pricing model weren't considering. My guess was that LC probably isn't allowed to incorporate zip code, gender, age into their model due to federal regulations (Community Reinvestment Act/discrimination laws?) and most individuals wouldn't because it was really messy getting the Census Bureau and US Postal Service zip code data integrated (especially with the last two digits XX).

I didn't do any statistical analysis on the data but just anecdotally it seemed like per capita income (relative to state and USA) and unemployment (relative to state and USA) were probably valuable. Net migration and zip code primary county population painted a good picture of the area as well. I really never used the marital status, age, or gender data but included it initially and never took it out.  There wasn't enough differentiation in marital status and gender for me to find it valuable (I think marital status the top quartile was >52.6% married and bottom quartile was <46.7% married).

To get the data in the zip code format 123XX, I think I used weighted average population of each county in each zip code then used pivot tables to combine each of the 100 zip codes for each 123XX. Then was able to use weighted average county population to come up with a zip code PCI/unemployment/etc for each 123XX.

The data is all in my file if you're interested. I think the zip code data could be really valuable in the hands of someone who knows how to analyze it much better than me.

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #10 on: March 19, 2019, 09:58:15 PM »
What kind of job causes a conflict with LC? Unless you are working for a firm that just doesn't allow that type of investment

My job I had before getting involved in LC was in commercial banking and in no way related to LC or P2P lending but wouldn't allow it. Now I work in structured finance and any type of p2p lending/investing is off limits--LC funds a large chunk of their loans through securitizations so I can understand their reasoning.

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #11 on: March 19, 2019, 10:10:18 PM »
Nicely done, interesting way to lay out some of the information. I am inspired to incorporate some ideas in PeerCube. Thanks.

Thanks AnilG! I read a lot of your blog and learned a ton. I specifically really enjoyed your post about loan performance with time to fund.

https://www.peercube.com/blog/post/mad-rush-at-loan-release-time-part-ii---loan-performance-with-time-to-fund

BusinessCasual

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Parting gift for you all
« Reply #12 on: March 19, 2019, 10:24:05 PM »
Attachment #2/4

That is really cool of you.  You won't be planting anymore corn in this field but you left some seeds for others.  It's a decent thing to do.

Thanks jd, rawraw, Fred93, and AnilG. Really appreciated all your posts on here over the past couple years.

pressure9pa

  • Newbie
  • *
  • Posts: 5
    • View Profile
Re: Parting gift for you all
« Reply #13 on: March 22, 2019, 05:34:48 PM »
Thanks for putting it out there.  Much appreciated. 

hdsouza

  • Jr. Member
  • **
  • Posts: 50
    • View Profile
Re: Parting gift for you all
« Reply #14 on: April 03, 2019, 07:45:50 PM »
Yes. I second that. This kind of attitude and information to the community keeps us going.
You Rock buddy!!!!
... And ALL the Best in your new job.