def __init__(self, file=None, template_styles=None, timestamp=None, templated_sheets=None, data_only=False): super(TemplatedWorkbook, self).__init__() self.workbook = load_workbook( filename=file, data_only=data_only) if file else Workbook() self.template_styles = template_styles or DefaultStyleSet() self.timestamp = timestamp self.templated_sheets = [] for sheetname, templated_sheet in self._items.items(): self.add_templated_sheet(templated_sheet, sheetname=sheetname, add_to_self=False) for templated_sheet in templated_sheets or []: self.add_templated_sheet(sheet=templated_sheet, sheetname=templated_sheet.sheetname, add_to_self=True) self._validate()
boolean = BoolColumn(header="BoolColumn", row_style="Row, integer", conditional_formatting=bold_true) integer = IntColumn(header="IntColumn", group=True) float = FloatColumn(header="FloatColumn", group=True) datetime = DatetimeColumn(header="DatetimeColumn", group=True) date = DateColumn(header="DateColumn") time = TimeColumn(header="TimeColumn", group=True) formula = FormulaColumn(header="FormulaColumn", formula="=SUM(ColumnDemo[IntColumn])") class DemoWorkbook(TemplatedWorkbook): timestamp = True persons = TemplatedPersonsSheet(sheetname="Persons", active=True) column_demo = ColumnDemoSheet(sheetname="Column demo") if __name__ == "__main__": workbook = DemoWorkbook(template_styles=DefaultStyleSet( ExtendedStyle(base="Default", name="Header", fill=SolidFill( "FF0000")))) workbook.column_demo.write(objects=list(demo_objects(100)), title="Column demo") workbook.persons.write(objects=persons, title="Persons") filename = workbook.save(join(dirname(__file__), "demo.xlsx")) wb = DemoWorkbook(file=filename) print(list(wb.persons.read()))
NamedStyle( name="Header", font=Font( name="Arial", size=12, bold=True, ), )) demo_style = StyleSet( NamedStyle(name="Default", font=Font(name="Arial", size=12)), ExtendedStyle( base="Default", # Reference to the style defined above name="Header", font={ "bold": True, })) bad_example = StyleSet( NamedStyle(name="Default", font=Font(name="Arial", size=12)), ExtendedStyle( base="Default", name="Header", font=Font( # Openpyxl will set name="Calibri" by default which will override name="Arial and break inheritance. bold=True))) new_font_default_style_set = DefaultStyleSet( NamedStyle( # Replace the existing "Default" font with a new one. name="Default", font=Font(name="Arial", )))
def main(): global float_with_comma parser = argparse.ArgumentParser() parser.add_argument( "eToroXLSXFiles", metavar="eToro-xlsx-file", help="eToro XLSX output file(s)", nargs="+", ) parser.add_argument( "-y", metavar="report-year", type=int, default=0, help= "Report will be generated for the provided calendar year (defaults to " + str(datetime.date.today().year - 1) + ")", ) parser.add_argument( "-t", help="Testing", action="store_true", ) parser.add_argument( "-c", help= "Include (real) crypto positions in the report (for s.p.; d.o.o.); crypto (real) positions are excluded otherwise; CFD cryptos are always included", action="store_true", default=False) args = parser.parse_args() inputFilenames = args.eToroXLSXFiles if args.y == 0: reportYear = datetime.date.today().year - 1 else: reportYear = int(args.y) reportCryptos = args.c test = args.t if not os.path.isdir("output"): os.mkdir("output") """ Creating daily exchange rates object """ bsRateXmlFilename = ("bsrate-" + str(datetime.date.today().year) + str(datetime.date.today().month) + str(datetime.date.today().day) + ".xml") if not os.path.isfile(bsRateXmlFilename): for file in glob.glob("bsrate-*.xml"): os.remove(file) urllib.request.urlretrieve(bsRateXmlUrl, bsRateXmlFilename) bsRateXml = xml.etree.ElementTree.parse(bsRateXmlFilename).getroot() rates = {} for d in bsRateXml: date = d.attrib["datum"].replace("-", "") rates[date] = {} for r in d: currency = r.attrib["oznaka"] rates[date][currency] = r.text """ Load crypto info """ if not reportCryptos: cryptoList = list(CryptoWorkbook(file="Kripto_info.xlsx").info.read()) else: cryptoList = [] """ Parsing of XLSX files """ tradesList = [] transactionList = [] for filename in inputFilenames: wb = EToroWorkbook(file=filename) tradesList.append(list(wb.closed_positions.read())) transactionList.append(list(wb.transactions.read())) statementStartDate = datetime.datetime(year=reportYear, month=1, day=1) statementEndDate = datetime.datetime(year=reportYear, month=12, day=31) """ Dictionary of stock trade arrays, each key represents a group of trades of same resource """ longNormalTrades = {} shortNormalTrades = {} longDerivateTrades = {} shortDerivateTrades = {} skippedCryptoTrades = {} """ Get trades from the worksheet and sort them by PositionID """ ETORO_DATETIME_FORMAT = None allTradesByPositionID = {} allTradesBySymbol = {} positionSymbols = get_position_symbols(transactionList) for tradeSheet in tradesList: if tradeSheet is None: continue for xlsTrade in tradeSheet: # determine etoro datetime format if ETORO_DATETIME_FORMAT is None: try: datetime.datetime.strptime(xlsTrade.close_date, ETORO_DATETIME_FORMAT_EN) ETORO_DATETIME_FORMAT = ETORO_DATETIME_FORMAT_EN except: try: datetime.datetime.strptime(xlsTrade.close_date, ETORO_DATETIME_FORMAT_SL) ETORO_DATETIME_FORMAT = ETORO_DATETIME_FORMAT_SL float_with_comma = True except: print( "ERROR: Could not determine eToro DATETIME format!" ) sys.exit(-1) # Position ID Action Copy Trader Name Amount Units Open Rate Close Rate Spread Profit Open Date Close Date Take Profit Rate Stop Loss Rate Rollover Fees And Dividends Is Real Leverage Notes close_date = datetime.datetime.strptime(xlsTrade.close_date, ETORO_DATETIME_FORMAT) if close_date.year != reportYear: # print("Skipping trade (year: " + str(close_date.year) + "): " + str(xlsTrade)) continue open_date = datetime.datetime.strptime( xlsTrade.open_date, ETORO_DATETIME_FORMAT) # ex.: 02/06/2020 13:57 action = xlsTrade.action.split(" ", 1) buy_sell = action[0] position_id = int(xlsTrade.position_id) name = action[1] symbol = positionSymbols[ position_id] if position_id in positionSymbols else None # fix for forex symbols if name is not None and len( name) == 7 and name[:4] == symbol + "/": symbol = name[0:3] + name[4:] is_etf = name.find(" ETF") >= 0 ifi_type = xlsTrade.is_real try: leverage = int( xlsTrade.leverage) if xlsTrade.leverage is not None else 0 except ValueError: leverage = 0 if leverage is not None and leverage > 1: amount = str2float(xlsTrade.amount) * leverage else: amount = str2float(xlsTrade.amount) units = str2float(xlsTrade.units) profit = str2float(xlsTrade.profit) # open & close prices are bogus in eToro statement... calculate it from amount and profit #open_price = str2float(xlsTrade.open_rate) #close_price = str2float(xlsTrade.close_rate) open_price = amount / units close_price = (amount + profit) / units open_rate = get_exchange_rate(rates, open_date, ETORO_CURRENCY) if get_exchange_rate(rates, close_date, ETORO_CURRENCY) is None: print("") close_rate = get_exchange_rate(rates, close_date, ETORO_CURRENCY) open_price_eur = open_price / open_rate close_price_eur = close_price / close_rate if buy_sell == "Buy": position_type = "long" elif buy_sell == "Sell": position_type = "short" else: print("ERROR: Could not determine position type! ") sys.exit(-1) if xlsTrade.is_real == "Real": asset_type = "normal" else: asset_type = "derivate" trade_open = { "position_id": position_id, "symbol": symbol, "position_type": position_type, "name": name, "is_etf": is_etf, "ifi_type": ifi_type, "leverage": leverage, "asset_type": asset_type, "quantity": units, "trade_date": open_date, "trade_price_eur": open_price_eur, # extra info "open_price_eur": open_price_eur, "close_price_eur": close_price_eur, "open_date": open_date, "close_date": close_date, } trade_close = { "position_id": position_id, "symbol": symbol, "position_type": position_type, "name": name, "is_etf": is_etf, "ifi_type": ifi_type, "leverage": leverage, "asset_type": asset_type, "quantity": -units, "trade_date": close_date, "trade_price_eur": close_price_eur, # extra info "open_price_eur": open_price_eur, "close_price_eur": close_price_eur, "open_date": open_date, "close_date": close_date, } allTradesByPositionID[position_id] = trade_open if symbol is not None: allTradesBySymbol[symbol] = trade_open if reportCryptos == False and ifi_type == "Real" and is_crypto( name, symbol, cryptoList): if name in skippedCryptoTrades.keys(): skippedCryptoTrades[name].extend([trade_open, trade_close]) else: skippedCryptoTrades[name] = [trade_open, trade_close] continue if asset_type == "normal": if position_type == "long": if name in longNormalTrades.keys(): longNormalTrades[name].extend( [trade_open, trade_close]) else: longNormalTrades[name] = [trade_open, trade_close] elif position_type == "short": if name in shortNormalTrades.keys(): shortNormalTrades[name].extend( [trade_open, trade_close]) else: shortNormalTrades[name] = [trade_open, trade_close] else: print("ERROR: Could not determine position type! ") sys.exit(-1) else: if position_type == "long": if name in longDerivateTrades.keys(): longDerivateTrades[name].extend( [trade_open, trade_close]) else: longDerivateTrades[name] = [trade_open, trade_close] elif position_type == "short": if name in shortDerivateTrades.keys(): shortDerivateTrades[name].extend( [trade_open, trade_close]) else: shortDerivateTrades[name] = [trade_open, trade_close] else: print("ERROR: Could not determine position type! ") sys.exit(-1) """ else: sys.exit( "Error: cannot figure out if trade is Normal or Derivate, Long or Short" ) """ """ Sort trades by position ID """ for securityID in longNormalTrades: longNormalTrades[securityID].sort( key=itemgetter('trade_date', 'position_id')) for securityID in shortNormalTrades: shortNormalTrades[securityID].sort( key=itemgetter('trade_date', 'position_id')) for securityID in longDerivateTrades: longDerivateTrades[securityID].sort( key=itemgetter('trade_date', 'position_id')) for securityID in shortDerivateTrades: shortDerivateTrades[securityID].sort( key=itemgetter('trade_date', 'position_id')) for securityID in skippedCryptoTrades: skippedCryptoTrades[securityID].sort( key=itemgetter('trade_date', 'position_id')) """ Save debug info to XLS """ wb = Workbook() sh = wb.create_sheet(title="Normal (long)") for securityID in longNormalTrades: trades = longNormalTrades[securityID] for trade in trades: sh.append([ trade["symbol"], trade["name"], "true" if trades[0]["is_etf"] else "false", "Open" if trade["quantity"] > 0 else "Close", trade["trade_date"].strftime(EDAVKI_DATETIME_FORMAT), trade["quantity"] if trade["quantity"] >= 0 else -trade["quantity"], trade["trade_price_eur"] ]) sh = wb.create_sheet(title="Derivate (long)") for securityID in longDerivateTrades: trades = longDerivateTrades[securityID] for trade in trades: sh.append([ trade["symbol"], trade["name"], "true" if trades[0]["is_etf"] else "false", "Open" if trade["quantity"] > 0 else "Close", trade["trade_date"].strftime(EDAVKI_DATETIME_FORMAT), trade["quantity"] if trade["quantity"] >= 0 else -trade["quantity"], trade["trade_price_eur"] ]) sh = wb.create_sheet(title="Derivate (short)") for securityID in shortDerivateTrades: trades = shortDerivateTrades[securityID] for trade in trades: sh.append([ trade["symbol"], trade["name"], "true" if trades[0]["is_etf"] else "false", "Open" if trade["quantity"] > 0 else "Close", trade["trade_date"].strftime(EDAVKI_DATETIME_FORMAT), trade["quantity"] if trade["quantity"] >= 0 else -trade["quantity"], trade["trade_price_eur"] ]) sh = wb.create_sheet(title="Skipped crypto") for securityID in skippedCryptoTrades: trades = skippedCryptoTrades[securityID] for trade in trades: sh.append([ trade["symbol"], trade["name"], "true" if trades[0]["is_etf"] else "false", "Open" if trade["quantity"] > 0 else "Close", trade["trade_date"].strftime(EDAVKI_DATETIME_FORMAT), trade["quantity"] if trade["quantity"] >= 0 else -trade["quantity"], trade["trade_price_eur"] ]) filename = "output/Debug-{0}.xlsx".format(reportYear) wb.save(filename) print("{0} created ".format(filename)) ########### ########### Doh-KDVP ########### """ Generate the files for Normal """ envelope = xml.etree.ElementTree.Element( "Envelope", xmlns="http://edavki.durs.si/Documents/Schemas/Doh_KDVP_9.xsd") envelope.set("xmlns:edp", "http://edavki.durs.si/Documents/Schemas/EDP-Common-1.xsd") header = xml.etree.ElementTree.SubElement(envelope, "edp:Header") xml.etree.ElementTree.SubElement(header, "edp:taxpayer") xml.etree.ElementTree.SubElement(envelope, "edp:AttachmentList") xml.etree.ElementTree.SubElement(envelope, "edp:Signatures") body = xml.etree.ElementTree.SubElement(envelope, "body") xml.etree.ElementTree.SubElement(body, "edp:bodyContent") Doh_KDVP = xml.etree.ElementTree.SubElement(body, "Doh_KDVP") KDVP = xml.etree.ElementTree.SubElement(Doh_KDVP, "KDVP") if test: xml.etree.ElementTree.SubElement(KDVP, "DocumentWorkflowID").text = "I" else: xml.etree.ElementTree.SubElement(KDVP, "DocumentWorkflowID").text = "O" xml.etree.ElementTree.SubElement(KDVP, "Year").text = str(reportYear) xml.etree.ElementTree.SubElement( KDVP, "PeriodStart").text = statementStartDate.strftime( EDAVKI_DATETIME_FORMAT) xml.etree.ElementTree.SubElement( KDVP, "PeriodEnd").text = statementEndDate.strftime(EDAVKI_DATETIME_FORMAT) xml.etree.ElementTree.SubElement(KDVP, "IsResident").text = "true" xml.etree.ElementTree.SubElement(KDVP, "SecurityCount").text = str( len(longNormalTrades)) xml.etree.ElementTree.SubElement(KDVP, "SecurityShortCount").text = str( len(shortNormalTrades)) xml.etree.ElementTree.SubElement(KDVP, "SecurityWithContractCount").text = "0" xml.etree.ElementTree.SubElement( KDVP, "SecurityWithContractShortCount").text = "0" xml.etree.ElementTree.SubElement(KDVP, "ShareCount").text = "0" for securityID in longNormalTrades: trades = longNormalTrades[securityID] KDVPItem = xml.etree.ElementTree.SubElement(Doh_KDVP, "KDVPItem") InventoryListType = xml.etree.ElementTree.SubElement( KDVPItem, "InventoryListType").text = "PLVP" Name = xml.etree.ElementTree.SubElement( KDVPItem, "Name").text = trades[0]["name"] HasForeignTax = xml.etree.ElementTree.SubElement( KDVPItem, "HasForeignTax").text = "false" HasLossTransfer = xml.etree.ElementTree.SubElement( KDVPItem, "HasLossTransfer").text = "false" ForeignTransfer = xml.etree.ElementTree.SubElement( KDVPItem, "ForeignTransfer").text = "false" TaxDecreaseConformance = xml.etree.ElementTree.SubElement( KDVPItem, "TaxDecreaseConformance").text = "false" Securities = xml.etree.ElementTree.SubElement(KDVPItem, "Securities") # We need to enter either ISIN, Code or Name # ISIN = xml.etree.ElementTree.SubElement(Securities, "ISIN").text = trades[0]["isin"] if len(trades) > 0 and "symbol" in trades[0] and trades[0][ "symbol"] is not None: Code = xml.etree.ElementTree.SubElement( Securities, "Code").text = trades[0]["symbol"] Name = xml.etree.ElementTree.SubElement( Securities, "Name").text = trades[0]["name"] IsFond = xml.etree.ElementTree.SubElement( Securities, "IsFond").text = "true" if trades[0]["is_etf"] else "false" F8Value = 0 n = -1 for trade in trades: n += 1 Row = xml.etree.ElementTree.SubElement(Securities, "Row") ID = xml.etree.ElementTree.SubElement(Row, "ID").text = str(n) if trade["quantity"] > 0: PurchaseSale = xml.etree.ElementTree.SubElement( Row, "Purchase") # Datum pridobitve F1 = xml.etree.ElementTree.SubElement( PurchaseSale, "F1").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) # Način pridobitve: A - vložek kapitala, B - nakup, C - povečanje kapitala družbe z lastnimi sredstvi zavezanca, # D - povečanje kapitala družbe iz sredstev družbe, E - zamenjava kapitala ob statusnih spremembah družbe, F - dedovanje, # G - darilo, H - drugo, I - povečanje kapitalskega deleža v osebni družbi zaradi pripisa dobička kapitalskemu deležu F2 = xml.etree.ElementTree.SubElement(PurchaseSale, "F2").text = "B" # Količina F3 = xml.etree.ElementTree.SubElement( PurchaseSale, "F3").text = "{0:.4f}".format(trade["quantity"]) # Nabavna vrednost ob pridobitvi (na enoto) F4 = xml.etree.ElementTree.SubElement( PurchaseSale, "F4").text = "{0:.4f}".format(trade["trade_price_eur"]) # Plačan davek na dediščine in darila (F2 == F | G) F5 = xml.etree.ElementTree.SubElement(PurchaseSale, "F5").text = "0.0000" elif trade["quantity"] == 0: print("Error! Trade units == 0! " + str(trade)) else: PurchaseSale = xml.etree.ElementTree.SubElement(Row, "Sale") # Datum odsvojitve F6 = xml.etree.ElementTree.SubElement( PurchaseSale, "F6").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) # Količina odsvojenega v.p. F7 = xml.etree.ElementTree.SubElement( PurchaseSale, "F7").text = "{0:.4f}".format(-trade["quantity"]) # Vrednost ob osvojitvi (na enoto) F9 = xml.etree.ElementTree.SubElement( PurchaseSale, "F9").text = "{0:.4f}".format(trade["trade_price_eur"]) # Pravilo iz drugega odstavka v povezavi s petim odstavkom 97.člena ZDoh-2 # TODO: #F10 = xml.etree.ElementTree.SubElement(PurchaseSale, "F10").text = "NE" # Trenutna zaloga F8Value += trade["quantity"] F8 = xml.etree.ElementTree.SubElement( Row, "F8").text = "{0:.4f}".format(F8Value) # trades # longNormalTrades for securityID in shortNormalTrades: trades = shortNormalTrades[securityID] KDVPItem = xml.etree.ElementTree.SubElement(Doh_KDVP, "KDVPItem") InventoryListType = xml.etree.ElementTree.SubElement( KDVPItem, "InventoryListType").text = "PLVPSHORT" Name = xml.etree.ElementTree.SubElement( KDVPItem, "Name").text = trades[0]["name"] HasForeignTax = xml.etree.ElementTree.SubElement( KDVPItem, "HasForeignTax").text = "false" HasLossTransfer = xml.etree.ElementTree.SubElement( KDVPItem, "HasLossTransfer").text = "false" ForeignTransfer = xml.etree.ElementTree.SubElement( KDVPItem, "ForeignTransfer").text = "false" TaxDecreaseConformance = xml.etree.ElementTree.SubElement( KDVPItem, "TaxDecreaseConformance").text = "false" SecuritiesShort = xml.etree.ElementTree.SubElement( KDVPItem, "SecuritiesShort") # We need to enter either ISIN, Code or Name #ISIN = xml.etree.ElementTree.SubElement(SecuritiesShort, "ISIN").text = trades[0]["isin"] if len(trades) > 0 and "symbol" in trades[0] and trades[0][ "symbol"] is not None: Code = xml.etree.ElementTree.SubElement( SecuritiesShort, "Code").text = trades[0]["symbol"] Name = xml.etree.ElementTree.SubElement( SecuritiesShort, "Name").text = trades[0]["name"] IsFond = xml.etree.ElementTree.SubElement( SecuritiesShort, "IsFond").text = "true" if trades[0]["is_etf"] else "false" F8Value = 0 n = -1 for trade in trades: n += 1 Row = xml.etree.ElementTree.SubElement(SecuritiesShort, "Row") ID = xml.etree.ElementTree.SubElement(Row, "ID").text = str(n) if trade["quantity"] > 0: PurchaseSale = xml.etree.ElementTree.SubElement( Row, "Purchase") F1 = xml.etree.ElementTree.SubElement( PurchaseSale, "F1").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) F2 = xml.etree.ElementTree.SubElement(PurchaseSale, "F2").text = "A" F3 = xml.etree.ElementTree.SubElement( PurchaseSale, "F3").text = "{0:.4f}".format(trade["quantity"]) F4 = xml.etree.ElementTree.SubElement( PurchaseSale, "F4").text = "{0:.4f}".format(trade["trade_price_eur"]) F5 = xml.etree.ElementTree.SubElement(PurchaseSale, "F5").text = "0.0000" else: PurchaseSale = xml.etree.ElementTree.SubElement(Row, "Sale") F6 = xml.etree.ElementTree.SubElement( PurchaseSale, "F6").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) F7 = xml.etree.ElementTree.SubElement( PurchaseSale, "F7").text = "{0:.4f}".format(-trade["quantity"]) F9 = xml.etree.ElementTree.SubElement( PurchaseSale, "F9").text = "{0:.4f}".format(trade["trade_price_eur"]) # Pravilo iz drugega odstavka v povezavi s petim odstavkom 97.člena ZDoh-2 # TODO: # F10 = xml.etree.ElementTree.SubElement(PurchaseSale, "F10").text = "NE" # Trenutna zaloga F8Value += trade["quantity"] F8 = xml.etree.ElementTree.SubElement( Row, "F8").text = "{0:.4f}".format(F8Value) # trades # shortNormalTrades xmlString = xml.etree.ElementTree.tostring(envelope) prettyXmlString = minidom.parseString(xmlString).toprettyxml(indent="\t") with open("output/Doh-KDVP.xml", "w", encoding="utf-8") as f: f.write(prettyXmlString) print("output/Doh-KDVP.xml created") print("") for securityID in skippedCryptoTrades: trades = skippedCryptoTrades[securityID] ids = [] name = trades[0]["name"] for trade in trades: if trade["position_id"] not in ids: ids.append(trade["position_id"]) ids = ','.join(map(str, ids)) print("Crypto: skipped {0} ({1})".format(name, ids)) print("") ########### ########### D-IFI ########### """ Generate the files for Derivates """ envelope = xml.etree.ElementTree.Element( "Envelope", xmlns="http://edavki.durs.si/Documents/Schemas/D_IFI_4.xsd") envelope.set("xmlns:edp", "http://edavki.durs.si/Documents/Schemas/EDP-Common-1.xsd") header = xml.etree.ElementTree.SubElement(envelope, "edp:Header") taxpayer = xml.etree.ElementTree.SubElement(header, "edp:taxpayer") xml.etree.ElementTree.SubElement(envelope, "edp:AttachmentList") xml.etree.ElementTree.SubElement(envelope, "edp:Signatures") body = xml.etree.ElementTree.SubElement(envelope, "body") xml.etree.ElementTree.SubElement(body, "edp:bodyContent") difi = xml.etree.ElementTree.SubElement(body, "D_IFI") xml.etree.ElementTree.SubElement( difi, "PeriodStart").text = statementStartDate.strftime( EDAVKI_DATETIME_FORMAT) xml.etree.ElementTree.SubElement( difi, "PeriodEnd").text = statementEndDate.strftime(EDAVKI_DATETIME_FORMAT) xml.etree.ElementTree.SubElement(difi, "TelephoneNumber").text = "" xml.etree.ElementTree.SubElement(difi, "Email").text = "" n = 0 for securityID in longDerivateTrades: trades = longDerivateTrades[securityID] n += 1 TItem = xml.etree.ElementTree.SubElement(difi, "TItem") TypeId = xml.etree.ElementTree.SubElement(TItem, "TypeId").text = "PLIFI" if trades[0]["ifi_type"] == "FUT": Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "01" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "terminska pogodba" elif trades[0]["ifi_type"] == "CFD": Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "02" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "finančne pogodbe na razliko" elif trades[0]["ifi_type"] == "OPT": Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "03" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "opcija in certifikat" else: Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "04" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "drugo" Name = xml.etree.ElementTree.SubElement( TItem, "Name").text = trades[0]["name"] if len(trades) > 0 and "symbol" in trades[0] and trades[0][ "symbol"] is not None: Code = xml.etree.ElementTree.SubElement( TItem, "Code").text = trades[0]["symbol"] #ISIN = xml.etree.ElementTree.SubElement(TItem, "ISIN").text = trades[0]["isin"] HasForeignTax = xml.etree.ElementTree.SubElement( TItem, "HasForeignTax").text = "false" F8Value = 0 for trade in trades: TSubItem = xml.etree.ElementTree.SubElement(TItem, "TSubItem") if trade["quantity"] > 0: PurchaseSale = xml.etree.ElementTree.SubElement( TSubItem, "Purchase") # Datum pridobitve F1 = xml.etree.ElementTree.SubElement( PurchaseSale, "F1").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) # Način pridobitve: A - nakup, B - dedovanje, C - darila, D - drugo F2 = xml.etree.ElementTree.SubElement(PurchaseSale, "F2").text = "A" # Količina F3 = xml.etree.ElementTree.SubElement( PurchaseSale, "F3").text = "{0:.4f}".format(trade["quantity"]) # Nabavna vrednost ob pridobitvi (na enoto) F4 = xml.etree.ElementTree.SubElement( PurchaseSale, "F4").text = "{0:.4f}".format(trade["trade_price_eur"]) # Trgovanje z vzvodom F9 = xml.etree.ElementTree.SubElement( PurchaseSale, "F9").text = "true" if trade["leverage"] > 1 else "false" else: PurchaseSale = xml.etree.ElementTree.SubElement( TSubItem, "Sale") # Datum odsvojitve F5 = xml.etree.ElementTree.SubElement( PurchaseSale, "F5").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) # Količina odsvojenega v.p. F6 = xml.etree.ElementTree.SubElement( PurchaseSale, "F6").text = "{0:.4f}".format(-trade["quantity"]) # Vrednost ob odsvojitvi F7 = xml.etree.ElementTree.SubElement( PurchaseSale, "F7").text = "{0:.4f}".format(trade["trade_price_eur"]) F8Value += trade["quantity"] F8 = xml.etree.ElementTree.SubElement( TSubItem, "F8").text = "{0:.4f}".format(F8Value) # trades # longDerivateTrades for securityID in shortDerivateTrades: trades = shortDerivateTrades[securityID] n += 1 TItem = xml.etree.ElementTree.SubElement(difi, "TItem") TypeId = xml.etree.ElementTree.SubElement(TItem, "TypeId").text = "PLIFIShort" if trades[0]["ifi_type"] == "FUT": Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "01" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "terminska pogodba" elif trades[0]["ifi_type"] == "CFD": Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "02" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "finančne pogodbe na razliko" elif trades[0]["ifi_type"] == "OPT": Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "03" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "opcija in certifikat" else: Type = xml.etree.ElementTree.SubElement(TItem, "Type").text = "04" TypeName = xml.etree.ElementTree.SubElement( TItem, "TypeName").text = "drugo" Name = xml.etree.ElementTree.SubElement( TItem, "Name").text = trades[0]["name"] if len(trades) > 0 and "symbol" in trades[0] and trades[0][ "symbol"] is not None: Code = xml.etree.ElementTree.SubElement( TItem, "Code").text = trades[0]["symbol"] #ISIN = xml.etree.ElementTree.SubElement(TItem, "ISIN").text = trades[0]["isin"] HasForeignTax = xml.etree.ElementTree.SubElement( TItem, "HasForeignTax").text = "false" F8Value = 0 for trade in trades: TShortSubItem = xml.etree.ElementTree.SubElement( TItem, "TShortSubItem") if trade["quantity"] > 0: PurchaseSale = xml.etree.ElementTree.SubElement( TShortSubItem, "Sale") F1 = xml.etree.ElementTree.SubElement( PurchaseSale, "F1").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) F2 = xml.etree.ElementTree.SubElement( PurchaseSale, "F2").text = "{0:.4f}".format(trade["quantity"]) F3 = xml.etree.ElementTree.SubElement( PurchaseSale, "F3").text = "{0:.4f}".format(trade["trade_price_eur"]) F9 = xml.etree.ElementTree.SubElement( PurchaseSale, "F9").text = "true" if trade["leverage"] > 1 else "false" else: PurchaseSale = xml.etree.ElementTree.SubElement( TShortSubItem, "Purchase") F4 = xml.etree.ElementTree.SubElement( PurchaseSale, "F4").text = trade["trade_date"].strftime( EDAVKI_DATETIME_FORMAT) F5 = xml.etree.ElementTree.SubElement(PurchaseSale, "F5").text = "A" F6 = xml.etree.ElementTree.SubElement( PurchaseSale, "F6").text = "{0:.4f}".format(-trade["quantity"]) F7 = xml.etree.ElementTree.SubElement( PurchaseSale, "F7").text = "{0:.4f}".format(trade["trade_price_eur"]) F8Value += trade["quantity"] F8 = xml.etree.ElementTree.SubElement( TShortSubItem, "F8").text = "{0:.4f}".format(F8Value) # trades # shortDerivateTrades xmlString = xml.etree.ElementTree.tostring(envelope) prettyXmlString = minidom.parseString(xmlString).toprettyxml(indent="\t") with open("output/D-IFI.xml", "w", encoding="utf-8") as f: f.write(prettyXmlString) print("output/D-IFI.xml created") ########### ########### Doh-Div ########### """ Get dividends from XLSX """ openPositions = {} dividends = [] for transactionSheet in transactionList: if transactionSheet is None: continue for xlsTransaction in transactionSheet: # Date Account Balance Type Details Position ID Amount Realized Equity Change Realized Equity NWA if xlsTransaction.details is None: continue if xlsTransaction.type == "Open Position" or xlsTransaction.type == "Profit/Loss of Trade": details_split = xlsTransaction.details.split("/", 1) position_id = int(xlsTransaction.position_id) open_pos = { "date": datetime.datetime.strptime( xlsTransaction.date, ETORO_TRANSACTION_DATETIME_FORMAT), "symbol": details_split[0].upper(), "currency": details_split[1] } openPositions[position_id] = open_pos continue if xlsTransaction.details.find("dividend") < 0: continue date = datetime.datetime.strptime( xlsTransaction.date, ETORO_TRANSACTION_DATETIME_FORMAT) if date.year != reportYear: # print("Skipping dividend (year: " + str(date.year) + "): " + str(xlsTransaction)) continue position_id = int(xlsTransaction.position_id) rate = get_exchange_rate(rates, date, ETORO_CURRENCY) amount_eur = str2float(xlsTransaction.amount) / rate open_pos = openPositions[position_id] symbol = open_pos["symbol"] name = None if position_id in allTradesByPositionID: info = allTradesByPositionID[position_id] name = info["name"] if name is None and symbol in allTradesBySymbol: info = allTradesBySymbol[symbol] name = info["name"] dividend = { "position_id": position_id, "amount_eur": amount_eur, "date": date, "name": name, "symbol": symbol, "currency": open_pos["currency"], } dividends.append(dividend) """ Merge multiple dividends or payments in lieu of dividends on the same day from the same company into a single entry """ mergedDividends = [] for dividend in dividends: merged = False for mergedDividend in mergedDividends: if dividend["date"].date() == mergedDividend["date"].date( ) and dividend["symbol"] == mergedDividend["symbol"]: mergedDividend["amount_eur"] = mergedDividend[ "amount_eur"] + dividend["amount_eur"] if "positions" in mergedDividend: mergedDividend["positions"].append(dividend["position_id"]) else: mergedDividend["positions"] = [ mergedDividend["position_id"], dividend["position_id"] ] merged = True break if not merged: mergedDividends.append(dividend) dividends = mergedDividends print("") rows = [] trimonth = -1 table = prettytable.PrettyTable([ "Date", "Symbol", "Company/Name", "Dividend [EUR]", "Orig. currency", "Position ID(s)", "Info link" ]) for dividend in dividends: # if int((dividend["date"].month-1)/3) != trimonth: # trimonth = int((dividend["date"].month-1)/3) # table.add_row([ # "{0}. trimesečje".format(trimonth+1), # "------", # "-------------------------------", # "--------------", # "--------------", # "--------------", # "---------------------------------------" # ]) row = [ dividend["date"].strftime(EDAVKI_DATETIME_FORMAT), dividend["symbol"], (dividend["name"] if not dividend["name"] is None else ""), "{0:.4f}".format(dividend["amount_eur"]), dividend["currency"], dividend["position_id"] if not "positions" in dividend else ", ".join(map(str, dividend["positions"])), "https://app.ft.com/tearsheet/{0}/profile".format( dividend["symbol"]) ] rows.append(row) table.add_row(row) table.align["Date"] = "r" table.align["Symbol"] = "l" table.align["Company/Name"] = "l" table.align["Dividend [EUR]"] = "r" table.align["Position ID(s)"] = "l" table.align["Info link"] = "l" #table.float_format["Dividend [EUR]"] = ".4" print(table) """ for dividend in dividends: print("{2}:\tSymbol: {4},\t\tDividend: {0:.4f} EUR (orig: {5}),\t\tCompany: {1},\t\tPosition ID(s): {3}".format( dividend["amount_eur"], (dividend["name"] if not dividend["name"] is None else ""), dividend["date"].strftime(EDAVKI_DATETIME_FORMAT), dividend["position_id"] if not "positions" in dividend else ", ".join(map(str, dividend["positions"])), dividend["symbol"], dividend["currency"])) """ """ Save dividend info to XLS """ wb = DividendsWorkbook( template_styles=DefaultStyleSet(NamedStyle(name="hyperlink"))) wb.dividends.write(objects=rows) filename = "output/Dividende-{0}.xlsx".format(reportYear) wb.save(filename) print("{0} created ".format(filename)) print( "\neToro ne razkrije količino davka, ki je avtomatsko odveden v državi iz katere izhaja dividenda. Vse dividende\n" "v eToro izpisu so neto. Za olajšavo pri eDavkih je potrebno izračunat koliko davka je bilo odvedenega in pripisat\n" "konvencijo (UL, št.) o preprečevanju dvojnega obdavčevanja. Za davek po državah in konvencije glej KIDO_info.xlsx.\n\n" "Dodatni info: https://www.etoro.com/customer-service/help/1484910272/how-much-tax-is-deducted-from-my-dividends/" ) sys.exit(0)