Results 1 to 2 of 2
  1. #1
    Scorpio11 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Location
    Cape Town, ZAR
    Posts
    36

    Fair Value of Stock

    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.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stock-take
    By Cullen1109 in forum Access
    Replies: 12
    Last Post: 10-04-2010, 11:16 AM
  2. Product / Stock Management
    By dale.90 in forum Access
    Replies: 1
    Last Post: 09-30-2010, 11:08 AM
  3. Updating Stock Quantity
    By jordanturner in forum Queries
    Replies: 11
    Last Post: 09-10-2010, 08:28 PM
  4. SQL Update stock Query HELP!!!
    By jordanturner in forum Queries
    Replies: 6
    Last Post: 09-06-2010, 10:34 AM
  5. creating a stock control database!!! HELP!!!!
    By Legend9 in forum Database Design
    Replies: 1
    Last Post: 09-10-2009, 02:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums