Fair Value of Stock

[Scorpio11]

I have a question regarding the layout of a database and I am not sure how to set it up



I have a database that keeps tracks of stocks and shares on a stock exchange. Now I have gotten the system working (mostly) keeping track of what I buy, sell and income earned.

I am now wanting to add a fair value component to it. The problem is that I have no idea of how to lay out the tables.

In one table I woud have the following example Rows

R1: - MSFT ; 01/10/2010 ; Purchase ; 100 ; $5000
R2: - MSFT ; 06/14/2010 ; Purchase ; 200 ; $7000

This means that I have 300 shares valued ay $12 000. Since shares change their value every day, so i would have to have a totally separate table set up for their closing values. and the query would have to make not of the fact that my holding in microsoft went up 200 shares on 14 June

My problem is this: The way i am currently conceptualising it, The purchase of the share would be a row in my one table. but in the fair value table, MSFT would have to be a column header and not a row. getting the 2 to connect would more or less okay, but a column automatically added when i add a row item for purchasing a share is something i have no idea about. If i can get the fair value of MSFT as a row item with a date and a value would make things easy as MSFT would be my link between the tables, but the linking of the formula is he other problem.

The fair value table would look like this as i am seeing it but adds SO many complications

ID DATE MSFT HP GOOG
1 01/10/2010 36.25 25.36 400.36
2 01/11/2010 35.26 22.36 380.26
3 01/12/2010 40.36 23.36 390.36
4 01/13/2010 58.00 24.36 440.00
5 01/14/2010 36.36 35.36 500.00

If have can have these 5 rows represented into rows where the 3 companies shown above are rows and not columns are my idea result, but to have 1 table for multiple companies is something i cannot picture at present. Any help on the layout it greatly appreciated.

Now assuming i can get this working. There would be an additional problem.

As the months and years go on, there would be additional rows for access to calculate the value for, meaning that it would get slower and slower to the point of madness. Can I set access to calculate the value once (for that query only) and never again, unless i expressly select a recalculate function because if i had to open the sheet afer 3 years (for eg), it would take several minutes before i have even view it let alone do anything else?

Any help with this would be greatly appreciated.

[NTC]

well in general terms you have in your 'Purchase' table the stock & quantity; while in your 'DailyPrice' table you have your stock & close price.

so really what is generally done in db design is to join these on the stock symbol in a query; and then in the query you can set up a calculated value of the quantity x dailyprice.

I would start with an aggregate query that sums all stock of the same kind - and then use this aggregate query and not the Purchase table.

Hope this helps you going in the right direction.