def create_asset_distribution_plot(self):
self.matplotlibWidget2.axis.clear()
def make_autopct(values):
def my_autopct(pct):
total = sum(values)
val = int(round(pct * total / 100.0))
return '{v:d} €'.format(v=val)
return my_autopct
date = self.dateEdit_3.date().toPyDate()
date = pd.to_datetime(date)
df = Dbm.get_spot_portfolio_valuation(pd.to_datetime(date))
df = df.drop(labels=['Date', 'Total Value'], axis=1)
df = df.loc[:, (df != 0).any(axis=0)]
for i in df.columns:
df.loc[:, i] = round(df.loc[:, i], 3)
labels = list(df.columns)
data = list(df.values[0, :])
self.matplotlibWidget2.axis.pie(data,
wedgeprops=dict(width=0.3,
linewidth=7,
edgecolor='white'),
startangle=90,
shadow=False,
labels=labels,
autopct=make_autopct(data))
self.matplotlibWidget2.canvas.draw()
def connector_performance_viewer(self, dates: list):
'''crates a series which can be appended to the performance_viewer DataFrame'''
start = dates[0]
end = dates[-1]
series = Dbm.get_portfolio_valuation(start, end)
normalized = self.normalize_prices_simp(df=series)
return normalized
def __init__(self, top):
# create widgets
self.top = top
samples = tk.StringVar()
top.title('Transactions')
stocks = ttk.Combobox(top, textvariable=samples)
names = Dtm.get_all_names()
stocks['values'] = list(names)
entry_date = tk.Entry(top) # Date
entry_amount = tk.Entry(top) # Amount
entry_price = tk.Entry(top) # Price
entry_fees = tk.Entry(top) # Fees
entry_tax = tk.Entry(top) # Tax
label_security = tk.Label(top, text="Security")
label_date = tk.Label(top, text="Date")
label_amount = tk.Label(top, text="Amount")
label_price = tk.Label(top, text=" x Price")
label01 = tk.Label(top, text="EUR =")
label_fees = tk.Label(top, text=" + Fees")
label_tax = tk.Label(top, text=" + tax")
label_tot_expense = tk.Label(top, text="Total Expense")
labelEUR01 = tk.Label(top, text="EUR")
labelEUR02 = tk.Label(top, text="EUR")
labelEUR03 = tk.Label(top, text="EUR")
labelEUR04 = tk.Label(top, text="EUR")
# place widgets
stocks.grid(row=1, column=2, columnspan=3)
label_security.grid(row=1, column=1)
label_date.grid(row=2, column=1)
label_amount.grid(row=3, column=1)
label_price.grid(row=3, column=3)
label01.grid(row=3, column=5)
label_fees.grid(row=5, column=5)
label_tax.grid(row=6, column=5)
label_tot_expense.grid(row=7, column=5)
entry_date.grid(row=2, column=2)
entry_amount.grid(row=3, column=2)
entry_price.grid(row=3, column=4)
entry_fees.grid(row=5, column=6)
entry_tax.grid(row=6, column=6)
labelEUR01.grid(row=3, column=7)
labelEUR02.grid(row=5, column=7)
labelEUR03.grid(row=6, column=7)
labelEUR04.grid(row=7, column=7)
def save_and_upload(self):
date = self.dateEdit.date().toPyDate()
amount = self.amount_entry.text()
fees = self.fees_entry.text()
tax = self.tax_entry.text()
typ = ''
if self.radioButtonDebit.isChecked() is True:
typ = 'Debit'
elif self.radioButtonCredit.isChecked() is True:
typ = 'Credit'
# TODO ELSE: create popup to warn for not selected transaction typ
notes = self.notes.toPlainText()
Dbm.upload_cash_transaction(date, typ, amount, fees, tax, notes)
print('upload complete')
def save_and_upload(self):
date = self.dateEdit.date().toPyDate()
name = self.comboBox.currentText()
price = self.price_entry.text()
amount = self.amount_entry.text()
fees = self.fees_entry.text()
tax = self.tax_entry.text()
typ = ''
if self.Buy_button.isChecked() is True:
typ = 'Buy'
elif self.Sell_button.isChecked() is True:
typ = 'Sell'
# TODO ELSE: create popup to warn for not selected transaction typ
notes = self.notes.toPlainText()
Dbm.upload_security_transaction(date, name, typ, amount, price, fees,
tax, notes)
print('upload complete')
def __init__(self):
# create basic properties of the class
self.assets = []
self.cash_actions = Dbm.get_cash_transactions()
self.stats = pd.DataFrame(columns=['Asset', 'Amount', 'Position'])
self.fund_states = pd.DataFrame(columns=['Date', 'ID'])
self.transactions_list = Dbm.read_transactions()
self.transactions_list['Transaction_ID'] = np.array(
range(0, len(self.transactions_list['Date'])))
self.cash_actions['Transaction_ID'] = np.array(
range(1000,
len(self.cash_actions['Date']) + 1000))
self.contents = pd.DataFrame(columns=[
'Date', 'Buy/Sell', 'Asset', 'Amount', 'Price', 'Fees', 'ID'
])
self.spot_amounts = pd.DataFrame(
columns=['Asset', 'Amount', 'Position'])
# start doing calculations and create basic structure of the portfolio data
self.create_fund_states()
# self.map_transactions()
# self.map_start_cash()
# self.initialize_stats()
# self.fill_stats()
# master most recent all star inclusions all most all relevant information included
self.combined_transactions = self.merge_transactions()
self.portfolio = self.form_portfolio()
# self.amount_schedule = self.create_amount_schedule(list(self.fund_states['Date']))
# characteristics
self.portfolio_opening = self.portfolio_start().values[0]
# valuation of all positions
# TODO Valuation of Alternatives is still to high because losses ar not properly integrated
self.portfolio_valuation = self.create_portfolio_value()
self.upload_valuation()
def create_portfolio_value(self, dates: list = None):
if dates is None:
dates = self.daterange_opening()
data_request = list(
filter(lambda x: x != 'Alternate' and x != 'Cash' and x != 'Date',
self.portfolio.columns))
names = data_request
prices = pd.DataFrame()
# create a DataFrame with columns being the different names
for name in data_request:
df = Dbm.get_prices(name, startdate=dates[0], enddate=dates[-1])
df['Date'] = pd.to_datetime(df['Date'])
df = df.set_index('Date')
prices = pd.concat([prices, df], ignore_index=True, axis=1)
structure = self.create_amount_schedule(dates=dates,
date_is_index=True)
# basic = basic.apply(lambda x: int(x))
prices.columns = names
prices = prices.fillna(method='bfill')
# prices = Cmp.test_gaps(prices)
for i in data_request:
structure.loc[:, i] = structure[i].multiply(prices[i])
# structure = structure[''] * prices
structure.loc[:,
['Alternate']] = structure.loc[:,
['Alternate']].apply(abs)
# structure = structure.drop('Alternate', axis=1)
# create sum of value column
structure = structure.fillna(method='ffill')
data = structure.values
sums = np.nansum(data, axis=1)
with_sums = np.concatenate((data.T, np.array([sums])), axis=0).T
columns = list(structure.columns) + ['Total Value']
portfolio_value_dist = pd.DataFrame(data=with_sums,
columns=columns,
index=structure.index)
portfolio_value_dist = portfolio_value_dist.fillna(method='ffill')
return portfolio_value_dist
def get_dataframe(self, names, startdate, enddate):
basic = pd.DataFrame()
# create a DataFrame with columns being the different names
for name in names:
df = Dbm.get_prices(name, startdate=startdate, enddate=enddate)
df['Date'] = pd.to_datetime(df['Date'])
df = df.set_index('Date')
basic = pd.concat([basic, df], ignore_index=True, axis=1)
basic.columns = names
normalized = Cmp.normalize_prices(basic)
dates = list(normalized.index)
portfolio = self.portfolio.connector_performance_viewer(dates=dates)
portfolio.columns = ['Porfolio']
combined = pd.concat([normalized, portfolio], axis=1)
combined = combined.fillna(method='ffill')
return combined
def alternate_amount(self, date):
mask = (self.combined_transactions['Date'] <= pd.to_datetime(date))
transactions = self.combined_transactions.loc[mask]
mask = (transactions['Security'] == 'Alternate')
interim = transactions.loc[mask]
# adjust bookings
interim = interim.set_index('Date')
change = interim.loc[:, ['Change']].sum()
# adjust bookings
extras = Dbm.get_alternative_investments()
extras = extras.loc[:, ['Date', 'NetChange']]
extras.loc[:, ['Date']] = extras.loc[:, ['Date']].apply(pd.to_datetime)
mask2 = (extras['Date'] <= pd.to_datetime(date))
extras = extras.loc[mask2]
adjustment = extras.loc[:, ['NetChange']].sum()
adj_change = float(change) - float(adjustment)
return adj_change
def merge_transactions(self):
frame = pd.DataFrame(
columns=['Date', 'Security', 'In/Out', 'Change', 'Fees', 'Tax'])
cash = Dbm.get_cash_transactions()
stocks = Dbm.get_transactions()
alter = Dbm.get_alternative_investments()
# get the essentials
cash = cash.loc[:, ['Date', 'Debit/Credit', 'Amount', 'Fees', 'Tax']]
stocks = stocks.loc[:, [
'Date', 'Security', 'Buy/Sell', 'Amount', 'Price', 'Fees', 'Tax'
]]
# same width
stocks.loc[:, 'Change'] = stocks['Amount'] * stocks['Price']
stocks = stocks.drop(['Amount', 'Price'], axis=1)
# uniform increase decrease
stocks.loc[:, 'In/Out'] = stocks['Buy/Sell'].apply(self.true_false)
cash.loc[:, 'In/Out'] = cash['Debit/Credit'].apply(self.true_false)
alter.loc[:, 'In/Out'] = alter['Buy/Sell'].apply(self.true_false)
cash.loc[:, 'Change'] = cash['Amount']
alter.loc[:, 'Change'] = alter['Amount']
cash.loc[:, 'Security'] = 'Cash'
alter.loc[:, 'Security'] = 'Alternate'
# order the columns [Date, Buy/Sell, Change, Fees, tax]
stocks = stocks.loc[:, [
'Date', 'Security', 'In/Out', 'Change', 'Fees', 'Tax'
]]
cash = cash.loc[:, [
'Date', 'Security', 'In/Out', 'Change', 'Fees', 'Tax'
]]
alter = alter.loc[:, [
'Date', 'Security', 'In/Out', 'Change', 'Fees', 'Tax'
]]
frame = frame.append(stocks, ignore_index=True)
frame = frame.append(cash, ignore_index=True)
frame = frame.append(alter, ignore_index=True)
# comparison is used because 'if cond is True' gives ERROR
buy_mask = (frame['In/Out'] == True)
buys = frame.loc[buy_mask].copy()
buys.loc[:, ['Change']] = buys['Change'].apply(self.turn_negative)
sell_mask = (frame['In/Out'] == False)
sells = frame.loc[sell_mask]
frame = pd.DataFrame(
columns=['Date', 'Security', 'In/Out', 'Change', 'Fees', 'Tax'])
frame = frame.append(buys, ignore_index=True)
frame = frame.append(sells, ignore_index=True)
frame.loc[:, ['Date']] = pd.to_datetime(frame['Date'])
frame = frame.sort_values(by='Date')
frame = frame.reset_index(drop=True)
return frame
def upload_valuation(self):
Dbm.upload_portfolio_valuation(self.portfolio_valuation)
def get_spot_valuation(date: str = '2018-06-07'):
'''returns a dataframe with position name in columns and corresponding value for the inserted date'''
# date will be converted to datetime to be recognized in the sql-query
data = Dbm.get_spot_portfolio_valuation(pd.to_datetime(date))
return data
def cbox2_options(self):
# use get stock names function from DatabaseManagement
self.comboBox_2.addItems(Dbm.get_index_names())
def cbox_options(self):
# use get stock names function from DatabaseManagement
options = Dbm.get_stock_names()
self.comboBox.addItems(options)