def test_MOEX_downloader_USD(prepare_db_moex):
usd_quotes = pd.DataFrame({'Close': [12.02, 11.90],
'Date': [datetime(2021, 12, 13), datetime(2021, 12, 14)]})
usd_quotes = usd_quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.MOEX_DataReader(8, 'FXGD', 2, 'IE00B8XB7377', 1639353600, 1639440000, update_symbol=False)
assert_frame_equal(usd_quotes, quotes_downloaded)
def test_Frankfurt_downloader():
quotes = pd.DataFrame({'Close': [233.40, 234.25],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14)]})
quotes = quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.YahooFRA_Downloader(0, 'VOW3', 3, '', 1618272000, 1618444800)
assert_frame_equal(quotes, quotes_downloaded)
def test_TMX_downloader():
quotes = pd.DataFrame({'Close': [117.18, 117.34, 118.02],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14), datetime(2021, 4, 15)]})
quotes = quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.TMX_Downloader(0, 'RY', 3, '', 1618272000, 1618444800)
assert_frame_equal(quotes, quotes_downloaded)
def test_Euronext_downloader():
quotes = pd.DataFrame({'Close': [3.4945, 3.5000, 3.4995],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14), datetime(2021, 4, 15)]})
quotes = quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.Euronext_DataReader(0, '', 3, 'FI0009000681', 1618272000, 1618444800)
assert_frame_equal(quotes, quotes_downloaded)
def test_LSE_downloader():
quotes = pd.DataFrame({'Close': [73.5, 75.5],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14)]})
quotes = quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.YahooLSE_Downloader(0, 'TSL', 3, '', 1618272000, 1618444800)
assert_frame_equal(quotes, quotes_downloaded)
def test_NYSE_downloader():
quotes = pd.DataFrame({'Close': [134.429993, 132.029999],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14)]})
quotes = quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.Yahoo_Downloader(0, 'AAPL', 2, '', 1618272000, 1618444800)
assert_frame_equal(quotes, quotes_downloaded)
def test_CBR_downloader():
codes = pd.DataFrame({'ISO_name': ['AUD', 'ATS'], 'CBR_code': ['R01010', 'R01015']})
rates = pd.DataFrame({'Rate': [77.5104, 77.2535, 75.6826],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14), datetime(2021, 4, 15)]})
rates = rates.set_index('Date')
downloader = QuoteDownloader()
downloader.PrepareRussianCBReader()
assert_frame_equal(codes, downloader.CBR_codes.head(2))
rates_downloaded = downloader.CBR_DataReader(0, 'USD', 1, '', 1618272000, 1618358400)
assert_frame_equal(rates, rates_downloaded)
def asset_id(self, asset_info) -> int:
asset = None
asset_info = {k: v for k, v in asset_info.items() if v} # drop keys with empty values
if 'isin' in asset_info:
asset = self._find_in_list(self._data[FOF.ASSETS], 'isin', asset_info['isin'])
if asset is None and 'reg_number' in asset_info:
asset_data = self._find_in_list(self._data[FOF.ASSETS_DATA], 'reg_number', asset_info['reg_number'])
if asset_data is not None:
asset = self._find_in_list(self._data[FOF.ASSETS], 'id', asset_data['asset'])
if asset is None and 'symbol' in asset_info:
symbol = self._find_in_list(self._data[FOF.SYMBOLS], 'symbol', asset_info['symbol'])
if symbol is not None:
asset = self._find_in_list(self._data[FOF.ASSETS], 'id', symbol['asset'])
if 'isin' in asset and 'isin' in asset_info and asset['isin'] != asset_info['isin']:
asset = None
if asset is None and 'search_online' in asset_info:
if asset_info['search_online'] == "MOEX":
search_data = {}
self._uppend_keys_from(search_data, asset_info, ['isin', 'reg_number'])
symbol = QuoteDownloader.MOEX_find_secid(**search_data)
if not symbol and 'symbol' in asset_info:
symbol = asset_info['symbol']
currency = asset_info['currency'] if 'currency' in asset_info else None # Keep currency
asset_info = QuoteDownloader.MOEX_info(symbol=symbol)
asset_info['type'] = FOF.convert_predefined_asset_type(asset_info['type'])
if currency is not None:
asset_info['currency'] = currency
asset_info['note'] = "MOEX"
return self.asset_id(asset_info) # Call itself once again to cross-check downloaded data
if asset is None:
if 'should_exist' in asset_info and asset_info['should_exist']:
raise Statement_ImportError(self.tr("Can't locate asset in statement data: ") + f"'{asset_info}'")
asset_id = max([0] + [x['id'] for x in self._data[FOF.ASSETS]]) + 1
asset = {"id": asset_id}
self._uppend_keys_from(asset, asset_info, ['type', 'name', 'isin', 'country'])
self._data[FOF.ASSETS].append(asset)
if 'symbol' in asset_info:
symbol_id = max([0] + [x['id'] for x in self._data[FOF.SYMBOLS]]) + 1
symbol = {"id": symbol_id, "asset": asset_id}
self._uppend_keys_from(symbol, asset_info, ['symbol', 'currency', 'note'])
self._data[FOF.SYMBOLS].append(symbol)
data = {}
self._uppend_keys_from(data, asset_info, ['reg_number', 'expiry', 'principal'])
if data:
data_id = max([0] + [x['id'] for x in self._data[FOF.ASSETS_DATA]]) + 1
data['id'] = data_id
data['asset'] = asset_id
self._data[FOF.ASSETS_DATA].append(data)
else:
if 'type' in asset and asset['type'] != FOF.ASSET_MONEY:
self.update_asset_data(asset['id'], asset_info)
return asset['id']
def __init__(self, language):
QMainWindow.__init__(self, None)
self.running = False
self.setupUi(self)
self.restoreGeometry(base64.decodebytes(JalSettings().getValue('WindowGeometry', '').encode('utf-8')))
self.restoreState(base64.decodebytes(JalSettings().getValue('WindowState', '').encode('utf-8')))
self.ledger = Ledger()
# Customize Status bar and logs
self.ProgressBar = QProgressBar(self)
self.StatusBar.addPermanentWidget(self.ProgressBar)
self.ProgressBar.setVisible(False)
self.ledger.setProgressBar(self, self.ProgressBar)
self.Logs.setStatusBar(self.StatusBar)
self.logger = logging.getLogger()
self.logger.addHandler(self.Logs)
log_level = os.environ.get('LOGLEVEL', 'INFO').upper()
self.logger.setLevel(log_level)
self.currentLanguage = language
self.downloader = QuoteDownloader()
self.statements = Statements(self)
self.reports = Reports(self, self.mdiArea)
self.backup = JalBackup(self, get_dbfilename(get_app_path()))
self.estimator = None
self.price_chart = None
self.actionImportSlipRU.setEnabled(dependency_present(['pyzbar', 'PIL']))
self.actionAbout = QAction(text=self.tr("About"), parent=self)
self.MainMenu.addAction(self.actionAbout)
self.langGroup = QActionGroup(self.menuLanguage)
self.createLanguageMenu()
self.statementGroup = QActionGroup(self.menuStatement)
self.createStatementsImportMenu()
self.reportsGroup = QActionGroup(self.menuReports)
self.createReportsMenu()
self.setWindowIcon(load_icon("jal.png"))
self.connect_signals_and_slots()
self.actionOperations.trigger()
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_assets(self, assets):
cnt = 0
for asset in assets:
if asset[
'type'] == OpenBroker_AssetType.NotSupported: # Skip not supported type of asset
continue
if asset['exchange'] == "MOEX":
asset_info = QuoteDownloader.MOEX_info(
symbol=asset['symbol'],
isin=asset['isin'],
regnumber=asset['reg_number'])
if asset_info:
asset.update(asset_info)
asset['type'] = FOF.convert_predefined_asset_type(
asset['type'])
if asset['exchange'] != '': # don't store empty exchange
asset['note'] = asset['exchange']
asset.pop('exchange')
self.asset_id(asset)
cnt += 1
logging.info(self.tr("Securities loaded: ") + f"{cnt} ({len(assets)})")
def load_assets(self, assets):
cnt = 0
base = max([0] + [x['id'] for x in self._data[FOF.ASSETS]]) + 1
for i, asset in enumerate(assets):
if asset[
'type'] == OpenBroker_AssetType.NotSupported: # Skip not supported type of asset
continue
asset['id'] = base + i
if asset['exchange'] == "MOEX":
asset_info = QuoteDownloader.MOEX_info(
symbol=asset['symbol'],
isin=asset['isin'],
regnumber=asset['reg_code'])
if asset_info:
asset.update(asset_info)
asset['type'] = FOF.convert_predefined_asset_type(
asset['type'])
if asset['exchange'] == '': # don't store empty exchange
asset.pop('exchange')
cnt += 1
self._data[FOF.ASSETS].append(asset)
logging.info(self.tr("Securities loaded: ") + f"{cnt} ({len(assets)})")
def test_MOEX_downloader(prepare_db_moex):
stock_quotes = pd.DataFrame({'Close': [287.95, 287.18],
'Date': [datetime(2021, 4, 13), datetime(2021, 4, 14)]})
stock_quotes = stock_quotes.set_index('Date')
bond_quotes = pd.DataFrame({'Close': [1001.00, 999.31],
'Date': [datetime(2021, 7, 22), datetime(2021, 7, 23)]})
bond_quotes = bond_quotes.set_index('Date')
corp_quotes = pd.DataFrame({'Close': [1002.90, 1003.70],
'Date': [datetime(2021, 7, 22), datetime(2021, 7, 23)]})
corp_quotes = corp_quotes.set_index('Date')
etf_quotes = pd.DataFrame({'Close': [1736.8, 1735.0],
'Date': [datetime(2021, 12, 13), datetime(2021, 12, 14)]})
etf_quotes = etf_quotes.set_index('Date')
downloader = QuoteDownloader()
quotes_downloaded = downloader.MOEX_DataReader(4, 'SBER', 1, 'RU0009029540', 1618272000, 1618358400)
assert_frame_equal(stock_quotes, quotes_downloaded)
assert readSQL("SELECT * FROM assets_ext WHERE id=4") == [4, PredefinedAsset.Stock, 'SBER', '', 'RU0009029540', 1, 0, -1]
assert readSQL("SELECT value FROM asset_data WHERE asset_id=4 AND datatype=1") == '10301481B'
quotes_downloaded = downloader.MOEX_DataReader(6, 'SU26238RMFS4', 1, 'RU000A1038V6', 1626912000, 1626998400)
assert_frame_equal(bond_quotes, quotes_downloaded)
assert readSQL("SELECT * FROM assets_ext WHERE id=6") == [6, PredefinedAsset.Bond, 'SU26238RMFS4', '', 'RU000A1038V6', 1, 0, -1]
assert readSQL("SELECT value FROM asset_data WHERE asset_id=6 AND datatype=1") == '26238RMFS'
assert readSQL("SELECT value FROM asset_data WHERE asset_id=6 AND datatype=2") == '2252188800'
assert readSQL("SELECT value FROM asset_data WHERE asset_id=6 AND datatype=3") == '1000.0'
quotes_downloaded = downloader.MOEX_DataReader(7, 'МКБ 1P2', 1, 'RU000A1014H6', 1626912000, 1626998400)
assert_frame_equal(corp_quotes, quotes_downloaded)
assert readSQL("SELECT * FROM assets_ext WHERE id=7") == [7, PredefinedAsset.Bond, 'МКБ 1P2', '', 'RU000A1014H6', 1, 0, -1]
assert readSQL("SELECT value FROM asset_data WHERE asset_id=7 AND datatype=1") == '4B020901978B001P'
assert readSQL("SELECT value FROM asset_data WHERE asset_id=7 AND datatype=2") == '1638230400'
assert readSQL("SELECT value FROM asset_data WHERE asset_id=7 AND datatype=3") == '1000.0'
quotes_downloaded = downloader.MOEX_DataReader(8, 'ЗПИФ ПНК', 1, 'RU000A1013V9', 1639353600, 1639440000, update_symbol=False)
assert_frame_equal(etf_quotes, quotes_downloaded)
def __init__(self, language):
QMainWindow.__init__(self, None)
self.setupUi(self)
self.currentLanguage = language
self.current_index = None # this is used in onOperationContextMenu() to track item for menu
self.ledger = Ledger()
self.downloader = QuoteDownloader()
self.taxes = TaxesRus()
self.statements = StatementLoader()
self.backup = JalBackup(self, get_dbfilename(get_app_path()))
self.estimator = None
self.price_chart = None
self.actionImportSlipRU.setEnabled(
dependency_present(['pyzbar', 'PIL']))
self.actionAbout = QAction(text=self.tr("About"), parent=self)
self.MainMenu.addAction(self.actionAbout)
self.langGroup = QActionGroup(self.menuLanguage)
self.createLanguageMenu()
self.statementGroup = QActionGroup(self.menuStatement)
self.createStatementsImportMenu()
# Set icons
self.setWindowIcon(load_icon("jal.png"))
self.NewOperationBtn.setIcon(load_icon("new.png"))
self.CopyOperationBtn.setIcon(load_icon("copy.png"))
self.DeleteOperationBtn.setIcon(load_icon("delete.png"))
# Operations view context menu
self.contextMenu = QMenu(self.OperationsTableView)
self.actionReconcile = QAction(load_icon("reconcile.png"),
self.tr("Reconcile"), self)
self.actionCopy = QAction(load_icon("copy.png"), self.tr("Copy"), self)
self.actionDelete = QAction(load_icon("delete.png"), self.tr("Delete"),
self)
self.contextMenu.addAction(self.actionReconcile)
self.contextMenu.addSeparator()
self.contextMenu.addAction(self.actionCopy)
self.contextMenu.addAction(self.actionDelete)
# Customize Status bar and logs
self.ProgressBar = QProgressBar(self)
self.StatusBar.addWidget(self.ProgressBar)
self.ProgressBar.setVisible(False)
self.ledger.setProgressBar(self, self.ProgressBar)
self.NewLogEventLbl = QLabel(self)
self.StatusBar.addWidget(self.NewLogEventLbl)
self.Logs.setNotificationLabel(self.NewLogEventLbl)
self.Logs.setFormatter(
logging.Formatter('%(asctime)s - %(levelname)s - %(message)s'))
self.logger = logging.getLogger()
self.logger.addHandler(self.Logs)
log_level = os.environ.get('LOGLEVEL', 'INFO').upper()
self.logger.setLevel(log_level)
# Setup reports tab
self.reports = Reports(self.ReportTableView, self.ReportTreeView)
# Customize UI configuration
self.balances_model = BalancesModel(self.BalancesTableView)
self.BalancesTableView.setModel(self.balances_model)
self.balances_model.configureView()
self.holdings_model = HoldingsModel(self.HoldingsTableView)
self.HoldingsTableView.setModel(self.holdings_model)
self.holdings_model.configureView()
self.HoldingsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.operations_model = OperationsModel(self.OperationsTableView)
self.OperationsTableView.setModel(self.operations_model)
self.operations_model.configureView()
self.OperationsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.connect_signals_and_slots()
self.NewOperationMenu = QMenu()
for i in range(self.OperationsTabs.count()):
if hasattr(self.OperationsTabs.widget(i), "isCustom"):
self.OperationsTabs.widget(i).dbUpdated.connect(
self.ledger.rebuild)
self.OperationsTabs.widget(i).dbUpdated.connect(
self.operations_model.refresh)
self.NewOperationMenu.addAction(
self.OperationsTabs.widget(i).name,
partial(self.createOperation, i))
self.NewOperationBtn.setMenu(self.NewOperationMenu)
# Setup balance and holdings parameters
current_time = QDateTime.currentDateTime()
current_time.setTimeSpec(
Qt.UTC) # We use UTC everywhere so need to force TZ info
self.BalanceDate.setDateTime(current_time)
self.BalancesCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.HoldingsDate.setDateTime(current_time)
self.HoldingsCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.OperationsTabs.setCurrentIndex(TransactionType.NA)
self.OperationsTableView.selectRow(0)
self.OnOperationsRangeChange(0)
def test_MOEX_details():
assert QuoteDownloader.MOEX_find_secid(regcode='') == ''
assert QuoteDownloader.MOEX_find_secid(isin='TEST') == ''
assert QuoteDownloader.MOEX_find_secid(regcode='2770') == 'RU000A1013V9'
assert QuoteDownloader.MOEX_find_secid(regcode='1-01-00010-A') == 'AFLT'
assert QuoteDownloader.MOEX_find_secid(isin='IE00B8XB7377') == 'FXGD'
assert QuoteDownloader.MOEX_find_secid(isin='JE00B6T5S470') == 'POLY'
assert QuoteDownloader.MOEX_find_secid(
isin='RU000A1038V6') == 'SU26238RMFS4'
assert QuoteDownloader.MOEX_info() == {}
assert QuoteDownloader.MOEX_info(special=True) == {}
assert QuoteDownloader.MOEX_info(symbol='AFLT', special=True) == {
'symbol': 'AFLT',
'isin': 'RU0009062285',
'name': 'Аэрофлот-росс.авиалин(ПАО)ао',
'principal': 1.0,
'reg_code': '1-01-00010-A',
'engine': 'stock',
'market': 'shares',
'board': 'TQBR',
'type': PredefinedAsset.Stock
}
assert QuoteDownloader.MOEX_info(isin='RU000A0JWUE9', special=True) == {
'symbol': 'СберБ БО37',
'isin': 'RU000A0JWUE9',
'name': 'Сбербанк ПАО БО-37',
'principal': 1000.0,
'reg_code': '4B023701481B',
'expiry': 1632960000,
'engine': 'stock',
'market': 'bonds',
'board': 'TQCB',
'type': PredefinedAsset.Bond
}
assert QuoteDownloader.MOEX_info(symbol='SiZ1', isin='', special=True) == {
'symbol': 'SiZ1',
'name': 'Фьючерсный контракт Si-12.21',
'expiry': 1639612800,
'engine': 'futures',
'market': 'forts',
'board': 'RFUD',
'type': PredefinedAsset.Derivative
}
assert QuoteDownloader.MOEX_info(symbol='', regnumber='2770') == {
'symbol': 'ЗПИФ ПНК',
'isin': 'RU000A1013V9',
'name': 'ЗПИФ Фонд ПНК-Рентал',
'reg_code': '2770',
'type': PredefinedAsset.ETF
}
assert QuoteDownloader.MOEX_info(isin='IE00B8XB7377',
regnumber='IE00B8XB7377',
symbol='FXGD ETF') == {
'symbol': 'FXGD',
'isin': 'IE00B8XB7377',
'name': 'FinEx Gold ETF USD',
'type': PredefinedAsset.ETF
}
assert QuoteDownloader.MOEX_info(isin='JE00B6T5S470',
regnumber='',
symbol='') == {
'symbol': 'POLY',
'isin': 'JE00B6T5S470',
'name': 'Polymetal International plc',
'type': PredefinedAsset.Stock
}
assert QuoteDownloader.MOEX_info(isin='RU000A1038V6') == {
'symbol': 'SU26238RMFS4',
'isin': 'RU000A1038V6',
'name': 'ОФЗ-ПД 26238 15/05/2041',
'principal': 1000.0,
'reg_code': '26238RMFS',
'expiry': 2252188800,
'type': PredefinedAsset.Bond
}
def updateExchangeData(self):
QuoteDownloader().updataData()