def build_libor(df_liquidity, df_libor_all, forecast_start, valuation_date):
libor_list = []
for index, row in df_liquidity.iterrows():
index = index.date()
if index == date_utils.calc_month_end(forecast_start, 'date'):
''' first month does not use forward curve '''
libor = get_smoothed_libor_spot_curve(df_libor_all, index,
valuation_date)
# sys.exit();
# libor = get_libor(df_libor_all, index)
libor_list.append([index, libor])
# df_liquidity.set_value(index, ['LIBOR'], libor)
elif index > date_utils.calc_month_end(forecast_start, 'date'):
"""
the libor in kean is already forward rates, we dont do calculation anymore
"""
libor = get_smoothed_libor_spot_curve(df_libor_all, index,
valuation_date)
# df_liquidity.set_value(index, ['LIBOR'], libor)
libor_list.append([index, libor])
for item in libor_list:
# print (item[1]);
df_liquidity.set_value(item[0], ['LIBOR'], item[1])
return df_liquidity
def build_tlb_int_exp(df_liquidity, forecast_start):
start = date_utils.calc_month_end(forecast_start, 'date')
df_liquidity.loc[start:,['TLB Int Exp']] = df_liquidity['Swap Avg Daily Balance'] * df_liquidity['Swap Fix Rate'] \
+ (df_liquidity['TLB BOP']- df_liquidity['Swap Avg Daily Balance']) * df_liquidity['TLB Float Rate']
df_liquidity.loc[start:, ['TLB Int Exp']] = df_liquidity[
'TLB Int Exp'] * df_liquidity.index.day / YEAR_DAYS
return df_liquidity
def process_hedge(conn_ins, company, scenario, upload_to_kean=False):
''' Need to develop flexible means of accomodating different instrument_id '''
''' e.g. AD Hub DA ATC vs AD Hub RT ATC vs AD Hub ATC - Monthly Strip '''
month_number = date_utils.get_month_number(scenario.split(" ")[1]);
month_number += 1;
forecast_start = date(2017, month_number, 1)
forecast_end = date(2021, 12, 31)
df_hedges = db_controller_hedge.get_hedges(conn_ins, company, 'HoldCo', forecast_start, forecast_end)
df_hedges = df_hedges.loc[df_hedges['trade_date']<forecast_start];
df_all_prices = db_controller_hedge.get_all_commodity_prices(conn_ins, scenario)
df_hours_pjm = db_controller_hedge.get_hours_pjm(conn_ins);
''' write results to financials '''
upload_to_financials_hedge_list = [];
loop_date = date_utils.calc_month_end(forecast_start, 'date')
while loop_date <= forecast_end:
sum_cashflow = 0.0
for i, hedge in df_hedges.iterrows():
if hedge.loc['date_start'] <= loop_date and hedge.loc['date_end'] >= loop_date:
# print (hedge.loc['receive_index']);
price_receive = hedge.loc['receive_value'] if hedge.loc['receive_index'] == 'fixed' else get_specific_price(df_all_prices, hedge.loc['receive_index'], loop_date)
price_pay = hedge.loc['pay_value'] if hedge.loc['pay_index'] == 'fixed' else get_specific_price(df_all_prices, hedge.loc['pay_index'], loop_date)
monthly_cashflow = 0.0;
if hedge.loc['pay_index'] == 'AD Hub DA Peak':
monthly_cashflow = calc_monthly_cashflow(price_receive, price_pay, hedge.loc['notional'], hedge.loc['frequency'], loop_date, df_hours_pjm, option = True)
else:
monthly_cashflow = calc_monthly_cashflow(price_receive, price_pay, hedge.loc['notional'], hedge.loc['frequency'], loop_date, df_hours_pjm)
sum_cashflow += monthly_cashflow
# print (hedge.loc['tid_kindle'], loop_date, monthly_cashflow, ' ' ,sum_cashflow);
# print ("--------------------------------------------");
"""
we can use db_controller to do the upload
we should build the upload list to store hedge values
"""
#load_hedges_financials('2017 June AMR', 'Lightstone', 'HoldCo', 'Hedge P&L', loop_date, '%.2f' % sum_cashflow)
""" (company, entity, scenario, account, period, value, version) """
upload_to_financials_hedge_list.append([company, 'HoldCo', scenario, 'Hedge P&L', str(loop_date), str('%.2f' % sum_cashflow)]);
loop_date = date_utils.calc_next_month_end(loop_date, 'date')
for item in upload_to_financials_hedge_list:
print (item);
if upload_to_kean:
db_controller.upload_cal_results_to_financials(conn_ins, upload_to_financials_hedge_list, company, scenario);
return df_hedges, df_all_prices, df_hours_pjm;
def build_change_work_cap(df_liquidity, forecast_start):
for index, row in df_liquidity.iterrows():
if date(index.year, index.month, index.day) < forecast_start:
change_cash = row['Cash EOP'] - row['Cash BOP']
activity_cash = row['EBITDA'] - row['Capex'] + row[
'Other Cash Use'] - row['TLB Int Exp'] - row[
'TLC Int Exp'] + row['TLB Change'] + row[
'DSRA Change'] - row['Distributions']
change_work_cap = change_cash - activity_cash
elif date(index.year, index.month,
index.day) == date_utils.calc_month_end(
forecast_start, 'date'):
change_work_cap = -df_liquidity.loc[:forecast_start,
['Change Work Cap']].sum(
) - 20000000
else:
change_work_cap = 0
df_liquidity.set_value(index, 'Change Work Cap', change_work_cap)
return df_liquidity
def build_swap_rate(df_liquidity, df_swap, forecast_start):
for index, row in df_liquidity.iterrows():
if date(index.year, index.month,
index.day) >= date_utils.calc_month_end(
forecast_start, 'date'):
month_start = date(index.year, index.month, 1)
month_end = date(index.year, index.month, index.day)
swap_balance = df_swap[month_start:month_end]['swap_balance'].sum()
if swap_balance > 0:
df_liquidity.set_value(index, ['Swap Avg Daily Balance'],
swap_balance / index.day)
swap_rate = df_swap[month_start:month_end][
'calc_swap_rate'].sum() / swap_balance
df_liquidity.set_value(index, ['Swap Fix Rate'],
swap_rate + .045000)
else:
df_liquidity.set_value(index, ['Swap Avg Daily Balance'], 0)
df_liquidity.set_value(index, ['Swap Fix Rate'], 0)
return df_liquidity
def fill_in_cells_hedges(new_file_path, df_hedges, df_all_prices, df_hours_pjm,
scenario):
wb = load_workbook(new_file_path)
ws = wb['hedges']
img = openpyxl.drawing.image.Image(LOGO_PATH)
img.anchor(ws['A1'])
ws.add_image(img)
forecast_start = date(
int(scenario.split(" ")[0]),
date_utils.get_month_number(scenario.split(" ")[1]) + 1, 1)
# print (forecast_start);
# month_header_list = date_utils.get_month_dates_list(forecast_start.year, forecast_start.month, 12);
# print (month_header_list);
start_col = 9
for item in range(forecast_start.month, 13):
ws.cell(
row=4,
column=start_col).value = date_utils.calc_forecast_monthly_headers(
forecast_start.year, item)
start_col += 1
ws.cell(row=4, column=start_col).value = '2018+'
forecast_end = date(int(scenario.split(" ")[0]) + 4, 12, 31)
# print (forecast_end);
valuation_date = date(int(scenario.split(" ")[0]),
date_utils.get_month_number(scenario.split(" ")[1]),
28)
loop_rows = 1
remaining_column = 8 + (13 - forecast_start.month) + 1
data_format = NamedStyle(name='data', number_format='#,##0_);(#,##0)')
df_hedges_relevant = df_hedges[df_hedges['trade_date'] < forecast_start]
df_hedges_relevant = df_hedges_relevant.reset_index(drop=True)
for i, hedge in df_hedges_relevant.iterrows():
ws.cell(row=i + 5, column=1, value=hedge.loc['tid_kindle'])
ws.cell(row=i + 5, column=2, value=hedge.loc['counterparty'])
instrument = hedge.loc['receive_index'] + "/" + hedge.loc['pay_index']
ws.cell(row=i + 5, column=3, value=instrument)
ws.cell(row=i + 5, column=4, value=hedge.loc['notional'])
fixed_price = hedge.loc['receive_value'] if hedge.loc[
'receive_index'] == 'fixed' else hedge.loc['pay_value']
ws.cell(row=i + 5, column=5, value=fixed_price)
ws.cell(row=i + 5, column=6, value=hedge.loc['frequency'])
ws.cell(row=i + 5, column=7, value=hedge.loc['date_start'])
ws.cell(row=i + 5, column=8, value=hedge.loc['date_end'])
"""show monthly results for remainder of forecast year, then sum remaining into one column"""
loop_date = date_utils.calc_month_end(forecast_start, 'date')
month_offset = 0
remaining_cashflow = 0
while loop_date <= forecast_end:
if hedge.loc['date_start'] <= loop_date and hedge.loc[
'date_end'] >= loop_date:
price_receive = hedge.loc['receive_value'] if hedge.loc[
'receive_index'] == 'fixed' else get_specific_price(
df_all_prices, hedge.loc['receive_index'], loop_date)
price_pay = hedge.loc['pay_value'] if hedge.loc[
'pay_index'] == 'fixed' else get_specific_price(
df_all_prices, hedge.loc['pay_index'], loop_date)
monthly_cashflow = 0.0
if hedge.loc['pay_index'] == 'AD Hub DA Peak':
monthly_cashflow = calc_monthly_cashflow(
price_receive,
price_pay,
hedge.loc['notional'],
hedge.loc['frequency'],
loop_date,
df_hours_pjm,
option=True)
else:
monthly_cashflow = calc_monthly_cashflow(
price_receive, price_pay, hedge.loc['notional'],
hedge.loc['frequency'], loop_date, df_hours_pjm)
if loop_date.year == forecast_start.year:
ws.cell(row=i + 5,
column=9 + month_offset,
value=monthly_cashflow)
ws.cell(row=i + 5,
column=9 + month_offset).style = data_format
else:
remaining_cashflow += monthly_cashflow
if loop_date > hedge.loc['date_end']:
ws.cell(row=i + 5,
column=remaining_column,
value=remaining_cashflow)
ws.cell(row=i + 5, column=remaining_column).style = data_format
break
month_offset += 1
loop_date = date_utils.calc_next_month_end(loop_date, 'date')
loop_rows += 1
''' add totals row '''
ws.cell(row=loop_rows + 4, column=1, value='Total')
for i in range(9, remaining_column + 1):
formula = '=SUM(' + chr(96 + i) + str(1 + 4) + ':' + chr(96 + i) + str(
loop_rows + 4 - 1) + ')'
ws.cell(row=loop_rows + 4, column=i, value=formula)
ws.cell(row=loop_rows + 4, column=i).style = data_format
''' Add bold font and grey fill to total row '''
for i in range(1, remaining_column + 1):
current_cell = chr(96 + i) + str(loop_rows + 4)
ws[current_cell].fill = PatternFill(start_color='FFD9D9D9',
end_color='FFD9D9D9',
fill_type='solid')
ws[current_cell].font = Font(bold=True)
''' Add underline to report title '''
for i in range(1, remaining_column + 1):
current_cell = chr(96 + i) + str(1)
ws[current_cell].border = Border(bottom=Side(style='thick'))
label = 'Pricing Date: ' + valuation_date.strftime('%m/%d/%Y')
ws.cell(row=1, column=remaining_column - 1, value=scenario)
ws.cell(row=loop_rows + 7, column=1, value=label)
wb.save(new_file_path)
def build_tlc_int_exp(df_liquidity, forecast_start):
start = date_utils.calc_month_end(forecast_start, 'date')
df_liquidity.loc[start:,
['TLC Int Exp']] = df_liquidity['TLC BOP'] * df_liquidity[
'TLC Float Rate'] * df_liquidity.index.day / YEAR_DAYS
def build_debt(conn_ins, df, forecast_start, forecast_end):
''' set initial debt balances '''
tlb_bop = db_controller_liquidity.get_inception_debt_balance(
conn_ins, 'Lightstone TLB')
tlc_bop = db_controller_liquidity.get_inception_debt_balance(
conn_ins, 'Lightstone TLC')
df_debt_activity_tlb = db_controller_liquidity.get_debt_activity_all(
conn_ins, 'Lightstone TLB')
df_debt_activity_tlb['payment_date_date'] = pd.to_datetime(
df_debt_activity_tlb['payment_date'])
df_debt_activity_tlc = db_controller_liquidity.get_debt_activity_all(
conn_ins, 'Lightstone TLC')
df_debt_activity_tlc['payment_date_date'] = pd.to_datetime(
df_debt_activity_tlc['payment_date'])
last_activity_date_tlb = date_utils.calc_month_end(
df_debt_activity_tlb['payment_date_date'].max(), 'date')
last_activity_date_tlc = date_utils.calc_month_end(
df_debt_activity_tlc['payment_date_date'].max(), 'date')
for index, row in df.iterrows():
''' tlb first '''
tlb_addl_borrow = 0
tlb_prepay = 0
tlb_amort = 0
if date(index.year, index.month, index.day) <= max(
last_activity_date_tlb, forecast_start):
df.set_value(index, 'TLB BOP', tlb_bop)
tlb_addl_borrow = get_debt_activity(df_debt_activity_tlb,
'additional borrowing', index)
df.set_value(index, 'TLB Addl Borrow', tlb_addl_borrow)
tlb_amort = get_debt_activity(df_debt_activity_tlb, 'amortization',
index)
df.set_value(index, 'TLB Amort', tlb_amort)
tlb_prepay = get_debt_activity(df_debt_activity_tlb, 'prepayment',
index)
df.set_value(index, 'TLB Prepay', tlb_prepay)
tlb_eop = tlb_bop + tlb_addl_borrow - tlb_amort - tlb_prepay
df.set_value(index, 'TLB EOP', tlb_eop)
tlb_bop = tlb_eop
''' tlc second '''
if date(index.year, index.month, index.day) < max(
last_activity_date_tlc, forecast_start):
df.set_value(index, 'TLC BOP', tlc_bop)
tlc_addl_borrow = get_debt_activity(df_debt_activity_tlc,
'additional borrowing', index)
df.set_value(index, 'TLC Addl Borrow', tlc_addl_borrow)
tlc_amort = get_debt_activity(df_debt_activity_tlc, 'amortization',
index)
df.set_value(index, 'TLC Amort', tlc_amort)
tlc_prepay = get_debt_activity(df_debt_activity_tlc, 'prepayment',
index)
df.set_value(index, 'TLC Prepay', tlc_prepay)
tlc_eop = tlc_bop + tlc_addl_borrow - tlc_amort - tlc_prepay
df.set_value(index, 'TLC EOP', tlc_eop)
tlc_bop = tlc_eop
''' forecast period '''
if date(index.year, index.month, index.day) >= forecast_start:
# print (index, row['TLB BOP']);
if row['TLB BOP'] <= 0:
''' calc amortization '''
if index.month % 3 == 0:
required_amort = calc_required_amort(
df, 'Lightstone TLB', index)
cum_prepay = df.loc[:index]['TLB Prepay'].sum()
cum_amort = df.loc[:index]['TLB Amort'].sum()
tlb_amort = max(
min(1625000000 * 0.0025,
required_amort - cum_prepay - cum_amort), 0)
df.set_value(index, ['TLB Amort'], tlb_amort)
df.set_value(index, 'TLB BOP', tlb_bop)
tlb_eop = tlb_bop + tlb_addl_borrow - tlb_amort - tlb_prepay
df.set_value(index, 'TLB EOP', tlb_bop - tlb_amort)
tlb_bop = tlb_eop
if row['TLC BOP'] <= 0:
df.set_value(index, 'TLC BOP', tlc_bop)
tlc_eop = tlc_bop + tlc_addl_borrow - tlc_amort - tlc_prepay
df.set_value(index, 'TLC EOP', tlc_bop)
tlc_bop = tlc_eop
return df
def get_debt_activity(df, activity, period):
month_start = date(period.year, period.month, 1)
month_end = date_utils.calc_month_end(period, 'date')
mask = (df['payment_date'] >= month_start) & (
df['payment_date'] <= month_end) & (df['payment_type'] == activity)
return df[mask]['amount'].sum()