Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2010
    Posts
    58

    Unanswered: Stock Market Database: Calc. % Change from previous day

    Hey all, hope everyone is good. This thread is following on from my previous threads which were all based on the same Stock Market Database design.

    The relationships of the database can be seen in the diagram. For this query I need to be able to show the % change in stock price, between the previous day and today. Another thing is that I want to show the number in a colour, so if it is a positive change it would be green, and if it is a negative change, then it would be red.

    All the daily prices of the stock are found in the Historical_Stock_Data table, and the company information in the Company_Information table.
    Attached Thumbnails Attached Thumbnails Database relationship.JPG  

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    make a query that finds the prior day values. use it as a subform in the main form (it can be ultimately not visible)

    then you have the prior day's value available during current day record entry that you can call and calc with.....for colorizing depending on value you will want to implement some conditional formatting....

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    In your table, what is StockPrice? The open, close or something else? If you are only storing opening or closing prices then you will need the last two days of data to calculate a percentage.

    You can use TOP 2 and GROUP BY the Date field to get the last two days that you recorded data. Then use those dates to get the data from the Stock Data table. The syntax will be something like:

    SELECT * FROM Historical_Stock_Data WHERE Date IN (SELECT TOP 2 Date FROM Historical_Stock_Data GROUP BY Date)

    But then you have to be able to compare the two values. It is a little late in the day and I can't think of a solution right now but I think you can do it with a Cross Tab query. With the Ticker symbol as the Row and the Dates as the Columns and the Price as the data.

  4. #4
    Join Date
    Jun 2010
    Posts
    58
    Yeah that is the last traded share price, so the comparison would have to be for only the last two days. I've tried that Syntax, but when I run the query, a form pops up asking me to enter parameter values for the date, if I leave it blank it comes up with blank values, any other entry and it comes up with all the records.

    Also, now that you've mentioned it would a crosstab query be the best option to calculate the Market Cap(i.e. Last Share Price * Number of Shares[NOSH])?

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    When Access prompts for something it means it does not recognize that name as a field name, table name, query name, etc. So if you aren't expecting a prompt it usually means you have a typo in your field name.

    I didn't check your schema before I wrote the SQL. It looks like Date should be Dates. If you make the substitution it will probably work.

    The Market Cap looks like a straight forward calculation. Once you have a select query with the last share price and the number of outstanding shares then you can just multiply the two fields together to calculate the Market Cap. A cross tab query is not necessary.

  6. #6
    Join Date
    Jun 2010
    Posts
    58
    Ahhhh! Cheers for that, another new thing I know about Access now.

    I tried the query, it works now, the only problem is it gives me the first two dates and values in the database; it stores 5 years worth of data at the moment, which means it gives me the two values from 2005.

    Ok, the solution for that sounds quite simple as well. Another thing; on that, I want it to show the Market Cap in $ as well in a different column; I've got a Historical_Currency table as well, which is in a relation with the main company info table, but not with the Historical_Stock_Data table.

  7. #7
    Join Date
    Jun 2010
    Posts
    58
    Not sure what do; the Top clause is only giving me the oldest two values that are there in the table; is there a way of reversing that?

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    SELECT * FROM Historical_Stock_Data WHERE Dates IN (SELECT TOP 2 Date FROM Historical_Stock_Data GROUP BY Dates ORDER BY Dates DESC)

  9. #9
    Join Date
    Jun 2010
    Posts
    58
    ahh cheers again! I knew it had to be GROUP BY something, but learning as I go.

    Going to try the crosstab query on it now and will let you know how that goes.

  10. #10
    Join Date
    Jun 2010
    Posts
    58
    Ok have been having a few issues with the expression in my crosstab query, but I think I should be able to solve it.

    The other thing I wanted to know was, I've got the Market Cap now, I put the format of it as currency, but I'm not sure how I can specify it to use the right currency type.

    For example at the moment I've got two companies in the database one based in the UK and the other based in Australia; but it shows sign in front of both their market caps; where can I specify what currency type it should use?

    Edit: Im guessing its not possible now, because I just checked the format field again, and for currency it shows and there is another one in there called Euro as well.
    Last edited by md85; 07-16-10 at 13:01.

  11. #11
    Join Date
    May 2012
    Posts
    1

    access database for shop

    hello everyone!
    i have a problem in stock management in product table i wanna your help
    when product is purchase the quantity of product purchasing should be added to the product stock and when the procuct is saleing the quantity of product saleing should be subtracted from product stock.
    how i will do this and plz reply me its really urgent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •