def transactions_fx():
# Gets the transaction table and fills with fx information
# Note that it uses the currency exchange for the date of transaction
# Get all transactions from db and format
df = pd.read_sql_table('trades', db.engine)
df = df[(df.user_id == current_user.username)]
# df = df[(df.trade_operation == "B") | (df.trade_operation == "S")]
df['trade_date'] = pd.to_datetime(df['trade_date'])
df = df.set_index('trade_date')
# Ignore times in df to merge - keep only dates
df.index = df.index.floor('d')
df.index.rename('date', inplace=True)
# The current fx needs no conversion, set to 1
df[current_user.fx()] = 1
# Need to get currencies into the df in order to normalize
# let's load a list of currencies needed and merge
list_of_fx = df.trade_currency.unique().tolist()
# loop through currency list
for currency in list_of_fx:
if currency == current_user.fx():
continue
# Make a price request
df[currency] = df.apply(find_fx, axis=1)
# Now create a cash value in the preferred currency terms
df['fx'] = df.apply(lambda x: x[x['trade_currency']], axis=1)
df['cash_value_fx'] = df['cash_value'].astype(float) / df['fx'].astype(
float)
df['trade_fees_fx'] = df['trade_fees'].astype(float) / df['fx'].astype(
float)
df['trade_price_fx'] = df['trade_price'].astype(float) / df['fx'].astype(
float)
return (df)
def navchart():
data = generatenav(current_user.username)
navchart = data[["NAV_fx"]]
# dates need to be in Epoch time for Highcharts
navchart.index = (navchart.index - datetime(1970, 1, 1)).total_seconds()
navchart.index = navchart.index * 1000
navchart.index = navchart.index.astype(np.int64)
navchart = navchart.to_dict()
navchart = navchart["NAV_fx"]
port_value_chart = data[[
"PORT_cash_value_fx", "PORT_fx_pos", "PORT_ac_CFs_fx"
]]
port_value_chart["ac_pnl_fx"] = (port_value_chart["PORT_fx_pos"] -
port_value_chart["PORT_ac_CFs_fx"])
# dates need to be in Epoch time for Highcharts
port_value_chart.index = (port_value_chart.index -
datetime(1970, 1, 1)).total_seconds()
port_value_chart.index = port_value_chart.index * 1000
port_value_chart.index = port_value_chart.index.astype(np.int64)
port_value_chart = port_value_chart.to_dict()
return render_template("navchart.html",
title="NAV Historical Chart",
navchart=navchart,
port_value_chart=port_value_chart,
fx=fxsymbol(current_user.fx()))
def account_positions():
transactions = Trades.query.filter_by(user_id=current_user.username)
if transactions.count() == 0:
return render_template("empty.html")
df = pd.read_sql_table("trades", db.engine)
df = df[(df.user_id == current_user.username)]
df["trade_date"] = pd.to_datetime(df["trade_date"])
account_table = df.groupby(["trade_account", "trade_asset_ticker"
])[["trade_quantity"]].sum()
# All accounts
all_accounts = (account_table.query(
"trade_asset_ticker != '" + current_user.fx() +
"'").index.get_level_values("trade_account").unique().tolist())
# Trim the account list only for accounts that currently hold a position
account_table = account_table[account_table.trade_quantity != 0]
# Remove accounts with USD only Positions
account_table = account_table.query("trade_asset_ticker != 'USD'")
# account_table = account_table['trade_asset_ticker' != 'USD']
accounts = account_table.index.get_level_values(
"trade_account").unique().tolist()
tickers = (account_table.index.get_level_values(
"trade_asset_ticker").unique().tolist())
# if 'USD' in tickers:
# tickers.remove('USD')
return render_template(
"account_positions.html",
title="Account Positions",
accounts=accounts,
tickers=tickers,
account_table=account_table,
all_accounts=all_accounts,
)
def price_data_fx(ticker):
for provider in HISTORICAL_PROVIDER_PRIORITY:
price_data = PriceData(ticker, PROVIDER_LIST[provider])
if price_data.df is not None:
break
# Loop through FX providers until a df is filled
for provider in FX_PROVIDER_PRIORITY:
prices = price_data.df_fx(current_user.fx(), PROVIDER_LIST[provider])
if prices is not None:
break
return (prices)
def fx_rate():
from thewarden.users.utils import fxsymbol
# This grabs the realtime current currency conversion against USD
print(price_data_rt(current_user.fx(), FX_RT_PROVIDER_PRIORITY))
try:
# get fx rate
rate = {}
rate['base'] = current_user.fx()
rate['symbol'] = fxsymbol(current_user.fx())
rate['name'] = fxsymbol(current_user.fx(), 'name')
rate['name_plural'] = fxsymbol(current_user.fx(), 'name_plural')
rate['cross'] = "USD" + " / " + current_user.fx()
try:
rate['fx_rate'] = 1 / (float(
price_data_rt(current_user.fx(), FX_RT_PROVIDER_PRIORITY)))
except Exception:
rate['fx_rate'] = 1
except Exception as e:
rate = {}
rate['error'] = (f"Error: {e}")
rate['fx_rate'] = 1
return (rate)
def price_data_rt_full(ticker, provider):
# Function to get a complete data set for realtime prices
# Loop through the providers to get the following info:
# price, chg, high, low, volume, mkt cap, last_update, source
# For some specific assets, a field 'note' can be passed and
# will replace volume and market cap at the main page
# ex: GBTC premium can be calculated here
# returns a list with the format:
# price, last_update, high, low, chg, mktcap,
# last_up_source, volume, source, notes
# All data returned in USD
# -----------------------------------------------------------
# This function is used to grab a single price that was missing from
# the multiprice request. Since this is a bit more time intensive, it's
# separated so it can be memoized for a period of time (this price will
# not refresh as frequently)
# default: timeout=30
if provider == 'cc':
multi_price = multiple_price_grab(ticker, 'USD,' + current_user.fx())
try:
# Parse the cryptocompare data
price = multi_price["RAW"][ticker][current_user.fx()]["PRICE"]
price = float(price * current_user.fx_rate_USD())
high = float(
multi_price["RAW"][ticker][current_user.fx()]["HIGHDAY"] *
current_user.fx_rate_USD())
low = float(
multi_price["RAW"][ticker][current_user.fx()]["LOWDAY"] *
current_user.fx_rate_USD())
chg = multi_price["RAW"][ticker][
current_user.fx()]["CHANGEPCT24HOUR"]
mktcap = multi_price["DISPLAY"][ticker][
current_user.fx()]["MKTCAP"]
volume = multi_price["DISPLAY"][ticker][
current_user.fx()]["VOLUME24HOURTO"]
last_up_source = multi_price["RAW"][ticker][
current_user.fx()]["LASTUPDATE"]
source = multi_price["DISPLAY"][ticker][
current_user.fx()]["LASTMARKET"]
last_update = datetime.now()
notes = None
return (price, last_update, high, low, chg, mktcap, last_up_source,
volume, source, notes)
except Exception:
return (None)
if provider == 'aa':
try:
globalURL = 'https://www.alphavantage.co/query?function=GLOBAL_QUOTE&apikey='
globalURL += api_keys['alphavantage'][
'api_key'] + '&symbol=' + ticker
data = tor_request(globalURL).json()
price = float(data['Global Quote']
['05. price']) * current_user.fx_rate_USD()
high = float(
data['Global Quote']['03. high']) * current_user.fx_rate_USD()
low = float(
data['Global Quote']['04. low']) * current_user.fx_rate_USD()
chg = data['Global Quote']['10. change percent'].replace('%', '')
try:
chg = float(chg)
except Exception:
chg = chg
mktcap = '-'
volume = '-'
last_up_source = '-'
last_update = '-'
source = 'Alphavantage'
notes = None
# Start Notes methods for specific assets. For example, for
# GBTC we report the premium to BTC
if ticker == 'GBTC':
fairvalue, premium = GBTC_premium(
float(data['Global Quote']['05. price']))
fairvalue = "{0:,.2f}".format(fairvalue)
premium = "{0:,.2f}".format(premium * 100)
notes = f"Fair Value: {fairvalue}<br>Premium: {premium}%"
return (price, last_update, high, low, chg, mktcap, last_up_source,
volume, source, notes)
except Exception:
return None
if provider == 'fp':
try:
globalURL = 'https://financialmodelingprep.com/api/v3/stock/real-time-price/'
globalURL += ticker
data = tor_request(globalURL).json()
price = float(data['price']) * current_user.fx_rate_USD()
high = '-'
low = '-'
chg = 0
mktcap = '-'
volume = '-'
last_up_source = '-'
last_update = '-'
source = 'FP Modeling API'
notes = None
return (price, last_update, high, low, chg, mktcap, last_up_source,
volume, source, notes)
except Exception:
return None
def generatenav(user, force=False, filter=None):
logging.info(f"[generatenav] Starting NAV Generator for user {user}")
# Portfolios smaller than this size do not account for NAV calculations
# Otherwise, there's an impact of dust left in the portfolio (in USD)
# This is set in config.ini file
min_size_for_calc = int(PORTFOLIO_MIN_SIZE_NAV)
logging.info(f"[generatenav] Force update status is {force}")
save_nav = True
# This process can take some time and it's intensive to run NAV
# generation every time the NAV is needed. A compromise is to save
# the last NAV generation locally and only refresh after a period of time.
# This period of time is setup in config.ini as RENEW_NAV (in minutes).
# If last file is newer than 60 minutes (default), the local saved file
# will be used.
# Unless force is true, then a rebuild is done regardless
# Local files are saved under a hash of username.
if force:
usernamehash = hashlib.sha256(
current_user.username.encode('utf-8')).hexdigest()
filename = "thewarden/nav_data/" + usernamehash + current_user.fx(
) + ".nav"
filename = os.path.join(current_path(), filename)
# Since this function can be run as a thread, it's safer to delete
# the current NAV file if it exists. This avoids other tasks reading
# the local file which is outdated
try:
os.remove(filename)
except OSError:
logging.info("[generatenav] Local NAV file was not found" +
" for removal - continuing")
if not force:
usernamehash = hashlib.sha256(
current_user.username.encode('utf-8')).hexdigest()
filename = "thewarden/nav_data/" + usernamehash + current_user.fx(
) + ".nav"
filename = os.path.join(current_path(), filename)
try:
# Check if NAV saved file is recent enough to be used
# Local file has to have a saved time less than RENEW_NAV min old
# See config.ini to change RENEW_NAV
modified = datetime.utcfromtimestamp(os.path.getmtime(filename))
elapsed_seconds = (datetime.utcnow() - modified).total_seconds()
logging.info(f"Last time file was modified {modified} is " +
f" {elapsed_seconds} seconds ago")
if (elapsed_seconds / 60) < int(RENEW_NAV):
nav_pickle = pd.read_pickle(filename)
return (nav_pickle)
else:
logging.info("File found but too old - rebuilding NAV")
except FileNotFoundError:
logging.info(f"[generatenav] File not found to load NAV" +
" - rebuilding")
# Pandas dataframe with transactions
df = transactions_fx()
# if a filter argument was passed, execute it
if filter:
df = df.query(filter)
logging.info("[generatenav] Success - read trades from database")
start_date = df.index.min() - timedelta(
days=1) # start on t-1 of first trade
end_date = datetime.today()
# Create a list of all tickers that were traded in this portfolio
tickers = df.trade_asset_ticker.unique().tolist()
# Create an empty DF, fill with dates and fill with operation and prices then NAV
dailynav = pd.DataFrame(columns=['date'])
# Fill the dates from first trade until today
dailynav['date'] = pd.date_range(start=start_date, end=end_date)
dailynav = dailynav.set_index('date')
# Create empty fields
dailynav['PORT_usd_pos'] = 0
dailynav['PORT_fx_pos'] = 0
dailynav['PORT_cash_value'] = 0
dailynav['PORT_cash_value_fx'] = 0
# Create a dataframe for each position's prices
for id in tickers:
if is_currency(id):
continue
try:
# Create a new PriceData class for this ticker
prices = price_data_fx(id)
if prices is None:
logging.error(f"Could not get a price for {id}")
save_nav = False
raise ValueError
prices = prices.rename(columns={'close_converted': id + '_price'})
prices = prices[id + '_price']
# Fill dailyNAV with prices for each ticker
dailynav = pd.merge(dailynav, prices, on='date', how='left')
# Replace NaN with prev value, if no prev value then zero
dailynav[id + '_price'].fillna(method='ffill', inplace=True)
dailynav[id + '_price'].fillna(0, inplace=True)
# Now let's find trades for this ticker and include in dailynav
tradedf = df[[
'trade_asset_ticker', 'trade_quantity', 'cash_value_fx'
]]
# Filter trades only for this ticker
tradedf = tradedf[tradedf['trade_asset_ticker'] == id]
# consolidate all trades in a single date Input
tradedf = tradedf.groupby(level=0).sum()
tradedf.sort_index(ascending=True, inplace=True)
# include column to cumsum quant
tradedf['cum_quant'] = tradedf['trade_quantity'].cumsum()
# merge with dailynav - 1st rename columns to match
tradedf.index.rename('date', inplace=True)
# rename columns to include ticker name so it's differentiated
# when merged with other ids
tradedf.rename(columns={
'trade_quantity': id + '_quant',
'cum_quant': id + '_pos',
'cash_value_fx': id + '_cash_value_fx'
},
inplace=True)
# merge
dailynav = pd.merge(dailynav, tradedf, on='date', how='left')
# for empty days just trade quantity = 0, same for CV
dailynav[id + '_quant'].fillna(0, inplace=True)
dailynav[id + '_cash_value_fx'].fillna(0, inplace=True)
# Now, for positions, fill with previous values, NOT zero,
# unless there's no previous
dailynav[id + '_pos'].fillna(method='ffill', inplace=True)
dailynav[id + '_pos'].fillna(0, inplace=True)
# Calculate USD and fx position and % of portfolio at date
# Calculate USD position and % of portfolio at date
dailynav[id + '_fx_pos'] = dailynav[id + '_price'].astype(
float) * dailynav[id + '_pos'].astype(float)
# Before calculating NAV, clean the df for small
# dust positions. Otherwise, a portfolio close to zero but with
# 10 sats for example, would still have NAV changes
dailynav[id + '_fx_pos'].round(2)
logging.info(f"Success: imported prices for id:{id}")
except (FileNotFoundError, KeyError, ValueError) as e:
logging.error(f"{id}: Error: {e}")
flash(
f"Ticker {id} generated an error. " +
f"NAV calculations will be off. Error: {e}", "danger")
# Another loop to sum the portfolio values - maybe there is a way to
# include this on the loop above. But this is not a huge time drag unless
# there are too many tickers in a portfolio
for id in tickers:
if is_currency(id):
continue
# Include totals in new columns
try:
dailynav['PORT_fx_pos'] = dailynav['PORT_fx_pos'] +\
dailynav[id + '_fx_pos']
except KeyError as e:
logging.error(f"[GENERATENAV] Ticker {id} was not found " +
"on NAV Table - continuing but this is not good." +
" NAV calculations will be erroneous.")
save_nav = False
flash(
f"Ticker {id} was not found on NAV table. " +
f"NAV calculations will be off. Error: {e}", "danger")
continue
dailynav['PORT_cash_value_fx'] = dailynav['PORT_cash_value_fx'] +\
dailynav[id + '_cash_value_fx']
# Now that we have the full portfolio value each day, calculate alloc %
for id in tickers:
if is_currency(id):
continue
try:
dailynav[id + "_fx_perc"] = dailynav[id + '_fx_pos'] /\
dailynav['PORT_fx_pos']
dailynav[id + "_fx_perc"].fillna(0, inplace=True)
except KeyError:
continue
# Create a new column with the portfolio change only due to market move
# discounting all cash flows for that day
dailynav['adj_portfolio_fx'] = dailynav['PORT_fx_pos'] -\
dailynav['PORT_cash_value_fx']
# For the period return let's use the Modified Dietz Rate of return method
# more info here: https://tinyurl.com/y474gy36
# There is one caveat here. If end value is zero (i.e. portfolio fully
# redeemed, the formula needs to be adjusted)
dailynav.loc[dailynav.PORT_fx_pos > min_size_for_calc,
'port_dietz_ret_fx'] =\
((dailynav['PORT_fx_pos'] -
dailynav['PORT_fx_pos'].shift(1)) -
dailynav['PORT_cash_value_fx']) /\
(dailynav['PORT_fx_pos'].shift(1) +
abs(dailynav['PORT_cash_value_fx']))
# Fill empty and NaN with zero
dailynav['port_dietz_ret_fx'].fillna(0, inplace=True)
dailynav['adj_port_chg_fx'] = (
(dailynav['PORT_fx_pos'] - dailynav['PORT_fx_pos'].shift(1)) -
dailynav['PORT_cash_value_fx'])
# let's fill NaN with zeros
dailynav['adj_port_chg_fx'].fillna(0, inplace=True)
# Calculate the metrics
dailynav['port_perc_factor_fx'] = (dailynav['port_dietz_ret_fx']) + 1
dailynav['NAV_fx'] = dailynav['port_perc_factor_fx'].cumprod()
dailynav['NAV_fx'] = dailynav['NAV_fx'] * 100
dailynav['PORT_ac_CFs_fx'] = dailynav['PORT_cash_value_fx'].cumsum()
logging.info(f"[generatenav] Success: NAV Generated for user {user}")
# Save NAV Locally as Pickle
if save_nav:
usernamehash = hashlib.sha256(
current_user.username.encode('utf-8')).hexdigest()
filename = "thewarden/nav_data/" + usernamehash + current_user.fx(
) + ".nav"
filename = os.path.join(current_path(), filename)
os.makedirs(os.path.dirname(filename), exist_ok=True)
dailynav.to_pickle(filename)
logging.info(f"[generatenav] NAV saved to {filename}")
return dailynav
def find_data(ticker):
notes = None
try:
# Parse the cryptocompare data
price = multi_price["RAW"][ticker][current_user.fx()]["PRICE"]
price = float(price)
high = float(
multi_price["RAW"][ticker][current_user.fx()]["HIGHDAY"])
low = float(
multi_price["RAW"][ticker][current_user.fx()]["LOWDAY"])
chg = multi_price["RAW"][ticker][
current_user.fx()]["CHANGEPCT24HOUR"]
mktcap = multi_price["DISPLAY"][ticker][
current_user.fx()]["MKTCAP"]
volume = multi_price["DISPLAY"][ticker][
current_user.fx()]["VOLUME24HOURTO"]
last_up_source = multi_price["RAW"][ticker][
current_user.fx()]["LASTUPDATE"]
source = multi_price["DISPLAY"][ticker][
current_user.fx()]["LASTMARKET"]
last_update = datetime.now()
except (KeyError, TypeError):
# Couldn't find price with CryptoCompare. Let's try a different source
# and populate data in the same format [aa = alphavantage]
try:
single_price = price_data_rt_full(ticker, 'aa')
if single_price is None:
raise KeyError
price = single_price[0]
high = single_price[2]
low = single_price[3]
(_, last_update, _, _, chg, mktcap, last_up_source, volume,
source, notes) = single_price
except Exception:
# Let's try a final time using Financial Modeling Prep API
try:
single_price = price_data_rt_full(ticker, 'fp')
if single_price is None:
raise KeyError
price = single_price[0]
high = single_price[2]
low = single_price[3]
(_, last_update, _, _, chg, mktcap, last_up_source, volume,
source, notes) = single_price
except Exception:
try:
# Finally, if realtime price is unavailable, find the latest
# saved value in historical prices
# Create a price class
price_class = price_data(ticker)
if price_class is None:
raise KeyError
price = float(price_class.df['close'].iloc[0]
) * current_user.fx_rate_USD()
high = float(price_class.df['high'].iloc[0]
) * current_user.fx_rate_USD()
low = float(price_class.df['low'].iloc[0]
) * current_user.fx_rate_USD()
volume = current_user.fx() + " " + "{0:,.0f}".format(
float(price_class.df['volume'].iloc[0]) *
current_user.fx_rate_USD())
mktcap = chg = 0
source = last_up_source = 'Historical Data'
last_update = price_class.df.index[0]
except Exception as e:
price = high = low = chg = mktcap = last_up_source = last_update = volume = 0
source = '-'
logging.error(
f"There was an error getting the price for {ticker}."
+ f"Error: {e}")
return price, last_update, high, low, chg, mktcap, last_up_source, volume, source, notes
def positions_dynamic():
# This method is the realtime updater for the front page. It gets the
# position information from positions above and returns a dataframe
# with all the realtime pricing and positions data - this method
# should be called from an AJAX request at the front page in order
# to reduce loading time.
df = positions()
# Drop all currencies from table
df = df[df['is_currency'] == False]
# check if trade_asset_ticker is set as index. If so, move to column
# This happens on some memoized functions - need to understand why
# The below is a temporary fix
df = df.reset_index()
if df is None:
return None, None
tickers_string = ",".join(list_tickers())
# Let's try to get as many prices as possible into the df with a
# single request - first get all the prices in current currency and USD
multi_price = multiple_price_grab(tickers_string,
'USD,' + current_user.fx())
# PARSER Function to fing the ticker price inside the matrix. First part
# looks into the cryptocompare matrix. In the exception, if price is not
# found, it sends a request to other providers
def find_data(ticker):
notes = None
try:
# Parse the cryptocompare data
price = multi_price["RAW"][ticker][current_user.fx()]["PRICE"]
price = float(price)
high = float(
multi_price["RAW"][ticker][current_user.fx()]["HIGHDAY"])
low = float(
multi_price["RAW"][ticker][current_user.fx()]["LOWDAY"])
chg = multi_price["RAW"][ticker][
current_user.fx()]["CHANGEPCT24HOUR"]
mktcap = multi_price["DISPLAY"][ticker][
current_user.fx()]["MKTCAP"]
volume = multi_price["DISPLAY"][ticker][
current_user.fx()]["VOLUME24HOURTO"]
last_up_source = multi_price["RAW"][ticker][
current_user.fx()]["LASTUPDATE"]
source = multi_price["DISPLAY"][ticker][
current_user.fx()]["LASTMARKET"]
last_update = datetime.now()
except (KeyError, TypeError):
# Couldn't find price with CryptoCompare. Let's try a different source
# and populate data in the same format [aa = alphavantage]
try:
single_price = price_data_rt_full(ticker, 'aa')
if single_price is None:
raise KeyError
price = single_price[0]
high = single_price[2]
low = single_price[3]
(_, last_update, _, _, chg, mktcap, last_up_source, volume,
source, notes) = single_price
except Exception:
# Let's try a final time using Financial Modeling Prep API
try:
single_price = price_data_rt_full(ticker, 'fp')
if single_price is None:
raise KeyError
price = single_price[0]
high = single_price[2]
low = single_price[3]
(_, last_update, _, _, chg, mktcap, last_up_source, volume,
source, notes) = single_price
except Exception:
try:
# Finally, if realtime price is unavailable, find the latest
# saved value in historical prices
# Create a price class
price_class = price_data(ticker)
if price_class is None:
raise KeyError
price = float(price_class.df['close'].iloc[0]
) * current_user.fx_rate_USD()
high = float(price_class.df['high'].iloc[0]
) * current_user.fx_rate_USD()
low = float(price_class.df['low'].iloc[0]
) * current_user.fx_rate_USD()
volume = current_user.fx() + " " + "{0:,.0f}".format(
float(price_class.df['volume'].iloc[0]) *
current_user.fx_rate_USD())
mktcap = chg = 0
source = last_up_source = 'Historical Data'
last_update = price_class.df.index[0]
except Exception as e:
price = high = low = chg = mktcap = last_up_source = last_update = volume = 0
source = '-'
logging.error(
f"There was an error getting the price for {ticker}."
+ f"Error: {e}")
return price, last_update, high, low, chg, mktcap, last_up_source, volume, source, notes
df = apply_and_concat(df, 'trade_asset_ticker', find_data, [
'price', 'last_update', '24h_high', '24h_low', '24h_change', 'mktcap',
'last_up_source', 'volume', 'source', 'notes'
])
# Now create additional columns with calculations
df['position_fx'] = df['price'] * df['trade_quantity']
df['allocation'] = df['position_fx'] / df['position_fx'].sum()
df['change_fx'] = df['position_fx'] * df['24h_change'] / 100
# Pnl and Cost calculations
df['breakeven'] = df['cash_value_fx'] / df['trade_quantity']
df['pnl_gross'] = df['position_fx'] - df['cash_value_fx']
df['pnl_net'] = df['pnl_gross'] - df['trade_fees_fx']
# FIFO and LIFO PnL calculations
df['LIFO_unreal'] = (df['price'] - df['LIFO_average_cost']) * \
df['trade_quantity']
df['FIFO_unreal'] = (df['price'] - df['FIFO_average_cost']) * \
df['trade_quantity']
df['LIFO_real'] = df['pnl_net'] - df['LIFO_unreal']
df['FIFO_real'] = df['pnl_net'] - df['FIFO_unreal']
df['LIFO_unrealized_be'] = df['price'] - \
(df['LIFO_unreal'] / df['trade_quantity'])
df['FIFO_unrealized_be'] = df['price'] - \
(df['FIFO_unreal'] / df['trade_quantity'])
# Allocations below 0.01% are marked as small
# this is used to hide small and closed positions at html
df.loc[df.allocation <= 0.0001, 'small_pos'] = 'True'
df.loc[df.allocation >= 0.0001, 'small_pos'] = 'False'
# Prepare for delivery. Change index, add total
df.set_index('trade_asset_ticker', inplace=True)
df.loc['Total'] = 0
# Column names can't be tuples - otherwise json generates an error
df.rename(columns={
('trade_quantity', 'B'): 'trade_quantity_B',
('trade_quantity', 'S'): 'trade_quantity_S',
('trade_quantity', 'D'): 'trade_quantity_D',
('trade_quantity', 'W'): 'trade_quantity_W',
('cash_value_fx', 'B'): 'cash_value_fx_B',
('cash_value_fx', 'S'): 'cash_value_fx_S',
('cash_value_fx', 'D'): 'cash_value_fx_D',
('cash_value_fx', 'W'): 'cash_value_fx_W',
('trade_fees_fx', 'B'): 'trade_fees_fx_B',
('trade_fees_fx', 'S'): 'trade_fees_fx_S',
('trade_fees_fx', 'D'): 'trade_fees_fx_D',
('trade_fees_fx', 'W'): 'trade_fees_fx_W'
},
inplace=True)
# Need to add only some fields - strings can't be added for example
columns_sum = [
'cash_value_fx', 'trade_fees_fx', 'position_fx', 'allocation',
'change_fx', 'pnl_gross', 'pnl_net', 'LIFO_unreal', 'FIFO_unreal',
'LIFO_real', 'FIFO_real'
]
for field in columns_sum:
df.loc['Total', field] = df[field].sum()
# Set the portfolio last update to be equal to the latest update in df
df.loc['Total',
'last_up_source'] = (datetime.now()).strftime('%d-%b-%Y %H:%M:%S')
df['last_update'] = df['last_update'].astype(str)
# Create a pie chart data in HighCharts format excluding small pos
pie_data = []
for ticker in list_tickers():
if df.loc[ticker, 'small_pos'] == 'False':
tmp_dict = {}
tmp_dict['y'] = round(df.loc[ticker, 'allocation'] * 100, 2)
tmp_dict['name'] = ticker
pie_data.append(tmp_dict)
return (df, pie_data)
def find_fx(row, fx=None):
# row.name is the date being passed
# row['trade_currency'] is the base fx (the one where the trade was included)
# Create an instance of PriceData:
price = fx_price_ondate(current_user.fx(), row['trade_currency'], row.name)
return price
def newtrade():
form = NewTrade()
acclist = AccountInfo.query.filter_by(user_id=current_user.username)
accounts = []
for item in acclist:
accounts.append((item.account_longname, item.account_longname))
form.trade_account.choices = accounts
form.cash_account.choices = accounts
if request.method == "POST":
if form.validate_on_submit():
# Need to include two sides of trade:
if form.trade_operation.data in ("B", "D"):
qop = 1
elif form.trade_operation.data in ("S", "W"):
qop = -1
else:
qop = 0
flash("Trade Operation Error. Should be B, S, D or W.",
"warning")
# Create a unique ID for this transaction
random_hex = secrets.token_hex(21)
# Calculate Trade's cash value
cvfail = False
if form.trade_type.data != "3":
try:
p = float(cleancsv(form.trade_price.data))
q = float(cleancsv(form.trade_quantity.data))
f = float(cleancsv(form.trade_fees.data))
cv = qop * (q * p) + f
except ValueError:
flash(
"Error on calculating fiat amount \
for transaction - TRADE NOT included",
"danger",
)
cvfail = True
cv = 0
# Check what type of trade this is
# Cash and/or Asset
if form.trade_type.data == "1" or form.trade_type.data == "2":
try:
tquantity = float(form.trade_quantity.data) * qop
except ValueError:
tquantity = 0
try:
tprice = float(form.trade_price.data)
except ValueError:
tprice = 0
trade = Trades(
user_id=current_user.username,
trade_date=form.trade_date.data,
trade_account=form.trade_account.data,
trade_currency=form.trade_currency.data,
trade_asset_ticker=form.trade_asset_ticker.data,
trade_quantity=tquantity,
trade_operation=form.trade_operation.data,
trade_price=tprice,
trade_fees=form.trade_fees.data,
trade_notes=form.trade_notes.data,
trade_reference_id=random_hex,
cash_value=cv,
)
if not cvfail:
db.session.add(trade)
db.session.commit()
regenerate_nav()
if form.trade_type.data == "1":
# First side is done, now for the matching side-financial only
if form.trade_operation.data == "D":
acc = "W"
elif form.trade_operation.data == "W":
acc = "D"
elif form.trade_operation.data == "B":
acc = "W"
elif form.trade_operation.data == "S":
acc = "D"
else:
acc = ""
trade = Trades(
user_id=current_user.username,
trade_date=form.trade_date.data,
trade_account=form.cash_account.data,
trade_currency=form.trade_currency.data,
trade_asset_ticker=form.trade_currency.data,
trade_operation=acc,
trade_price="1",
trade_quantity=float(cleancsv(form.cash_value.data)),
trade_fees=0,
cash_value=cv * (-1),
trade_notes=f"Matching Trade for trade id: \
<{random_hex} > - included as a pair",
trade_reference_id=random_hex,
)
db.session.add(trade)
db.session.commit()
regenerate_nav()
if form.trade_type.data == "3":
# Cash Only Transaction
try:
cv = qop * (float(cleancsv(form.cash_value.data)))
except ValueError:
flash("Error on calculating cash amount for transaction",
"warning")
cv = 0
cvfail = True
trade = Trades(
user_id=current_user.username,
trade_date=form.trade_date.data,
trade_account=form.cash_account.data,
trade_currency=form.trade_currency.data,
trade_asset_ticker=form.trade_currency.data,
trade_quantity=cv,
trade_price=1,
trade_operation=form.trade_operation.data,
trade_fees=form.trade_fees.data,
trade_notes=form.trade_notes.data,
cash_value=cv,
trade_reference_id=random_hex,
)
if not cvfail:
db.session.add(trade)
db.session.commit()
regenerate_nav()
if not cvfail:
flash("Trade included", "success")
return redirect(url_for("main.home"))
else:
flash("Trade Input failed. Something went wrong. Try Again.",
"danger")
form.trade_currency.data = current_user.fx()
form.trade_date.data = datetime.utcnow()
return render_template("newtrade.html", form=form, title="New Trade")