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.
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....
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.
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])?
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.
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.
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.
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