Creating a LendingClub Secondary Market Bot

In this post, I will outline how to create a bot for trading notes on FolioFn which is a platform that provides a secondary market for Lending Club notes. My goal is to document the steps I took to create my bot which should allow you to create your own bot, specify your own trading criteria, and even include a custom “grading” system for your filtered loans.

What is LendingClub?

Peer-to-Peer investing (commonly P2P) is the practice of investing in notes of loans for borrowers requesting a loan without going through a traditional financial intermediary and who are unknown to the investor. This type of investing typically takes place online through P2P platforms, among which Lending Club and Prosper are the industry leaders. Personally, I have only had experience using Lending Club and have had no complaints with the platform. This post isn’t to promote one platform over the other but does use developer functionality from LendingClub. Because I haven’t used any other P2P platforms I don’t know if a similar bot could be created for them.

More information can be found on my (dead) personal finance blog.

What is FolioFn?

As mentioned above FolioFn provides a secondary market for P2P lending notes. This market allows investors in loans to sell their notes to other investors. There are some restrictions on the notes that can be sold, e.g. notes of borrowers in bankruptcy cannot be listed, but there are usually tens of thousands of notes listed meeting many different criteria. This secondary market provides a means of liquidity for investors who no longer wish to invest in P2P notes or who have underperforming notes that can be purchased by those speculating that the borrower will “have a turn-around”.

Why Did I Do This?

A few years ago I posted on my financial blog (dead now) about why I quit using P2P lending platforms. This was focused largely on investing in the primary P2P note market, i.e. the initial distribution of funds from investors to borrowers for new loans. However, when looking for alternative investments I kept reading about P2P lending and decided to take another deep dive. This time though I educated myself on making purchases through the secondary market, i.e. a marketplace where investors sell previously originated notes to other investors.

The secondary market has some major advantages and disadvantages. To me, the most advantageous aspect of the market is that you can determine how a borrower will behave with respect to the particular loan you’re purchasing. In other lending/borrowing situations, you can only see how a borrower behaved with respect to past loans which should be an indicator of the borrower’s overall behavior but may not be. Contrarily, on the secondary market, you’re purchasing notes that come with payment history for the particular loan, not just other loans. The two major disadvantages are the typical markup (price over principal value) you pay for the notes and, in my case at least, the fact that most of the interest has already been paid since more interest is paid at the start of the loan rather than at the end.

To get started I read a few blog posts on strategies for the secondary market and found one very appealing. This strategy looks to minimize risk by purchasing notes with only a few months left for repayment, have borrowers that haven’t missed any payments, and offer an attractive yield (among a few other minor criteria). However, most investors were doing this by hand using FolioFn’s filtering capabilities and manually purchasing notes. I thought this could easily be automated since, unlike investing in stocks and bonds, most of the data is quantitative rather than qualitative. Fortunately, FolioFn has an API to view information about currently listed loans and to purchase notes meeting my criteria. Therefore, after some initial setup, this would be a fairly low-risk (due to the nature of the loans and the sheer number of notes being purchased), passive investment since my bot churns out new note investments with the capital generated from old note investments.

Implementation and Details

The implementation of this bot was split into three separate parts: a class to access the Lending Club API, a class used to fetch the most recent note data and return a Pandas dataframe, and the actual bot that uses these classes to filter and purchase notes.

LendingClub API

The LendingClubAPI Python class is used to utilize the Lending Club API functionality. The class is pretty limited since we only need to get some basic account details and make API calls to purchase the notes. Below is the entirety of this class.

from requests_oauthlib import OAuth1
import requests
import json

