def getCoinPrices(coins): if settings.mySettings.proxies(): cg.proxies = settings.mySettings.proxies() else: cg.proxies = {} # try to load price from coingecko coinIds = coinSymbolsToIds(coins) try: response = cg.get_price( ids=coinIds, vs_currencies=[key for key in core.CoinValue()], include_market_cap='true', include_24hr_vol='true', include_24hr_change='true', include_last_updated_at='true') prices = {} for coinId in response: coinSymbol = coinIdToSymbol(coinId) prices[coinSymbol] = {} for key in core.CoinValue(): prices[coinSymbol][key] = {} prices[coinSymbol][key]['PRICE'] = response[coinId][ key.lower()] prices[coinSymbol][key]['CHANGEPCT24HOUR'] = response[coinId][ key.lower() + '_24h_change'] return prices except Exception as ex: logger.globalLogger.warning('error loading prices: ' + str(ex)) return {}
def data(self, index, role=qt.DisplayRole): if index.parent().isValid(): # child level if role == qt.DisplayRole: if index.column() == 0: # wallet properties wallet = self.coins[index.parent().row()].wallets[index.row()] return QWalletPropertiesData(walletname=wallet.getWalletName(), notes=wallet.notes, taxLimitEnabled=wallet.taxYearLimitEnabled, taxLimitYears=wallet.taxYearLimit) if index.column() == 3: # wallet chart return self.coins[index.parent().row()].wallets[index.row()] else: # top level if role == qt.DisplayRole: if index.column() == 0: # coin row return self.coins[index.row()].coinname # return coinname if index.column() == 1: # balance row return self.coins[index.row()] # return CoinBalance if index.column() == 2: # profit row return self.coins[index.row()].getTotalProfit() # return profit if index.column() >= self.firstValueColumn: # return CoinBalance and key keys = [*core.CoinValue().value] return self.coins[index.row()], keys[index.column() - self.firstValueColumn], self.valueCols if role == qt.DecorationRole: if index.column() == 0: coinIcon = self.coins[index.row()].coinIcon if coinIcon: return coinIcon else: return qtcore.QVariant() return qtcore.QVariant()
def getCoinPrices(coins): ccCoins = [] for coin in coins: ccCoins.append(coinSwapToCryptocompare(coin)) if settings.mySettings.proxies(): proxies = settings.mySettings.proxies() else: proxies = {} # try to load price from cryptocompare try: response = cryptcomp.get_price(ccCoins, [key for key in core.CoinValue()], full=True, proxies=proxies, timeout=10) except Exception as ex: logger.globalLogger.warning('error loading prices: ' + str(ex)) return {} if response and 'RAW' in response: prices = {} for ccCoin in response['RAW']: coin = coinSwapFromCryptoCompare(ccCoin) prices[ccCoin] = response['RAW'][ccCoin] prices[coin] = response['RAW'][ccCoin] return prices else: if response: logger.globalLogger.warning('error loading prices: ' + str(response)) else: logger.globalLogger.warning('error loading prices') return {}
def modelCallback_TradeList(headernames, dataFrame): tradeList = core.TradeList() feeList = core.TradeList() skippedRows = 0 for row in range(dataFrame.shape[0]): try: trade = core.Trade() # date trade.date = convertDate(dataFrame[headernames[0]][row]) # type trade.tradeType = dataFrame[headernames[1]][row] trade.coin = dataFrame[headernames[2]][row] trade.amount = float(dataFrame[headernames[3]][row]) trade.valueLoaded = False if headernames[6]: valueLoaded = dataFrame[headernames[6]][row] if valueLoaded: headers = dataFrame.columns.tolist() keysImport = [] valueHeaders = [] for header in headers: # check all header for included historical values valueMatch = pat.TRADELIST_VALUE_REGEX.match(header) if valueMatch: valueHeaders.append(valueMatch.group(0)) keysImport.append(valueMatch.group(1)) trade.valueLoaded = True for key in core.CoinValue(): # check if all needed currencies are included if key not in keysImport: trade.valueLoaded = False for valueInd in range(len(valueHeaders)): # load all included historical values trade.setValue(keysImport[valueInd], float(dataFrame[valueHeaders[valueInd]][row])) # exchange if headernames[7]: exchange = str(dataFrame[headernames[7]][row]) if exchange != 'nan': trade.exchange = exchange # extern id if headernames[8]: externId = str(dataFrame[headernames[8]][row]) if externId != 'nan': trade.externId = externId # wallet if headernames[9]: wallet = str(dataFrame[headernames[9]][row]) if wallet != 'nan': trade.wallet = wallet # id if headernames[4]: trade.tradeID = str(dataFrame[headernames[4]][row]) else: trade.generateID() # partner id if headernames[5]: tradePartnerId = str(dataFrame[headernames[5]][row]) if tradePartnerId != 'nan': trade.tradePartnerId = tradePartnerId swapCoinName(trade) if trade.tradeType == 'fee': feeList.addTrade(trade) else: tradeList.addTrade(trade) except Exception as ex: localLogger.warning('error in Converter: ' + str(ex)) skippedRows += 1 return tradeList, feeList, skippedRows
def createProfitExcel(coinList, path, minDate, maxDate, currency='EUR', taxyearlimit=1, includeTaxFreeTrades=True, lang="en", translator=None): if translator: def trans(text): return translator.translate(text, lang) wb = openpyxl.Workbook(write_only=False) wb.remove(wb.active) # %% profit sheets profitSumColumn = 'O' profitSumRows = [] profitSumColumns = [] for coin in coinList.coins: if not coin.isFiat(): # check if there are sells in the given timeframe validSellFound = False for sell in coin.tradeMatcher.sellsMatched: if sell.date >= minDate and sell.date <= maxDate: validSellFound = True break if validSellFound: wsname = re.sub('[^A-Za-z0-9]+', '', coin.coinname) ws = wb.create_sheet(wsname) # write top header # cell = WriteOnlyCell(ws) if translator: ws.append([ '', '', '', trans('Buy'), '', '', '', '', trans('Sell'), '', '', '', '', trans('Profit'), '' ]) else: ws.append([ '', '', '', 'Ankauf', '', '', '', '', 'Verkauf', '', '', '', '', 'Profit', '' ]) ws.merge_cells('A1:B1') ws.merge_cells('D1:G1') ws.merge_cells('I1:L1') ws.merge_cells('N1:O1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') headings = [ws['A1'], ws['D1'], ws['I1'], ws['N1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment # blue, green, yellow, purple headingColors = [ 'FF61D2FF', 'FF6DE992', 'FFFFFF52', 'FFE057FF' ] for i in range(len(headings)): headings[i].fill = PatternFill( fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append([ '', '', '', trans('Date'), trans('Amount'), trans('Price'), trans('Value'), '', trans('Date'), trans('Amount'), trans('Price'), trans('Value'), '', trans('Profit'), trans('tax relevant') ]) ws.append([ '', '', '', '', trans('in') + ' ' + trans('pc'), trans('in') + ' ' + currency + '/' + trans('pc'), trans('in') + ' ' + currency, '', '', trans('in') + ' ' + trans('pc'), trans('in') + ' ' + currency + '/' + trans('pc'), trans('in') + ' ' + currency, '', trans('in') + ' ' + currency, trans('in') + ' ' + currency ]) else: ws.append([ '', '', '', 'Datum', 'Anzahl', 'Preis', 'Wert', '', 'Datum', 'Anzahl', 'Preis', 'Wert', '', 'Gewinn', 'zu versteuern' ]) ws.append([ '', '', '', '', 'in Stk', 'in ' + currency + '/Stk', 'in ' + currency, '', '', 'in Stk', 'in ' + currency + '/Stk', 'in ' + currency, '', 'in ' + currency, 'in ' + currency ]) # coinname ws.append([coin.coinname, '']) firstProfitRow = ws.max_row + 1 # write data for irow in range(len(coin.tradeMatcher.profitMatched)): sell = coin.tradeMatcher.sellsMatched[irow] # check date of sell if sell.date >= minDate and sell.date <= maxDate: buy = coin.tradeMatcher.buysMatched[irow] profit = coin.tradeMatcher.profitMatched[irow] # if taxyearlimit is given # if limit is relevant if taxyearlimit and ( (sell.date - relativedelta(years=taxyearlimit)) > buy.date): # if taxyearlimit is given taxProfit = 0 if includeTaxFreeTrades: ws.append([ '', '', '', buy.date, buy.amount, buy.getPrice()[currency], buy.value[currency], '', sell.date, sell.amount, sell.getPrice()[currency], sell.value[currency], '', round(profit[currency], 3), round(taxProfit, 3) ]) else: taxProfit = profit[currency] ws.append([ '', '', '', buy.date, buy.amount, buy.getPrice()[currency], buy.value[currency], '', sell.date, sell.amount, sell.getPrice()[currency], sell.value[currency], '', round(profit[currency], 3), round(taxProfit, 3) ]) profitSumRows.append(ws.max_row + 2) profitSumColumns.append(15) # ws['M' + str(profitSumRows[-1])] = 'Summe' ws[profitSumColumn + str(profitSumRows[-1] )] = '=ROUNDDOWN(SUM(' + profitSumColumn + str( firstProfitRow) + ':' + profitSumColumn + str( profitSumRows[-1] - 2) + '),2)' # set width of date columns ws.column_dimensions['D'].width = 11 ws.column_dimensions['I'].width = 11 # set width of gap columns gapColumns = ['C', 'H', 'M', 'P'] gapFill = PatternFill(fill_type='solid', start_color='FFC8C8C8', end_color='FFC8C8C8') for gapColumn in gapColumns: ws.column_dimensions[gapColumn].width = 4 # set gap color for cell in ws[gapColumn]: cell.fill = gapFill # page setup # ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE # ws.page_setup.fitToWidth = 1 pageSetup(ws) # %% fee sheets feeSumColumn = 'G' feeSumRows = [] for coin in coinList.coins: fees = coin.getFees() feeSum = core.CoinValue() for fee in fees: if fee.date.date() >= minDate and fee.date.date() <= maxDate: feeSum.add(fee.value) if feeSum[currency] != 0: # if fees have been paid wsname = re.sub('[^A-Za-z0-9]+', '', coin.coinname) ws = wb.create_sheet(wsname + '_fees') # write top header # cell = WriteOnlyCell(ws) if translator: ws.append(['', '', '', trans('Fees'), '', '', '', '']) else: ws.append(['', '', '', 'Gebühren', '', '', '', '']) ws.merge_cells('A1:B1') ws.merge_cells('D1:G1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') headings = [ws['A1'], ws['D1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment # blue, purple headingColors = ['FF61D2FF', 'FFE057FF'] for i in range(len(headings)): headings[i].fill = PatternFill(fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append([ '', '', '', trans('Date'), trans('Fee'), '', trans('Fee'), '' ]) ws.append([ '', '', '', '', trans('in') + ' ' + trans('pc'), '', trans('in') + ' ' + currency, '' ]) else: ws.append(['', '', '', 'Datum', 'Gebühr', '', 'Gebühr', '']) ws.append(['', '', '', '', 'in Stk', '', 'in ' + currency, '']) # coinname ws.append([coin.coinname, '']) firstProfitRow = ws.max_row + 1 # write data for fee in coin.getFees(): # check date of sell if fee.date.date() >= minDate and fee.date.date() <= maxDate: ws.append([ '', '', '', fee.date, fee.amount, '', round(fee.value[currency], 3), '' ]) profitSumRows.append(ws.max_row + 2) profitSumColumns.append(7) # ws['M' + str(profitSumRows[-1])] = 'Summe' ws[feeSumColumn + str( profitSumRows[-1])] = '=ROUNDDOWN(SUM(' + feeSumColumn + str( firstProfitRow) + ':' + feeSumColumn + str( profitSumRows[-1] - 2) + '),2)' # set width of date columns ws.column_dimensions['D'].width = 11 # set width of gap columns gapColumns = ['C', 'H'] gapFill = PatternFill(fill_type='solid', start_color='FFC8C8C8', end_color='FFC8C8C8') for gapColumn in gapColumns: ws.column_dimensions[gapColumn].width = 4 # set gap color for cell in ws[gapColumn]: cell.fill = gapFill # page setup # ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE # ws.page_setup.fitToWidth = 1 pageSetup(ws) # %% overview sheet ws = wb.create_sheet('Overview', 0) # write top header if translator: ws.append(['', '', '', trans('Profit'), '', '']) else: ws.append(['', '', '', 'Profit', '', '']) ws.merge_cells('A1:B1') ws.merge_cells('D1:E1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') # blue, purple headings = [ws['A1'], ws['D1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment headingColors = ['FF61D2FF', 'FFE057FF'] for i in range(len(headings)): headings[i].fill = PatternFill(fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append(['', '', '', trans('Group'), trans('Profit'), '']) ws.append(['', '', '', '', trans('in') + ' ' + currency, '']) ws.append([trans('Timeframe'), str(minDate) + ' : ' + str(maxDate)]) else: ws.append(['', '', '', 'Gruppe', 'Gewinn', '']) ws.append(['', '', '', '', 'in ' + currency, '']) ws.append(['Zeitraum', str(minDate) + ' : ' + str(maxDate)]) firstProfitRow = ws.max_row + 1 # write data sheets = wb.sheetnames[1:] for isheet in range(len(sheets)): # profit = wb[sheets[isheet]][profitSumColumn + str(profitSumRows[isheet])].value # =INDIREKT("ETH!"&ADRESSE(13;16;4)) profit = '=INDIRECT(\"' + sheets[isheet] + '!\"&ADDRESS(' + str( profitSumRows[isheet]) + ',' + str( profitSumColumns[isheet]) + ',4))' ws.append(['', '', '', sheets[isheet], profit]) profitSumRow = ws.max_row + 2 profitSumColumn = 'E' # ws['M' + str(profitSumRows[-1])] = 'Summe' ws[profitSumColumn + str(profitSumRow)] = '=SUM(' + profitSumColumn + str( firstProfitRow) + ':' + profitSumColumn + str(profitSumRow - 2) + ')' # set width of gap columns gapColumns = ['C', 'F'] gapFill = PatternFill(fill_type='solid', start_color='FFC8C8C8', end_color='FFC8C8C8') for gapColumn in gapColumns: ws.column_dimensions[gapColumn].width = 4 # set gap color for cell in ws[gapColumn]: cell.fill = gapFill # page setup pageSetup(ws) def textLen(value): if value is None: return 5 elif isinstance(value, float): # return len(str(value))/2 return 5 elif isinstance(value, str) and '=' in value: return 10 else: length = len(str(value)) + 2 if length < 5: length = 5 return length for ws in wb: for column_cells in ws.columns: length = max(textLen(cell.value) for cell in column_cells[1:]) ws.column_dimensions[str( column_cells[1].column_letter)].width = length # save file # wb.save(path + '-' + str(datetime.datetime.now()).replace(' ', '_').replace(':', '_').replace('-', '_').replace('.', # '_') + '.xlsx') wb.save(path)
def initDisplayCurrencies(self): self.keys = [*core.CoinValue().value] self.header = ['id', 'partner id', 'date', 'type', 'coin', 'amount', 'exchange', 'wallet'] + ['value ' + key for key in self.keys] self.headerLen = len(self.header)
def __init__(self, controller, *args, **kwargs): super(QExportProfitFrame, self).__init__(controller=controller, *args, **kwargs) self.fileDialog = qtwidgets.QFileDialog(self) # self.fileDialog.setDirectory(self.controller.appPath) # title self.titleLabel = qtwidgets.QLabel("export profit", self) font = self.titleLabel.font() font.setPointSize(14) self.titleLabel.setFont(font) self.headingLayout = qtwidgets.QHBoxLayout() self.headingLayout.addStretch() self.headingLayout.addWidget(self.titleLabel) self.headingLayout.addStretch() # start and end date self.fromDateLabel = qtwidgets.QLabel("start: ", self) self.toDateLabel = qtwidgets.QLabel("end: ", self) self.fromDateEdit = qtwidgets.QDateEdit(self) self.fromDateEdit.setCalendarPopup(True) self.toDateEdit = qtwidgets.QDateEdit(self) self.toDateEdit.setCalendarPopup(True) self.dateLayout = qtwidgets.QHBoxLayout() self.dateLayout.addWidget(self.fromDateLabel) self.dateLayout.addWidget(self.fromDateEdit) self.dateLayout.addStretch() self.dateLayout.addWidget(self.toDateLabel) self.dateLayout.addWidget(self.toDateEdit) # self.dateLayout.addStretch() # year self.yearLabel = qtwidgets.QLabel("year: ", self) self.yearDateEdit = qtwidgets.QSpinBox(self) self.yearDateEdit.valueChanged.connect(self.yearChanged) self.yearDateEdit.setMinimum(0) self.yearDateEdit.setMaximum(datetime.datetime.now().year) self.yearDateEdit.setValue(datetime.datetime.now().year) self.yearLayout = qtwidgets.QHBoxLayout() self.yearLayout.addStretch() self.yearLayout.addWidget(self.yearLabel) self.yearLayout.addWidget(self.yearDateEdit) self.yearLayout.addStretch() self.optionsLayout = qtwidgets.QGridLayout() # currency self.currencyLabel = qtwidgets.QLabel("currency", self) self.currencyBox = qtwidgets.QComboBox(self) listModel = qtcore.QStringListModel() currencys = list(core.CoinValue()) listModel.setStringList(currencys) self.currencyBox.setModel(listModel) defaultCurrency = settings.mySettings.reportCurrency() self.currencyBox.setCurrentIndex(currencys.index(defaultCurrency)) self.optionsLayout.addWidget(self.currencyLabel, 0, 1) self.optionsLayout.addWidget(self.currencyBox, 0, 2) # language self.languageLabel = qtwidgets.QLabel("language", self) self.languageBox = qtwidgets.QComboBox(self) lanListModel = qtcore.QStringListModel() languages = self.controller.exportTranslator.getLanguages() lanListModel.setStringList(languages) self.languageBox.setModel(lanListModel) defaultLanguage = settings.mySettings.getTaxSetting('exportLanguage') self.languageBox.setCurrentIndex(languages.index(defaultLanguage)) self.optionsLayout.addWidget(self.languageLabel, 1, 1) self.optionsLayout.addWidget(self.languageBox, 1, 2) # tax timelimit self.timeLimitLabel = qtwidgets.QLabel("tax year limit", self) self.timeLimitBox = qtwidgets.QCheckBox(self) self.timeLimitEdit = qtwidgets.QSpinBox(self) self.timeLimitEdit.setValue( settings.mySettings.getTaxSetting('taxfreelimityears')) self.timeLimitEdit.setMinimum(0) if settings.mySettings.getTaxSetting('taxfreelimit'): self.timeLimitBox.setCheckState(qt.Checked) else: self.timeLimitBox.setCheckState(qt.Unchecked) self.optionsLayout.addWidget(self.timeLimitBox, 2, 0) self.optionsLayout.addWidget(self.timeLimitLabel, 2, 1) self.optionsLayout.addWidget(self.timeLimitEdit, 2, 2) # use wallet tax free limit self.useWalletTaxLimitLabel = qtwidgets.QLabel( "use wallet tax year limit", self) self.useWalletTaxLimitBox = qtwidgets.QCheckBox(self) if settings.mySettings.getTaxSetting('usewallettaxfreelimityears'): self.useWalletTaxLimitBox.setCheckState(qt.Checked) else: self.useWalletTaxLimitBox.setCheckState(qt.Unchecked) self.optionsLayout.addWidget(self.useWalletTaxLimitBox, 3, 0) self.optionsLayout.addWidget(self.useWalletTaxLimitLabel, 3, 1) # include tax free trades self.taxFreeTradesLabel = qtwidgets.QLabel("include tax free trades", self) self.taxFreeTradesBox = qtwidgets.QCheckBox(self) self.taxFreeTradesBox.setCheckState(qt.Checked) self.optionsLayout.addWidget(self.taxFreeTradesBox, 4, 0) self.optionsLayout.addWidget(self.taxFreeTradesLabel, 4, 1) # self.taxFreeTradesLayout.addStretch() # todo: add export checkboxes # include fees # label # checkbox # include exchanges # label # checkbox # daywise matching # label # checkbox self.timeLimitBox.stateChanged.connect(self.timeLimitCheckBoxChanged) self.timeLimitCheckBoxChanged() self.useWalletTaxLimitBox.stateChanged.connect( self.useWalletTaxLimitChanged) self.useWalletTaxLimitChanged() # export button self.exportProfitButton = qtwidgets.QPushButton("export", self) self.exportProfitButton.clicked.connect(self.exportProfit) self.buttonLayout = qtwidgets.QHBoxLayout() self.buttonLayout.addStretch() self.buttonLayout.addWidget(self.exportProfitButton) self.buttonLayout.addStretch() self.optionsHorzLayout = qtwidgets.QHBoxLayout() self.optionsHorzLayout.addLayout(self.optionsLayout) self.optionsHorzLayout.addStretch() self.vertLayout = qtwidgets.QVBoxLayout(self) self.vertLayout.addLayout(self.headingLayout) self.vertLayout.addLayout(self.yearLayout) self.vertLayout.addLayout(self.dateLayout) self.vertLayout.addLayout(self.optionsHorzLayout) # self.vertLayout.addLayout(self.currencyLayout) # self.vertLayout.addLayout(self.timeLimitLayout) # self.vertLayout.addLayout(self.taxFreeTradesLayout) # self.vertLayout.addLayout(self.) self.vertLayout.addStretch() self.vertLayout.addLayout(self.buttonLayout)
def coinTableChangedSlot(self): # update new values # todo: display all displayCurrencies taxCoinName = settings.mySettings.reportCurrency() numberOfDecimals = 4 # initialize local vars # total invested fiat totalInvestFiat = core.CoinValue() # total returned fiat totalReturnFiat = core.CoinValue() # fiat performance # fiatPerformance = core.CoinValue() # invested value of current holdings currentInvestNoFiat = core.CoinValue() # current value of current holdings (hypothetical) hypotheticalCoinValueNoFiat = core.CoinValue() # current performance of current holdings (hypothetical) # hypotheticalPerformanceNoFiat = core.CoinValue() # realized profit (relevant for tax) realizedProfit = core.CoinValue() # unrealized profit (would be relevant for tax if realized) # unrealizedProfit = core.CoinValue() # paid fees paidFees = core.CoinValue() # testing # currentInvestAll = core.CoinValue() # hypotheticalValueAll = core.CoinValue() # realizedProfitAll = core.CoinValue() if self.controller.tradeList.isEmpty(): startYear = datetime.datetime.now().year else: startYear = min([trade.date for trade in self.controller.tradeList]).year stopYear = datetime.datetime.now().year realizedProfitPerYear = {} paidFeesPerYear = {} fiatPerYear = {} taxProfitPerYear = {} rewardPerYear = {} for year in range(startYear, stopYear+1): realizedProfitPerYear[str(year)] = core.CoinValue() paidFeesPerYear[str(year)] = core.CoinValue() fiatPerYear[str(year)] = core.CoinValue() taxProfitPerYear[str(year)] = core.CoinValue() rewardPerYear[str(year)] = core.CoinValue() # calculate all needed values for coin in self.model: if coin.isFiat(): # calculate invested and returned fiat for trade in coin.trades: # only add fiat trade if partner trade is not fiat isFiatCryptoTrade = True if trade.tradePartnerId: if self.controller.tradeList.getTradeById(trade.tradePartnerId): if self.controller.tradeList.getTradeById(trade.tradePartnerId).isFiat(): isFiatCryptoTrade = False if isFiatCryptoTrade: if trade.amount < 0: totalInvestFiat.add(trade.getValue().mult(-1)) else: totalReturnFiat.add(trade.getValue()) # fiat invest/ return per year for year in range(startYear, stopYear + 1): startDate = datetime.date(year=year, month=1, day=1) endDate = datetime.date(year=year, month=12, day=31) if trade.date.date() >= startDate and trade.date.date() <= endDate: fiatPerYear[str(year)].add(trade.getValue()) else: # calculate value of portfolio currentInvestNoFiat.add(coin.initialValue) hypotheticalCoinValueNoFiat.add(coin.getCurrentValue()) realizedProfit.add(coin.getTotalProfit()) # calc fees per year for trade in coin.trades: if trade.tradeType == "fee": paidFees.add(trade.getValue()) for year in range(startYear, stopYear + 1): startDate = datetime.date(year=year, month=1, day=1) endDate = datetime.date(year=year, month=12, day=31) if trade.date.date() >= startDate and trade.date.date() <= endDate: paidFeesPerYear[str(year)].add(trade.getValue()) for year in range(startYear, stopYear + 1): startDate = datetime.date(year=year, month=1, day=1) endDate = datetime.date(year=year, month=12, day=31) taxProfitPerYear[str(year)].add(coin.getTimeDeltaProfitTaxable(startDate, endDate)) rewardPerYear[str(year)].add(coin.getTimeDeltaReward(startDate, endDate)) realizedProfitPerYear[str(year)].add(coin.getTimeDeltaProfit(startDate, endDate)) # fiat and coins # currentInvestAll.add(coin.initialValue) # hypotheticalValueAll.add(coin.getCurrentValue()) # realizedProfitAll.add(coin..getTotalProfit()) fiatPerformance = (totalReturnFiat-totalInvestFiat).div(totalInvestFiat).mult(100) hypotheticalPerformanceNoFiat = (hypotheticalCoinValueNoFiat.div(currentInvestNoFiat) - core.CoinValue().setValue(1)).mult(100) unrealizedProfit = hypotheticalCoinValueNoFiat - currentInvestNoFiat def setLabelColor(label, isPositiv): if isPositiv: label.setBodyColor(self.posColor.name()) else: label.setBodyColor(self.negColor.name()) # tax value chart self.currentValueChart.chartView.setText( [controls.floatToString(currentInvestNoFiat[taxCoinName], numberOfDecimals) + ' ' + taxCoinName, controls.floatToString(hypotheticalCoinValueNoFiat[taxCoinName], numberOfDecimals) + ' ' + taxCoinName, "%.2f%%" % hypotheticalPerformanceNoFiat[taxCoinName]]) self.currentValueChart.setLabelToolTip(['current invest', 'current value', 'performance']) if unrealizedProfit[taxCoinName] < 0: self.donutSliceInvested.setValue(currentInvestNoFiat[taxCoinName]+unrealizedProfit[taxCoinName]) self.donutSliceInvested.setColor(self.neutrColor) self.donutSlicePerformance.setValue(-unrealizedProfit[taxCoinName]) self.donutSlicePerformance.setColor(self.negColor) # self.donutSlicePerformance.setLabelColor(self.negColor) self.currentValueChart.chartView.setColor([self.neutrColor, self.negColor, self.negColor]) else: self.donutSliceInvested.setValue(currentInvestNoFiat[taxCoinName]) self.donutSliceInvested.setColor(self.neutrColor) self.donutSlicePerformance.setValue(unrealizedProfit[taxCoinName]) self.donutSlicePerformance.setColor(self.posColor) # self.donutSlicePerformance.setLabelColor(self.posColor) self.currentValueChart.chartView.setColor([self.neutrColor, self.posColor, self.posColor]) # fiat value chart self.currentFiatValueChart.chartView.setText( [controls.floatToString(totalInvestFiat[taxCoinName], numberOfDecimals) + ' ' + taxCoinName, controls.floatToString(hypotheticalCoinValueNoFiat[taxCoinName], numberOfDecimals) + ' ' + taxCoinName, controls.floatToString(totalReturnFiat[taxCoinName], numberOfDecimals) + ' ' + taxCoinName], qt.AlignCenter) self.currentFiatValueChart.setLabelToolTip(['fiat invest', 'current value', 'fiat return']) self.sliceFiatInvested.setValue(totalInvestFiat[taxCoinName]) self.sliceFiatInvested.setColor(self.neutrColor) self.sliceFiatReturn.setValue(totalReturnFiat[taxCoinName]) self.sliceFiatReturn.setColor(self.styleHandler.getQColor('PRIMARY')) self.sliceCoinValue.setValue(hypotheticalCoinValueNoFiat[taxCoinName]) if (hypotheticalCoinValueNoFiat[taxCoinName] + totalReturnFiat[taxCoinName]) \ < totalInvestFiat[taxCoinName]: self.sliceCoinValue.setColor(self.negColor) self.currentFiatValueChart.chartView.setColor([self.neutrColor, self.negColor, self.styleHandler.getQColor('PRIMARY')]) else: self.sliceCoinValue.setColor(self.posColor) self.currentFiatValueChart.chartView.setColor([self.neutrColor, self.posColor, self.styleHandler.getQColor('PRIMARY')]) # profit table years = [] for year in realizedProfitPerYear: years.append(year) self.profitTable.setRowCount(len(years)) self.profitTable.setVerticalHeaderLabels(years) for year, row in zip(realizedProfitPerYear, range(len(realizedProfitPerYear))): self.profitTable.setItem(row, 0, qtwidgets.QTableWidgetItem( controls.floatToString(realizedProfitPerYear[year][taxCoinName] + rewardPerYear[year][taxCoinName], 5) + ' ' + taxCoinName)) self.profitTable.setItem(row, 1, qtwidgets.QTableWidgetItem( controls.floatToString(taxProfitPerYear[year][taxCoinName] + rewardPerYear[year][taxCoinName], 5) + ' ' + taxCoinName)) self.profitTable.setItem(row, 2, qtwidgets.QTableWidgetItem( controls.floatToString(paidFeesPerYear[year][taxCoinName], 5) + ' ' + taxCoinName)) self.profitTable.setItem(row, 3, qtwidgets.QTableWidgetItem( controls.floatToString(fiatPerYear[year][taxCoinName], 5) + ' ' + taxCoinName)) # pie chart pieSeries = qtchart.QPieSeries() sortedModelIndex = sorted(range(len(self.model)), key=lambda x: self.model.coins[x].getCurrentValue()[taxCoinName], reverse=True) otherssum = core.CoinValue() try: topvalue = self.model.coins[sortedModelIndex[0]].getCurrentValue()[taxCoinName] except IndexError: topvalue = 0 for index in sortedModelIndex: coin = self.model.coins[index] if not coin.isFiat(): if coin.getCurrentValue()[taxCoinName] > topvalue/40 and \ coin.getCurrentValue()[taxCoinName] > abs(hypotheticalCoinValueNoFiat[taxCoinName]/75): pieSeries.append(coin.coinname, coin.getCurrentValue()[taxCoinName]) elif coin.getCurrentValue()[taxCoinName] > 0: otherssum.add(coin.getCurrentValue()) if otherssum[taxCoinName] > abs(hypotheticalCoinValueNoFiat[taxCoinName]/100): slice = pieSeries.append("others", otherssum[taxCoinName]) slice.setLabelVisible() # if len(pieSeries.slices()) > 5: # for slice in pieSeries.slices()[0:5]: # if slice.value() > hypotheticalCoinValueNoFiat[taxCoinName]/20: # slice.setLabelVisible() # else: for slice in pieSeries.slices(): if slice.value() > abs(hypotheticalCoinValueNoFiat[taxCoinName]/20): slice.setLabelVisible() color = [255, 75, 225] for slice in pieSeries.slices(): color = style.nextColor(color, 55) slice.setBrush(qtgui.QColor(*tuple(color))) slice.setLabelColor(qtgui.QColor(*tuple(color))) slice.setLabelPosition(qtchart.QPieSlice.LabelOutside) pieSeries.setHoleSize(0.6) self.portfolioChart.setSeries(pieSeries) portfolioChartLabels = [] for coin in hypotheticalCoinValueNoFiat: portfolioChartLabels.append(controls.floatToString(hypotheticalCoinValueNoFiat[coin], numberOfDecimals) + ' ' + coin) self.portfolioChart.chartView.setText(portfolioChartLabels, qt.AlignCenter) self.portfolioChart.chartView.setColor(self.neutrColor, False)
def initDisplayCurrencies(self): self.keys = [*core.CoinValue().value] self.setColumnHeader(self.valueCols)
def createProfitExcel(coinList, path, minDate, maxDate, currency='EUR', taxyearlimit=1, useWalletTaxYearLimit=True, includeTaxFreeTrades=True, lang="en", translator=None): if translator: def trans(text): return translator.translate(text, lang) wb = openpyxl.Workbook(write_only=False) wb.remove(wb.active) tradeprofitSumRows = [] feeSumRows = [] rewardSumRows = [] tradeprofitSumColumns = [] feeSumColumns = [] rewardSumColumns = [] # %% profit sheets profitSumColumn = 'O' profitSumRows = [] profitSumColumns = [] for coinWallets in coinList.coins: # check if coin is report Currency if not coinWallets.isReportCurrency(): # check if there are sells in the given timeframe validSellFound = False for coin in coinWallets.wallets: for sell in coin.tradeMatcher.sellsMatched: if sell.date >= minDate and sell.date <= maxDate: validSellFound = True break if validSellFound: if useWalletTaxYearLimit: # get taxfreelimit from wallet if coin.taxYearLimitEnabled == True: taxyearlimit = coin.taxYearLimit else: taxyearlimit = None walletname = coin.getWalletName() wsname = re.sub('[^A-Za-z0-9_]+', '', walletname) ws = wb.create_sheet(wsname) # write top header # cell = WriteOnlyCell(ws) if translator: ws.append([ '', '', '', trans('Buy'), '', '', '', '', trans('Sell'), '', '', '', '', trans('Profit'), '' ]) else: ws.append([ '', '', '', 'Ankauf', '', '', '', '', 'Verkauf', '', '', '', '', 'Profit', '' ]) ws.merge_cells('A1:B1') ws.merge_cells('D1:G1') ws.merge_cells('I1:L1') ws.merge_cells('N1:O1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') headings = [ws['A1'], ws['D1'], ws['I1'], ws['N1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment # blue, green, yellow, purple headingColors = [ BLUECOLORCODE, GREENCOLORCODE, YELLOWCOLORCODE, PURPLECOLORCODE ] for i in range(len(headings)): headings[i].fill = PatternFill( fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append([ trans('Coin') + '/' + trans('Wallet'), '', '', trans('Date'), trans('Amount'), trans('Price'), trans('Value'), '', trans('Date'), trans('Amount'), trans('Price'), trans('Value'), '', trans('Profit'), trans('tax relevant') ]) ws.append([ '', '', '', '', trans('in') + ' ' + trans('pc'), trans('in') + ' ' + currency + '/' + trans('pc'), trans('in') + ' ' + currency, '', '', trans('in') + ' ' + trans('pc'), trans('in') + ' ' + currency + '/' + trans('pc'), trans('in') + ' ' + currency, '', trans('in') + ' ' + currency, trans('in') + ' ' + currency ]) else: ws.append([ 'Coin/Wallet', '', '', 'Datum', 'Anzahl', 'Preis', 'Wert', '', 'Datum', 'Anzahl', 'Preis', 'Wert', '', 'Gewinn', 'zu versteuern' ]) ws.append([ '', '', '', '', 'in Stk', 'in ' + currency + '/Stk', 'in ' + currency, '', '', 'in Stk', 'in ' + currency + '/Stk', 'in ' + currency, '', 'in ' + currency, 'in ' + currency ]) # walletname ws.append([wsname, '']) firstProfitRow = ws.max_row + 1 # write data rowcount = 0 for irow in range(len(coin.tradeMatcher.profitMatched)): sell = coin.tradeMatcher.sellsMatched[irow] # check amount of sell not zero (can happen for very small fees) if sell.amount > 0: # check date of sell if sell.date >= minDate and sell.date <= maxDate: buy = coin.tradeMatcher.buysMatched[irow] profit = coin.tradeMatcher.profitMatched[irow] # if taxyearlimit is given # if limit is relevant if taxyearlimit and ( (sell.date - relativedelta(years=taxyearlimit)) > buy.date): # if taxyearlimit is given taxProfit = 0 if includeTaxFreeTrades: rowcount += 1 ws.append([ '', '', '', buy.date, buy.amount, buy.getPrice()[currency], buy.value[currency], '', sell.date, sell.amount, sell.getPrice()[currency], sell.value[currency], '', round(profit[currency], 3), round(taxProfit, 3) ]) else: rowcount += 1 taxProfit = profit[currency] ws.append([ '', '', '', buy.date, buy.amount, buy.getPrice()[currency], buy.value[currency], '', sell.date, sell.amount, sell.getPrice()[currency], sell.value[currency], '', round(profit[currency], 3), round(taxProfit, 3) ]) if rowcount == 0: # no trades added: wb.remove(ws) else: profitSumRows.append(ws.max_row + 2) tradeprofitSumRows.append(profitSumRows[-1]) profitSumColumns.append(15) tradeprofitSumColumns.append(profitSumColumns[-1]) # ws['M' + str(profitSumRows[-1])] = 'Summe' ws[profitSumColumn + str( profitSumRows[-1] )] = '=ROUNDDOWN(SUM(' + profitSumColumn + str( firstProfitRow) + ':' + profitSumColumn + str( profitSumRows[-1] - 2) + '),2)' # page setup if translator: pageSetup(ws, dateCols=['D', 'I'], gapCols=['C', 'H', 'M', 'P'], lastRow=profitSumRows[-1], lastCol=profitSumColumns[-1] + 1, setWidthCols=['A', 'B', 'O'], setWidthValue=[ 15, 3, len(trans('tax relevant')) - 1 ], trans=trans) else: pageSetup(ws, dateCols=['D', 'I'], gapCols=['C', 'H', 'M', 'P'], lastRow=profitSumRows[-1], lastCol=profitSumColumns[-1] + 1, setWidthCols=['A', 'B', 'O'], setWidthValue=[15, 3, 11]) # %% fee sheets feeSumColumn = 'G' for coin in coinList.coins: fees = coin.getFees() feeSum = core.CoinValue() for fee in fees: if fee.date.date() >= minDate and fee.date.date() <= maxDate: feeSum.add(fee.value) if feeSum[currency] != 0: # if fees have been paid wsname = re.sub('[^A-Za-z0-9]+', '', coin.coinname) ws = wb.create_sheet(wsname + '_fees') # write top header # cell = WriteOnlyCell(ws) if translator: ws.append(['', '', '', trans('Fees'), '', '', '', '']) else: ws.append(['', '', '', 'Gebühren', '', '', '', '']) ws.merge_cells('A1:B1') ws.merge_cells('D1:G1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') headings = [ws['A1'], ws['D1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment # blue, purple headingColors = [BLUECOLORCODE, PURPLECOLORCODE] for i in range(len(headings)): headings[i].fill = PatternFill(fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append([ trans('Coin'), '', '', trans('Date'), trans('Fee'), '', trans('Fee'), '' ]) ws.append([ '', '', '', '', trans('in') + ' ' + trans('pc'), '', trans('in') + ' ' + currency, '' ]) else: ws.append( ['Coin', '', '', 'Datum', 'Gebühr', '', 'Gebühr', '']) ws.append(['', '', '', '', 'in Stk', '', 'in ' + currency, '']) # coinname ws.append([wsname, '']) firstProfitRow = ws.max_row + 1 # write data fees.sort(key=lambda x: x.date, reverse=False) for fee in fees: # check amount of fee not zero if fee.amount < 0: # check date of sell if fee.date.date() >= minDate and fee.date.date( ) <= maxDate: feedate = fee.date.astimezone( pytz.utc).replace(tzinfo=None).date() ws.append([ '', '', '', feedate, fee.amount, '', round(fee.value[currency], 3), '' ]) profitSumRows.append(ws.max_row + 2) feeSumRows.append(profitSumRows[-1]) profitSumColumns.append(7) feeSumColumns.append(profitSumColumns[-1]) # ws['M' + str(profitSumRows[-1])] = 'Summe' ws[feeSumColumn + str( profitSumRows[-1])] = '=ROUNDDOWN(SUM(' + feeSumColumn + str( firstProfitRow) + ':' + feeSumColumn + str( profitSumRows[-1] - 2) + '),2)' # page setup # page setup if translator: pageSetup(ws, dateCols=['D'], gapCols=['C', 'H'], lastRow=profitSumRows[-1], lastCol=profitSumColumns[-1] + 1, setWidthCols=['A', 'B', 'F'], setWidthValue=[13, 3, 3], trans=trans) else: pageSetup(ws, dateCols=['D'], gapCols=['C', 'H'], lastRow=profitSumRows[-1], lastCol=profitSumColumns[-1] + 1, setWidthCols=['A', 'B', 'F'], setWidthValue=[13, 3, 3]) # %% reward sheets rewardSumColumn = 'G' rewardSumRows = [] for coin in coinList.coins: rewards = coin.getRewards() rewardSum = core.CoinValue() for reward in rewards: if reward.date.date() >= minDate and reward.date.date() <= maxDate: rewardSum.add(reward.value) if rewardSum[currency] != 0: # if rewards have been received wsname = re.sub('[^A-Za-z0-9]+', '', coin.coinname) ws = wb.create_sheet(wsname + '_rewards') # write top header # cell = WriteOnlyCell(ws) if translator: ws.append(['', '', '', trans('Rewards'), '', '', '', '']) else: ws.append(['', '', '', 'Gebühren', '', '', '', '']) ws.merge_cells('A1:B1') ws.merge_cells('D1:G1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') headings = [ws['A1'], ws['D1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment # blue, purple headingColors = [BLUECOLORCODE, PURPLECOLORCODE] for i in range(len(headings)): headings[i].fill = PatternFill(fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append([ trans('Coin'), '', '', trans('Date'), trans('Reward'), '', trans('Reward'), '' ]) ws.append([ '', '', '', '', trans('in') + ' ' + trans('pc'), '', trans('in') + ' ' + currency, '' ]) else: ws.append( ['Coin', '', '', 'Datum', 'Gebühr', '', 'Gebühr', '']) ws.append(['', '', '', '', 'in Stk', '', 'in ' + currency, '']) # coinname ws.append([wsname, '']) firstProfitRow = ws.max_row + 1 # write data rewards = coin.getRewards() rewards.sort(key=lambda x: x.date, reverse=False) for reward in rewards: # check date of sell if reward.date.date() >= minDate and reward.date.date( ) <= maxDate: rewarddate = reward.date.astimezone( pytz.utc).replace(tzinfo=None).date() ws.append([ '', '', '', rewarddate, reward.amount, '', round(reward.value[currency], 3), '' ]) profitSumRows.append(ws.max_row + 2) rewardSumRows.append(profitSumRows[-1]) profitSumColumns.append(7) rewardSumColumns.append(profitSumColumns[-1]) # ws['M' + str(profitSumRows[-1])] = 'Summe' ws[rewardSumColumn + str(profitSumRows[-1] )] = '=ROUNDDOWN(SUM(' + rewardSumColumn + str( firstProfitRow) + ':' + rewardSumColumn + str( profitSumRows[-1] - 2) + '),2)' # page setup if translator: pageSetup(ws, dateCols=['D'], gapCols=['C', 'H'], lastRow=profitSumRows[-1], lastCol=profitSumColumns[-1] + 1, setWidthCols=['A', 'B', 'F'], setWidthValue=[13, 3, 3], trans=trans) else: pageSetup(ws, dateCols=['D'], gapCols=['C', 'H'], lastRow=profitSumRows[-1], lastCol=profitSumColumns[-1] + 1, setWidthCols=['A', 'B', 'F'], setWidthValue=[13, 3, 3]) # %% overview sheet ws = wb.create_sheet('Overview', 0) # write top header if translator: ws.append([ '', '', '', trans('Trades'), '', '', trans('Fees'), '', '', trans('Rewards'), '', '' ]) else: ws.append( ['', '', '', 'Trades', '', '', 'Fees', '', '', 'Rewards', '', '']) ws.merge_cells('A1:B1') ws.merge_cells('D1:E1') ws.merge_cells('G1:H1') ws.merge_cells('J1:K1') headingFont = Font(size=12, bold=True) headingAlignment = Alignment(horizontal='center', vertical='center') # blue, green, yellow, purple headings = [ws['A1'], ws['D1'], ws['G1'], ws['J1']] for heading in headings: heading.font = headingFont heading.alignment = headingAlignment headingColors = [ BLUECOLORCODE, GREENCOLORCODE, YELLOWCOLORCODE, PURPLECOLORCODE ] for i in range(len(headings)): headings[i].fill = PatternFill(fill_type='solid', start_color=headingColors[i], end_color=headingColors[i]) # empty row ws.append([]) # write sub header if translator: ws.append([ '', '', '', trans('Coin') + '/' + trans('Wallet'), trans('Profit'), '', trans('Coin'), trans('Fee'), '', trans('Coin'), trans('Reward'), '' ]) ws.append([ '', '', '', '', trans('in') + ' ' + currency, '', '', trans('in') + ' ' + currency, '', '', trans('in') + ' ' + currency, '' ]) ws.append([trans('Timeframe'), str(minDate) + ' : ' + str(maxDate)]) else: ws.append([ '', '', '', 'Coin/Wallet', 'Gewinn', '', 'Coin', 'Gebühren', '', 'Coin', 'Rewards', '' ]) ws.append([ '', '', '', '', 'in ' + currency, '', '', 'in ' + currency, '', '', 'in ' + currency, '' ]) ws.append(['Zeitraum', str(minDate) + ' : ' + str(maxDate)]) firstProfitRow = ws.max_row + 1 # write data sheets = wb.sheetnames[1:] # count number of different sheet types numTradeSheets = len(tradeprofitSumRows) numFeeSheets = len(feeSumRows) numRewardSheets = len(rewardSumRows) # for isheet in range(len(sheets)): for irow in range(max(numTradeSheets, numFeeSheets, numRewardSheets)): # claculate sheetindex for all sheet types iTradeSheet = irow iFeeSheet = irow + numTradeSheets iRewardSheet = iFeeSheet + numFeeSheets if irow < numTradeSheets: tradeProfitSumRef = '=INDIRECT(\"' + sheets[ iTradeSheet] + '!\"&ADDRESS(' + str( tradeprofitSumRows[irow]) + ',' + str( tradeprofitSumColumns[irow]) + ',4))' tradeProfitSheet = sheets[iTradeSheet] else: tradeProfitSumRef = '' tradeProfitSheet = '' if irow < numFeeSheets: feeSumRef = '=INDIRECT(\"' + sheets[ iFeeSheet] + '!\"&ADDRESS(' + str( feeSumRows[irow]) + ',' + str(feeSumColumns[irow]) + ',4))' feeSheet = sheets[iFeeSheet] else: feeSumRef = '' feeSheet = '' if irow < numRewardSheets: rewardSumRef = '=INDIRECT(\"' + sheets[ iRewardSheet] + '!\"&ADDRESS(' + str( rewardSumRows[irow]) + ',' + str( rewardSumColumns[irow]) + ',4))' rewardSheet = sheets[iRewardSheet] else: rewardSumRef = '' rewardSheet = '' ws.append([ '', '', '', tradeProfitSheet, tradeProfitSumRef, '', feeSheet, feeSumRef, '', rewardSheet, rewardSumRef ]) profitSumRow = ws.max_row + 2 profitSumColumns = ['E', 'H', 'K'] for profitSumColumn in profitSumColumns: ws[profitSumColumn + str(profitSumRow)] = '=SUM(' + profitSumColumn + str( firstProfitRow) + ':' + profitSumColumn + str(profitSumRow - 2) + ')' if translator: ws['A' + str(profitSumRow)] = trans('Profit') else: ws['A' + str(profitSumRow)] = 'Gewinn' profitSumSumColumn = 'B' cells = [col + str(profitSumRow) for col in profitSumColumns] ws[profitSumSumColumn + str(profitSumRow)] = ('=' + cells[0] + '+' + cells[1] + '+' + cells[2]) # page setup pageSetup(ws, dateCols=[], gapCols=['C', 'F', 'I', 'L'], lastRow=profitSumRow, lastCol=6, setWidthCols=['A', 'B', 'D', 'G', 'J'], setWidthValue=[10, 23, 20, 20, 20], trans=trans) # def textLen(value): # if value is None: # return 2 # elif value == '': # return 2 # elif isinstance(value, float): # # return len(str(value))/2 # return 5 # elif isinstance(value, str) and '=' in value: # return 5 # else: # if str(value).islower(): # length = len(str(value)) * 0.9 # elif str(value).isupper(): # length = len(str(value)) * 1.2 # else: # length = len(str(value)) + 2 # if length < 3: # length = 3 # return length # # for ws in wb: # for column_cells in ws.columns: # length = max(textLen(cell.value) for cell in column_cells[1:]) # ws.column_dimensions[str(column_cells[1].column_letter)].width = length # save file # wb.save(path + '-' + str(datetime.datetime.now()).replace(' ', '_').replace(':', '_').replace('-', '_').replace('.', # '_') + '.xlsx') try: wb.save(path) except PermissionError as ex: localLogger.error("saving export failed: " + str(ex))