Date: Wed, 2 Nov 2005 11:40:01 -0800
From: Shane <shane-pgsql@cm.nu>
To: pgsql-sql@postgresql.org
Subject: Designing a stock portfolio database
Message-ID: <20051102194001.GA21986@cm.nu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.9i

Hello,

I am attempting to design a database capable of displaying
a stock portfolio.  I have run into some early problems
though suggesting that I might have some design problems.

I have two simple transaction tables as follows:
create table stock_transactions(
id serial not null,
date timestamp(0) without time zone,
symbol varchar(12) not null,
currency char(3) not null,
shares int not null,  -- negative for a sell or a short
multiplier int not null default 1, -- of contracts for
                                   -- options
price numeric not null,
commission numeric not null default 0,
notes text,
primary key(id)
);

create table cash_transactions(
id serial not null,
date timestamp(0) without time zone not null,
currency char(3) not null,
amount numeric not null, -- may be negative
div_symbol varchar(12) default null, -- for stock dividends
notes text,
primary key(id)
);

Finally, I have a query to display the portfolio's current
holdings.

create view stock_holdings as
SELECT t.symbol, t.currency, sum(t.shares) AS shares, t.multiplier,
sum(t.price * t.shares::numeric * t.multiplier::numeric) AS price_paid,
sum(t.commission) AS commission,
COALESCE(( SELECT sum(cash_transactions.amount) AS sum
FROM cash_transactions
WHERE cash_transactions.div_symbol::text = t.symbol::text), 0::numeric)
AS dividends
FROM stock_transactions t
GROUP BY t.symbol, t.currency, t.multiplier;

The above query however has a problem.  If the user trades
out of a stock and then back in, the price_paid isn't
right as it's calculating the previous purchase as well.

Should a portfolio DB instead have a real stock_holdings
table and have functions update it as new transactions are
inserted or is there a better way to write the view so as
include closed positions in the output?

Also, I haven't found many oss projects which do stock
portfolios in SQL but did look at beancounter which keeps a
similar transaction log in the db but does all the view
stuff in the code.  Are there any other portfolio pgsql
projects out there I could look at?

Best regards,
Shane

-- 
http://www.cm.nu/~shane/