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]}'")