def export_universe_history(universe):
rows = [GUARDIAN_HISTORY_HEADER]
for security in universe.members.all():
rows = rows + export_security_history(security, rows)
save_path = os.path.join(WORKING_PATH, universe.name + "_HISTORY_" + str(datetime.date.today()) + ".xlsx")
simple_xlsx_dump(rows, save_path)
return save_path
def convert_pictet_operations_guardian(pictet_file, guardian_file, accounts_file, export_file):
export_header = ['progr','rif','simulazione','spuntato','storicizzato','storno','annullato','data_ins','data_ope','ora_ope','data_val',
'cod_ope','cod_rap','cod_tit','cod_div_tit','cod_isin','cod_bloomberg','cod_esterno','qta','prezzo','rateo','cambiom',
'cambiod','cod_ctp','cod_dep_tit','cod_dep_liq','cod_dep_liq2','cod_div_reg','ctv_tit_dn','ctv_rat_dn','comp_dep_dn',
'coma_dep_dn','comp_alt_dn','coma_alt_dn','spese_dn','imposte_dn','ctv_tot_dn','margini_dn','ctv_tit_dr','ctv_rat_dr',
'omp_dep_dr','coma_dep_dr','comp_alt_dr','coma_alt_dr','spese_dr','imposte_dr','ctv_tot_dr','des_mov','rif_est',
'coordinate1','coordinate2','data_sto','progr_sto','prezzo_car','cod_div_ds','cambiom_ds','cambiod_ds','ctv_tot_ds',
'cod_mer','tipo_prezzo','tipo_tempo','cash']
data = load_pictet_operations(pictet_file)
guardian = xls_loader.load_xlsx("Guardian",guardian_file,[u'PICTET 1',u'PICTET 2'])
accounts = xls_loader.load_xlsx("GUARDIAN_ACCOUNTS", accounts_file, [u'pictet_code',u'account_currency'])
full_content = [export_header]
line_index = 1
account_code = None
for order in data.keys():
LOGGER.info("Working on row " + str(line_index) + " with operation code " + str(order))
code_operation = data[order][0][u'Code op\xe9ration']
type_operation = data[order][0][u'Type op\xe9ration']
try:
account_code = accounts[data[order][0][u'No du compte']][data[order][0][u'Monnaie de r\xe9f\xe9rence']][0][u'portfolio_code']
pictet_code = data[order][0][u'No du compte']
except:
continue
if len(data[order])==1:
# Simple case
guardian_operation = guardian[type_operation][code_operation][0][u'GUARDIAN']
LOGGER.info("Pictet=" + type_operation + "/" + code_operation)
LOGGER.info("Guard.=" + str(guardian_operation))
if guardian_operation!=None and guardian_operation!='':
if guardian_operation=='GIROCONTO' and type_operation!='Fiduciary deposit':
guardian_operation = 'PRELEVAMENTO'
guardian_cash = 'S' if guardian[type_operation][code_operation][0][u'CASH']==1 else ''
if guardian_operation!=None and guardian_operation!='' and guardian_operation!='GIROCONTO':
row = default_operation(data[order][0], account_code, pictet_code, accounts, code_operation, type_operation, guardian_cash, guardian_operation)
full_content.append(row)
LOGGER.info("Size:" + str(len(full_content)))
elif guardian_operation=='GIROCONTO':
row = fiduciary_transfer(data[order][0], account_code, pictet_code, accounts, code_operation, type_operation, guardian_cash, guardian_operation)
full_content.append(row)
LOGGER.info("Size:" + str(len(full_content)))
else:
LOGGER.warn('\tSkipping')
elif guardian[type_operation][code_operation][0][u'ADVANCED']=='STOCKDIVIDEND':
row = switch_operation(data[order][0], account_code, pictet_code, accounts, '', 'CARICO')
full_content.append(row)
LOGGER.info("Size:" + str(len(full_content)))
line_index += 1
else:
# Complex: Cancellation, transfer
rows = get_guardian_operation(data[order], account_code, pictet_code, guardian, accounts)
if rows!=None:
[full_content.append(row) for row in rows]
LOGGER.info("Size:" + str(len(full_content)))
else:
LOGGER.warn('\tSkipping')
line_index += 1
simple_xlsx_dump(full_content, export_file)
def export_security_history(security, global_rows=None):
if global_rows == None:
rows = [GUARDIAN_HISTORY_HEADER]
else:
rows = []
isin = security.aliases.filter(alias_type__name="ISIN")
if isin.exists():
isin = isin[0].alias_value
else:
isin = None
bloomberg = security.aliases.filter(alias_type__name="BLOOMBERG")
if bloomberg.exists():
bloomberg = bloomberg[0].alias_value
else:
bloomberg = None
external_information = external_content.get_security_information(
GUARDIAN_DATASOURCE, isin=isin, bloomberg=bloomberg
)
if external_information != None:
content = get_main_track(security, True, False)
if content != None:
for token in content:
row = []
row.append(token["date"])
row.append(external_information["cod_tit"])
row.append(None)
row.append(None)
row.append(None)
row.append(None)
row.append(token["value"])
row.append(None)
rows.append(row)
else:
LOGGER.warn(
"Security "
+ str(security.name)
+ " could not be found with [BLOOMBERG="
+ str(bloomberg)
+ ", ISIN="
+ str(isin)
+ "]"
)
if global_rows == None:
save_path = os.path.join(WORKING_PATH, security.name + "_HISTORY_" + str(datetime.date.today()) + ".xlsx")
simple_xlsx_dump(rows, save_path)
return save_path
else:
return rows
def get_guardian_operation(saxo_codes, guardian_code, start_date, end_date, export_file):
export_header = ['progr','rif','simulazione','spuntato','storicizzato','storno','annullato','data_ins','data_ope','ora_ope','data_val',
'cod_ope','cod_rap','cod_tit','cod_div_tit','cod_isin','cod_bloomberg','cod_esterno','qta','prezzo','rateo','cambiom',
'cambiod','cod_ctp','cod_dep_tit','cod_dep_liq','cod_dep_liq2','cod_div_reg','ctv_tit_dn','ctv_rat_dn','comp_dep_dn',
'coma_dep_dn','comp_alt_dn','coma_alt_dn','spese_dn','imposte_dn','ctv_tot_dn','margini_dn','ctv_tit_dr','ctv_rat_dr',
'omp_dep_dr','coma_dep_dr','comp_alt_dr','coma_alt_dr','spese_dr','imposte_dr','ctv_tot_dr','des_mov','rif_est',
'coordinate1','coordinate2','data_sto','progr_sto','prezzo_car','cod_div_ds','cambiom_ds','cambiod_ds','ctv_tot_ds',
'cod_mer','tipo_prezzo','tipo_tempo','cash']
rows = [export_header]
for trade in get_operations('saxo', start_date, end_date):
if trade[u'product_account_id'] in saxo_codes:
LOGGER.info("Working on row " + str(trade[u'trade_id']) + " with operation code " + str(trade[ u'trade_buy_sell']) + ": " + str(trade[u'trade_product']) + " of " + str(trade[u'trade_instrument']))
try:
trade_date = datetime.datetime.strptime(trade[u'trade_timestamp'],'%Y-%m-%d %H:%M:%S.%f')
except:
trade_date = datetime.datetime.strptime(trade[u'trade_timestamp'],'%Y-%m-%d %H:%M:%S')
trade_time = trade_date.time()
trade_date = trade_date.date()
trade_details = {'fees': 0.0, 'taxes': 0.0, 'pnl': 0.0}
for det in trade[u'details']:
if det[u'trade_booking_type'] in [u'Commission',u'Exchange Fee',u'Partner Commission',u'Currency Cut Commission']:
trade_details['fees'] = trade_details['fees'] + det[u'trade_booked_amount']
elif det[u'trade_booking_type'] in ['[Swiss Stamp Duty Foreign]','Tax Commission','[UK PTM Levy]', 'Stamp Duty']:
trade_details['taxes'] = trade_details['taxes'] + det[u'trade_booked_amount']
elif det[u'trade_booking_type'] in ['P/L']:
trade_details['pnl'] = trade_details['pnl'] + det[u'trade_booked_amount']
if not trade_details.has_key('rate'):
if det[u'trade_conversion_rate']!=0.0 and det[u'trade_conversion_rate']!=1.0 and det[u'trade_conversion_rate']!=None:
trade_details['rate'] = det[u'trade_conversion_rate']
security_code = get_security_code(trade[u'trade_instrument'], trade[u'trade_isin'], trade[u'trade_product'])
if security_code==None:
LOGGER.warning("Could not find security: " + trade[u'trade_instrument'] + "/" + trade[u'trade_isin'] + "/" + trade[u'trade_product'])
continue
divide = get_security_divisor(security_code)
buy_cfd = trade[u'trade_product']=='CFDs' and trade[u'trade_buy_sell']=='Bought'
sell_cfd = trade[u'trade_product']=='CFDs' and trade[u'trade_buy_sell']=='Sold'
gross_amount = trade[u'trade_amount'] * (trade[u'trade_price'] / (1.0 if divide==None or divide=='' else divide)) * -1.0
if trade[u'trade_instrument_currency']!=saxo_accounts[trade[u'product_account_id']]['currency'] and not trade_details.has_key('rate'):
LOGGER.info("\tNo rate but FX needed")
exchange_rate = get_exchange_rate_price(trade[u'trade_instrument_currency'],saxo_accounts[trade[u'product_account_id']]['currency'],trade_date )
else:
exchange_rate = 1.0 if not trade_details.has_key('rate') or trade_details['rate']==0.0 else trade_details['rate']
final_gross_amount = gross_amount * exchange_rate
if final_gross_amount<0:
final_net_amount = final_gross_amount + trade_details['fees'] + trade_details['taxes']
else:
final_net_amount = ((final_gross_amount * -1.0) + trade_details['fees'] + trade_details['taxes']) * -1.0
row = ['','','','','','','',
trade_date,
trade_date,
str(trade_time)[0:5],
datetime.datetime.strptime(trade[ u'trade_value_date'],'%Y-%m-%d'),
'B' if trade[u'trade_buy_sell']=='Bought' else 'S',
guardian_code,
security_code, # TRADED SECURITY
trade[u'trade_instrument_currency'],
'', # ISIN
'', # BLOOMBERG
'',
trade[u'trade_amount'],
trade[u'trade_price'],
'',
exchange_rate if exchange_rate!=1.0 else None,
'',
'SAXO',
'DEPTIT',
('LIQCFD' + saxo_accounts[trade[u'product_account_id']]['currency']) if trade[u'trade_product']=='CFDs' else saxo_accounts[trade[u'product_account_id']]['guardian'],
'', # NONE IF BUY OR SELL
saxo_accounts[trade[u'product_account_id']]['currency'],
gross_amount,'','','','','','','',gross_amount,'',
final_gross_amount if not buy_cfd else final_net_amount,'','','','','',trade_details['fees'] if not buy_cfd else 0.0, trade_details['taxes'] if not buy_cfd else 0.0, final_net_amount,
str(trade[ u'trade_buy_sell']) + " " + str(trade[u'trade_product']) + " of " + str(trade[u'trade_instrument']) + " [" + trade[u'trade_id'] + "]",'','','','','','','','','','','','','',
''
]
rows.append(row)
if sell_cfd and trade_details['pnl']!=0.0:
row = ['','','','','','','',
trade_date,
trade_date,
str(trade_time)[0:5],
datetime.datetime.strptime(trade[ u'trade_value_date'],'%Y-%m-%d'),
'B' if trade_details['pnl']<0.0 else 'S',
guardian_code,
'LIQCFD' + saxo_accounts[trade[u'product_account_id']]['currency'],
'',
'', # ISIN
'', # BLOOMBERG
'',
-1.0 * trade_details['pnl'],
'',
'',
'',
'',
'SAXO',
'',
saxo_accounts[trade[u'product_account_id']]['guardian'],
'',
saxo_accounts[trade[u'product_account_id']]['currency'],
trade_details['pnl'],'','','','','','','',trade_details['pnl'],'',
trade_details['pnl'],'','','','','','', '', trade_details['pnl'],
"PnL for " + str(trade[ u'trade_buy_sell']) + " " + str(trade[u'trade_product']) + " of " + str(trade[u'trade_instrument']),'','','','','','','','','','','','','',
''
]
LOGGER.info("\tCFD PnL have been found")
rows.append(row)
if (buy_cfd or sell_cfd) and trade_details['taxes']!=0.0:
prefix = 'Taxes ' if trade_details['fees']<0 else 'Commission'
row = ['','','','','','','',
trade_date,
trade_date,
str(trade_time)[0:5],
datetime.datetime.strptime(trade[ u'trade_value_date'],'%Y-%m-%d'),
'DEBIT' if trade_details['fees']<0 else 'CREDIT',
guardian_code,
saxo_accounts[trade[u'product_account_id']]['currency'],
saxo_accounts[trade[u'product_account_id']]['currency'],
'', # ISIN
'', # BLOOMBERG
'',
trade_details['taxes'],
'',
'',
'',
'',
'SAXO',
'',
saxo_accounts[trade[u'product_account_id']]['guardian'],
'',
saxo_accounts[trade[u'product_account_id']]['currency'],
trade_details['taxes'],'','','','','','','',trade_details['taxes'],'',
trade_details['taxes'],'','','','','','', '', trade_details['taxes'],
prefix + " for " + str(trade[ u'trade_buy_sell']) + " " + str(trade[u'trade_product']) + " of " + str(trade[u'trade_instrument']),'','','','','','','','','','','','','',
''
]
LOGGER.info("\tTaxes have been found")
rows.append(row)
if (buy_cfd or sell_cfd) and trade_details['fees']!=0.0:
prefix = 'Fees ' if trade_details['fees']<0 else 'Commission'
row = ['','','','','','','',
trade_date,
trade_date,
str(trade_time)[0:5],
datetime.datetime.strptime(trade[ u'trade_value_date'],'%Y-%m-%d'),
'DEBIT' if trade_details['fees']<0 else 'CREDIT',
guardian_code,
saxo_accounts[trade[u'product_account_id']]['currency'],
saxo_accounts[trade[u'product_account_id']]['currency'],
'', # ISIN
'', # BLOOMBERG
'',
trade_details['fees'],
'',
'',
'',
'',
'SAXO',
'',
saxo_accounts[trade[u'product_account_id']]['guardian'],
'',
saxo_accounts[trade[u'product_account_id']]['currency'],
trade_details['fees'],'','','','','','','',trade_details['fees'],'',
trade_details['fees'],'','','','','','', '', trade_details['fees'],
prefix + " for " + str(trade[ u'trade_buy_sell']) + " " + str(trade[u'trade_product']) + " of " + str(trade[u'trade_instrument']),'','','','','','','','','','','','','',
''
]
LOGGER.info("\tFees have been found")
rows.append(row)
simple_xlsx_dump(rows, export_file)
def export_universe(universe):
LOGGER.info("Exporting " + str(universe.name) + " as GUARDIAN")
rows = [GUARDIAN_SECURITY_HEADER]
for member in universe.members.all():
data = []
data.append("")
data.append(member.name)
data.append(member.currency.short_name)
isin = member.aliases.filter(alias_type__name="ISIN")
if isin.exists():
data.append(isin[0].alias_value)
else:
data.append("")
bloomberg = member.aliases.filter(alias_type__name="BLOOMBERG")
if bloomberg.exists():
data.append(bloomberg[0].alias_value)
else:
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
stock = bond = option = fund = ucits = bond_fund = cfd = coupon = dividend = forward = "N"
minimal_quantity = divisor = 1.0
if member.type.identifier == "CONT_BOND":
bond = "S"
coupon = "S"
divisor = 100.0
elif member.type.identifier == "CONT_STOCK":
stock = "S"
dividend = "S"
elif member.type.identifier == "CONT_FUND":
fund = "S"
minimal_quantity = 0.00001
elif member.type.identifier == "CONT_FORWARD":
forward = "S"
elif member.type.identifier in ("CONT_PUT", "CONT_CALL"):
option = "S"
data.append(stock)
data.append(bond)
data.append(option)
data.append(fund)
data.append(ucits)
data.append(bond_fund)
data.append("N")
data.append("N")
data.append(cfd)
data.append(coupon)
data.append(dividend)
data.append(forward)
data.append("")
data.append("") # Coupon rate
data.append("")
data.append("")
data.append("")
data.append("")
data.append(minimal_quantity)
data.append("S")
data.append(divisor)
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("S") # Price link
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("")
data.append("S")
rows.append(data)
save_path = os.path.join(WORKING_PATH, universe.name + "_SECURITIES_" + str(datetime.date.today()) + ".xlsx")
simple_xlsx_dump(rows, save_path)
return save_path
