def addOperation(self):
if self.AccountEdit.selected_id == 0:
logging.warning(self.tr("Not possible to import slip: no account set for import"))
return
if self.PeerEdit.selected_id == 0:
logging.warning(self.tr("Not possible to import slip: can't import: no peer set for import"))
return
if self.slip_lines[self.slip_lines['category'] == 0].shape[0] != 0:
logging.warning(self.tr("Not possible to import slip: some categories are not set"))
return
query = executeSQL("INSERT INTO actions (timestamp, account_id, peer_id) "
"VALUES (:timestamp, :account_id, :peer_id)",
[(":timestamp", self.SlipDateTime.dateTime().toSecsSinceEpoch()),
(":account_id", self.AccountEdit.selected_id),
(":peer_id", self.PeerEdit.selected_id)])
pid = query.lastInsertId()
# update mappings
_ = executeSQL("INSERT INTO map_peer (value, mapped_to) VALUES (:peer_name, :peer_id)",
[(":peer_name", self.SlipShopName.text()), (":peer_id", self.PeerEdit.selected_id)])
for index, row in self.slip_lines.iterrows():
_ = executeSQL("INSERT INTO action_details (pid, category_id, tag_id, amount, note) "
"VALUES (:pid, :category_id, :tag_id, :amount, :note)",
[(":pid", pid), (":category_id", row['category']), (":tag_id", row['tag']),
(":amount", row['sum']), (":note", row['name'])])
# update mappings
_ = executeSQL("INSERT INTO map_category (value, mapped_to) VALUES (:item_name, :category_id)",
[(":item_name", row['name']), (":category_id", row['category'])], commit=True)
self.clearSlipData()
def prepare(self, begin, end, account_id, group_dates):
if account_id == 0:
raise ValueError(
g_tr('Reports',
"You should select account to create Deals report"))
self._group_dates = group_dates
if group_dates == 1:
self._query = executeSQL(
"SELECT asset, "
"strftime('%s', datetime(open_timestamp, 'unixepoch', 'start of day')) as open_timestamp, "
"strftime('%s', datetime(close_timestamp, 'unixepoch', 'start of day')) as close_timestamp, "
"SUM(open_price*qty)/SUM(qty) as open_price, SUM(close_price*qty)/SUM(qty) AS close_price, "
"SUM(qty) as qty, SUM(fee) as fee, SUM(profit) as profit, "
"coalesce(100*SUM(qty*(close_price-open_price)-fee)/SUM(qty*open_price), 0) AS rel_profit "
"FROM deals_ext "
"WHERE account_id=:account_id AND close_timestamp>=:begin AND close_timestamp<=:end "
"GROUP BY asset, open_timestamp, close_timestamp "
"ORDER BY close_timestamp, open_timestamp",
[(":account_id", account_id), (":begin", begin),
(":end", end)],
forward_only=False)
else:
self._query = executeSQL(
"SELECT asset, open_timestamp, close_timestamp, open_price, close_price, "
"qty, fee, profit, rel_profit, corp_action FROM deals_ext "
"WHERE account_id=:account_id AND close_timestamp>=:begin AND close_timestamp<=:end "
"ORDER BY close_timestamp, open_timestamp",
[(":account_id", account_id), (":begin", begin),
(":end", end)],
forward_only=False)
self.setQuery(self._query)
self.modelReset.emit()
def calculateDealsReport(self):
if self._account_id == 0:
return
if self._group_dates == 1:
self._query = executeSQL(
"SELECT asset, "
"strftime('%s', datetime(open_timestamp, 'unixepoch', 'start of day')) as o_datetime, "
"strftime('%s', datetime(close_timestamp, 'unixepoch', 'start of day')) as c_datetime, "
"SUM(open_price*qty)/SUM(qty) as open_price, SUM(close_price*qty)/SUM(qty) AS close_price, "
"SUM(qty) as qty, SUM(fee) as fee, SUM(profit) as profit, "
"coalesce(100*SUM(qty*(close_price-open_price)-fee)/SUM(qty*open_price), 0) AS rel_profit "
"FROM deals_ext "
"WHERE account_id=:account_id AND close_timestamp>=:begin AND close_timestamp<=:end "
"GROUP BY asset, o_datetime, c_datetime "
"ORDER BY c_datetime, o_datetime",
[(":account_id", self._account_id), (":begin", self._begin), (":end", self._end)], forward_only=False)
else:
self._query = executeSQL(
"SELECT asset, open_timestamp AS o_datetime, close_timestamp AS c_datetime, "
"open_price, close_price, qty, fee, profit, rel_profit, corp_action "
"FROM deals_ext "
"WHERE account_id=:account_id AND close_timestamp>=:begin AND close_timestamp<=:end "
"ORDER BY c_datetime, o_datetime",
[(":account_id", self._account_id), (":begin", self._begin), (":end", self._end)], forward_only=False)
self.setQuery(self._query)
self.modelReset.emit()
def deleteWithChilderen(self, parent_id: int) -> None:
query = executeSQL(f"SELECT id FROM {self._table} WHERE pid=:pid",
[(":pid", parent_id)])
while query.next():
self.deleteWithChilderen(query.value(0))
_ = executeSQL(f"DELETE FROM {self._table} WHERE id=:id",
[(":id", parent_id)])
def add_transfer(self, timestamp, f_acc_id, f_amount, t_acc_id, t_amount,
fee_acc_id, fee, note):
transfer_id = readSQL(
"SELECT id FROM transfers WHERE withdrawal_timestamp=:timestamp "
"AND withdrawal_account=:from_acc_id AND deposit_account=:to_acc_id",
[(":timestamp", timestamp), (":from_acc_id", f_acc_id),
(":to_acc_id", t_acc_id)])
if transfer_id:
logging.info(
g_tr('JalDB', "Transfer/Exchange already exists: ") +
f"{f_amount}->{t_amount}")
return
if abs(fee) > Setup.CALC_TOLERANCE:
_ = executeSQL(
"INSERT INTO transfers (withdrawal_timestamp, withdrawal_account, withdrawal, "
"deposit_timestamp, deposit_account, deposit, fee_account, fee, note) "
"VALUES (:timestamp, :f_acc_id, :f_amount, :timestamp, :t_acc_id, :t_amount, "
":fee_acc_id, :fee_amount, :note)",
[(":timestamp", timestamp), (":f_acc_id", f_acc_id),
(":t_acc_id", t_acc_id), (":f_amount", f_amount),
(":t_amount", t_amount), (":fee_acc_id", fee_acc_id),
(":fee_amount", fee), (":note", note)],
commit=True)
else:
_ = executeSQL(
"INSERT INTO transfers (withdrawal_timestamp, withdrawal_account, withdrawal, "
"deposit_timestamp, deposit_account, deposit, note) "
"VALUES (:timestamp, :f_acc_id, :f_amount, :timestamp, :t_acc_id, :t_amount, :note)",
[(":timestamp", timestamp), (":f_acc_id", f_acc_id),
(":t_acc_id", t_acc_id), (":f_amount", f_amount),
(":t_amount", t_amount), (":note", note)],
commit=True)
def prepare_db_taxes(prepare_db):
assert executeSQL(
"INSERT INTO agents (pid, name) VALUES (0, 'IB')") is not None
assert executeSQL(
"INSERT INTO accounts (type_id, name, currency_id, active, number, organization_id, country_id) "
"VALUES (4, 'Inv. Account', 2, 1, 'U7654321', 1, 2)") is not None
yield
def add_asset(self,
symbol,
name,
asset_type,
isin,
data_source=-1,
reg_code=None,
country_code='',
expiry=0): # TODO Change params to **kwargs
country_id = get_country_by_code(country_code)
query = executeSQL(
"INSERT INTO assets(name, type_id, full_name, isin, src_id, country_id, expiry) "
"VALUES(:symbol, :type, :full_name, :isin, :data_src, :country_id, :expiry)",
[(":symbol", symbol), (":type", asset_type), (":full_name", name),
(":isin", isin), (":data_src", data_source),
(":country_id", country_id), (":expiry", expiry)],
commit=True)
asset_id = query.lastInsertId()
if asset_id is None:
logging.error(self.tr("Failed to add new asset: ") + f"{symbol}")
if reg_code is not None:
_ = executeSQL(
"INSERT INTO asset_reg_id(asset_id, reg_code) VALUES(:asset_id, :new_reg)",
[(":new_reg", reg_code), (":asset_id", asset_id)])
return asset_id
def add_symbol(self,
asset_id,
symbol,
currency,
note,
data_source=MarketDataFeed.NA):
existing = readSQL(
"SELECT id, symbol, description, quote_source FROM asset_tickers "
"WHERE asset_id=:asset_id AND symbol=:symbol AND currency_id=:currency",
[(":asset_id", asset_id), (":symbol", symbol),
(":currency", currency)],
named=True)
if existing is None: # Deactivate old symbols and create a new one
_ = executeSQL(
"UPDATE asset_tickers SET active=0 WHERE asset_id=:asset_id AND currency_id=:currency",
[(":asset_id", asset_id), (":currency", currency)])
_ = executeSQL(
"INSERT INTO asset_tickers (asset_id, symbol, currency_id, description, quote_source) "
"VALUES (:asset_id, :symbol, :currency, :note, :data_source)",
[(":asset_id", asset_id), (":symbol", symbol),
(":currency", currency), (":note", note),
(":data_source", data_source)])
else: # Update data for existing symbol
if not existing['description']:
_ = executeSQL(
"UPDATE asset_tickers SET description=:note WHERE id=:id",
[(":note", note), (":id", existing['id'])])
if existing['quote_source'] == MarketDataFeed.NA:
_ = executeSQL(
"UPDATE asset_tickers SET quote_source=:data_source WHERE id=:id",
[(":data_source", data_source), (":id", existing['id'])])
def appendTransaction(self, book, amount, value=None):
seq_id = self.current_seq
timestamp = self.current['timestamp']
if book == BookAccount.Assets:
asset_id = self.current['asset']
else:
asset_id = self.current['currency']
account_id = self.current['account']
if book == BookAccount.Costs or book == BookAccount.Incomes:
peer_id = self.current['peer']
category_id = self.current['category']
tag_id = None if self.current['tag'] == '' else self.current['tag']
else:
peer_id = None
category_id = None
tag_id = None
value = 0.0 if value is None else value
self.amounts[(book, account_id, asset_id)] += amount
self.values[(book, account_id, asset_id)] += value
if (abs(amount) + abs(value)) <= (4 * Setup.CALC_TOLERANCE):
return # we have zero amount - no reason to put it into ledger
_ = executeSQL(
"INSERT INTO ledger (timestamp, sid, book_account, asset_id, account_id, "
"amount, value, peer_id, category_id, tag_id) "
"VALUES(:timestamp, :sid, :book, :asset_id, :account_id, "
":amount, :value, :peer_id, :category_id, :tag_id)",
[(":timestamp", timestamp), (":sid", seq_id), (":book", book),
(":asset_id", asset_id), (":account_id", account_id),
(":amount", amount), (":value", value), (":peer_id", peer_id),
(":category_id", category_id), (":tag_id", tag_id)])
try:
old_sid = self.sids[(book, account_id, asset_id)]
except KeyError:
old_sid = -1
if seq_id == old_sid:
_ = executeSQL(
"UPDATE ledger_sums SET sum_amount = :new_amount, sum_value = :new_value"
" WHERE sid = :sid AND book_account = :book"
" AND asset_id = :asset_id AND account_id = :account_id",
[(":new_amount", self.amounts[(book, account_id, asset_id)]),
(":new_value", self.values[(book, account_id, asset_id)]),
(":sid", seq_id), (":book", book), (":asset_id", asset_id),
(":account_id", account_id)],
commit=True)
else:
_ = executeSQL(
"INSERT INTO ledger_sums(sid, timestamp, book_account, "
"asset_id, account_id, sum_amount, sum_value) "
"VALUES(:sid, :timestamp, :book, :asset_id, "
":account_id, :new_amount, :new_value)",
[(":sid", seq_id), (":timestamp", timestamp), (":book", book),
(":asset_id", asset_id), (":account_id", account_id),
(":new_amount", self.amounts[(book, account_id, asset_id)]),
(":new_value", self.values[(book, account_id, asset_id)])],
commit=True)
self.sids[(book, account_id, asset_id)] = seq_id
def prepare_db_fifo(prepare_db):
assert executeSQL("INSERT INTO agents (pid, name) VALUES (0, 'Test Peer')") is not None
assert executeSQL("INSERT INTO accounts (type_id, name, currency_id, active, number, organization_id) "
"VALUES (4, 'Inv. Account', 2, 1, 'U7654321', 1)") is not None
# Create starting balance
assert executeSQL("INSERT INTO actions (timestamp, account_id, peer_id) VALUES (1604221200, 1, 1)") is not None
assert executeSQL("INSERT INTO action_details (pid, category_id, amount, note) "
"VALUES (1, :category, 10000.0, 'Initial balance')",
[(":category", PredefinedCategory.StartingBalance)]) is not None
def test_symbol_change(prepare_db_fifo):
# Prepare trades and corporate actions setup
test_assets = [(4, 'A', 'A SHARE'), (5, 'B', 'B SHARE')]
for asset in test_assets:
assert executeSQL(
"INSERT INTO assets (id, name, type_id, full_name) "
"VALUES (:id, :name, :type, :full_name)",
[(":id", asset[0]), (":name", asset[1]),
(":type", PredefinedAsset.Stock), (":full_name", asset[2])],
commit=True) is not None
test_corp_actions = [(1, 1622548800, 3, 4, 100.0, 5, 100.0, 1.0,
'Symbol change 100 A -> 100 B')]
for action in test_corp_actions:
assert executeSQL(
"INSERT INTO corp_actions "
"(id, timestamp, account_id, type, asset_id, qty, asset_id_new, qty_new, basis_ratio, note) "
"VALUES (:id, :timestamp, 1, :type, :a_o, :q_o, :a_n, :q_n, :ratio, :note)",
[(":id", action[0]), (":timestamp", action[1]),
(":type", action[2]), (":a_o", action[3]), (":q_o", action[4]),
(":a_n", action[5]), (":q_n", action[6]), (":ratio", action[7]),
(":note", action[8])],
commit=True) is not None
test_trades = [
(1, 1619870400, 1619870400, 4, 100.0, 10.0,
0.0), # Buy 100 A x 10.00 01/05/2021
(2, 1625140800, 1625140800, 5, -100.0, 20.0, 0.0
) # Sell 100 B x 20.00 01/07/2021
]
for trade in test_trades:
assert executeSQL(
"INSERT INTO trades (id, timestamp, settlement, account_id, asset_id, qty, price, fee) "
"VALUES (:id, :timestamp, :settlement, 1, :asset, :qty, :price, :fee)",
[(":id", trade[0]), (":timestamp", trade[1]),
(":settlement", trade[2]),
(":asset", trade[3]), (":qty", trade[4]), (":price", trade[5]),
(":fee", trade[6])]) is not None
# Build ledgerye
ledger = Ledger()
ledger.rebuild(from_timestamp=0)
assert readSQL("SELECT * FROM deals_ext WHERE asset_id=4") == [
1, 'Inv. Account', 4, 'A', 1619870400, 1622548800, 10.0, 10.0, 100.0,
0.0, 0.0, 0.0, -3
]
assert readSQL("SELECT * FROM deals_ext WHERE asset_id=5") == [
1, 'Inv. Account', 5, 'B', 1622548800, 1625140800, 10.0, 20.0, 100.0,
0.0, 1000.0, 100.0, 3
]
def create_stocks(assets, currency_id):
for asset in assets:
query = executeSQL(
"INSERT INTO assets (id, type_id, full_name) VALUES (:id, :type, :full_name)",
[(":id", asset[0]), (":type", PredefinedAsset.Stock),
(":full_name", asset[2])],
commit=True)
asset_id = query.lastInsertId()
assert executeSQL(
"INSERT INTO asset_tickers (asset_id, symbol, currency_id) "
"VALUES (:asset_id, :symbol, :currency_id)",
[(":asset_id", asset_id), (":symbol", asset[1]),
(":currency_id", currency_id)],
commit=True) is not None
def prepare_db_ibkr(prepare_db):
assert executeSQL("INSERT INTO agents (pid, name) VALUES (0, 'IB')") is not None
assert executeSQL("INSERT INTO accounts (type_id, name, currency_id, active, number, organization_id) "
"VALUES (4, 'Inv. Account', 2, 1, 'U7654321', 1)") is not None
assert executeSQL("INSERT INTO assets (id, name, type_id, full_name, src_id) "
"VALUES (4, 'VUG', 4, 'Growth ETF', 0), "
"(5, 'EDV', 4, 'VANGUARD EXTENDED DUR TREAS', 0)") is not None
assert executeSQL("INSERT INTO dividends (id, timestamp, type, account_id, asset_id, amount, tax, note) "
"VALUES (1, 1529612400, 1, 1, 5, 16.76, 1.68, "
"'EDV (US9219107094) CASH DIVIDEND USD 0.8381 (Ordinary Dividend)'), "
"(2, 1533673200, 1, 1, 5, 20.35, 2.04, "
"'EDV(US9219107094) CASH DIVIDEND 0.10175000 USD PER SHARE (Ordinary Dividend)')") is not None
yield
def prepare_db_moex(prepare_db): # Create SBER stock in database to be updated from www.moex.com
assert executeSQL("INSERT INTO assets (id, name, type_id, full_name, isin, src_id) "
"VALUES (4, 'SBER', :stock, '', '', 0)",
[(":stock", PredefinedAsset.Stock)]) is not None
assert executeSQL("INSERT INTO assets (id, name, type_id, full_name, isin, src_id) "
"VALUES (5, 'SiZ1', :derivative, 'Si-12.11 Контракт на курс доллар-рубль', '', 0)",
[(":derivative", PredefinedAsset.Derivative)]) is not None
assert executeSQL("INSERT INTO assets (id, name, type_id, full_name, isin, src_id) "
"VALUES (6, 'SU26238RMFS4', :bond, '', 'RU000A1038V6', 0)",
[(":bond", PredefinedAsset.Bond)]) is not None
assert executeSQL("INSERT INTO assets (id, name, type_id, full_name, isin, src_id) "
"VALUES (7, 'МКБ 1P2', :bond, '', 'RU000A1014H6', 0)",
[(":bond", PredefinedAsset.Bond)]) is not None
yield
def add_cash_transaction(self, account_id, broker_id, timestamp, amount,
category_id, description):
query = executeSQL(
"INSERT INTO actions (timestamp, account_id, peer_id) "
"VALUES (:timestamp, :account_id, :bank_id)",
[(":timestamp", timestamp), (":account_id", account_id),
(":bank_id", broker_id)])
pid = query.lastInsertId()
_ = executeSQL(
"INSERT INTO action_details (pid, category_id, sum, note) "
"VALUES (:pid, :category_id, :sum, :note)",
[(":pid", pid), (":category_id", category_id), (":sum", amount),
(":note", description)],
commit=True)
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 add_account(self,
account_number,
currency_id,
account_type=PredefindedAccountType.Investment):
account_id = self.find_account(account_number, currency_id)
if account_id: # Account already exists
logging.warning(
self.tr("Account already exists: ") +
f"{account_number} ({self.get_asset_name(currency_id)})")
return account_id
currency = self.get_asset_name(currency_id)
account = readSQL(
"SELECT a.name, a.organization_id, a.country_id, c.symbol AS currency FROM accounts a "
"LEFT JOIN assets_ext c ON c.id=a.currency_id WHERE a.number=:number LIMIT 1",
[(":number", account_number)],
named=True)
if account: # Account with the same number but different currency exists
if account['name'][-len(account['currency']
):] == account['currency']:
new_name = account['name'][:-len(account['currency']
)] + currency
else:
new_name = account['name'] + '.' + currency
query = executeSQL(
"INSERT INTO accounts (type_id, name, active, number, currency_id, organization_id, country_id) "
"SELECT a.type_id, :new_name, a.active, a.number, :currency_id, a.organization_id, a.country_id "
"FROM accounts AS a LEFT JOIN assets AS c ON c.id=:currency_id "
"WHERE number=:account_number LIMIT 1",
[(":account_number", account_number),
(":currency_id", currency_id), (":new_name", new_name)])
return query.lastInsertId()
bank_name = self.tr("Bank for #" + account_number)
bank_id = readSQL("SELECT id FROM agents WHERE name=:bank_name",
[(":bank_name", bank_name)])
if bank_id is None:
query = executeSQL(
"INSERT INTO agents (pid, name) VALUES (0, :bank_name)",
[(":bank_name", bank_name)])
bank_id = query.lastInsertId()
query = executeSQL(
"INSERT INTO accounts (type_id, name, active, number, currency_id, organization_id) "
"VALUES(:type, :name, 1, :number, :currency, :bank)",
[(":type", account_type),
(":name", account_number + '.' + currency),
(":number", account_number), (":currency", currency_id),
(":bank", bank_id)])
return query.lastInsertId()
def processStockDividend(self, ledger):
asset_amount = ledger.getAmount(BookAccount.Assets, self._account,
self._asset)
if asset_amount < -Setup.CALC_TOLERANCE:
raise NotImplemented(
self.tr(
"Not supported action: stock dividend closes short trade.")
+ f" Operation: {self.dump()}")
quote = JalDB().get_quote(self._asset,
JalDB().get_account_currency(self._account),
self._timestamp)
if quote is None:
raise ValueError(
self.tr("No stock quote for stock dividend.") +
f" Operation: {self.dump()}")
_ = 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", quote),
(":remaining_qty", self._amount)])
ledger.appendTransaction(self,
BookAccount.Assets,
self._amount,
asset_id=self._asset,
value=self._amount * quote)
if self._tax:
ledger.appendTransaction(self, BookAccount.Money, -self._tax)
ledger.appendTransaction(self,
BookAccount.Costs,
self._tax,
category=PredefinedCategory.Taxes,
peer=self._broker)
def loadIBOptions(self, options):
transaction_desctiption = {
"Assignment": g_tr('StatementLoader', "Option assignment"),
"Exercise": g_tr('StatementLoader', "Option exercise"),
"Expiration": g_tr('StatementLoader', "Option expiration"),
"Buy": g_tr('StatementLoader', "Option assignment/exercise"),
"Sell": g_tr('StatementLoader', "Option assignment/exercise"),
}
cnt = 0
for option in options:
try:
description = transaction_desctiption[
option['transactionType']]
if description:
_ = executeSQL(
"UPDATE trades SET note=:description WHERE "
"account_id=:account_id AND asset_id=:asset_id AND number=:trade_id",
[(":description", description),
(":account_id", option['accountId']),
(":asset_id", option['symbol']),
(":trade_id", option['tradeID'])],
commit=True)
cnt += 1
except KeyError:
logging.error(
g_tr('StatementLoader',
"Option E&A&E action isn't implemented: ") +
f"{option['transactionType']}")
logging.info(
g_tr('StatementLoader', "Options E&A&E loaded: ") +
f"{cnt} ({len(options)})")
def init_db(self, db_path) -> JalDBError:
db = QSqlDatabase.addDatabase("QSQLITE", Setup.DB_CONNECTION)
if not db.isValid():
return JalDBError(JalDBError.DbDriverFailure)
db.setDatabaseName(get_dbfilename(db_path))
db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1")
db.open()
sqlite_version = readSQL("SELECT sqlite_version()")
if parse_version(sqlite_version) < parse_version(
Setup.SQLITE_MIN_VERSION):
db.close()
return JalDBError(JalDBError.OutdatedSqlite)
tables = db.tables(QSql.Tables)
if not tables:
logging.info("Loading DB initialization script")
error = self.run_sql_script(db_path + Setup.INIT_SCRIPT_PATH)
if error.code != JalDBError.NoError:
return error
schema_version = JalSettings().getValue('SchemaVersion')
if schema_version < Setup.TARGET_SCHEMA:
db.close()
return JalDBError(JalDBError.OutdatedDbSchema)
elif schema_version > Setup.TARGET_SCHEMA:
db.close()
return JalDBError(JalDBError.NewerDbSchema)
_ = executeSQL("PRAGMA foreign_keys = ON")
db_triggers_enable()
return JalDBError(JalDBError.NoError)
def add_trade(self,
account_id,
asset_id,
timestamp,
settlement,
number,
qty,
price,
fee,
note=''):
trade_id = readSQL(
"SELECT id FROM trades "
"WHERE timestamp=:timestamp AND asset_id = :asset "
"AND account_id = :account AND number = :number AND qty = :qty AND price = :price",
[(":timestamp", timestamp), (":asset", asset_id),
(":account", account_id), (":number", number), (":qty", qty),
(":price", price)])
if trade_id:
logging.info(self.tr("Trade already exists: #") + f"{number}")
return
_ = executeSQL(
"INSERT INTO trades (timestamp, settlement, number, account_id, asset_id, qty, price, fee, note)"
" VALUES (:timestamp, :settlement, :number, :account, :asset, :qty, :price, :fee, :note)",
[(":timestamp", timestamp), (":settlement", settlement),
(":number", number), (":account", account_id),
(":asset", asset_id), (":qty", float(qty)),
(":price", float(price)), (":fee", float(fee)), (":note", note)],
commit=True)
def add_dividend(self,
subtype,
timestamp,
account_id,
asset_id,
amount,
note,
trade_number='',
tax=0.0,
price=None):
id = readSQL(
"SELECT id FROM dividends WHERE timestamp=:timestamp AND type=:subtype AND account_id=:account_id "
"AND asset_id=:asset_id AND amount=:amount AND note=:note",
[(":timestamp", timestamp), (":subtype", subtype),
(":account_id", account_id), (":asset_id", asset_id),
(":amount", amount), (":note", note)])
if id:
logging.info(self.tr("Dividend already exists: ") + f"{note}")
return
_ = executeSQL(
"INSERT INTO dividends (timestamp, number, type, account_id, asset_id, amount, tax, note) "
"VALUES (:timestamp, :number, :subtype, :account_id, :asset_id, :amount, :tax, :note)",
[(":timestamp", timestamp), (":number", trade_number),
(":subtype", subtype), (":account_id", account_id),
(":asset_id", asset_id), (":amount", amount), (":tax", tax),
(":note", note)],
commit=True)
if price is not None:
self.update_quotes(asset_id, self.get_account_currency(account_id),
[{
'timestamp': timestamp,
'quote': price
}])
def addWithholdingTax(self, timestamp, account_id, asset_id, amount, note):
parts = re.match(IBKR.TaxNotePattern, note)
if not parts:
logging.warning(
g_tr('StatementLoader', "*** MANUAL ENTRY REQUIRED ***"))
logging.warning(
g_tr('StatementLoader', "Unhandled tax pattern found: ") +
f"{note}")
return
dividend_note = parts.group(1) + '%'
country_code = parts.group(2).lower()
country_id = get_country_by_code(country_code)
update_asset_country(asset_id, country_id)
dividend_id = self.findDividend4Tax(timestamp, account_id, asset_id,
dividend_note)
if dividend_id is None:
logging.warning(
g_tr('StatementLoader',
"Dividend not found for withholding tax: ") + f"{note}")
return
old_tax = readSQL("SELECT tax FROM dividends WHERE id=:id",
[(":id", dividend_id)])
_ = executeSQL("UPDATE dividends SET tax=:tax WHERE id=:dividend_id",
[(":dividend_id", dividend_id),
(":tax", old_tax + amount)],
commit=True)
def deleteRows(self, rows):
for row in rows:
if (row >= 0) and (row < len(self._data)):
table_name = self._tables[self._data[row][self.COL_TYPE]]
query = f"DELETE FROM {table_name} WHERE id={self._data[row][self.COL_ID]}"
_ = executeSQL(query)
self.prepareData()
def add_dividend(self,
subtype,
timestamp,
account_id,
asset_id,
amount,
note,
trade_number='',
tax=0.0):
id = readSQL(
"SELECT id FROM dividends WHERE timestamp=:timestamp "
"AND account_id=:account_id AND asset_id=:asset_id AND note=:note",
[(":timestamp", timestamp), (":account_id", account_id),
(":asset_id", asset_id), (":note", note)])
if id:
logging.info(
g_tr('JalDB', "Dividend already exists: ") + f"{note}")
return
_ = executeSQL(
"INSERT INTO dividends (timestamp, number, type, account_id, asset_id, amount, tax, note) "
"VALUES (:timestamp, :number, :subtype, :account_id, :asset_id, :amount, :tax, :note)",
[(":timestamp", timestamp), (":number", trade_number),
(":subtype", subtype), (":account_id", account_id),
(":asset_id", asset_id), (":amount", amount), (":tax", tax),
(":note", note)],
commit=True)
def onLanguageChanged(self, action):
language_code = action.data()
if language_code != self.currentLanguage:
executeSQL(
"UPDATE settings "
"SET value=(SELECT id FROM languages WHERE language = :new_language) WHERE name ='Language'",
[(':new_language', language_code)])
QMessageBox().information(
self, g_tr('MainWindow', "Restart required"),
g_tr('MainWindow', "Language was changed to ") +
QLocale.languageToString(QLocale(language_code).language()) +
"\n" + g_tr(
'MainWindow',
"You should restart application to apply changes\n"
"Application will be terminated now"), QMessageBox.Ok)
self.close()
def add_corporate_action(self, account_id, type, timestamp, number,
asset_id_old, qty_old, asset_id_new, qty_new,
basis_ratio, note):
action_id = readSQL(
"SELECT id FROM corp_actions "
"WHERE timestamp=:timestamp AND type = :type AND account_id = :account AND number = :number "
"AND asset_id = :asset AND asset_id_new = :asset_new",
[(":timestamp", timestamp), (":type", type),
(":account", account_id), (":number", number),
(":asset", asset_id_old), (":asset_new", asset_id_new)])
if action_id:
logging.info(
g_tr('JalDB', "Corporate action already exists: #") +
f"{number}")
return
_ = executeSQL(
"INSERT INTO corp_actions (timestamp, number, account_id, type, "
"asset_id, qty, asset_id_new, qty_new, basis_ratio, note) "
"VALUES (:timestamp, :number, :account, :type, "
":asset, :qty, :asset_new, :qty_new, :basis_ratio, :note)",
[(":timestamp", timestamp), (":number", number),
(":account", account_id), (":type", type),
(":asset", asset_id_old), (":qty", float(qty_old)),
(":asset_new", asset_id_new), (":qty_new", float(qty_new)),
(":basis_ratio", basis_ratio), (":note", note)],
commit=True)
def add_account(self,
account_number,
currency_code,
account_type=PredefindedAccountType.Investment):
account_id = self.find_account(account_number, currency_code)
if account_id: # Account already exists
logging.warning(
self.tr("Account already exists: ") +
f"{account_number} ({self.get_asset_name(currency_code)})")
return account_id
currency = self.get_asset_name(currency_code)
account_info = readSQL(
"SELECT a.name AS name, SUBSTR(a.name, 1, LENGTH(a.name)-LENGTH(c.name)-1) AS short_name, "
"SUBSTR(a.name, -(LENGTH(c.name)+1), LENGTH(c.name)+1) = '.'||c.name AS auto_name "
"FROM accounts AS a LEFT JOIN assets AS c ON a.currency_id = c.id WHERE number=:account_number LIMIT 1",
[(":account_number", account_number)],
named=True)
if account_info: # Account with the same number but different currency exists
if account_info['auto_name']:
new_name = account_info['short_name'] + '.' + currency
else:
new_name = account_info['name'] + '.' + currency
query = executeSQL(
"INSERT INTO accounts (type_id, name, active, number, currency_id, organization_id, country_id) "
"SELECT a.type_id, :new_name, a.active, a.number, :currency_id, a.organization_id, a.country_id "
"FROM accounts AS a LEFT JOIN assets AS c ON c.id=:currency_id "
"WHERE number=:account_number LIMIT 1",
[(":account_number", account_number),
(":currency_id", currency_code), (":new_name", new_name)])
return query.lastInsertId()
bank_name = self.tr("Bank for #" + account_number)
bank_id = readSQL("SELECT id FROM agents WHERE name=:bank_name",
[(":bank_name", bank_name)])
if bank_id is None:
query = executeSQL(
"INSERT INTO agents (pid, name) VALUES (0, :bank_name)",
[(":bank_name", bank_name)])
bank_id = query.lastInsertId()
query = executeSQL(
"INSERT INTO accounts (type_id, name, active, number, currency_id, organization_id) "
"VALUES(:type, :name, 1, :number, :currency, :bank)",
[(":type", account_type),
(":name", account_number + '.' + currency),
(":number", account_number), (":currency", currency_code),
(":bank", bank_id)])
return query.lastInsertId()
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 run_sql_script(self, script_file) -> JalDBError:
try:
with open(script_file) as sql_script:
statements = sqlparse.split(sql_script)
for statement in statements:
clean_statement = sqlparse.format(statement,
strip_comments=True)
if executeSQL(clean_statement, commit=False) is None:
_ = executeSQL("ROLLBACK")
db_connection().close()
return JalDBError(JalDBError.SQLFailure,
f"FAILED: {clean_statement}")
else:
logging.debug(f"EXECUTED OK:\n{clean_statement}")
except FileNotFoundError:
return JalDBError(JalDBError.NoDeltaFile, script_file)
return JalDBError(JalDBError.NoError)