def load_cash_operations(self, cash_operations):
cnt = 0
operations = {
'Комиссия Брокера / ':
None, # These operations are included of trade's data
'Удержан налог на купонный доход':
None, # Tax information is included into interest payment data
'Поставлены на торги средства клиента': self.transfer_in,
'Выплата дохода': self.asset_payment
}
for cash in cash_operations:
operation = [
operation for operation in operations
if cash['description'].startswith(operation)
]
if len(operation) != 1:
raise Statement_ImportError(
self.tr("Operation not supported: ") + cash['description'])
for operation in operations:
if cash['description'].startswith(operation):
if operations[operation] is not None:
account_id = self.account_by_currency(cash['currency'])
if account_id == 0:
raise Statement_ImportError(
self.
tr("Can't find account for cash operation: ") +
f"{cash}")
operations[operation](cash['timestamp'], account_id,
cash['amount'],
cash['description'])
cnt += 1
logging.info(self.tr("Cash operations loaded: ") + f"{cnt}")
def _find_account_id(self, number, currency):
try:
code = self.currency_substitutions[currency]
except KeyError:
code = currency
match = [x for x in self._data[FOF.ASSETS] if
'symbol' in x and x['symbol'] == code and x['type'] == FOF.ASSET_MONEY]
if match:
if len(match) == 1:
currency_id = match[0]['id']
else:
raise Statement_ImportError(self.tr("Multiple currency found: ") + f"{currency}")
else:
currency_id = max([0] + [x['id'] for x in self._data[FOF.ASSETS]]) + 1
new_currency = {"id": currency_id, "symbol": code, 'name': '', 'type': FOF.ASSET_MONEY}
self._data[FOF.ASSETS].append(new_currency)
match = [x for x in self._data[FOF.ACCOUNTS] if
'number' in x and x['number'] == number and x['currency'] == currency_id]
if match:
if len(match) == 1:
return match[0]['id']
else:
raise Statement_ImportError(self.tr("Multiple accounts found: ") + f"{number}/{currency}")
new_id = max([0] + [x['id'] for x in self._data[FOF.ACCOUNTS]]) + 1
new_account = {"id": new_id, "number": number, 'currency': currency_id}
self._data[FOF.ACCOUNTS].append(new_account)
return new_id
def interest_payment(self, timestamp, account_id, amount, description):
intrest_pattern = r"^Выплата дохода клиент (?P<account>\w+) \((?P<type>\w+) (?P<number>\d+) (?P<symbol>.*)\) налог.* (?P<tax>\d+\.\d+) рубл.*$"
parts = re.match(intrest_pattern, description, re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Can't parse Interest description ") +
f"'{description}'")
interest = parts.groupdict()
if len(interest) != intrest_pattern.count(
"(?P<"): # check expected number of matches
raise Statement_ImportError(
self.tr("Interest description miss some data ") +
f"'{description}'")
asset_id = self.asset_id({'symbol': interest['symbol']})
if asset_id is None:
raise Statement_ImportError(
self.tr("Can't find asset for bond interest ") +
f"'{interest['symbol']}'")
tax = float(
interest['tax']
) # it has '\d+\.\d+' regex pattern so here shouldn't be an exception
note = f"{interest['type']} {interest['number']}"
new_id = max([0] + [x['id']
for x in self._data[FOF.ASSET_PAYMENTS]]) + 1
payment = {
"id": new_id,
"type": FOF.PAYMENT_INTEREST,
"account": account_id,
"timestamp": timestamp,
"asset": asset_id,
"amount": amount,
"tax": tax,
"description": note
}
self._data[FOF.ASSET_PAYMENTS].append(payment)
def load_symbol_change(self, action, parts_b) -> int:
SymbolChangePattern = r"^(?P<symbol_old>\w+)\((?P<isin_old>\w+)\) +CUSIP\/ISIN CHANGE TO +\((?P<isin_new>\w+)\) +\((?P<symbol>\w+), (?P<name>.*), (?P<id>\w+)\)$"
parts = re.match(SymbolChangePattern, action['description'], re.IGNORECASE)
if parts is None:
raise Statement_ImportError(self.tr("Can't parse Symbol Change description ") + f"'{action}'")
isin_change = parts.groupdict()
if len(isin_change) != SymbolChangePattern.count("(?P<"): # check that expected number of groups was matched
raise Statement_ImportError(self.tr("Spin-off description miss some data ") + f"'{action}'")
description_b = action['description'][:parts.span('symbol')[0]] + isin_change['symbol_old'] + ".OLD, "
asset_b = self.locate_asset(isin_change['symbol_old'], isin_change['isin_old'])
paired_record = list(filter(
lambda pair: pair['asset'] == asset_b
and pair['description'].startswith(description_b)
and pair['type'] == action['type']
and pair['timestamp'] == action['timestamp'], parts_b))
if len(paired_record) != 1:
raise Statement_ImportError(self.tr("Can't find paired record for: ") + f"{action}")
action['id'] = max([0] + [x['id'] for x in self._data[FOF.CORP_ACTIONS]]) + 1
action['cost_basis'] = 1.0
action['asset'] = [paired_record[0]['asset'], action['asset']]
action['quantity'] = [-paired_record[0]['quantity'], action['quantity']]
self.drop_extra_fields(action, ["proceeds", "code", "asset_type", "jal_processed"])
self._data[FOF.CORP_ACTIONS].append(action)
paired_record[0]['jal_processed'] = True
return 2
def validate_file_header_attributes(self, attributes):
if 'title' not in attributes:
raise Statement_ImportError(
self.tr("Open broker report title not found"))
if not attributes['title'].startswith("Отчет АО «Открытие Брокер»"):
raise Statement_ImportError(
self.tr("Unexpected Open broker report header: ") +
f"{attributes['title']}")
def dividend(self, timestamp, number, account_id, amount, description):
DividendPattern = r"> (?P<DESCR1>.*) \((?P<REG_NUMBER>.*)\)((?P<DESCR2> .*)?(?P<TAX_TEXT> налог (в размере (?P<TAX>\d+\.\d\d) )?.*удержан))?\. НДС не облагается\."
ISINPattern = r"[A-Z]{2}.{9}\d"
parts = re.match(DividendPattern, description, re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Can't parse dividend description ") +
f"'{description}'")
dividend_data = parts.groupdict()
isin_match = re.match(ISINPattern, dividend_data['REG_NUMBER'])
currency_code = self.currency_id('RUB')
if isin_match:
asset_id = self.asset_id({
'isin': dividend_data['REG_NUMBER'],
'currency': currency_code,
'search_online': "MOEX"
})
else:
asset_id = self.asset_id({
'reg_number': dividend_data['REG_NUMBER'],
'currency': currency_code,
'search_online': "MOEX"
})
if dividend_data['DESCR2']:
short_description = dividend_data['DESCR1'] + ' ' + dividend_data[
'DESCR2'].strip()
else:
short_description = dividend_data['DESCR1']
if dividend_data['TAX']:
try:
tax = float(dividend_data['TAX'])
except ValueError:
raise Statement_ImportError(
self.tr("Failed to convert dividend tax ") +
f"'{description}'")
else:
tax = 0
if dividend_data['TAX_TEXT']:
short_description += '; ' + dividend_data['TAX_TEXT'].strip()
amount = amount + tax # Statement contains value after taxation while JAL stores value before tax
new_id = max([0] + [x['id']
for x in self._data[FOF.ASSET_PAYMENTS]]) + 1
payment = {
"id": new_id,
"type": FOF.PAYMENT_DIVIDEND,
"account": account_id,
"timestamp": timestamp,
"number": number,
"asset": asset_id,
"amount": amount,
"tax": tax,
"description": short_description
}
self._data[FOF.ASSET_PAYMENTS].append(payment)
def dividend(self, timestamp, number, account_id, amount, description):
DividendPattern = r"> (?P<DESCR1>.*) \((?P<REG_CODE>.*)\)((?P<DESCR2> .*)? налог в размере (?P<TAX>\d+\.\d\d) удержан)?\. НДС не облагается\."
ISINPattern = r"[A-Z]{2}.{9}\d"
parts = re.match(DividendPattern, description, re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Can't parse dividend description ") +
f"'{description}'")
dividend_data = parts.groupdict()
isin_match = re.match(ISINPattern, dividend_data['REG_CODE'])
if isin_match:
asset_id = self._find_asset_id(isin=dividend_data['REG_CODE'])
if not asset_id:
asset_id = self._add_asset(isin=dividend_data['REG_CODE'],
reg_code='')
else:
asset_id = self._find_asset_id(reg_code=dividend_data['REG_CODE'])
if not asset_id:
asset_id = self._add_asset(isin='',
reg_code=dividend_data['REG_CODE'])
if dividend_data['TAX']:
try:
tax = float(dividend_data['TAX'])
except ValueError:
raise Statement_ImportError(
self.tr("Failed to convert dividend tax ") +
f"'{description}'")
else:
tax = 0
amount = amount + tax # Statement contains value after taxation while JAL stores value before tax
if dividend_data['DESCR2']:
short_description = dividend_data['DESCR1'] + ' ' + dividend_data[
'DESCR2'].strip()
else:
short_description = dividend_data['DESCR1']
new_id = max([0] + [x['id']
for x in self._data[FOF.ASSET_PAYMENTS]]) + 1
payment = {
"id": new_id,
"type": FOF.PAYMENT_DIVIDEND,
"account": account_id,
"timestamp": timestamp,
"number": number,
"asset": asset_id,
"amount": amount,
"tax": tax,
"description": short_description
}
self._data[FOF.ASSET_PAYMENTS].append(payment)
def interest(self, timestamp, number, account_id, amount, description):
BondInterestPattern = r"Погашение купона №( -?\d+)? (?P<NAME>.*)"
parts = re.match(BondInterestPattern, description, re.IGNORECASE)
if parts is None:
logging.error(
self.tr("Can't parse bond interest description ") +
f"'{description}'")
return
interest_data = parts.groupdict()
asset_id = self._find_asset_id(symbol=interest_data['NAME'])
if asset_id is None:
raise Statement_ImportError(
self.tr("Can't find asset for bond interest ") +
f"'{description}'")
new_id = max([0] + [x['id']
for x in self._data[FOF.ASSET_PAYMENTS]]) + 1
payment = {
"id": new_id,
"type": FOF.PAYMENT_INTEREST,
"account": account_id,
"timestamp": timestamp,
"number": number,
"asset": asset_id,
"amount": amount,
"description": description
}
self._data[FOF.ASSET_PAYMENTS].append(payment)
def load(self, filename: str) -> None:
self._data = {
FOF.PERIOD: [None, None],
FOF.ACCOUNTS: [],
FOF.ASSETS: [],
FOF.TRADES: [],
FOF.TRANSFERS: [],
FOF.CORP_ACTIONS: [],
FOF.ASSET_PAYMENTS: [],
FOF.INCOME_SPENDING: []
}
if filename.endswith(".zip"):
with ZipFile(filename) as zip_file:
contents = zip_file.namelist()
if len(contents) != 1:
raise Statement_ImportError(self.tr("Archive contains multiple files"))
with zip_file.open(contents[0]) as r_file:
self._statement = pandas.read_excel(io=r_file.read(), header=None, na_filter=False)
else:
self._statement = pandas.read_excel(filename, header=None, na_filter=False)
self._validate()
self._load_currencies()
self._load_accounts()
self._load_money()
self._load_assets()
self._load_deals()
self._load_cash_transactions()
logging.info(self.tr("Statement loaded successfully: ") + f"{self.StatementName}")
def bond_repayment(self, timestamp, account_id, amount, description):
repayment_pattern = r"^Выплата дохода клиент (?P<account>\w+) \((?P<type>\w+) (?P<asset>.*)\) налог не удерживается$"
parts = re.match(repayment_pattern, description, re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Can't parse Bond Mature description ") +
f"'{description}'")
repayment = parts.groupdict()
if len(repayment) != repayment_pattern.count(
"(?P<"): # check expected number of matches
raise Statement_ImportError(
self.tr("Bond repayment description miss some data ") +
f"'{description}'")
match = [
x for x in self.asset_withdrawal
if (x['symbol'] == repayment['asset'] or x['alt_symbol'] ==
repayment['asset']) and x['timestamp'] == timestamp
]
if not match:
raise Statement_ImportError(
self.tr("Can't find asset cancellation record for ") +
f"'{description}'")
if len(match) != 1:
raise Statement_ImportError(
self.tr("Multiple asset cancellation match for ") +
f"'{description}'")
asset_cancel = match[0]
number = datetime.utcfromtimestamp(timestamp).strftime(
'%Y%m%d') + f"-{asset_cancel['id']}"
qty = asset_cancel['quantity']
price = abs(amount / qty) # Price is always positive
new_id = max([0] + [x['id'] for x in self._data[FOF.TRADES]]) + 1
trade = {
"id": new_id,
"number": number,
"timestamp": timestamp,
"settlement": timestamp,
"account": account_id,
"asset": asset_cancel['asset'],
"quantity": qty,
"price": price,
"fee": 0.0,
"note": asset_cancel['note']
}
self._data[FOF.TRADES].append(trade)
def _get_statement_period(self):
parts = re.match(self.PeriodPattern[2],
self._statement[self.PeriodPattern[0]][self.PeriodPattern[1]], re.IGNORECASE)
if parts is None:
raise Statement_ImportError(self.tr("Can't read report period"))
statement_dates = parts.groupdict()
start_day = int(datetime.strptime(statement_dates['S'], "%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
end_day = int(datetime.strptime(statement_dates['E'], "%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
self._data[FOF.PERIOD] = [start_day, self._end_of_date(end_day)]
def asset_payment(self, timestamp, account_id, amount, description):
payment_operations = {
'НКД': self.interest_payment,
'Погашение': self.bond_repayment
}
payment_pattern = r"^.*\((?P<type>\w+).*\).*$"
parts = re.match(payment_pattern, description, re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Unknown payment description: ") + f"'{description}'")
try:
payment_operations[parts.groupdict()['type']](timestamp,
account_id, amount,
description)
except KeyError:
raise Statement_ImportError(
self.tr("Unknown payment type: ") +
f"'{parts.groupdict()['type']}'")
def load_trades(self, trades):
cnt = 0
trade_base = max([0] + [x['id'] for x in self._data[FOF.TRADES]]) + 1
for i, trade in enumerate(sorted(trades,
key=lambda x: x['timestamp'])):
trade['id'] = trade_base + i
trade['account'] = self.account_by_currency(trade['currency'])
if trade['account'] == 0:
raise Statement_ImportError(
self.tr("Can't find account for trade: ") + f"{trade}")
if trade['quantity_buy'] < 0 and trade['quantity_sell'] < 0:
raise Statement_ImportError(
self.tr("Can't determine trade type/quantity: ") +
f"{trade}")
if trade['quantity_sell'] < 0:
trade['quantity'] = trade['quantity_buy']
trade['accrued_interest'] = -trade['accrued_interest']
else:
trade['quantity'] = -trade['quantity_sell']
amount = trade['proceeds'] + trade['accrued_interest']
if abs(abs(trade['price'] * trade['quantity']) -
amount) >= Setup.DISP_TOLERANCE:
trade['price'] = abs(amount / trade['quantity'])
if abs(trade['accrued_interest']) > 0:
new_id = max([0] +
[x['id']
for x in self._data[FOF.ASSET_PAYMENTS]]) + 1
payment = {
"id": new_id,
"type": FOF.PAYMENT_INTEREST,
"account": trade['account'],
"timestamp": trade['timestamp'],
"number": trade['number'],
"asset": trade['asset'],
"amount": trade['accrued_interest'],
"description": "НКД"
}
self._data[FOF.ASSET_PAYMENTS].append(payment)
self.drop_extra_fields(trade, [
"currency", "proceeds", "quantity_buy", "quantity_sell",
"accrued_interest"
])
self._data[FOF.TRADES].append(trade)
cnt += 1
def load_cash_transactions(self):
cnt = 0
columns = {
"number": "№ операции",
"date": "Дата",
"type": "Тип операции",
"amount": "Сумма",
"currency": "Валюта",
"description": "Комментарий"
}
operations = {
'Ввод ДС': self.transfer_in,
'Вывод ДС': self.transfer_out,
'Перевод ДС': self.transfer,
'Налог': self.tax,
'Доход по финансовым инструментам': self.dividend,
'Погашение купона': self.interest,
'Погашение номинала': self.bond_repayment,
'Списано по сделке':
None, # These operations are results of trades
'Получено по сделке': None,
"Вариационная маржа":
None, # These are non-trade operations for derivatives
"Заблокировано средств ГО": None
}
row, headers = self.find_section_start(
"ДВИЖЕНИЕ ДЕНЕЖНЫХ СРЕДСТВ ЗА ОТЧЕТНЫЙ ПЕРИОД", columns)
if row < 0:
return False
while row < self._statement.shape[0]:
if self._statement[self.HeaderCol][row] == '' and self._statement[
self.HeaderCol][row + 1] == '':
break
operation = self._statement[headers['type']][row]
if operation not in operations:
raise Statement_ImportError(
self.tr("Unsuppported cash transaction ") +
f"'{operation}'")
number = self._statement[headers['number']][row]
timestamp = int(
datetime.strptime(
self._statement[headers['date']][row],
"%d.%m.%Y").replace(tzinfo=timezone.utc).timestamp())
amount = self._statement[headers['amount']][row]
description = self._statement[headers['description']][row]
account_id = self._find_account_id(
self._account_number,
self._statement[headers['currency']][row])
if operations[operation] is not None:
operations[operation](timestamp, number, account_id, amount,
description)
cnt += 1
row += 1
logging.info(self.tr("Cash operations loaded: ") + f"{cnt}")
def load_split(self, action, parts_b) -> int:
SplitPattern = r"^(?P<symbol_old>\w+)\((?P<isin_old>\w+)\) +SPLIT +(?P<X>\d+) +FOR +(?P<Y>\d+) +\((?P<symbol>\w+), (?P<name>.*), (?P<id>\w+)\)$"
parts = re.match(SplitPattern, action['description'], re.IGNORECASE)
if parts is None:
raise Statement_ImportError(self.tr("Can't parse Split description ") + f"'{action}'")
split = parts.groupdict()
if len(split) != SplitPattern.count("(?P<"): # check that expected number of groups was matched
raise Statement_ImportError(self.tr("Split description miss some data ") + f"'{action}'")
if parts['isin_old'] == parts['id']: # Simple split without ISIN change
qty_delta = action['quantity']
if qty_delta >= 0: # Forward split (X>Y)
qty_old = qty_delta / (int(split['X']) - int(split['Y']))
qty_new = int(split['X']) * qty_delta / (int(split['X']) - int(split['Y']))
else: # Reverse split (X<Y)
qty_new = qty_delta / (int(split['X']) - int(split['Y']))
qty_old = int(split['Y']) * qty_delta / (int(split['X']) - int(split['Y']))
action['id'] = max([0] + [x['id'] for x in self._data[FOF.CORP_ACTIONS]]) + 1
action['cost_basis'] = 1.0
action['asset'] = [action['asset'], action['asset']]
action['quantity'] = [qty_old, qty_new]
self.drop_extra_fields(action, ["code", "asset_type", "jal_processed"])
self._data[FOF.CORP_ACTIONS].append(action)
return 1
else: # Split together with ISIN change and there should be 2nd record available
description_b = action['description'][:parts.span('symbol')[0]] + split['symbol_old']
asset_b = self.locate_asset(split['symbol_old'], split['isin_old'])
paired_record = list(filter(
lambda pair: pair['asset'] == asset_b
and (pair['description'].startswith(description_b + ", ")
or pair['description'].startswith(description_b + ".OLD, "))
and pair['type'] == action['type']
and pair['timestamp'] == action['timestamp'], parts_b))
if len(paired_record) != 1:
raise Statement_ImportError(self.tr("Can't find paired record for: ") + f"{action}")
action['id'] = max([0] + [x['id'] for x in self._data[FOF.CORP_ACTIONS]]) + 1
action['cost_basis'] = 1.0
action['asset'] = [paired_record[0]['asset'], action['asset']]
action['quantity'] = [-paired_record[0]['quantity'], action['quantity']]
self.drop_extra_fields(action, ["proceeds", "code", "asset_type", "jal_processed"])
self._data[FOF.CORP_ACTIONS].append(action)
paired_record[0]['jal_processed'] = True
return 2
def parse_attributes(self, section_tag, element):
tag_dictionary = {}
if self._sections[section_tag]['level']: # Skip extra lines (SUMMARY, etc)
if self.attr_string(element, 'levelOfDetail', '') != self._sections[section_tag]['level']:
return None
for attr_name, key_name, attr_type, attr_default in self._sections[section_tag]['values']:
attr_value = self.attr_loader[attr_type](element, attr_name, attr_default)
if attr_value is None:
raise Statement_ImportError(self.tr("Failed to load attribute: ") + f"{attr_name} / {element.attrib}")
tag_dictionary[key_name] = attr_value
return tag_dictionary
def _add_asset(self, isin, reg_code, symbol=''):
if self._find_asset_id(symbol, isin, reg_code) != 0:
raise Statement_ImportError(
self.tr("Attempt to recreate existing asset: ") + f"{isin}/{reg_code}")
asset_id = JalDB().get_asset_id('', isin=isin, reg_code=reg_code, dialog_new=False)
if asset_id is None:
asset = QuoteDownloader.MOEX_info(symbol=symbol, isin=isin, regnumber=reg_code)
if asset:
asset['id'] = asset_id = max([0] + [x['id'] for x in self._data[FOF.ASSETS]]) + 1
asset['exchange'] = "MOEX"
asset['type'] = FOF.convert_predefined_asset_type(asset['type'])
else:
raise Statement_ImportError(self.tr("Can't import asset: ") + f"{isin}/{reg_code}")
else:
asset = {"id": -asset_id, "symbol": JalDB().get_asset_name(asset_id),
"type": FOF.convert_predefined_asset_type(JalDB().get_asset_type(asset_id)),
'name': '', "isin": isin, "reg_code": reg_code}
asset_id = -asset_id
self._data[FOF.ASSETS].append(asset)
return asset_id
def load_spinoff(self, action, _parts_b) -> int:
SpinOffPattern = r"^(?P<symbol_old>\w+)\((?P<isin_old>\w+)\) +SPINOFF +(?P<X>\d+) +FOR +(?P<Y>\d+) +\((?P<symbol>\w+), (?P<name>.*), (?P<id>\w+)\)$"
parts = re.match(SpinOffPattern, action['description'], re.IGNORECASE)
if parts is None:
raise Statement_ImportError(self.tr("Can't parse Spin-off description ") + f"'{action}'")
spinoff = parts.groupdict()
if len(spinoff) != SpinOffPattern.count("(?P<"): # check that expected number of groups was matched
raise Statement_ImportError(self.tr("Spin-off description miss some data ") + f"'{action}'")
asset_old = self.locate_asset(spinoff['symbol_old'], spinoff['isin_old'])
if not asset_old:
raise Statement_ImportError(self.tr("Spin-off initial asset not found ") + f"'{action}'")
qty_old = int(spinoff['Y']) * action['quantity'] / int(spinoff['X'])
action['id'] = max([0] + [x['id'] for x in self._data[FOF.CORP_ACTIONS]]) + 1
action['cost_basis'] = 0.0
action['asset'] = [asset_old, action['asset']]
action['quantity'] = [qty_old, action['quantity']]
self.drop_extra_fields(action, ["proceeds", "code", "asset_type", "jal_processed"])
self._data[FOF.CORP_ACTIONS].append(action)
return 1
def transfer(self, timestamp, number, account_id, amount, description):
TransferPattern = r"^Перевод ДС на с\/с (?P<account_to>[\w|\/]+) с с\/с (?P<account_from>[\w|\/]+)\..*$"
if amount < 0: # there should be positive paired record
return
parts = re.match(TransferPattern, description, re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Can't parse transfer description ") +
f"'{description}'")
transfer = parts.groupdict()
if len(transfer) != TransferPattern.count(
"(?P<"): # check that expected number of groups was matched
raise Statement_ImportError(
self.tr("Transfer description miss some data ") +
f"'{description}'")
if transfer['account_from'] == transfer[
'account_to']: # It is a technical record for incoming transfer
return
currency_id = [
x for x in self._data[FOF.ACCOUNTS] if x["id"] == account_id
][0]['currency']
currency_name = [
x for x in self._data[FOF.ASSETS] if x["id"] == currency_id
][0]['symbol']
account_from = self._find_account_id(transfer['account_from'],
currency_name)
account_to = self._find_account_id(transfer['account_to'],
currency_name)
new_id = max([0] + [x['id'] for x in self._data[FOF.TRANSFERS]]) + 1
transfer = {
"id": new_id,
"account": [account_from, account_to, 0],
"number": number,
"asset": [currency_id, currency_id],
"timestamp": timestamp,
"withdrawal": amount,
"deposit": amount,
"fee": 0.0,
"description": description
}
self._data[FOF.TRANSFERS].append(transfer)
def load_asset_operations(self, asset_operations):
# Asset name is stored as alternative symbol and in self.asset_withdrawal[]
bond_repayment_pattern = r"^.*Снятие ЦБ с учета\. Погашение облигаций - (?P<asset_name>.*)$"
for operation in asset_operations:
if "Снятие ЦБ с учета. Погашение облигаций" not in operation[
'description']:
raise Statement_ImportError(
self.tr("Unknown non-trade operation: ") +
operation['description'])
parts = re.match(bond_repayment_pattern, operation['description'],
re.IGNORECASE)
if parts is None:
raise Statement_ImportError(
self.tr("Can't parse bond repayment description ") +
f"'{operation['description']}'")
repayment_note = parts.groupdict()
if len(repayment_note) != bond_repayment_pattern.count(
"(?P<"): # check expected number of matches
raise Statement_ImportError(
self.tr("Can't detect bond name from description ") +
f"'{repayment_note}'")
ticker = self._find_in_list(self._data[FOF.SYMBOLS], 'asset',
operation['asset'])
if ticker['symbol'] != repayment_note[
'asset_name']: # Store alternative depositary name
ticker = ticker.copy()
ticker['id'] = max([0] +
[x['id']
for x in self._data[FOF.SYMBOLS]]) + 1
ticker['symbol'] = repayment_note['asset_name']
self._data[FOF.SYMBOLS].append(ticker)
new_id = max([0] + [x['id'] for x in self.asset_withdrawal]) + 1
record = {
"id": new_id,
"timestamp": operation['timestamp'],
"asset": operation['asset'],
"symbol": ticker['symbol'],
"quantity": operation['quantity'],
"note": operation['description']
}
self.asset_withdrawal.append(record)
def load_merger(self, action, parts_b) -> int:
MergerPatterns = [
r"^(?P<symbol_old>\w+)(.OLD)?\((?P<isin_old>\w+)\) +MERGED\(\w+\) +WITH +(?P<isin_new>\w+) +(?P<X>\d+) +FOR +(?P<Y>\d+) +\((?P<symbol>\w+)(.OLD)?, (?P<name>.*), (?P<id>\w+)\)$",
r"^(?P<symbol_old>\w+)(.OLD)?\((?P<isin_old>\w+)\) +CASH and STOCK MERGER +\(\w+\) +(?P<isin_new>\w+) +(?P<X>\d+) +FOR +(?P<Y>\d+) +AND +(?P<currency>\w+) +(\d+(\.\d+)?) +\((?P<symbol>\w+)(.OLD)?, (?P<name>.*), (?P<id>\w+)\)$"
]
parts = None
pattern_id = -1
for pattern_id, pattern in enumerate(MergerPatterns):
parts = re.match(pattern, action['description'], re.IGNORECASE)
if parts:
break
if parts is None:
raise Statement_ImportError(self.tr("Can't parse Merger description ") + f"'{action}'")
merger_a = parts.groupdict()
if len(merger_a) != MergerPatterns[pattern_id].count("(?P<"): # check expected number of matches
raise Statement_ImportError(self.tr("Merger description miss some data ") + f"'{action}'")
description_b = action['description'][:parts.span('symbol')[0]] + merger_a['symbol_old'] + ", "
asset_b = self.locate_asset(merger_a['symbol_old'], merger_a['isin_old'])
paired_record = list(filter(
lambda pair: pair['asset'] == asset_b
and pair['description'].startswith(description_b)
and pair['type'] == action['type']
and pair['timestamp'] == action['timestamp'], parts_b))
if len(paired_record) != 1:
raise Statement_ImportError(self.tr("Can't find paired record for ") + f"{action}")
if pattern_id == 1:
self.add_merger_payment(action['timestamp'], action['account'], paired_record[0]['proceeds'],
parts['currency'], action['description'])
action['id'] = max([0] + [x['id'] for x in self._data[FOF.CORP_ACTIONS]]) + 1
action['cost_basis'] = 1.0
action['asset'] = [paired_record[0]['asset'], action['asset']]
action['quantity'] = [-paired_record[0]['quantity'], action['quantity']]
self.drop_extra_fields(action, ["proceeds", "code", "asset_type", "jal_processed"])
self._data[FOF.CORP_ACTIONS].append(action)
paired_record[0]['jal_processed'] = True
return 2
def bond_repayment(self, timestamp, _number, account_id, amount,
description):
BondRepaymentPattern = r"Погашение номинала (?P<NAME>.*)"
parts = re.match(BondRepaymentPattern, description, re.IGNORECASE)
if parts is None:
logging.error(
self.tr("Can't parse bond repayment description ") +
f"'{description}'")
return
interest_data = parts.groupdict()
asset_id = self._find_asset_id(symbol=interest_data['NAME'])
if not asset_id:
raise Statement_ImportError(
self.tr("Can't find asset for bond repayment ") +
f"'{description}'")
match = [
x for x in self.asset_withdrawal
if x['asset'] == asset_id and x['timestamp'] == timestamp
]
if not match:
logging.error(
self.tr("Can't find asset cancellation record for ") +
f"'{description}'")
return
if len(match) != 1:
logging.error(
self.tr("Multiple asset cancellation match for ") +
f"'{description}'")
return
asset_cancel = match[0]
qty = asset_cancel['quantity']
price = abs(amount / qty) # Price is always positive
note = description + ", " + asset_cancel['note']
new_id = max([0] + [x['id'] for x in self._data[FOF.TRADES]]) + 1
trade = {
"id": new_id,
"number": asset_cancel['number'],
"timestamp": timestamp,
"settlement": timestamp,
"account": account_id,
"asset": asset_id,
"quantity": qty,
"price": price,
"fee": 0.0,
"note": note
}
self._data[FOF.TRADES].append(trade)
def attr_timestamp(xml_element, attr_name, default_value):
if attr_name not in xml_element.attrib:
return default_value
time_str = xml_element.attrib[attr_name]
try:
if len(time_str) == 19: # YYYY-MM-DDTHH:MM:SS
return int(datetime.strptime(time_str, "%Y-%m-%dT%H:%M:%S").replace(tzinfo=timezone.utc).timestamp())
if len(time_str) == 15: # YYYYMMDD;HHMMSS
return int(datetime.strptime(time_str, "%Y%m%d;%H%M%S").replace(tzinfo=timezone.utc).timestamp())
elif len(time_str) == 8: # YYYYMMDD
return int(datetime.strptime(time_str, "%Y%m%d").replace(tzinfo=timezone.utc).timestamp())
else:
return default_value
except ValueError:
raise Statement_ImportError(QApplication.translate("StatementXML", "Unsupported date/time format: ")
+ f"{xml_element.attrib[attr_name]}")
def _load_cash_transactions(self):
cnt = 0
columns = {
"date": "Дата",
"operation": "Тип операции",
"amount": "Сумма",
"currency": " Валюта",
"reason": "Основание",
"note": "Примечание",
"market": "Секция"
}
operations = {
'Внесение д/с в торг': self.transfer_in,
'Вывод дс': self.transfer_out,
'Ком бр аб плата спот': self.fee,
'Комиссия НРД': self.fee,
'Delta-long': self.fee,
'% по займу ЦБ': self.interest,
'Налог с див.доход ФЛ': self.tax
}
row, headers = self.find_section_start(
"Движение денежных средств по неторговым операциям", columns)
if row < 0:
return False
while row < self._statement.shape[0]:
if self._statement[0][row] == '':
break
operation = self._statement[headers['operation']][row]
if operation not in operations: # not supported type of operation
raise Statement_ImportError(
self.tr("Unsuppported cash transaction ") +
f"'{operation}'")
timestamp = int(self._statement[headers['date']][row].replace(
tzinfo=timezone.utc).timestamp())
account_id = self._find_account_id(
self._account_number,
self._statement[headers['currency']][row])
amount = self._statement[headers['amount']][row]
reason = self._statement[headers['reason']][row]
description = self._statement[headers['note']][row]
operations[operation](timestamp, account_id, amount, reason,
description)
cnt += 1
row += 1
logging.info(self.tr("Cash operations loaded: ") + f"{cnt}")
def load_corporate_actions(self, actions):
action_loaders = {
FOF.ACTION_MERGER: self.load_merger,
FOF.ACTION_SPINOFF: self.load_spinoff,
FOF.ACTION_SYMBOL_CHANGE: self.load_symbol_change,
FOF.ACTION_STOCK_DIVIDEND: self.load_stock_dividend,
FOF.ACTION_SPLIT: self.load_split
}
cnt = 0
if any(action['code'] == StatementIBKR.CancelledFlag for action in actions):
actions = [action for action in actions if action['code'] != StatementIBKR.CancelledFlag]
logging.warning(self.tr("Statement contains cancelled corporate actions. They were skipped."))
if any(action['asset_type'] != FOF.ASSET_STOCK for action in actions):
actions = [action for action in actions if action['asset_type'] == FOF.ASSET_STOCK]
logging.warning(self.tr("Corporate actions are supported for stocks only, other assets were skipped"))
# If stocks were bought/sold on a corporate action day IBKR may put several records for one corporate
# action. So first step is to aggregate quantity.
key_func = lambda x: (x['account'], x['asset'], x['type'], x['description'], x['timestamp'])
actions_sorted = sorted(actions, key=key_func)
actions_aggregated = []
for k, group in groupby(actions_sorted, key=key_func):
group_list = list(group)
part = group_list[0] # Take fist of several actions as a basis
part['quantity'] = sum(action['quantity'] for action in group_list) # and update quantity in it
part['jal_processed'] = False # This flag will be used to mark already processed records
actions_aggregated.append(part)
cnt += len(group_list) - 1
# Now split in 2 parts: A for new stocks deposit, B for old stocks withdrawal
parts_a = [action for action in actions_aggregated if action['quantity'] >= 0]
parts_b = [action for action in actions_aggregated if action['quantity'] < 0]
# Process sequentially '+' and '-', 'jal_processed' will set True when '+' has pair record in '-'
for action in parts_a + parts_b:
if action['jal_processed']:
continue
if action['type'] in action_loaders:
cnt += action_loaders[action['type']](action, parts_b)
else:
raise Statement_ImportError(
self.tr("Corporate action type is not supported: ") + f"{action['type']}")
logging.info(self.tr("Corporate actions loaded: ") + f"{cnt} ({len(actions)})")
def load(self, filename: str) -> None:
try:
xml_root = etree.parse(filename)
except etree.XMLSyntaxError as e:
raise Statement_ImportError(self.tr("Can't parse XML file: ") + e.msg)
self.validate_file_header_attributes(xml_root.findall('.')[0].attrib)
statements = xml_root.findall(self.statements_path)
for statement in statements:
if statement.tag != self.statement_tag:
continue
header_data = self.get_section_data(statement)
self._sections[StatementXML.STATEMENT_ROOT]['loader'](header_data)
for section in self._sections:
if section == StatementXML.STATEMENT_ROOT:
continue # skip header description
section_elements = statement.xpath(section) # Actually should be list of 0 or 1 element
if section_elements:
section_data = self.get_section_data(section_elements[0])
if section_data is None:
return
self._sections[section]['loader'](section_data)
logging.info(self.statement_name + self.tr(" loaded successfully"))
def _check_statement_header(self):
if self._statement[self.Header[0]][self.Header[1]] != self.Header[2]:
raise Statement_ImportError(
self.tr("Can't find expected report header: ") +
f"'{self.Header[2]}'")