def displayText(self, value, locale): item_name = '' query = executeSQL( f"SELECT {self._field} FROM {self._table} WHERE id=:id", [(":id", value)]) while query.next(): readSQLrecord(query) item_name = item_name + '/' + readSQLrecord( query) if item_name else readSQLrecord(query) return item_name
def prepare_chart_data(self): min_price = max_price = 0 min_ts = max_ts = 0 self.currency_name = JalDB().get_asset_name(JalDB().get_account_currency(self.account_id)) start_time = readSQL("SELECT MAX(ts) FROM " # Take either last "empty" timestamp "(SELECT coalesce(MAX(timestamp), 0) AS ts " "FROM ledger_sums WHERE account_id=:account_id AND asset_id=:asset_id " "AND book_account=:assets_book AND sum_amount==0 " "UNION " # or first timestamp where position started to appear "SELECT coalesce(MIN(timestamp), 0) AS ts " "FROM ledger_sums WHERE account_id=:account_id AND asset_id=:asset_id " "AND book_account=:assets_book AND sum_amount!=0)", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":assets_book", BookAccount.Assets)]) # Get quotes quotes query = executeSQL("SELECT timestamp, quote FROM quotes WHERE asset_id=:asset_id AND timestamp>:last", [(":asset_id", self.asset_id), (":last", start_time)]) while query.next(): quote = readSQLrecord(query, named=True) self.quotes.append({'timestamp': quote['timestamp'] * 1000, 'quote': quote['quote']}) # timestamp to ms min_price = quote['quote'] if min_price == 0 or quote['quote'] < min_price else min_price max_price = quote['quote'] if quote['quote'] > max_price else max_price min_ts = quote['timestamp'] if min_ts == 0 or quote['timestamp'] < min_ts else min_ts max_ts = quote['timestamp'] if quote['timestamp'] > max_ts else max_ts # Get deals quotes query = executeSQL("SELECT timestamp, price, qty FROM trades " "WHERE account_id=:account_id AND asset_id=:asset_id AND timestamp>=:last", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":last", start_time)]) while query.next(): trade = readSQLrecord(query, named=True) self.trades.append({'timestamp': trade['timestamp'] * 1000, 'price': trade['price'], 'qty': trade['qty']}) min_price = trade['price'] if min_price == 0 or trade['price'] < min_price else min_price max_price = trade['price'] if trade['price'] > max_price else max_price min_ts = trade['timestamp'] if min_ts == 0 or trade['timestamp'] < min_ts else min_ts max_ts = trade['timestamp'] if trade['timestamp'] > max_ts else max_ts # Round min/max values to near "round" values in order to have 10 nice intervals step = 10 ** floor(log10(max_price - min_price)) min_price = floor(min_price / step) * step max_price = ceil(max_price / step) * step # Add a gap at the beginning and end min_ts -= 86400 * 3 max_ts += 86400 * 3 self.range = [min_ts, max_ts, min_price, max_price]
def test_ledger(prepare_db_ledger): actions = [(1638349200, 1, 1, [(5, -100.0)]), (1638352800, 1, 1, [(6, -30.0), (8, 55.0)]), (1638356400, 1, 1, [(7, 84.0)])] create_actions(actions) # Build ledger from scratch ledger = Ledger() ledger.rebuild(from_timestamp=0) # validate book amounts expected_book_values = [None, 130.0, -139.0, 9.0, None, 0.0] query = executeSQL( "SELECT MAX(id) AS mid, book_account, amount_acc, value_acc " "FROM ledger GROUP BY book_account") while query.next(): row = readSQLrecord(query, named=True) assert row['amount_acc'] == expected_book_values[row['book_account']] actions = [(1638360000, 1, 1, [(5, -34.0)]), (1638363600, 1, 1, [(7, 11.0)])] create_actions(actions) # Build ledger for recent transactions only ledger = Ledger() ledger.rebuild() # validate book amounts and values expected_book_amounts = [None, 164.0, -150.0, -0.0, None, -14.0] expected_book_values = [0.0, 0.0, 0.0, 0.0, 0.0, 0.0] query = executeSQL( "SELECT MAX(id) AS mid, book_account, amount_acc, value_acc " "FROM ledger GROUP BY book_account") while query.next(): row = readSQLrecord(query, named=True) assert row['amount_acc'] == expected_book_amounts[row['book_account']] assert row['value_acc'] == expected_book_values[row['book_account']] # Re-build from the middle - validation should pass again ledger.rebuild(from_timestamp=1638352800) query = executeSQL( "SELECT MAX(id) AS mid, book_account, amount_acc, value_acc " "FROM ledger GROUP BY book_account") while query.next(): row = readSQLrecord(query, named=True) assert row['amount_acc'] == expected_book_amounts[row['book_account']] assert row['value_acc'] == expected_book_values[row['book_account']]
def processActionDetails(self): query = executeSQL( "SELECT sum as amount, category_id, tag_id FROM action_details AS d WHERE pid=:pid", [(":pid", self.current['id'])]) while query.next(): amount, self.current['category'], self.current[ 'tag'] = readSQLrecord(query) book = BookAccount.Costs if amount < 0 else BookAccount.Incomes self.appendTransaction(book, -amount)
def fetchMore(self, index): new_size = len(self._data) + self.PAGE_SIZE new_size = new_size if new_size < self._row_count else self._row_count self.beginInsertRows(index, len(self._data), new_size - 1) i = 0 while (i < self.PAGE_SIZE) and self._query.next(): values = readSQLrecord(self._query, named=True) self._data.append(values) i += 1 self.endInsertRows()
def prepare_derivatives(self): derivatives = [] # Take all actions without conversion query = executeSQL("SELECT s.symbol, d.qty AS qty, cc.iso_code AS country_iso, " "o.timestamp AS o_date, qo.quote AS o_rate, o.settlement AS os_date, o.number AS o_number, " "qos.quote AS os_rate, o.price AS o_price, o.qty AS o_qty, o.fee AS o_fee, " "c.timestamp AS c_date, qc.quote AS c_rate, c.settlement AS cs_date, c.number AS c_number, " "qcs.quote AS cs_rate, c.price AS c_price, c.qty AS c_qty, c.fee AS c_fee " "FROM deals AS d " "JOIN trades AS o ON o.id=d.open_op_id AND o.op_type=d.open_op_type " "JOIN trades AS c ON c.id=d.close_op_id AND c.op_type=d.close_op_type " "LEFT JOIN accounts AS a ON a.id = :account_id " "LEFT JOIN assets_ext AS s ON s.id = o.asset_id AND s.currency_id=a.currency_id " "LEFT JOIN countries AS cc ON cc.id = a.country_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 AND qo.currency_id=:base_currency " "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 AND qos.currency_id=:base_currency " "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 AND qc.currency_id=:base_currency " "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 AND qcs.currency_id=:base_currency " "WHERE c.settlement>=:begin AND c.settlement<:end AND d.account_id=:account_id " "AND s.type_id = :derivative " "ORDER BY s.symbol, o.timestamp, c.timestamp", [(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id), (":base_currency", JalSettings().getValue('BaseCurrency')), (":derivative", PredefinedAsset.Derivative)]) while query.next(): deal = readSQLrecord(query, named=True) if not self.use_settlement: deal['os_rate'] = deal['o_rate'] deal['cs_rate'] = deal['c_rate'] deal['o_type'] = "Покупка" if deal['qty'] >= 0 else "Продажа" deal['c_type'] = "Продажа" if deal['qty'] >= 0 else "Покупка" deal['o_amount'] = round(deal['o_price'] * abs(deal['qty']), 2) deal['o_amount_rub'] = round(deal['o_amount'] * deal['os_rate'], 2) if deal['os_rate'] else 0 deal['c_amount'] = round(deal['c_price'] * abs(deal['qty']), 2) deal['c_amount_rub'] = round(deal['c_amount'] * deal['cs_rate'], 2) if deal['cs_rate'] else 0 deal['o_fee'] = deal['o_fee'] * abs(deal['qty'] / deal['o_qty']) deal['c_fee'] = deal['c_fee'] * abs(deal['qty'] / deal['c_qty']) deal['o_fee_rub'] = round(deal['o_fee'] * deal['o_rate'], 2) if deal['o_rate'] else 0 deal['c_fee_rub'] = round(deal['c_fee'] * deal['c_rate'], 2) if deal['c_rate'] else 0 deal['income_rub'] = deal['c_amount_rub'] if deal['qty'] >= 0 else deal['o_amount_rub'] deal['income'] = deal['c_amount'] if deal['qty'] >= 0 else deal['o_amount'] deal['spending_rub'] = deal['o_amount_rub'] if deal['qty'] >= 0 else deal['c_amount_rub'] deal['spending_rub'] = deal['spending_rub'] + deal['o_fee_rub'] + deal['c_fee_rub'] deal['spending'] = deal['o_amount'] if deal['qty'] >= 0 else deal['c_amount'] deal['spending'] = deal['spending'] + deal['o_fee'] + deal['c_fee'] deal['profit_rub'] = deal['income_rub'] - deal['spending_rub'] deal['profit'] = deal['income'] - deal['spending'] deal['report_template'] = "trade" derivatives.append(deal) self.insert_totals(derivatives, ["income_rub", "spending_rub", "profit_rub", "profit"]) return derivatives
def prepareData(self): self._data = [] if self._begin == 0 and self._end == 0: self._row_count = 0 else: query_text = f"SELECT * FROM operation_sequence WHERE timestamp>={self._begin} AND timestamp<={self._end}" if self._account: query_text += f" AND account_id={self._account}" query = executeSQL(query_text, forward_only=True) while query.next(): self._data.append(readSQLrecord(query, named=True)) self.modelReset.emit()
def UpdateQuotes(self, start_timestamp, end_timestamp): self.PrepareRussianCBReader() jal_db = JalDB() query = executeSQL("WITH _holdings AS ( " "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 = :assets_book 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 = :money_book OR l.book_account = :liabilities_book) " "AND l.timestamp >= :start_timestamp AND l.timestamp <= :end_timestamp " ") " "SELECT h.asset AS asset_id, a.name AS name, a.src_id AS feed_id, a.isin AS isin, " "MIN(q.timestamp) AS first_timestamp, MAX(q.timestamp) AS last_timestamp " "FROM _holdings 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", [(":start_timestamp", start_timestamp), (":end_timestamp", end_timestamp), (":assets_book", BookAccount.Assets), (":money_book", BookAccount.Money), (":liabilities_book", BookAccount.Liabilities), (":tolerance", Setup.CALC_TOLERANCE)]) while query.next(): asset = readSQLrecord(query, named=True) first_timestamp = asset['first_timestamp'] if asset['first_timestamp'] != '' else 0 last_timestamp = asset['last_timestamp'] if asset['last_timestamp'] != '' else 0 if start_timestamp < first_timestamp: from_timestamp = start_timestamp else: from_timestamp = last_timestamp if last_timestamp > start_timestamp else start_timestamp if end_timestamp < from_timestamp: continue try: data = self.data_loaders[asset['feed_id']](asset['asset_id'], asset['name'], asset['isin'], from_timestamp, end_timestamp) except (xml_tree.ParseError, pd.errors.EmptyDataError, KeyError): logging.warning(g_tr('QuotesUpdateDialog', "No data were downloaded for ") + f"{asset}") continue if data is not None: for date, quote in data.iterrows(): # Date in pandas dataset is in UTC by default jal_db.update_quote(asset['asset_id'], int(date.timestamp()), float(quote[0])) jal_db.commit() logging.info(g_tr('QuotesUpdateDialog', "Download completed"))
def next_corporate_action(self, actions, operation_id, symbol, qty, basis, level, group): # get list of deals that were closed as result of current corporate action open_query = executeSQL("SELECT open_op_id AS open_op_id, open_op_type AS op_type " "FROM deals " "WHERE close_op_id=:close_op_id AND close_op_type=:corp_action " "ORDER BY id", [(":close_op_id", operation_id), (":corp_action", LedgerTransaction.CorporateAction)]) while open_query.next(): open_id, open_type = readSQLrecord(open_query) if open_type == LedgerTransaction.Trade: qty = self.output_purchase(actions, open_id, qty, basis, level, group) elif open_type == LedgerTransaction.CorporateAction: self.proceed_corporate_action(actions, open_id, symbol, qty, basis, level, group) else: assert False
def prepare_dividends(self): dividends = [] query = executeSQL("SELECT d.type, d.timestamp AS payment_date, s.symbol, s.full_name AS full_name, " "s.isin AS isin, d.amount AS amount, d.tax AS tax, q.quote AS rate, p.quote AS price, " "c.name AS country, c.iso_code AS country_iso, c.tax_treaty AS tax_treaty " "FROM dividends AS d " "LEFT JOIN accounts AS a ON d.account_id = a.id " "LEFT JOIN assets_ext AS s ON s.id = d.asset_id AND s.currency_id=a.currency_id " "LEFT JOIN countries AS c ON s.country_id = c.id " "LEFT JOIN t_last_dates AS ld ON d.timestamp=ld.ref_id " "LEFT JOIN quotes AS q ON ld.timestamp=q.timestamp AND a.currency_id=q.asset_id AND q.currency_id=:base_currency " "LEFT JOIN quotes AS p ON d.timestamp=p.timestamp AND d.asset_id=p.asset_id AND p.currency_id=a.currency_id " "WHERE d.timestamp>=:begin AND d.timestamp<:end AND d.account_id=:account_id " " AND d.amount>0 AND (d.type=:type_dividend OR d.type=:type_stock_dividend) " "ORDER BY d.timestamp", [(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id), (":base_currency", JalSettings().getValue('BaseCurrency')), (":type_dividend", Dividend.Dividend), (":type_stock_dividend", Dividend.StockDividend)]) while query.next(): dividend = readSQLrecord(query, named=True) dividend["note"] = '' if dividend["type"] == Dividend.StockDividend: if not dividend["price"]: logging.error(self.tr("No price data for stock dividend: ") + f"{dividend}") continue dividend["amount"] = dividend["amount"] * dividend["price"] dividend["note"] = "Дивиденд выплачен в натуральной форме (ценными бумагами)" dividend["amount_rub"] = round(dividend["amount"] * dividend["rate"], 2) if dividend["rate"] else 0 dividend["tax_rub"] = round(dividend["tax"] * dividend["rate"], 2) if dividend["rate"] else 0 dividend["tax2pay"] = round(0.13 * dividend["amount_rub"], 2) if dividend["tax_treaty"]: if dividend["tax2pay"] > dividend["tax_rub"]: dividend["tax2pay"] = dividend["tax2pay"] - dividend["tax_rub"] else: dividend["tax2pay"] = 0 dividend['tax_treaty'] = "Да" if dividend['tax_treaty'] else "Нет" dividend['report_template'] = "dividend" del dividend['type'] del dividend['price'] dividends.append(dividend) self.insert_totals(dividends, ["amount", "amount_rub", "tax", "tax_rub", "tax2pay"]) return dividends
def prepare_broker_fees(self): fees = [] query = executeSQL("SELECT a.timestamp AS payment_date, d.amount AS amount, d.note AS note, q.quote AS rate " "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.timestamp=ld.ref_id " "LEFT JOIN quotes AS q ON ld.timestamp=q.timestamp AND c.currency_id=q.asset_id AND q.currency_id=:base_currency " "WHERE a.timestamp>=:begin AND a.timestamp<:end " "AND a.account_id=:account_id AND d.category_id=:fee", [(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id), (":fee", PredefinedCategory.Fees), (":base_currency", JalSettings().getValue('BaseCurrency'))]) while query.next(): fee = readSQLrecord(query, named=True) fee['amount'] = -fee['amount'] fee['amount_rub'] = round(fee['amount'] * fee['rate'], 2) if fee['rate'] else 0 fee['report_template'] = "fee" fees.append(fee) self.insert_totals(fees, ["amount", "amount_rub"]) return fees
def __init__(self, operation_id=None): super().__init__(operation_id) self._otype = LedgerTransaction.IncomeSpending self._table = "actions" self._data = readSQL( "SELECT a.timestamp, a.account_id, a.peer_id, p.name AS peer, " "a.alt_currency_id AS currency FROM actions AS a " "LEFT JOIN agents AS p ON a.peer_id = p.id WHERE a.id=:oid", [(":oid", self._oid)], named=True) self._timestamp = self._data['timestamp'] self._account = self._data['account_id'] self._account_name = JalDB().get_account_name(self._account) self._account_currency = JalDB().get_asset_name( JalDB().get_account_currency(self._account)) self._reconciled = JalDB().account_reconciliation_timestamp( self._account) >= self._timestamp self._peer_id = self._data['peer_id'] self._peer = self._data['peer'] self._currency = self._data['currency'] details_query = executeSQL( "SELECT d.category_id, c.name AS category, d.tag_id, t.tag, " "d.amount, d.amount_alt, d.note FROM action_details AS d " "LEFT JOIN categories AS c ON c.id=d.category_id " "LEFT JOIN tags AS t ON t.id=d.tag_id " "WHERE d.pid= :pid", [(":pid", self._oid)]) self._details = [] while details_query.next(): self._details.append(readSQLrecord(details_query, named=True)) self._amount = sum(line['amount'] for line in self._details) self._label, self._label_color = ( '—', CustomColor.DarkRed) if self._amount < 0 else ( '+', CustomColor.DarkGreen) if self._currency: self._view_rows = 2 self._currency_name = JalDB().get_asset_name(self._currency) self._amount_alt = sum(line['amount_alt'] for line in self._details)
def updataData(self): query = executeSQL("SELECT * FROM assets_ext WHERE quote_source!=:NA", [(":NA", MarketDataFeed.NA)]) while query.next(): asset = readSQLrecord(query, named=True) if asset['type_id'] in [ PredefinedAsset.Money, PredefinedAsset.Commodity, PredefinedAsset.Forex ]: continue if asset['quote_source'] == MarketDataFeed.RU: logging.info( self.tr("Checking MOEX data for: ") + asset['symbol']) data = self.MOEX_info(symbol=asset['symbol'], isin=asset['isin']) if data: if asset['full_name'] != data['name']: logging.info( self.tr("New full name found for: ") + f"{JalDB().get_asset_name(asset['id'])}: {asset['full_name']} -> {data['name']}" ) isin = data['isin'] if not asset[ 'isin'] and 'isin' in data and data['isin'] else '' JalDB().update_asset_data(asset['id'], {'isin': isin})
def recognize_categories(purchases): import tensorflow as tf import tensorflow.keras as keras tf.get_logger().setLevel('WARNING') # Load only categories that were used for import query = executeSQL( "SELECT DISTINCT mapped_to AS category FROM map_category") table = [] classes_number = 0 while query.next(): category = readSQLrecord(query) table.append({'idx': classes_number, 'category': category}) classes_number += 1 categories = pd.DataFrame(table) # Load data from DB into pandas dataframe query = executeSQL("SELECT value, mapped_to FROM map_category") table = [] while query.next(): value, mapped_to = readSQLrecord(query) table.append({'value': value, 'mapped_to': mapped_to}) data = pd.DataFrame(table) data = data.merge(categories, left_on="mapped_to", right_on='category') data = data.drop(columns=[ 'mapped_to', 'category' ]) # we don't need this column as we will use custom 'idx' data['cleaned_value'] = data.value.apply(clean_text) # prepare X values descriptions = data.cleaned_value tokenizer = keras.preprocessing.text.Tokenizer(num_words=5000, oov_token='UNKNOWN', lower=False) tokenizer.fit_on_texts(descriptions) dictionary_size = len(tokenizer.word_index) descriptions_sequenced = tokenizer.texts_to_sequences(descriptions) max_desc_len = len(max(descriptions_sequenced, key=len)) X = keras.preprocessing.sequence.pad_sequences(descriptions_sequenced, padding='post', maxlen=max_desc_len) # prepare Y values Y = keras.utils.to_categorical(data.idx) # prepare and train model nn_model = keras.Sequential([ keras.layers.Embedding(input_length=max_desc_len, input_dim=dictionary_size + 1, output_dim=classes_number * 2), keras.layers.Flatten(), keras.layers.Dense(classes_number * 4, activation='relu'), keras.layers.Dense(classes_number, activation='softmax') ]) nn_model.compile(loss='categorical_crossentropy', optimizer='adam', metrics=['accuracy']) nn_model.fit(X, Y, epochs=40, batch_size=50, verbose=0) # predict categories purchases_sequenced = tokenizer.texts_to_sequences(purchases) NewX = keras.preprocessing.sequence.pad_sequences(purchases_sequenced, padding='post', maxlen=max_desc_len) NewY = nn_model.predict(NewX) result_idx = keras.backend.argmax(NewY, axis=1) result = categories.take(result_idx.numpy().tolist()).category probability = NewY.max(axis=1) return result.tolist(), probability.tolist()
def test_fifo(prepare_db_fifo): # Prepare trades and corporate actions setup test_assets = [ (4, 'A', 'A SHARE'), (5, 'B', 'B SHARE'), (6, 'C', 'C SHARE'), (7, 'D', 'D SHARE'), (8, 'E', 'E SHARE'), (9, 'F', 'F SHARE'), (10, 'G1', 'G SHARE BEFORE'), (11, 'G2', 'G SHARE AFTER'), (12, 'H', 'H SPIN-OFF FROM G'), (13, 'K', 'K SHARE'), (14, 'L', 'L SHARE'), (15, 'M', 'M SHARE'), (16, 'N', 'N WITH STOCK DIVIDEND'), (17, 'O', 'O SHARE'), (18, 'P', 'P SHARE'), ] create_stocks(test_assets, currency_id=2) test_corp_actions = [ (1606899600, 3, 10, 100.0, 11, 100.0, 1.0, 'Symbol change G1 -> G2'), (1606986000, 2, 11, 100.0, 12, 20.0, 0.8, 'Spin-off H from G2'), (1607763600, 4, 14, 15.0, 14, 30.0, 1.0, 'Split L 15 -> 30'), (1607850000, 3, 13, 5.0, 15, 5.0, 1.0, 'Another symbol change K -> M'), (1607936412, 1, 14, 30.0, 15, 20.0, 1.0, 'Merger 30 L into 20 M'), (1608022800, 4, 15, 25.0, 15, 5.0, 1.0, 'Split M 25 -> 5') ] create_corporate_actions(1, test_corp_actions) stock_dividends = [(1608368400, 1, 16, 1.0, 2, 1050.0, 60.0, 'Stock dividend +1 N')] create_stock_dividends(stock_dividends) test_trades = [(1609567200, 1609653600, 4, 10.0, 100.0, 1.0), (1609653600, 1609740000, 4, -10.0, 200.0, 5.0), (1609653600, 1609740000, 5, 10.0, 100.0, 1.0), (1609740000, 1609826400, 5, -3.0, 200.0, 2.0), (1609740000, 1609826400, 5, -7.0, 50.0, 3.0), (1609826400, 1609912800, 6, 2.0, 100.0, 2.0), (1609912800, 1609999200, 6, 8.0, 200.0, 2.0), (1609999200, 1610085600, 6, -10.0, 50.0, 2.0), (1610085600, 1610172000, 7, -100.0, 1.0, 1.0), (1610172000, 1610258400, 7, 50.0, 2.0, 1.0), (1610258400, 1610344800, 7, 50.0, 1.5, 1.0), (1610344800, 1610431200, 8, -1.3, 100.0, 1.0), (1610431200, 1610517600, 8, -1.7, 200.0, 1.0), (1610517600, 1610604000, 8, 3.0, 50.0, 1.0), (1610604000, 1610690400, 9, 10.0, 100.0, 0.0), (1610690400, 1610776800, 9, -7.0, 200.0, 0.0), (1610776800, 1610863200, 9, -5.0, 200.0, 0.0), (1610863200, 1610949600, 9, -10.0, 200.0, 0.0), (1610949600, 1611036000, 9, -8.0, 200.0, 0.0), (1611036000, 1611122400, 9, 40.0, 100.0, 0.0), (1611122400, 1611208800, 9, -11.0, 200.0, 0.0), (1611208800, 1611295200, 9, -18.0, 200.0, 0.0), (1611295200, 1611381600, 9, 15.0, 300.0, 0.0), (1611381600, 1611468000, 9, -3.0, 200.0, 0.0), (1611468000, 1611554400, 9, -2.0, 200.0, 0.0), (1611554400, 1611640800, 9, -1.0, 200.0, 0.0), (1606813200, 1606856400, 10, 100.0, 10.0, 0.0), (1607072400, 1607115600, 11, -100.0, 20.0, 0.0), (1607158800, 1607202000, 12, -20.0, 10.0, 0.0), (1607580000, 1607634000, 13, 5.0, 20.0, 0.0), (1607666400, 1607720400, 14, 10.0, 25.0, 0.0), (1607673600, 1607720400, 14, 10.0, 50.0, 0.0), (1607680800, 1607720400, 14, -5.0, 40.0, 0.0), (1608195600, 1608238800, 15, -5.0, 200.0, 1.0), (1608282000, 1608325200, 16, 5.0, 1000.0, 0.0), (1608454800, 1608498000, 16, -1.0, 1000.0, 0.0), (1608541200, 1608584400, 16, -5.0, 1100.0, 0.0), (1608616800, 1608670800, 17, 8.0, 130.0, 0.0), (1608624000, 1608670800, 17, -8.0, 120.0, 0.0), (1608620400, 1608670800, 17, 22.0, 110.0, 0.0), (1608627600, 1608670800, 17, -22.0, 120.0, 0.0), (1608703200, 1608757200, 18, 1.0, 1000.0, 0.0), (1608706800, 1608757200, 18, -1.0, 2000.0, 0.0), (1608710400, 1608757200, 18, -1.0, 1900.0, 0.0), (1608714000, 1608757200, 18, 1.0, 2700.0, 0.0), (1608717600, 1608757200, 18, -1.0, 3000.0, 0.0), (1608721200, 1608757200, 18, -1.0, 2000.0, 0.0), (1608724800, 1608757200, 18, 2.0, 2500.0, 0.0)] create_trades(1, test_trades) # Build ledger ledger = Ledger() ledger.rebuild(from_timestamp=0) # Check single deal assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=4") == 1 assert readSQL("SELECT SUM(profit) FROM deals_ext WHERE asset_id=4") == 994 assert readSQL("SELECT SUM(fee) FROM deals_ext WHERE asset_id=4") == 6 # One buy multiple sells assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=5") == 2 assert readSQL("SELECT SUM(profit) FROM deals_ext WHERE asset_id=5") == -56 assert readSQL("SELECT SUM(fee) FROM deals_ext WHERE asset_id=5") == 6 # Multiple buy one sell assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=6") == 2 assert readSQL( "SELECT SUM(profit) FROM deals_ext WHERE asset_id=6") == -1306 assert readSQL("SELECT SUM(fee) FROM deals_ext WHERE asset_id=6") == 6 # One sell multiple buys assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=7") == 2 assert readSQL("SELECT SUM(profit) FROM deals_ext WHERE asset_id=7") == -78 assert readSQL("SELECT SUM(fee) FROM deals_ext WHERE asset_id=7") == 3 # Multiple sells one buy assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=8") == 2 assert readSQL("SELECT SUM(profit) FROM deals_ext WHERE asset_id=8") == 317 assert readSQL("SELECT SUM(fee) FROM deals_ext WHERE asset_id=8") == 3 # Multiple buys and sells assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=9") == 11 assert readSQL( "SELECT SUM(profit) FROM deals_ext WHERE asset_id=9") == 3500 assert readSQL("SELECT SUM(fee) FROM deals_ext WHERE asset_id=9") == 0 # Symbol change assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=10") == 1 assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=11") == 1 assert readSQL("SELECT profit FROM deals_ext WHERE asset_id=11") == 1200 # Spin-off assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=12") == 1 assert readSQL("SELECT profit FROM deals_ext WHERE asset_id=12") == approx( 0) # Multiple corp actions assert readSQL( "SELECT COUNT(*) FROM deals_ext WHERE asset_id=13 AND corp_action IS NOT NULL" ) == 1 assert readSQL("SELECT profit FROM deals_ext WHERE asset_id=13") == 0 assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=14") == 3 assert readSQL( "SELECT COUNT(*) FROM deals_ext WHERE asset_id=14 AND corp_action IS NOT NULL" ) == 2 assert readSQL( "SELECT profit FROM deals_ext WHERE asset_id=14 AND corp_action IS NULL" ) == 75 assert readSQL( "SELECT profit FROM deals_ext WHERE asset_id=14 AND corp_action IS NOT NULL" ) == 0 assert readSQL( "SELECT COUNT(*) FROM deals_ext WHERE asset_id=15 AND corp_action IS NOT NULL" ) == 1 assert readSQL("SELECT profit FROM deals_ext WHERE asset_id=15") == 274 # Stock dividend assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=16") == 3 assert readSQL( "SELECT SUM(profit) FROM deals_ext WHERE asset_id=16") == approx(450) assert readSQL( "SELECT profit FROM deals_ext WHERE asset_id=16 AND close_timestamp=1608454800" ) == approx(0) assert readSQL( "SELECT profit FROM deals_ext WHERE asset_id=16 AND open_timestamp=1608368400" ) == approx(50) # Order of buy/sell assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=17") == 2 assert readSQL( "SELECT SUM(profit) FROM deals_ext WHERE asset_id=17") == 140 assert readSQL("SELECT COUNT(*) FROM deals_ext WHERE asset_id=18") == 4 assert readSQL("SELECT SUM(qty) FROM deals_ext WHERE asset_id=18") == -2 assert readSQL( "SELECT SUM(profit) FROM deals_ext WHERE asset_id=18") == 200 # totals assert readSQL("SELECT COUNT(*) FROM deals") == 41 assert readSQL( "SELECT COUNT(*) FROM deals WHERE open_op_type=:trade AND close_op_type=:trade", [(":trade", LedgerTransaction.Trade)]) == 29 assert readSQL( "SELECT COUNT(*) FROM deals WHERE open_op_type!=:corp_action OR close_op_type!=:corp_action", [(":corp_action", LedgerTransaction.CorporateAction)]) == 37 assert readSQL( "SELECT COUNT(*) FROM deals WHERE open_op_type=:corp_action AND close_op_type=:corp_action", [(":corp_action", LedgerTransaction.CorporateAction)]) == 4 # validate final amounts query = executeSQL( "SELECT MAX(id) AS mid, asset_id, amount_acc, value_acc FROM ledger " "WHERE book_account=:money OR book_account=:assets GROUP BY asset_id", [(":money", BookAccount.Money), (":assets", BookAccount.Assets)]) while query.next(): row = readSQLrecord(query, named=True) if row['asset_id'] == 2: # Checking money amount assert row['amount_acc'] == 16700 else: assert row['amount_acc'] == 0 assert row['value_acc'] == 0
def processCorporateAction(self): # Stock dividends are imported without initial stock amounts -> correction happens here if self.current[ 'subtype'] == CorporateAction.StockDividend and self.current[ 'amount'] < 0: self.updateStockDividendAssets() seq_id = self.current_seq account_id = self.current['account'] asset_id = self.current['asset'] qty = self.current['amount'] processed_qty = 0 processed_value = 0 # Get asset amount accumulated before current operation asset_amount = self.getAmount(BookAccount.Assets, asset_id) if asset_amount < (qty - 2 * Setup.CALC_TOLERANCE): logging.fatal( g_tr( 'Ledger', "Asset amount is not enough for corporate action processing. Date: " ) + f"{datetime.utcfromtimestamp(self.current['timestamp']).strftime('%d/%m/%Y %H:%M:%S')}" ) return # Get information about last deal last_sid = readSQL( "SELECT " "CASE WHEN qty>0 THEN open_sid ELSE close_sid END AS last_sid " "FROM deals " "WHERE account_id=:account_id AND asset_id=:asset_id " "ORDER BY close_sid DESC, open_sid DESC LIMIT 1", [(":account_id", account_id), (":asset_id", asset_id)]) last_sid = 0 if last_sid is None else last_sid # Next get information about abs trade quantity that was in this last deal last_qty = readSQL( "SELECT coalesce(ABS(t.qty), 0)+coalesce(ABS(ca.qty_new) , 0) AS qty " "FROM sequence AS s " "LEFT JOIN trades AS t ON t.id=s.operation_id AND s.type=3 " "LEFT JOIN corp_actions AS ca ON ca.id=s.operation_id AND s.type=5 " "WHERE s.id=:last_sid", [(":last_sid", last_sid)]) last_qty = 0 if last_qty is None else last_qty # Collect quantity of all deals where this last opposite trade participated (positive value) deals_qty = readSQL( "SELECT coalesce(SUM(ABS(qty)), 0) " "FROM deals AS d " "WHERE account_id=:account_id AND asset_id=:asset_id " "AND (open_sid=:last_sid OR close_sid=:last_sid)", [(":account_id", account_id), (":asset_id", asset_id), (":last_sid", last_sid)]) reminder = last_qty - deals_qty # if last trade is fully matched (reminder<=0) we start from next trade, otherwise we need to shift by 1 if reminder > 0: last_sid = last_sid - 1 # Get a list of all previous not matched trades or corporate actions of opposite direction (type parameter) query = executeSQL( "SELECT * FROM (" "SELECT s.id, ABS(t.qty), t.price FROM trades AS t " "LEFT JOIN sequence AS s ON s.type = 3 AND s.operation_id=t.id " "WHERE qty > 0 AND t.asset_id = :asset_id AND t.account_id = :account_id " "AND s.id < :sid AND s.id > :last_sid " "UNION ALL " "SELECT s.id, ABS(c.qty_new), coalesce(l.value/c.qty_new, 0) AS price FROM corp_actions AS c " "LEFT JOIN sequence AS s ON s.type = 5 AND s.operation_id=c.id " "LEFT JOIN ledger AS l ON s.id = l.sid AND l.asset_id=c.asset_id_new AND l.value > 0 " "WHERE c.qty_new > 0 AND c.asset_id_new=:asset_id AND c.account_id=:account_id " "AND s.id < :sid AND s.id > :last_sid " ")ORDER BY id", [(":asset_id", asset_id), (":account_id", account_id), (":sid", seq_id), (":last_sid", last_sid)]) while query.next(): # Perform match ("closure") of previous trades deal_sid, deal_qty, deal_price = readSQLrecord( query) # deal_sid -> trade_sid if reminder > 0: next_deal_qty = reminder reminder = 0 else: next_deal_qty = deal_qty if (processed_qty + next_deal_qty ) >= qty: # We can't process more than qty of current trade next_deal_qty = qty - processed_qty # If it happens - just process the remainder of the trade # Create a deal with relevant sign of quantity (-1 for short, +1 for long) _ = executeSQL( "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)]) processed_qty += next_deal_qty processed_value += (next_deal_qty * deal_price) if processed_qty == qty: break # Asset allocations for different corporate actions: # +-----------------+-------+-----+------------+-----------+----------+---------------+--------------------+ # | | Asset | Qty | cost basis | Asset new | Qty new | cost basis | The same algo for: | # +-----------------+-------+-----+------------+-----------+----------+---------------+--------------------+ # | Symbol Change | A | N | 100 % | B | N | 100% | | # | (R-)Split | A | N | 100 % | A | M | 100% | Stock Dividend | # | Merger | A | N | 100 % | B | M | 100% | | # | Spin-Off | A | N | 100 % | A & B | AxN, BxM | X% & (100-X)% | | # +-----------------+-------+-----+------------+-----------+----------+---------------+--------------------+ # Withdraw value with old quantity of old asset as it common for all corporate action self.appendTransaction(BookAccount.Assets, -processed_qty, -processed_value) # Prepare details about new asset new_asset = self.current['peer'] new_qty = self.current['price'] new_value = processed_value if self.current['subtype'] == CorporateAction.SpinOff: new_value = processed_value * self.current['fee_tax'] # Modify value for old asset self.appendTransaction(BookAccount.Assets, self.current['amount'], processed_value - new_value) # Create value for new asset self.current['asset'] = new_asset self.appendTransaction(BookAccount.Assets, new_qty, new_value)
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, TransactionType.CorporateAction: self.processCorporateAction } if from_timestamp >= 0: frontier = from_timestamp silent = False else: frontier = self.getCurrentFrontier() operations_count = readSQL( "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.utcfromtimestamp(frontier).strftime('%d/%m/%Y %H:%M:%S')}" ) start_time = datetime.now() _ = executeSQL( "DELETE FROM deals WHERE close_sid >= " "(SELECT coalesce(MIN(id), 0) FROM sequence WHERE timestamp >= :frontier)", [(":frontier", frontier)]) _ = executeSQL("DELETE FROM ledger WHERE timestamp >= :frontier", [(":frontier", frontier)]) _ = executeSQL("DELETE FROM sequence WHERE timestamp >= :frontier", [(":frontier", frontier)]) _ = executeSQL("DELETE FROM ledger_sums WHERE timestamp >= :frontier", [(":frontier", frontier)], commit=True) db_triggers_disable() if fast_and_dirty: # For 30k operations difference of execution time is - with 0:02:41 / without 0:11:44 _ = executeSQL("PRAGMA synchronous = OFF") try: query = executeSQL( "SELECT type, id, timestamp, subtype, account, currency, asset, amount, " "category, price, fee_tax, peer, tag FROM all_transactions " "WHERE timestamp >= :frontier", [(":frontier", frontier)]) while query.next(): self.current = readSQLrecord(query, named=True) if self.current['type'] == TransactionType.Action: subtype = copysign(1, self.current['subtype']) else: subtype = self.current['subtype'] seq_query = executeSQL( "INSERT INTO sequence(timestamp, type, subtype, operation_id) " "VALUES(:timestamp, :type, :subtype, :operation_id)", [(":timestamp", self.current['timestamp']), (":type", self.current['type']), (":subtype", subtype), (":operation_id", self.current['id'])]) self.current_seq = seq_query.lastInsertId() operationProcess[self.current['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.utcfromtimestamp(self.current['timestamp']).strftime('%d/%m/%Y %H:%M:%S')}" ) finally: if fast_and_dirty: _ = executeSQL("PRAGMA synchronous = ON") db_triggers_enable() if not silent: logging.info( g_tr('Ledger', "Ledger is complete. Elapsed time: ") + f"{datetime.now() - start_time}" + g_tr('Ledger', ", new frontier: ") + f"{datetime.utcfromtimestamp(self.current['timestamp']).strftime('%d/%m/%Y %H:%M:%S')}" ) self.updated.emit()
def prepare_stocks_and_etf(self): deals = [] # Take all actions without conversion query = executeSQL("SELECT s.symbol AS symbol, s.isin AS isin, d.qty AS qty, cc.iso_code AS country_iso, " "o.timestamp AS o_date, qo.quote AS o_rate, o.settlement AS os_date, o.number AS o_number, " "qos.quote AS os_rate, o.price AS o_price, o.qty AS o_qty, o.fee AS o_fee, " "c.timestamp AS c_date, qc.quote AS c_rate, c.settlement AS cs_date, c.number AS c_number, " "qcs.quote AS cs_rate, c.price AS c_price, c.qty AS c_qty, c.fee AS c_fee, " "SUM(coalesce(-sd.amount*qsd.quote, 0)) AS s_dividend " # Dividend paid for short position "FROM deals AS d " "JOIN trades AS o ON o.id=d.open_op_id AND o.op_type=d.open_op_type " "JOIN trades AS c ON c.id=d.close_op_id AND c.op_type=d.close_op_type " "LEFT JOIN accounts AS a ON a.id = :account_id " "LEFT JOIN assets_ext AS s ON s.id = o.asset_id AND s.currency_id=a.currency_id " "LEFT JOIN countries AS cc ON cc.id = a.country_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 AND qo.currency_id=:base_currency " "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 AND qos.currency_id=:base_currency " "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 AND qc.currency_id=:base_currency " "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 AND qcs.currency_id=:base_currency " "LEFT JOIN dividends AS sd ON d.asset_id=sd.asset_id AND sd.amount<0 " # Include dividends paid from short positions "AND sd.ex_date>=o_date AND sd.ex_date<=c_date " "LEFT JOIN t_last_dates AS ldsd ON sd.timestamp=ldsd.ref_id " "LEFT JOIN quotes AS qsd ON ldsd.timestamp=qsd.timestamp AND a.currency_id=qsd.asset_id AND qsd.currency_id=:base_currency " "WHERE c.settlement>=:begin AND c.settlement<:end AND d.account_id=:account_id " "AND (s.type_id = :stock OR s.type_id = :fund) " "GROUP BY d.rowid " # to prevent collapse to 1 line if 'sd' values are NULL "ORDER BY s.symbol, o.timestamp, c.timestamp", [(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id), (":base_currency", JalSettings().getValue('BaseCurrency')), (":stock", PredefinedAsset.Stock), (":fund", PredefinedAsset.ETF)]) while query.next(): deal = readSQLrecord(query, named=True) if not deal['symbol']: # there will be row of NULLs if no deals are present (due to SUM aggregation) continue if not self.use_settlement: deal['os_rate'] = deal['o_rate'] deal['cs_rate'] = deal['c_rate'] deal['o_type'] = "Покупка" if deal['qty'] >= 0 else "Продажа" deal['c_type'] = "Продажа" if deal['qty'] >= 0 else "Покупка" deal['o_amount'] = round(deal['o_price'] * abs(deal['qty']), 2) deal['o_amount_rub'] = round(deal['o_amount'] * deal['os_rate'], 2) if deal['os_rate'] else 0 deal['c_amount'] = round(deal['c_price'] * abs(deal['qty']), 2) deal['c_amount_rub'] = round(deal['c_amount'] * deal['cs_rate'], 2) if deal['cs_rate'] else 0 deal['o_fee'] = deal['o_fee'] * abs(deal['qty'] / deal['o_qty']) deal['c_fee'] = deal['c_fee'] * abs(deal['qty'] / deal['c_qty']) deal['o_fee_rub'] = round(deal['o_fee'] * deal['o_rate'], 2) if deal['o_rate'] else 0 deal['c_fee_rub'] = round(deal['c_fee'] * deal['c_rate'], 2) if deal['c_rate'] else 0 deal['income_rub'] = deal['c_amount_rub'] if deal['qty'] >= 0 else deal['o_amount_rub'] deal['income'] = deal['c_amount'] if deal['qty'] >= 0 else deal['o_amount'] deal['spending_rub'] = deal['o_amount_rub'] if deal['qty'] >= 0 else deal['c_amount_rub'] deal['spending_rub'] = deal['spending_rub'] + deal['o_fee_rub'] + deal['c_fee_rub'] + deal['s_dividend'] deal['spending'] = deal['o_amount'] if deal['qty'] >= 0 else deal['c_amount'] deal['spending'] = deal['spending'] + deal['o_fee'] + deal['c_fee'] deal['profit_rub'] = deal['income_rub'] - deal['spending_rub'] deal['profit'] = deal['income'] - deal['spending'] if deal['s_dividend'] > 0: # Dividend was paid during short position deal['s_dividend_note'] = f"Удержанный дивиденд: {deal['s_dividend']:.2f} RUB" else: deal['s_dividend_note'] = '' deal['report_template'] = "trade" deals.append(deal) self.insert_totals(deals, ["income_rub", "spending_rub", "profit_rub", "profit"]) return deals
def processTrade(self): if self.current['peer'] == '': logging.error( g_tr( 'Ledger', "Can't process trade as bank isn't set for investment account" )) return seq_id = self.current_seq account_id = self.current['account'] asset_id = self.current['asset'] type = copysign(1, self.current['amount']) # 1 is buy, -1 is sell qty = type * self.current['amount'] price = self.current['price'] trade_value = round(price * qty, 2) + type * self.current['fee_tax'] processed_qty = 0 processed_value = 0 # Get asset amount accumulated before current operation asset_amount = self.getAmount(BookAccount.Assets, asset_id) if ( (-type) * asset_amount ) > 0: # Process deal match if we have asset that is opposite to operation last_type = readSQL( "SELECT s.type FROM deals AS d " "LEFT JOIN sequence AS s ON d.close_sid=s.id " "WHERE d.account_id=:account_id AND d.asset_id=:asset_id " "ORDER BY d.close_sid DESC, d.open_sid DESC LIMIT 1", [(":account_id", account_id), (":asset_id", asset_id)]) if last_type is None or last_type == TransactionType.Trade: # Get information about last deal with quantity of opposite sign last_sid = readSQL( "SELECT " "CASE WHEN (:type)*qty<0 THEN open_sid ELSE close_sid END AS last_sid " "FROM deals " "WHERE account_id=:account_id AND asset_id=:asset_id " "ORDER BY close_sid DESC, open_sid DESC LIMIT 1", [(":type", type), (":account_id", account_id), (":asset_id", asset_id)]) last_sid = 0 if last_sid is None else last_sid # Next get information about abs trade quantity that was in this last deal # It may be a corporate action - its quantity calculation is a bit more complicated last_qty = readSQL( "SELECT coalesce(SUM(qty), 0) AS qty FROM ( " "SELECT ABS(t.qty) AS qty " "FROM sequence AS s " "LEFT JOIN trades AS t ON t.id=s.operation_id AND s.type=3 " "WHERE s.id=:last_sid " "UNION ALL " "SELECT " "CASE " " WHEN ca.type = 2 AND ca.asset_id=:asset_id THEN ca.qty " " ELSE ca.qty_new " "END AS qty " "FROM sequence AS s " "LEFT JOIN corp_actions AS ca ON ca.id=s.operation_id AND s.type=5 " "WHERE s.id=:last_sid " ")", [(":asset_id", asset_id), (":last_sid", last_sid)]) # Collect quantity of all deals where this last opposite trade participated (positive value) # If it was a corporate action we need to take only where it was an opening of the deal deals_qty = readSQL( "SELECT coalesce(SUM(ABS(qty)), 0) " "FROM deals AS d " "LEFT JOIN sequence AS s ON s.id=d.close_sid " "WHERE account_id=:account_id AND asset_id=:asset_id " "AND (open_sid=:last_sid OR close_sid=:last_sid) AND s.type!=5", [(":account_id", account_id), (":asset_id", asset_id), (":last_sid", last_sid)]) reminder = last_qty - deals_qty # if last trade is fully matched (reminder<=0) we start from next trade, otherwise we need to shift by 1 if reminder > 0: last_sid -= 1 elif last_type == TransactionType.CorporateAction: last_sid, ca_type = readSQL( "SELECT d.close_sid, c.type FROM deals AS d " "LEFT JOIN sequence AS s ON d.close_sid=s.id " "LEFT JOIN corp_actions AS c ON s.operation_id=c.id " "WHERE d.account_id=:account_id AND d.asset_id=:asset_id " "ORDER BY d.close_sid DESC, d.open_sid DESC LIMIT 1", [(":account_id", account_id), (":asset_id", asset_id)]) if ca_type == CorporateAction.Split \ or ca_type == CorporateAction.StockDividend or ca_type == CorporateAction.SpinOff: last_sid -= 1 reminder = 0 # Get a list of all previous not matched trades or corporate actions of opposite direction (type parameter) query = executeSQL( "SELECT * FROM (" "SELECT s.id, ABS(t.qty), t.price FROM trades AS t " "LEFT JOIN sequence AS s ON s.type = 3 AND s.operation_id=t.id " "WHERE (:type)*qty < 0 AND t.asset_id = :asset_id AND t.account_id = :account_id " "AND s.id < :sid AND s.id > :last_sid " "UNION ALL " "SELECT s.id, " "CASE " " WHEN c.type = 2 AND c.asset_id=:asset_id THEN c.qty " " ELSE c.qty_new " "END AS qty, " "CASE " " WHEN c.type = 2 AND c.asset_id=:asset_id THEN coalesce(l.value/c.qty, 0) " " ELSE coalesce(l.value/c.qty_new, 0) " "END AS price " "FROM corp_actions AS c " "LEFT JOIN sequence AS s ON s.type = 5 AND s.operation_id=c.id " "LEFT JOIN ledger AS l ON s.id = l.sid AND l.asset_id=:asset_id AND l.value > 0 " "WHERE (:type)*c.qty_new < 0 AND (c.asset_id_new=:asset_id OR (c.asset_id=:asset_id AND c.type=2)) AND c.account_id=:account_id " "AND s.id < :sid AND s.id > :last_sid " ")ORDER BY id", [(":type", type), (":asset_id", asset_id), (":account_id", account_id), (":sid", seq_id), (":last_sid", last_sid)]) while query.next(): # Perform match ("closure") of previous trades deal_sid, deal_qty, deal_price = readSQLrecord( query) # deal_sid -> trade_sid if reminder > 0: next_deal_qty = reminder reminder = 0 else: next_deal_qty = deal_qty if ( processed_qty + next_deal_qty ) >= qty: # We can't process more than qty of current trade next_deal_qty = qty - processed_qty # If it happens - just process the remainder of the trade # Create a deal with relevant sign of quantity (-1 for short, +1 for long) _ = executeSQL( "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", (-type) * next_deal_qty)]) processed_qty += next_deal_qty processed_value += (next_deal_qty * deal_price) if processed_qty == qty: break if type > 0: credit_value = self.takeCredit(trade_value) else: credit_value = self.returnCredit(trade_value) if credit_value < trade_value: self.appendTransaction(BookAccount.Money, (-type) * (trade_value - credit_value)) if processed_qty > 0: # Add result of closed deals # decrease (for sell) or increase (for buy) amount of assets in ledger self.appendTransaction(BookAccount.Assets, type * processed_qty, type * processed_value) self.current['category'] = PredefinedCategory.Profit self.appendTransaction( BookAccount.Incomes, type * ((price * processed_qty) - processed_value)) if processed_qty < qty: # We have reminder that opens a new position self.appendTransaction(BookAccount.Assets, type * (qty - processed_qty), type * (qty - processed_qty) * price) if self.current['fee_tax']: self.current['category'] = PredefinedCategory.Fees self.appendTransaction(BookAccount.Costs, self.current['fee_tax'])
def processLedger(self, ledger): if self._broker is None: raise ValueError( self. tr("Can't process trade as bank isn't set for investment account: " ) + self._account_name) type = copysign(1, self._qty) # 1 is buy, -1 is sell qty = abs(self._qty) trade_value = round(self._price * qty, 2) + type * self._fee processed_qty = 0 processed_value = 0 # Get asset amount accumulated before current operation asset_amount = ledger.getAmount(BookAccount.Assets, self._account, self._asset) if ( (-type) * asset_amount ) > 0: # Process deal match if we have asset that is opposite to operation # Get a list of all previous not matched trades or corporate actions query = executeSQL( "SELECT timestamp, op_type, operation_id, account_id, asset_id, price, remaining_qty " "FROM open_trades " "WHERE account_id=:account_id AND asset_id=:asset_id AND remaining_qty!=0 " "ORDER BY timestamp, op_type DESC", [(":account_id", self._account), (":asset_id", self._asset)]) while query.next(): opening_trade = readSQLrecord(query, named=True) next_deal_qty = opening_trade['remaining_qty'] if ( processed_qty + next_deal_qty ) > qty: # We can't close all trades with current operation next_deal_qty = qty - processed_qty # If it happens - just process the remainder of the trade _ = executeSQL( "UPDATE open_trades SET remaining_qty=remaining_qty-:qty " "WHERE op_type=:op_type AND operation_id=:id AND asset_id=:asset_id", [(":qty", next_deal_qty), (":op_type", opening_trade['op_type']), (":id", opening_trade['operation_id']), (":asset_id", self._asset)]) _ = executeSQL( "INSERT INTO deals(account_id, asset_id, open_op_type, open_op_id, open_timestamp, open_price, " "close_op_type, close_op_id, close_timestamp, close_price, qty) " "VALUES(:account_id, :asset_id, :open_op_type, :open_op_id, :open_timestamp, :open_price, " ":close_op_type, :close_op_id, :close_timestamp, :close_price, :qty)", [(":account_id", self._account), (":asset_id", self._asset), (":open_op_type", opening_trade['op_type']), (":open_op_id", opening_trade['operation_id']), (":open_timestamp", opening_trade['timestamp']), (":open_price", opening_trade['price']), (":close_op_type", self._otype), (":close_op_id", self._oid), (":close_timestamp", self._timestamp), (":close_price", self._price), (":qty", (-type) * next_deal_qty)]) processed_qty += next_deal_qty processed_value += (next_deal_qty * opening_trade['price']) if processed_qty == qty: break if type > 0: credit_value = ledger.takeCredit(self, self._account, trade_value) else: credit_value = ledger.returnCredit(self, self._account, trade_value) if credit_value < trade_value: ledger.appendTransaction(self, BookAccount.Money, (-type) * (trade_value - credit_value)) if processed_qty > 0: # Add result of closed deals # decrease (for sell) or increase (for buy) amount of assets in ledger ledger.appendTransaction(self, BookAccount.Assets, type * processed_qty, asset_id=self._asset, value=type * processed_value) ledger.appendTransaction( self, BookAccount.Incomes, type * ((self._price * processed_qty) - processed_value), category=PredefinedCategory.Profit, peer=self._broker) if processed_qty < qty: # We have reminder that opens a new position _ = executeSQL( "INSERT INTO open_trades(timestamp, op_type, operation_id, account_id, asset_id, price, remaining_qty) " "VALUES(:timestamp, :type, :operation_id, :account_id, :asset_id, :price, :remaining_qty)", [(":timestamp", self._timestamp), (":type", self._otype), (":operation_id", self._oid), (":account_id", self._account), (":asset_id", self._asset), (":price", self._price), (":remaining_qty", qty - processed_qty)]) ledger.appendTransaction(self, BookAccount.Assets, type * (qty - processed_qty), asset_id=self._asset, value=type * (qty - processed_qty) * self._price) if self._fee: ledger.appendTransaction(self, BookAccount.Costs, self._fee, category=PredefinedCategory.Fees, peer=self._broker)
def prepare_bonds(self): bonds = [] # First put all closed deals with bonds query = executeSQL("SELECT s.symbol AS symbol, s.isin AS isin, d.qty AS qty, cc.iso_code AS country_iso, " "o.timestamp AS o_date, qo.quote AS o_rate, o.settlement AS os_date, o.number AS o_number, " "qos.quote AS os_rate, o.price AS o_price, o.qty AS o_qty, o.fee AS o_fee, -oi.amount AS o_int, " "c.timestamp AS c_date, qc.quote AS c_rate, c.settlement AS cs_date, c.number AS c_number, " "qcs.quote AS cs_rate, c.price AS c_price, c.qty AS c_qty, c.fee AS c_fee, ci.amount AS c_int " "FROM deals AS d " "JOIN trades AS o ON o.id=d.open_op_id AND o.op_type=d.open_op_type " "LEFT JOIN dividends AS oi ON oi.account_id=:account_id AND oi.number=o.number AND oi.timestamp=o.timestamp AND oi.asset_id=o.asset_id " "JOIN trades AS c ON c.id=d.close_op_id AND c.op_type=d.close_op_type " "LEFT JOIN dividends AS ci ON ci.account_id=:account_id AND ci.number=c.number AND ci.timestamp=c.timestamp AND ci.asset_id=c.asset_id " "LEFT JOIN accounts AS a ON a.id = :account_id " "LEFT JOIN assets_ext AS s ON s.id = o.asset_id AND s.currency_id=a.currency_id " "LEFT JOIN countries AS cc ON cc.id = a.country_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 AND qo.currency_id=:base_currency " "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 AND qos.currency_id=:base_currency " "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 AND qc.currency_id=:base_currency " "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 AND qcs.currency_id=:base_currency " "WHERE c.settlement>=:begin AND c.settlement<:end AND d.account_id=:account_id " "AND s.type_id = :bond " "ORDER BY s.symbol, o.timestamp, c.timestamp", [(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id), (":base_currency", JalSettings().getValue('BaseCurrency')), (":bond", PredefinedAsset.Bond)]) while query.next(): deal = readSQLrecord(query, named=True) deal['principal'] = self.BOND_PRINCIPAL if not self.use_settlement: deal['os_rate'] = deal['o_rate'] deal['cs_rate'] = deal['c_rate'] deal['o_type'] = "Покупка" if deal['qty'] >= 0 else "Продажа" deal['c_type'] = "Продажа" if deal['qty'] >= 0 else "Покупка" deal['o_amount'] = round(deal['o_price'] * abs(deal['qty']), 2) deal['o_amount_rub'] = round(deal['o_amount'] * deal['os_rate'], 2) if deal['os_rate'] else 0 deal['c_amount'] = round(deal['c_price'] * abs(deal['qty']), 2) deal['c_amount_rub'] = round(deal['c_amount'] * deal['cs_rate'], 2) if deal['cs_rate'] else 0 # Convert price from currency to % of principal deal['o_price'] = 100.0 * deal['o_price'] / deal['principal'] deal['c_price'] = 100.0 * deal['c_price'] / deal['principal'] deal['o_fee'] = deal['o_fee'] * abs(deal['qty'] / deal['o_qty']) deal['c_fee'] = deal['c_fee'] * abs(deal['qty'] / deal['c_qty']) deal['o_fee_rub'] = round(deal['o_fee'] * deal['o_rate'], 2) if deal['o_rate'] else 0 deal['c_fee_rub'] = round(deal['c_fee'] * deal['c_rate'], 2) if deal['c_rate'] else 0 deal['o_int_rub'] = round(deal['o_int'] * deal['o_rate'], 2) if deal['o_rate'] and deal['o_int'] else 0 deal['c_int_rub'] = round(deal['c_int'] * deal['o_rate'], 2) if deal['o_rate'] and deal['c_int'] else 0 # TODO accrued interest calculations for short deals is not clear - to be corrected deal['income_rub'] = deal['c_amount_rub'] + deal['c_int_rub'] if deal['qty'] >= 0 else deal['o_amount_rub'] deal['income'] = deal['c_amount'] if deal['qty'] >= 0 else deal['o_amount'] deal['spending_rub'] = deal['o_amount_rub'] if deal['qty'] >= 0 else deal['c_amount_rub'] deal['spending_rub'] = deal['spending_rub'] + deal['o_fee_rub'] + deal['c_fee_rub'] + deal['o_int_rub'] deal['spending'] = deal['o_amount'] if deal['qty'] >= 0 else deal['c_amount'] deal['spending'] = deal['spending'] + deal['o_fee'] + deal['c_fee'] deal['profit_rub'] = deal['income_rub'] - deal['spending_rub'] deal['profit'] = deal['income'] - deal['spending'] deal['report_template'] = "bond_trade" bonds.append(deal) # Second - take all bond interest payments not linked with buy/sell transactions query = executeSQL("SELECT b.symbol AS symbol, b.isin AS isin, i.timestamp AS o_date, i.number AS number, " "i.amount AS interest, r.quote AS rate, cc.iso_code AS country_iso " "FROM dividends AS i " "LEFT JOIN trades AS t ON i.account_id=t.account_id AND i.number=t.number " "AND i.timestamp=t.timestamp AND i.asset_id=t.asset_id " "LEFT JOIN accounts AS a ON a.id = i.account_id " "LEFT JOIN assets_ext AS b ON b.id = i.asset_id AND b.currency_id=a.currency_id " "LEFT JOIN countries AS cc ON cc.id = a.country_id " "LEFT JOIN t_last_dates AS ld ON i.timestamp=ld.ref_id " "LEFT JOIN quotes AS r ON ld.timestamp=r.timestamp AND a.currency_id=r.asset_id AND r.currency_id=:base_currency " "WHERE i.timestamp>=:begin AND i.timestamp<:end AND i.account_id=:account_id " "AND i.type = :type_interest AND t.id IS NULL", [(":begin", self.year_begin), (":end", self.year_end), (":account_id", self.account_id), (":base_currency", JalSettings().getValue('BaseCurrency')), (":type_interest", Dividend.BondInterest)]) while query.next(): interest = readSQLrecord(query, named=True) interest['type'] = "Купон" interest['empty'] = '' # to keep cell borders drawn interest['interest_rub'] = round(interest['interest'] * interest['rate'], 2) if interest['rate'] else 0 interest['income_rub'] = interest['profit_rub'] = interest['interest_rub'] interest['spending_rub'] = 0.0 interest['profit'] = interest['interest'] interest['report_template'] = "bond_interest" bonds.append(interest) self.insert_totals(bonds, ["income_rub", "spending_rub", "profit_rub", "profit"]) return bonds
def UpdateQuotes(self, start_timestamp, end_timestamp): self.PrepareRussianCBReader() jal_db = JalDB() query = executeSQL( "WITH _holdings AS ( " "SELECT l.asset_id AS asset, l.account_id FROM ledger AS l " "WHERE l.book_account = :assets_book AND l.timestamp <= :end_timestamp " "GROUP BY l.asset_id " "HAVING SUM(l.amount) > :tolerance " "UNION " "SELECT DISTINCT l.asset_id AS asset, l.account_id FROM ledger AS l " "WHERE l.book_account = :assets_book AND l.timestamp >= :start_timestamp " "AND l.timestamp <= :end_timestamp " "UNION " "SELECT c.id, NULL FROM currencies c " ") " "SELECT h.asset AS asset_id, coalesce(ac.currency_id, 1) AS currency_id, " "a.symbol AS name, a.quote_source AS feed_id, a.isin AS isin, " "MIN(q.timestamp) AS first_timestamp, MAX(q.timestamp) AS last_timestamp " "FROM _holdings AS h " "LEFT JOIN accounts AS ac ON ac.id=h.account_id " "LEFT JOIN assets_ext AS a ON a.id=h.asset AND a.currency_id=coalesce(ac.currency_id, 1) " "LEFT JOIN quotes AS q ON q.asset_id=h.asset " "GROUP BY h.asset, ac.currency_id " "ORDER BY feed_id", [(":start_timestamp", start_timestamp), (":end_timestamp", end_timestamp), (":assets_book", BookAccount.Assets), (":money_book", BookAccount.Money), (":liabilities_book", BookAccount.Liabilities), (":tolerance", Setup.CALC_TOLERANCE)]) while query.next(): asset = readSQLrecord(query, named=True) if asset['asset_id'] == int( JalSettings().getValue('BaseCurrency')): continue first_timestamp = asset[ 'first_timestamp'] if asset['first_timestamp'] != '' else 0 last_timestamp = asset[ 'last_timestamp'] if asset['last_timestamp'] != '' else 0 if start_timestamp < first_timestamp: from_timestamp = start_timestamp else: from_timestamp = last_timestamp if last_timestamp > start_timestamp else start_timestamp if end_timestamp < from_timestamp: continue try: data = self.data_loaders[asset['feed_id']]( asset['asset_id'], asset['name'], asset['currency_id'], asset['isin'], from_timestamp, end_timestamp) except (xml_tree.ParseError, pd.errors.EmptyDataError, KeyError): logging.warning( self.tr("No data were downloaded for ") + f"{asset}") continue if data is not None: quotations = [] for date, quote in data.iterrows( ): # Date in pandas dataset is in UTC by default quotations.append({ 'timestamp': int(date.timestamp()), 'quote': float(quote[0]) }) jal_db.update_quotes(asset['asset_id'], asset['currency_id'], quotations) jal_db.commit() logging.info(self.tr("Download completed"))
def rebuild(self, from_timestamp=-1, fast_and_dirty=False): exception_happened = False last_timestamp = 0 self.amounts.clear() self.values.clear() if from_timestamp >= 0: frontier = from_timestamp operations_count = readSQL( "SELECT COUNT(id) FROM operation_sequence WHERE timestamp >= :frontier", [(":frontier", frontier)]) else: frontier = self.getCurrentFrontier() operations_count = readSQL( "SELECT COUNT(id) FROM operation_sequence WHERE timestamp >= :frontier", [(":frontier", frontier)]) if operations_count > self.SILENT_REBUILD_THRESHOLD: if QMessageBox().warning( None, self.tr("Confirmation"), f"{operations_count}" + self. tr(" operations require rebuild. Do you want to do it right now?" ), QMessageBox.Yes, QMessageBox.No) == QMessageBox.No: JalSettings().setValue('RebuildDB', 1) return if operations_count == 0: logging.info(self.tr("Leger is empty")) return if self.progress_bar is not None: self.progress_bar.setRange(0, operations_count) self.main_window.showProgressBar(True) logging.info( self.tr("Re-building ledger since: ") + f"{datetime.utcfromtimestamp(frontier).strftime('%d/%m/%Y %H:%M:%S')}" ) start_time = datetime.now() _ = executeSQL("DELETE FROM deals WHERE close_timestamp >= :frontier", [(":frontier", frontier)]) _ = executeSQL("DELETE FROM ledger WHERE timestamp >= :frontier", [(":frontier", frontier)]) _ = executeSQL( "DELETE FROM ledger_totals WHERE timestamp >= :frontier", [(":frontier", frontier)]) _ = executeSQL("DELETE FROM open_trades WHERE timestamp >= :frontier", [(":frontier", frontier)]) db_triggers_disable() if fast_and_dirty: # For 30k operations difference of execution time is - with 0:02:41 / without 0:11:44 _ = executeSQL("PRAGMA synchronous = OFF") try: query = executeSQL( "SELECT op_type, id, timestamp, account_id, subtype FROM operation_sequence " "WHERE timestamp >= :frontier", [(":frontier", frontier)]) while query.next(): data = readSQLrecord(query, named=True) last_timestamp = data['timestamp'] operation = LedgerTransaction().get_operation( data['op_type'], data['id'], data['subtype']) operation.processLedger(self) if self.progress_bar is not None: self.progress_bar.setValue(query.at()) except Exception as e: exception_happened = True logging.error(f"{traceback.format_exc()}") finally: if fast_and_dirty: _ = executeSQL("PRAGMA synchronous = ON") db_triggers_enable() if self.progress_bar is not None: self.main_window.showProgressBar(False) # Fill ledger totals values _ = executeSQL( "INSERT INTO ledger_totals" "(op_type, operation_id, timestamp, book_account, asset_id, account_id, amount_acc, value_acc) " "SELECT op_type, operation_id, timestamp, book_account, " "asset_id, account_id, amount_acc, value_acc FROM ledger " "WHERE id IN (" "SELECT MAX(id) FROM ledger WHERE timestamp >= :frontier " "GROUP BY op_type, operation_id, book_account, account_id, asset_id)", [(":frontier", frontier)]) JalSettings().setValue('RebuildDB', 0) if exception_happened: logging.error( self. tr("Exception happened. Ledger is incomplete. Please correct errors listed in log" )) else: logging.info( self.tr("Ledger is complete. Elapsed time: ") + f"{datetime.now() - start_time}" + self.tr(", new frontier: ") + f"{datetime.utcfromtimestamp(last_timestamp).strftime('%d/%m/%Y %H:%M:%S')}" ) self.updated.emit()
def processLedger(self, ledger): processed_qty = 0 processed_value = 0 # Get asset amount accumulated before current operation asset_amount = ledger.getAmount(BookAccount.Assets, self._account, self._asset) if asset_amount < (self._qty - 2 * Setup.CALC_TOLERANCE): raise ValueError( self. tr("Asset amount is not enough for corporate action processing. Date: " ) + f"{datetime.utcfromtimestamp(self._timestamp).strftime('%d/%m/%Y %H:%M:%S')}, " + f"Asset amount: {asset_amount}, Operation: {self.dump()}") # Get a list of all previous not matched trades or corporate actions query = executeSQL( "SELECT timestamp, op_type, operation_id, account_id, asset_id, price, remaining_qty " "FROM open_trades " "WHERE account_id=:account_id AND asset_id=:asset_id AND remaining_qty!=0 " "ORDER BY timestamp, op_type DESC", [(":account_id", self._account), (":asset_id", self._asset)]) while query.next(): opening_trade = readSQLrecord(query, named=True) next_deal_qty = opening_trade['remaining_qty'] if (processed_qty + next_deal_qty) > ( self._qty + 2 * Setup.CALC_TOLERANCE ): # We can't close all trades with current operation raise ValueError( self. tr("Unhandled case: Corporate action covers not full open position. Date: " ) + f"{datetime.utcfromtimestamp(self._timestamp).strftime('%d/%m/%Y %H:%M:%S')}, " + f"Processed: {processed_qty}, Next: {next_deal_qty}, Operation: {self.dump()}" ) _ = executeSQL( "UPDATE open_trades SET remaining_qty=0 " # FIXME - is it true to have here 0? (i.e. if we have not a full match) "WHERE op_type=:op_type AND operation_id=:id AND asset_id=:asset_id", [(":op_type", opening_trade['op_type']), (":id", opening_trade['operation_id']), (":asset_id", self._asset)]) # Deal have the same open and close prices as corportate action doesn't create profit, but redistributes value _ = executeSQL( "INSERT INTO deals(account_id, asset_id, open_op_type, open_op_id, open_timestamp, open_price, " " close_op_type, close_op_id, close_timestamp, close_price, qty) " "VALUES(:account_id, :asset_id, :open_op_type, :open_op_id, :open_timestamp, :open_price, " ":close_op_type, :close_op_id, :close_timestamp, :close_price, :qty)", [(":account_id", self._account), (":asset_id", self._asset), (":open_op_type", opening_trade['op_type']), (":open_op_id", opening_trade['operation_id']), (":open_timestamp", opening_trade['timestamp']), (":open_price", opening_trade['price']), (":close_op_type", self._otype), (":close_op_id", self._oid), (":close_timestamp", self._timestamp), (":close_price", opening_trade['price']), (":qty", next_deal_qty)]) processed_qty += next_deal_qty processed_value += (next_deal_qty * opening_trade['price']) if processed_qty == self._qty: break # Asset allocations for different corporate actions: # +-----------------+-------+-----+------------+-----------+----------+---------------+ # | | Asset | Qty | cost basis | Asset new | Qty new | cost basis | # +-----------------+-------+-----+------------+-----------+----------+---------------+ # | Symbol Change | A | N | 100 % | B | N | 100% | # | (R-)Split | A | N | 100 % | A | M | 100% | # | Merger | A | N | 100 % | B | M | 100% | # | Spin-Off | A | N | 100 % | A & B | AxN, BxM | X% & (100-X)% | # | Delisting | A | N | 100 % | None | None | 0 % | # +-----------------+-------+-----+------------+-----------+----------+---------------+ # Withdraw value with old quantity of old asset as it common for all corporate action ledger.appendTransaction(self, BookAccount.Assets, -processed_qty, asset_id=self._asset, value=-processed_value) if self._subtype == CorporateAction.Delisting: # Map value to costs ledger.appendTransaction(self, BookAccount.Costs, processed_value, category=PredefinedCategory.Profit, peer=self._broker) return new_value = processed_value if self._subtype == CorporateAction.SpinOff: new_value = processed_value * (1 - self._basis) price = (processed_value - new_value) / self._qty # Modify value for old asset ledger.appendTransaction(self, BookAccount.Assets, self._qty, asset_id=self._asset, value=processed_value - new_value) _ = executeSQL( "INSERT INTO open_trades(timestamp, op_type, operation_id, account_id, asset_id, price, remaining_qty) " "VALUES(:timestamp, :type, :operation_id, :account_id, :asset_id, :price, :remaining_qty)", [(":timestamp", self._timestamp), (":type", self._otype), (":operation_id", self._oid), (":account_id", self._account), (":asset_id", self._asset), (":price", price), (":remaining_qty", self._qty)]) # Create value for new asset new_price = new_value / self._qty_after _ = executeSQL( "INSERT INTO open_trades(timestamp, op_type, operation_id, account_id, asset_id, price, remaining_qty) " "VALUES(:timestamp, :type, :operation_id, :account_id, :asset_id, :price, :remaining_qty)", [(":timestamp", self._timestamp), (":type", self._otype), (":operation_id", self._oid), (":account_id", self._account), (":asset_id", self._asset_new), (":price", new_price), (":remaining_qty", self._qty_after)]) ledger.appendTransaction(self, BookAccount.Assets, self._qty_after, asset_id=self._asset_new, value=new_value)
def find_dividend4tax(self, timestamp, account_id, asset_id, prev_tax, new_tax, note): PaymentInLiueOfDividend = 'PAYMENT IN LIEU OF DIVIDEND' TaxNotePattern = r"^(?P<symbol>.*\w) ?\((?P<isin>\w+)\)(?P<prefix>( \w*)+) +(?P<amount>\d+\.\d+)?(?P<suffix>.*)$" DividendNotePattern = r"^(?P<symbol>.*\w) ?\((?P<isin>\w+)\)(?P<prefix>( \w*)+) +(?P<amount>\d+\.\d+)?(?P<suffix>.*) \(.*\)$" dividends = [x for x in self._data[FOF.ASSET_PAYMENTS] if x['type'] == FOF.PAYMENT_DIVIDEND and x['asset'] == asset_id and x['account'] == account_id] account = [x for x in self._data[FOF.ACCOUNTS] if x["id"] == account_id][0] currency = [x for x in self._data[FOF.ASSETS] if x["id"] == account['currency']][0] db_account = JalDB().get_account_id(account['number'], currency['symbol']) asset = [x for x in self._data[FOF.ASSETS] if x["id"] == asset_id][0] isin = asset['isin'] if 'isin' in asset else '' db_asset = JalDB().get_asset_id(asset['symbol'], isin=isin, dialog_new=False) if db_account is not None and db_asset is not None: query = executeSQL( "SELECT -id AS id, -account_id AS account, timestamp, number, " "-asset_id AS asset, amount, tax, note as description FROM dividends " "WHERE type=:div AND account_id=:account_id AND asset_id=:asset_id", [(":div", DividendSubtype.Dividend), (":account_id", db_account), (":asset_id", db_asset)], forward_only=True) while query.next(): db_dividend = readSQLrecord(query, named=True) db_dividend['asset'] = asset_id db_dividend['account'] = account_id dividends.append(db_dividend) if datetime.utcfromtimestamp(timestamp).timetuple().tm_yday < 75: # We may have wrong date in taxes before March, 15 due to tax correction range_start = ManipulateDate.startOfPreviousYear(day=datetime.utcfromtimestamp(timestamp)) dividends = [x for x in dividends if x['timestamp'] >= range_start] else: # For any other day - use exact time match dividends = [x for x in dividends if x['timestamp'] == timestamp] dividends = [x for x in dividends if 'tax' not in x or (abs(x['tax'] - prev_tax) < 0.0001)] dividends = sorted(dividends, key=lambda x: x['timestamp']) # Choose either Dividends or Payments in liue with regards to note of the matching tax if PaymentInLiueOfDividend in note.upper(): dividends = list(filter(lambda item: PaymentInLiueOfDividend in item['description'], dividends)) # we don't check for full match as there are a lot of records without amount else: dividends = list(filter(lambda item: PaymentInLiueOfDividend not in item['description'], dividends)) # Check for full match for dividend in dividends: if (dividend['timestamp'] == timestamp) and (note.upper() == dividend['description'][:len(note)].upper()): return dividend if len(dividends) == 0: return None # Chose most probable dividend - by amount, timestamp and description parts = re.match(TaxNotePattern, note, re.IGNORECASE) if not parts: logging.warning(self.tr("*** MANUAL ENTRY REQUIRED ***")) logging.warning(self.tr("Unhandled tax pattern found: ") + f"{note}") return None parts = parts.groupdict() note_prefix = parts['prefix'] note_suffix = parts['suffix'] try: note_amount = float(parts['amount']) except (ValueError, TypeError): note_amount = 0 score = [0] * len(dividends) for i, dividend in enumerate(dividends): parts = re.match(DividendNotePattern, dividend['description'], re.IGNORECASE) if not parts: logging.warning(self.tr("*** MANUAL ENTRY REQUIRED ***")) logging.warning(self.tr("Unhandled dividend pattern found: ") + f"{dividend['description']}") return None parts = parts.groupdict() try: amount = float(parts['amount']) except (ValueError, TypeError): amount = 0 if abs(amount - note_amount) <= 0.000005: # Description has very similar amount +++++ score[i] += 5 if dividend['timestamp'] == timestamp: # Timestamp exact match gives ++ score[i] += 2 if abs(0.1 * dividend['amount'] - new_tax) <= 0.01: # New tax is 10% of dividend gives + score[i] += 1 if parts['prefix'] == note_prefix: # Prefix part of description match gives + score[i] += 1 if parts['suffix'] == note_suffix: # Suffix part of description match gives + score[i] += 1 for i, vote in enumerate(score): if (vote == max(score)) and (vote > 0): return dividends[i] # Final check - if only one found, return it if len(dividends) == 1: return dividends[0] return None
def prepare_corporate_actions(self): corp_actions = [] # get list of all deals that were opened with corp.action and closed by normal trade query = executeSQL("SELECT d.open_op_id AS operation_id, s.symbol, d.qty AS qty, " "t.number AS trade_number, t.timestamp AS t_date, qt.quote AS t_rate, " "t.settlement AS s_date, qts.quote AS s_rate, t.price AS price, t.fee AS fee, " "s.full_name AS full_name, s.isin AS isin, s.type_id AS type_id " "FROM deals AS d " "JOIN trades AS t ON t.id=d.close_op_id AND t.op_type=d.close_op_type " "LEFT JOIN accounts AS a ON a.id = :account_id " "LEFT JOIN assets_ext AS s ON s.id = t.asset_id AND s.currency_id=a.currency_id " "LEFT JOIN t_last_dates AS ldt ON t.timestamp=ldt.ref_id " "LEFT JOIN quotes AS qt ON ldt.timestamp=qt.timestamp AND a.currency_id=qt.asset_id AND qt.currency_id=:base_currency " "LEFT JOIN t_last_dates AS ldts ON t.settlement=ldts.ref_id " "LEFT JOIN quotes AS qts ON ldts.timestamp=qts.timestamp AND a.currency_id=qts.asset_id AND qts.currency_id=:base_currency " "WHERE t.settlement<:end AND d.account_id=:account_id AND d.open_op_type=:corp_action " "ORDER BY s.symbol, t.timestamp", [(":end", self.year_end), (":account_id", self.account_id), (":corp_action", LedgerTransaction.CorporateAction), (":base_currency", JalSettings().getValue('BaseCurrency'))]) group = 1 basis = 1 previous_symbol = "" while query.next(): actions = [] sale = readSQLrecord(query, named=True) if previous_symbol != sale['symbol']: # Clean processed qty records if symbol have changed _ = executeSQL("DELETE FROM t_last_assets") if sale["s_date"] >= self.year_begin: # Don't put sub-header of operation is out of scope corp_actions.append({'report_template': "symbol_header", 'report_group': 0, 'description': f"Сделки по бумаге: {sale['symbol']} - {sale['full_name']}"}) previous_symbol = sale['symbol'] sale['operation'] = "Продажа" sale['basis_ratio'] = 100.0 * basis sale['amount'] = round(sale['price'] * sale['qty'], 2) if sale['s_rate']: sale['amount_rub'] = round(sale['amount'] * sale['s_rate'], 2) else: sale['amount_rub'] = 0 if sale['t_rate']: sale['fee_rub'] = round(sale['fee'] * sale['t_rate'], 2) else: sale['fee_rub'] = 0 sale['income_rub'] = sale['amount_rub'] sale['spending_rub'] = sale['fee_rub'] if sale["t_date"] < self.year_begin: # Don't show deal that is before report year (level = -1) self.proceed_corporate_action(actions, sale['operation_id'], sale['symbol'], sale['qty'], basis, -1, group) else: sale['report_template'] = "trade" sale['report_group'] = group actions.append(sale) if sale['type_id'] == PredefinedAsset.Bond: self.output_accrued_interest(actions, sale['trade_number'], 1, 0) self.proceed_corporate_action(actions, sale['operation_id'], sale['symbol'], sale['qty'], basis, 1, group) self.insert_totals(actions, ["income_rub", "spending_rub"]) corp_actions += actions group += 1 return corp_actions
def calculateBalances(self): query = executeSQL( "WITH " "_last_quotes AS (SELECT MAX(timestamp) AS timestamp, asset_id, quote " "FROM quotes WHERE timestamp <= :balances_timestamp GROUP BY asset_id), " "_last_dates AS (SELECT account_id AS ref_id, MAX(timestamp) AS timestamp " "FROM ledger WHERE timestamp <= :balances_timestamp GROUP BY ref_id) " "SELECT a.type_id AS account_type, t.name AS type_name, l.account_id AS account, " "a.name AS account_name, a.currency_id AS currency, c.name AS currency_name, " "SUM(CASE WHEN l.book_account=:assets_book THEN l.amount*act_q.quote ELSE l.amount END) AS balance, " "SUM(CASE WHEN l.book_account=:assets_book THEN l.amount*coalesce(act_q.quote*cur_q.quote/cur_adj_q.quote, 0) " "ELSE l.amount*coalesce(cur_q.quote/cur_adj_q.quote, 0) END) AS balance_a, " "(d.timestamp - coalesce(a.reconciled_on, 0))/86400 AS unreconciled, " "a.active AS active " "FROM ledger AS l " "LEFT JOIN accounts AS a ON l.account_id = a.id " "LEFT JOIN assets AS c ON c.id = a.currency_id " "LEFT JOIN account_types AS t ON a.type_id = t.id " "LEFT JOIN _last_quotes AS act_q ON l.asset_id = act_q.asset_id " "LEFT JOIN _last_quotes AS cur_q ON a.currency_id = cur_q.asset_id " "LEFT JOIN _last_quotes AS cur_adj_q ON cur_adj_q.asset_id = :base_currency " "LEFT JOIN _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)>:tolerance " "ORDER BY account_type", [(":base_currency", self._currency), (":money_book", BookAccount.Money), (":assets_book", BookAccount.Assets), (":liabilities_book", BookAccount.Liabilities), (":balances_timestamp", self._date), (":tolerance", Setup.DISP_TOLERANCE)], forward_only=True) self._data = [] current_type = 0 current_type_name = '' field_names = list( map(query.record().fieldName, range( query.record().count()))) + ['level'] while query.next(): values = readSQLrecord(query, named=True) values['level'] = 0 if self._active_only and (values['active'] == 0): continue if values['account_type'] != current_type: if current_type != 0: sub_total = sum([ row['balance_a'] for row in self._data if row['account_type'] == current_type ]) self._data.append( dict( zip(field_names, [ current_type, current_type_name, 0, '', 0, '', 0, sub_total, 0, 1, 1 ]))) current_type = values['account_type'] current_type_name = values['type_name'] self._data.append(values) if current_type != 0: sub_total = sum([ row['balance_a'] for row in self._data if row['account_type'] == current_type ]) self._data.append( dict( zip(field_names, [ current_type, current_type_name, 0, '', 0, '', 0, sub_total, 0, 1, 1 ]))) total_sum = sum( [row['balance_a'] for row in self._data if row['level'] == 0]) self._data.append( dict( zip(field_names, [0, self.tr("Total"), 0, '', 0, '', 0, total_sum, 0, 1, 2]))) self.modelReset.emit()
def calculateHoldings(self): query = executeSQL( "WITH " "_last_quotes AS (SELECT MAX(timestamp) AS timestamp, asset_id, quote " "FROM quotes WHERE timestamp <= :holdings_timestamp GROUP BY asset_id), " "_last_assets AS (" "SELECT id, SUM(t_value) AS total_value " "FROM " "(" "SELECT a.id, SUM(l.amount) AS t_value " "FROM ledger AS l " "LEFT JOIN accounts AS a ON l.account_id = a.id " "WHERE (l.book_account=:money_book OR l.book_account=:liabilities_book) " "AND a.type_id = :investments AND l.timestamp <= :holdings_timestamp GROUP BY a.id " "UNION ALL " "SELECT a.id, SUM(l.amount*q.quote) AS t_value " "FROM ledger AS l " "LEFT JOIN accounts AS a ON l.account_id = a.id " "LEFT JOIN _last_quotes AS q ON l.asset_id = q.asset_id " "WHERE l.book_account=:assets_book AND a.type_id = :investments AND l.timestamp <= :holdings_timestamp " "GROUP BY a.id" ") " "GROUP BY id HAVING ABS(total_value) > :tolerance) " "SELECT h.currency_id, c.name AS currency, h.account_id, h.account, h.asset_id, " "c.name=a.name AS asset_is_currency, a.name AS asset, a.full_name AS asset_name, a.expiry, " "h.qty, h.value AS value_i, h.quote, h.quote_a, h.total FROM (" "SELECT a.currency_id, l.account_id, a.name AS account, l.asset_id, sum(l.amount) AS qty, " "sum(l.value) AS value, q.quote, q.quote*cur_q.quote/cur_adj_q.quote AS quote_a, t.total_value AS total " "FROM ledger AS l " "LEFT JOIN accounts AS a ON l.account_id = a.id " "LEFT JOIN _last_quotes AS q ON l.asset_id = q.asset_id " "LEFT JOIN _last_quotes AS cur_q ON a.currency_id = cur_q.asset_id " "LEFT JOIN _last_quotes AS cur_adj_q ON cur_adj_q.asset_id = :base_currency " "LEFT JOIN _last_assets AS t ON l.account_id = t.id " "WHERE a.type_id = :investments AND l.book_account = :assets_book AND l.timestamp <= :holdings_timestamp " "GROUP BY l.account_id, l.asset_id " "HAVING ABS(qty) > :tolerance " "UNION ALL " "SELECT a.currency_id, l.account_id, a.name AS account, l.asset_id, sum(l.amount) AS qty, " "0 AS value, 1, cur_q.quote/cur_adj_q.quote AS quote_a, t.total_value AS total " "FROM ledger AS l " "LEFT JOIN accounts AS a ON l.account_id = a.id " "LEFT JOIN _last_quotes AS cur_q ON a.currency_id = cur_q.asset_id " "LEFT JOIN _last_quotes AS cur_adj_q ON cur_adj_q.asset_id = :base_currency " "LEFT JOIN _last_assets AS t ON l.account_id = t.id " "WHERE (l.book_account=:money_book OR l.book_account=:liabilities_book) " "AND a.type_id = :investments AND l.timestamp <= :holdings_timestamp " "GROUP BY l.account_id, l.asset_id " "HAVING ABS(qty) > :tolerance " ") AS h " "LEFT JOIN assets AS c ON c.id=h.currency_id " "LEFT JOIN assets AS a ON a.id=h.asset_id " "ORDER BY currency, account, asset_is_currency, asset", [(":base_currency", self._currency), (":money_book", BookAccount.Money), (":assets_book", BookAccount.Assets), (":liabilities_book", BookAccount.Liabilities), (":holdings_timestamp", self._date), (":investments", PredefindedAccountType.Investment), (":tolerance", Setup.DISP_TOLERANCE)], forward_only=True) # Load data from SQL to tree self._root = TreeItem({}) currency = 0 c_node = None account = 0 a_node = None while query.next(): values = readSQLrecord(query, named=True) values['level'] = 2 if values['currency_id'] != currency: currency = values['currency_id'] c_node = TreeItem(values, self._root) c_node.data['level'] = 0 c_node.data['asset_name'] = '' c_node.data['expiry'] = 0 c_node.data['qty'] = 0 self._root.appendChild(c_node) if values['account_id'] != account: account = values['account_id'] a_node = TreeItem(values, c_node) a_node.data['level'] = 1 a_node.data['asset_name'] = '' a_node.data['expiry'] = 0 a_node.data['qty'] = 0 c_node.appendChild(a_node) if values['quote']: if values['asset_is_currency']: profit = 0 else: profit = values['quote'] * values['qty'] - values['value_i'] if values['value_i'] != 0: profit_relative = values['quote'] * values['qty'] / values[ 'value_i'] - 1 else: profit_relative = 0 value = values['quote'] * values['qty'] share = 100.0 * value / values['total'] value_adjusted = values['quote_a'] * values['qty'] if values[ 'quote_a'] else 0 values.update( dict( zip(self.calculated_names, [ share, profit, profit_relative, value, value_adjusted ]))) else: values.update(dict(zip(self.calculated_names, [0, 0, 0, 0, 0]))) node = TreeItem(values, a_node) a_node.appendChild(node) # Update totals for i in range(self._root.count()): # Iterate through each currency currency_child = self._root.getChild(i) for j in range(currency_child.count( )): # Iterate through each account for given currency self.add_node_totals(currency_child.getChild(j)) self.add_node_totals(currency_child) for j in range(currency_child.count( )): # Calculate share of each account within currency if currency_child.data['value']: currency_child.getChild(j).data['share'] = \ 100.0 * currency_child.getChild(j).data['value'] / currency_child.data['value'] # Get full total of totals for all currencies adjusted to common currency total = sum([ self._root.getChild(i).data['value_a'] for i in range(self._root.count()) ]) for i in range(self._root.count( )): # Calculate share of each currency (adjusted to common currency) if total != 0: self._root.getChild( i).data['share'] = 100.0 * self._root.getChild( i).data['value_a'] / total else: self._root.getChild(i).data['share'] = None self.modelReset.emit() self._view.expandAll()
def prepare_chart_data(self): self.currency_name = JalDB().get_asset_name( JalDB().get_account_currency(self.account_id)) start_time = readSQL( "SELECT MAX(ts) FROM " # Take either last "empty" timestamp "(SELECT coalesce(MAX(timestamp), 0) AS ts " "FROM ledger WHERE account_id=:account_id AND asset_id=:asset_id " "AND book_account=:assets_book AND amount_acc==0 " "UNION " # or first timestamp where position started to appear "SELECT coalesce(MIN(timestamp), 0) AS ts " "FROM ledger WHERE account_id=:account_id AND asset_id=:asset_id " "AND book_account=:assets_book AND amount_acc!=0)", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":assets_book", BookAccount.Assets)]) # Get asset quotes query = executeSQL( "SELECT timestamp, quote FROM quotes " "WHERE asset_id=:asset_id AND currency_id=:currency_id AND timestamp>:last", [(":asset_id", self.asset_id), (":currency_id", self.currency_id), (":last", start_time)]) while query.next(): quote = readSQLrecord(query, named=True) self.quotes.append({ 'timestamp': quote['timestamp'] * 1000, 'quote': quote['quote'] }) # timestamp to ms # Get deals prices query = executeSQL( "SELECT timestamp, price, qty FROM trades " "WHERE account_id=:account_id AND asset_id=:asset_id AND timestamp>=:last", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":last", start_time)]) while query.next(): trade = readSQLrecord(query, named=True) self.trades.append({ 'timestamp': trade['timestamp'] * 1000, 'price': trade['price'], 'qty': trade['qty'] }) if self.quotes or self.trades: min_price = min([x['quote'] for x in self.quotes] + [x['price'] for x in self.trades]) max_price = max([x['quote'] for x in self.quotes] + [x['price'] for x in self.trades]) min_ts = min([x['timestamp'] for x in self.quotes] + [x['timestamp'] for x in self.trades]) / 1000 max_ts = max([x['timestamp'] for x in self.quotes] + [x['timestamp'] for x in self.trades]) / 1000 else: self.range = [0, 0, 0, 0] return # push range apart if we have too close points if min_price == max_price: min_price = 0.95 * min_price max_price = 1.05 * max_price if min_ts == max_ts: min_ts = 0.95 * min_ts max_ts = 1.05 * max_ts # Round min/max values to near "round" values in order to have 10 nice intervals step = 10**floor(log10(max_price - min_price)) min_price = floor(min_price / step) * step max_price = ceil(max_price / step) * step # Add a gap at the beginning and end min_ts = int(min_ts - 86400 * 3) max_ts = int(max_ts + 86400 * 3) self.range = [min_ts, max_ts, min_price, max_price]
def prepare_tax(self): _ = executeSQL("DELETE FROM t_last_dates") _ = executeSQL("DELETE FROM t_last_quotes") _ = executeSQL( "INSERT INTO t_last_dates(ref_id, timestamp) " "SELECT ref_id, coalesce(MAX(q.timestamp), 0) AS timestamp " "FROM (" "SELECT t.timestamp AS ref_id FROM trades AS t " "WHERE t.account_id=:account_id AND t.asset_id=:asset_id " "UNION " "SELECT t.settlement AS ref_id FROM trades AS t " "WHERE t.account_id=:account_id AND t.asset_id=:asset_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 AND q.currency_id=:base_currency " "WHERE ref_id IS NOT NULL " "GROUP BY ref_id ORDER BY ref_id", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":base_currency", JalSettings().getValue('BaseCurrency'))]) _ = executeSQL( "INSERT INTO t_last_quotes(timestamp, asset_id, quote) " "SELECT MAX(timestamp) AS timestamp, asset_id, quote " "FROM quotes AS q LEFT JOIN accounts AS a ON a.id = :account_id " "WHERE q.asset_id = :asset_id OR q.asset_id = a.currency_id AND q.currency_id=:base_currency " "GROUP BY asset_id", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":base_currency", JalSettings().getValue('BaseCurrency'))]) self.quote = readSQL( "SELECT quote FROM t_last_quotes WHERE asset_id=:asset_id", [(":asset_id", self.asset_id)]) if self.quote is None: logging.error( self.tr("Can't get current quote for ") + self.asset_name) return self.currency_name = JalDB().get_asset_name( JalDB().get_account_currency(self.account_id)) self.rate = readSQL( "SELECT quote FROM accounts AS a " "LEFT JOIN t_last_quotes AS q ON q.asset_id=a.currency_id WHERE id=:account_id", [(":account_id", self.account_id)]) if self.rate is None: logging.error( self.tr("Can't get current rate for ") + self.currency_name) return query = executeSQL( "SELECT strftime('%d/%m/%Y', datetime(t.timestamp, 'unixepoch')) AS timestamp, " "t.qty AS qty, t.price AS o_price, oq.quote AS o_rate FROM trades AS t " "LEFT JOIN accounts AS ac ON ac.id = :account_id " "LEFT JOIN t_last_dates AS od ON od.ref_id = IIF(t.settlement=0, t.timestamp, t.settlement) " "LEFT JOIN quotes AS oq ON ac.currency_id=oq.asset_id AND oq.currency_id=:base_currency " "AND oq.timestamp=od.timestamp " "WHERE t.account_id=:account_id AND t.asset_id=:asset_id AND t.qty*(:total_qty)>0 " "ORDER BY t.timestamp DESC, t.id DESC", [(":account_id", self.account_id), (":asset_id", self.asset_id), (":total_qty", self.asset_qty), (":base_currency", JalSettings().getValue('BaseCurrency'))]) table = [] remainder = self.asset_qty profit = 0 value = 0 profit_rub = 0 value_rub = 0 while query.next(): record = readSQLrecord(query, named=True) record['qty'] = record[ 'qty'] if record['qty'] <= remainder else remainder record['profit'] = record['qty'] * (self.quote - record['o_price']) record[ 'o_rate'] = 1 if record['o_rate'] == '' else record['o_rate'] record['profit_rub'] = record['qty'] * ( self.quote * self.rate - record['o_price'] * record['o_rate']) record['tax'] = 0.13 * record['profit_rub'] if record[ 'profit_rub'] > 0 else 0 table.append(record) remainder -= record['qty'] profit += record['profit'] value += record['qty'] * record['o_price'] profit_rub += record['profit_rub'] value_rub += record['qty'] * record['o_price'] * record['o_rate'] if remainder <= 0: break tax = 0.13 * profit_rub if profit_rub > 0 else 0 table.append({ 'timestamp': self.tr("TOTAL"), 'qty': self.asset_qty, 'o_price': value / self.asset_qty, 'o_rate': value_rub / value, 'profit': profit, 'profit_rub': profit_rub, 'tax': tax }) data = pd.DataFrame(table) self.dataframe = data