-
Notifications
You must be signed in to change notification settings - Fork 0
/
margin_interest.py
executable file
·60 lines (49 loc) · 1.93 KB
/
margin_interest.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#!/usr/bin/env python3
"""Compare margin interest between CHF and USD."""
import common
import plot
LEDGER_LOAN_BALANCE_CHF = (
f"{common.LEDGER_BIN} -f {common.LEDGER_DAT} -c "
+ r"""--limit 'commodity=~/^CHF$/' -J -E reg ^Assets:Investments:'Interactive Brokers'"""
)
def chf_interest_as_percentage_of_usd():
"""Determine CHF interest paid as a percentage of if USD interest were paid."""
ibkr_rates_df = common.read_sql_table(
"interactive_brokers_margin_rates"
).sort_index()
forex_df = common.read_sql_table("forex").sort_index()[["CHFUSD"]]
merged_df = common.reduce_merge_asof([ibkr_rates_df, forex_df]).dropna()
return merged_df.iloc[-1]["CHF"] / merged_df.iloc[-1]["USD"]
def interest_comparison_df():
"""Get a monthly interest comparison dataframe."""
balance_df = (
plot.load_loan_balance_df(
ledger_loan_balance_cmd=LEDGER_LOAN_BALANCE_CHF,
)
.resample("D")
.last()
.ffill()
)
ibkr_rates_df = common.read_sql_table(
"interactive_brokers_margin_rates"
).sort_index()
forex_df = common.read_sql_table("forex").sort_index()[["CHFUSD"]]
merged_df = common.reduce_merge_asof([balance_df, ibkr_rates_df, forex_df]).dropna()
merged_df["CHF Interest"] = merged_df["Loan Balance"] * (
merged_df["CHF"] / 100 / 365
)
merged_df["CHF Interest in USD"] = merged_df["CHF Interest"] * merged_df["CHFUSD"]
merged_df["USD Interest"] = (merged_df["Loan Balance"] * merged_df["CHFUSD"]) * (
merged_df["USD"] / 100 / 365
)
return merged_df[["CHF Interest in USD", "USD Interest"]].resample("ME").sum()
if __name__ == "__main__":
dataframe = interest_comparison_df()
print("Monthly:")
print(dataframe)
print("\nCumulative:")
print(dataframe.cumsum())
print(
"\nCost of CHF loan as percentage of USD loan: "
+ f"{chf_interest_as_percentage_of_usd()*100:.2f}%"
)