def findFX(df):
df[['nativebalance','USbalance','CAbalance']] = np.cumsum(df.groupby(['Owner','Currency'])[['Native Amount','USD Amount','CAD Amount']])
### total of (all transactions in native currency multiplied by transaction date rate)
df['USDbalance'] = df.apply(lambda row: convert(row['nativebalance'], row['Currency'], row['Rate']), axis=1)
df['CADbalance'] = df['USDbalance'] * df['Rate']
## total of all transactions in native currency converted at ending rate
df['USFX'] = df.USDbalance - df.USbalance
df['CADFX'] = df.CADbalance - df.CAbalance
df = df[['transdate','Owner','Currency','USFX','CADFX']]
df['transdate'] = pd.DatetimeIndex(df['transdate'])
df['PrevDate'] = pd.DatetimeIndex(df['transdate']) + pd.offsets.MonthEnd(-1)
df.iloc[0:4,3:5] = 0.0 ### change first three balances to zero. Needed for pad filling step below.
df = df.sort(['Owner','Currency','transdate','PrevDate']).fillna(method='pad')
df = pd.merge(df, df, how='left', left_on=['PrevDate','Owner','Currency'], right_on=['transdate','Owner','Currency'])
df['FXUSD'] = df.USFX_x - df.USFX_y
df['FXCAD'] = df.CADFX_x - df.CADFX_y
df = df[['transdate_x','Owner','Currency','FXUSD','FXCAD']]
df = df.sort(['Owner','Currency','transdate_x']).fillna(method='pad')
df['Category'] = "FX Gain/Loss"
df.columns = ['Date','Owner','Currency','USD Amount','CAD Amount','Category']
df = df.groupby(['Date','Owner','Category'])['USD Amount','CAD Amount'].sum().reset_index()
return df
def totalbalances():
### inserts daily balance data for all accounts to database
from datetime import datetime
a = sqlqueries.sqltotalbalances() ### bankaccounts, transactions, dates, fxrates
df = pd.read_sql(a, engine, parse_dates='transdate')
df['amount'] = df['amount'].fillna(0)
df['balance'] = np.cumsum(df.groupby(['AccountName'])['amount']) # adds column of running total balances
df = df[df['balance'] != 0 ] # removes zero balances which should be balances before account started
df = df.sort('transdate')
df = df[df['transdate'] <= datetime.today()] # removes any future dates
df['USDAmount'] = df.apply(lambda row: convert(row['balance'],row['Currency'],row['Rate']), axis=1)
df['CADAmount'] = df.USDAmount * df.Rate
df.balance = df.balance.round(2)
df.USDAmount = df.USDAmount.round(2)
df.CADAmount = df.CADAmount.round(2)
df.to_sql('balances', engine, if_exists = 'replace', index=False)
