def overallbudgetData(): a = sqlqueries.sqloverallbudget() df = pd.read_sql(a, engine, parse_dates='transdate') return returnTable(df)
def budgetData(): a = sqlqueries.sqlbudget() df = pd.read_sql(a, engine) return returnTable(df)
def accountbalancesbyaccount(): a = sqlqueries.sqlcurrentbalancebyaccount() df = pd.read_sql(a, engine, parse_dates='transdate') return returnTable(df)
def owners(): ### return list of owners a = sqlqueries.sqlowners() df = pd.read_sql(a, engine) return returnTable(df)
def sumspendingdata(): a = sqlqueries.sqlSumSpendTable() df = pd.read_sql(a, engine, parse_dates='transdate') return returnTable(df)
def netincomedata(): a = sqlqueries.sqlmonthlyexpenses() string = "" multiplier = "" df = pd.read_sql(a %(string, multiplier), engine, parse_dates='Date') return returnTable(df)
def spendingdata(): a = sqlqueries.sqlmonthlyexpenses() multiplier = "* - 1" string = "WHERE categories.Spending" df = pd.read_sql(a %(multiplier, string), engine, parse_dates='Date') return returnTable(df)
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 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)
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 currentbalancedata(): a = sqlqueries.sqlcurrentbalance() df = pd.read_sql(a, engine, parse_dates='transdate') return returnTable(df)
def owners(): return returnTable(getowners())
def sumstockdata(): a = sqlqueries.sqlSumStockTable() df = pd.read_sql(a, engine, parse_dates='transdate') return returnTable(df)
def accruals(): a = sqlqueries.accruals() df = pd.read_sql(a, engine, parse_dates='transdate') return returnTable(df)
def NIFXdata(): ### returns net income data with fx df = pd.read_sql_table('googlechartsmonthlynetincome', engine, parse_dates='Date') return returnTable(df)