def test_exchange_rates_rub(trade_date: datetime, currency: Currency,
expect_rate: Money):
try:
p = ExchangeRatesRUB(year_from=2015, cache_dir=None)
except ConnectionError as ex:
pytest.skip(f'connection error: {ex}')
return
rate = p.get_rate(currency, trade_date)
assert rate == expect_rate, f'{trade_date}: {rate} != {expect_rate}'
def prepare_interests_report(interests: List[Interest], cbr_client_usd: cbr.ExchangeRatesRUB) -> pandas.DataFrame:
operation_date_column = 'date'
df_data = [
(i + 1, pandas.to_datetime(x.date), x.amount, x.description, x.date.year)
for i, x in enumerate(interests)
]
df = pandas.DataFrame(df_data, columns=['N', operation_date_column, 'amount', 'description', 'tax_year'])
df['rate'] = df.apply(lambda x: cbr_client_usd.get_rate(x['amount'].currency, x[operation_date_column]), axis=1)
df['amount_rub'] = df.apply(lambda x: cbr_client_usd.convert_to_rub(x['amount'], x[operation_date_column]), axis=1)
return df
def prepare_dividends_report(dividends: List[Dividend], cbr_client_usd: cbr.ExchangeRatesRUB, verbose: bool) -> pandas.DataFrame:
operation_date_column = 'date'
if not verbose:
dividends = [x for x in dividends if x.amount.amount != 0 or x.tax.amount != 0] # remove reversed dividends
df_data = [(i + 1, x.ticker, pandas.to_datetime(x.date), x.amount, x.tax) for i, x in enumerate(dividends)]
df = pandas.DataFrame(df_data, columns=['N', 'ticker', 'date', 'amount', 'tax_paid'])
df['tax_year'] = df[operation_date_column].map(lambda x: x.year)
df['rate'] = df.apply(lambda x: cbr_client_usd.get_rate(x['amount'].currency, x[operation_date_column]), axis=1)
df['amount_rub'] = df.apply(lambda x: cbr_client_usd.convert_to_rub(x['amount'], x[operation_date_column]), axis=1)
df['tax_paid_rub'] = df.apply(lambda x: cbr_client_usd.convert_to_rub(x['tax_paid'], x[operation_date_column]), axis=1)
df['tax_rate'] = df.apply(lambda x: round(x['tax_paid'].amount * 100 / x['amount'].amount, 2), axis=1)
return df
def test_precision():
"""
Отладка проблемы с потерей точности при расчётах финансового результата.
"""
test_case = test_trades_precision()
res: dict = prepare_trades_report(test_case, ExchangeRatesRUB()).to_dict()
assert [x.amount for x in res['total_rub'].values()] == [
Decimal(
'-51586.552320'
), # Расход: (80.62 * 10 * 63.9091) + (0.1 * 10 * 63.0359) = 51586.55232₽
Decimal(
'52258.4492595587200'
), # Доход: (81.82 * 10 * 63.9490) - (0.101812674 * 10 * 63.4720) = 52258.4492595587200₽
]
assert [x.amount for x in res['profit_rub'].values()] == [
Decimal('0'),
Decimal(
'671.8969395587200'
), # Финансовый результат: 52258.4492595587200 - 51586.552320 = 671.896939559₽
]
def prepare_fees_report(fees: List[Fee], cbr_client_usd: cbr.ExchangeRatesRUB, verbose: bool) -> pandas.DataFrame:
operation_date_column = 'date'
df_data = [
(i + 1, pandas.to_datetime(x.date), x.amount, x.description, x.date.year)
for i, x in enumerate(fees)
]
df = pandas.DataFrame(df_data, columns=['N', operation_date_column, 'amount', 'description', 'tax_year'])
df['rate'] = df.apply(lambda x: cbr_client_usd.get_rate(x['amount'].currency, x[operation_date_column]), axis=1)
df['amount_rub'] = df.apply(lambda x: cbr_client_usd.convert_to_rub(x['amount'], x[operation_date_column]), axis=1)
if not verbose:
df['abs_amount_del'] = df.apply(lambda x: abs(x.amount.amount), axis=1)
df.drop_duplicates(subset=[operation_date_column, 'description', 'abs_amount_del'], keep=False, inplace=True)
df.drop(columns=['abs_amount_del'], inplace=True)
df['N'] = range(1, len(df) + 1)
return df
def test_exchange_rates_rub():
test_cases = [
(datetime(2015, 1, 15), Money('66.0983', Currency.RUB)),
(datetime(2015, 3, 7), Money('59.9938', Currency.RUB)),
(datetime(2015, 3, 8), Money('59.9938', Currency.RUB)),
(datetime(2020, 3, 31), Money('77.7325', Currency.RUB)),
]
try:
p = ExchangeRatesRUB(year_from=2015, cache_dir=None)
except ConnectionError as ex:
pytest.skip(f'connection error: {ex}')
return
for tc in test_cases:
rate = p.get_rate(tc[0])
assert rate == tc[1], f'{tc[0]}: {rate} != {tc[1]}'
def test_convert_to_rub():
client_usd = ExchangeRatesRUB()
rate_date = datetime(2020, 3, 31)
expected_rate = client_usd.get_rate(Currency.USD, rate_date)
assert expected_rate.amount == Decimal('77.7325')
test_usd = Money(10.98, Currency.USD)
res = client_usd.convert_to_rub(test_usd, rate_date)
assert res.amount == Decimal('853.50285')
assert res.currency == Currency.RUB
test_rub = Money(Decimal('858.3066'), Currency.RUB)
res = client_usd.convert_to_rub(test_rub, rate_date)
assert res.amount == Decimal('858.3066')
assert res.currency == Currency.RUB
def prepare_trades_report(finished_trades: List[FinishedTrade], cbr_client_usd: cbr.ExchangeRatesRUB) -> pandas.DataFrame:
"""
Расчёт расхода/дохода и финансового результата по закрытым сделкам.
Общая методика расчёта расхода/дохода по сделке:
[сумма сделки] * [курс валюты на дату поставки] +/- [сумма комиссии] * [курс валюты на дату сделки]
"""
trade_date_column = 'trade_date'
tax_date_column = 'settle_date'
df = pandas.DataFrame(finished_trades, columns=finished_trades[0].fields)
df[trade_date_column] = df[trade_date_column].dt.normalize()
df[tax_date_column] = pandas.to_datetime(df[tax_date_column])
tax_years = df.groupby('N')[tax_date_column].max().map(lambda x: x.year).rename('tax_year')
df = df.join(tax_years, how='left', on='N')
df['price_rub'] = df.apply(lambda x: cbr_client_usd.convert_to_rub(x['price'], x[tax_date_column]), axis=1)
df['fee_per_piece_rub'] = df.apply(lambda x: cbr_client_usd.convert_to_rub(x['fee_per_piece'], x[trade_date_column]), axis=1)
df['fee'] = df.apply(lambda x: (x['fee_per_piece'] * abs(x['quantity'])), axis=1)
df['total'] = df.apply(
lambda x: compute_total_cost(x['quantity'], x['price'], x['fee_per_piece']),
axis=1,
)
df['total_rub'] = df.apply(
lambda x: compute_total_cost(x['quantity'], x['price_rub'], x['fee_per_piece_rub']),
axis=1,
)
df['settle_rate'] = df.apply(lambda x: cbr_client_usd.get_rate(x['price'].currency, x[tax_date_column]), axis=1)
df['fee_rate'] = df.apply(lambda x: cbr_client_usd.get_rate(x['fee_per_piece'].currency, x[trade_date_column]), axis=1)
df['profit_rub'] = df['total_rub']
profit = df.groupby('N')['profit_rub'].sum().reset_index().set_index('N')
df = df.join(profit, how='left', on='N', lsuffix='_delete')
df.drop(columns=['profit_rub_delete'], axis=0, inplace=True)
df.loc[~df.index.isin(df.groupby('N')[trade_date_column].idxmax()), 'profit_rub'] = Money(0, Currency.RUB)
return df
def test_simple_fees_no_verbose():
fees = [
Fee(date=datetime.datetime(2020, 1, 30, 0, 0),
amount=Money(0.01, Currency.USD),
description='Other Fees'),
Fee(date=datetime.datetime(2020, 1, 30, 0, 0),
amount=Money(-0.01, Currency.USD),
description='Other Fees'),
]
cbr_client = ExchangeRatesRUB()
res: dict = prepare_fees_report(fees, cbr_client, False).to_dict()
assert res['rate'] == {}
assert res['amount_rub'] == {}
def main():
parser = argparse.ArgumentParser()
parser.add_argument('--activity-reports-dir', type=str, required=True, help='directory with InteractiveBrokers .csv activity reports')
parser.add_argument('--confirmation-reports-dir', type=str, required=True, help='directory with InteractiveBrokers .csv confirmation reports')
parser.add_argument('--cache-dir', type=str, default='.', help='directory for caching (CBR RUB exchange rates)')
parser.add_argument('--years', type=lambda x: [int(v.strip()) for v in x.split(',')], default=[], help='comma separated years for final report, omit for all')
args = parser.parse_args()
if os.path.abspath(args.activity_reports_dir) == os.path.abspath(args.confirmation_reports_dir):
print('--activity-reports-dir and --confirmation-reports-dir MUST be different directories')
return
p = InteractiveBrokersReportParser()
activity_reports = csvs_in_dir(args.activity_reports_dir)
confirmation_reports = csvs_in_dir(args.confirmation_reports_dir)
for apath in activity_reports:
print(f'[*] Activity report {apath}')
for cpath in confirmation_reports:
print(f'[*] Confirmation report {cpath}')
print('========' * 8)
print('')
p.parse_csv(
activity_csvs=activity_reports,
trade_confirmation_csvs=confirmation_reports,
)
trades = p.trades()
dividends = p.dividends()
if not trades:
print('no trades found')
return
first_year = min(trades[0].datetime.year, dividends[0].date.year) if dividends else trades[0].datetime.year
cbrates_df = ExchangeRatesRUB(year_from=first_year, cache_dir=args.cache_dir).dataframe()
dividends_report = prepare_dividends_report(dividends, cbrates_df) if dividends else None
portfolio, finished_trades = analyze_trades_fifo(trades)
if finished_trades:
finished_trades_df = pandas.DataFrame(finished_trades, columns=finished_trades[0]._fields) # noqa: WPS437
trades_report = prepare_trades_report(finished_trades_df, cbrates_df)
else:
trades_report = None
show_report(trades_report, dividends_report, portfolio, args.years)
def test_simple_trades():
ticker = Ticker(symbol='VT', kind=TickerKind.Stock)
trades = [
FinishedTrade(N=1,
ticker=ticker,
trade_date=datetime.datetime(2020, 1, 30, 0, 0),
settle_date=datetime.datetime(2020, 2, 3, 0, 0),
quantity=7,
price=Money(80.62, Currency.USD),
fee_per_piece=Money(-0.123375, Currency.USD)),
FinishedTrade(N=1,
ticker=ticker,
trade_date=datetime.datetime(2020, 1, 31, 0, 0),
settle_date=datetime.datetime(2020, 2, 4, 0, 0),
quantity=-7,
price=Money(81.82, Currency.USD),
fee_per_piece=Money('-0.1309628571428571428571428571',
Currency.USD)),
FinishedTrade(N=2,
ticker=ticker,
trade_date=datetime.datetime(2020, 1, 30, 0, 0),
settle_date=datetime.datetime(2020, 2, 3, 0, 0),
quantity=1,
price=Money(80.62, Currency.USD),
fee_per_piece=Money('-0.123375', Currency.USD)),
FinishedTrade(N=2,
ticker=ticker,
trade_date=datetime.datetime(2020, 1, 31, 0, 0),
settle_date=datetime.datetime(2020, 2, 4, 0, 0),
quantity=9,
price=Money(80.62, Currency.USD),
fee_per_piece=Money(-0.1, Currency.USD)),
FinishedTrade(N=2,
ticker=ticker,
trade_date=datetime.datetime(2020, 2, 10, 0, 0),
settle_date=datetime.datetime(2020, 2, 12, 0, 0),
quantity=-10,
price=Money(81.82, Currency.USD),
fee_per_piece=Money('-0.101812674', Currency.USD)),
]
cbr_client = ExchangeRatesRUB()
res: dict = prepare_trades_report(trades, cbr_client).to_dict()
assert res['settle_rate'] == {
0: Money(63.1385, Currency.RUB),
1: Money(63.9091, Currency.RUB),
2: Money(63.1385, Currency.RUB),
3: Money(63.9091, Currency.RUB),
4: Money(63.9490, Currency.RUB)
}
assert res['fee_rate'] == {
0: Money(62.3934, Currency.RUB),
1: Money(63.0359, Currency.RUB),
2: Money(62.3934, Currency.RUB),
3: Money(63.0359, Currency.RUB),
4: Money(63.4720, Currency.RUB)
}
assert res['fee'] == {
0: Money('-0.863625', Currency.USD),
1: Money('-0.9167399999999999999999999997', Currency.USD),
2: Money('-0.123375', Currency.USD),
3: Money('-0.9', Currency.USD),
4: Money('-1.018126740', Currency.USD)
}
assert res['total'] == {
0: Money('-565.203625', Currency.USD),
1: Money('571.82326', Currency.USD),
2: Money('-80.743375', Currency.USD),
3: Money('-726.48', Currency.USD),
4: Money('817.181873260', Currency.USD)
}
assert res['total_rub'] == {
0: Money('-35685.465590075', Currency.RUB),
1: Money('36545.510403034', Currency.RUB),
2: Money('-5097.923655725', Currency.RUB),
3: Money('-46427.897088', Currency.RUB),
4: Money('52258.44925955872', Currency.RUB)
}
assert res['profit_rub'] == {
0: Money(0, Currency.RUB),
1: Money('860.044812959', Currency.RUB),
2: Money(0, Currency.RUB),
3: Money(0, Currency.RUB),
4: Money('732.62851583372', Currency.RUB)
}