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)