def test_ukfu_json_import(tmp_path, project_root, data_path, prepare_db_moex):
statement = Statement()
statement.load(data_path + 'ukfu.json')
statement.validate_format()
statement.match_db_ids(verbal=False)
statement.import_into_db()
# validate assets
test_assets = [
[1, 'RUB', 1, 'Российский Рубль', '', 0, -1, 0],
[2, 'USD', 1, 'Доллар США', '', 0, 0, 0],
[3, 'EUR', 1, 'Евро', '', 0, 0, 0],
[4, 'SBER', 2, '', '', 0, 0, 0],
[5, 'SiZ1', 6, 'Si-12.11 Контракт на курс доллар-рубль', '', 0, 0, 0],
[6, 'SU26238RMFS4', 3, '', 'RU000A1038V6', 0, 0, 0],
[7, 'МКБ 1P2', 3, '', 'RU000A1014H6', 0, 0, 0],
[8, 'FXGD', 4, 'FinEx Gold ETF USD', 'IE00B8XB7377', 0, 1, 0],
[9, 'ТинькоффБ7', 3, 'АО "Тинькофф Банк" БО-07', 'RU000A0JWM31', 0, 1, 1624492800],
[10, 'ЗПИФ ПНК', 4, 'ЗПИФ Фонд ПНК-Рентал', 'RU000A1013V9', 0, 1, 0],
[11, 'VTBR', 2, 'ао ПАО Банк ВТБ', 'RU000A0JP5V6', 0, 1, 0],
[12, 'POLY', 2, 'Polymetal International plc', 'JE00B6T5S470', 0, 1, 0],
[13, 'SiZ1', 6, 'Фьючерсный контракт Si-12.21', '', 0, 1, 1639612800],
[14, 'MOEX', 2, 'ПАО Московская Биржа', 'RU000A0JR4A1', 0, 1, 0],
[15, 'CHMF', 2, 'Северсталь (ПАО)ао', 'RU0009046510', 0, 1, 0]
]
assert readSQL("SELECT COUNT(*) FROM assets") == len(test_assets)
for i, asset in enumerate(test_assets):
assert readSQL("SELECT * FROM assets WHERE id=:id", [(":id", i + 1)]) == asset
def get_asset_id(self,
symbol,
isin='',
reg_code='',
name='',
expiry=0,
dialog_new=True): # TODO Change params to **kwargs
asset_id = None
if isin:
asset_id = readSQL("SELECT id FROM assets WHERE isin=:isin",
[(":isin", isin)])
if asset_id is None:
asset_id = readSQL(
"SELECT id FROM assets WHERE name=:symbol COLLATE NOCASE AND coalesce(isin, '')=''",
[(":symbol", symbol)])
if asset_id is None:
if reg_code:
asset_id = readSQL(
"SELECT asset_id FROM asset_reg_id WHERE reg_code=:reg_code",
[(":reg_code", reg_code)])
if asset_id is None:
asset_id = readSQL(
"SELECT id FROM assets WHERE name=:symbol AND "
"((expiry=:expiry AND type_id=:derivative) OR type_id<>:derivative) COLLATE NOCASE",
[(":symbol", symbol), (":expiry", expiry),
(":derivative", PredefinedAsset.Derivative)])
if asset_id is None and dialog_new:
dialog = AddAssetDialog(symbol, isin=isin, name=name)
dialog.exec()
asset_id = dialog.asset_id
return asset_id
def test_symbol_change(prepare_db_fifo):
# Prepare trades and corporate actions setup
test_assets = [(4, 'A', 'A SHARE'), (5, 'B', 'B SHARE')]
create_stocks(test_assets, currency_id=2)
test_corp_actions = [(1622548800, CorporateAction.SymbolChange, 4, 100.0,
5, 100.0, 1.0, 'Symbol change 100 A -> 100 B')]
create_corporate_actions(1, test_corp_actions)
test_trades = [
(1619870400, 1619870400, 4, 100.0, 10.0,
0.0), # Buy 100 A x 10.00 01/05/2021
(1625140800, 1625140800, 5, -100.0, 20.0, 0.0
) # Sell 100 B x 20.00 01/07/2021
]
create_trades(1, test_trades)
# 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 get_asset_id(self,
symbol,
isin='',
reg_code='',
name='',
dialog_new=True):
asset_id = None
if isin:
asset_id = readSQL("SELECT id FROM assets WHERE isin=:isin",
[(":isin", isin)])
if asset_id is None:
asset_id = readSQL(
"SELECT id FROM assets WHERE name=:symbol COLLATE NOCASE AND coalesce(isin, '')=''",
[(":symbol", symbol)])
else:
if reg_code:
asset_id = readSQL(
"SELECT asset_id FROM asset_reg_id WHERE reg_code=:reg_code",
[(":reg_code", reg_code)])
if asset_id is None:
asset_id = readSQL(
"SELECT id FROM assets WHERE name=:symbol COLLATE NOCASE",
[(":symbol", symbol)])
if asset_id is not None:
self.update_asset_data(asset_id, symbol, isin, reg_code)
elif dialog_new:
dialog = AddAssetDialog(symbol, isin=isin, name=name)
dialog.exec_()
asset_id = dialog.asset_id
return asset_id
def findDividend4Tax(self, timestamp, account_id, asset_id, note):
# Check strong match
id = readSQL(
"SELECT id FROM dividends WHERE type=:div AND timestamp=:timestamp "
"AND account_id=:account_id AND asset_id=:asset_id AND note LIKE :dividend_description",
[(":div", DividendSubtype.Dividend), (":timestamp", timestamp),
(":account_id", account_id), (":asset_id", asset_id),
(":dividend_description", note)])
if id is not None:
return id
# Check weak match
range_start = ManipulateDate.startOfPreviousYear(
day=datetime.utcfromtimestamp(timestamp))
count = readSQL(
"SELECT COUNT(id) FROM dividends WHERE type=:div AND timestamp>=:start_range "
"AND account_id=:account_id AND asset_id=:asset_id AND note LIKE :dividend_description",
[(":div", DividendSubtype.Dividend), (":start_range", range_start),
(":account_id", account_id), (":asset_id", asset_id),
(":dividend_description", note)])
if count > 1:
logging.warning(
g_tr('StatementLoader',
"Multiple dividends match withholding tax"))
return None
id = readSQL(
"SELECT id FROM dividends WHERE type=:div AND timestamp>=:start_range "
"AND account_id=:account_id AND asset_id=:asset_id AND note LIKE :dividend_description",
[(":div", DividendSubtype.Dividend), (":start_range", range_start),
(":account_id", account_id), (":asset_id", asset_id),
(":dividend_description", note)])
return id
def test_delisting(prepare_db_fifo):
create_stocks([(4, 'A', 'A SHARE')], currency_id=2)
test_corp_actions = [(1622548800, CorporateAction.Delisting, 4, 100.0, 4,
0.0, 1.0, 'Delisting 100 A')]
create_corporate_actions(1, test_corp_actions)
test_trades = [(1619870400, 1619870400, 4, 100.0, 10.0, 0.0
) # Buy 100 A x 10.00 01/05/2021
]
create_trades(1, test_trades)
# Build ledger
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, -5
]
assert readSQL(
"SELECT * FROM ledger_totals WHERE asset_id=4 ORDER BY id DESC LIMIT 1"
) == [5, 5, 1, 1622548800, 4, 4, 1, 0.0, 0.0]
assert readSQL("SELECT * FROM ledger WHERE book_account=1") == [
6, 1622548800, 5, 1, 1, 2, 1, 1000.0, 0.0, 1000.0, 0.0, 1, 9, ''
]
def get_asset_name(self, asset_id, full=False):
if full:
return readSQL("SELECT full_name FROM assets WHERE id=:asset_id",
[(":asset_id", asset_id)])
else: # FIXME Below query may return several symbols
return readSQL(
"SELECT symbol FROM assets AS a LEFT JOIN asset_tickers AS s "
"ON s.asset_id=a.id AND s.active=1 WHERE a.id=:asset_id",
[(":asset_id", asset_id)])
def find_asset_like_name(self, partial_name, asset_type=0):
name = '%' + partial_name.replace(' ', '%') + '%'
if asset_type:
return readSQL(
"SELECT id FROM assets WHERE full_name LIKE :name AND type_id=:type",
[(":name", name), (":type", asset_type)])
else:
return readSQL("SELECT id FROM assets WHERE full_name LIKE :name",
[(":name", name)])
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 parent(self, index):
if not index.isValid():
return QModelIndex()
child_id = index.internalId()
parent_id = readSQL(f"SELECT pid FROM {self._table} WHERE id=:id",
[(":id", child_id)])
if parent_id == self.ROOT_PID:
return QModelIndex()
row = readSQL(
f"SELECT row_number FROM ("
f"SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_number, id, pid "
f"FROM {self._table} WHERE pid IN (SELECT pid FROM {self._table} WHERE id=:id)) "
f"WHERE id=:id", [(":id", parent_id)])
return self.createIndex(row - 1, 0, id=parent_id)
def _money_total(self, account_id) -> float:
money = readSQL(
"SELECT amount_acc FROM ledger_totals WHERE op_type=:op_type AND operation_id=:oid AND "
"account_id = :account_id AND book_account=:book",
[(":op_type", self._otype), (":oid", self._oid),
(":account_id", account_id), (":book", BookAccount.Money)])
debt = readSQL(
"SELECT amount_acc FROM ledger_totals WHERE op_type=:op_type AND operation_id=:oid AND "
"account_id = :account_id AND book_account=:book",
[(":op_type", self._otype), (":oid", self._oid),
(":account_id", account_id), (":book", BookAccount.Liabilities)])
if money is not None:
return money
else:
return debt
def get_account_id(self, accountNumber, accountCurrency=''):
if accountCurrency:
account_id = readSQL(
"SELECT a.id FROM accounts AS a "
"LEFT JOIN currencies AS c ON c.id=a.currency_id "
"WHERE a.number=:account_number AND c.symbol=:currency_name",
[(":account_number", accountNumber),
(":currency_name", accountCurrency)],
check_unique=True)
else:
account_id = readSQL(
"SELECT a.id FROM accounts AS a WHERE a.number=:account_number",
[(":account_number", accountNumber)],
check_unique=True)
return account_id
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 update_db_schema(self, db_path) -> JalDBError:
if QMessageBox().warning(
None,
QApplication.translate('DB', "Database format is outdated"),
QApplication.translate(
'DB',
"Do you agree to upgrade your data to newer format?"),
QMessageBox.Yes, QMessageBox.No) == QMessageBox.No:
return JalDBError(JalDBError.OutdatedDbSchema)
db = db_connection()
version = readSQL(
"SELECT value FROM settings WHERE name='SchemaVersion'")
try:
schema_version = int(version)
except ValueError:
return JalDBError(JalDBError.DbInitFailure)
for step in range(schema_version, Setup.TARGET_SCHEMA):
delta_file = db_path + Setup.UPDATES_PATH + os.sep + Setup.UPDATE_PREFIX + f"{step + 1}.sql"
logging.info(
f"Applying delta schema {step}->{step + 1} from {delta_file}")
error = self.run_sql_script(delta_file)
if error.code != JalDBError.NoError:
db.close()
return error
return JalDBError(JalDBError.NoError)
def __init__(self, operation_id=None):
super().__init__(operation_id)
self._table = "trades"
self._otype = LedgerTransaction.Trade
self._view_rows = 2
self._data = readSQL(
"SELECT t.timestamp, t.number, t.account_id, t.asset_id, t.qty, t.price AS price, "
"t.fee, t.note FROM trades AS t WHERE t.id=:oid",
[(":oid", self._oid)],
named=True)
self._label, self._label_color = (
'S', CustomColor.DarkRed) if self._data['qty'] < 0 else (
'B', CustomColor.DarkGreen)
self._timestamp = self._data['timestamp']
self._account = JalDB().get_account_name(self._data['account_id'])
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._asset = self._data['asset_id']
self._asset_symbol = JalDB().get_asset_name(self._asset)
self._asset_name = JalDB().get_asset_name(self._asset, full=True)
self._number = self._data['number']
self._qty = self._data['qty']
self._price = self._data['price']
self._fee = self._data['fee']
self._note = self._data['note']
self._broker = JalDB().get_account_bank(self._account)
def locateItem(self, item_id, use_filter=''):
row = readSQL(
f"SELECT row_number FROM (SELECT ROW_NUMBER() OVER (ORDER BY tag) AS row_number, id "
f"FROM {self._table}) WHERE id=:id", [(":id", item_id)])
if row is None:
return QModelIndex()
return self.index(row - 1, 0)
def _asset_total(self, account_id, asset_id) -> float:
return readSQL(
"SELECT amount_acc FROM ledger_totals WHERE op_type=:op_type AND operation_id=:oid AND "
"account_id = :account_id AND asset_id AND book_account=:book",
[(":op_type", self._otype), (":oid", self._oid),
(":account_id", account_id), (":asset_id", asset_id),
(":book", BookAccount.Assets)])
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 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 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 output_accrued_interest(self, actions, trade_number, share, level):
interest = readSQL("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 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.account_id=:account_id AND i.type=:interest AND i.number=:trade_number",
[(":account_id", self.account_id), (":interest", Dividend.BondInterest),
(":trade_number", trade_number),
(":base_currency", JalSettings().getValue('BaseCurrency'))], named=True)
if interest is None:
return
interest['empty'] = ''
interest['interest'] = interest['interest'] if share == 1 else share * interest['interest']
interest['interest_rub'] = abs(round(interest['interest'] * interest['rate'], 2)) if interest['rate'] else 0
if interest['interest'] < 0: # Accrued interest paid for purchase
interest['interest'] = -interest['interest']
interest['operation'] = ' ' * level * 3 + "НКД уплачен"
interest['spending_rub'] = interest['interest_rub']
interest['income_rub'] = 0.0
else: # Accrued interest received for sale
interest['operation'] = ' ' * level * 3 + "НКД получен"
interest['income_rub'] = interest['interest_rub']
interest['spending_rub'] = 0.0
interest['report_template'] = "bond_interest"
actions.append(interest)
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 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 getAccountBank(self, account_id):
bank_id = readSQL(
"SELECT organization_id FROM accounts WHERE id=:account_id",
[(":account_id", account_id)])
if bank_id == '':
raise RuntimeError("Broker isn't defined for Investment account")
return bank_id
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 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 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_spin_off(prepare_db_fifo):
# Prepare trades and corporate actions setup
test_assets = [(4, 'A', 'A SHARE'), (5, 'B', 'B SHARE')]
create_stocks(test_assets, currency_id=2)
test_corp_actions = [
(1622548800, CorporateAction.SpinOff, 4, 100.0, 5, 5.0, 1.0,
'Spin-off 5 B from 100 A'), # 01/06/2021, cost basis 0.0
(1627819200, CorporateAction.Split, 4, 104.0, 4, 13.0, 1.0,
'Split A 104 -> 13') # 01/08/2021
]
create_corporate_actions(1, test_corp_actions)
test_trades = [
(1619870400, 1619870400, 4, 100.0, 14.0,
0.0), # Buy 100 A x 14.00 01/05/2021
(1625140800, 1625140800, 4, 4.0, 13.0,
0.0), # Buy 4 A x 13.00 01/07/2021
(1629047520, 1629047520, 4, -13.0, 150.0, 0.0
) # Sell 13 A x 150.00 15/08/2021
]
create_trades(1, test_trades)
create_quotes(2, 2, [(1614600000, 70.0)])
create_quotes(4, 2, [(1617278400, 15.0)])
create_quotes(5, 2, [(1617278400, 2.0)])
create_quotes(4, 2, [(1628683200, 100.0)])
# Build ledgerye
ledger = Ledger()
ledger.rebuild(from_timestamp=0)
# Check ledger amounts before selling
assert readSQL(
"SELECT * FROM ledger WHERE asset_id=4 AND timestamp<1628615520 ORDER BY id DESC LIMIT 1"
) == [
11, 1627819200, 5, 2, 4, 4, 1, 13.0, 1452.0, 13.0, 1452.0, '', '', ''
]
assert readSQL(
"SELECT * FROM ledger WHERE asset_id=5 AND timestamp<1628615520 ORDER BY id DESC LIMIT 1"
) == [7, 1622548800, 5, 1, 4, 5, 1, 5.0, 0.0, 5.0, 0.0, '', '', '']
assert readSQL(
"SELECT * FROM ledger WHERE book_account=3 AND timestamp<1628615520 ORDER BY id DESC LIMIT 1"
) == [8, 1625140800, 3, 2, 3, 2, 1, -52.0, 0.0, 8548.0, 0.0, '', '', '']
assert readSQL(
"SELECT profit FROM deals_ext WHERE close_timestamp>=1629047520"
) == 498.0
def account_reconciliation_timestamp(self, account_id):
timestamp = readSQL(
"SELECT reconciled_on FROM accounts WHERE id=:account_id",
[(":account_id", account_id)])
if timestamp is None:
return 0
else:
return timestamp
