aboutsummaryrefslogtreecommitdiffstats
path: root/python/externalmarksbackfill.py
diff options
context:
space:
mode:
Diffstat (limited to 'python/externalmarksbackfill.py')
-rw-r--r--python/externalmarksbackfill.py153
1 files changed, 0 insertions, 153 deletions
diff --git a/python/externalmarksbackfill.py b/python/externalmarksbackfill.py
deleted file mode 100644
index 1304a7e8..00000000
--- a/python/externalmarksbackfill.py
+++ /dev/null
@@ -1,153 +0,0 @@
-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)"
- 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)"
- 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']]
- 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.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")
-}
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 01:18:32 +0000