If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup function for closing price stocks
Hi there,
I upload on my table a data text file containing a list of stocks everyday. with the following column headings: Ticket, high, low, close. I would like to find a way to create another table containing the previous closing date. Therefore the column headings would be; Ticket, previous$, high, low, close, gain/loss. Some days a stock might no be trading for 3 days and also saturdays and sundays are not trading days for those stocks. I would appreciate your suggestions as I've been spending time on creating two queries and then putting togetherin to one query but I cannot get the trading date right. Thank you |
#2
|
|||
|
|||
Lookup function for closing price stocks
Use a subquery to get the most recent close value.
Details in: http://allenbrowne.com/subquery-01.html#AnotherRecord -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwwjd" wrote in message ... I upload on my table a data text file containing a list of stocks everyday. with the following column headings: Ticket, high, low, close. I would like to find a way to create another table containing the previous closing date. Therefore the column headings would be; Ticket, previous$, high, low, close, gain/loss. Some days a stock might no be trading for 3 days and also saturdays and sundays are not trading days for those stocks. I would appreciate your suggestions as I've been spending time on creating two queries and then putting togetherin to one query but I cannot get the trading date right. |
#3
|
|||
|
|||
Lookup function for closing price stocks
Allen, thanks for the link. I use one of the examples as a guidance
Now my only challenge is to return the previous close value as it can be multiple numbers as the dates changes. for now I'm using this [dupe].date = [01 all].date - 1 to bring me the previous day closing price. Is it possible to get a variation of this formula so it can bring the last previous date with closing date? This is so far the sql that I have for now. SELECT [01 All].*, (select top 1 [dupe].close from [01 all] as [dupe] where [dupe].ticket = [01 all].ticket and [dupe].date = [01 all].date - 1) AS priorvalue FROM [01 All]; THANKS FOR YOUR HELP "Allen Browne" wrote: Use a subquery to get the most recent close value. Details in: http://allenbrowne.com/subquery-01.html#AnotherRecord -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwwjd" wrote in message ... I upload on my table a data text file containing a list of stocks everyday. with the following column headings: Ticket, high, low, close. I would like to find a way to create another table containing the previous closing date. Therefore the column headings would be; Ticket, previous$, high, low, close, gain/loss. Some days a stock might no be trading for 3 days and also saturdays and sundays are not trading days for those stocks. I would appreciate your suggestions as I've been spending time on creating two queries and then putting togetherin to one query but I cannot get the trading date right. |
#4
|
|||
|
|||
Lookup function for closing price stocks
Ask for a lesser date, and use an ORDER BY clause to sort so the record from
the most recent date is the one returned: SELECT [01 All].*, (select top 1 [dupe].[close] from [01 all] as [dupe] where ([dupe].ticket = [01 all].ticket) AND ([dupe].[date] [01 all].[date]) ORDER BY dupe.[date] DESC, dupe.ID DESC) AS priorvalue FROM [01 All]; Some of the field names you used are reserved words: e.g. Close and Date. Be sure to put square brackets around them. In general, it's best to avoid these names. Here's a list to refer to when designing tables: http://allenbrowne.com/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jwwjd" wrote in message ... Allen, thanks for the link. I use one of the examples as a guidance Now my only challenge is to return the previous close value as it can be multiple numbers as the dates changes. for now I'm using this [dupe].date = [01 all].date - 1 to bring me the previous day closing price. Is it possible to get a variation of this formula so it can bring the last previous date with closing date? This is so far the sql that I have for now. SELECT [01 All].*, (select top 1 [dupe].close from [01 all] as [dupe] where [dupe].ticket = [01 all].ticket and [dupe].date = [01 all].date - 1) AS priorvalue FROM [01 All]; Use a subquery to get the most recent close value. Details in: http://allenbrowne.com/subquery-01.html#AnotherRecord |
#5
|
|||
|
|||
Lookup function for closing price stocks
Thanks Allen, this is great!! I was spending lots of time trying to figure it my self. Thanks a lot Cheers!! |
Thread Tools | |
Display Modes | |
|
|