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