def calc_disc_xns(issuer, reporting_owner, sec_ids, prim_sec_id,
price_dict, ticker_sec_dict, startdate, enddate,
is_10b5_1):
disc_xns = Form345Entry.objects.filter(issuer_cik=issuer)\
.filter(reporting_owner_cik=reporting_owner)\
.filter(Q(security__in=sec_ids) |
Q(underlying_security__in=sec_ids))\
.filter(Q(transaction_code='P') | Q(transaction_code='S'))\
.filter(transaction_date__gte=startdate)\
.filter(transaction_date__lte=enddate)\
.exclude(transaction_shares=None)
if is_10b5_1 is True:
disc_xns = disc_xns.filter(tenbfive_note=True)
else:
disc_xns = disc_xns.exclude(tenbfive_note=True)
disc_xns = disc_xns\
.values('xn_prim_share_eq', 'xn_value',
'prim_adjustment_factor')
if len(disc_xns) is 0:
return Decimal(0), Decimal(0)
net_xn_shares = Decimal(0)
net_xn_value = Decimal(0)
for x in disc_xns:
xn_prim_share_eq = x['xn_prim_share_eq']
xn_value = x['xn_value']
prim_adjustment_factor = x['prim_adjustment_factor']
net_xn_shares += rep_none_with_zero(xn_prim_share_eq)\
* prim_adjustment_factor
net_xn_value += rep_none_with_zero(xn_value)
return net_xn_shares, net_xn_value
def calc_disc_xns(issuer, reporting_owner, sec_ids, prim_sec_id, price_dict,
ticker_sec_dict, startdate, enddate, is_10b5_1):
disc_xns = Form345Entry.objects.filter(issuer_cik=issuer)\
.filter(reporting_owner_cik=reporting_owner)\
.filter(Q(security__in=sec_ids) |
Q(underlying_security__in=sec_ids))\
.filter(Q(transaction_code='P') | Q(transaction_code='S'))\
.filter(transaction_date__gte=startdate)\
.filter(transaction_date__lte=enddate)\
.exclude(transaction_shares=None)
if is_10b5_1 is True:
disc_xns = disc_xns.filter(tenbfive_note=True)
else:
disc_xns = disc_xns.exclude(tenbfive_note=True)
disc_xns = disc_xns\
.values('xn_prim_share_eq', 'xn_value',
'prim_adjustment_factor')
if len(disc_xns) is 0:
return Decimal(0), Decimal(0)
net_xn_shares = Decimal(0)
net_xn_value = Decimal(0)
for x in disc_xns:
xn_prim_share_eq = x['xn_prim_share_eq']
xn_value = x['xn_value']
prim_adjustment_factor = x['prim_adjustment_factor']
net_xn_shares += rep_none_with_zero(xn_prim_share_eq)\
* prim_adjustment_factor
net_xn_value += rep_none_with_zero(xn_value)
return net_xn_shares, net_xn_value
def calc_equity_grants(issuer, reporting_owner, sec_ids, prim_sec_id,
price_dict, ticker_sec_dict):
grants = Form345Entry.objects.filter(issuer_cik=issuer)\
.filter(reporting_owner_cik=reporting_owner)\
.filter(Q(security__in=sec_ids) |
Q(underlying_security__in=sec_ids))\
.filter(transaction_code='A')\
.exclude(transaction_shares=None)\
.exclude(transaction_date=None)\
.filter(transaction_date__gte=today - grant_period_calc_lookback -
datetime.timedelta(5))\
.filter(filedatetime__gte=todaymid - grant_period_calc_lookback)
grant_dates = \
list(grants.order_by('transaction_date')
.values_list('transaction_date', flat=True).distinct())
# Do not pass go if no grant info available
if len(grant_dates) == 0:
return Decimal(0), Decimal(0), 1
# If you have only one grant, assume annual because that is typical
if len(grant_dates) == 1:
grants_per_year = 1
# Otherwise, figure out grants per year received based on spacing
else:
day_gaps = []
for first_date, second_date in zip(grant_dates, grant_dates[1:]):
day_gaps.append(Decimal((second_date - first_date).days))
median_day_gap = median(day_gaps)
day_gap_options = [Decimal(30), Decimal(45), Decimal(60),
Decimal(91), Decimal(182), Decimal(365)]
estimated_day_gap = min(day_gap_options,
key=lambda x: abs(x-median_day_gap))
grants_per_year = int(round(Decimal(365) / estimated_day_gap, 0))
# now calc grant amounts in last year and use total to annualize
grant_dates.reverse()
last_year_dates = grant_dates[:grants_per_year]
grants_amounts = grants\
.filter(transaction_date__in=last_year_dates)\
.values('xn_prim_share_eq', 'xn_full_conv_cost',
'prim_adjustment_factor')
# If no grants in last year, stop here.
if grants_amounts.count() == 0:
return Decimal(0), Decimal(0), 1
# Otherwise calculate total grants
annual_grant_shares = Decimal(0)
total_conv_cost = Decimal(0)
for g in grants_amounts:
xn_prim_share_eq = g['xn_prim_share_eq']
xn_full_conv_cost = g['xn_full_conv_cost']
prim_adjustment_factor = g['prim_adjustment_factor']
annual_grant_shares += rep_none_with_zero(xn_prim_share_eq)\
* prim_adjustment_factor
total_conv_cost += rep_none_with_zero(xn_full_conv_cost)
#
if annual_grant_shares != Decimal(0):
avg_conv_price = total_conv_cost / annual_grant_shares
else:
avg_conv_price = None
#
return annual_grant_shares, avg_conv_price, grants_per_year
def calc_equity_grants(issuer, reporting_owner, sec_ids, prim_sec_id,
price_dict, ticker_sec_dict):
grants = Form345Entry.objects.filter(issuer_cik=issuer)\
.filter(reporting_owner_cik=reporting_owner)\
.filter(Q(security__in=sec_ids) |
Q(underlying_security__in=sec_ids))\
.filter(transaction_code='A')\
.exclude(transaction_shares=None)\
.exclude(transaction_date=None)\
.filter(transaction_date__gte=today - grant_period_calc_lookback -
datetime.timedelta(5))\
.filter(filedatetime__gte=todaymid - grant_period_calc_lookback)
grant_dates = \
list(grants.order_by('transaction_date')
.values_list('transaction_date', flat=True).distinct())
# Do not pass go if no grant info available
if len(grant_dates) == 0:
return Decimal(0), Decimal(0), 1
# If you have only one grant, assume annual because that is typical
if len(grant_dates) == 1:
grants_per_year = 1
# Otherwise, figure out grants per year received based on spacing
else:
day_gaps = []
for first_date, second_date in zip(grant_dates, grant_dates[1:]):
day_gaps.append(Decimal((second_date - first_date).days))
median_day_gap = median(day_gaps)
day_gap_options = [Decimal(30), Decimal(45), Decimal(60),
Decimal(91), Decimal(182), Decimal(365)]
estimated_day_gap = min(day_gap_options,
key=lambda x: abs(x-median_day_gap))
grants_per_year = int(round(Decimal(365) / estimated_day_gap, 0))
# now calc grant amounts in last year and use total to annualize
grant_dates.reverse()
last_year_dates = grant_dates[:grants_per_year]
grants_amounts = grants\
.filter(transaction_date__in=last_year_dates)\
.values('xn_prim_share_eq', 'xn_full_conv_cost',
'prim_adjustment_factor')
# If no grants in last year, stop here.
if grants_amounts.count() == 0:
return Decimal(0), Decimal(0), 1
# Otherwise calculate total grants
annual_grant_shares = Decimal(0)
total_conv_cost = Decimal(0)
for g in grants_amounts:
xn_prim_share_eq = g['xn_prim_share_eq']
xn_full_conv_cost = g['xn_full_conv_cost']
prim_adjustment_factor = g['prim_adjustment_factor']
annual_grant_shares += rep_none_with_zero(xn_prim_share_eq)\
* prim_adjustment_factor
total_conv_cost += rep_none_with_zero(xn_full_conv_cost)
#
if annual_grant_shares != Decimal(0):
avg_conv_price = total_conv_cost / annual_grant_shares
else:
avg_conv_price = None
#
return annual_grant_shares, avg_conv_price, grants_per_year
def calc_holdings(issuer, reporting_owner, sec_ids, prim_sec_id, calc_dt,
price_dict, ticker_sec_dict):
calc_date = calc_dt.date()
prim_price = get_price(ticker_sec_dict[prim_sec_id], calc_date, price_dict)
if prim_price is None or prim_price == Decimal(0):
return None, None, None, None
holdings = Form345Entry.objects.filter(issuer_cik=issuer)\
.filter(reporting_owner_cik=reporting_owner)\
.filter(Q(security__in=sec_ids) |
Q(underlying_security__in=sec_ids))\
.exclude(filedatetime__gt=calc_dt)\
.exclude(supersededdt__lte=calc_dt)\
.exclude(shares_following_xn=None)\
.values('shares_following_xn', 'adjustment_factor',
'security__conversion_multiple', 'conversion_price',
'security', 'underlying_security')
total_shares_held = Decimal(0)
total_value = Decimal(0)
total_conv_cost = Decimal(0)
for h in holdings:
# Do we price security directly or underlying?
if h['security'] in ticker_sec_dict:
sec_id = h['security']
underlying_conversion_mult = Decimal(1)
else:
sec_id = h['underlying_security']
underlying_conversion_mult = h['security__conversion_multiple']
# If pricing secondary security, calculate conversion multiple.
if sec_id != prim_sec_id:
holding_price = get_price(ticker_sec_dict[sec_id], calc_date,
price_dict)
if holding_price is None or prim_price is None or\
prim_price == Decimal(0):
continue
share_conversion_mult_to_primary_ticker = \
holding_price / prim_price
else:
share_conversion_mult_to_primary_ticker = Decimal(1)
security_shares = h['shares_following_xn'] * h['adjustment_factor'] *\
underlying_conversion_mult
prim_eq_shares = security_shares *\
share_conversion_mult_to_primary_ticker
price = get_price(ticker_sec_dict[sec_id], calc_date, price_dict)
if price is None or price == Decimal(0):
continue
adj_conversion_price = rep_none_with_zero(h['conversion_price']) /\
h['adjustment_factor']
holding_value = security_shares *\
max(Decimal(0), price - adj_conversion_price)
conv_cost = adj_conversion_price * security_shares
total_shares_held += prim_eq_shares
total_value += holding_value
total_conv_cost += conv_cost
if total_shares_held != Decimal(0):
avg_conv_price = total_conv_cost / total_shares_held
conv_to_price_ratio = avg_conv_price / prim_price
else:
avg_conv_price = None
conv_to_price_ratio = None
return total_shares_held, avg_conv_price, total_value, conv_to_price_ratio
def fill_in_form_data():
print 'Filling in primary equivalent and form value data...'
print ' ...sorting...'
xns = Form345Entry.objects\
.filter(xn_full_conv_cost=None)\
.exclude(security=None)\
.exclude(xn_acq_disp_code=None)\
.exclude(prim_security=None)
if not xns.exists():
print ' ....no new transactions; done.'
return
# xns = xns\
# .values(
# 'conversion_price', 'deriv_or_nonderiv', 'pk', 'prim_security',
# 'security', 'transaction_date', 'transaction_shares',
# 'underlying_shares', 'underlying_security')
counter = 0.0
looplength = float(xns.count())
price_dict = {}
sph_pk_dict = {}
print ' ...updating...'
for x in queryset_iterator(xns):
counter += 1.0
# Do we price security directly or underlying?
# Simplest case - what if the security is the primary security
conversion_price = rep_none_with_zero(x.conversion_price)
deriv_or_nonderiv = x.deriv_or_nonderiv
prim_security = x.prim_security
prim_security_sph_pk = get_sph_pk(prim_security, sph_pk_dict)
security = x.security
security_sph_pk = get_sph_pk(security, sph_pk_dict)
if x.transaction_date is not None:
xn_date = x.transaction_date
else:
xn_date = x.filedatetime.date
xn_shares = x.transaction_shares
xn_acq_disp_code = x.xn_acq_disp_code
underlying_security = x.underlying_security
underlying_security_sph_pk =\
get_sph_pk(underlying_security, sph_pk_dict)
underlying_shares = x.underlying_shares
if xn_acq_disp_code == 'A':
xn_sign = Decimal(1)
elif xn_acq_disp_code == 'D':
xn_sign = Decimal(-1)
else:
print '\nno transaction acq_or_disp code error for item pk', x.pk
print 'acq_or_disp is neither "A" nor "D"\n'
continue
prim_price = get_price(prim_security_sph_pk, xn_date, price_dict)
xn_value = None
# Calc conversion costs
if underlying_shares is not None:
xn_full_conv_cost = underlying_shares * conversion_price
elif xn_shares is not None:
xn_full_conv_cost = xn_shares * conversion_price
else:
xn_full_conv_cost = Decimal(0)
# Calc xn_value
if security.pk == prim_security.pk:
security_price = prim_price
if prim_price is not None and xn_shares is not None:
xn_value = xn_shares * prim_price * xn_sign
else:
xn_value = None
# What if not primary security but is nonderivative
elif deriv_or_nonderiv == 'N':
if security_sph_pk is not None:
security_price =\
get_price(security_sph_pk, xn_date, price_dict)
else:
security_price = None
if security_price is not None and xn_shares is not None:
xn_value = xn_shares * security_price * xn_sign
else:
xn_value = None
# Now we deal with derivative securities
# If there is direct pricing of the derivative (like class a
# convertible into class b).
elif deriv_or_nonderiv == 'D' and\
security_sph_pk is not None:
security_price = get_price(security_sph_pk, xn_date, price_dict)
if security_price is not None and xn_shares is not None:
xn_value = xn_shares * security_price * xn_sign
else:
xn_value = None
# If no direct pricing of derivative, but have underlying pricing.
elif deriv_or_nonderiv == 'D' and\
security_sph_pk is None and\
underlying_security_sph_pk is not None:
underlying_price =\
get_price(underlying_security_sph_pk, xn_date, price_dict)
if underlying_price is not None and underlying_shares is not None:
gross_value = underlying_shares * underlying_price
xn_value = \
max(gross_value - xn_full_conv_cost, Decimal(0)) * xn_sign
else:
xn_value = None
elif deriv_or_nonderiv != 'D' and deriv_or_nonderiv != 'N':
print '\nError, deriv or nonderiv is blank for', x
print '\n'
continue
else:
continue
# Calc primary share equivalents
if security_sph_pk == prim_security_sph_pk and xn_shares is not None:
xn_prim_share_eq = xn_shares * xn_sign
elif underlying_security_sph_pk == prim_security_sph_pk\
and underlying_shares is not None:
xn_prim_share_eq = underlying_shares * xn_sign
elif prim_price is not None and xn_value is not None:
xn_prim_share_eq = xn_value / prim_price
else:
xn_prim_share_eq = None
x.xn_prim_share_eq = xn_prim_share_eq
x.xn_value = xn_value
x.xn_full_conv_cost = xn_full_conv_cost
x.save()
percentcomplete = round(counter / looplength * 100, 2)
sys.stdout.write("\r%s / %s forms to update: %.2f%%" %
(int(counter), int(looplength), percentcomplete))
sys.stdout.flush()
print '\n done.'
return
def calc_holdings(issuer, reporting_owner, sec_ids, prim_sec_id, calc_dt,
price_dict, ticker_sec_dict):
calc_date = calc_dt.date()
prim_price = get_price(ticker_sec_dict[prim_sec_id],
calc_date, price_dict)
if prim_price is None or prim_price == Decimal(0):
return None, None, None, None
holdings = Form345Entry.objects.filter(issuer_cik=issuer)\
.filter(reporting_owner_cik=reporting_owner)\
.filter(Q(security__in=sec_ids) |
Q(underlying_security__in=sec_ids))\
.exclude(filedatetime__gt=calc_dt)\
.exclude(supersededdt__lte=calc_dt)\
.exclude(shares_following_xn=None)\
.values('shares_following_xn', 'adjustment_factor',
'security__conversion_multiple', 'conversion_price',
'security', 'underlying_security')
total_shares_held = Decimal(0)
total_value = Decimal(0)
total_conv_cost = Decimal(0)
for h in holdings:
# Do we price security directly or underlying?
if h['security'] in ticker_sec_dict:
sec_id = h['security']
underlying_conversion_mult = Decimal(1)
else:
sec_id = h['underlying_security']
underlying_conversion_mult = h['security__conversion_multiple']
# If pricing secondary security, calculate conversion multiple.
if sec_id != prim_sec_id:
holding_price = get_price(ticker_sec_dict[sec_id],
calc_date, price_dict)
if holding_price is None or prim_price is None or\
prim_price == Decimal(0):
continue
share_conversion_mult_to_primary_ticker = \
holding_price / prim_price
else:
share_conversion_mult_to_primary_ticker = Decimal(1)
security_shares = h['shares_following_xn'] * h['adjustment_factor'] *\
underlying_conversion_mult
prim_eq_shares = security_shares *\
share_conversion_mult_to_primary_ticker
price = get_price(ticker_sec_dict[sec_id], calc_date, price_dict)
if price is None or price == Decimal(0):
continue
adj_conversion_price = rep_none_with_zero(h['conversion_price']) /\
h['adjustment_factor']
holding_value = security_shares *\
max(Decimal(0), price - adj_conversion_price)
conv_cost = adj_conversion_price * security_shares
total_shares_held += prim_eq_shares
total_value += holding_value
total_conv_cost += conv_cost
if total_shares_held != Decimal(0):
avg_conv_price = total_conv_cost / total_shares_held
conv_to_price_ratio = avg_conv_price / prim_price
else:
avg_conv_price = None
conv_to_price_ratio = None
return total_shares_held, avg_conv_price, total_value, conv_to_price_ratio
def fill_in_form_data():
print 'Filling in primary equivalent and form value data...'
print ' ...sorting...'
xns = Form345Entry.objects\
.filter(xn_full_conv_cost=None)\
.exclude(security=None)\
.exclude(xn_acq_disp_code=None)\
.exclude(prim_security=None)
if not xns.exists():
print ' ....no new transactions; done.'
return
# xns = xns\
# .values(
# 'conversion_price', 'deriv_or_nonderiv', 'pk', 'prim_security',
# 'security', 'transaction_date', 'transaction_shares',
# 'underlying_shares', 'underlying_security')
counter = 0.0
looplength = float(xns.count())
price_dict = {}
sph_pk_dict = {}
print ' ...updating...'
for x in queryset_iterator(xns):
counter += 1.0
# Do we price security directly or underlying?
# Simplest case - what if the security is the primary security
conversion_price = rep_none_with_zero(x.conversion_price)
deriv_or_nonderiv = x.deriv_or_nonderiv
prim_security = x.prim_security
prim_security_sph_pk = get_sph_pk(prim_security, sph_pk_dict)
security = x.security
security_sph_pk = get_sph_pk(security, sph_pk_dict)
if x.transaction_date is not None:
xn_date = x.transaction_date
else:
xn_date = x.filedatetime.date
xn_shares = x.transaction_shares
xn_acq_disp_code = x.xn_acq_disp_code
underlying_security = x.underlying_security
underlying_security_sph_pk =\
get_sph_pk(underlying_security, sph_pk_dict)
underlying_shares = x.underlying_shares
if xn_acq_disp_code == 'A':
xn_sign = Decimal(1)
elif xn_acq_disp_code == 'D':
xn_sign = Decimal(-1)
else:
print '\nno transaction acq_or_disp code error for item pk', x.pk
print 'acq_or_disp is neither "A" nor "D"\n'
continue
prim_price = get_price(prim_security_sph_pk, xn_date, price_dict)
xn_value = None
# Calc conversion costs
if underlying_shares is not None:
xn_full_conv_cost = underlying_shares * conversion_price
elif xn_shares is not None:
xn_full_conv_cost = xn_shares * conversion_price
else:
xn_full_conv_cost = Decimal(0)
# Calc xn_value
if security.pk == prim_security.pk:
security_price = prim_price
if prim_price is not None and xn_shares is not None:
xn_value = xn_shares * prim_price * xn_sign
else:
xn_value = None
# What if not primary security but is nonderivative
elif deriv_or_nonderiv == 'N':
if security_sph_pk is not None:
security_price =\
get_price(security_sph_pk, xn_date, price_dict)
else:
security_price = None
if security_price is not None and xn_shares is not None:
xn_value = xn_shares * security_price * xn_sign
else:
xn_value = None
# Now we deal with derivative securities
# If there is direct pricing of the derivative (like class a
# convertible into class b).
elif deriv_or_nonderiv == 'D' and\
security_sph_pk is not None:
security_price = get_price(security_sph_pk, xn_date, price_dict)
if security_price is not None and xn_shares is not None:
xn_value = xn_shares * security_price * xn_sign
else:
xn_value = None
# If no direct pricing of derivative, but have underlying pricing.
elif deriv_or_nonderiv == 'D' and\
security_sph_pk is None and\
underlying_security_sph_pk is not None:
underlying_price =\
get_price(underlying_security_sph_pk, xn_date, price_dict)
if underlying_price is not None and underlying_shares is not None:
gross_value = underlying_shares * underlying_price
xn_value = \
max(gross_value - xn_full_conv_cost, Decimal(0)) * xn_sign
else:
xn_value = None
elif deriv_or_nonderiv != 'D' and deriv_or_nonderiv != 'N':
print '\nError, deriv or nonderiv is blank for', x
print '\n'
continue
else:
continue
# Calc primary share equivalents
if security_sph_pk == prim_security_sph_pk and xn_shares is not None:
xn_prim_share_eq = xn_shares * xn_sign
elif underlying_security_sph_pk == prim_security_sph_pk\
and underlying_shares is not None:
xn_prim_share_eq = underlying_shares * xn_sign
elif prim_price is not None and xn_value is not None:
xn_prim_share_eq = xn_value / prim_price
else:
xn_prim_share_eq = None
x.xn_prim_share_eq = xn_prim_share_eq
x.xn_value = xn_value
x.xn_full_conv_cost = xn_full_conv_cost
x.save()
percentcomplete = round(counter / looplength * 100, 2)
sys.stdout.write("\r%s / %s forms to update: %.2f%%" %
(int(counter), int(looplength), percentcomplete))
sys.stdout.flush()
print '\n done.'
return
def calc_prior_trigger_perf_for_sale(aff, sale, issuer, reporting_owner,
ticker_sec_ids, prim_sec_id, price_dict,
ticker_sec_dict, transaction_date,
recent_period_start, hist_period_start,
is_10b5_1, acq_or_disp):
hist_lookback = True
recent_xn_shares, recent_xn_value =\
calc_disc_xns(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id, price_dict,
ticker_sec_dict, recent_period_start, transaction_date, is_10b5_1
)
if acq_or_disp == 'D':
hist_xns_shares, hist_xns_value_disc =\
calc_disc_xns(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id,
price_dict, ticker_sec_dict, hist_period_start,
recent_period_start, is_10b5_1
)
else:
hist_xns_shares =\
None
if acq_or_disp == 'D' and is_10b5_1 is True:
clusters_in_hist_period =\
hist_net_xn_clusters_per_year(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id,
price_dict, ticker_sec_dict, hist_period_start,
recent_period_start, is_10b5_1
)
equity_grant_rate, avg_grant_conv_price, grants_per_year =\
None, None, None
cluster_rate = hist_xns_shares
clusters_in_hist_period = clusters_in_hist_period
else:
clusters_in_hist_period = None
equity_grant_rate, avg_grant_conv_price, grants_per_year =\
calc_equity_grants(
issuer, reporting_owner, ticker_sec_ids,
prim_sec_id, price_dict, ticker_sec_dict
)
cluster_rate = Decimal(-1) * equity_grant_rate
clusters_in_hist_period = grants_per_year
transaction_date_price_info = \
recent_dates_prices(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id, price_dict,
ticker_sec_dict, recent_period_start, transaction_date, is_10b5_1
)
recent = recent_xn_shares
len_hist_over_len_recent =\
(hist_sale_period - recent_sale_period).days /\
(recent_sale_period).days
if acq_or_disp == 'A':
exp_recent_rate = Decimal(0)
elif rep_none_with_zero(cluster_rate) == 0 or\
rep_none_with_zero(clusters_in_hist_period) == 0:
exp_recent_rate = Decimal(0)
else:
exp_recent_rate = min(cluster_rate / len_hist_over_len_recent,
cluster_rate / clusters_in_hist_period)
# Note that the signs are flipped below because we are looking
# for the most negative quantities.
increase_in_xns, price_selling, selling_date, selling_price,\
selling_prior_performance, selling_subs_performance, xn_days =\
calc_increase_in_xns(
abs_sig_min, acq_or_disp, aff, exp_recent_rate, hist_lookback,
is_10b5_1, issuer, price_dict, prim_sec_id, recent,
recent_xn_value, reporting_owner, ticker_sec_dict, ticker_sec_ids,
transaction_date_price_info)
return increase_in_xns, recent_xn_value, selling_date,\
selling_subs_performance
def assemble_ticker_dataframe(ticker):
code = 'ZES/' + ticker
sph = SecurityPriceHist.objects.filter(ticker_sym=ticker).first()
# Set up blank pandas dataframe for insider info
workdf = pandas.DataFrame({
'purch_disc': [], 'sale_disc': [],
'purch_disc_ceo': [], 'sale_disc_ceo': [],
'purch_disc_no_10b5': [], 'sale_disc_no_10b5': [],
'purch_disc_ceo_no_10b5': [], 'sale_disc_ceo_no_10b5': []})
if sph is None:
# This means there is no match to the quandl ticker in our DB.
print 'No issuer record for ticker:', ticker
return None
else:
issuer = sph.issuer
#
# Here we pull down the quandl data
basedf = quandl.get(code)
# Select the quandl columns we care about
selected_quandl_df = basedf[[
'EPS_MEAN_EST', 'EPS_ACT', 'EPS_AMT_DIFF_SURP',
'EPS_PCT_DIFF_SURP', 'EPS_STD_EST', 'EPS_CNT_EST']]
#
for index, row in basedf.iterrows():
# Here we use the issuer and quandl data date to pull insider data
# for a relevant period.
index_datetime = index.to_datetime().replace(tzinfo=EST)
lookback_datetime =\
index_datetime.replace(tzinfo=EST) - earnings_transaction_lookback
#
# Query pulls all discretionary issuer transactions for the period.
transactions = Form345Entry.objects\
.filter(issuer_cik=issuer, filedatetime__gte=lookback_datetime,
filedatetime__lt=index_datetime)
#
# Calc purchase and sale values
purch_disc = rep_none_with_zero(
transactions.filter(transaction_code='P')
.aggregate(Sum('xn_value'))['xn_value__sum'])
sale_disc = rep_none_with_zero(
transactions.filter(transaction_code='S')
.aggregate(Sum('xn_value'))['xn_value__sum'])
#
# Calc purchase and sale values for CEO
disc_ceo = transactions\
.filter(is_officer=True)\
.filter(
Q(reporting_owner_title__icontains='ceo') |
Q(reporting_owner_title__icontains='chief executive officer'))
purch_disc_ceo = rep_none_with_zero(
disc_ceo.filter(transaction_code='P')
.aggregate(Sum('xn_value'))['xn_value__sum'])
sale_disc_ceo = rep_none_with_zero(
disc_ceo.filter(transaction_code='S')
.aggregate(Sum('xn_value'))['xn_value__sum'])
#
# Calc purchase and sale values for non-10b5 transactions
disc_no_10b5 = transactions.filter(tenbfive_note=None)
purch_disc_no_10b5 = rep_none_with_zero(
disc_no_10b5.filter(transaction_code='P')
.aggregate(Sum('xn_value'))['xn_value__sum'])
sale_disc_no_10b5 = rep_none_with_zero(
disc_no_10b5.filter(transaction_code='S')
.aggregate(Sum('xn_value'))['xn_value__sum'])
#
# Calc purchase and sale values for non-10b5 transactions for CEO
disc_ceo_no_10b5 =\
disc_ceo.filter(tenbfive_note=None)
purch_disc_ceo_no_10b5 = rep_none_with_zero(
disc_ceo_no_10b5.filter(transaction_code='P')
.aggregate(Sum('xn_value'))['xn_value__sum'])
sale_disc_ceo_no_10b5 = rep_none_with_zero(
disc_ceo_no_10b5.filter(transaction_code='S')
.aggregate(Sum('xn_value'))['xn_value__sum'])
#
# Add the insider data to a blank working dataframe
workdf.loc[index] = \
[purch_disc, sale_disc,
purch_disc_ceo, sale_disc_ceo,
purch_disc_no_10b5, sale_disc_no_10b5,
purch_disc_ceo_no_10b5, sale_disc_ceo_no_10b5]
#
# Add our insider data to the selected quandl data
ticker_df = pandas.concat([selected_quandl_df, workdf],
axis=1, join='inner')
return ticker_df
def calc_prior_trigger_perf_for_sale(
aff, sale, issuer, reporting_owner, ticker_sec_ids, prim_sec_id,
price_dict, ticker_sec_dict, transaction_date, recent_period_start,
hist_period_start, is_10b5_1, acq_or_disp):
hist_lookback = True
recent_xn_shares, recent_xn_value =\
calc_disc_xns(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id, price_dict,
ticker_sec_dict, recent_period_start, transaction_date, is_10b5_1
)
if acq_or_disp == 'D':
hist_xns_shares, hist_xns_value_disc =\
calc_disc_xns(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id,
price_dict, ticker_sec_dict, hist_period_start,
recent_period_start, is_10b5_1
)
else:
hist_xns_shares =\
None
if acq_or_disp == 'D' and is_10b5_1 is True:
clusters_in_hist_period =\
hist_net_xn_clusters_per_year(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id,
price_dict, ticker_sec_dict, hist_period_start,
recent_period_start, is_10b5_1
)
equity_grant_rate, avg_grant_conv_price, grants_per_year =\
None, None, None
cluster_rate = hist_xns_shares
clusters_in_hist_period = clusters_in_hist_period
else:
clusters_in_hist_period = None
equity_grant_rate, avg_grant_conv_price, grants_per_year =\
calc_equity_grants(
issuer, reporting_owner, ticker_sec_ids,
prim_sec_id, price_dict, ticker_sec_dict
)
cluster_rate = Decimal(-1) * equity_grant_rate
clusters_in_hist_period = grants_per_year
transaction_date_price_info = \
recent_dates_prices(
issuer, reporting_owner, ticker_sec_ids, prim_sec_id, price_dict,
ticker_sec_dict, recent_period_start, transaction_date, is_10b5_1
)
recent = recent_xn_shares
len_hist_over_len_recent =\
(hist_sale_period - recent_sale_period).days /\
(recent_sale_period).days
if acq_or_disp == 'A':
exp_recent_rate = Decimal(0)
elif rep_none_with_zero(cluster_rate) == 0 or\
rep_none_with_zero(clusters_in_hist_period) == 0:
exp_recent_rate = Decimal(0)
else:
exp_recent_rate = min(cluster_rate / len_hist_over_len_recent,
cluster_rate / clusters_in_hist_period)
# Note that the signs are flipped below because we are looking
# for the most negative quantities.
increase_in_xns, price_selling, selling_date, selling_price,\
selling_prior_performance, selling_subs_performance, xn_days =\
calc_increase_in_xns(
abs_sig_min, acq_or_disp, aff, exp_recent_rate, hist_lookback,
is_10b5_1, issuer, price_dict, prim_sec_id, recent,
recent_xn_value, reporting_owner, ticker_sec_dict, ticker_sec_ids,
transaction_date_price_info)
return increase_in_xns, recent_xn_value, selling_date,\
selling_subs_performance
def assemble_ticker_dataframe(ticker):
code = 'ZES/' + ticker
sph = SecurityPriceHist.objects.filter(ticker_sym=ticker).first()
# Set up blank pandas dataframe for insider info
workdf = pandas.DataFrame({
'purch_disc': [],
'sale_disc': [],
'purch_disc_ceo': [],
'sale_disc_ceo': [],
'purch_disc_no_10b5': [],
'sale_disc_no_10b5': [],
'purch_disc_ceo_no_10b5': [],
'sale_disc_ceo_no_10b5': []
})
if sph is None:
# This means there is no match to the quandl ticker in our DB.
print 'No issuer record for ticker:', ticker
return None
else:
issuer = sph.issuer
#
# Here we pull down the quandl data
basedf = quandl.get(code)
# Select the quandl columns we care about
selected_quandl_df = basedf[[
'EPS_MEAN_EST', 'EPS_ACT', 'EPS_AMT_DIFF_SURP', 'EPS_PCT_DIFF_SURP',
'EPS_STD_EST', 'EPS_CNT_EST'
]]
#
for index, row in basedf.iterrows():
# Here we use the issuer and quandl data date to pull insider data
# for a relevant period.
index_datetime = index.to_datetime().replace(tzinfo=EST)
lookback_datetime =\
index_datetime.replace(tzinfo=EST) - earnings_transaction_lookback
#
# Query pulls all discretionary issuer transactions for the period.
transactions = Form345Entry.objects\
.filter(issuer_cik=issuer, filedatetime__gte=lookback_datetime,
filedatetime__lt=index_datetime)
#
# Calc purchase and sale values
purch_disc = rep_none_with_zero(
transactions.filter(transaction_code='P').aggregate(
Sum('xn_value'))['xn_value__sum'])
sale_disc = rep_none_with_zero(
transactions.filter(transaction_code='S').aggregate(
Sum('xn_value'))['xn_value__sum'])
#
# Calc purchase and sale values for CEO
disc_ceo = transactions\
.filter(is_officer=True)\
.filter(
Q(reporting_owner_title__icontains='ceo') |
Q(reporting_owner_title__icontains='chief executive officer'))
purch_disc_ceo = rep_none_with_zero(
disc_ceo.filter(transaction_code='P').aggregate(
Sum('xn_value'))['xn_value__sum'])
sale_disc_ceo = rep_none_with_zero(
disc_ceo.filter(transaction_code='S').aggregate(
Sum('xn_value'))['xn_value__sum'])
#
# Calc purchase and sale values for non-10b5 transactions
disc_no_10b5 = transactions.filter(tenbfive_note=None)
purch_disc_no_10b5 = rep_none_with_zero(
disc_no_10b5.filter(transaction_code='P').aggregate(
Sum('xn_value'))['xn_value__sum'])
sale_disc_no_10b5 = rep_none_with_zero(
disc_no_10b5.filter(transaction_code='S').aggregate(
Sum('xn_value'))['xn_value__sum'])
#
# Calc purchase and sale values for non-10b5 transactions for CEO
disc_ceo_no_10b5 =\
disc_ceo.filter(tenbfive_note=None)
purch_disc_ceo_no_10b5 = rep_none_with_zero(
disc_ceo_no_10b5.filter(transaction_code='P').aggregate(
Sum('xn_value'))['xn_value__sum'])
sale_disc_ceo_no_10b5 = rep_none_with_zero(
disc_ceo_no_10b5.filter(transaction_code='S').aggregate(
Sum('xn_value'))['xn_value__sum'])
#
# Add the insider data to a blank working dataframe
workdf.loc[index] = \
[purch_disc, sale_disc,
purch_disc_ceo, sale_disc_ceo,
purch_disc_no_10b5, sale_disc_no_10b5,
purch_disc_ceo_no_10b5, sale_disc_ceo_no_10b5]
#
# Add our insider data to the selected quandl data
ticker_df = pandas.concat([selected_quandl_df, workdf],
axis=1,
join='inner')
return ticker_df