class LendingClubAPI():
    def __init__(self, auth_key, account_nbr):
        self.auth_key = auth_key
        self.account_nbr = account_nbr
        self.base_url = "https://api.lendingclub.com/api/investor/v1/"
        self.available_cash_url = self.base_url + "accounts/{}/availablecash"
        self.notes_owned_url = self.base_url + "accounts/{}/notes"
        self.account_summary_url = self.base_url + "accounts/{}/summary"
        self.buy_note_url = self.base_url + "accounts/{}/trades/buy/"

    def __do_call(self, url):
        response = requests.get(url, headers={"Authorization" : str(self.auth_key)})
        return response.json()

    def buy_note(self, loanId, orderId, noteId, price):
        this_url = self.buy_note_url.format(self.account_nbr)
        headers = {'Content-Type': 'application/json', 'Accept':'application/json', "Authorization" : str(self.auth_key)}
        data = {
    	   "aid": int(self.account_nbr), "notes":
    	   [
        		{"loanId": int(loanId), "orderId": int(orderId), "noteId": int(noteId), "bidPrice": float(price)}
	       ]
        }
        r = requests.post(this_url, json=data, headers=headers)
        return r.json()

    def get_available_cash(self):
        return self.__do_call(self.available_cash_url.format(self.account_nbr))

    def get_account_summary(self):
        # https://www.lendingclub.com/developers/summary
        return self.__do_call(self.account_summary_url.format(self.account_nbr))

    def get_notes_owned(self):
        return self.__do_call(self.notes_owned_url.format(self.account_nbr))

The constructor of this class defines some variables that hold the Lending Club API authorization key (auth_key), the user’s account number (account_nbr), and the URLs required for our few API calls. The account number and authorization key are passed as parameters to the class which enables reusability of the class without changing the source, i.e. setting these values manually.

The class’ methods are described fairly well by their function names:

  • __do_call(…) is a private method that actually performs the API call and returns the response JSON,
  • buy_note(…) takes some note information as input and creates the URL and parameters required to purchase a note through the API,
  • get_available_cash(…) returns the amount of cash in the user’s account,
  • get_account_summary(…) returns some summary statistics for the account number which was primarily used for some logging purposes,
  • and get_notes_owned(…) gets a listing of notes owned in this account which prevents the bot from purchasing notes belonging to the same loan (ensuring diversification).

Note Fetcher

This is the simplest class in the implementation. The functionality probably could have been wrapped up in the main Folio Bot implementation but was placed in its own class for modularity’s sake. The full implementation is as follows,

import pandas as pd

class NoteFetcher():
    def __init__(self, csv_url="http://public-resources.lendingclub.com.s3-website-us-west-2.amazonaws.com/SecondaryMarketAllNotes.csv"):
        self.url = csv_url

    def fetch_note_data(self):
        """ Returns a pandas dataframe """
        noteData = pd.read_csv(self.url, low_memory=False)
        noteData["YTM"] = pd.to_numeric(noteData["YTM"], errors='coerce')
        return noteData

This class fetches the note CSV provided by LendingClub/FolioFn which is stored via AWS. The YTM (yield to maturity) column in the CSV was originally stored as text so it is coerced into a numeric value using the Pandas library. A Pandas dataframe containing all of the note data is returned from the function call.

Bot Settings

There are a few things that I didn’t include in the source but rather created a file to store these settings. This file is read when the bot runs and applies the settings as need be. In the future, I plan to enhance the bot by making most of the search criteria settings as well.

STOPBUYING=0
MINYIELD=5
CASHRESERVE=50
MINCASHTOBUY=10
MAXMONTHS=7

The settings set forth the following criteria:

  • if STOPBUYING is set to any value greater than 0 the bot does not run (this option stops the bot from attempting to purchase notes),
  • MINYIELD specifies the minimum yield we want to get from the notes,
  • CASHRESERVE is the amount of cash I want to keep in my account (typically a pretty small amount),
  • MINCASHTOBUY determines how much extra cash I need past the CASHRESERVE before the bot attempts to buy any notes (e.g. if the cash reserve is $50 and the min. cash to buy is $10 my account needs $60 in cash before the bot attempts to find any notes for purchase),
  • MAXMONTHS determines the max number of remaining payments (months left on the note).

Folio Bot

FolioBot.py is the core implementation of the note purchasing bot. This logic grabs the notes available on FolioFn, filters them based on my investing criteria, ranks them, and attempts to make purchases.

