def UpdateQuotes(self, start_timestamp, end_timestamp):
self.PrepareRussianCBReader()
jal_db = JalDB()
query = executeSQL(
"WITH _holdings AS ( "
"SELECT l.asset_id AS asset, l.account_id FROM ledger AS l "
"WHERE l.book_account = :assets_book AND l.timestamp <= :end_timestamp "
"GROUP BY l.asset_id "
"HAVING SUM(l.amount) > :tolerance "
"UNION "
"SELECT DISTINCT l.asset_id AS asset, l.account_id FROM ledger AS l "
"WHERE l.book_account = :assets_book AND l.timestamp >= :start_timestamp "
"AND l.timestamp <= :end_timestamp "
"UNION "
"SELECT c.id, NULL FROM currencies c "
") "
"SELECT h.asset AS asset_id, coalesce(ac.currency_id, 1) AS currency_id, "
"a.symbol AS name, a.quote_source 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 accounts AS ac ON ac.id=h.account_id "
"LEFT JOIN assets_ext AS a ON a.id=h.asset AND a.currency_id=coalesce(ac.currency_id, 1) "
"LEFT JOIN quotes AS q ON q.asset_id=h.asset "
"GROUP BY h.asset, ac.currency_id "
"ORDER BY feed_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)
if asset['asset_id'] == int(
JalSettings().getValue('BaseCurrency')):
continue
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['currency_id'],
asset['isin'], from_timestamp, end_timestamp)
except (xml_tree.ParseError, pd.errors.EmptyDataError, KeyError):
logging.warning(
self.tr("No data were downloaded for ") + f"{asset}")
continue
if data is not None:
quotations = []
for date, quote in data.iterrows(
): # Date in pandas dataset is in UTC by default
quotations.append({
'timestamp': int(date.timestamp()),
'quote': float(quote[0])
})
jal_db.update_quotes(asset['asset_id'], asset['currency_id'],
quotations)
jal_db.commit()
logging.info(self.tr("Download completed"))
def _match_account_ids(self):
for account in self._data[FOF.ACCOUNTS]:
account_id = JalDB().find_account(account['number'], -account['currency'])
if account_id:
old_id, account['id'] = account['id'], -account_id
self._update_id("account", old_id, account_id)
def prepare_tax(self):
_ = executeSQL("DELETE FROM t_last_dates")
_ = executeSQL("DELETE FROM t_last_quotes")
_ = executeSQL(
"INSERT INTO t_last_dates(ref_id, timestamp) "
"SELECT ref_id, coalesce(MAX(q.timestamp), 0) AS timestamp "
"FROM ("
"SELECT t.timestamp AS ref_id FROM trades AS t "
"WHERE t.account_id=:account_id AND t.asset_id=:asset_id "
"UNION "
"SELECT t.settlement AS ref_id FROM trades AS t "
"WHERE t.account_id=:account_id AND t.asset_id=:asset_id "
") LEFT JOIN accounts AS a ON a.id = :account_id "
"LEFT JOIN quotes AS q ON ref_id >= q.timestamp AND a.currency_id=q.asset_id AND q.currency_id=:base_currency "
"WHERE ref_id IS NOT NULL "
"GROUP BY ref_id ORDER BY ref_id",
[(":account_id", self.account_id), (":asset_id", self.asset_id),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
_ = executeSQL(
"INSERT INTO t_last_quotes(timestamp, asset_id, quote) "
"SELECT MAX(timestamp) AS timestamp, asset_id, quote "
"FROM quotes AS q LEFT JOIN accounts AS a ON a.id = :account_id "
"WHERE q.asset_id = :asset_id OR q.asset_id = a.currency_id AND q.currency_id=:base_currency "
"GROUP BY asset_id",
[(":account_id", self.account_id), (":asset_id", self.asset_id),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
self.quote = readSQL(
"SELECT quote FROM t_last_quotes WHERE asset_id=:asset_id",
[(":asset_id", self.asset_id)])
if self.quote is None:
logging.error(
self.tr("Can't get current quote for ") + self.asset_name)
return
self.currency_name = JalDB().get_asset_name(
JalDB().get_account_currency(self.account_id))
self.rate = readSQL(
"SELECT quote FROM accounts AS a "
"LEFT JOIN t_last_quotes AS q ON q.asset_id=a.currency_id WHERE id=:account_id",
[(":account_id", self.account_id)])
if self.rate is None:
logging.error(
self.tr("Can't get current rate for ") + self.currency_name)
return
query = executeSQL(
"SELECT strftime('%d/%m/%Y', datetime(t.timestamp, 'unixepoch')) AS timestamp, "
"t.qty AS qty, t.price AS o_price, oq.quote AS o_rate FROM trades AS t "
"LEFT JOIN accounts AS ac ON ac.id = :account_id "
"LEFT JOIN t_last_dates AS od ON od.ref_id = IIF(t.settlement=0, t.timestamp, t.settlement) "
"LEFT JOIN quotes AS oq ON ac.currency_id=oq.asset_id AND oq.currency_id=:base_currency "
"AND oq.timestamp=od.timestamp "
"WHERE t.account_id=:account_id AND t.asset_id=:asset_id AND t.qty*(:total_qty)>0 "
"ORDER BY t.timestamp DESC, t.id DESC",
[(":account_id", self.account_id), (":asset_id", self.asset_id),
(":total_qty", self.asset_qty),
(":base_currency", JalSettings().getValue('BaseCurrency'))])
table = []
remainder = self.asset_qty
profit = 0
value = 0
profit_rub = 0
value_rub = 0
while query.next():
record = readSQLrecord(query, named=True)
record['qty'] = record[
'qty'] if record['qty'] <= remainder else remainder
record['profit'] = record['qty'] * (self.quote - record['o_price'])
record[
'o_rate'] = 1 if record['o_rate'] == '' else record['o_rate']
record['profit_rub'] = record['qty'] * (
self.quote * self.rate - record['o_price'] * record['o_rate'])
record['tax'] = 0.13 * record['profit_rub'] if record[
'profit_rub'] > 0 else 0
table.append(record)
remainder -= record['qty']
profit += record['profit']
value += record['qty'] * record['o_price']
profit_rub += record['profit_rub']
value_rub += record['qty'] * record['o_price'] * record['o_rate']
if remainder <= 0:
break
tax = 0.13 * profit_rub if profit_rub > 0 else 0
table.append({
'timestamp': self.tr("TOTAL"),
'qty': self.asset_qty,
'o_price': value / self.asset_qty,
'o_rate': value_rub / value,
'profit': profit,
'profit_rub': profit_rub,
'tax': tax
})
data = pd.DataFrame(table)
self.dataframe = data
def MOEX_DataReader(self,
asset_id,
asset_code,
currency,
isin,
start_timestamp,
end_timestamp,
update_symbol=True):
currency = JalDB().get_asset_name(currency)
asset = self.MOEX_info(symbol=asset_code,
isin=isin,
currency=currency,
special=True)
if (asset['engine'] is None) or (asset['market'] is
None) or (asset['board'] is None):
logging.warning(f"Failed to find {asset_code} on moex.com")
return None
if (asset['market'] == 'bonds') and (asset['board'] == 'TQCB'):
asset_code = isin # Corporate bonds are quoted by ISIN
if (asset['market'] == 'shares') and (asset['board'] == 'TQIF'):
asset_code = isin # ETFs are quoted by ISIN
if update_symbol:
isin = asset['isin'] if 'isin' in asset else ''
reg_number = asset['reg_number'] if 'reg_number' in asset else ''
expiry = asset['expiry'] if 'expiry' in asset else 0
principal = asset['principal'] if 'principal' in asset else 0
details = {
'isin': isin,
'reg_number': reg_number,
'expiry': expiry,
'principal': principal
}
JalDB().update_asset_data(asset_id, details)
# 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/{asset['engine']}/markets/{asset['market']}/" \
f"boards/{asset['board']}/securities/{asset_code}.xml?from={date1}&till={date2}"
xml_root = xml_tree.fromstring(get_web_data(url))
history_rows = xml_root.findall("data[@id='history']/rows/*")
quotes = []
for row in history_rows:
if row.attrib['CLOSE']:
if 'FACEVALUE' in row.attrib: # Correction for bonds
price = float(row.attrib['CLOSE']) * float(
row.attrib['FACEVALUE']) / 100.0
quotes.append({
"Date": row.attrib['TRADEDATE'],
"Close": price
})
else:
quotes.append({
"Date": row.attrib['TRADEDATE'],
"Close": row.attrib['CLOSE']
})
data = pd.DataFrame(quotes, columns=["Date", "Close"])
data['Date'] = pd.to_datetime(data['Date'], format="%Y-%m-%d")
data['Close'] = pd.to_numeric(data['Close'])
close = data.set_index("Date")
return close
def load(self):
# Read first table with account details
try:
data = pandas.read_html(self._filename,
match='Account Capabilities')
except:
logging.error(self.tr("Can't read statement file"))
return False
if len(data) != 1:
logging.error(
self.tr("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(
self.tr("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(
self.tr("Can't read Trades table from statement file"))
return False
if len(data) != 1:
logging.error(self.tr("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 = JalDB().get_account_id(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 = JalDB().get_asset_id(row[0])
if asset_id is None:
logging.warning(self.tr("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(self.tr("Load IBKR Activity statement completed"))
return True
def setCurrency(self, currency_id):
if self._currency != currency_id:
self._currency = currency_id
self._currency_name = JalDB().get_asset_name(currency_id)
self.calculateHoldings()
def find_dividend4tax(self, timestamp, account_id, asset_id, prev_tax, new_tax, note):
PaymentInLiueOfDividend = 'PAYMENT IN LIEU OF DIVIDEND'
TaxNotePattern = r"^(?P<symbol>.*\w) ?\((?P<isin>\w+)\)(?P<prefix>( \w*)+) +(?P<amount>\d+\.\d+)?(?P<suffix>.*)$"
DividendNotePattern = r"^(?P<symbol>.*\w) ?\((?P<isin>\w+)\)(?P<prefix>( \w*)+) +(?P<amount>\d+\.\d+)?(?P<suffix>.*) \(.*\)$"
dividends = [x for x in self._data[FOF.ASSET_PAYMENTS] if
x['type'] == FOF.PAYMENT_DIVIDEND and x['asset'] == asset_id and x['account'] == account_id]
account = [x for x in self._data[FOF.ACCOUNTS] if x["id"] == account_id][0]
currency = [x for x in self._data[FOF.ASSETS] if x["id"] == account['currency']][0]
db_account = JalDB().get_account_id(account['number'], currency['symbol'])
asset = [x for x in self._data[FOF.ASSETS] if x["id"] == asset_id][0]
isin = asset['isin'] if 'isin' in asset else ''
db_asset = JalDB().get_asset_id(asset['symbol'], isin=isin, dialog_new=False)
if db_account is not None and db_asset is not None:
query = executeSQL(
"SELECT -id AS id, -account_id AS account, timestamp, number, "
"-asset_id AS asset, amount, tax, note as description FROM dividends "
"WHERE type=:div AND account_id=:account_id AND asset_id=:asset_id",
[(":div", DividendSubtype.Dividend), (":account_id", db_account), (":asset_id", db_asset)],
forward_only=True)
while query.next():
db_dividend = readSQLrecord(query, named=True)
db_dividend['asset'] = asset_id
db_dividend['account'] = account_id
dividends.append(db_dividend)
if datetime.utcfromtimestamp(timestamp).timetuple().tm_yday < 75:
# We may have wrong date in taxes before March, 15 due to tax correction
range_start = ManipulateDate.startOfPreviousYear(day=datetime.utcfromtimestamp(timestamp))
dividends = [x for x in dividends if x['timestamp'] >= range_start]
else:
# For any other day - use exact time match
dividends = [x for x in dividends if x['timestamp'] == timestamp]
dividends = [x for x in dividends if 'tax' not in x or (abs(x['tax'] - prev_tax) < 0.0001)]
dividends = sorted(dividends, key=lambda x: x['timestamp'])
# Choose either Dividends or Payments in liue with regards to note of the matching tax
if PaymentInLiueOfDividend in note.upper():
dividends = list(filter(lambda item: PaymentInLiueOfDividend in item['description'], dividends))
# we don't check for full match as there are a lot of records without amount
else:
dividends = list(filter(lambda item: PaymentInLiueOfDividend not in item['description'], dividends))
# Check for full match
for dividend in dividends:
if (dividend['timestamp'] == timestamp) and (note.upper() == dividend['description'][:len(note)].upper()):
return dividend
if len(dividends) == 0:
return None
# Chose most probable dividend - by amount, timestamp and description
parts = re.match(TaxNotePattern, note, re.IGNORECASE)
if not parts:
logging.warning(self.tr("*** MANUAL ENTRY REQUIRED ***"))
logging.warning(self.tr("Unhandled tax pattern found: ") + f"{note}")
return None
parts = parts.groupdict()
note_prefix = parts['prefix']
note_suffix = parts['suffix']
try:
note_amount = float(parts['amount'])
except (ValueError, TypeError):
note_amount = 0
score = [0] * len(dividends)
for i, dividend in enumerate(dividends):
parts = re.match(DividendNotePattern, dividend['description'], re.IGNORECASE)
if not parts:
logging.warning(self.tr("*** MANUAL ENTRY REQUIRED ***"))
logging.warning(self.tr("Unhandled dividend pattern found: ") + f"{dividend['description']}")
return None
parts = parts.groupdict()
try:
amount = float(parts['amount'])
except (ValueError, TypeError):
amount = 0
if abs(amount - note_amount) <= 0.000005: # Description has very similar amount +++++
score[i] += 5
if dividend['timestamp'] == timestamp: # Timestamp exact match gives ++
score[i] += 2
if abs(0.1 * dividend['amount'] - new_tax) <= 0.01: # New tax is 10% of dividend gives +
score[i] += 1
if parts['prefix'] == note_prefix: # Prefix part of description match gives +
score[i] += 1
if parts['suffix'] == note_suffix: # Suffix part of description match gives +
score[i] += 1
for i, vote in enumerate(score):
if (vote == max(score)) and (vote > 0):
return dividends[i]
# Final check - if only one found, return it
if len(dividends) == 1:
return dividends[0]
return None
def __init__(self, operation_id=None, display_type=None):
labels = {
Transfer.Outgoing: ('<', CustomColor.DarkBlue),
Transfer.Incoming: ('>', CustomColor.DarkBlue),
Transfer.Fee: ('=', CustomColor.DarkRed)
}
super().__init__(operation_id)
self.table = "transfers"
self._otype = LedgerTransaction.Transfer
self._display_type = display_type
self._data = readSQL(
"SELECT t.withdrawal_timestamp, t.withdrawal_account, t.withdrawal, t.deposit_timestamp, "
"t.deposit_account, t.deposit, t.fee_account, t.fee, t.asset, t.note "
"FROM transfers AS t WHERE t.id=:oid", [(":oid", self._oid)],
named=True)
self._withdrawal_account = self._data['withdrawal_account']
self._withdrawal_account_name = JalDB().get_account_name(
self._withdrawal_account)
self._withdrawal_timestamp = self._data['withdrawal_timestamp']
self._withdrawal = self._data['withdrawal']
self._withdrawal_currency = JalDB().get_asset_name(
JalDB().get_account_currency(self._withdrawal_account))
self._deposit_account = self._data['deposit_account']
self._deposit_account_name = JalDB().get_account_name(
self._deposit_account)
self._deposit = self._data['deposit']
self._deposit_currency = JalDB().get_asset_name(
JalDB().get_account_currency(self._deposit_account))
self._deposit_timestamp = self._data['deposit_timestamp']
self._fee_account = self._data['fee_account']
self._fee_currency = JalDB().get_asset_name(
JalDB().get_account_currency(self._fee_account))
self._fee_account_name = JalDB().get_account_name(self._fee_account)
self._fee = self._data['fee']
self._label, self._label_color = labels[display_type]
self._note = self._data['note']
if self._display_type == Transfer.Outgoing:
self._reconciled = JalDB().account_reconciliation_timestamp(
self._withdrawal_account) >= self._timestamp
elif self._display_type == Transfer.Incoming:
self._reconciled = JalDB().account_reconciliation_timestamp(
self._deposit_account) >= self._timestamp
elif self._display_type == Transfer.Fee:
self._reconciled = JalDB().account_reconciliation_timestamp(
self._fee_account) >= self._timestamp
else:
assert True, "Unknown transfer type"
def description(self) -> str:
return self._note + "\n" + self.tr(
"Tax: ") + JalDB().get_asset_country(self._asset)
def getAmount(self, book, account_id, asset_id=None):
if asset_id is None:
asset_id = JalDB().get_account_currency(account_id)
return self.amounts[(book, account_id, asset_id)]
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(self.tr("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 = JalDB().get_account_id(parts.group(1))
else:
logging.error(
self.tr("Can't get account number from the statement."))
return False
if account_id is None:
logging.error(
self.tr("Account with number ") + f"{parts.group(1)}" +
self.tr(" 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(
self.tr("Unknown operation type ") + f"'{row[self.Type]}'")
continue
asset_id = JalDB().get_asset_id(row[self.Symbol])
if asset_id is None:
logging.warning(
self.tr("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