How to keep track changing items in a stock portfolio?

Question

I have a system where people can pick some stocks and it values their portfolios but I'm having trouble doing this in a efficient way on a daily basis because I'm creating entries for days that don't have any changes(think of it like I'm measuring the values and having version control so I can track changes to the way the portfolio is designed).

Here's a example(each day's portfolio with stock name and weight):

Day1:
ibm = 10%
microsoft = 50%
google = 40%

day5:
ibm = 20%
microsoft = 20%
google = 40%
cisco = 20%

I can measure the value of the portfolio on day1 and understand I need to measure it again on day5(when it changed) but how do I measure day2-4 without recreating day1's entry in the database?

My approach right now(which I don't like) is to create a temp entry in my database for when someone changes the portfolio and then at the end of the day when I calculate the values if there is a temp entry I use that otherwise I create a new entry(for day2-4) using the last days data. The issue is as data often doesn't change I'm creating entries that are basically duplicates. The catch is: my stock data is all daily. I also thought of taking the portfolio and if it hasn't been updated in 3 days to find the returns of the last 3 days for each stock but I wasn't sure if there was a better solution.

Any ideas? I think this is a straight forward problem but I just can't see a efficient way of doing it.

note: in finance terms, its called creating a NAV and most firms do it the inefficient way I'm doing it but its because the process was created like 50 years ago and hasn't changed. I think this problem is very similar to version control but I can't seem to make a solution.

Answer 1

In storage terms is makes most sense to just store:

UserId - StockId1 - 23% - 2012-06-25 UserId - StockId2 - 11% - 2012-06-26 UserId - StockId1 - 20% - 2012-06-30

So you see that stock 1 went down at 30th. Now if you want to know the StockId1 percentage at the 28th you just select:

SELECT * 
FROM stocks 
WHERE datecolumn<=DATE(2012-06-28) 
ORDER BY datecolumn DESC LIMIT 0,1

If it gives nothing back you did not have it, otherwise you get the last position back.

BTW. if you need for example a graph of stock 1 you could left join against a table full of dates. Then you can fill in the gaps easily.

Found this post here for example:

UPDATE mytable
SET number = (@n := COALESCE(number, @n))
ORDER BY date;

SQL QUERY replace NULL value in a row with a value from the previous known value