To start, the bot determines its running mode and reads the bot settings file.

manual = False
if len(sys.argv) > 1:
    if sys.argv[1] == '-m':
        manual = True
        log.write("Running in Manual mode...\n")
    
settings_file = open('/home/pi/FolioBot/BotSettings.dat', 'r')
bot_settings = {}
setting = settings_file.readline()
while setting:
    setting = setting.rstrip().lstrip()
    parts = setting.split("=")
    bot_settings[parts[0]] = parts[1]
    setting = settings_file.readline()

if int(bot_settings["STOPBUYING"]) > 0 and not manual:
    log.write("BotSettings.dat has specified the STOPBUYING condition... exiting.\n")
    log.write("End time " + datetime.now(pytz.timezone('US/Mountain')).strftime("%Y-%m-%d %H:%M:%S") + "\n")
    log.write("-"*80 + "\n\n")
    settings_file.close()
    log.close()
    exit()

minYield = float(bot_settings["MINYIELD"])
cashReserve = float(bot_settings["CASHRESERVE"])
minCashToBuy = float(bot_settings["MINCASHTOBUY"])
minGoodness = float(bot_settings["MINGOODNESS"])

In the logic above the settings file is read and stored in various variables and the run-type is determined. Running in manual mode just means that the same logic will occur for selecting notes but the user will be prompted for buy/pass decisions. Manual mode is used if a -m flag is specified on the command line.

The following chunk of code is where the filtering of the loans happens. The bot starts by ensuring the account has enough cash to look for notes to purchase as specified in the settings file. Then the notes are filtered based on my personal criteria:

  • Notes that are NeverLate, meaning that, for the life of the loan, the borrower hasn’t missed a payment.
  • Notes that have fewer than 10 remaining payments. This shorter-term horizon decreases the amount of interest received since most interest is paid at the beginning of a loan but similarly decreases risk because each note is near the end of repayment. Risk is also mitigated by this time horizon because the borrower has already made most of their payments (at least 83% for 60-month loans [50 out of 60 payments] and 72% for 36-month loans [26 out of 36 payments]).
  • The next criteria is a little confusing but essentially I just want the notes of borrowers whose credit score hasn’t gone down. Although, as specified by the filter, I will accept credit scores trending down if the yield is higher and there are fewer payments left.
  • The next few filters remove notes that are too heavily marked-up, notes with too high of an asking price, and notes that don’t meet our minimum yield criteria, respectively.
fetcher = NoteFetcher()
api = LendingClubAPI(auth_key, acct_nbr)

account_details = api.get_account_summary()
cash = account_details["availableCash"]
cash = cash - cashReserve
notes_owned = account_details["totalNotes"]
log.write("Account cash: " + str(cash) + "\tNotes Owned: " + str(notes_owned) + "\n")

if cash < minCashToBuy and cash > 0:
    log.write("Not enough cash to look for notes.\n")

if cash > 0 and cash > minCashToBuy:
    ## Investment Grade
    notedf = fetcher.fetch_note_data()
    notedf = notedf.rename({'Markup/Discount': 'Markup Discount'}, axis='columns')

    notedf = notedf.loc[notedf["NeverLate"] == True]
    notedf = notedf.loc[notedf["Remaining Payments"] <= 10]
    ## accept the "DOWN" credit trends if there are fewer payments left (relative to loan length)
    ## and higher returns.
    notedf = notedf.query("CreditScoreTrend != 'DOWN' | (`Loan Maturity` == 60 & YTM >= 7.25 & `Remaining Payments` <= 8) \
        | (`Remaining Payments` <= 5 & `Loan Maturity` == 36 & YTM >= 7.25)")
    ## Accept higher markups if there is almost no time left on loan, as long as
    ## we get a higher yield
    notedf = notedf.query("`Markup Discount` <= 2") # | \ (YTM > 4 & `Remaining Payments` <= 2 & `Markup Discount` < 5)")
    notedf = notedf.loc[notedf["AskPrice"] <= 20]
    notedf = notedf.loc[notedf["YTM"] >= minYield] 

