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