from bs4 import BeautifulSoup
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

import datetime
from scipy import optimize

def xnpv(rate,cashflows):
    chron_order = sorted(cashflows, key = lambda x: x[0])
    t0 = chron_order[0][0]
    return sum([cf/(1+rate)**((t-t0).days/365.0) for (t,cf) in chron_order])

def xirr(cashflows,guess=0.1):
    return optimize.newton(lambda r: xnpv(r,cashflows),guess)

with open('data/fundrise_transactions_201010_1.html') as file:
    link_list_html = file.read()
soup = BeautifulSoup(link_list_html,
                     "html.parser")

transactions = soup.find_all('div',
                             {'class':'transaction-item__expandable-section'})
print(len(transactions))

transaction_list = []
for t in transactions:
    text = (t
            .get_text())
    transaction_list.append(text)

transaction_text = pd.DataFrame(transaction_list)
print(transaction_text.shape)
transaction_text.head()

for header in ['Name','Advisory fee','Quantity','Price','Gross dividend',
               'Net dividend','Total','Amount','Discount','Bank account',
               'Effective date','Issued date','Effective date','Issued date']:
    transaction_text[0] = (transaction_text[0]
                           .str.replace(header,''))

parsed = pd.DataFrame(
    transaction_text[0].str.split(r'[\t\n]+',
                                  expand=True))
parsed = (parsed
          .drop(columns=[0])
          .rename(columns={1:'type'}))
parsed = parsed.replace('',np.nan)
parsed.head()

parsed['type'] = (parsed['type']
                  .replace({'Order #'           : 'Purchase',
                            'Issued Date'       : 'Dividend',
                            'Redemption number' : 'Withdrawal'}))
print(parsed['type'].value_counts())

d = parsed[parsed['type']=='Dividend'].reset_index(drop=True)
p = parsed[parsed['type']=='Purchase'].reset_index(drop=True)
w = parsed[parsed['type']=='Withdrawal'].reset_index(drop=True)

for col in range(4,80,4):
    d = (d
         .drop(columns=[col,col+1]))
d.columns = range(d.shape[1])
d = d.T.dropna(how='all').T
d = d.rename(columns={0:'type',
                      1:'date'})

d_stack = pd.DataFrame()
for col in range(2,34,2):
    d_stack = (d_stack
               .append(d[['type','date',col,col+1]]
                       .rename(columns={col:'fund',
                                        col+1:'amount'})))
d_stack['date'] = pd.to_datetime(d_stack['date'])
d_stack = (d_stack
           .dropna(subset=['amount'])
           .drop_duplicates()
           .sort_values(['date','fund'])
           .reset_index(drop=True))

p.columns = range(p.shape[1])
p = (p
     .drop(columns=[1,2,3,4,5]))
for col in range(8,80,4):
    p = p.drop(columns=[col,col+1])
p.columns = range(p.shape[1])
p = p.T.dropna(how='all').T
p = p.rename(columns={0:'type',
                      1:'date'})

p_stack = pd.DataFrame()
for col in range(2,38,2):
    p_stack = (p_stack
               .append(p[['type','date',col,col+1]]
                       .rename(columns={col:'fund',
                                        col+1:'amount'})))

p_stack['date'] = pd.to_datetime(p_stack['date'])
p_stack = (p_stack
           .dropna(subset=['amount'])
           .drop_duplicates()
           .sort_values(['date','fund'])
           .reset_index(drop=True))

p_stack.head()

w.columns = range(w.shape[1])
w = (w
     .drop(columns=[1,2,4,6,7,8,10,11,12,13,14])
     .rename(columns={0:'type',
                      3:'date',
                      5:'fund',
                      9:'amount'}))
w = w.T.dropna(how='all').T

w_stack = w.copy()
w_stack['date'] = pd.to_datetime(w_stack['date'])
w_stack.head()

c = pd.DataFrame([('Growth eREIT',        '$5,332.65'),
                  ('Income eREIT',        '$5,332.65'),
                  ('East Coast eREIT',    '$4,004.94'),
                  ('West Coast eREIT',    '$3,017.11'),
                  ('Heartland eREIT',     '$2,968.83'),
                  ('Income eREIT II',     '$1,029.73'),
                  ('Los Angeles eFund',   '$571.24'),
                  ('Washington DC eFund', '$531.79'),
                  ('Growth eREIT III',    '$264.39'),
                  ('Income eREIT III',    '$206.83'),
                  ('National eFund',      '$108.29'),
                  ('Fundrise iPO',        '$3,190.00')],
                  columns=['fund','amount'])
c['type'] = 'Current Value'
c['date'] = pd.to_datetime('2020-10-10')
c.head()

t = (d_stack
     .append(p_stack)
     .append(w_stack)
     .append(c)
     .sort_values(['date','fund'])
     .reset_index(drop=True))