One final filter removes any notes for loans that are already represented in my portfolio.

## Not Owned
owned_ids = []
if notes_owned > 0:
    notes_owned_json = api.get_notes_owned()["myNotes"]
    for note in notes_owned_json:
        owned_ids.append(note["loanId"])
    notedf = notedf[~notedf.LoanId.isin(owned_ids)]

The following function is actually the first thing defined in FolioBot.py but I’ve saved the details until now since we haven’t used the function yet. The YTM offered by FolioFn is, as YTMs tend to be, the yield you will receive on the note if the income generated by the note is reinvested at the same rate. This is useful if you plan to reinvest or if you’re using a longer time horizon but in my case, I would like to know the actual income that will be generated from the note as a percentage of the note’s price including markup and investor fees.

Therefore, the following function determines the yield on each note by calculating what the note’s payment amount is, multiplying that by the number of remaining payments, accounting for investor fees, and then dividing by the purchase price to give the note’s yield. Disregard the logging, that’s just so I can monitor the investment decisions made by the bot.

def actual_yield(loan, log):
    price = loan["AskPrice"]
    principal = loan["Original Note Amount"]
    rate = (loan["Interest Rate"]/100) / 12
    term = loan["Loan Maturity"]
    payments_left = loan["Remaining Payments"]

    payment_amt = float(principal) * float((rate*(1+rate)**term)/(((1+rate)**term)-1))
    payment_amt = payment_amt * .99     ## INVESTOR FEEs
    total_back = float(payments_left) * payment_amt

    income = total_back - price
    percent_back = income / price
    yld = ((1 + percent_back)**(float(12/payments_left))) - 1   ## Annualized yield
    log.write("\t\tPrice: " + str(price) + "\n\t\tPrincipal: " + str(principal) + "\n\t\tRate: " + str(rate) \
            + "\n\t\tTerm: " + str(term) + "\n\t\tPayments Left: " + str(payments_left) \
            + "\n\t\tPayment Amount: " + str(payment_amt) + "\n\t\tTotal Back: " + str(total_back) \
            + "\n\t\tIncome: " + str(income) + "\n\t\tPercent Back: " + str(percent_back) + "\n\t\tYield: " \
            + str(yld) + "\n")
    return yld

This yield calculation is used in the following code to create a goodness dictionary and to re-filter based on my yield calculations. This dictionary organizes the notes based on which criteria I care about the most. The comments in the logic are pretty useful but essentially, I am determining a goodness score that favors (above all) YTM, 60-month loans over 36-month loans (since a larger percentage is paid back thus a better payment history), and fewer remaining payments. This dictionary is sorted based on the goodness score and the bot attempts to purchase the notes with the best score first (in case we run out of money while buying notes).

## Best For Portfolio
notedf = notedf.sort_values(["YTM"], ascending=[0])
log.write("Found " + str(notedf.shape[0]) +" investment grade notes.\n")
if(notedf.shape[0] > 0):
    myyield = [0 for _ in range(notedf.shape[0])]
    notedf["MyYield"] = myyield
    goodness_dict = {}   # index -> weighted goodness of loan
    log.write("Calculating note goodness...\n")
    for index, row in notedf.iterrows():
        # Favor 60 month loans over 36 month loans since, if there is a year left,
        # one has paid 80% and the other only 66%. Also favor fewer payments left.
        # Favor, above all else, YTM.
        # Need an equation (weighting eqn.) to order notes
        goodness = 0.0
        log.write("\tCalculating Goodness of Loan Id: " + str(row["LoanId"]) + "\n")
        ytm = (actual_yield(row, log)*100)   # annual return after fees, etc
        notedf.loc[index, "MyYield"] = ytm
        if manual:
            print("Loan Id: " + str(row["LoanId"]) + "\tYTM: " + str(ytm))
        if ytm < minYield or ytm > 55:
            if ytm > row["YTM"]:
                log.write("\t!! Something is fishy with this note... inspect manually if need be !!\n")
                # if we don't get enough after fees, bail
                continue
        pmts = row["Remaining Payments"]
        length = row["Loan Maturity"]
        length_add = 0
        if length == 36:
            length_add = 0
        else:
            length_add = 2
        goodness = ytm - (pmts / 2) + length_add
        if goodness < minGoodness:
            continue
        goodness_dict[index] = goodness

    goodness_dict = sorted(goodness_dict.items(), key=lambda kv : kv[1])
    goodness_dict.reverse()
    ## goodness_dict is now a collection of notedf indices to our goodness score
    ## based on the criteria we favored above.
    goodness_dict = collections.OrderedDict(goodness_dict)
    log.write(str(len(goodness_dict)) + " notes remain after actual yield calculations.\n")

