def saveCSV(date): sql = '''SELECT isnull(tm.barrid, sm.barrid) as barrid, sm.sedol, sm.name, sm.localid, sm.listed_country, s4.datadate, s4.category, s4.rank, s4.region, s4.country, s4.sector FROM [wzhu].[dbo].[shortcolor4] s4 join [nipun_prod].[dbo].[security_master] sm on s4.datadate between sm.datadate and isnull(sm.stopdate, '2050-01-01') and s4.sedol = sm.sedol and s4.section = 'TopDTC' and s4.region in ('AS', 'JP') and s4.datadate = '{:%Y%m%d}' left join nipun_prod..thai_barrid_map tm on left(sm.barrid, 6)=tm.root_id '''.format(date) dbo = db.db(connect='qai') data = dbo.query(sql, df=True) data = data.sort(['barrid', 'rank']) data = data.drop_duplicates(['barrid'], take_last=False) data = data.sort(['category', 'region', 'rank']) univ = lb.load_production_universe(UNIVERSE, date).index data = data[map(lambda x: x in univ, data['barrid'])] fout = "{}/shortcolorDTC.{:%Y%m%d}.csv".format(OPT_DIR, date) data.to_csv(fout, index=False, columns=[ 'barrid', 'sedol', 'name', 'localid', 'listed_country', 'datadate', 'category', 'region', 'rank', 'sector' ])
import datetime import pandas import nipun.dbc as dbc dbo = dbc.db(connect='qai') def run(ident): sql = ''' select datadate , stopdate , barrid , active_flag , cusip , ds2_id , dxl_id , ibes_code , ibes_region , isin , isocurr , listed_country , localid , name , repno , sedol , synth_barrid , tk2_id , wspit_id
import nipun.utils as nu import nipun.dbc as dbc import nipun.cpa.winsor as wins import nipun.cpa.get_cp as get_cp import pandas import datetime import nipun.utils as nu dbo = dbc.db(connect='gce-data') USE_NEW = True def load_ret(date): if USE_NEW: return load_ret_new(date) return load_ret_q(date) def load_data(date): if USE_NEW: return load_new_data(date) return load_data_q(date) ### data loaders - move me out of quandl to something else def load_ret_q(date): sql = ''' select * from dsargent.commodity_quandl_returns
import nipun.dbc as dbc dbo = dbc.db(connect='g_holdings') def run(date): data = dbo.query( '''select * from production_holdings.position_holdings where sleeve_id=1000 and datadate='%s' ''' % date, df=True) print date print data[['total_notional_usd', 'active_weight']].abs().sum()
import nipun.dbc as dbc import nipun.cpa.load_barra as lb import pandas dbo = dbc.db(connect='gce-reporting') def run(date): sql = ''' select * from production_holdings.prelocate_request where datadate='%s' and shares_received is not null ''' % date.strftime('%Y%m%d') data = dbo.query(sql, df=True) if data is None: return rsk = lb.loadrsk2('ase1jpn', 'S', date, daily=True) rsk['usdp'] = rsk['USD_CAPT'] * rsk['LOC_PRIC'] / rsk['LOC_CAPT'] data = pandas.merge(data, rsk[['usdp']], left_on='barrid', right_index=True, how='left') data['request_notional'] = data['usdp'] * data['shares_requested'].astype( float) data['get_notional'] = data['usdp'] * data['shares_received'].astype(float) data['delta_notional'] = data['request_notional'] - data['get_notional'] a = data[['request_notional', 'get_notional', 'delta_notional' ]].sum() / 1e6