From: Shane <shane-pgsql@cm.nu>
To: pgsql-sql@postgresql.org
Subject: Designing a stock portfolio database
Date: Wed, 2 Nov 2005 11:40:01 -0800
Message-ID: <20051102194001.GA21986@cm.nu> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: shane-pgsql@cm.nu
Subject: Re: Designing a stock portfolio database
In-Reply-To: <20051102194001.GA21986@cm.nu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox