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

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.

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.

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

Thanks Allen, this is great!!

I was spending lots of time trying to figure it my self. Thanks a lot

Cheers!!