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
|
import pandas as pd
import os
import re
import psycopg2
import datetime
import bbg_helpers
from db import dbconn
from db import dbengine
from pickle import dumps
from sqlalchemy import create_engine
from itertools import chain
from glob import glob, iglob
from pandas.tseries.offsets import MonthEnd, BDay
def runAllFill():
for f in get_globs():
if settings[os.path.basename(f)][2] == "N":
runSingleFill(f)
print("done " + f)
def runSingleFill(f):
range_name, sheet_name, done = settings[os.path.basename(f)]
markdate = pd.Timestamp(os.path.dirname(f).rsplit(os.path.sep, 1)[-1])-5*BDay() + MonthEnd()
#change /usr/lib/python3.6/site-packages/xlrd/xlsx.py line 609 to check for ":" in ref as well. Otherwise single merged cells bombs
marks = pd.read_excel(f, sheet_name, skiprows=13, parse_cols=range_name)
df = pd.DataFrame()
for s in ['1st', '2nd', '3rd'] +[str(i)+'th' for i in range(4, 12)]:
if s+' Source Price' not in marks:
break
temp = marks[['CUSIP', s+' Source Price', s+' Source']]
temp.columns = ['identifier', 'mark', 'source']
df = df.append(temp)
df = df.dropna()
df = df[df.mark != 0]
df['date'] = markdate
df['source'] = df['source'].str.upper()
conn = dbconn('dawndb')
sql_str = "INSERT INTO external_marks VALUES(%(identifier)s, %(date)s, %(mark)s, %(source)s) ON CONFLICT DO NOTHING"
with conn.cursor() as c:
for r in df.to_dict(orient='record'):
try:
c.execute(sql_str, r)
except (psycopg2.DataError, psycopg2.IntegrityError) as detail:
print(detail)
conn.rollback()
finally:
conn.commit()
def get_CUSIPs():
conn = dbconn('dawndb')
sql_str = "select distinct cusip from bonds where asset_class = 'Subprime'"
df = pd.read_sql_query(sql_str, dbengine('dawndb'))
return df
def get_BVAL():
BBG_IP = ['192.168.9.65']
bbgstartdate = pd.datetime(2013, 1, 1)
hist_securities = get_CUSIPs()['cusip']+ ' Mtge'
hist_fields = ['PX_LAST']
with bbg_helpers.init_bbg_session(BBG_IP) as session:
hist_data = bbg_helpers.retrieve_data(session, hist_securities, hist_fields, start_date=bbgstartdate)
hist_data1 = pd.concat(hist_data, axis = 1)
hist_data1 = hist_data1.resample('1m', closed= 'right').last()
hist_data1 = hist_data1.drop(pd.Timestamp('2017-02-28')).stack(level = -2)
hist_data1['source'] = 'BVAL'
hist_data1 = hist_data1.reset_index().rename(columns = {'level_1':'identifier', 'PX_LAST': 'mark'})
hist_data1.identifier = hist_data1.identifier.str[:9]
return hist_data1
def pop_BVAL_to_database(df):
conn = dbconn('dawndb')
sql_str = "INSERT INTO external_marks VALUES(%(identifier)s, %(date)s, %(mark)s, %(source)s) ON CONFLICT DO NOTHING"
with conn.cursor() as c:
for r in df.to_dict(orient='record'):
try:
c.execute(sql_str, r)
except (psycopg2.DataError, psycopg2.IntegrityError) as detail:
print(detail)
conn.rollback()
finally:
conn.commit()
def get_globs():
basedir = '/home/serenitas/Daily'
globs = [iglob(os.path.join(basedir,
year,
"{}_*/{}*/ReviewedPack*.xlsx".format(year, year)))
for year in ['2015', '2016', '2017', '2018']]
for year in ['2013', '2014']:
globs.append(iglob(os.path.join(basedir,
year,
"{}_*/{}*/Serenitas_Month-end_book*.xlsx".format(year, year))))
return chain.from_iterable(globs)
settings = {
'ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20180901.20180930.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180801.20180831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180701.20180731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180601.20180630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.jipark.SERENITAS.SERCGMAST.20180501.20180531.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180401.20180430.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180301.20180331.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180201.20180228.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20180101.20180131.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171201.20171231.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171101.20171130.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171001.20171031.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170901.20170930.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170801.20170831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170701.20170731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170601.20170630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170501.20170531.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170401.20170430.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170301.20170331.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170201.20170228.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170101.20170131.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161201.20161231.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161101.20161130.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161001.20161031.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160901.20160930.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160801.20160831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160701.20160731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160601.20160630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160501.20160531.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160401.20160430.Final.xlsx': ("IA:IV", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160301.20160331.Final.xlsx': ("IA:IV", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160201.20160229.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160101.20160131.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151201.20151231.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151101.20151130.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151001.20151031.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150901.20150930.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150801.20150831.Draft.savable.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150701.20150731.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150601.20150630.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150501.20150531.Draft.xlsx': ("GA:GR", "VC Report 0531", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150401.20150430.Draft.xlsx': ("GA:GR", "All Securities Valuation Report", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150301.20150331.Draft.xlsx': ("GA:GP", "Actual Valuation Detail", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150201.20150228.Draft.xlsx': ("GA:GL", "Securities Valuation - Details", "Y"),
'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150101.20150131.Draft.xlsx': ("GA:GL", "Securities Valuation - Details", "Y"),
'Serenitas_Month-end_book_12.31.14.xlsx': ("NA:NR", "Valuation report", "Y"),
'Serenitas_Month-end_book_113014.xlsx': ("NA:NR", "Valuation report", "Y"),
'Serenitas_Month-end_book-10_31_2014_updated.xlsx': ("NA:NR", "Valuation report", "Y"),
'Serenitas_Month-end_book-09_30_2014.xlsx': ("II:IZ", "Valuation report", "Y"),
'Serenitas_Month-end_book--08_31_2014_Final.xlsx': ("II:IZ", "Valuation report", "Y"),
'Serenitas_Month-end_book--07_31_2014_final.xlsx': ("II:IZ", "Valuation report", "Y"),
'Serenitas_Month-end_book--06_30_2014_-_Final_with_VC_Report (with CAD payments).xlsx': ("II:IZ", "Valuation report", "Y"),
'Serenitas_Month-end_book--05_31_2014_Final.xlsx': ("II:IZ", "Valuation report", "Y"),
'Serenitas_Month-end_book--04_30_2014 Final VC Report.xlsx': ("II:IZ", "All Securities Report", "Y"),
'Serenitas_Month-end_book--03_31_2014 - Final.xlsx': ("II:IZ", "All Securities Report", "Y"),
'Serenitas_Month-end_book--02_28_2014 Final.xlsx': ("II:IZ", "All Securities Report", "Y"),
'Serenitas_Month-end_book--01_31_2014.xlsx': ("II:IZ", "ALL Securities", "Y"),
'Serenitas_Month-end_book--12_31_2013.xlsx': ("II:IZ", "ALL Securities", "Y"),
'Serenitas_Month-end_book--11_30_2013.xlsx': ("II:IZ", "ALL Securities", "Y"),
'Serenitas_Month-end_book--10_31_2013.xlsx': ("II:IZ", "All Securities", "Y"),
'Serenitas_Month-end_book--09_30_2013.xlsx': ("II:IZ", "All Securities", "Y"),
'Serenitas_Month-end_book--08_31_2013.xlsx': ("II:IZ", "All Securities", "Y"),
'Serenitas_Month-end_book--07_31_2013.xlsx': ("II:IZ", "All Securities", "Y"),
'Serenitas_Month-end_book--06_30_2013.xlsx': ("II:IZ", "All Securities", "Y"),
'Serenitas_Month-end_book--05 31 2013.xlsx': ("II:IZ", "All Securities", "Y"),
'Serenitas_Month-end_book--04 30 2013.xlsx': ("II:IZ", "All Securities", "Y")
}
|
