def dividend(self, timestamp, number, amount, description):
parts = re.match(self.DividendPattern, description, re.IGNORECASE)
if parts is None:
logging.error(
g_tr('Uralsib', "Can't parse dividend description ") +
f"'{description}'")
return
dividend_data = parts.groupdict()
asset_id = JalDB().get_asset_id('', reg_code=dividend_data['REG_CODE'])
if asset_id is None:
logging.error(
g_tr('Uralsib', "Can't find asset for dividend ") +
f"'{description}'")
return
try:
tax = float(dividend_data['TAX'])
except ValueError:
logging.error(
g_tr('Uralsib', "Failed to convert dividend tax ") +
f"'{description}'")
return
amount = amount + tax # Statement contains value after taxation while JAL stores value before tax
shortened_description = dividend_data['DESCR1'] + ' ' + dividend_data[
'DESCR2']
JalDB().add_dividend(DividendSubtype.Dividend,
timestamp,
self._account_id,
asset_id,
amount,
shortened_description,
trade_number=number,
tax=tax)
def load(self):
try:
data = pandas.read_html(self._filename, encoding='cp1251',
converters={self.Qty: convert_amount, self.Amount: convert_amount,
self.Price: convert_amount, self.Coupon: convert_amount})
except:
logging.error(g_tr('Quik', "Can't read statement file"))
return False
report_info = data[0]
deals_info = data[1]
parts = re.match(self.ClientPattern, report_info[0][2])
if parts:
account_id = self._parent.findAccountID(parts.group(1))
else:
logging.error(g_tr('Quik', "Can't get account number from the statement."))
return False
if account_id is None:
logging.error(g_tr('Quik', "Account with number ") + f"{parts.group(1)}" +
g_tr('Quik', " not found. Import cancelled."))
return False
for index, row in deals_info.iterrows():
if row[self.Type] == self.Buy:
qty = int(row[self.Qty])
elif row[self.Type] == self.Sell:
qty = -int(row[self.Qty])
elif row[self.Type][:len(self.Total)] == self.Total:
break # End of statement reached
else:
logging.warning(g_tr('Quik', "Unknown operation type ") + f"'{row[self.Type]}'")
continue
asset_id = self._parent.findAssetID(row[self.Symbol])
if asset_id is None:
logging.warning(g_tr('Quik', "Unknown asset ") + f"'{row[self.Symbol]}'")
continue
timestamp = int(
datetime.strptime(row[self.DateTime], "%d.%m.%Y %H:%M:%S").replace(tzinfo=timezone.utc).timestamp())
settlement = int(
datetime.strptime(row[self.SettleDate], "%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
number = row[self.TradeNumber]
price = row[self.Price]
amount = row[self.Amount]
lot_size = math.pow(10, round(math.log10(amount / (price * abs(qty)))))
qty = qty * lot_size
fee = float(row[self.Fee])
if self.FeeEx in row: # Broker dependent fee import
fee = fee + float(row[self.FeeEx])
else:
fee = fee + float(row[self.FeeEx1]) + float(row[self.FeeEx2]) + float(row[self.FeeEx3])
bond_interest = float(row[self.Coupon])
JalDB().add_trade(account_id, asset_id, timestamp, settlement, number, qty, price, -fee)
if bond_interest != 0:
JalDB().add_dividend(DividendSubtype.BondInterest, timestamp, account_id, asset_id,
bond_interest, "НКД", number)
return True
def loadIBStockTrade(self, trade):
qty = trade['quantity'] * trade['multiplier']
if trade['settleDateTarget'] == 0:
trade['settleDateTarget'] = trade['dateTime']
if trade['notes'] == IBKR.CancelledFlag:
JalDB().del_trade(trade['accountId'], trade['symbol'], trade['dateTime'], trade['settleDateTarget'],
trade['tradeID'], qty, trade['tradePrice'], trade['ibCommission'])
else:
JalDB().add_trade(trade['accountId'], trade['symbol'], trade['dateTime'], trade['settleDateTarget'],
trade['tradeID'], qty, trade['tradePrice'], trade['ibCommission'])
return 1
def transfer_in(self, timestamp, _number, amount, description):
currency_name = JalDB().get_asset_name(JalDB().get_account_currency(
self._account_id))
text = g_tr('Uralsib', "Deposit of ") + f"{amount:.2f} {currency_name} " + \
f"@{datetime.utcfromtimestamp(timestamp).strftime('%d.%m.%Y')}\n" + \
g_tr('Uralsib', "Select account to withdraw from:")
pair_account = self._parent.selectAccount(text, self._account_id)
if pair_account == 0:
return
JalDB().add_transfer(timestamp, pair_account, amount, self._account_id,
amount, 0, 0, description)
def transfer_out(self, timestamp, _number, amount, description):
currency_name = JalDB().get_asset_name(JalDB().get_account_currency(
self._account_id))
text = g_tr('Uralsib', "Withdrawal of ") + f"{-amount:.2f} {currency_name} " + \
f"@{datetime.utcfromtimestamp(timestamp).strftime('%d.%m.%Y')}\n" + \
g_tr('Uralsib', "Select account to deposit to:")
pair_account = self._parent.selectAccount(text, self._account_id)
if pair_account == 0:
return # amount is negative in XLS file
JalDB().add_transfer(timestamp, self._account_id, -amount,
pair_account, -amount, 0, 0, description)
def loadIBBondTrade(self, trade):
qty = trade['quantity'] / IBKR.BondPricipal
price = trade['tradePrice'] * IBKR.BondPricipal / 100.0 # Bonds are priced in percents of principal
if trade['settleDateTarget'] == 0:
trade['settleDateTarget'] = trade['dateTime']
if trade['notes'] == IBKR.CancelledFlag:
JalDB().del_trade(trade['accountId'], trade['symbol'], trade['dateTime'], trade['settleDateTarget'],
trade['tradeID'], qty, price, trade['ibCommission'])
else:
JalDB().add_trade(trade['accountId'], trade['symbol'], trade['dateTime'], trade['settleDateTarget'],
trade['tradeID'], qty, price, trade['ibCommission'])
return 1
def loadIBSecurities(self, assets):
cnt = 0
for asset in assets:
if asset['assetCategory'] == IBKR.NotSupported: # Skip not supported type of asset
continue
# IB may use '.OLD' suffix if asset is being replaced
symbol = asset['symbol'][:-len('.OLD')] if asset['symbol'].endswith('.OLD') else asset['symbol']
asset_id = JalDB().get_asset_id(symbol, isin=asset['isin'], dialog_new=False)
if asset_id is not None:
continue
asset_type = PredefinedAsset.ETF if asset['subCategory'] == "ETF" else asset['assetCategory']
JalDB().add_asset(symbol, asset['description'], asset_type, asset['isin'])
cnt += 1
logging.info(g_tr('StatementLoader', "Securities loaded: ") + f"{cnt} ({len(assets)})")
def flAsset(data, name, default_value):
if name not in data.attrib:
return default_value
if data.attrib[name] == '':
return default_value
if data.tag == 'Trade' and IBKR.flAssetType(data, 'assetCategory', None) == PredefinedAsset.Money:
currency_asset = default_value
for currency in data.attrib['symbol'].split('.'):
currency_asset = JalDB().get_asset_id(currency)
return currency_asset
isin = data.attrib['isin'] if 'isin' in data.attrib else ''
if data.tag == 'CorporateAction' and data.attrib[name].endswith('.OLD'):
return JalDB().get_asset_id(data.attrib[name][:-len('.OLD')], isin=isin)
return JalDB().get_asset_id(data.attrib[name], isin=isin)
def loadIBCurrencyTrade(self, trade):
if trade['quantity'] > 0:
from_idx = 1
to_idx = 0
to_amount = trade['quantity'] # positive value
from_amount = -trade[
'proceeds'] # we use positive value in DB while it is negative in report
elif trade['quantity'] < 0:
from_idx = 0
to_idx = 1
from_amount = -trade[
'quantity'] # we use positive value in DB while it is negative in report
to_amount = trade['proceeds'] # positive value
else:
logging.error(
g_tr('StatementLoader', "Zero quantity in cash trade: ") +
f"{trade}")
return 0
fee_idx = 2
fee_amount = -trade[
'ibCommission'] # Fee is negative in IB report but we store positive value in database
JalDB().add_transfer(trade['dateTime'], trade['accountId'][from_idx],
from_amount, trade['accountId'][to_idx],
to_amount, trade['accountId'][fee_idx],
fee_amount, trade['exchange'])
return 1
def validate(self):
if self._statement[2][0] != self.Header:
logging.error(
g_tr('Uralsib', "Can't find Uralsib Capital report header"))
return False
account_name = self._statement[2][7]
parts = re.match(self.PeriodPattern, self._statement[2][2],
re.IGNORECASE)
if parts is None:
logging.error(
g_tr('Uralsib',
"Can't parse Uralsib Capital statement period"))
return False
statement_dates = parts.groupdict()
self._report_start = int(
datetime.strptime(
statement_dates['S'],
"%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
end_day = datetime.strptime(statement_dates['E'], "%d.%m.%Y")
self._report_end = int(
datetime.combine(end_day, time(
23, 59, 59)).replace(tzinfo=timezone.utc).timestamp())
if not self._parent.checkStatementPeriod(account_name,
self._report_start):
return False
logging.info(
g_tr('Uralsib', "Loading Uralsib Capital statement for account ") +
f"{account_name}: {statement_dates['S']} - {statement_dates['E']}")
self._account_id = JalDB().get_account_id(account_name)
if self._account_id is None:
return False
return True
def __init__(self, account_id, asset_id, asset_qty, position, parent=None):
super(TaxEstimator, self).__init__(parent)
self.setupUi(self)
self.account_id = account_id
self.asset_id = asset_id
self.asset_name = JalDB().get_asset_name(self.asset_id)
self.asset_qty = asset_qty
self.dataframe = None
self.ready = False
self.setWindowTitle(
g_tr('TaxEstimator', "Tax estimation for ") + self.asset_name)
self.setWindowFlag(Qt.Tool)
self.setGeometry(position.x(), position.y(), self.width(),
self.height())
font = self.DealsView.horizontalHeader().font()
font.setBold(True)
self.DealsView.horizontalHeader().setFont(font)
self.quote = 0
self.rate = 1
self.currency_name = ''
self.prepare_tax()
if self.dataframe is None:
return
self.QuoteLbl.setText(f"{self.quote:.4f}")
self.RateLbl.setText(f"{self.rate:.4f} {self.currency_name}/RUB")
self.model = TaxEstimatorModel(self.dataframe, self.currency_name)
self.DealsView.setModel(self.model)
self.ready = True
def loadIBInterest(self, interest):
JalDB().add_cash_transaction(
interest['accountId'],
self._parent.getAccountBank(interest['accountId']),
interest['dateTime'], interest['amount'],
PredefinedCategory.Interest, interest['description'])
return 1
def validate(self):
if self._statement[4][0] != self.Header:
logging.error(g_tr('KIT', "Can't find KIT Finance report header"))
return False
parts = re.match(self.AccountPattern, self._statement[5][5],
re.IGNORECASE)
if parts is None:
logging.error(g_tr('KIT',
"Can't parse KIT Finance account number"))
return False
account_name = parts.groupdict()['ACCOUNT']
parts = re.match(self.PeriodPattern, self._statement[5][8],
re.IGNORECASE)
if parts is None:
logging.error(
g_tr('KIT', "Can't parse KIT Finance statement period"))
return False
statement_dates = parts.groupdict()
report_start = int(
datetime.strptime(
statement_dates['S'],
"%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
if not self._parent.checkStatementPeriod(account_name, report_start):
return False
logging.info(
g_tr('KIT', "Loading KIT Finance statement for account ") +
f"{account_name}: {statement_dates['S']} - {statement_dates['E']}")
self._account_id = JalDB().get_account_id(account_name)
return True
def accept(self):
self.asset_id = JalDB().add_asset(
self.SymbolEdit.text(), self.NameEdit.text(),
self.type_model.record(self.TypeCombo.currentIndex()).value("id"),
self.isinEdit.text(),
self.data_src_model.record(
self.DataSrcCombo.currentIndex()).value("id"))
super().accept()
def interest(self, timestamp, number, amount, description):
parts = re.match(self.BondInterestPattern, description, re.IGNORECASE)
if parts is None:
logging.error(
g_tr('Uralsib', "Can't parse bond interest description ") +
f"'{description}'")
return
interest_data = parts.groupdict()
asset_id = JalDB().find_asset_like_name(
interest_data['NAME'], asset_type=PredefinedAsset.Bond)
if asset_id is None:
logging.error(
g_tr('Uralsib', "Can't find asset for bond interest ") +
f"'{description}'")
return
JalDB().add_dividend(DividendSubtype.BondInterest, timestamp,
self._account_id, asset_id, amount, description,
number)
def loadIBDepositWithdraw(self, cash):
if cash['amount'] >= 0: # Deposit
text = g_tr('StatementLoader', "Deposit of ") + f"{cash['amount']:.2f} {cash['currency']} " + \
f"@{datetime.utcfromtimestamp(cash['dateTime']).strftime('%d.%m.%Y')}\n" + \
g_tr('StatementLoader', "Select account to withdraw from:")
else: # Withdrawal
text = g_tr('StatementLoader', "Withdrawal of ") + f"{-cash['amount']:.2f} {cash['currency']} " + \
f"@{datetime.utcfromtimestamp(cash['dateTime']).strftime('%d.%m.%Y')}\n" + \
g_tr('StatementLoader', "Select account to deposit to:")
pair_account = self._parent.selectAccount(text, cash['accountId'], self.last_selected_account)
if pair_account == 0:
return 0
self.last_selected_account = pair_account
if cash['amount'] >= 0:
JalDB().add_transfer(cash['dateTime'], pair_account, cash['amount'],
cash['accountId'], cash['amount'], 0, 0, cash['description'])
else:
JalDB().add_transfer(cash['dateTime'], cash['accountId'], -cash['amount'],
pair_account, -cash['amount'], 0, 0, cash['description'])
return 1
def onLanguageChanged(self, action):
language_code = action.data()
if language_code != self.currentLanguage:
JalSettings().setValue('Language',
JalDB().get_language_id(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 flAccount(data, name, default_value):
if name not in data.attrib:
return default_value
if data.tag == 'Trade' and IBKR.flAssetType(data, 'assetCategory', None) == PredefinedAsset.Money:
if 'symbol' not in data.attrib:
logging.error(g_tr('IBKR', "Can't get currencies for accounts: ") + f"{data}")
return None
if 'ibCommissionCurrency' not in data.attrib:
logging.error(g_tr('IBKR', "Can't get account currency for fee account: ") + f"{data}")
return None
currencies = data.attrib['symbol'].split('.')
currencies.append(data.attrib['ibCommissionCurrency'])
accountIds = []
for currency in currencies:
account = JalDB().get_account_id(data.attrib[name], currency)
if account is None:
return None
accountIds.append(account)
return accountIds
if 'currency' not in data.attrib:
if default_value is None:
logging.error(g_tr('IBKR', "Can't get account currency for account: ") + f"{data}")
return default_value
return JalDB().get_account_id(data.attrib[name], data.attrib['currency'])
def loadIBTaxes(self, taxes):
cnt = 0
for tax in taxes:
note = f"{tax['symbol']} ({tax['description']}) - {tax['taxDescription']}"
id = readSQL("SELECT id FROM all_operations WHERE type = :type "
"AND timestamp=:timestamp AND account_id=:account_id AND amount=:amount",
[(":timestamp", tax['date']), (":type", TransactionType.Action),
(":account_id", tax['accountId']), (":amount", tax['taxAmount'])])
if id:
logging.warning(g_tr('StatementLoader', "Tax transaction already exists ") + f"{tax}")
continue
JalDB().add_cash_transaction(tax['accountId'], self._parent.getAccountBank(tax['accountId']), tax['date'],
tax['taxAmount'], PredefinedCategory.Taxes, note)
cnt += 1
logging.info(g_tr('StatementLoader', "Taxes loaded: ") + f"{cnt} ({len(taxes)})")
def updateStockDividendAssets(self):
asset_amount = self.getAmount(BookAccount.Assets,
self.current['asset'])
self.current['price'] = self.current['price'] + asset_amount
self.current['amount'] = asset_amount
asset = JalDB().get_asset_name(self.current['asset'])
QMessageBox().information(
None, g_tr('Ledger', "Confirmation"),
g_tr('Ledger', "Stock dividend for was updated for ") + asset +
f" @{datetime.utcfromtimestamp(self.current['timestamp']).strftime('%d.%m.%Y')}\n"
+ g_tr('Ledger', "Please check that quantity is correct."),
QMessageBox.Ok)
_ = executeSQL(
"UPDATE corp_actions SET qty=:qty, qty_new=:qty_new WHERE id=:id",
[(":id", self.current['id']), (":qty", self.current['amount']),
(":qty_new", self.current['price'])])
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 load_broker_fee(self):
cnt = 0
header_found = False
for i, row in self._statement.iterrows():
if (not header_found) and (row[0]
== "Уплаченная комиссия, в том числе"):
header_found = True # Start of broker fees list
continue
if header_found:
if row[0] != "": # End of broker fee list
break
try:
fee = float(row[6])
except (ValueError, TypeError):
continue
if fee == 0:
continue
JalDB().add_cash_transaction(
self._account_id,
self._parent.getAccountBank(self._account_id),
self._report_end, fee, PredefinedCategory.Fees, row[1])
cnt += 1
logging.info(g_tr('Uralsib', "Fees loaded: ") + f"{cnt}")
def main():
sys.excepthook = exception_logger
os.environ[
'QT_MAC_WANTS_LAYER'] = '1' # Workaround for https://bugreports.qt.io/browse/QTBUG-87014
own_path = os.path.dirname(os.path.realpath(__file__)) + os.sep
error = init_and_check_db(own_path)
if error.code == LedgerInitError.EmptyDbInitialized: # If DB was just created from SQL - initialize it again
error = init_and_check_db(own_path)
app = QApplication([])
language = JalDB().get_language_code(JalSettings().getValue('Language',
default=1))
translator = QTranslator(app)
language_file = own_path + "languages" + os.sep + language + '.qm'
translator.load(language_file)
app.installTranslator(translator)
if error.code == LedgerInitError.OutdatedDbSchema:
error = update_db_schema(own_path)
if error.code == LedgerInitError.DbInitSuccess:
error = init_and_check_db(own_path)
if error.code != LedgerInitError.DbInitSuccess:
window = QMessageBox()
window.setAttribute(Qt.WA_DeleteOnClose)
window.setWindowTitle("JAL: Start-up aborted")
window.setIcon(QMessageBox.Critical)
window.setText(error.message)
window.setInformativeText(error.details)
else:
window = MainWindow(own_path, language)
window.show()
app.exec_()
app.removeTranslator(translator)
def load_futures_deals(self):
cnt = 0
columns = {
"number": "Номер сделки",
"date": "Дата сделки",
"time": "Время сделки",
"symbol": "Код контракта",
"B/S": "Вид сделки",
"price": "Цена фьючерса",
"qty": "Количество контрактов, шт.",
"amount": "Сумма",
"settlement": "Дата расчетов по сделке",
"fee_broker": "Комиссия брокера, руб.",
"fee_ex": "Комиссия ТС, руб."
}
row, headers = self.find_section_start(
"СДЕЛКИ С ФЬЮЧЕРСАМИ И ОПЦИОНАМИ", "Сделки с фьючерсами", columns)
if row < 0:
return False
while row < self._statement.shape[0]:
if self._statement[0][row] == '' and self._statement[0][row +
1] == '':
break
if self._statement[0][row].startswith("Входящая позиция по контракту") or \
self._statement[0][row].startswith("Итого по контракту") or self._statement[0][row] == '':
row += 1
continue
try:
deal_number = int(self._statement[0][row])
except ValueError:
row += 1
continue
asset_id = JalDB().get_asset_id(
self._statement[headers['symbol']][row])
if self._statement[headers['B/S']][row] == 'Покупка':
qty = self._statement[headers['qty']][row]
elif self._statement[headers['B/S']][row] == 'Продажа':
qty = -self._statement[headers['qty']][row]
else:
row += 1
logging.warning(
g_tr('Uralsib', "Unknown trade type: ") +
self._statement[headers['B/S']][row])
continue
price = self._statement[headers['price']][row]
fee = self._statement[headers['fee_broker']][
row] + self._statement[headers['fee_ex']][row]
amount = self._statement[headers['amount']][row]
if abs(abs(price * qty) - amount) >= Setup.DISP_TOLERANCE:
price = abs(amount / qty)
ts_string = self._statement[headers['date']][
row] + ' ' + self._statement[headers['time']][row]
timestamp = int(
datetime.strptime(ts_string, "%d.%m.%Y %H:%M:%S").replace(
tzinfo=timezone.utc).timestamp())
settlement = int(
datetime.strptime(
self._statement[headers['settlement']][row],
"%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
JalDB().add_trade(self._account_id, asset_id, timestamp,
settlement, deal_number, qty, price, -fee)
cnt += 1
row += 1
logging.info(g_tr('Uralsib', "Futures trades loaded: ") + f"{cnt}")
def load_stock_deals(self):
cnt = 0
columns = {
"number": "Номер сделки",
"date": "Дата сделки",
"time": "Время сделки",
"isin": "ISIN",
"B/S": "Вид сделки",
"price": "Цена одной ЦБ",
"qty": "Количество ЦБ, шт.",
"amount": "Сумма сделки",
"accrued_int": "НКД",
"settlement": "Дата поставки, плановая",
"fee_ex": "Комиссия ТС"
}
row, headers = self.find_section_start(
"СДЕЛКИ С ЦЕННЫМИ БУМАГАМИ",
"Биржевые сделки с ценными бумагами в отчетном периоде", columns)
if row < 0:
return False
asset_name = ''
while row < self._statement.shape[0]:
if self._statement[0][row] == '' and self._statement[0][row +
1] == '':
break
if self._statement[0][
row] == 'Итого по выпуску:' or self._statement[0][
row] == '':
row += 1
continue
try:
deal_number = int(self._statement[0][row])
except ValueError:
asset_name = self._statement[0][row]
row += 1
continue
asset_id = JalDB().get_asset_id(
'',
isin=self._statement[headers['isin']][row],
name=asset_name)
if self._statement[headers['B/S']][row] == 'Покупка':
qty = self._statement[headers['qty']][row]
bond_interest = -self._statement[headers['accrued_int']][row]
elif self._statement[headers['B/S']][row] == 'Продажа':
qty = -self._statement[headers['qty']][row]
bond_interest = self._statement[headers['accrued_int']][row]
else:
row += 1
logging.warning(
g_tr('Uralsib', "Unknown trade type: ") +
self._statement[headers['B/S']][row])
continue
price = self._statement[headers['price']][row]
fee = self._statement[headers['fee_ex']][row]
amount = self._statement[headers['amount']][row]
if abs(abs(price * qty) - amount) >= Setup.DISP_TOLERANCE:
price = abs(amount / qty)
ts_string = self._statement[headers['date']][
row] + ' ' + self._statement[headers['time']][row]
timestamp = int(
datetime.strptime(ts_string, "%d.%m.%Y %H:%M:%S").replace(
tzinfo=timezone.utc).timestamp())
settlement = int(
datetime.strptime(
self._statement[headers['settlement']][row],
"%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
JalDB().add_trade(self._account_id, asset_id, timestamp,
settlement, deal_number, qty, price, -fee)
if bond_interest != 0:
JalDB().add_dividend(DividendSubtype.BondInterest, timestamp,
self._account_id, asset_id, bond_interest,
"НКД", deal_number)
cnt += 1
row += 1
logging.info(g_tr('Uralsib', "Trades loaded: ") + f"{cnt}")
def MOEX_DataReader(self, asset_id, asset_code, isin, start_timestamp, end_timestamp):
engine = None
market = None
board_id = None
# Get primary board ID
url = f"http://iss.moex.com/iss/securities/{asset_code}.xml"
xml_root = xml_tree.fromstring(get_web_data(url))
for node in xml_root:
if node.tag == 'data' and node.attrib['id'] == 'boards':
boards_data = list(node)
for row in boards_data:
if row.tag == 'rows':
boards = list(row)
for board in boards:
if board.attrib['is_primary'] == '1':
engine = board.attrib['engine']
market = board.attrib['market']
board_id = board.attrib['boardid']
if (engine is None) or (market is None) or (board_id is None):
logging.warning(f"Failed to find {asset_code} at {url}")
return None
# Get security info
url = f"https://iss.moex.com/iss/engines/{engine}/"\
f"markets/{market}/boards/{board_id}/securities/{asset_code}.xml"
xml_root = xml_tree.fromstring(get_web_data(url))
for node in xml_root:
if node.tag == 'data' and node.attrib['id'] == 'securities':
sec_data = list(node)
for row in sec_data:
if row.tag == 'rows':
if len(list(row)) == 1:
asset_info = list(row)[0]
new_isin = asset_info.attrib['ISIN'] if 'ISIN' in asset_info.attrib else ''
new_reg = asset_info.attrib['REGNUMBER'] if 'REGNUMBER' in asset_info.attrib else ''
JalDB().update_isin_reg(asset_id, new_isin, new_reg)
# Get price history
date1 = datetime.utcfromtimestamp(start_timestamp).strftime('%Y-%m-%d')
date2 = datetime.utcfromtimestamp(end_timestamp).strftime('%Y-%m-%d')
url = f"http://iss.moex.com/iss/history/engines/"\
f"{engine}/markets/{market}/boards/{board_id}/securities/{asset_code}.xml?from={date1}&till={date2}"
xml_root = xml_tree.fromstring(get_web_data(url))
rows = []
for node in xml_root:
if node.tag == 'data' and node.attrib['id'] == 'history':
sections = list(node)
for section in sections:
if section.tag == "rows":
history_rows = list(section)
for row in history_rows:
if row.tag == "row":
if row.attrib['CLOSE']:
if 'FACEVALUE' in row.attrib: # Correction for bonds
price = float(row.attrib['CLOSE']) * float(row.attrib['FACEVALUE']) / 100.0
rows.append({"Date": row.attrib['TRADEDATE'], "Close": price})
else:
rows.append({"Date": row.attrib['TRADEDATE'], "Close": row.attrib['CLOSE']})
data = pd.DataFrame(rows, columns=["Date", "Close"])
data['Date'] = pd.to_datetime(data['Date'], format="%Y-%m-%d")
close = data.set_index("Date")
return close
def tax(self, timestamp, _number, amount, description):
JalDB().add_cash_transaction(
self._account_id, self._parent.getAccountBank(self._account_id),
timestamp, amount, PredefinedCategory.Taxes, description)
def setCurrency(self, currency_id):
if self._currency != currency_id:
self._currency = currency_id
self._currency_name = JalDB().get_asset_name(currency_id)
self.calculateBalances()
def load(self):
# Read first table with account details
try:
data = pandas.read_html(self._filename,
match='Account Capabilities')
except:
logging.error(g_tr('IBKR', "Can't read statement file"))
return False
if len(data) != 1:
logging.error(
g_tr('IBKR', "Can't get account details from the statement"))
return False
for i, row in data[0].iterrows():
if row[0] == 'Account':
self._account = row[1]
if self._account == '':
logging.error(
g_tr('IBKR', "Can't get account number from the statement"))
return False
# Read Trades table
try:
data = pandas.read_html(self._filename,
match='Date/Time',
attrs={'id': 'summaryDetailTable'})
except:
logging.error(
g_tr('IBKR', "Can't read Trades table from statement file"))
return False
if len(data) != 1:
logging.error(
g_tr('IBKR', "Can't get Trades table from the statement"))
return False
statement = data[0]
statement = statement[statement['Symbol'].notna()]
account_id = None
for i, row in statement.iterrows():
if row[0] == 'Forex': # We reached end of Stock trades
break
if row[0].startswith('Total') or row[0] == 'Stocks' or row[
0] == 'Symbol': # Skip totals and headers
continue
if row[0] == row[1]: # it's a currency header - match account
account_id = self._parent.findAccountID(self._account, row[0])
continue
if account_id is None: # no reason to check further if we don't have valid account here
continue
asset_id = self._parent.findAssetID(row[0])
if asset_id is None:
logging.warning(g_tr('IBKR', "Unknown asset ") + f"'{row[0]}'")
continue
timestamp = int(
datetime.strptime(row[1], "%Y-%m-%d, %H:%M:%S").replace(
tzinfo=timezone.utc).timestamp())
settlement = int(
datetime.strptime(
row[1][:10],
"%Y-%m-%d").replace(tzinfo=timezone.utc).timestamp())
number = f"{i}"
qty = float(row[2])
price = float(row[3])
fee = float(row[6])
JalDB().add_trade(account_id, asset_id, timestamp, settlement,
number, qty, price, fee)
logging.info(g_tr('IBKR', "Load IBKR Activity statement completed"))
return True
def loadIBFee(self, fee):
JalDB().add_cash_transaction(
fee['accountId'], self._parent.getAccountBank(fee['accountId']),
fee['dateTime'], fee['amount'], PredefinedCategory.Fees,
fee['description'])
return 1
