def get_ssy_details(number):
try:
ssy_obj = Ssy.objects.get(number=number)
total = 0
interest = 0
principal = 0
cash_flows = list()
ssy_num = ssy_obj.number
ssy_trans = SsyEntry.objects.filter(number=ssy_num).order_by('trans_date')
for entry in ssy_trans:
if entry.entry_type.lower() == 'cr' or entry.entry_type.lower() == 'credit':
if entry.interest_component:
interest += entry.amount
else:
principal += entry.amount
cash_flows.append((entry.trans_date, -1*float(entry.amount)))
else:
principal -= entry.amount
if principal < 0:
interest += principal
principal = 0
cash_flows.append((entry.trans_date, float(entry.amount)))
total = principal + interest
cash_flows.append((datetime.date.today(), float(total)))
roi = xirr(cash_flows, 0.1)*100
roi = round(roi, 2)
return {'number': ssy_num, 'total': total, 'principal':principal, 'interest':interest, 'roi':roi}
except Ssy.DoesNotExist:
return None
def update_latest_value(user):
if not user:
objs = Gold.objects.all()
else:
objs = Gold.objects.filter(user=user)
for g in objs:
cash_flows = list()
cash_flows.append((g.buy_date, -1*float(g.buy_value)))
lp,ld = get_latest_price(g.buy_type, g.purity)
wt = float(g.weight)
realised_gain = 0
sold_wt = 0
for st in SellTransaction.objects.filter(buy_trans=g):
sold_wt += float(st.weight)
realised_gain += float(st.weight) * (float(st.per_gm) - float(g.per_gm))
cash_flows.append((st.trans_date, float(st.trans_amount)))
unsold_wt = wt - sold_wt
g.unsold_weight = unsold_wt
g.realised_gain = realised_gain
if ld and lp:
g.unrealised_gain = (float(lp) - float(g.per_gm))* unsold_wt
g.as_on_date = ld
g.latest_value = float(lp) * unsold_wt
g.latest_price = float(lp)
if unsold_wt > 0:
cash_flows.append((ld, float(g.latest_value)))
x = xirr(cash_flows, 0.1)*100
print(f'roi: {x}')
g.roi = x
g.save()
def reconcile_401k():
accounts = Account401K.objects.all()
cash_flows = list()
for account in accounts:
total_units = 0
employee_contrib = 0
employer_contrib = 0
latest_date = None
latest_nav = 0
for transaction in Transaction401K.objects.filter(
account=account).order_by('trans_date'):
total_units += transaction.units
employee_contrib += transaction.employee_contribution
employer_contrib += transaction.employer_contribution
if not latest_date:
latest_date = transaction.trans_date
latest_nav = (
transaction.employee_contribution +
transaction.employer_contribution) / transaction.units
else:
if latest_date < transaction.trans_date:
latest_date = transaction.trans_date
latest_nav = (
transaction.employee_contribution +
transaction.employer_contribution) / transaction.units
cash_flows.append((transaction.trans_date,
-1 * float(transaction.employee_contribution +
transaction.employer_contribution)))
account.units = total_units
account.employee_contribution = employee_contrib
account.employer_contribution = employer_contrib
account.total = employee_contrib + employer_contrib
if latest_date:
nav_date, nav_value = get_latest_month_end_nav(account.id)
if nav_date and nav_date > latest_date:
latest_date = nav_date
latest_nav = nav_value
fx = get_conversion_rate('USD', 'INR', latest_date)
account.latest_value = float(latest_nav) * float(
account.units) * fx
account.gain = float(
account.latest_value) - float(account.total) * fx
if len(cash_flows) > 1:
cash_flows.append(
(latest_date, float(latest_nav) * float(account.units)))
roi = xirr(cash_flows, 0.1) * 100
roi = round(roi, 2)
account.roi = roi
else:
account.roi = 0
account.nav = latest_nav
account.nav_date = latest_date
else:
account.latest_value = 0
account.roi = 0
account.nav = 0
account.nav_date = None
account.save()
def calculate_xirr(folios):
current_folio_cash_flows = list()
all_folio_cash_flows = list()
folios_list = list()
for folio in folios:
folios_list.append(folio.folio)
for trans in MutualFundTransaction.objects.all():
if trans.folio.folio in folios_list:
all_folio_cash_flows.append(
(trans.trans_date, float(trans.trans_price) if trans.trans_type
== 'Sell' else float(-1 * trans.trans_price)))
if trans.folio.folio in folios_list and trans.folio.units and trans.folio.units > 0:
current_folio_cash_flows.append(
(trans.trans_date, float(trans.trans_price) if trans.trans_type
== 'Sell' else float(-1 * trans.trans_price)))
latest_value = 0
for folio in folios:
if folio.latest_value and folio.latest_value > 0:
latest_value += float(folio.latest_value)
if latest_value > 0:
all_folio_cash_flows.append((datetime.date.today(), latest_value))
current_folio_cash_flows.append((datetime.date.today(), latest_value))
curr_folio_returns = 0
all_folio_returns = 0
if len(all_folio_cash_flows) > 0:
all_folio_returns = round(xirr(all_folio_cash_flows, 0.1) * 100, 2)
if len(current_folio_cash_flows) > 0:
curr_folio_returns = round(
xirr(current_folio_cash_flows, 0.1) * 100, 2)
print(f'returning {curr_folio_returns}, {all_folio_returns}')
return curr_folio_returns, all_folio_returns
def update_latest_vals(espp_obj):
start = datetime.date.today()+relativedelta(days=-5)
end = datetime.date.today()
sold_units = 0
realised_gain = 0
cash_flows = list()
cash_flows.append((espp_obj.purchase_date, -1*float(espp_obj.total_purchase_price)))
for sell_trans in EsppSellTransactions.objects.filter(espp=espp_obj):
sold_units += sell_trans.units
realised_gain += sell_trans.realised_gain
cash_flows.append((sell_trans.trans_date, float(sell_trans.trans_price)))
try:
_ = Stock.objects.get(exchange=espp_obj.exchange, symbol=espp_obj.symbol)
except Stock.DoesNotExist:
_ = Stock.objects.create(
exchange = espp_obj.exchange,
symbol=espp_obj.symbol,
etf=False,
collection_start_date=datetime.date.today()
)
remaining_units = espp_obj.shares_purchased - sold_units
espp_obj.shares_avail_for_sale = remaining_units
espp_obj.realised_gain = realised_gain
if remaining_units > 0:
vals = get_latest_vals(espp_obj.symbol, espp_obj.exchange, start, end)
print('vals', vals)
if vals:
for k, v in vals.items():
if k and v:
if not espp_obj.as_on_date or k > espp_obj.as_on_date:
espp_obj.as_on_date = k
espp_obj.latest_price = v
if espp_obj.exchange == 'NASDAQ':
espp_obj.latest_conversion_rate = get_conversion_rate('USD', 'INR', k)
else:
espp_obj.latest_conversion_rate = 1
espp_obj.latest_value = float(espp_obj.latest_price) * float(espp_obj.latest_conversion_rate) * float(espp_obj.shares_avail_for_sale)
espp_obj.unrealised_gain = float(espp_obj.latest_value) - (float(espp_obj.purchase_price) * float(espp_obj.latest_conversion_rate) * float(espp_obj.shares_avail_for_sale))
if espp_obj.latest_value and espp_obj.latest_value > 0:
cash_flows.append((datetime.date.today(), float(espp_obj.latest_value)))
x = xirr(cash_flows, 0.1)*100
espp_obj.xirr = x
else:
espp_obj.latest_value = 0
espp_obj.xirr = 0
espp_obj.save()
print('done with update request')
def update_crypto(co, latest_prices=None):
latest_price = None
latest_time = None
if latest_prices:
if co.symbol in latest_prices:
latest_price = latest_prices.get(co.symbol)['price']
latest_time = latest_prices.get(co.symbol)['time']
if not latest_price:
latest_price, latest_time = get_price(co.symbol)
if latest_price:
latest_prices[co.symbol] = {'price': latest_price, 'time': latest_time}
conv_rate = get_in_preferred_currency(1.0, 'USD',
datetime.date.today())
transactions = Transaction.objects.filter(
crypto=co).order_by('trans_date')
qty, buy_value, buy_price, realised_gain, unrealised_gain = reconcile_event_based(
transactions, False, latest_price, conv_rate)
print(
f'qty {qty}, buy_value {buy_value}, buy_price {buy_price}, realised_gain {realised_gain}, unrealised_gain {unrealised_gain} in update_crypto'
)
co.latest_value = float(latest_price) * float(qty) * float(conv_rate)
co.buy_price = buy_price
co.buy_value = buy_value
co.realised_gain = realised_gain
co.unrealised_gain = unrealised_gain
co.units = qty
co.latest_price = latest_price
co.as_on_date = latest_time
cash_flows = list()
for t in transactions:
if t.trans_type == 'Buy':
cash_flows.append((t.trans_date, -1 * float(t.trans_price)))
else:
cash_flows.append((t.trans_date, float(t.trans_price)))
co.save()
if qty > 0:
cash_flows.append((datetime.date.today(), float(co.latest_value)))
try:
roi = xirr(cash_flows, 0.1) * 100
roi = round(roi, 2)
co.xirr = roi
except Exception as ex:
print(f'exception finding xirr {ex}')
co.xirr = 0
co.save()
def get_epf_details(number):
try:
epf_obj = Epf.objects.get(number=number)
total = 0
interest = 0
principal = 0
employer_contrib = 0
employee_contrib = 0
withdrawl = 0
cash_flows = list()
epf_trans = EpfEntry.objects.filter(
epf_id=epf_obj).order_by('trans_date')
for entry in epf_trans:
employer_contrib += entry.employer_contribution
employee_contrib += entry.employee_contribution
interest += entry.interest_contribution
principal += entry.employee_contribution + entry.employer_contribution
cash_flows.append(
(entry.trans_date, -1 * float(entry.employee_contribution +
entry.employer_contribution)))
if entry.withdrawl and entry.withdrawl > 0:
principal -= entry.withdrawl
if principal < 0:
interest += principal
principal = 0
cash_flows.append((entry.trans_date, float(entry.withdrawl)))
withdrawl += entry.withdrawl
total = principal + interest
cash_flows.append((datetime.date.today(), float(total)))
roi = xirr(cash_flows, 0.1) * 100
roi = round(roi, 2)
return {
'number': number,
'total': total,
'employer_contrib': employer_contrib,
'employee_contrib': employee_contrib,
'interest': interest,
'roi': roi,
'withdrawl': withdrawl
}
except Epf.DoesNotExist:
return None
def get_roi(transactions, latest_value):
from shared.financial import xirr
roi = 0
try:
cash_flows = list()
for t in transactions:
if t.trans_type == 'Buy':
cash_flows.append((t.trans_date, -1 * float(t.trans_price)))
else:
cash_flows.append((t.trans_date, float(t.trans_price)))
if latest_value > 0:
cash_flows.append((datetime.date.today(), float(latest_value)))
roi = xirr(cash_flows, 0.1) * 100
roi = round(roi, 2)
except Exception as ex:
print(
f'exception {ex} when getting roi {roi} with cash flows {cash_flows}'
)
roi = 0
return roi
def update_policy_val(policy):
if policy.policy_type != 'ULIP':
return
cash_flows = list()
summary = dict()
for trans in Transaction.objects.filter(policy=policy):
if not trans.fund.name in summary:
summary[trans.fund.name] = dict()
if trans.trans_type == 'Premium':
summary[trans.fund.name]['premium'] = summary[trans.fund.name].get('premium', 0) + trans.trans_amount
summary[trans.fund.name]['units'] = summary[trans.fund.name].get('units', 0) + trans.units
cash_flows.append((trans.trans_date, -1*float(trans.trans_amount)))
if 'nav_date' not in summary[trans.fund.name] or summary[trans.fund.name]['nav_date'] < trans.trans_date:
summary[trans.fund.name]['nav_date'] = trans.trans_date
summary[trans.fund.name]['nav'] = trans.nav
elif trans.trans_type in ['PolicyAdminCharges', 'OtherCharges']:
summary[trans.fund.name]['charges'] = summary[trans.fund.name].get('charges', 0) + trans.trans_amount
elif trans.trans_type in ['CentralGST', 'StateGST', 'OtherTaxes']:
summary[trans.fund.name]['taxes'] = summary[trans.fund.name].get('taxes', 0) + trans.trans_amount
elif trans.trans_type == 'OtherCredits':
summary[trans.fund.name]['units'] = summary[trans.fund.name].get('units', 0) + trans.units
if 'nav_date' not in summary[trans.fund.name] or summary[trans.fund.name]['nav_date'] < trans.trans_date:
summary[trans.fund.name]['nav_date'] = trans.trans_date
summary[trans.fund.name]['nav'] = trans.nav
elif trans.trans_type == 'OtherDeductions':
summary[trans.fund.name]['units'] = summary[trans.fund.name].get('units', 0) + trans.units
if 'nav_date' not in summary[trans.fund.name] or summary[trans.fund.name]['nav_date'] < trans.trans_date:
summary[trans.fund.name]['nav_date'] = trans.trans_date
summary[trans.fund.name]['nav'] = trans.nav
elif trans.trans_type == 'MortalityCharges':
summary[trans.fund.name]['mc'] = summary[trans.fund.name].get('mc', 0) + trans.trans_amount
print(summary)
for fund_name, summ in summary.items():
f = Fund.objects.get(policy=policy, name=fund_name)
update_fund(policy, f, summ)
as_on_date = None
premium = 0
latest_value = 0
mc = 0
taxes = 0
charges = 0
for fund in Fund.objects.filter(policy=policy):
if not as_on_date:
as_on_date = fund.nav_date
elif as_on_date > fund.nav_date:
as_on_date = fund.nav_date
premium += float(summary[fund.name]['premium'])
latest_value += float(fund.nav*fund.units)
mc += float(summary[fund.name].get('mc', 0))
taxes += float(summary[fund.name].get('taxes', 0))
charges += float(summary[fund.name].get('charges', 0))
gain = latest_value - premium
policy.gain = gain
policy.as_on_date = as_on_date
policy.buy_value = premium
policy.latest_value = latest_value
cash_flows.append((policy.as_on_date, latest_value))
print(f'cash flows for {policy.policy} {cash_flows}')
roi = xirr(cash_flows, 0.1)*100
policy.roi = roi
policy.mortality_charges = mc
policy.taxes = taxes
policy.charges = charges
policy.save()
def get_context_data(self, **kwargs):
data = super().get_context_data(**kwargs)
print(data)
data['goal_str'] = get_goal_name_from_id(data['object'].goal)
data['user_str'] = get_user_name_from_id(data['object'].user)
obj = data['object']
cash_flows = list()
cash_flows.append(
(obj.purchase_date, -1 * float(obj.total_purchase_price)))
for st in EsppSellTransactions.objects.filter(espp=data['object']):
cash_flows.append((st.trans_date, float(st.trans_price)))
if float(obj.latest_value) > 0:
cash_flows.append((obj.as_on_date, float(obj.latest_value)))
roi = xirr(cash_flows, 0.1) * 100
roi = round(roi, 2)
data['roi'] = roi
data['curr_module_id'] = 'id_espp_module'
'''
data['transactions'] = list()
for st in EsppSellTransactions.objects.filter(espp=data['object']):
data['transactions'].append({
'id':st.id,
'trans_date':st.trans_date,
'price':st.price,
'units':st.units,
'conversion_rate':st.conversion_rate,
'trans_price':st.trans_price,
'realised_gain':st.realised_gain,
'notes':st.notes
})
'''
std = obj.purchase_date
today = datetime.date.today()
r = lambda: random.randint(0, 255)
color = '#{:02x}{:02x}{:02x}'.format(r(), r(), r())
ret = list()
ret.append({
'label': '',
'data': list(),
'fill': 'false',
'borderColor': color
})
ret[0]['data'].append({
'x': obj.purchase_date.strftime('%Y-%m-%d'),
'y': round(float(obj.total_purchase_price), 2)
})
std = std + relativedelta(months=1)
if std > today:
std = today
else:
std = std.replace(day=1)
reset_to_zero = False
while True:
val = 0
trans = list()
trans.append(
Trans(obj.shares_purchased, obj.purchase_date, 'buy',
obj.total_purchase_price))
for st in EsppSellTransactions.objects.filter(espp=obj,
trans_date__lte=std):
trans.append(
Trans(st.units, st.trans_date, 'sell', st.trans_price))
q, _, _, _, _ = reconcile_event_based(trans, list(), list())
lv = get_historical_stock_price_based_on_symbol(
obj.symbol, obj.exchange, std + relativedelta(days=-5), std)
if lv:
print(lv)
conv_rate = 1
if obj.exchange == 'NASDAQ' or obj.exchange == 'NYSE':
conv_val = get_conversion_rate('USD', 'INR', std)
if conv_val:
conv_rate = conv_val
for k, v in lv.items():
val += float(v) * float(conv_rate) * float(q)
break
else:
print(
f'failed to get value of {obj.exchange}:{obj.symbol} on {std}'
)
if val > 0 or reset_to_zero:
x = std.strftime('%Y-%m-%d')
ret[0]['data'].append({'x': x, 'y': round(val, 2)})
if val > 0:
reset_to_zero = True
else:
reset_to_zero = False
if std == today:
break
std = std + relativedelta(months=1)
if std > today:
std = today
data['progress_data'] = ret
try:
s = Stock.objects.get(symbol=obj.symbol, exchange=obj.exchange)
last_date = datetime.date.today()
if obj.shares_avail_for_sale == 0:
all_sell = EsppSellTransactions.objects.filter(
espp=obj).order_by('trans_date')
last_date = all_sell[len(all_sell) - 1].trans_date
res = get_comp_index_values(s, obj.purchase_date, last_date)
if 'chart_labels' in res and len(res['chart_labels']) > 0:
for k, v in res.items():
data[k] = v
except Stock.DoesNotExist:
print(
f'trying to get stock that does not exist {obj.symbol} {obj.exchange}'
)
return data