1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
from analytics.cms_spread import CmsSpread, _dawndb
from db import dbconn
import datetime
from pathlib import Path
from quantlib.time.api import Date, Days, UnitedStates
from scipy.optimize import brentq
import os
import pandas as pd
import re
DAILY_DIR = Path(os.environ["DAILY_DIR"])
def lmcg_navs(trade_id):
r = []
today = pd.Timestamp.today()
trade = CmsSpread.from_tradeid(trade_id)
trade.corr = 0.72
dr = pd.bdate_range(trade.value_date, today, closed="left", normalize=True)
for d in dr:
trade.value_date = d
r.append(trade.pv)
return pd.Series(r, dr)
def gs_navs(trade_id, orig_nav):
# load gs navs for a given trade
dates = []
r = []
cal = UnitedStates()
for fname in (DAILY_DIR / "GS_reports").glob("Trade_Detail*.xls"):
df = pd.read_excel(fname, skiprows=9, skipfooter=77)
df = df.set_index('Trade Id')
try:
npv = df.loc[trade_id, 'NPV (USD)']
trade_date = Date.from_datetime(pd.Timestamp(df.loc[trade_id, 'Trade Date']))
except KeyError:
continue
else:
m = re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", fname.name)
if m:
date_string, = m.groups()
date = datetime.datetime.strptime(date_string, "%d_%b_%Y")
current_date = Date.from_datetime(date)
if cal.advance(trade_date, 2, Days) > current_date:
npv -= orig_nav
dates.append(date)
r.append(npv)
s = pd.Series(r, dates)
s = s.sort_index()
return -s
def ms_navs(trade_id="JWY3N"):
dates = []
r = []
for fname in (DAILY_DIR / "MS_reports").glob("Trade_Detail*.xls"):
df = pd.read_excel(fname)
try:
r.append(df.set_index('trade_id').loc[trade_id, 'exposure_in_rpt_ccy'])
except KeyError:
continue
else:
m = re.match(r"[^\d]*(\d{8})", fname.name)
if m:
date_string, = m.groups()
dates.append(datetime.datetime.strptime(date_string, "%Y%m%d"))
s = pd.Series(r, dates)
s = s.sort_index()
s[:2] += 105_000
return s
if __name__ == "__main__":
with _dawndb.cursor() as c:
c.execute("SELECT id, cpty_id, amount * premium_percent / 100 AS orig_nav "
"FROM capfloors WHERE cp_code='GOLDNY'")
gs_mapping = {r['id']: (r['cpty_id'], r['orig_nav']) for r in c}
# s1 = lmcg_navs(3)
# s2 = gs_navs(d[3])
# df = pd.concat([s1, s2], axis=1)
# s2 = lmcg_navs(4)
# s3 = ms_navs()
# df = pd.concat([s2, s3], axis=1)
# s = gs_navs()
# s2 = lmcg_navs(1)
# df = pd.concat([s, s2], axis=1)
# for gs_id in gs_mapping.values():
# s = gs_navs(gs_id)
# with dbconn('dawndb') as conn:
# with conn.cursor() as c:
# for d, val in s.items():
# c.execute("INSERT INTO external_marks_deriv "
# "VALUES(%s, %s, %s)",
# (d, gs_id, val))
# compute implied correlation from external mark
s = gs_navs(*gs_mapping[1])
trade = CmsSpread.from_tradeid(1)
def myfun(corr, args):
trade.corr = corr
return trade.pv - args
rho = []
for d in s.index:
trade.value_date = d
x0 = brentq(myfun, 0.5, 0.99, (s[d],))
rho.append(x0)
pd.Series(rho, s.index).plot()
|