Finally, the bot iterates through the goodness dictionary and attempts to purchase the notes therein. This part of the logic accounts for most of the manual input. If manual mode is specified the user is prompted for buy/pass decisions. Otherwise, the bot attempts to buy all of the notes in the goodness dictionary. Every time the bot attempts to purchase a note, the status is recorded and logged. Similarly, every note that is purchased is written to a CSV file to help generate data to enhance future decision-making processes. As the notes are paid-off or charged-off the CSV files will be updated and, as a future addition to the bot, machine learning will be utilized to assist in the decision to purchase a note based on similar note performance. This would boil down to a two-class classification system (Fully Paid or Charged Off) and will be factored into the goodness score or the note filtering.

## Buy as many notes out of the goodness_dict that we can
purchasedNotes = pd.DataFrame(columns=notedf.columns)
for key in goodness_dict:
    row = notedf.loc[key]
    loanId = row["LoanId"]
    orderId = row["OrderId"]
    noteId = row["NoteId"]
    price = row["AskPrice"]

    log.write("Attempting to buy note\n")
    log.write("\tLoanId: " + str(loanId) + "\tOrderId: " + str(orderId) + \
        "\tNoteId: " + str(noteId) + "\tPrice: " + str(price) + "\tTheir YTM: " + str(row["YTM"]) + \
        "\tMarkup/Discount: " + str(row['Markup Discount']) + "\tMy YTM: " + str(row["MyYield"]) + "\n")
    response = None
    if cash > price:
        # loanId, orderId, noteId, price
        if manual:
             print(row)
             val = input("\nDo you want to purchase this note (y/n)?")
             while val != 'y' and val != 'n':
                 val = input("Select Yes or No (y/n): ")
             if val == 'n':
                 print("\n\nPassing on note.\n\n")
                 continue
             if val == 'y':
                 print("\n\nPurchasing note...\n\n")
                 response = api.buy_note(loanId, orderId, noteId, price)
        else:
            response = api.buy_note(loanId, orderId, noteId, price)
    else:
        log.write("\tNot enough cash! Available Cash: " + str(cash) + " Note Price: " + str(price) + "\n")
        # not enough money, try the next note
        continue
    # successful? change cash
    if response is not None:
        executionStatus = response["buyNoteConfirmations"][0]["executionStatus"][0].strip()
        log.write("\tExecution Status: " + executionStatus + "\n")
        if executionStatus == "SUCCESS_PENDING_SETTLEMENT":
            log.write("\tThe note was successfully purchased, pending settlement.\n")
            purchasedNotes = purchasedNotes.append(row, ignore_index=True)
                cash -= price
    if purchasedNotes.shape[0] > 0:
        purchasedNotes.to_csv("/home/pi/FolioBot/purchase_history/" + datetime.now(pytz.timezone('US/Mountain')).strftime("%Y_%m_%d_%H_%M_%S") + "_purchased_notes.csv", index=False)

Full Code

NOTE: lines 34 and 35 of FolioBot.py need to be updated with your information if you plan on copy/pasting this logic.

NoteFetcher.py

import pandas as pd

