diff options
Diffstat (limited to 'python/task_server')
| -rw-r--r-- | python/task_server/globeop.py | 2 | ||||
| -rw-r--r-- | python/task_server/insert_tranche_quotes.py | 188 |
2 files changed, 189 insertions, 1 deletions
diff --git a/python/task_server/globeop.py b/python/task_server/globeop.py index 61471b4b..afd6a194 100644 --- a/python/task_server/globeop.py +++ b/python/task_server/globeop.py @@ -2,7 +2,7 @@ import os import os.path
from ftplib import FTP
import gnupg
-import config
+import task_server.config
import re
import logging
import argparse
diff --git a/python/task_server/insert_tranche_quotes.py b/python/task_server/insert_tranche_quotes.py new file mode 100644 index 00000000..251d60bf --- /dev/null +++ b/python/task_server/insert_tranche_quotes.py @@ -0,0 +1,188 @@ +from sqlalchemy import Table, create_engine, MetaData
+from sqlalchemy.exc import IntegrityError, DataError
+import os
+import csv
+import datetime
+import logging
+
+engine = create_engine('postgresql://serenitas_user@debian/serenitasdb')
+metadata = MetaData(bind = engine)
+quotes = Table('tranche_quotes', metadata, autoload = True)
+ins = quotes.insert()
+
+def convert(x):
+ try:
+ return float(x)
+ except ValueError:
+ return None
+
+tenordict = {'3': '3yr', '5': '5yr', '7':'7yr', '10':'10yr'}
+runningdict1 = {0: 500, 3:100, 7:100, 15: 25}
+runningdict2 = {0: 500, 3:500, 7:500, 10:100, 15:100, 30:100}
+
+def insert_quotes(year=2015, quote_dir=None):
+ if not quote_dir:
+ root_dir = '/home/share/CorpCDOs'
+ quote_dir = os.path.join(root_dir, 'Tranche_data', 'Quotes')
+ quotefiles = [f for f in os.listdir(quote_dir) if f.endswith('csv')]
+ for quotefile in quotefiles:
+ with open(os.path.join(quote_dir, quotefile)) as fh:
+ reader = csv.DictReader(fh)
+ data = []
+ for csvdict in reader:
+ try:
+ timestamp = datetime.datetime.strptime(csvdict['Date'], "%d %b %Y %H:%M")
+ except ValueError:
+ logging.error("%s: Date in the wrong format", csvdict['Date'])
+ return
+
+ attach = int(csvdict['Attach'])
+ series = int(csvdict['Series'])
+ version = int(csvdict['Version'])
+ if csvdict['Ref']=='': #no delta quote maybe
+ continue
+
+ #common values to all indices
+ try:
+ d = {'quotedate' : timestamp,
+ 'tranchedelta': convert(csvdict['Delta']),
+ 'quotesource' : csvdict['Source'],
+ 'series': series,
+ 'version': version,
+ 'attach': attach,
+ 'detach': int(csvdict['Detach']),
+ 'tenor': tenordict[csvdict['Tenor']]
+ }
+ except KeyError:
+ continue
+
+ if csvdict['Ticker'] == 'CDX-NAHY':
+ trancheupfrontbid = convert(csvdict['Price Bid'])
+ trancheupfrontask = convert(csvdict['Price Ask'])
+
+ try:
+ trancheupfrontmid = (trancheupfrontbid+trancheupfrontask)/2
+ except TypeError:
+ trancheupfrontmid = None
+ running = 0 if series in [9, 10] and attach==10 else 500
+ d.update({'indexrefprice': float(csvdict['Ref']),
+ 'indexrefspread': 375 if series==9 else 500,
+ 'trancheupfrontbid' : trancheupfrontbid,
+ 'trancheupfrontmid' : trancheupfrontmid,
+ 'trancheupfrontask' : trancheupfrontask,
+ 'trancherunningbid' : running,
+ 'trancherunningmid' : running,
+ 'trancherunningask' : running,
+ 'index': 'HY'
+ })
+
+ elif csvdict['Ticker'] == 'CDX-NAIG':
+
+ if series>=26:
+ continue
+ trancheupfrontbid = convert(csvdict['Upfront Bid'])
+ trancheupfrontask = convert(csvdict['Upfront Ask'])
+ try:
+ trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2
+ except TypeError:
+ trancheupfrontmid = None
+ if series==25:
+ running = 100
+ else:
+ running = runningdict2[attach] if series<19 else runningdict1[attach]
+ try:
+ d.update({'indexrefspread': int(float(csvdict['Ref'])),
+ 'trancheupfrontbid' : trancheupfrontbid,
+ 'trancheupfrontmid' : trancheupfrontmid,
+ 'trancheupfrontask' : trancheupfrontask,
+ 'trancherunningbid' : running,
+ 'trancherunningmid' : running,
+ 'trancherunningask' : running,
+ 'index' : 'IG'})
+ except KeyError:
+ continue
+ elif csvdict['Ticker'] == 'ITRAXX-Europe':
+ if series<=15:
+ if attach<=6:
+ trancherunningbid = trancherunningmid = trancherunningask = 500 if attach<=3 else 300
+ trancheupfrontbid = convert(csvdict['Upfront Bid'])
+ trancheupfrontask = convert(csvdict['Upfront Ask'])
+ try:
+ trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2
+ except TypeError:
+ trancheupfrontmid = None
+ else:
+ trancherunningbid = convert(csvdict['Bid'])
+ trancherunningask = convert(csvdict['Ask'])
+ try:
+ trancherunningmid = (trancherunningbid+trancherunningask)/2
+ except TypeError:
+ trancherunning = None
+ trancheupfrontbid = trancheupfrontmid = trancheupfrontask = 0
+ if series in [19, 21, 22, 24]:
+ if attach<=3:
+ trancherunningbid = trancherunningask = trancherunningmid = 500 if series==19 else 100
+ trancheupfrontbid = convert(csvdict['Upfront Bid'])
+ trancheupfrontask = convert(csvdict['Upfront Ask'])
+ try:
+ trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2
+ except TypeError:
+ trancheupfrontmid = None
+ else:
+ trancherunningbid = convert(csvdict['Bid'])
+ trancherunningask = convert(csvdict['Ask'])
+ try:
+ trancherunningmid = (trancherunningbid+trancherunningask)/2
+ except TypeError:
+ trancherunning = None
+ trancheupfrontbid = trancheupfrontmid = trancheupfrontask = 0
+
+ d.update({'indexrefspread': int(float(csvdict['Ref'])),
+ 'trancheupfrontbid' : trancheupfrontbid,
+ 'trancheupfrontmid' : trancheupfrontmid,
+ 'trancheupfrontask' : trancheupfrontask,
+ 'trancherunningbid' : trancherunningbid,
+ 'trancherunningmid' : trancherunningmid,
+ 'trancherunningask' : trancherunningask,
+ 'index' : 'EU'})
+ elif csvdict['Ticker'] == 'ITRAXX-Xover':
+ if attach<35:
+ ## the quote is sometimes in Price Bid, sometimes in Bid/Ask
+ ## so we try both
+ trancheupfrontbid = convert(csvdict['Upfront Bid'])
+ trancheupfrontask = convert(csvdict['Upfront Ask'])
+ try:
+ trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2
+ except TypeError:
+ trancheupfrontmid = None
+ trancherunningbid = trancherunningask = trancherunningmid = 500
+ else:
+ trancherunningbid= convert(csvdict['Bid'])
+ trancherunningask= convert(csvdict['Ask'])
+ try:
+ trancherunningmid = (trancherunningbid + trancherunningask)/2
+ except TypeError:
+ trancherunningmid = None
+ trancheupfrontbid = trancheupfrontmid = trancheupfrontask = 0
+ d.update({'indexrefspread': int(float(csvdict['Ref'])),
+ 'trancheupfrontbid' : trancheupfrontbid,
+ 'trancheupfrontmid' : trancheupfrontmid,
+ 'trancheupfrontask' : trancheupfrontask,
+ 'trancherunningbid' : trancherunningbid,
+ 'trancherunningmid' : trancherunningmid,
+ 'trancherunningask' : trancherunningask,
+ 'index' : 'XO'})
+ data.append(d)
+ with engine.connect() as conn:
+ for i, l in enumerate(data):
+ with conn.begin() as t:
+ try:
+ conn.execute(ins, l)
+ except (IntegrityError, DataError) as e:
+ logging.debug(e.orig)
+ t.rollback()
+
+ os.unlink(os.path.join(quote_dir, quotefile))
+
+if __name__=="__main__":
+ insert_quotes()
|