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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
{
"metadata": {
"name": "Load Intex Data"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Preamble"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In everything that follows the paths are relative from\n",
"our directory ``//WDSENTINEL/share/CorpCDOs`` on Windows and\n",
"``/home/share/CorpCDOs`` on Linux."
]
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Load data from intex and populate the database"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"we need to fill up three tables:\n",
"\n",
"* clo_universe\n",
"* cusip_universe\n",
"* et_collateral\n",
"\n",
"We first create a directory called ``data/Indicative_%Y-%m-%d``.\n",
"From the spreadsheet, this is driven by the two shortcuts\n",
"``CTRL+Shift+Y`` and ``CTRL+Shift+G``.\n",
"\n",
"* First select the cusips of interest, do ``CTRL+Shift+Y`` and paste them\n",
" into a new portfolio tab in intex. On the portfolio tab itself,\n",
" right click, then Export to text, and save into ``data/Indicative_%Y-%m-%d``.\n",
" The intex filename should be called ``Portfolio.txt`` or ``Portfolio(i).txt``\n",
" for some ``i``.\n",
"* Then in the Collat Detail tab, make sure Asset Detail is\n",
" toggled. Right click, then Export to text, All bonds and extract the\n",
" resulting zip file into a folder named ``data/Collaterals_%Y-%m-%d``\n",
"* Last step, select the cusips again, do ``CTRL+SHIFT+G``, paste into a\n",
" new portfolio tab. Click on All Tranches, Right Click, Export to\n",
" text, and save the file into a folder named ``data/Indicative_%Y-%m-%d``.\n",
" The file name should be called ``Portfolio_TrInfo.txt`` or\n",
" ``Portfolio_TrInfo(i).txt`` for some ``i``.\n",
"\n",
"We're done exporting the data from intex, now we need to load it into\n",
"the database. We need to run 2 scripts. To do so, after connecting to\n",
"the remote server, at the prompt do:\n",
"\n",
"$$c = \\sqrt{a^2 + b^2}$$\n"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"%run load_indicative.py"
],
"language": "python",
"metadata": {},
"outputs": [
{
"ename": "InterfaceError",
"evalue": "connection already closed",
"output_type": "pyerr",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mInterfaceError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m/usr/lib/python3/dist-packages/IPython/utils/py3compat.py\u001b[0m in \u001b[0;36mexecfile\u001b[0;34m(fname, glob, loc)\u001b[0m\n\u001b[1;32m 74\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mexecfile\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mglob\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mloc\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 75\u001b[0m \u001b[0mloc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mloc\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mloc\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mglob\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 76\u001b[0;31m \u001b[0mexec\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcompile\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'rb'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'exec'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mglob\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mloc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 77\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 78\u001b[0m \u001b[0;31m# Refactor print statements in doctests.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m/home/share/CorpCDOs/code/python/load_indicative.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m 164\u001b[0m \u001b[0;31m#first load deal data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 165\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mdeal\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdeal_files\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 166\u001b[0;31m \u001b[0mupload_deal_data\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdeal\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 167\u001b[0m \u001b[0;31m#then load tranche data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 168\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mcusip\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcusip_files\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;32m/home/share/CorpCDOs/code/python/db.py\u001b[0m in \u001b[0;36mwith_connection_\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m 12\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 13\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 14\u001b[0;31m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrollback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 15\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 16\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mrv\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mInterfaceError\u001b[0m: connection already closed"
]
},
{
"output_type": "stream",
"stream": "stdout",
"text": [
"connection already closed\n",
"connection already closed\n"
]
}
],
"prompt_number": 12
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"%run load_intex_collateral.py"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"done\n"
]
}
],
"prompt_number": 11
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Steps to build the model"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* We need to have a monitor running that will trigger the scenarios\n",
" generations for us.\n",
"\n",
" cd /home/share/CorpCDOs/scripts\n",
" bash monitor.sh\n",
"\n",
" **Note** need to set it up as a daemon so that it get automatically\n",
" started at boot.\n",
"* After that, scenarios generation can be triggered by selecting the\n",
" cusips in the excel spreadsheet and using the ``CTRL+Shift+H``\n",
" shortcut.\n",
"* To disable Reinvestment put \"Y\" under \"DisableReinv\" column before\n",
" using ``CTRL+Shift+H``.\n",
"* htop to monitor server activities.\n",
"* This will trigger three scripts: first it will build the survival\n",
" curves: these are saved into ``Scenarios/Portfolios_%Y_%m_%d``,\n",
" second it will generate the loss and prepay distributions and save\n",
" them into ``Scenarios/Intex curves_%Y-%m%d/csv``, and finally\n",
" generate the sss files that we can load into intex in\n",
" ``Scenarios/Intex curves_%Y-%m%d/sss``.\n",
"* You can look at the logs file in ``scripts/logs``\n",
" to monitor the process."
]
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Running the scenarios in Intex"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* First make sure that Intex look for the sss files in the right\n",
" directory. Go into Tools, Options, Data Access and change it\n",
" appropriately. (e.g. Z:\\CorpCDOs\\Scenarios\\Intex\n",
" curves_2013-07-22\\sss)\n",
"* In Intex Options, under Miscellaneous, Change the export settings to\n",
" \"Tab delimited\"\n",
"* Select the cusips of interest on the spreadsheet (make sure the\n",
" corresponding sss files exist), do ``CTRL+Shift+I`` and paste them\n",
" into a new portfolio tab in Intex... twice so that Intex actually\n",
" paste the data.\n",
"* Then go onto the Scenarios Tab, and click the cross next to Rate\n",
" Update to make sure it reloads the Interest Rates (otherwise intex\n",
" will run it with libor set at 0... not good)\n",
"* Then go onto the Cashflows tab and wait... Once it's done, export\n",
" the cashflows into ``Scenarios/Prices_%Y-%m%-%d``.\n",
"* Finally, on the server do:\n",
"\n",
" cd /home/share/CorpCDOs/code/R\n",
" Rscript load_cf.R\n",
"\n",
" This will load the cashflows and compute the deltas, and save it in\n",
" the database.\n",
"* Enjoy your work by typing ``CTRL+T`` on the spreadsheet and see how\n",
" all the columns get filled. Yay!"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [],
"language": "python",
"metadata": {},
"outputs": []
}
],
"metadata": {}
}
]
}
|