class NoteFetcher():
    def __init__(self, csv_url="http://public-resources.lendingclub.com.s3-website-us-west-2.amazonaws.com/SecondaryMarketAllNotes.csv"):
        self.url = csv_url

    def fetch_note_data(self):
        """ Returns a pandas dataframe """
        noteData = pd.read_csv(self.url, low_memory=False)
        noteData["YTM"] = pd.to_numeric(noteData["YTM"], errors='coerce')
        return noteData

LendingClubAPI.py

from requests_oauthlib import OAuth1
import requests
import json

class LendingClubAPI():
    def __init__(self, auth_key, account_nbr):
        self.auth_key = auth_key
        self.account_nbr = account_nbr
        self.base_url = "https://api.lendingclub.com/api/investor/v1/"
        self.available_cash_url = self.base_url + "accounts/{}/availablecash"
        self.notes_owned_url = self.base_url + "accounts/{}/notes"
        self.account_summary_url = self.base_url + "accounts/{}/summary"
        self.buy_note_url = self.base_url + "accounts/{}/trades/buy/"

    def __do_call(self, url):
        response = requests.get(url, headers={"Authorization" : str(self.auth_key)})
        return response.json()

    def buy_note(self, loanId, orderId, noteId, price):
        this_url = self.buy_note_url.format(self.account_nbr)
        headers = {'Content-Type': 'application/json', 'Accept':'application/json', "Authorization" : str(self.auth_key)}
        data = {
    	   "aid": int(self.account_nbr), "notes":
    	   [
        		{"loanId": int(loanId), "orderId": int(orderId), "noteId": int(noteId), "bidPrice": float(price)}
	       ]
        }
        r = requests.post(this_url, json=data, headers=headers)
        return r.json()

    def get_available_cash(self):
        return self.__do_call(self.available_cash_url.format(self.account_nbr))

    def get_account_summary(self):
        # https://www.lendingclub.com/developers/summary
        return self.__do_call(self.account_summary_url.format(self.account_nbr))

    def get_notes_owned(self):
        return self.__do_call(self.notes_owned_url.format(self.account_nbr))

FolioBot.py

from NoteFetcher import NoteFetcher
from LendingClubAPI import LendingClubAPI

import collections
import operator
from datetime import datetime
import pandas as pd
import pytz
import sys
pd.set_option('display.max_columns', 500)

def actual_yield(loan, log):
    price = loan["AskPrice"]
    principal = loan["Original Note Amount"]
    rate = (loan["Interest Rate"]/100) / 12
    term = loan["Loan Maturity"]
    payments_left = loan["Remaining Payments"]

    payment_amt = float(principal) * float((rate*(1+rate)**term)/(((1+rate)**term)-1))
    payment_amt = payment_amt * .99     ## INVESTOR FEEs
    total_back = float(payments_left) * payment_amt

    income = total_back - price
    percent_back = income / price
    yld = ((1 + percent_back)**(float(12/payments_left))) - 1   ## Annualized yield
    log.write("\t\tPrice: " + str(price) + "\n\t\tPrincipal: " + str(principal) + "\n\t\tRate: " + str(rate) \
            + "\n\t\tTerm: " + str(term) + "\n\t\tPayments Left: " + str(payments_left) \
            + "\n\t\tPayment Amount: " + str(payment_amt) + "\n\t\tTotal Back: " + str(total_back) \
            + "\n\t\tIncome: " + str(income) + "\n\t\tPercent Back: " + str(percent_back) + "\n\t\tYield: " \
            + str(yld) + "\n")
    return yld

