def update_market_price(self, info_table, symbol_col, date=None):
url_symbol = vlookup(info_table, self.symbol, symbol_col, 'ticker_url')
if date is not None:
# print('getting historical data for {} on {}'.format(self.symbol, date.strftime('%Y-%m-%d')))
self.market_price = useful_functions.get_hist_price(
url_symbol, date)
self.market_price_time = date
else:
price_pair = get_last_price(url_symbol)
self.market_price = price_pair[1]
self.market_price_time = price_pair[0]
def update_name_changes(self, reference_df):
name_changes = self.df[self.df['Action'] == 'NAC']
print('{} rows of name changes detected'.format(len(name_changes)))
if len(name_changes) > 0:
for i in name_changes.index:
i_symbol = vlookup(reference_df, self.df.loc[i, 'Symbol'],
'transfer_symbol', 'real_symbol')
if i_symbol is None:
print('{} not found in reference table'.format(
self.df.loc[i, 'Symbol']))
self.df.loc[i, 'Symbol'] = i_symbol
def update_splits(self, reference_df):
splits = self.df[self.df['Action'] == 'REV']
print('{} rows of reverse splits/splits detected'.format(len(splits)))
if len(splits) > 0:
for i in splits.index:
i_symbol = vlookup(reference_df, self.df.loc[i, 'Symbol'],
'transfer_symbol', 'real_symbol')
if i_symbol is None:
print('{} not found in reference table'.format(
self.df.loc[i, 'Symbol']))
self.df.loc[i, 'Symbol'] = i_symbol
def update_market_price(self, info_table, symbol_col, date=None):
url_symbol = vlookup(info_table, self.underlying_symbol, symbol_col,
'ticker_url')
lookup_instrument = vlookup(info_table, self.underlying_symbol,
symbol_col, 'instrument')
if date is not None:
self.underlying_market_price = useful_functions.get_hist_price(
self.underlying_symbol, date)
self.underlying_market_price_time = date
else:
price_pair = get_last_price(url_symbol)
self.underlying_market_price = price_pair[1]
self.underlying_market_price_time = price_pair[0]
# the market price of the option is intrinsic value only due to difficulties of getting market value of options
if self.option_type == 'Call':
self.market_price = max(0, self.underlying_market_price -
self.strike) * self.num_shares
else:
self.market_price = max(
0,
self.strike - self.underlying_market_price) * self.num_shares
self.market_price_time = self.underlying_market_price_time
def update_inkind_transfer(self, reference_df):
inkind_transfers = self.df[(self.df['Activity Type'] == 'Transfers')
& (~pd.isna(self.df['Symbol']))]
print('{} rows of in-kind transfers detectted'.format(
len(inkind_transfers)))
if len(inkind_transfers) > 0:
for i in inkind_transfers.index:
i_currency = self.df.loc[i, 'Currency']
if i_currency == 'CAD':
self.df.loc[i, 'Symbol'] = self.df.loc[i, 'Symbol'] + '.TO'
elif i_currency == 'USD':
self.df.loc[i,
'Symbol'] = vlookup(reference_df,
self.df.loc[i, 'Symbol'],
'transfer_symbol',
'real_symbol')
def update_security_info(self, info_table, symbol_col):
self.asset_class = vlookup(info_table, self.underlying_symbol,
symbol_col, 'asset_class')
self.region = vlookup(info_table, self.underlying_symbol, symbol_col,
'region')
def update_security_info(self, info_table, symbol_col):
self.instrument = vlookup(info_table, self.symbol, symbol_col,
'instrument')
self.asset_class = vlookup(info_table, self.symbol, symbol_col,
'asset_class')
self.region = vlookup(info_table, self.symbol, symbol_col, 'region')
def questrade_transaction_to_sec(transaction_df, split_reference=None):
'''
Parameters
----------
transaction_df : DataFrame
the dataframe exported by questrade
split_reference : DataFrame, optional
a reference dataframe for stock splits, default None
Returns
-------
two dictionaries
'''
l = len(transaction_df)
holdings_dict = {'CAD': 0, 'USD': 0}
security_dict = {}
for i in range(l):
if (i % 50) == 0:
print('processing row {}'.format(i))
i_row = transaction_df.loc[i, ]
i_transaction_date = i_row['Transaction Date']
i_action = i_row['Action']
i_symbol = i_row['Symbol']
i_description = i_row['Description']
i_quantity = i_row['Quantity']
i_price = i_row['Price']
i_gross_amount = i_row['Gross Amount']
i_commission = i_row['Commission']
i_net_amount = i_row['Net Amount']
i_currency = i_row['Currency']
i_activity = i_row['Activity Type']
# different cases
# cash deposit
if i_activity == 'Deposits':
holdings_dict[i_currency.upper()] += i_net_amount
# dividends operate the same way as deposits
elif i_activity == 'Dividends':
holdings_dict[i_currency.upper()] += i_net_amount
# fx conversion is like two deposits at once
elif i_activity == 'FX conversion':
holdings_dict[i_currency.upper()] += i_net_amount
# the opposite of deposits
elif i_activity == 'Withdrawals':
holdings_dict[i_currency.upper()] += i_net_amount
# buying or selling securities
elif i_activity == 'Trades':
# first see if it is an option
i_description_split = i_description.split(' ')
if (i_description_split[0] == 'PUT') or (i_description_split[0]
== 'CALL'): # option
i_option = description_to_option(i_description, i_currency)
i_option_symbol = i_option.symbol
if i_option_symbol not in security_dict.keys():
security_dict[i_option_symbol] = i_option
i_option = security_dict[i_option_symbol]
i_option.new_trade(i_quantity, i_price * 100,
abs(i_commission))
if i_option.liquidated:
del security_dict[i_option_symbol]
else:
security_dict[i_option_symbol] = i_option
else: # not an option
if i_symbol not in security_dict.keys():
i_security = Security(i_symbol, i_currency)
security_dict[i_symbol] = i_security
i_security = security_dict[i_symbol]
i_security.new_trade(i_quantity, i_price, abs(i_commission))
if i_security.liquidated:
del security_dict[i_symbol]
else:
security_dict[i_symbol] = i_security
holdings_dict[i_currency.upper()] += i_net_amount
# cash or in-knd transfer to and from another investment account
elif i_activity == 'Transfers':
if i_action == 'TF6': # transfer in
if (i_symbol != '') and (
not pd.isna(i_symbol)): # in-kind transfer of shares
if (i_currency == 'CAD') and ('.TO' not in i_symbol):
i_symbol = i_symbol + '.TO'
i_price = useful_functions.get_hist_price(
i_symbol, i_transaction_date)
if i_symbol not in security_dict.keys():
i_security = Security(i_symbol, i_currency)
security_dict[i_symbol] = i_security
i_security = security_dict[i_symbol]
i_security.new_trade(
i_quantity, i_price,
0) # no commission for in-kind transfer
else: # cash transfer
holdings_dict[i_currency.upper()] += i_net_amount
elif i_action == 'TFO': # trasnfer out
if (i_symbol != '') and (
not pd.isna(i_symbol)): # transferring securities
if i_symbol not in security_dict.keys():
print('transferring out non-existent security')
return
i_security = security_dict[i_symbol]
i_security.new_trade(i_quantity, i_price,
abs(i_commission))
if i_security.liquidated:
del security_dict[i_symbol]
else:
security_dict[i_symbol] = i_security
else: # transferring out cash
holdings_dict[i_currency.upper()] += i_net_amount
# other activities
elif i_activity == 'Other':
if i_action == 'EXP': # option expiry
i_option = description_to_option(i_description, i_currency)
i_option_symbol = i_option.symbol
del security_dict[i_option_symbol]
elif i_action == 'GST': # tax on fees
holdings_dict[i_currency.upper()] += i_net_amount
elif i_action == 'BRW': # journalling
if i_quantity < 0:
i_security = security_dict[i_symbol]
symbol_to = journal_symbol_dest(
i_symbol, i_currency) # need to define
i_security.journal(symbol_to, i_transaction_date)
del security_dict[i_symbol]
if symbol_to not in security_dict.keys():
security_dict[symbol_to] = i_security
else:
existing_security = security_dict[symbol_to]
existing_security.new_trade(i_security.quantity,
i_security.average_cost,
i_security.commission)
security_dict[symbol_to] = existing_security
elif i_action == 'ADJ': # option adjustment because of splits
if i_quantity < 0:
# first find the new underlying symbol
counter_adj = transaction_df[
(transaction_df['Action'] == 'ADJ')
& (transaction_df['Transaction Date'] ==
i_transaction_date) &
(transaction_df['Quantity']
== -1 * i_quantity)].reset_index()
new_underlying_symbol = description_to_option(
counter_adj.loc[0, 'Description'],
counter_adj.loc[0, 'Currency']).underlying_symbol
# then find the mutliplier that should be applied to the strike/share number due to the split
lookup_date = datetime(i_transaction_date.year,
i_transaction_date.month,
i_transaction_date.day)
multiplier = vlookup(split_reference, lookup_date, 'date',
'multiplier')
# get the current held option and manipulate it
old_symbol = description_to_option(i_description,
i_currency).symbol
i_option = security_dict[old_symbol]
i_option.adjust_for_split(multiplier,
new_underlying_symbol)
# make the change
del security_dict[old_symbol]
security_dict[i_option.symbol] = i_option
# fees and rebates
elif i_activity == 'Fees and rebates':
if i_action == 'FCH': # fees
holdings_dict[i_currency.upper()] += i_net_amount
else:
pass
# corporate actions, e.g. splits, name changes
elif i_activity == 'Corporate actions':
if i_action == 'CIL': # cash in lieu
holdings_dict[i_currency.upper()] += i_net_amount
elif i_action == 'REV': # reverse split
if i_quantity < 0:
i_security = security_dict[i_symbol]
counter_split = transaction_df[
(transaction_df['Action'] == 'REV')
& (transaction_df['Transaction Date'] ==
i_transaction_date) &
(transaction_df['Quantity'] > 0)]
new_quantity = int(counter_split['Quantity'])
lookup_date = datetime(i_transaction_date.year,
i_transaction_date.month,
i_transaction_date.day)
ratio = 1 / vlookup(split_reference, lookup_date, 'date',
'multiplier')
i_security.reverse_split(ratio, new_quantity)
security_dict[i_symbol] = i_security
elif i_action == 'NAC': # name change
pass
# any activities not encountered before
else:
print('a new activity not encountered before, exiting...')
return
return security_dict, holdings_dict