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