if __name__ == '__main__':
    auth_key = "<YOUR AUTH KEY>"
    acct_nbr = <YOUR ACCOUNT NUMBER>
    log = open('/home/pi/FolioBot/log.log', "a")
    log.write("-"*80 + "\n")
    log.write("Start time " + datetime.now(pytz.timezone('US/Mountain')).strftime("%Y-%m-%d %H:%M:%S") + "\n")
    
    manual = False
    if len(sys.argv) > 1:
        if sys.argv[1] == '-m':
            manual = True
            log.write("Running in Manual mode...\n")
    
    settings_file = open('/home/pi/FolioBot/BotSettings.dat', 'r')
    bot_settings = {}
    setting = settings_file.readline()
    while setting:
        setting = setting.rstrip().lstrip()
        parts = setting.split("=")
        bot_settings[parts[0]] = parts[1]
        setting = settings_file.readline()

    if int(bot_settings["STOPBUYING"]) > 0 and not manual:
        log.write("BotSettings.dat has specified the STOPBUYING condition... exiting.\n")
        log.write("End time " + datetime.now(pytz.timezone('US/Mountain')).strftime("%Y-%m-%d %H:%M:%S") + "\n")
        log.write("-"*80 + "\n\n")
        settings_file.close()
        log.close()
        exit()
    minYield = float(bot_settings["MINYIELD"])
    cashReserve = float(bot_settings["CASHRESERVE"])
    minCashToBuy = float(bot_settings["MINCASHTOBUY"])
    minGoodness = float(bot_settings["MINGOODNESS"])

    fetcher = NoteFetcher()
    api = LendingClubAPI(auth_key, acct_nbr)

    account_details = api.get_account_summary()
    cash = account_details["availableCash"]
    """
        IMPORTANT: Change this later... LC only allowed us to transfer 1000 initially
            I don't want to have ~1/2 of my savings in these investments when the
            money is supposed to be for riskless investments (CDs/High-Yield Savings).
            Therefore, just keep 500 in cash and we will transfer this out later.
    """
    cash = cash - cashReserve
    notes_owned = account_details["totalNotes"]
    log.write("Account cash: " + str(cash) + "\tNotes Owned: " + str(notes_owned) + "\n")

    if cash < minCashToBuy and cash > 0:
        log.write("Not enough cash to look for notes.\n")

    if cash > 0 and cash > minCashToBuy:
        ## Investment Grade
        notedf = fetcher.fetch_note_data()
        notedf = notedf.rename({'Markup/Discount': 'Markup Discount'}, axis='columns')

        notedf = notedf.loc[notedf["NeverLate"] == True]
        notedf = notedf.loc[notedf["Remaining Payments"] <= 10]
        ## accept the "DOWN" credit trends if there are fewer payments left (relative to loan length)
        ## and higher returns.
        notedf = notedf.query("CreditScoreTrend != 'DOWN' | (`Loan Maturity` == 60 & YTM >= 7.25 & `Remaining Payments` <= 8) \
            | (`Remaining Payments` <= 5 & `Loan Maturity` == 36 & YTM >= 7.25)")
        ## Accept higher markups if there is almost no time left on loan, as long as
        ## we get a higher yield
        notedf = notedf.query("`Markup Discount` <= 2") # | \ (YTM > 4 & `Remaining Payments` <= 2 & `Markup Discount` < 5)")
        notedf = notedf.loc[notedf["AskPrice"] <= 20]
        notedf = notedf.loc[notedf["YTM"] >= minYield]   # Ally 1 yr CD is @ 2% need that + extra for risk

        ## Not Owned
        owned_ids = []
        if notes_owned > 0:
            notes_owned_json = api.get_notes_owned()["myNotes"]
            for note in notes_owned_json:
                owned_ids.append(note["loanId"])
        notedf = notedf[~notedf.LoanId.isin(owned_ids)]

        ## Best For Portfolio
        notedf = notedf.sort_values(["YTM"], ascending=[0])
        log.write("Found " + str(notedf.shape[0]) +" investment grade notes.\n")
        if(notedf.shape[0] > 0):
            myyield = [0 for _ in range(notedf.shape[0])]
            notedf["MyYield"] = myyield
            goodness_dict = {}   # index -> weighted goodness of loan
            log.write("Calculating note goodness...\n")
            for index, row in notedf.iterrows():
                # Favor 60 month loans over 36 month loans since, if there is a year left,
                # one has paid 80% and the other only 66%. Also favor fewer payments left.
                # Favor, above all else, YTM.
                # Need an equation (weighting eqn.) to order notes
                goodness = 0.0
                log.write("\tCalculating Goodness of Loan Id: " + str(row["LoanId"]) + "\n")
                ytm = (actual_yield(row, log)*100)   # annual return after fees, etc
                notedf.loc[index, "MyYield"] = ytm
                if manual:
                    print("Loan Id: " + str(row["LoanId"]) + "\tYTM: " + str(ytm))
                if ytm < minYield or ytm > 55:
                    if ytm > row["YTM"]:
                        log.write("\t!! Something is fishy with this note... inspect manually if need be !!\n")
                    # if we don't get enough after fees, bail
                    continue
                pmts = row["Remaining Payments"]
                length = row["Loan Maturity"]
                length_add = 0
                if length == 36:
                    length_add = 0
                else:
                    length_add = 2
                goodness = ytm - (pmts / 2) + length_add
                if goodness < minGoodness:
                    continue
                goodness_dict[index] = goodness

            goodness_dict = sorted(goodness_dict.items(), key=lambda kv : kv[1])
            goodness_dict.reverse()
            ## goodness_dict is now a collection of notedf indices to our goodness score
            ## based on the criteria we favored above.
            goodness_dict = collections.OrderedDict(goodness_dict)
            log.write(str(len(goodness_dict)) + " notes remain after actual yield calculations.\n")

            ## Buy as many notes out of the goodness_dict that we can
            ## Buy is tested, this loop is not
            purchasedNotes = pd.DataFrame(columns=notedf.columns)
            for key in goodness_dict:
                row = notedf.loc[key]
                loanId = row["LoanId"]
                orderId = row["OrderId"]
                noteId = row["NoteId"]
                price = row["AskPrice"]

                log.write("Attempting to buy note\n")
                log.write("\tLoanId: " + str(loanId) + "\tOrderId: " + str(orderId) + \
                          "\tNoteId: " + str(noteId) + "\tPrice: " + str(price) + "\tTheir YTM: " + str(row["YTM"]) + \
                          "\tMarkup/Discount: " + str(row['Markup Discount']) + "\tMy YTM: " + str(row["MyYield"]) + "\n")
                response = None
                if cash > price:
                    # loanId, orderId, noteId, price
                    if manual:
                        print(row)
                        val = input("\nDo you want to purchase this note (y/n)?")
                        while val != 'y' and val != 'n':
                            val = input("Select Yes or No (y/n): ")
                        if val == 'n':
                            print("\n\nPassing on note.\n\n")
                            continue
                        if val == 'y':
                            print("\n\nPurchasing note...\n\n")
                            response = api.buy_note(loanId, orderId, noteId, price)
                    else:
                        response = api.buy_note(loanId, orderId, noteId, price)
                else:
                    log.write("\tNot enough cash! Available Cash: " + str(cash) + " Note Price: " + str(price) + "\n")
                    # not enough money, try the next note
                    continue
                # successful? change cash
                if response is not None:
                    executionStatus = response["buyNoteConfirmations"][0]["executionStatus"][0].strip()
                    log.write("\tExecution Status: " + executionStatus + "\n")
                    if executionStatus == "SUCCESS_PENDING_SETTLEMENT":
                        log.write("\tThe note was successfully purchased, pending settlement.\n")
                        purchasedNotes = purchasedNotes.append(row, ignore_index=True)
                        cash -= price

            if purchasedNotes.shape[0] > 0:
                purchasedNotes.to_csv("/home/pi/FolioBot/purchase_history/" + datetime.now(pytz.timezone('US/Mountain')).strftime("%Y_%m_%d_%H_%M_%S") + "_purchased_notes.csv", index=False)
    else:
        log.write("You're broke...\n")

    
    log.write("End time " + datetime.now(pytz.timezone('US/Mountain')).strftime("%Y-%m-%d %H:%M:%S") + "\n")
    log.write("-"*80 + "\n\n")

    settings_file.close()
    log.close()

Leave a Reply

Your email address will not be published. Required fields are marked *