def update_db_schema(db_path):
if QMessageBox().warning(
None, QApplication.translate('DB', "Database format is outdated"),
QApplication.translate(
'DB', "Do you agree to upgrade your data to newer format?"),
QMessageBox.Yes, QMessageBox.No) == QMessageBox.No:
return LedgerInitError(LedgerInitError.OutdatedDbSchema)
db = sqlite3.connect(get_dbfilename(db_path))
cursor = db.cursor()
try:
cursor.execute("SELECT value FROM settings WHERE name='SchemaVersion'")
except:
return LedgerInitError(LedgerInitError.DbInitFailure)
schema_version = cursor.fetchone()[0]
for step in range(schema_version, Setup.TARGET_SCHEMA):
delta_file = db_path + Setup.UPDATES_PATH + os.sep + Setup.UPDATE_PREFIX + f"{step+1}.sql"
logging.info(
f"Applying delta schema {step}->{step+1} from {delta_file}")
try:
with open(delta_file) as delta_sql:
try:
cursor.executescript(delta_sql.read())
except sqlite3.OperationalError as e:
return LedgerInitError(LedgerInitError.SQLFailure,
e.args[0])
except FileNotFoundError:
return LedgerInitError(LedgerInitError.NoDeltaFile, delta_file)
db.close()
return LedgerInitError(LedgerInitError.DbInitSuccess)
def update_db_schema(self, db_path) -> JalDBError:
if QMessageBox().warning(
None,
QApplication.translate('DB', "Database format is outdated"),
QApplication.translate(
'DB',
"Do you agree to upgrade your data to newer format?"),
QMessageBox.Yes, QMessageBox.No) == QMessageBox.No:
return JalDBError(JalDBError.OutdatedDbSchema)
db = db_connection()
version = readSQL(
"SELECT value FROM settings WHERE name='SchemaVersion'")
try:
schema_version = int(version)
except ValueError:
return JalDBError(JalDBError.DbInitFailure)
for step in range(schema_version, Setup.TARGET_SCHEMA):
delta_file = db_path + Setup.UPDATES_PATH + os.sep + Setup.UPDATE_PREFIX + f"{step + 1}.sql"
logging.info(
f"Applying delta schema {step}->{step + 1} from {delta_file}")
error = self.run_sql_script(delta_file)
if error.code != JalDBError.NoError:
db.close()
return error
return JalDBError(JalDBError.NoError)
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:
logging.error(
QApplication.translate("StatementXML",
"Unsupported date/time format: ") +
f"{xml_element.attrib[attr_name]}")
return None
def save(self):
try:
self.workbook.close()
except:
logging.error(
QApplication.translate('XLSX', "Can't save report into file ")
+ f"'{self.filename}'")
def MOEX_find_secid(**kwargs) -> str:
secid = ''
data = []
if 'reg_number' in kwargs:
url = f"https://iss.moex.com/iss/securities.json?q={kwargs['reg_number']}&iss.meta=off&limit=10"
asset_data = json.loads(get_web_data(url))
securities = asset_data['securities']
columns = securities['columns']
data = [
x for x in securities['data']
if x[columns.index('regnumber')] == kwargs['reg_number']
or x[columns.index('regnumber')] is None
]
if not data and 'isin' in kwargs:
url = f"https://iss.moex.com/iss/securities.json?q={kwargs['isin']}&iss.meta=off&limit=10"
asset_data = json.loads(get_web_data(url))
securities = asset_data['securities']
columns = securities['columns']
data = securities[
'data'] # take the whole list if we search by isin
if data:
if len(data) > 1:
# remove not traded assets if there are any outdated doubles present
data = [x for x in data if x[columns.index('is_traded')] == 1]
if len(data) > 1: # and then check again
logging.info(
QApplication.translate(
"MOEX", "Multiple MOEX assets found for: ") +
f"{kwargs}")
return secid
asset_data = dict(zip(columns, data[0]))
secid = asset_data['secid'] if 'secid' in asset_data else ''
return secid
def __init__(self, assets_list, symbol, reg_code=''):
self.id = None
# Try to find asset in list first by reg.code, next by symbol and as last resort by alt_symbol if it exists
if reg_code:
match = [
x for x in assets_list
if ('symbol' in x and 'reg_code' in x) and (
x['symbol'] == symbol and x['reg_code'] == reg_code)
]
if match:
if len(match) == 1:
self.id = match[0]['id']
return
else:
logging.error(
QApplication.translate("OpenBroker",
"Multiple asset match for ") +
f"'{symbol}':'{reg_code}'")
return
match = [
x for x in assets_list if 'symbol' in x and x['symbol'] == symbol
]
if match:
if len(match) == 1:
self.id = match[0]['id']
return
else:
logging.error(
QApplication.translate("OpenBroker",
"Multiple asset match for ") +
f"'{symbol}'")
match = [
x for x in assets_list
if 'alt_symbol' in x and x['alt_symbol'] == symbol
]
if match:
if len(match) == 1:
self.id = match[0]['id']
return
else:
logging.error(
QApplication.translate("OpenBroker",
"Multiple asset match for ") +
f"'{symbol}'")
def __init__(self, exchange):
self.name = ''
try:
self.name = self._exchange_types[exchange]
except KeyError:
logging.warning(
QApplication.translate("OpenBroker",
"Exchange isn't supported: ") +
f"'{exchange}'")
def __init__(self, asset_type):
self.type = self.NotSupported
try:
self.type = self._asset_types[asset_type]
except KeyError:
logging.warning(
QApplication.translate("OpenBroker",
"Asset type isn't supported: ") +
f"'{asset_type}'")
def __init__(self, asset_type, subtype):
self.type = self.NotSupported
try:
self.type = self._asset_types[asset_type]
except KeyError:
logging.warning(QApplication.translate("IBKR", "Asset type isn't supported: ") + f"'{asset_type}'")
if self.type == FOF.ASSET_STOCK and subtype: # distinguish ADR and ETF from stocks
try:
self.type = self._asset_types[subtype]
except KeyError:
pass
def __init__(self, assets_list, code):
self.id = None
match = [x for x in assets_list if x['symbol'] == code and x['type'] == FOF.ASSET_MONEY]
if match:
if len(match) == 1:
self.id = match[0]["id"]
else:
logging.error(QApplication.translate("IBKR", "Multiple match for ") + f"{code}")
else:
self.id = max([0] + [x['id'] for x in assets_list]) + 1
currency = {"id": self.id, "type": "money", "symbol": code}
assets_list.append(currency)
def get_country_by_code(country_code):
if not country_code:
return 0
country_id = readSQL("SELECT id FROM countries WHERE code=:code",
[(":code", country_code)],
check_unique=True)
if country_id is None:
country_id = 0
logging.warning(
QApplication.translate('DB', "Unknown country code: ") +
f"'{country_code}'")
return country_id
def update_asset_country(asset_id, country_id):
old_id = readSQL("SELECT country_id FROM assets WHERE id=:asset_id",
[(":asset_id", asset_id)])
if old_id == country_id:
return
_ = executeSQL(
"UPDATE assets SET country_id=:country_id WHERE id=:asset_id",
[(":asset_id", asset_id), (":country_id", country_id)])
if old_id == 0:
return
old_country = readSQL("SELECT name FROM countries WHERE id=:country_id",
[(":country_id", old_id)])
new_country = readSQL("SELECT name FROM countries WHERE id=:country_id",
[(":country_id", country_id)])
asset_name = readSQL("SELECT name FROM assets WHERE id=:asset_id",
[(":country_id", asset_id)])
logging.warning(
QApplication.translate('DB', "Country was changed for asset ") +
f"{asset_name}: f{old_country} -> {new_country}")
def request_url(method, url, params=None, json_params=None):
session = requests.Session()
session.headers['User-Agent'] = make_user_agent(url=url)
if method == "GET":
response = session.get(url)
elif method == "POST":
if params:
response = session.post(url, data=params)
elif json_params:
response = session.post(url, json=json_params)
else:
response = session.post(url)
else:
raise ValueError("Unknown download method for URL")
if response.status_code == 200:
return response.text
else:
logging.error(f"URL: {url}" + QApplication.translate('Net', " failed: ")
+ f"{response.status_code}: {response.text}")
return ''
def __init__(self, accounts_list, number, currency_ids):
self.id = None
account_ids = []
for currency in currency_ids:
match = [x for x in accounts_list if x['number'] == number and x['currency'] == currency]
if match:
if len(match) == 1:
account_ids.append(match[0]["id"])
else:
logging.error(QApplication.translate("IBKR", "Multiple account match for ") + f"{number}")
else:
new_id = max([0] + [x['id'] for x in accounts_list]) + 1
account_ids.append(new_id)
account = {"id": new_id, "number": number, "currency": currency}
accounts_list.append(account)
if account_ids:
if len(account_ids) == 1:
self.id = account_ids[0]
else:
self.id = account_ids
def MOEX_find_secid(**kwargs) -> str:
secid = ''
try:
search_key = kwargs['regcode'] if 'regcode' in kwargs else kwargs[
'isin']
except KeyError:
return secid
if not search_key:
return secid
url = f"https://iss.moex.com/iss/securities.json?q={search_key}&iss.meta=off&limit=10"
asset_data = json.loads(get_web_data(url))
securities = asset_data['securities']
columns = securities['columns']
if 'regcode' in kwargs:
# Take only records that have given regnumber to get rid of additional issues
data = [
x for x in securities['data']
if x[columns.index('regnumber')] == search_key
or x[columns.index('regnumber')] is None
]
else:
data = securities[
'data'] # take the whole list if we search by isin
if data:
if len(data) > 1:
# remove not traded assets if there are any outdated doubles present
data = [x for x in data if x[columns.index('is_traded')] == 1]
if len(data) > 1: # and then check again
logging.info(
QApplication.translate(
"MOEX", "Multiple MOEX assets found for reg.number: ")
+ search_key)
return secid
asset_data = dict(zip(columns, data[0]))
secid = asset_data['secid'] if 'secid' in asset_data else ''
return secid
def tr(self, text):
return QApplication.translate("TaxesRus", text)
def MOEX_download_info(asset_code, currency='') -> dict:
mapping = {
'SECID': 'symbol',
'NAME': 'name',
'SHORTNAME': 'short_name',
'ISIN': 'isin',
'REGNUMBER': 'reg_number',
'FACEVALUE': 'principal',
'MATDATE': 'expiry',
'LSTDELDATE': 'expiry',
'GROUP': 'type'
}
asset_type = {
'stock_shares': PredefinedAsset.Stock,
'stock_dr': PredefinedAsset.Stock,
'stock_bonds': PredefinedAsset.Bond,
'stock_etf': PredefinedAsset.ETF,
'stock_ppif': PredefinedAsset.ETF,
'futures_forts': PredefinedAsset.Derivative
}
asset = {}
if not asset_code:
return asset
url = f"http://iss.moex.com/iss/securities/{asset_code}.xml"
xml_root = xml_tree.fromstring(get_web_data(url))
info_rows = xml_root.findall("data[@id='description']/rows/*")
boards = xml_root.findall("data[@id='boards']/rows/*")
if not boards: # can't find boards -> not traded asset
return asset
for info in info_rows:
asset.update({
mapping[field]: info.attrib['value']
for field in mapping if info.attrib['name'] == field
})
if 'isin' in asset:
# replace symbol with short name if we have isin in place of symbol
asset['symbol'] = asset['short_name'] if asset['symbol'] == asset[
'isin'] else asset['symbol']
del asset['short_name'] # drop short name as we won't use it further
if 'principal' in asset: # Convert principal into float if possible or drop otherwise
try:
asset['principal'] = float(asset['principal'])
except ValueError:
del asset['principal']
if 'expiry' in asset: # convert YYYY-MM-DD into timestamp
date_value = datetime.strptime(asset['expiry'], "%Y-%m-%d")
asset['expiry'] = int(
date_value.replace(tzinfo=timezone.utc).timestamp())
try:
asset['type'] = asset_type[asset['type']]
except KeyError:
logging.error(
QApplication.translate("MOEX",
"Unsupported MOEX security type: ") +
f"{asset['type']}")
return {}
boards_list = [board.attrib for board in boards]
primary_board = [
x for x in boards_list
if "is_primary" in x and x["is_primary"] == '1'
]
if primary_board:
if currency == 'USD':
board_id = primary_board[0]['boardid'][:-1] + 'D'
elif currency == 'EUR':
board_id = primary_board[0]['boardid'][:-1] + 'E'
else:
board_id = primary_board[0]['boardid']
board = [
x for x in boards_list
if "boardid" in x and x["boardid"] == board_id
]
if board:
asset.update({
'engine': board[0]['engine'],
'market': board[0]['market'],
'board': board[0]['boardid']
})
return asset
def tr(self, text):
return QApplication.translate("IBKR", text)
def tr(self, text):
return QApplication.translate("Quik", text)
def tr(self, text):
return QApplication.translate("XLSX", text)
def tr(self, text):
return QApplication.translate("SlipsTaxAPI", text)
def tr(self, text):
return QApplication.translate("DLSG", text)
def tr(self, text):
return QApplication.translate("Statement", text)
def tr(self, text):
return QApplication.translate("JalBackup", text)
def __init__(self, action_type):
self.type = self.NotSupported
try:
self.type = self._corporate_action_types[action_type]
except KeyError:
logging.warning(QApplication.translate("IBKR", "Corporate action isn't supported: ") + f"{action_type}")
