def SubmitQuote(self, asset_id, asset_name, timestamp, quote):
old_id = 0
query = executeSQL(
self.db,
"SELECT id FROM quotes WHERE asset_id = :asset_id AND timestamp = :timestamp",
[(":asset_id", asset_id), (":timestamp", timestamp)])
if query.next():
old_id = query.value(0)
if old_id:
executeSQL(self.db,
"UPDATE quotes SET quote=:quote WHERE id=:old_id", [
(":quote", quote),
(":old_id", old_id),
])
else:
executeSQL(
self.db,
"INSERT INTO quotes(timestamp, asset_id, quote) VALUES (:timestamp, :asset_id, :quote)",
[(":timestamp", timestamp), (":asset_id", asset_id),
(":quote", quote)])
self.db.commit()
logging.info(
g_tr('QuotesUpdateDialog', "Quote loaded: ") +
f"{asset_name} @ {datetime.fromtimestamp(timestamp).strftime('%d/%m/%Y %H:%M:%S')} = {quote}"
)
def prepareDealsReport(self, begin, end, account_id, group_dates):
if account_id == 0:
self.report_failure.emit(
g_tr('Reports',
"You should select account to create Deals report"))
return False
if group_dates == 1:
self.query = executeSQL(
self.db, "SELECT asset, "
"strftime('%s', datetime(open_timestamp, 'unixepoch', 'start of day')) as open_timestamp, "
"strftime('%s', datetime(close_timestamp, 'unixepoch', 'start of day')) as close_timestamp, "
"SUM(open_price*qty)/SUM(qty) as open_price, SUM(close_price*qty)/SUM(qty) AS close_price, "
"SUM(qty) as qty, SUM(fee) as fee, SUM(profit) as profit, "
"coalesce(100*SUM(qty*(close_price-open_price)-fee)/SUM(qty*open_price), 0) AS rel_profit "
"FROM deals_ext "
"WHERE account_id=:account_id AND close_timestamp>=:begin AND close_timestamp<=:end "
"GROUP BY asset, open_timestamp, close_timestamp "
"ORDER BY close_timestamp, open_timestamp",
[(":account_id", account_id), (":begin", begin),
(":end", end)],
forward_only=False)
else:
self.query = executeSQL(
self.db,
"SELECT asset, open_timestamp, close_timestamp, open_price, close_price, "
"qty, fee, profit, rel_profit FROM deals_ext "
"WHERE account_id=:account_id AND close_timestamp>=:begin AND close_timestamp<=:end",
[(":account_id", account_id), (":begin", begin),
(":end", end)],
forward_only=False)
return True
def response_esia(self, auth_code, state):
client_secret = readSQL(
self.db,
"SELECT value FROM settings WHERE name='RuTaxClientSecret'")
payload = '{' + f'"authorization_code": "{auth_code}", "client_secret": "{client_secret}", "state": "{state}"' \
+ '}'
response = self.web_session.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/esia/auth',
data=payload)
if response.status_code != 200:
logging.error(
g_tr('SlipsTaxAPI', "ESIA login failed: ") +
f"{response}/{response.text}")
return
logging.info(
g_tr('SlipsTaxAPI', "ESIA login successful: ") +
f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_session WHERE name='RuTaxSessionId'",
[(":new_session", new_session_id)])
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_refresh_token WHERE name='RuTaxRefreshToken'",
[(":new_refresh_token", new_refresh_token)])
self.db.commit()
self.accept()
def createTransfer(self, timestamp, f_acc_id, f_amount, t_acc_id, t_amount, fee_acc_id, fee, note):
transfer_id = readSQL(self.db,
"SELECT id FROM transfers_combined "
"WHERE from_timestamp=:timestamp AND from_acc_id=:from_acc_id AND to_acc_id=:to_acc_id",
[(":timestamp", timestamp), (":from_acc_id", f_acc_id), (":to_acc_id", t_acc_id)])
if transfer_id:
logging.info(f"Currency exchange {f_amount}->{t_amount} already exists in ledger. Skipped")
return
if abs(fee) > Setup.CALC_TOLERANCE:
_ = executeSQL(self.db,
"INSERT INTO transfers_combined (from_timestamp, from_acc_id, from_amount, "
"to_timestamp, to_acc_id, to_amount, fee_timestamp, fee_acc_id, fee_amount, note) "
"VALUES (:timestamp, :f_acc_id, :f_amount, :timestamp, :t_acc_id, :t_amount, "
":timestamp, :fee_acc_id, :fee_amount, :note)",
[(":timestamp", timestamp), (":f_acc_id", f_acc_id), (":t_acc_id", t_acc_id),
(":f_amount", f_amount), (":t_amount", t_amount), (":fee_acc_id", fee_acc_id),
(":fee_amount", fee), (":note", note)])
else:
_ = executeSQL(self.db,
"INSERT INTO transfers_combined (from_timestamp, from_acc_id, from_amount, "
"to_timestamp, to_acc_id, to_amount, note) "
"VALUES (:timestamp, :f_acc_id, :f_amount, :timestamp, :t_acc_id, :t_amount, :note)",
[(":timestamp", timestamp), (":f_acc_id", f_acc_id), (":t_acc_id", t_acc_id),
(":f_amount", f_amount), (":t_amount", t_amount), (":note", note)])
self.db.commit()
logging.info(g_tr('StatementLoader', "Currency exchange ") + f"{f_amount}->{t_amount}" +
g_tr('StatementLoader', " added"))
def loadIBTransactionTax(self, IBtax):
account_id = self.findAccountID(IBtax.accountId, IBtax.currency)
if account_id is None:
logging.error(g_tr('StatementLoader', "Account ") + f"{IBtax.accountId} ({IBtax.currency})" +
g_tr('StatementLoader', " not found. Tax #") + f"{IBtax.tradeID}" +
g_tr('StatementLoader', " skipped"))
return
timestamp = int(datetime.combine(IBtax.date, datetime.min.time()).timestamp())
amount = float(IBtax.taxAmount) # value is negative already
note = f"{IBtax.symbol} ({IBtax.description}) - {IBtax.taxDescription} (#{IBtax.tradeId})"
id = readSQL(self.db, "SELECT id FROM all_operations WHERE type = :type "
"AND timestamp=:timestamp AND account_id=:account_id AND amount=:amount",
[(":timestamp", timestamp), (":type", TransactionType.Action),
(":account_id", account_id), (":amount", amount)])
if id:
logging.warning(g_tr('StatementLoader', "Tax transaction #") + f"{IBtax.tradeId}" +
g_tr('StatementLoader', " already exists"))
return
query = executeSQL(self.db,
"INSERT INTO actions (timestamp, account_id, peer_id) VALUES "
"(:timestamp, :account_id, (SELECT organization_id FROM accounts WHERE id=:account_id))",
[(":timestamp", timestamp), (":account_id", account_id)])
pid = query.lastInsertId()
_ = executeSQL(self.db, "INSERT INTO action_details (pid, category_id, sum, note) "
"VALUES (:pid, :category_id, :sum, :note)",
[(":pid", pid), (":category_id", PredefinedCategory.Taxes), (":sum", amount), (":note", note)])
self.db.commit()
logging.info(g_tr('StatementLoader', "Transaction tax added: ") + f"{note}, {amount}")
def loadIBFee(self, fee):
account_id = self.findAccountID(fee.accountId, fee.currency)
if account_id is None:
logging.error(
g_tr('StatementLoader', "Account ") +
f"{fee.accountId} ({fee.currency})" +
g_tr('StatementLoader', " not found. Skipping fee #") +
f"{fee.transactionID}")
return
timestamp = int(fee.dateTime.timestamp())
amount = float(fee.amount) # value may be both positive and negative
note = fee.description
query = executeSQL(
self.db,
"INSERT INTO actions (timestamp, account_id, peer_id) VALUES "
"(:timestamp, :account_id, (SELECT organization_id FROM accounts WHERE id=:account_id))",
[(":timestamp", timestamp), (":account_id", account_id)])
pid = query.lastInsertId()
_ = executeSQL(
self.db,
"INSERT INTO action_details (pid, category_id, sum, note) "
"VALUES (:pid, :category_id, :sum, :note)",
[(":pid", pid), (":category_id", PredefinedCategory.Fees),
(":sum", amount), (":note", note)])
self.db.commit()
logging.info(
g_tr('StatementLoader', "Fee added: ") + f"{note}, {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 UpdateQuotes(self, start_timestamp, end_timestamp):
self.PrepareRussianCBReader()
executeSQL(self.db, "DELETE FROM holdings_aux")
# Collect list of assets that are/were held on end date
executeSQL(
self.db, "INSERT INTO holdings_aux(asset) "
"SELECT l.asset_id AS asset FROM ledger AS l "
"WHERE l.book_account = 4 AND l.timestamp <= :end_timestamp "
"GROUP BY l.asset_id "
"HAVING sum(l.amount) > :tolerance "
"UNION "
"SELECT DISTINCT l.asset_id AS asset FROM ledger AS l "
"WHERE l.book_account = 4 AND l.timestamp >= :start_timestamp AND l.timestamp <= :end_timestamp "
"UNION "
"SELECT DISTINCT a.currency_id AS asset FROM ledger AS l "
"LEFT JOIN accounts AS a ON a.id = l.account_id "
"WHERE (l.book_account = 3 OR l.book_account = 5) "
"AND l.timestamp >= :start_timestamp AND l.timestamp <= :end_timestamp",
[(":start_timestamp", start_timestamp),
(":end_timestamp", end_timestamp),
(":tolerance", Setup.CALC_TOLERANCE)])
# Get a list of symbols ordered by data source ID
query = executeSQL(
self.db,
"SELECT h.asset, a.name, a.src_id, a.isin, MAX(q.timestamp) AS last_timestamp "
"FROM holdings_aux AS h "
"LEFT JOIN assets AS a ON a.id=h.asset "
"LEFT JOIN quotes AS q ON q.asset_id=h.asset "
"GROUP BY h.asset "
"ORDER BY a.src_id")
while query.next():
asset_id = query.value(0)
asset = query.value(1)
feed_id = query.value(2)
isin = query.value(3)
last_timestamp = query.value(4) if query.value(4) != '' else 0
from_timestamp = last_timestamp if last_timestamp > start_timestamp else start_timestamp
try:
data = self.data_loaders[feed_id](asset, isin, from_timestamp,
end_timestamp)
except (xml_tree.ParseError, pd.errors.EmptyDataError):
logging.warning(
g_tr('QuotesUpdateDialog', "No data were downloaded for ")
+ f"{asset}")
continue
if data is not None:
for date, quote in data.iterrows():
self.SubmitQuote(asset_id, asset, int(date.timestamp()),
float(quote[0]))
logging.info(g_tr('QuotesUpdateDialog', "Download completed"))
def addOperation(self):
if self.AccountEdit.selected_id == 0:
logging.warning(
g_tr('ImportSlipDialog',
"Not possible to import slip: no account set for import"))
return
if self.PeerEdit.selected_id == 0:
logging.warning(
g_tr(
'ImportSlipDialog',
"Not possible to import slip: can't import: no peer set for import"
))
return
if self.slip_lines[self.slip_lines['category'] == 0].shape[0] != 0:
logging.warning(
g_tr(
'ImportSlipDialog',
"Not possible to import slip: some categories are not set")
)
return
query = executeSQL(
self.db, "INSERT INTO actions (timestamp, account_id, peer_id) "
"VALUES (:timestamp, :account_id, :peer_id)",
[(":timestamp", self.SlipDateTime.dateTime().toSecsSinceEpoch()),
(":account_id", self.AccountEdit.selected_id),
(":peer_id", self.PeerEdit.selected_id)])
pid = query.lastInsertId()
# update mappings
_ = executeSQL(
self.db,
"INSERT INTO map_peer (value, mapped_to) VALUES (:peer_name, :peer_id)",
[(":peer_name", self.SlipShopName.text()),
(":peer_id", self.PeerEdit.selected_id)])
for index, row in self.slip_lines.iterrows():
_ = executeSQL(
self.db,
"INSERT INTO action_details (pid, category_id, sum, note) "
"VALUES (:pid, :category_id, :amount, :note)",
[(":pid", pid), (":category_id", row['category']),
(":amount", row['sum']), (":note", row['name'])])
# update mappings
_ = executeSQL(
self.db,
"INSERT INTO map_category (value, mapped_to) VALUES (:item_name, :category_id)",
[(":item_name", row['name']),
(":category_id", row['category'])])
self.db.commit()
self.clearSlipData()
def onLanguageChanged(self, action):
language_code = action.data()
if language_code != self.currentLanguage:
executeSQL(self.db,
"UPDATE settings "
"SET value=(SELECT id FROM languages WHERE language = :new_language) WHERE name ='Language'",
[(':new_language', language_code)])
QMessageBox().information(self, g_tr('MainWindow', "Restart required"),
g_tr('MainWindow', "Language was changed to ") +
QLocale.languageToString(QLocale(language_code).language()) + "\n" +
g_tr('MainWindow', "You should restart application to apply changes\n"
"Application will be terminated now"),
QMessageBox.Ok)
self.close()
def copyOperation(self):
self.checkForUncommittedChanges()
index = self.table_view.currentIndex()
operations_model = self.table_view.model()
operation_type = operations_model.data(
operations_model.index(index.row(), 0))
mapper = self.operations[operation_type][self.OP_MAPPER]
row = mapper.currentIndex()
old_id = mapper.model().record(row).value(
mapper.model().fieldIndex("id"))
new_record = mapper.model().record(row)
new_record = self.prepareNewOperation(operation_type,
new_record,
copy_mode=True)
mapper.model().setFilter(
f"{self.operations[operation_type][self.OP_MAPPER_TABLE]}.id = 0")
assert mapper.model().insertRows(0, 1)
mapper.model().setRecord(0, new_record)
mapper.toLast()
if self.operations[operation_type][self.OP_CHILD_VIEW]:
child_view = self.operations[operation_type][self.OP_CHILD_VIEW]
child_view.model().setFilter(
f"{self.operations[operation_type][self.OP_CHILD_TABLE]}.pid = 0"
)
query = executeSQL(
mapper.model().database(),
f"SELECT * FROM {self.operations[operation_type][self.OP_CHILD_TABLE]} "
"WHERE pid = :pid ORDER BY id DESC", [(":pid", old_id)])
while query.next():
new_record = query.record()
new_record.setNull("id")
new_record.setNull("pid")
assert child_view.model().insertRows(0, 1)
child_view.model().setRecord(0, new_record)
def addWithholdingTax(self, timestamp, account_id, asset_id, amount, note):
parts = re.match(IBKR.TaxNotePattern, note)
if not parts:
logging.warning(
g_tr('StatementLoader', "*** MANUAL ENTRY REQUIRED ***"))
logging.warning(
g_tr('StatementLoader', "Unhandled tax pattern found: ") +
f"{note}")
return
dividend_note = parts.group(1) + '%'
country_code = parts.group(2)
try:
dividend_id, old_tax = readSQL(
self.db, "SELECT id, sum_tax FROM dividends "
"WHERE timestamp=:timestamp AND account_id=:account_id "
"AND asset_id=:asset_id AND note LIKE :dividend_description",
[(":timestamp", timestamp), (":account_id", account_id),
(":asset_id", asset_id),
(":dividend_description", dividend_note)])
except:
logging.warning(
g_tr('StatementLoader',
"Dividend not found for withholding tax: ") + f"{note}")
return
_ = executeSQL(
self.db,
"UPDATE dividends SET sum_tax=:tax, note_tax=:note WHERE id=:dividend_id",
[(":dividend_id", dividend_id), (":tax", old_tax + amount),
(":note", country_code + " tax")])
self.db.commit()
logging.info(
g_tr('StatementLoader', "Withholding tax added: ") + f"{note}")
def deleteTrade(self, account_id, asset_id, timestamp, _settlement, number, qty, price, _fee):
_ = executeSQL(self.db, "DELETE FROM trades "
"WHERE timestamp=:timestamp AND asset_id=:asset "
"AND account_id=:account AND number=:number AND qty=:qty AND price=:price",
[(":timestamp", timestamp), (":asset", asset_id), (":account", account_id),
(":number", number), (":qty", -qty), (":price", price)])
self.db.commit()
logging.info(f"Trade #{number} cancelled for account {account_id} asset {asset_id} @{timestamp}: {qty}x{price}")
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 reconcileAtCurrentOperation(self):
model = self.current_index.model()
timestamp = model.data(model.index(self.current_index.row(), 2),
Qt.DisplayRole)
account_id = model.data(model.index(self.current_index.row(), 3),
Qt.DisplayRole)
_ = executeSQL(
model.database(),
"UPDATE accounts SET reconciled_on=:timestamp WHERE id = :account_id",
[(":timestamp", timestamp), (":account_id", account_id)])
model.select()
def addNewAsset(db, symbol, name, asset_type, isin, data_source=-1):
_ = executeSQL(db, "INSERT INTO assets(name, type_id, full_name, isin, src_id) "
"VALUES(:symbol, :type, :full_name, :isin, :data_src)",
[(":symbol", symbol), (":type", asset_type), (":full_name", name),
(":isin", isin), (":data_src", data_source)])
db.commit()
asset_id = readSQL(db, "SELECT id FROM assets WHERE name=:symbol", [(":symbol", symbol)])
if asset_id is not None:
logging.info(g_tr('', "New asset with id ") + f"{asset_id}" + g_tr('', " was added: ") + f"{symbol} - '{name}'")
else:
logging.error(g_tr('', "Failed to add new asset: "), + f"{symbol}")
return asset_id
def refresh_session(self):
session_id = self.get_ru_tax_session()
client_secret = readSQL(
self.db,
"SELECT value FROM settings WHERE name='RuTaxClientSecret'")
refresh_token = readSQL(
self.db,
"SELECT value FROM settings WHERE name='RuTaxRefreshToken'")
s = requests.Session()
s.headers['ClientVersion'] = '2.9.0'
s.headers['Device-Id'] = str(uuid.uuid1())
s.headers['Device-OS'] = 'Android'
s.headers['sessionId'] = session_id
s.headers['Content-Type'] = 'application/json; charset=UTF-8'
s.headers['Accept-Encoding'] = 'gzip'
s.headers['User-Agent'] = 'okhttp/4.2.2'
payload = '{' + f'"client_secret":"{client_secret}","refresh_token":"{refresh_token}"' + '}'
response = s.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/refresh',
data=payload)
if response.status_code == 200:
logging.info(
g_tr('SlipsTaxAPI', "Session refreshed: ") +
f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_session WHERE name='RuTaxSessionId'",
[(":new_session", new_session_id)])
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_refresh_token WHERE name='RuTaxRefreshToken'",
[(":new_refresh_token", new_refresh_token)])
else:
logging.error(
g_tr('SlipsTaxAPI', "Can't refresh session, response: ") +
f"{response}/{response.text}")
def createDividend(self, timestamp, account_id, asset_id, amount, note):
id = readSQL(self.db, "SELECT id FROM dividends WHERE timestamp=:timestamp "
"AND account_id=:account_id AND asset_id=:asset_id AND note=:note",
[(":timestamp", timestamp), (":account_id", account_id), (":asset_id", asset_id), (":note", note)])
if id:
logging.warning(g_tr('StatementLoader', "Dividend already exists: ") + f"{note}")
return
_ = executeSQL(self.db, "INSERT INTO dividends (timestamp, account_id, asset_id, sum, note) "
"VALUES (:timestamp, :account_id, :asset_id, :sum, :note)",
[(":timestamp", timestamp), (":account_id", account_id), (":asset_id", asset_id),
(":sum", amount), (":note", note)])
self.db.commit()
logging.info(g_tr('StatementLoader', "Dividend added: ") + f"{note}")
def login_fns(self):
client_secret = readSQL(
self.db,
"SELECT value FROM settings WHERE name='RuTaxClientSecret'")
inn = self.InnEdit.text()
password = self.PasswordEdit.text()
s = requests.Session()
s.headers['ClientVersion'] = '2.9.0'
s.headers['Device-Id'] = str(uuid.uuid1())
s.headers['Device-OS'] = 'Android'
s.headers['Content-Type'] = 'application/json; charset=UTF-8'
s.headers['Accept-Encoding'] = 'gzip'
s.headers['User-Agent'] = 'okhttp/4.2.2'
payload = '{' + f'"client_secret":"{client_secret}","inn":"{inn}","password":"******"' + '}'
response = s.post(
'https://irkkt-mobile.nalog.ru:8888/v2/mobile/users/lkfl/auth',
data=payload)
if response.status_code != 200:
logging.error(
g_tr('SlipsTaxAPI', "FNS login failed: ") +
f"{response}/{response.text}")
return
logging.info(
g_tr('SlipsTaxAPI', "FNS login successful: ") + f"{response.text}")
json_content = json.loads(response.text)
new_session_id = json_content['sessionId']
new_refresh_token = json_content['refresh_token']
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_session WHERE name='RuTaxSessionId'",
[(":new_session", new_session_id)])
_ = executeSQL(
self.db,
"UPDATE settings SET value=:new_refresh_token WHERE name='RuTaxRefreshToken'",
[(":new_refresh_token", new_refresh_token)])
self.db.commit()
self.accept()
def prepareCategoryReport(self, begin, end, category_id, group_dates):
if category_id == 0:
self.report_failure.emit(
g_tr(
'Reports',
"You should select category to create By Category report"))
return False
self.query = executeSQL(
self.db,
"SELECT a.timestamp, ac.name AS account, p.name, d.sum, d.note "
"FROM actions AS a "
"LEFT JOIN action_details AS d ON d.pid=a.id "
"LEFT JOIN agents AS p ON p.id=a.peer_id "
"LEFT JOIN accounts AS ac ON ac.id=a.account_id "
"WHERE a.timestamp>=:begin AND a.timestamp<=:end "
"AND d.category_id=:category_id", [(":category_id", category_id),
(":begin", begin),
(":end", end)],
forward_only=False)
return True
def createTrade(self, account_id, asset_id, timestamp, settlement, number, qty, price, fee, coupon=0.0):
trade_id = readSQL(self.db,
"SELECT id FROM trades "
"WHERE timestamp=:timestamp AND asset_id = :asset "
"AND account_id = :account AND number = :number AND qty = :qty AND price = :price",
[(":timestamp", timestamp), (":asset", asset_id), (":account", account_id),
(":number", number), (":qty", qty), (":price", price)])
if trade_id:
logging.info(g_tr('StatementLoader', "Trade #") + f"{number}" +
g_tr('StatementLoader', " already exists in ledger. Skipped"))
return
_ = executeSQL(self.db,
"INSERT INTO trades (timestamp, settlement, corp_action_id, number, account_id, "
"asset_id, qty, price, fee, coupon) "
"VALUES (:timestamp, :settlement, 0, :number, :account, "
":asset, :qty, :price, :fee, :coupon)",
[(":timestamp", timestamp), (":settlement", settlement), (":number", number),
(":account", account_id), (":asset", asset_id), (":qty", float(qty)),
(":price", float(price)), (":fee", -float(fee)), (":coupon", float(coupon))])
self.db.commit()
logging.info(f"Trade #{number} added for account {account_id} asset {asset_id} @{timestamp}: {qty}x{price}")
def calculateBalances(db, timestamp, currency, active_accounts_only):
_ = executeSQL(db, "DELETE FROM t_last_quotes")
_ = executeSQL(db, "DELETE FROM t_last_dates")
_ = executeSQL(db, "DELETE FROM balances_aux")
_ = executeSQL(db, "DELETE FROM balances")
_ = executeSQL(
db, "INSERT INTO t_last_quotes(timestamp, asset_id, quote) "
"SELECT MAX(timestamp) AS timestamp, asset_id, quote "
"FROM quotes "
"WHERE timestamp <= :balances_timestamp "
"GROUP BY asset_id", [(":balances_timestamp", timestamp)])
_ = executeSQL(
db, "INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT account_id AS ref_id, MAX(timestamp) AS timestamp "
"FROM ledger "
"WHERE timestamp <= :balances_timestamp "
"GROUP BY ref_id", [(":balances_timestamp", timestamp)])
_ = executeSQL(
db,
"INSERT INTO balances_aux(account_type, account, currency, balance, "
"balance_adj, unreconciled_days, active) "
"SELECT a.type_id AS account_type, l.account_id AS account, a.currency_id AS currency, "
"SUM(CASE WHEN l.book_account = 4 THEN l.amount*act_q.quote ELSE l.amount END) AS balance, "
"SUM(CASE WHEN l.book_account = 4 THEN l.amount*act_q.quote*cur_q.quote/cur_adj_q.quote "
"ELSE l.amount*cur_q.quote/cur_adj_q.quote END) AS balance_adj, "
"(d.timestamp - coalesce(a.reconciled_on, 0))/86400 AS unreconciled_days, "
"a.active AS active "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN t_last_quotes AS act_q ON l.asset_id = act_q.asset_id "
"LEFT JOIN t_last_quotes AS cur_q ON a.currency_id = cur_q.asset_id "
"LEFT JOIN t_last_quotes AS cur_adj_q ON cur_adj_q.asset_id = :base_currency "
"LEFT JOIN t_last_dates AS d ON l.account_id = d.ref_id "
"WHERE (book_account = :money_book OR book_account = :assets_book OR book_account = :liabilities_book) "
"AND l.timestamp <= :balances_timestamp "
"GROUP BY l.account_id "
"HAVING ABS(balance)>0.0001",
[(":base_currency", currency), (":money_book", BookAccount.Money),
(":assets_book", BookAccount.Assets),
(":liabilities_book", BookAccount.Liabilities),
(":balances_timestamp", timestamp)])
_ = executeSQL(
db,
"INSERT INTO balances(level1, level2, account_name, currency_name, "
"balance, balance_adj, days_unreconciled, active) "
"SELECT level1, level2, account, currency, balance, balance_adj, unreconciled_days, active "
"FROM ( "
"SELECT 0 AS level1, 0 AS level2, account_type, a.name AS account, c.name AS currency, "
"balance, balance_adj, unreconciled_days, b.active "
"FROM balances_aux AS b LEFT JOIN accounts AS a ON b.account = a.id "
"LEFT JOIN assets AS c ON b.currency = c.id "
"WHERE b.active >= :active_only "
"UNION "
"SELECT 0 AS level1, 1 AS level2, account_type, t.name AS account, c.name AS currency, "
"0 AS balance, SUM(balance_adj) AS balance_adj, 0 AS unreconciled_days, 1 AS active "
"FROM balances_aux AS b LEFT JOIN account_types AS t ON b.account_type = t.id "
"LEFT JOIN assets AS c ON c.id = :base_currency "
"WHERE active >= :active_only "
"GROUP BY account_type "
"UNION "
"SELECT 1 AS level1, 0 AS level2, -1 AS account_type, 'Total' AS account, c.name AS currency, "
"0 AS balance, SUM(balance_adj) AS balance_adj, 0 AS unreconciled_days, 1 AS active "
"FROM balances_aux LEFT JOIN assets AS c ON c.id = :base_currency "
"WHERE active >= :active_only "
") ORDER BY level1, account_type, level2",
[(":base_currency", currency), (":active_only", active_accounts_only)])
db.commit()
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())
})
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 appendTransaction(self, book, amount, value=None):
seq_id = self.current_seq
timestamp = self.current[TIMESTAMP]
if book == BookAccount.Assets:
asset_id = self.current[ASSET_ID]
else:
asset_id = self.current[CURRENCY_ID]
account_id = self.current[ACCOUNT_ID]
if book == BookAccount.Costs or book == BookAccount.Incomes:
peer_id = self.current[COUPON_PEER]
category_id = self.current[PRICE_CATEGORY]
tag_id = self.current[FEE_TAX_TAG]
else: # TODO - check None for empty values (to put NULL in DB)
peer_id = None
category_id = None
tag_id = None
try:
old_sid, old_amount, old_value = readSQL(
self.db, "SELECT sid, sum_amount, sum_value FROM ledger_sums "
"WHERE book_account = :book AND asset_id = :asset_id "
"AND account_id = :account_id AND sid <= :seq_id "
"ORDER BY sid DESC LIMIT 1", [(":book", book),
(":asset_id", asset_id),
(":account_id", account_id),
(":seq_id", seq_id)])
except:
old_sid = -1
old_amount = 0.0
old_value = 0.0
new_amount = old_amount + amount
if value is None:
new_value = old_value
else:
new_value = old_value + value
if (abs(new_amount - old_amount) +
abs(new_value - old_value)) <= (2 * Setup.CALC_TOLERANCE):
return # we have zero amount - no reason to put it into ledger
_ = executeSQL(
self.db,
"INSERT INTO ledger (timestamp, sid, book_account, asset_id, account_id, "
"amount, value, peer_id, category_id, tag_id) "
"VALUES(:timestamp, :sid, :book, :asset_id, :account_id, "
":amount, :value, :peer_id, :category_id, :tag_id)",
[(":timestamp", timestamp), (":sid", seq_id), (":book", book),
(":asset_id", asset_id), (":account_id", account_id),
(":amount", amount), (":value", value), (":peer_id", peer_id),
(":category_id", category_id), (":tag_id", tag_id)])
if seq_id == old_sid:
_ = executeSQL(
self.db,
"UPDATE ledger_sums SET sum_amount = :new_amount, sum_value = :new_value"
" WHERE sid = :sid AND book_account = :book"
" AND asset_id = :asset_id AND account_id = :account_id",
[(":new_amount", new_amount), (":new_value", new_value),
(":sid", seq_id), (":book", book), (":asset_id", asset_id),
(":account_id", account_id)])
else:
_ = executeSQL(
self.db,
"INSERT INTO ledger_sums(sid, timestamp, book_account, "
"asset_id, account_id, sum_amount, sum_value) "
"VALUES(:sid, :timestamp, :book, :asset_id, "
":account_id, :new_amount, :new_value)",
[(":sid", seq_id), (":timestamp", timestamp), (":book", book),
(":asset_id", asset_id), (":account_id", account_id),
(":new_amount", new_amount), (":new_value", new_value)])
self.db.commit()
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 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 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 prepareProfitLossReport(self, begin, end, account_id, group_dates):
if account_id == 0:
self.report_failure.emit(
g_tr('Reports',
"You should select account to create Profit/Loss report"))
return False
_ = executeSQL(self.db, "DELETE FROM t_months")
_ = executeSQL(
self.db, "INSERT INTO t_months(asset_id, month, last_timestamp) "
"SELECT DISTINCT(l.asset_id) AS asset_id, m.m_start, MAX(q.timestamp) AS last_timestamp "
"FROM ledger AS l "
"LEFT JOIN "
"(WITH RECURSIVE months(m_start) AS "
"( "
" VALUES(CAST(strftime('%s', date(:begin, 'unixepoch', 'start of month')) AS INTEGER)) "
" UNION ALL "
" SELECT CAST(strftime('%s', date(m_start, 'unixepoch', '+1 month')) AS INTEGER) "
" FROM months "
" WHERE m_start < :end "
") "
"SELECT m_start FROM months) AS m "
"LEFT JOIN quotes AS q ON q.timestamp<=m.m_start AND q.asset_id=l.asset_id "
"WHERE l.timestamp>=:begin AND l.timestamp<=:end AND l.account_id=:account_id "
"GROUP BY m.m_start, l.asset_id "
"ORDER BY m.m_start, l.asset_id", [(":account_id", account_id),
(":begin", begin),
(":end", end)])
self.db.commit()
self.query = executeSQL(
self.db,
"SELECT DISTINCT(m.month) AS period, coalesce(t.transfer, 0) AS transfer, coalesce(a.assets, 0) AS assets, "
"coalesce(p.result, 0) AS result, coalesce(o.profit, 0) AS profit, coalesce(d.dividend, 0) AS dividend, "
"coalesce(f.tax_fee, 0) AS tax_fee "
"FROM t_months AS m "
"LEFT JOIN ( "
" SELECT mt.month, SUM(-l.amount) AS transfer "
" FROM t_months AS mt "
" LEFT JOIN ledger AS l ON mt.month = "
" CAST(strftime('%s', date(l.timestamp, 'unixepoch', 'start of month')) AS INTEGER) "
" AND mt.asset_id=l.asset_id "
" WHERE l.book_account=:book_transfers AND l.account_id=:account_id GROUP BY mt.month "
") AS t ON t.month = m.month "
"LEFT JOIN ( "
" SELECT ma.month, SUM(l.amount*q.quote) AS assets "
" FROM t_months AS ma "
" LEFT JOIN ledger AS l ON l.timestamp<=ma.month AND l.asset_id=ma.asset_id "
" LEFT JOIN quotes AS q ON ma.last_timestamp=q.timestamp AND ma.asset_id=q.asset_id "
" WHERE l.account_id = 76 AND (l.book_account=:book_money OR l.book_account=:book_assets) "
" GROUP BY ma.month "
") AS a ON a.month = m.month "
"LEFT JOIN ( "
" SELECT CAST(strftime('%s', date(l.timestamp, 'unixepoch', 'start of month')) AS INTEGER) AS month,"
" SUM(-l.amount) as result"
" FROM ledger AS l "
" WHERE (l.book_account=:book_costs OR l.book_account=:book_incomes) AND l.account_id=:account_id "
" GROUP BY month "
") AS p ON p.month = m.month "
"LEFT JOIN ( "
" SELECT CAST(strftime('%s', date(l.timestamp, 'unixepoch', 'start of month')) "
" AS INTEGER) AS month, SUM(-l.amount) as profit "
" FROM ledger AS l "
" WHERE (l.book_account=:book_costs OR l.book_account=:book_incomes) "
" AND category_id=9 AND l.account_id=:account_id "
" GROUP BY month "
") AS o ON o.month = m.month "
"LEFT JOIN ( "
" SELECT CAST(strftime('%s', date(l.timestamp, 'unixepoch', 'start of month')) AS INTEGER) "
" AS month, SUM(-l.amount) as dividend "
" FROM ledger AS l "
" WHERE (l.book_account=:book_costs OR l.book_account=:book_incomes) "
" AND (l.category_id=7 OR l.category_id=8) AND l.account_id=:account_id "
" GROUP BY month "
") AS d ON d.month = m.month "
"LEFT JOIN ( "
" SELECT CAST(strftime('%s', date(l.timestamp, 'unixepoch', 'start of month')) "
" AS INTEGER) AS month, SUM(-l.amount) as tax_fee "
" FROM ledger AS l "
" WHERE l.book_account=:book_costs AND l.category_id<>7 AND l.category_id<>8 AND l.account_id=:account_id "
" GROUP BY month "
") AS f ON f.month = m.month",
[(":account_id", account_id), (":book_costs", BookAccount.Costs),
(":book_incomes", BookAccount.Incomes),
(":book_money", BookAccount.Money),
(":book_assets", BookAccount.Assets),
(":book_transfers", BookAccount.Transfers)],
forward_only=False)
return True
def calculateHoldings(db, timestamp, currency):
_ = executeSQL(db, "DELETE FROM t_last_quotes")
_ = executeSQL(db, "DELETE FROM t_last_assets")
_ = executeSQL(db, "DELETE FROM holdings_aux")
_ = executeSQL(db, "DELETE FROM holdings")
_ = executeSQL(
db, "INSERT INTO t_last_quotes(timestamp, asset_id, quote) "
"SELECT MAX(timestamp) AS timestamp, asset_id, quote "
"FROM quotes "
"WHERE timestamp <= :balances_timestamp "
"GROUP BY asset_id", [(":balances_timestamp", timestamp)])
# TODO Is account name really required in this temporary table?
_ = executeSQL(
db, "INSERT INTO t_last_assets (id, name, total_value) "
"SELECT a.id, a.name, "
"SUM(CASE WHEN a.currency_id = l.asset_id THEN l.amount "
"ELSE (l.amount*q.quote) END) AS total_value "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN t_last_quotes AS q ON l.asset_id = q.asset_id "
"WHERE (l.book_account = 3 OR l.book_account = 4 OR l.book_account = 5) "
"AND a.type_id = 4 AND l.timestamp <= :holdings_timestamp "
"GROUP BY a.id "
"HAVING ABS(total_value) > :tolerance",
[(":holdings_timestamp", timestamp),
(":tolerance", Setup.DISP_TOLERANCE)])
_ = executeSQL(
db,
"INSERT INTO holdings_aux (currency, account, asset, qty, value, quote, quote_adj, total, total_adj) "
"SELECT a.currency_id, l.account_id, l.asset_id, sum(l.amount) AS qty, sum(l.value), "
"q.quote, q.quote*cur_q.quote/cur_adj_q.quote, t.total_value, t.total_value*cur_q.quote/cur_adj_q.quote "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN t_last_quotes AS q ON l.asset_id = q.asset_id "
"LEFT JOIN t_last_quotes AS cur_q ON a.currency_id = cur_q.asset_id "
"LEFT JOIN t_last_quotes AS cur_adj_q ON cur_adj_q.asset_id = :recalc_currency "
"LEFT JOIN t_last_assets AS t ON l.account_id = t.id "
"WHERE l.book_account = 4 AND l.timestamp <= :holdings_timestamp "
"GROUP BY l.account_id, l.asset_id "
"HAVING ABS(qty) > :tolerance", [(":recalc_currency", currency),
(":holdings_timestamp", timestamp),
(":tolerance", Setup.DISP_TOLERANCE)])
_ = executeSQL(
db,
"INSERT INTO holdings_aux (currency, account, asset, qty, value, quote, quote_adj, total, total_adj) "
"SELECT a.currency_id, l.account_id, l.asset_id, sum(l.amount) AS qty, sum(l.value), 1, "
"cur_q.quote/cur_adj_q.quote, t.total_value, t.total_value*cur_q.quote/cur_adj_q.quote "
"FROM ledger AS l "
"LEFT JOIN accounts AS a ON l.account_id = a.id "
"LEFT JOIN t_last_quotes AS cur_q ON a.currency_id = cur_q.asset_id "
"LEFT JOIN t_last_quotes AS cur_adj_q ON cur_adj_q.asset_id = :recalc_currency "
"LEFT JOIN t_last_assets AS t ON l.account_id = t.id "
"WHERE (l.book_account = 3 OR l.book_account = 5) AND a.type_id = 4 AND l.timestamp <= :holdings_timestamp "
"GROUP BY l.account_id, l.asset_id "
"HAVING ABS(qty) > :tolerance", [(":recalc_currency", currency),
(":holdings_timestamp", timestamp),
(":tolerance", Setup.DISP_TOLERANCE)])
_ = executeSQL(
db,
"INSERT INTO holdings (level1, level2, currency, account, asset, asset_name, "
"qty, open, quote, share, profit_rel, profit, value, value_adj) "
"SELECT * FROM ( "
""
"SELECT 0 AS level1, 0 AS level2, c.name AS currency, a.name AS account, "
"s.name AS asset, s.full_name AS asset_name, "
"h.qty, h.value/h.qty AS open, h.quote, 100*h.quote*h.qty/h.total AS share, "
"100*(h.quote*h.qty/h.value-1) AS profit_rel, h.quote*h.qty-h.value AS profit, "
"h.qty*h.quote AS value, h.qty*h.quote_adj AS value_adj "
"FROM holdings_aux AS h "
"LEFT JOIN assets AS c ON h.currency = c.id "
"LEFT JOIN accounts AS a ON h.account = a.id "
"LEFT JOIN assets AS s ON h.asset = s.id "
"UNION "
"SELECT 0 AS level1, 1 AS level2, c.name AS currency, "
"a.name AS account, '' AS asset, '' AS asset_name, "
"NULL AS qty, NULL AS open, NULL as quote, NULL AS share, "
"100*SUM(h.quote*h.qty-h.value)/(SUM(h.qty*h.quote)-SUM(h.quote*h.qty-h.value)) AS profit_rel, "
"SUM(h.quote*h.qty-h.value) AS profit, SUM(h.qty*h.quote) AS value, "
"SUM(h.qty*h.quote_adj) AS value_adj "
"FROM holdings_aux AS h "
"LEFT JOIN assets AS c ON h.currency = c.id "
"LEFT JOIN accounts AS a ON h.account = a.id "
"GROUP BY currency, account "
"UNION "
"SELECT 1 AS level1, 1 AS level2, c.name AS currency, c.name AS account, '' AS asset, "
"c.full_name AS asset_name, NULL AS qty, NULL AS open, NULL as quote, NULL AS share, "
"100*SUM(h.quote*h.qty-h.value)/(SUM(h.qty*h.quote)-SUM(h.quote*h.qty-h.value)) AS profit_rel, "
"SUM(h.quote*h.qty-h.value) AS profit, SUM(h.qty*h.quote) AS value, "
"SUM(h.qty*h.quote_adj) AS value_adj "
"FROM holdings_aux AS h "
"LEFT JOIN assets AS c ON h.currency = c.id "
"GROUP BY currency "
") ORDER BY currency, level1 DESC, account, level2 DESC")
