示例#1
0
文件: views.py 项目: andlister/frano
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
示例#2
0
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
示例#3
0
文件: views.py 项目: andlister/frano
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