def balanceData():
a = sqlqueries.sqlmonthlybalances() ### bankaccounts, balances
df = pd.read_sql(a, engine, parse_dates='transdate')
df = pd.pivot_table(df, index=['transdate','owner', 'FXRate'],values=["balance"],columns=['AccountName'],fill_value=0).reset_index()
### takes daily balance data and returns dataframe with each account as separate column
droplevel(df) # adjusts column names that occurred from pivoting
return returnTable(df)
def sumstockPricesOriginalData():
a = sqlqueries.sqlSumStockData()
df = pd.read_sql(a, engine, parse_dates='transdate')
df = pd.pivot_table(df, index=['transdate','owner', 'FXRate'],values=["Price"],columns=['symbol']).reset_index()
df = df.fillna(0)
droplevel(df)
return returnTable(df)
def stockPricesData():
a = sqlqueries.sqlstocksprices()
df = pd.read_sql(a, engine, parse_dates='transdate')
df = pd.pivot_table(df, index=['transdate','owner', 'FXRate'],values=["Price"],columns=['symbol']).reset_index()
for owner in owners:
df[df.owner==owner] = df[df.owner==owner].sort(['transdate']).fillna(method='pad')
df = df.fillna(0)
droplevel(df)
return returnTable(df)
def stockData():
a = sqlqueries.sqlstockgain()
df = pd.read_sql(a, engine, parse_dates='transdate')
df['Gain/Loss'] = np.cumsum(df.groupby(['owner', 'description'])['Gain/Loss'])
df = pd.pivot_table(df, index=['transdate','owner', 'FXRate'],values=["Gain/Loss"],columns=['description']).reset_index()
for owner in owners:
df[df.owner==owner] = df[df.owner==owner].sort(['transdate']).fillna(method='pad')
df = df.fillna(0)
droplevel(df)
return returnTable(df)
def sumstockPricesData():
a = sqlqueries.sqlSumStockData()
df = pd.read_sql(a, engine, parse_dates='transdate')
df = pd.pivot_table(df, index=['transdate','owner', 'FXRate'],values=["Price"],columns=['symbol']).reset_index()
droplevel(df)
df4 = df
df4 = df4.iloc[:2,3:]
df4 = pd.DataFrame(df4.sum())
df3 = df.iloc[:,3:]
initial = df3.ix[0:1]
initial = initial.sum()
df2 = df3.divide(initial / 100)
df.iloc[:,3:] = df2
df4 = df4.reset_index()
df4.columns = ['Stock','Price']
df = df.fillna(0)
return returnTable(df), returnTable(df4)
