At first blush this seems to be a straightforward inventory table. On
closer examination though, it appears that portfolio holdings (past and
present) are actually a residual of transaction analysis of the
investment account journal. An investor is not just interested in open
positions, but closed positions as well. I'm not interested in current
prices or historical analysis or comprehensive stock tables (the sorts
of things that are discussed to death).

Assume a journal has the following columns:

ID Date Type Symbol Description Quantity Price Amount

Type is in a separate table (Buy,Sell,Deposit,Withdraw,Dividend,Split,
Interest)
Symbol is in a table of company names

The idea then is to generate open positions (or closed, or whatever)
from the transaction journal.

This solution must have been reinvented about a million times by now,
but I can't seem to find much information on it. Surprisingly, I see
recent calls for papers on the topic, so maybe nobody has a good
solution yet...

Is this approach completely wrong headed? Should I instead be focusing
on the inventory approach? I have this prototyped and working in the
small, but being a noob I don't want to get too invested in a schema
that won't scale, or violates some DB design principle that will
eventually result in my regret ;-)

Thanks in advance for your thoughts,

bullshark

"bullshark" <bullshark (AT) gmail (DOT) com> wrote:

ideal for this application provided we are not talking about a
portfolio the size of a retail investment fund.

I don't know about elsewhere but, in Australia, your transaction lacks
some vital information. Here, if you buy a stock at two or more
different prices, you can choose which shares you are selling for the
purpose of calculating the capital gains tax. A sale needs to refer to
a particular, or even several, buy transactions.

David Segall wrote:
Yes, I left a field or two off the description for brevity sake (so I
wouldn't have to explain them).

I have "SellID" in the journal which can be linked to specific buys in
a number of ways. The most straightforward (obvious) is to enter the ID
of the matching buy, decomposing the the Sell into multiple
transactions when it applies to multiple Buys. A structured comment
provides a basis for re-consituting the sale transaction.

bullshark