t['amount'] = (t['amount']
               .str.replace('$','')
               .str.replace(',','')
               .astype(float))
t.loc[t['type']=='Purchase','amount'] = -1*t['amount']

t.to_csv('cleansed_transactions.csv')

t[t['fund']=='Income eREIT'].tail()

returns = []
for fund in t['fund'].unique():
    cashflows = t[t['fund']==fund][['date','amount']].to_numpy()
    r = round(xirr(cashflows,guess=0.1)*100,1)
    returns.append((fund,r))

returns = (pd.DataFrame(returns,
                        columns=['fund','irr'])
           .sort_values('irr',
                        ascending=False)
           .reset_index(drop=True))
current_value = (t[t['type']=='Current Value'][['fund','amount']]
                 .rename(columns={'amount':'current value'}))
returns = pd.merge(returns, current_value,
                   on=['fund'],
                   how='outer')
cost_basis = (t[t['type']=='Purchase'][['fund','amount']]
              .rename(columns={'amount':'cost basis'})
              .groupby('fund')
              .sum()
              .reset_index())
cost_basis['cost basis'] *= -1
dividends = (t[t['type']=='Dividend'][['fund','amount']]
             .rename(columns={'amount':'dividends'})
             .groupby('fund')
             .sum()
             .reset_index())
withdrawals = (t[t['type']=='Withdrawal'][['fund','amount']]
               .rename(columns={'amount':'withdrawals'})
               .groupby('fund')
               .sum()
               .reset_index())
first_purchase = (t[t['type']=='Purchase'][['fund','date']]
                  .rename(columns={'date':'first purchase'})
                  .groupby('fund')
                  .min()
                  .reset_index())
last_purchase = (t[t['type']=='Purchase'][['fund','date']]
                 .rename(columns={'date':'last purchase'})
                 .groupby('fund')
                 .max()
                 .reset_index())
returns = pd.merge(returns, cost_basis,
                   on=['fund'],
                   how='outer')
returns = pd.merge(returns, withdrawals,
                   on=['fund'],
                   how='outer')
returns['appreciation'] = returns['cost basis'] - returns['withdrawals'].fillna(0)
returns['appreciation'] = returns['current value'] - returns['appreciation']
returns['appreciation'] = returns['appreciation'].round(2)
returns = pd.merge(returns, dividends,
                   on=['fund'],
                   how='outer')
for col in ['current value', 'cost basis', 'withdrawals', 'appreciation', 'dividends']:
    returns[col] = returns[col].round(0)
returns = returns.fillna('')
for col in ['current value', 'cost basis', 'appreciation']:
    returns[col] = returns[col].astype(int)
returns = pd.merge(returns, first_purchase,
                   on=['fund'],
                   how='outer')
returns = pd.merge(returns, last_purchase,
                   on=['fund'],
                   how='outer').fillna('')
returns

cashflows = t[['date','amount']].to_numpy()
r = round(xirr(cashflows,guess=0.1)*100,1)
print('All Funds IRR to Date: {}%'.format(r))

irr_threshold_to_liquidate = 7

plt.figure(figsize=(11,8.5))
barlist = plt.bar(returns['fund'],
                  returns['irr'])
plt.xticks(rotation=20)
for spine in plt.gca().spines.values():
    spine.set_visible(False)
for bar in barlist[5:]:
    bar.set_color('#ff7f0e')
plt.axhline(irr_threshold_to_liquidate,
            color='k',
            linewidth=1)
plt.title('Keep the Funds in Blue');

funds_to_retain = returns[(returns['irr']>irr_threshold_to_liquidate)&
                          (returns['fund']!='Fundrise iPO')].copy()
print('Total Amount to Retain: $ {}'.format(funds_to_retain['current value'].sum()))
print('          Fundrise iPO: $  {}'.format((returns[returns['fund']=='Fundrise iPO']
                                                   ['current value'].to_numpy()[0])))
cashflows = t[t['fund'].isin(funds_to_retain['fund'])][['date','amount']].to_numpy()
r = round(xirr(cashflows,guess=0.1)*100,1)
print('    Retained Funds IRR:      {}%'.format(r))
funds_to_retain

funds_to_liquidate = returns[(returns['irr']<irr_threshold_to_liquidate)&
                             (returns['fund']!='Fundrise iPO')].copy()
print('Total Amount to Liqudate: $ {}'.format((funds_to_liquidate['current value']
                                               .sum()
                                               .round(2))))
cashflows = t[t['fund'].isin(funds_to_liquidate['fund'])][['date','amount']].to_numpy()
r = round(xirr(cashflows,guess=0.1)*100,1)
print('    Liquidated Funds IRR:      {}%'.format(r))
funds_to_liquidate