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