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/