def export(request, portfolio, is_sample, read_only, format):
format = format.lower()
name = ('DEMO' if is_sample else portfolio.name)
response = HttpResponse(mimetype = ('text/%s' % format))
response['Content-Disposition'] = 'attachment; filename=transactions-%s-%s.%s' % (name, datetime.now().strftime('%Y%m%d'), format)
if format == 'csv':
transactions_as_csv(response, portfolio)
elif format == 'ofx':
transactions = Transaction.objects.filter(portfolio__id__exact = portfolio.id).order_by('-as_of_date', '-id')
for transaction in transactions:
transaction.commission = abs(transaction.total - (transaction.price * transaction.quantity))
transaction.quantity = ((-transaction.quantity) if transaction.type == 'SELL' else transaction.quantity)
transaction.total = ((-transaction.total) if transaction.type == 'BUY' or transaction.type == 'WITHDRAW' else transaction.total)
quotes = [ quote_by_symbol(symbol) for symbol in set([t.symbol for t in transactions]).difference([CASH_SYMBOL]) ]
response.write(render_to_string('transactions.ofx', {
'portfolio' : portfolio,
'transactions': transactions,
'start_date' : min([t.as_of_date for t in transactions]),
'end_date' : max([t.as_of_date for t in transactions]),
'quotes' : quotes,
}))
return response
def parse_charles_transactions(reader):
parsed = []
for row in reader:
date_field = row[0][:10]
action_field = row[1].strip(' ')
quantity_field = row[2].strip(' ')
symbol_field = row[3].strip(' ')
price_field = row[5].replace('$', '').strip(' ')
amount_field = row[6].replace('$', '').strip(' ')
commission_field = row[7].replace('$', '').strip(' ').strip('*')
linked_symbol = None
# deposits and withdrawals have no symbols or prices
if symbol_field == '' and price_field == '':
symbol = CASH_SYMBOL
type = ('DEPOSIT' if float(amount_field) >= 0 else 'WITHDRAW')
quantity = abs(float(amount_field))
price = 1.0
commission = 0.0
# buys and sells
elif action_field == 'Buy' or action_field == 'Sell':
symbol = symbol_field
type = ('SELL' if action_field == 'Sell' else 'BUY')
quantity = float(quantity_field)
price = float(price_field)
commission = (float(commission_field) if commission_field != '' else 0.0)
# transfers have a symbol and quantity, and little else
elif symbol_field != '' and quantity_field != '' and amount_field == '':
as_of_date = datetime.strptime(date_field, '%m/%d/%Y')
symbol = symbol_field
quantity = float(quantity_field)
price = price_as_of(quote_by_symbol(symbol), as_of_date)
parsed.append({
'date' : as_of_date.date(),
'type' : 'DEPOSIT',
'symbol' : CASH_SYMBOL,
'quantity' : (price * quantity),
'price' : 1.0,
'total' : (price * quantity),
})
type = 'BUY'
commission = 0.0
# everything else is an adjustment
else:
symbol = CASH_SYMBOL
type = 'ADJUST'
quantity = float(amount_field)
price = 1.0
commission = 0.0
linked_symbol = symbol_field
commission_multiplier = 1.0
if type == 'SELL':
commission_multiplier = -1.0
parsed.append({
'date' : datetime.strptime(date_field, '%m/%d/%Y').date(),
'type' : type,
'symbol' : symbol,
'quantity' : quantity,
'price' : price,
'total' : ((quantity * price) + (commission_multiplier * commission)),
'linked_symbol': linked_symbol,
})
return parsed
def _get_performance_history(portfolio, days):
query = """
SELECT D.portfolio_date,
D.deposit,
D.withdrawal,
SUM(P.quantity * ((CASE WHEN P.symbol = '*CASH' OR Q.cash_equivalent = '1' THEN 1.0 ELSE H.price END))) as market_value
FROM position P
JOIN
(
SELECT D.portfolio_id,
D.portfolio_date,
D.position_date,
SUM(CASE WHEN T.type = 'DEPOSIT' THEN T.total ELSE 0 END) as deposit,
SUM(CASE WHEN T.type = 'WITHDRAW' THEN T.total ELSE 0 END) as withdrawal
FROM (
SELECT P.portfolio_id,
D.portfolio_date,
MAX(P.as_of_date) as position_date
FROM (
SELECT DISTINCT
D.portfolio_id,
DATE(H.as_of_date) as portfolio_date
FROM (
SELECT DISTINCT
P.portfolio_id,
P.symbol
FROM position P
WHERE P.symbol != '*CASH'
AND P.portfolio_id = %(portfolio_id)s
) S
JOIN quote Q ON (Q.symbol = S.symbol)
JOIN price_history H ON (H.quote_id = Q.id)
JOIN
(
SELECT P.portfolio_id,
MIN(as_of_date) as start_date
FROM position P
WHERE P.portfolio_id = %(portfolio_id)s
GROUP BY P.portfolio_id
) D ON (D.portfolio_id = S.portfolio_id AND H.as_of_date >= D.start_date)
WHERE DATEDIFF(NOW(), H.as_of_date) < %(days)s
) D
JOIN
(
SELECT DISTINCT
P.portfolio_id,
P.as_of_date
FROM position P
WHERE P.portfolio_id = %(portfolio_id)s
) P ON (P.portfolio_id = D.portfolio_id AND P.as_of_date <= D.portfolio_date)
GROUP BY D.portfolio_id,
D.portfolio_date
) D
LEFT JOIN
(
SELECT T.portfolio_id,
T.as_of_date,
T.type,
T.total
FROM transaction T
WHERE T.type IN ('DEPOSIT', 'WITHDRAW')
AND T.portfolio_id = %(portfolio_id)s
) T ON (T.portfolio_id = D.portfolio_id AND T.as_of_date = D.portfolio_date)
GROUP BY D.portfolio_id,
D.portfolio_date
) D ON (P.portfolio_id = D.portfolio_id AND P.as_of_date = D.position_date)
LEFT JOIN quote Q ON (Q.symbol = P.symbol)
LEFT JOIN price_history H ON (H.quote_id = Q.id AND H.as_of_date = D.portfolio_date)
WHERE P.quantity <> 0
AND P.portfolio_id = %(portfolio_id)s
GROUP BY D.portfolio_date
ORDER BY D.portfolio_date"""
cursor = connection.cursor()
cursor.execute(query, { 'days' : days, 'portfolio_id' : portfolio.id })
benchmark_quote = quote_by_symbol(PERFORMANCE_BENCHMARK_SYMBOL)
cutoff_date = datetime.now().date() - timedelta(days = DAYS_IN_PERFORMANCE_HISTORY)
benchmark_price = {}
for history in benchmark_quote.pricehistory_set.filter(as_of_date__gte = cutoff_date).order_by('as_of_date'):
benchmark_price[history.as_of_date.date()] = history.price
shares = None
last_price = None
first_benchmark = None
out = []
for row in cursor.fetchall():
as_of_date = row[0]
deposit = float(row[1])
withdraw = float(row[2])
market_value = float(row[3])
benchmark = benchmark_price.get(as_of_date, 0.0)
if first_benchmark == None:
first_benchmark = benchmark
performance = 0
if shares == None:
shares = market_value
else:
net_inflow = deposit - withdraw
shares += net_inflow / last_price
performance = (((market_value / shares) - 1) if shares <> 0 else 0)
last_price = ((market_value / shares) if shares <> 0 else 1.0)
benchmark_performance = (((benchmark / first_benchmark) - 1) if first_benchmark <> 0 else 0)
out.append(PerformanceHistory(as_of_date, performance, benchmark_performance))
cursor.close()
return out
