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))
