I am going to create small system/website that will allow me to manage my stocks, and monitor the portfolio. I do not have much of it, but I cannot access the data from wherever I want and it is not so dynamic.
Beside the design itself I came across a problem of recreating a FIFO (first in first out) type of queue. Why that? Because if I buy and sell stock and I want to see the current value of my opened position that is how it should be calculated: the first stock I buy that is also the first that is out of the book when I sell. The solution below was made in PostgreSQL 9.1.
So, assume we have a table called transactions consisting of fields:
id – trade id
tr_date – trade date
price – stock price
type – 1-sell; 0-buy
amount – number of stocks
is_opened – flag indicating that transactions have not been closed
id_stock – foreign key to stock table
id_user – foreign key to user table
SELECT id, tr_date, price, amount_left , amount_left*price AS value FROM ( SELECT id, tr_date, price, amount , GREATEST(SUM(amount) OVER(ORDER BY type, tr_date), 0) AS amount_left FROM ( SELECT id, tr_date, type, price, amount FROM ( SELECT 0 as id , to_date('1900-01-01','YYYY-MM-DD') AS tr_date , 0 As price , type , -SUM(amount) AS amount FROM transactions WHERE is_opened IS TRUE AND type IS FALSE AND id_user = 3 AND id_stock = 3 GROUP BY type UNION SELECT id, tr_date, price, type, amount FROM transactions WHERE is_opened IS TRUE AND type IS TRUE AND id_user = 3 AND id_stock = 3 ) AS x ) AS y ) AS z WHERE amount_left > 0 ORDER BY tr_date;
How does it work? For a given id_user = 3 and id_stock = 3 take all transactions that are opened, is_opened IS TRUE – that is a common part of two SELECTs joined by UNION statement. The difference between them is that the upper one sums up all the sell transactions, whereas the bottom one has only buy transactions.
id | tr_date | price | type | amount
0 | 1900-01-01 | 0 | f | -150
1 | 2013-09-01 | 7 | t | 100
2 | 2013-09-03 | 8.1 | t | 50
6 | 2013-06-05 | 7.8 | t | 60
In the next step, just for clarity, I pick all the important values. The next one is a window function that given the order by type and tr_date makes all the calculations. This way in new column, amount_left, we get a current number of a given stock we own.
id | tr_date | price | amount | amount_left
0 | 1900-01-01 | 0 | -150 | 0
6 | 2013-06-05 | 7.8 | 60 | 0
1 | 2013-09-01 | 7 | 100 | 10
2 | 2013-09-03 | 8.1 | 50 | 60
The very last step just picks the transactions that have some shares left in the portfolio, strong>amount_left>0, and orders them by trade date, tr_date.
id | tr_date | price | amount_left | value
1 | 2013-09-01 | 7 | 10 | 70
2 | 2013-09-03 | 8.1 | 60 | 486
From the performance point of view, assuming I have many users, many stock, and much more transactions. I have created two partial indexes on “opened” buy/sell transactions. By opened transaction I understand those transactions that have not been closed. Transaction will be marked as closed if after entering new sell transaction all amounts of previous sell/buy transactions sum up to 0. In other words all transactions are OUT. Below the code for the partial indexes.
CREATE INDEX CONCURRENTLY idx_trans_user_stock_f ON transactions(id_user, id_stock) WHERE is_opened IS TRUE AND type IS FALSE; CREATE INDEX CONCURRENTLY idx_trans_user_stock_t ON transactions(id_user, id_stock) WHERE is_opened IS TRUE AND type IS TRUE;
I thought about making bitmap indexes, but as I found out they are not implemented in Postgres. Besides, doing two partial indexes that cover exclusive parts of table seems a reasonable idea.