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
