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
|
import pandas as pd
import os
import re
import psycopg2
import datetime
from db import dbconn
from pickle import dumps
from sqlalchemy import create_engine
from itertools import chain
from glob import glob, iglob
from pandas.tseries.offsets import MonthEnd
#engine = create_engine('postgresql://dawn_user@debian/dawndb')
def runAllFill():
for f in get_globs():
if settings[os.path.basename(f)][2] == "N":
runSingleFill(f)
def runSingleFill(f):
conn = dbconn('dawndb')
sql_str = "INSERT INTO external_marks VALUES(%(identifier)s, %(date)s, %(mark)s, %(source)s)"
c = conn.cursor()
(range_name, sheet_name, done) = settings[os.path.basename(f)]
dirstructure = os.path.dirname(f).split('/')
markdate = (pd.Timestamp(dirstructure[5].replace("_","-"))+MonthEnd()).date()
marks = pd.read_excel(f, sheet_name, skiprows=13, parse_cols=range_name)
for s in ['1st', '2nd', '3rd'] +[str(i)+'th' for i in range(4, 12)]:
if s+' Source Price' not in marks:
break
if s == '1st':
temp = marks.loc[:, ['CUSIP', '1st Source Price', '1st Source']]
temp.columns = ['identifier', 'mark', 'source']
else:
temp1 = marks.loc[:, ['CUSIP', s+' Source Price', s+' Source']]
temp1.columns = ['identifier', 'mark', 'source']
temp = temp.append(temp1)
for columnname in temp.columns:
temp.dropna(axis=0, subset=[columnname], inplace=True)
temp = temp[temp.mark != 0]
temp.loc[:, 'date'] = markdate
temp['source']= temp['source'].str.upper()
for r in temp.to_dict(orient='record'):
try:
#import pdb; pdb.set_trace()
c.execute(sql_str, r)
except (psycopg2.DataError, psycopg2.IntegrityError) as detail:
print(detail)
conn.rollback()
finally:
conn.commit()
c.close()
def get_globs():
basedir = '/home/serenitas/Daily'
globs = [iglob(os.path.join(basedir,
year,
"{}_*/{}*/ReviewedPack*.xlsx".format(year, year)))
for year in ['2015', '2016']]
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.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")
}
|
