def processActionDetails(self):
query = executeSQL(
self.db,
"SELECT sum as amount, category_id, tag_id FROM action_details AS d WHERE pid=:pid",
[(":pid", self.current[OPERATION_ID])])
while query.next():
amount, self.current[PRICE_CATEGORY], self.current[
FEE_TAX_TAG] = readSQLrecord(query)
book = BookAccount.Costs if amount < 0 else BookAccount.Incomes
self.appendTransaction(book, -amount)
def prepare_broker_fees(self, sheet, account_id, begin, end, formats):
self.add_report_header(sheet, formats, "Отчет по комиссиям, уплаченным брокеру в отчетном периоде")
_ = executeSQL(self.db, "DELETE FROM t_last_dates")
_ = executeSQL(self.db,
"INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT a.id AS ref_id, MAX(q.timestamp) AS timestamp "
"FROM actions AS a "
"LEFT JOIN accounts AS c ON c.id = :account_id "
"LEFT JOIN quotes AS q ON a.timestamp >= q.timestamp AND c.currency_id=q.asset_id "
"LEFT JOIN action_details AS d ON d.pid=a.id "
"WHERE a.timestamp>=:begin AND a.timestamp<:end "
"AND a.account_id=:account_id AND d.note LIKE '%MONTHLY%' "
"GROUP BY a.id",
[(":begin", begin), (":end", end), (":account_id", account_id)])
self.db.commit()
header_row = {
0: ("Описание", formats.ColumnHeader(), 50, 0, 0),
1: ("Сумма, USD", formats.ColumnHeader(), 8, 0, 0),
2: ("Дата оплаты", formats.ColumnHeader(), 10, 0, 0),
3: ("Курс USD/RUB на дату оплаты", formats.ColumnHeader(), 10, 0, 0),
4: ("Сумма, RUB", formats.ColumnHeader(), 10, 0, 0)
}
xlsxWriteRow(sheet, 7, header_row, 60)
for column in range(len(header_row)): # Put column numbers for reference
header_row[column] = (f"({column + 1})", formats.ColumnHeader())
xlsxWriteRow(sheet, 8, header_row)
query = executeSQL(self.db,
"SELECT a.timestamp AS payment_date, d.sum AS amount, d.note AS note, q.quote AS rate_cbr "
"FROM actions AS a "
"LEFT JOIN action_details AS d ON d.pid=a.id "
"LEFT JOIN accounts AS c ON c.id = :account_id "
"LEFT JOIN t_last_dates AS ld ON a.id=ld.ref_id "
"LEFT JOIN quotes AS q ON ld.timestamp=q.timestamp AND c.currency_id=q.asset_id "
"WHERE a.timestamp>=:begin AND a.timestamp<:end "
"AND a.account_id=:account_id AND d.note LIKE '%MONTHLY%' ",
[(":begin", begin), (":end", end), (":account_id", account_id)])
row = 9
amount_rub_sum = 0
while query.next():
payment_date, amount, note, rate = readSQLrecord(query)
amount_rub = round(-amount * rate, 2)
xlsxWriteRow(sheet, row, {
0: (note, formats.Text(row)),
1: (-amount, formats.Number(row, 2)),
2: (datetime.fromtimestamp(payment_date).strftime('%d.%m.%Y'), formats.Text(row)),
3: (rate, formats.Number(row, 4)),
4: (amount_rub, formats.Number(row, 2))
})
amount_rub_sum += amount_rub
row += 1
sheet.write(row, 3, "ИТОГО", formats.ColumnFooter())
sheet.write(row, 4, amount_rub_sum, formats.ColumnFooter())
def prepareIncomeSpendingReport(self, begin, end, account_id, group_dates):
_ = executeSQL(self.db, "DELETE FROM t_months")
_ = executeSQL(self.db, "DELETE FROM t_pivot")
_ = executeSQL(
self.db, "INSERT INTO t_months (month, asset_id, last_timestamp) "
"SELECT strftime('%s', datetime(timestamp, 'unixepoch', 'start of month') ) "
"AS month, asset_id, MAX(timestamp) AS last_timestamp "
"FROM quotes AS q "
"LEFT JOIN assets AS a ON q.asset_id=a.id "
"WHERE a.type_id=:asset_money "
"GROUP BY month, asset_id",
[(":asset_money", PredefinedAsset.Money)])
_ = executeSQL(
self.db, "INSERT INTO t_pivot (row_key, col_key, value) "
"SELECT strftime('%s', datetime(t.timestamp, 'unixepoch', 'start of month') ) AS row_key, "
"t.category_id AS col_key, sum(-t.amount * coalesce(q.quote, 1)) AS value "
"FROM ledger AS t "
"LEFT JOIN t_months AS d ON row_key = d.month AND t.asset_id = d.asset_id "
"LEFT JOIN quotes AS q ON d.last_timestamp = q.timestamp AND t.asset_id = q.asset_id "
"WHERE (t.book_account=:book_costs OR t.book_account=:book_incomes) "
"AND t.timestamp>=:begin AND t.timestamp<=:end "
"GROUP BY row_key, col_key",
[(":book_costs", BookAccount.Costs),
(":book_incomes", BookAccount.Incomes), (":begin", begin),
(":end", end)])
self.db.commit()
self.query = executeSQL(
self.db, "SELECT c.id, c.level, c.path, "
"strftime('%Y', datetime(p.row_key, 'unixepoch')) AS year, "
"strftime('%m', datetime(p.row_key, 'unixepoch')) AS month, p.value "
"FROM categories_tree AS c "
"LEFT JOIN t_pivot AS p ON p.col_key=c.id "
"ORDER BY c.path, year, month")
table = []
while self.query.next():
id, level, category, year, month, value = readSQLrecord(self.query)
turnover = value if value != '' else 0
table.append({
'category': category,
'Y': year,
'M': month,
'turnover': turnover
})
data = pd.DataFrame(table)
data = pd.pivot_table(data,
index=['category'],
columns=['Y', 'M'],
values=['turnover'],
aggfunc=sum,
fill_value=0.0,
margins=True,
margins_name=g_tr('Reports', "TOTAL"))
if data.columns[0][
1] == '': # if some categories have no data and we have null 1st column
data = data.drop(columns=[data.columns[0]])
# Calculate sub-totals from bottom to top
totals = {}
prev_level = 0
for index, row in data[::-1].iterrows():
if index == g_tr('Reports', "TOTAL"):
continue
level = index.count(TREE_LEVEL_SEPARATOR)
if level > prev_level:
totals[level] = row['turnover']
prev_level = level
elif level == prev_level:
try:
totals[level] = totals[level] + row['turnover']
except KeyError:
totals[level] = row['turnover']
elif level < prev_level:
try:
totals[level] = totals[level] + totals[prev_level] + row[
'turnover']
except KeyError:
totals[level] = totals[prev_level] + row['turnover']
sub_total = totals.pop(prev_level, None)
data.loc[index, :] = sub_total.values
prev_level = level
self.dataframe = data
return True
def rebuild(self, from_timestamp=-1, fast_and_dirty=False, silent=True):
operationProcess = {
TransactionType.Action: self.processAction,
TransactionType.Dividend: self.processDividend,
TransactionType.Trade: self.processTrade,
TransactionType.Transfer: self.processTransfer,
}
if from_timestamp >= 0:
frontier = from_timestamp
silent = False
else:
frontier = self.getCurrentFrontier()
operations_count = readSQL(
self.db,
"SELECT COUNT(id) FROM all_transactions WHERE timestamp >= :frontier",
[(":frontier", frontier)])
if operations_count > self.SILENT_REBUILD_THRESHOLD:
silent = False
if QMessageBox().warning(
None, g_tr('Ledger', "Confirmation"),
f"{operations_count}" + g_tr(
'Ledger',
" operations require rebuild. Do you want to do it right now?"
), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No:
return
if not silent:
logging.info(
g_tr('Ledger', "Re-build ledger from: ") +
f"{datetime.fromtimestamp(frontier).strftime('%d/%m/%Y %H:%M:%S')}"
)
start_time = datetime.now()
_ = executeSQL(
self.db, "DELETE FROM deals WHERE close_sid >= "
"(SELECT coalesce(MIN(id), 0) FROM sequence WHERE timestamp >= :frontier)",
[(":frontier", frontier)])
_ = executeSQL(self.db,
"DELETE FROM ledger WHERE timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL(self.db,
"DELETE FROM sequence WHERE timestamp >= :frontier",
[(":frontier", frontier)])
_ = executeSQL(self.db,
"DELETE FROM ledger_sums WHERE timestamp >= :frontier",
[(":frontier", frontier)])
self.db.commit()
if fast_and_dirty: # For 30k operations difference of execution time is - with 0:02:41 / without 0:11:44
_ = executeSQL(self.db, "PRAGMA synchronous = OFF")
query = executeSQL(
self.db,
"SELECT type, id, timestamp, subtype, account, currency, asset, amount, "
"price_category, coupon_peer, fee_tax_tag FROM all_transactions "
"WHERE timestamp >= :frontier", [(":frontier", frontier)])
while query.next():
self.current = readSQLrecord(query)
seq_query = executeSQL(
self.db, "INSERT INTO sequence(timestamp, type, operation_id) "
"VALUES(:timestamp, :type, :operation_id)",
[(":timestamp", self.current[TIMESTAMP]),
(":type", self.current[TRANSACTION_TYPE]),
(":operation_id", self.current[OPERATION_ID])])
self.current_seq = seq_query.lastInsertId()
operationProcess[self.current[TRANSACTION_TYPE]]()
if not silent and (query.at() % 1000) == 0:
logging.info(
g_tr('Ledger', "Processed ") + f"{int(query.at()/1000)}" +
g_tr('Ledger', "k records, current frontier: ") +
f"{datetime.fromtimestamp(self.current[TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}"
)
if fast_and_dirty:
_ = executeSQL(self.db, "PRAGMA synchronous = ON")
end_time = datetime.now()
if not silent:
logging.info(
g_tr('Ledger', "Ledger is complete. Elapsed time: ") +
f"{end_time - start_time}" +
g_tr('Ledger', ", new frontier: ") +
f"{datetime.fromtimestamp(self.current[TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}"
)
self.updateBalancesView()
self.updateHoldingsView()
def processSell(self):
seq_id = self.current_seq
account_id = self.current[ACCOUNT_ID]
asset_id = self.current[ASSET_ID]
qty = -self.current[AMOUNT_QTY]
price = self.current[PRICE_CATEGORY]
trade_value = round(
price * qty,
2) - self.current[FEE_TAX_TAG] + self.current[COUPON_PEER]
buy_qty = 0
buy_value = 0
if self.getAmount(BookAccount.Assets, asset_id) > 0:
query = executeSQL(
self.db,
"SELECT d.open_sid AS open, abs(o.qty) - SUM(d.qty) AS remainder FROM deals AS d "
"LEFT JOIN sequence AS os ON os.type=3 AND os.id=d.open_sid "
"JOIN trades AS o ON o.id = os.operation_id "
"WHERE d.account_id=:account_id AND d.asset_id=:asset_id "
"GROUP BY d.open_sid "
"ORDER BY d.close_sid DESC, d.open_sid DESC LIMIT 1",
[(":account_id", account_id), (":asset_id", asset_id)])
if query.next(
): # sid of Buy trade from last deal and non-matched reminder of last Sell trade
last_sid, reminder = readSQLrecord(query)
query = executeSQL(
self.db, "SELECT s.id, t.qty, t.price FROM trades AS t "
"LEFT JOIN sequence AS s ON s.type = 3 AND s.operation_id=t.id "
"WHERE t.qty > 0 AND t.asset_id = :asset_id AND t.account_id = :account_id "
"AND s.id < :sid AND s.id >= :last_sid",
[(":asset_id", asset_id), (":account_id", account_id),
(":sid", seq_id), (":last_sid", last_sid)])
else: # There were no deals -> Select all purchases
reminder = 0
query = executeSQL(
self.db, "SELECT s.id, t.qty, t.price FROM trades AS t "
"LEFT JOIN sequence AS s ON s.type = 3 AND s.operation_id=t.id "
"WHERE t.qty>0 AND t.asset_id=:asset_id AND t.account_id=:account_id AND s.id<:sid",
[(":asset_id", asset_id), (":account_id", account_id),
(":sid", seq_id)])
while query.next():
deal_sid, deal_qty, deal_price = readSQLrecord(query)
if reminder > 0:
next_deal_qty = reminder
reminder = 0
else:
next_deal_qty = deal_qty
if (
buy_qty + next_deal_qty
) >= qty: # we are selling less or the same amount as was bought previously
next_deal_qty = qty - buy_qty
_ = executeSQL(
self.db,
"INSERT INTO deals(account_id, asset_id, open_sid, close_sid, qty) "
"VALUES(:account_id, :asset_id, :open_sid, :close_sid, :qty)",
[(":account_id", account_id), (":asset_id", asset_id),
(":open_sid", deal_sid), (":close_sid", seq_id),
(":qty", next_deal_qty)])
buy_qty = buy_qty + next_deal_qty
buy_value = buy_value + (next_deal_qty * deal_price)
if buy_qty == qty:
break
credit_returned = self.returnCredit(trade_value)
if credit_returned < trade_value:
self.appendTransaction(BookAccount.Money,
(trade_value - credit_returned))
if buy_qty > 0: # Add result of closed deals
self.appendTransaction(BookAccount.Assets, -buy_qty, -buy_value)
self.current[PRICE_CATEGORY] = PredefinedCategory.Profit
self.appendTransaction(BookAccount.Incomes,
(buy_value - (price * buy_qty)))
if buy_qty < qty: # Add new short position
self.appendTransaction(BookAccount.Assets, (buy_qty - qty),
(buy_qty - qty) * price)
if self.current[COUPON_PEER]:
self.current[PRICE_CATEGORY] = PredefinedCategory.Dividends
self.appendTransaction(BookAccount.Incomes,
-self.current[COUPON_PEER])
if self.current[FEE_TAX_TAG]:
self.current[PRICE_CATEGORY] = PredefinedCategory.Fees
self.appendTransaction(BookAccount.Costs,
self.current[FEE_TAX_TAG])
def prepare_dividends(self, sheet, account_id, begin, end, formats):
self.add_report_header(sheet, formats, "Отчет по дивидендам, полученным в отчетном периоде")
_ = executeSQL(self.db, "DELETE FROM t_last_dates")
_ = executeSQL(self.db,
"INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT d.id AS ref_id, MAX(q.timestamp) AS timestamp "
"FROM dividends AS d "
"LEFT JOIN accounts AS a ON d.account_id=a.id "
"LEFT JOIN quotes AS q ON d.timestamp >= q.timestamp AND a.currency_id=q.asset_id "
"WHERE d.timestamp>=:begin AND d.timestamp<:end AND d.account_id=:account_id "
"GROUP BY d.id", [(":begin", begin), (":end", end), (":account_id", account_id)])
self.db.commit()
header_row = {
0: ("Дата выплаты", formats.ColumnHeader(), 10, 0, 0),
1: ("Ценная бумага", formats.ColumnHeader(), 8, 0, 0),
2: ("Полное наименование", formats.ColumnHeader(), 50, 0, 0),
3: ("Курс USD/RUB на дату выплаты", formats.ColumnHeader(), 16, 0, 0),
4: ("Доход, USD", formats.ColumnHeader(), 12, 0, 0),
5: ("Доход, RUB", formats.ColumnHeader(), 12, 0, 0),
6: ("Налог упл., USD", formats.ColumnHeader(), 12, 0, 0),
7: ("Налог упл., RUB", formats.ColumnHeader(), 12, 0, 0),
8: ("Налок к уплате, RUB", formats.ColumnHeader(), 12, 0, 0)
}
xlsxWriteRow(sheet, 7, header_row, 30)
for column in range(len(header_row)): # Put column numbers for reference
header_row[column] = (f"({column + 1})", formats.ColumnHeader())
xlsxWriteRow(sheet, 8, header_row)
query = executeSQL(self.db,
"SELECT d.timestamp AS payment_date, s.name AS symbol, s.full_name AS full_name, "
"d.sum AS amount, d.sum_tax AS tax_amount, q.quote AS rate_cbr "
"FROM dividends AS d "
"LEFT JOIN assets AS s ON s.id = d.asset_id "
"LEFT JOIN accounts AS a ON d.account_id = a.id "
"LEFT JOIN t_last_dates AS ld ON d.id=ld.ref_id "
"LEFT JOIN quotes AS q ON ld.timestamp=q.timestamp AND a.currency_id=q.asset_id "
"WHERE d.timestamp>=:begin AND d.timestamp<:end AND d.account_id=:account_id "
"ORDER BY d.timestamp",
[(":begin", begin), (":end", end), (":account_id", account_id)])
row = 9
amount_rub_sum = 0
amount_usd_sum = 0
tax_usd_sum = 0
tax_us_rub_sum = 0
tax_ru_rub_sum = 0
while query.next():
payment_date, symbol, full_name, amount_usd, tax_usd, rate = readSQLrecord(query)
amount_rub = round(amount_usd * rate, 2)
tax_us_rub = round(-tax_usd * rate, 2)
tax_ru_rub = round(0.13 * amount_rub, 2)
if tax_ru_rub > tax_us_rub:
tax_ru_rub = tax_ru_rub - tax_us_rub
else:
tax_ru_rub = 0
xlsxWriteRow(sheet, row, {
0: (datetime.fromtimestamp(payment_date).strftime('%d.%m.%Y'), formats.Text(row)),
1: (symbol, formats.Text(row)),
2: (full_name, formats.Text(row)),
3: (rate, formats.Number(row, 4)),
4: (amount_usd, formats.Number(row, 2)),
5: (amount_rub, formats.Number(row, 2)),
6: (-tax_usd, formats.Number(row, 2)),
7: (tax_us_rub, formats.Number(row, 2)),
8: (tax_ru_rub, formats.Number(row, 2))
})
amount_usd_sum += amount_usd
amount_rub_sum += amount_rub
tax_usd_sum += -tax_usd
tax_us_rub_sum += tax_us_rub
tax_ru_rub_sum += tax_ru_rub
row += 1
xlsxWriteRow(sheet, row, {
3: ("ИТОГО", formats.ColumnFooter()),
4: (amount_usd_sum, formats.ColumnFooter()),
5: (amount_rub_sum, formats.ColumnFooter()),
6: (tax_usd_sum, formats.ColumnFooter()),
7: (tax_us_rub_sum, formats.ColumnFooter()),
8: (tax_ru_rub_sum, formats.ColumnFooter())
})
def prepare_trades(self, sheet, account_id, begin, end, formats):
self.add_report_header(sheet, formats, "Отчет по сделкам с ценными бумагами, завершённым в отчетном периоде")
_ = executeSQL(self.db, "DELETE FROM t_last_dates")
_ = executeSQL(self.db,
"INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT ref_id, MAX(q.timestamp) AS timestamp "
"FROM (SELECT o.timestamp AS ref_id "
"FROM deals AS d "
"LEFT JOIN sequence AS os ON os.id=d.open_sid "
"LEFT JOIN trades AS o ON os.operation_id=o.id "
"WHERE o.timestamp<:end AND d.account_id=:account_id "
"UNION "
"SELECT c.timestamp AS ref_id "
"FROM deals AS d "
"LEFT JOIN sequence AS cs ON cs.id=d.close_sid "
"LEFT JOIN trades AS c ON cs.operation_id=c.id "
"WHERE c.timestamp<:end AND d.account_id=:account_id "
"UNION "
"SELECT o.settlement AS ref_id "
"FROM deals AS d "
"LEFT JOIN sequence AS os ON os.id=d.open_sid "
"LEFT JOIN trades AS o ON os.operation_id=o.id "
"WHERE o.timestamp<:end AND d.account_id=:account_id "
"UNION "
"SELECT c.settlement AS ref_id "
"FROM deals AS d "
"LEFT JOIN sequence AS cs ON cs.id=d.close_sid "
"LEFT JOIN trades AS c ON cs.operation_id=c.id "
"WHERE c.timestamp<:end AND d.account_id=:account_id "
"ORDER BY ref_id) "
"LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN quotes AS q ON ref_id >= q.timestamp AND a.currency_id=q.asset_id "
"GROUP BY ref_id",
[(":begin", begin), (":end", end), (":account_id", account_id)])
self.db.commit()
header_row = {
0: ("Ценная бумага", formats.ColumnHeader(), 8, 0, 0),
1: ("Кол-во", formats.ColumnHeader(), 8, 0, 0),
2: ("Тип сделки", formats.ColumnHeader(), 8, 0, 0),
3: ("Дата сделки", formats.ColumnHeader(), 10, 0, 0),
4: ("Курс USD/RUB на дату сделки", formats.ColumnHeader(), 9, 0, 0),
5: ("Дата поставки", formats.ColumnHeader(), 10, 0, 0),
6: ("Курс USD/RUB на дату поставки", formats.ColumnHeader(), 9, 0, 0),
7: ("Цена, USD", formats.ColumnHeader(), 12, 0, 0),
8: ("Сумма сделки, USD", formats.ColumnHeader(), 12, 0, 0),
9: ("Сумма сделки, RUB", formats.ColumnHeader(), 12, 0, 0),
10: ("Комиссия, USD", formats.ColumnHeader(), 12, 0, 0),
11: ("Комиссия, RUB", formats.ColumnHeader(), 9, 0, 0),
12: ("Доход, RUB", formats.ColumnHeader(), 12, 0, 0),
13: ("Расход, RUB", formats.ColumnHeader(), 12, 0, 0),
14: ("Финансовый результат, RUB", formats.ColumnHeader(), 12, 0, 0)
}
xlsxWriteRow(sheet, 7, header_row, 60)
for column in range(len(header_row)): # Put column numbers for reference
header_row[column] = (f"({column + 1})", formats.ColumnHeader())
xlsxWriteRow(sheet, 8, header_row)
# Take all actions without conversion
query = executeSQL(self.db,
"SELECT s.name AS symbol, d.qty AS qty, "
"o.timestamp AS o_date, qo.quote AS o_rate, o.settlement AS os_date, "
"qos.quote AS os_rate, o.price AS o_price, o.fee AS o_fee, "
"c.timestamp AS c_date, qc.quote AS c_rate, c.settlement AS cs_date, "
"qcs.quote AS cs_rate, c.price AS c_price, c.fee AS c_fee, "
"coalesce(ao.type, ac.type, 0) AS corp_action_type "
"FROM deals AS d "
"LEFT JOIN sequence AS os ON os.id=d.open_sid "
"LEFT JOIN trades AS o ON os.operation_id=o.id "
"LEFT JOIN sequence AS cs ON cs.id=d.close_sid "
"LEFT JOIN trades AS c ON cs.operation_id=c.id "
"LEFT JOIN assets AS s ON o.asset_id=s.id "
"LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN t_last_dates AS ldo ON o.timestamp=ldo.ref_id "
"LEFT JOIN quotes AS qo ON ldo.timestamp=qo.timestamp AND a.currency_id=qo.asset_id "
"LEFT JOIN t_last_dates AS ldos ON o.settlement=ldos.ref_id "
"LEFT JOIN quotes AS qos ON ldos.timestamp=qos.timestamp AND a.currency_id=qos.asset_id "
"LEFT JOIN t_last_dates AS ldc ON c.timestamp=ldc.ref_id "
"LEFT JOIN quotes AS qc ON ldc.timestamp=qc.timestamp AND a.currency_id=qc.asset_id "
"LEFT JOIN t_last_dates AS ldcs ON c.settlement=ldcs.ref_id "
"LEFT JOIN quotes AS qcs ON ldcs.timestamp=qcs.timestamp AND a.currency_id=qcs.asset_id "
"LEFT JOIN corp_actions AS ao ON ao.id=o.corp_action_id "
"LEFT JOIN corp_actions AS ac ON ac.id=c.corp_action_id "
"WHERE c.timestamp>=:begin AND c.timestamp<:end "
"AND d.account_id=:account_id AND corp_action_type != 1 "
"ORDER BY o.timestamp, c.timestamp",
[(":begin", begin), (":end", end), (":account_id", account_id)])
start_row = 9
data_row = 0
income_sum = 0.0
spending_sum = 0.0
profit_sum = 0.0
while query.next():
symbol, qty, o_date, o_fee_rate, os_date, o_rate, o_price, o_fee_usd, \
c_date, c_fee_rate, cs_date, c_rate, c_price, c_fee_usd, corp_action_type = readSQLrecord(query)
row = start_row + (data_row * 2)
o_amount_usd = round(o_price * qty, 2)
o_amount_rub = round(o_amount_usd * o_rate, 2)
c_amount_usd = round(c_price * qty, 2)
c_amount_rub = round(c_amount_usd * c_rate, 2)
o_fee_rub = round(o_fee_usd * o_fee_rate, 2)
c_fee_rub = round(c_fee_usd * c_fee_rate, 2)
income = c_amount_rub
spending = o_amount_rub + o_fee_rub + c_fee_rub
xlsxWriteRow(sheet, row, {
0: (symbol, formats.Text(data_row), 0, 0, 1),
1: (float(qty), formats.Number(data_row, 0, True), 0, 0, 1),
2: ("Покупка", formats.Text(data_row)),
3: (datetime.fromtimestamp(o_date).strftime('%d.%m.%Y'), formats.Text(data_row)),
4: (o_fee_rate, formats.Number(data_row, 4)),
5: (datetime.fromtimestamp(os_date).strftime('%d.%m.%Y'), formats.Text(data_row)),
6: (o_rate, formats.Number(data_row, 4)),
7: (o_price, formats.Number(data_row, 6)),
8: (o_amount_usd, formats.Number(data_row, 2)),
9: (o_amount_rub, formats.Number(data_row, 2)),
10: (o_fee_usd, formats.Number(data_row, 6)),
11: (o_fee_rub, formats.Number(data_row, 2)),
12: (income, formats.Number(data_row, 2), 0, 0, 1),
13: (spending, formats.Number(data_row, 2), 0, 0, 1),
14: (income - spending, formats.Number(data_row, 2), 0, 0, 1)
})
xlsxWriteRow(sheet, row + 1, {
2: ("Продажа", formats.Text(data_row)),
3: (datetime.fromtimestamp(c_date).strftime('%d.%m.%Y'), formats.Text(data_row)),
4: (c_fee_rate, formats.Number(data_row, 4)),
5: (datetime.fromtimestamp(cs_date).strftime('%d.%m.%Y'), formats.Text(data_row)),
6: (c_rate, formats.Number(data_row, 4)),
7: (c_price, formats.Number(data_row, 6)),
8: (c_amount_usd, formats.Number(data_row, 2)),
9: (c_amount_rub, formats.Number(data_row, 2)),
10: (c_fee_usd, formats.Number(data_row, 6)),
11: (c_fee_rub, formats.Number(data_row, 2))
})
income_sum += income
spending_sum += spending
profit_sum += income - spending
data_row = data_row + 1
row = start_row + (data_row * 2)
xlsxWriteRow(sheet, row, {
11: ("ИТОГО", formats.ColumnFooter()),
12: (income_sum, formats.ColumnFooter()),
13: (spending_sum, formats.ColumnFooter()),
14: (profit_sum, formats.ColumnFooter())
})