title: how to calculate monthly portfolio shares and dividends
body: <p>I have a simple app that should track users stock portfolio. Basicly user may buy stocks like JNJ, AAPL or MCD. He can also sell some/all of them. Dividends might be reinvested instantly as they are paid out (same as if user did buy same stock for it's dividend value). I need to calculate this portfolio value on monthly basis.<br>
  <strong>Easy example</strong>:<br>
  Transactions:   </p>
  
  <pre><code>+----------+--------+------------+-------+
  | buy/sell | amount |    date    | price |
  +----------+--------+------------+-------+
  | buy      |      5 | 2015-01-01 |   $60 |
  | sell     |      1 | 2015-03-01 |   $70 |
  +----------+--------+------------+-------+
  </code></pre>
  
  <p>From this transactions I would like to get this dictionary of shares:</p>
  
  <pre><code>{
  u'JNJ': {
      datetime.date(2015, 6, 1): Decimal('5.00000'), 
      datetime.date(2015, 7, 1): Decimal('5.00000'),
      datetime.date(2015, 8, 1): Decimal('4.00000'), 
      datetime.date(2015, 9, 1): Decimal('4.00000'), 
      datetime.date(2015, 10, 1): Decimal('4.00000')}
  }
  </code></pre>
  
  <p>These are my shares by month. Lets say there was a  $0.75 dividend on 2015-08-21 and it on same day, I bought partial shares of JNJ on this date:</p>
  
  <p><strong>Example with Dividends</strong>:<br>
  Transactions:   </p>
  
  <pre><code>+----------+--------+------------+-------+
  | buy/sell | amount |    date    | price |
  +----------+--------+------------+-------+
  | buy      |      5 | 2015-01-01 |   $60 |
  | sell     |      1 | 2015-03-01 |   $70 |
  +----------+--------+------------+-------+
  </code></pre>
  
  <p>Dividends:</p>
  
  <pre><code>+------------+--------+-------+
  |    date    | amount | price |
  +------------+--------+-------+
  | 2015-08-21 | 0.75   |    64 |
  +------------+--------+-------+
  </code></pre>
  
  <p>When dividend was paid, I was holding 4 shares. For 4 shares I received 4*$0.75 and I bought 0.031393889 shares of JNJ.
  Result:  </p>
  
  <pre><code>{u'JNJ': 
      {  
          datetime.date(2015, 6, 1): Decimal('5.00000'), 
          datetime.date(2015, 7, 1): Decimal('5.00000'),
          datetime.date(2015, 8, 1): Decimal('4.031393889'),
          datetime.date(2015, 9, 1): Decimal('4.031393889'), 
          datetime.date(2015, 10, 1): Decimal('4.031393889')}
  } 
  </code></pre>
  
  <p>So this is what I have to calculate. There might be any number of transaction and dividends. There must be at least one Buy transaction, but dividends may not exist.<br>
  <strong>These are my classes in models.py:</strong><br>
  Stock model representing Stock, for example JNJ.  </p>
  
  <pre><code>class Stock(models.Model):
      name = models.CharField("Stock's name", max_length=200, default="")
      symbol = models.CharField("Stock's symbol", max_length=20, default="", db_index=True)
      price = models.DecimalField(max_digits=30, decimal_places=5, null=True, blank=True)
  </code></pre>
  
  <p>Than I have StockTransaction, which represends object for one stock for one portfolio. Transactions are linked to StockTransaction, because drip applies to all Transactions.</p>
  
  <pre><code>class StockTransaction(models.Model):
      stock = models.ForeignKey('stocks.Stock')
      portfolio = models.ForeignKey(Portfolio, related_name="stock_transactions")
      drip = models.BooleanField(default=False)
  </code></pre>
  
  <p>Transaction class:</p>
  
  <pre><code>BUYCHOICE = [(True,'Buy'),(False,'Sell')]
  class Transaction(models.Model):
      amount = models.DecimalField(max_digits=20, decimal_places=5, validators=[MinValueValidator(Decimal('0.0001'))])
      buy = models.BooleanField(choices=BUYCHOICE, default=True)
      date = models.DateField('buy date')
      price = models.DecimalField('price per share', max_digits=20, decimal_places=5, validators=[MinValueValidator(Decimal('0.0001'))])
  
      stock_transaction = models.ForeignKey(StockTransaction, related_name="transactions", null=False)
  </code></pre>
  
  <p>and lastly Dividend class:</p>
  
  <pre><code>class Dividend(models.Model):
      date = models.DateField('pay date', db_index=True)
      amount = models.DecimalField(max_digits=20, decimal_places=10)
      price = models.DecimalField('price per share', max_digits=20, decimal_places=10)
      stock_transaction = models.ManyToManyField('portfolio.StockTransaction', related_name="dividends", blank=True)
      stock = models.ForeignKey(Stock, related_name="dividends")
  </code></pre>
  
  <p>I've coded my method, but I do think there is a better way. My method is too long and takes to much time for portfolio with 106 stocks (each 5 transactions). Here is my method:  </p>
  
  <pre><code>def get_portfolio_month_shares(portfolio_id):
      """
      Return number of dividends and shares per month respectfully in dict
      {symbol: {year: decimal, year: decimal} }
      :param portfolio: portfolio object for which to calculate shares and dividends
      :return: total dividends and amount of shares, respectfully
      """
  
      total_shares, total_dividends = {}, {}
      for stock_transaction in StockTransaction.objects.filter(portfolio_id=portfolio_id)\
              .select_related('stock').prefetch_related('dividends', 'transactions', 'stock__dividends'):
          shares = 0 #number of shares
          monthly_shares, monthly_dividends = {}, {}
          transactions = list(stock_transaction.transactions.all())
          first_transaction = transactions[0]
  
          for dividend in stock_transaction.stock.dividends.all():
              if dividend.date &lt; first_transaction.date:
                  continue
              try:
                  #transactions that are older than last dividend
                  while transactions[0].date &lt; dividend.date:
                      if transactions[0].buy:
                          shares = shares + transactions[0].amount
                      else: #transaction is a sell
                          shares = shares - transactions[0].amount
                      monthly_shares[date(transactions[0].date.year, transactions[0].date.month, 1)] = shares
                      transactions.remove(transactions[0])
              except IndexError: #no more transactions
                  pass
              if dividend in stock_transaction.dividends.all(): # if drip is active for dividend
                  if dividend.price!=0:
                      shares += (dividend.amount * shares / dividend.price)
                      monthly_shares[date(dividend.date.year, dividend.date.month, 1)] = shares
              try:
                  monthly_dividends[date(dividend.date.year, dividend.date.month, 1)] += shares * dividend.amount
              except KeyError:
                  monthly_dividends[date(dividend.date.year, dividend.date.month, 1)] = shares * dividend.amount
  
          #fill blank months with 0
          if monthly_shares!={}:
              for dt in rrule.rrule(rrule.MONTHLY,
                                    dtstart=first_transaction.date,
                                    until=datetime.now() + relativedelta.relativedelta(months=1)):
                  try:
                      monthly_shares[date(dt.year, dt.month, 1)]
                  except KeyError: #keyerror on dt year
                      dt_previous = dt - relativedelta.relativedelta(months=1)
                      monthly_shares[date(dt.year, dt.month, 1)] = monthly_shares[date(dt_previous.year, dt_previous.month, 1)]
                  try:
                      monthly_dividends[date(dt.year, dt.month, 1)]
                  except KeyError:
                      monthly_dividends[date(dt.year, dt.month, 1)] = 0
  
          # for each transaction not covered by dividend for cycle
          if transactions:
              for transaction in transactions:
                  for dt in rrule.rrule(rrule.MONTHLY,
                                dtstart=transaction.date,
                                until=datetime.now() + relativedelta.relativedelta(months=1)):
                      if transaction.buy:
                          try:
                              monthly_shares[date(dt.year, dt.month, 1)] += transaction.amount
                          except KeyError:
                              monthly_shares[date(dt.year, dt.month, 1)] = transaction.amount
                      else: #sell
                          monthly_shares[date(dt.year, dt.month, 1)] -= transaction.amount
          total_dividends[stock_transaction.stock.symbol] = monthly_dividends
          total_shares[stock_transaction.stock.symbol] = monthly_shares
      return total_dividends, total_shares
  </code></pre>
  
  <p><strong>Description</strong>:<br>
  First for cycle - for each stock in portfolio.<br>
  Second for cycle - for each stock's dividend<br>
  this line <code>if dividend in stock_transaction.dividends.all()</code> checks if dividends are reinvested. There exists m2m relation between stock_transaction and dividend objects if they are.<br>
  for cycle with rrule fills up blank months to previous month value.  </p>
  
  <hr>
  
  <p><strong>EDIT1:</strong><br>
  I already optimized number of sql queries with django-debug-toolbar (4 sql queries needed). My code is slow probably because of many objects and large dictionaries. </p>