How to show time for day's stock market high/low

  • Thread starter Thread starter Seano
  • Start date Start date
S

Seano

I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks
 
Seano,

This is probably a dumb question, but I will ask it anyway. Doesn't online
sites like Yahoo Finance all ready have that info? And to go one step further
I have seen "real time" quotes and other tools.

Richard
 
Hi Richard,

Yahoo Finance doesn't provide historical intraday data, but that's really
not the issue. I have the data, now I want to analyse it. I was hoping Access
might be able to extract not just the high and low of the day (easy enough to
find), but the actual time the low and high occurred for every day. All the
info is in my database, but with nearly 700,000 entries, it's not something I
want to extract manually.
 
I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks
Click to expand...

The Format function isn't necessary (or even a very good way to do this): the
DateValue() function will extract just the date portion for grouping. To get
the high for the day and its time you can use a subquery. Could you post the
actual fieldnames of your table, and indicate which field contains the price
of interest? Is there in fact a high/low/open/close field for each *minute*?
And does the field (misnamed, it's a reserved word) Date contain the date and
time?
 
Thanks John, the fieldnames are date/time/open/high/low/close/volume for each
minute of the day. Date and time originally came as one field but I separated
them (I could join them again if needed). So I guess I'm looking for the Max
of the high field for the day, and the minute it occurred, and the Min of the
low field, and the minute it occurred.

John W. Vinson said:
I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks
Click to expand...

The Format function isn't necessary (or even a very good way to do this): the
DateValue() function will extract just the date portion for grouping. To get
the high for the day and its time you can use a subquery. Could you post the
actual fieldnames of your table, and indicate which field contains the price
of interest? Is there in fact a high/low/open/close field for each *minute*?
And does the field (misnamed, it's a reserved word) Date contain the date and
time?
Click to expand...
 
You did not respond to John's request -- Could you post the actual
fieldnames of your table, and indicate which field contains the price of
interest?


--
Build a little, test a little.


Seano said:
Thanks John, the fieldnames are date/time/open/high/low/close/volume for each
minute of the day. Date and time originally came as one field but I separated
them (I could join them again if needed). So I guess I'm looking for the Max
of the high field for the day, and the minute it occurred, and the Min of the
low field, and the minute it occurred.

John W. Vinson said:
I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks
Click to expand...

The Format function isn't necessary (or even a very good way to do this): the
DateValue() function will extract just the date portion for grouping. To get
the high for the day and its time you can use a subquery. Could you post the
actual fieldnames of your table, and indicate which field contains the price
of interest? Is there in fact a high/low/open/close field for each *minute*?
And does the field (misnamed, it's a reserved word) Date contain the date and
time?
Click to expand...
Click to expand...
 
To get the date and time and the high you can use a correlated sub-query.
SELECT [Date], [Time], [High]
FROM [SomeTable]
WHERE [High] =
(SELECT Max(High)
FROM [SomeTable] as T
WHERE T.[Date]=[SomeTable].[Date])

You would need s similar query to get the lows
SELECT [Date], [Time], [Low]
FROM [SomeTable]
WHERE [Low] =
(SELECT Min([Low])
FROM [SomeTable] as T
WHERE T.[Date]=[SomeTable].[Date])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, the fieldnames are date/time/open/high/low/close/volume for each
minute of the day. Date and time originally came as one field but I separated
them (I could join them again if needed). So I guess I'm looking for the Max
of the high field for the day, and the minute it occurred, and the Min of the
low field, and the minute it occurred.

John W. Vinson said:
I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks
Click to expand...
The Format function isn't necessary (or even a very good way to do this): the
DateValue() function will extract just the date portion for grouping. To get
the high for the day and its time you can use a subquery. Could you post the
actual fieldnames of your table, and indicate which field contains the price
of interest? Is there in fact a high/low/open/close field for each *minute*?
And does the field (misnamed, it's a reserved word) Date contain the date and
time?
Click to expand...
Click to expand...
 
Forgive my newbie ignorance. I'm not 100% sure what you require, but for this
query the fields I need are [Date],[Time],[High],[Low] which show in the
table:

Date Time Open High Low Close
18/12/2008 3:25:00 PM 3581 3581 3580 3580 18/12/2008 3:26:00
PM 3584 3584 3582 3584
18/12/2008 3:27:00 PM 3584 3587 3584 3584
so every Date (for one day) has 1440 Time intervals of one minute.

I want to find the highest [High] for every day (i.e. 24-hour period) in the
database, and also the [Time] (i.e. one minute period) when that high
occurred.

Ditto for lowest [Low].


KARL DEWEY said:
You did not respond to John's request -- Could you post the actual
fieldnames of your table, and indicate which field contains the price of
interest?


--
Build a little, test a little.


Seano said:
Thanks John, the fieldnames are date/time/open/high/low/close/volume for each
minute of the day. Date and time originally came as one field but I separated
them (I could join them again if needed). So I guess I'm looking for the Max
of the high field for the day, and the minute it occurred, and the Min of the
low field, and the minute it occurred.

John W. Vinson said:
I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks


The Format function isn't necessary (or even a very good way to do this): the
DateValue() function will extract just the date portion for grouping. To get
the high for the day and its time you can use a subquery. Could you post the
actual fieldnames of your table, and indicate which field contains the price
of interest? Is there in fact a high/low/open/close field for each *minute*?
And does the field (misnamed, it's a reserved word) Date contain the date and
time?
Click to expand...
Click to expand...
Click to expand...
 
Many thanks, John. I managed to get this working, and I feel closer to the
answer, but alas it returns the Max(High) for every minute in the database,
not the highest High for the day. I'm after the highest High in every 24-hour
period and the Time (minute) that occurred. Apologies for not explaining it
properly (see my reply to Karl Dewey for further clarification).

John Spencer said:
To get the date and time and the high you can use a correlated sub-query.
SELECT [Date], [Time], [High]
FROM [SomeTable]
WHERE [High] =
(SELECT Max(High)
FROM [SomeTable] as T
WHERE T.[Date]=[SomeTable].[Date])

You would need s similar query to get the lows
SELECT [Date], [Time], [Low]
FROM [SomeTable]
WHERE [Low] =
(SELECT Min([Low])
FROM [SomeTable] as T
WHERE T.[Date]=[SomeTable].[Date])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, the fieldnames are date/time/open/high/low/close/volume for each
minute of the day. Date and time originally came as one field but I separated
them (I could join them again if needed). So I guess I'm looking for the Max
of the high field for the day, and the minute it occurred, and the Min of the
low field, and the minute it occurred.

John W. Vinson said:
I've set up a database of stock market intraday data going back two years,
showing Open/High/Low/Close data for every one minute bar of the day. I'm
trying to find 1) the high and low of each day, and 2) the times they
occurred.

Using: Date By Day: Format$([Stock Data].[Date],'yyyy.mm.dd') I can find the
Max of High and Min of Low to give me the day's high and low, but how do I
find the minute these occurred (there may be more than one, so if only one
could be displayed, the first occurence would be best ).

Many thanks

The Format function isn't necessary (or even a very good way to do this): the
DateValue() function will extract just the date portion for grouping. To get
the high for the day and its time you can use a subquery. Could you post the
actual fieldnames of your table, and indicate which field contains the price
of interest? Is there in fact a high/low/open/close field for each *minute*?
And does the field (misnamed, it's a reserved word) Date contain the date and
time?
Click to expand...
Click to expand...
.
Click to expand...
 
Many thanks, John. I managed to get this working, and I feel closer to the
answer, but alas it returns the Max(High) for every minute in the database,
not the highest High for the day. I'm after the highest High in every 24-hour
period and the Time (minute) that occurred. Apologies for not explaining it
properly (see my reply to Karl Dewey for further clarification).
Click to expand...

Then you either didn't follow John Spencer's advice, or your table is not as
you described it: his subqueries will in fact retrieve the high and the low
for the day.

Please post the exact SQL of the query that you are using, and perhaps a few
rows of example data.
 
Original code would hang but I finally got this to work:

SELECT [Date], [Time], [High]
FROM [TestTable]
WHERE [High] =
(SELECT Max([TestTable].High)
FROM [TestTable] AS T
WHERE T.[Date]=[TestTable].Date);

Some sample raw data (Date,Time,Open,High,Low,Close,Volume):

20090102;16:03:00;3694;3706;3694;3702;44
20090102;16:04:00;3700;3702;3697;3700;85
20090102;16:05:00;3699;3702;3698;3700;22
20090102;16:06:00;3699;3699;3697;3699;26
20090102;16:07:00;3700;3702;3700;3702;10
20090102;16:08:00;3704;3708;3704;3708;14
20090102;16:09:00;3707;3715;3707;3710;48
20090102;16:10:00;3709;3715;3705;3708;86
20090102;16:11:00;3706;3706;3696;3703;207
 
Original code would hang but I finally got this to work:

SELECT [Date], [Time], [High]
FROM [TestTable]
WHERE [High] =
(SELECT Max([TestTable].High)
FROM [TestTable] AS T
WHERE T.[Date]=[TestTable].Date);
Click to expand...

An alternative would use a Top Values subquery:

SELECT [Date], [High], (SELECT TOP 1 X.[Time] FROM tablename AS X WHERE
X.[Date] = tablename.[Date] ORDER BY X.[High], X.[Time] DESC) AS TimeOfHigh,
[Low], (SELECT TOP 1 X.[Time] FROM tablename AS X WHERE X.[Date] =
tablename.[Date] ORDER BY X.[Low] DESC, X.[Time] DESC);
 
Your SQL is retrieving the high for each record in TestTable.

HOW many records do you have? Do you have an index on the Date field? The
query will take a long time with many records because it is running the query
in the where clause ONCE for every record in the table.

If you have a lot of records you may have to use two queries to get the
results you want. If you didn't have a field name Date that requires square
brackets to be used, you could do this all with a subquery in the from clause.

First Query (Save this as qMaxHigh)
SELECT [TestTable].[Date]
, Max([TestTable].[High]) as TheHigh
FROM [TestTable]
GROUP BY [TestTable].[Date]

Now you can use that saved query and your original table.
SELECT [TestTable].[Date]
, [TestTable].[Time]
, [TestTable].[High]
FROM [TestTable] INNER JOIN qMaxHigh
ON [TestTable].[Date] = qMaxHigh.[Date]
AND [TestTable].[High] = qMaxHigh.TheHigh

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Original code would hang but I finally got this to work:

SELECT [Date], [Time], [High]
FROM [TestTable]
WHERE [High] =
(SELECT Max([TestTable].High)
FROM [TestTable] AS T
WHERE T.[Date]=[TestTable].Date);

Some sample raw data (Date,Time,Open,High,Low,Close,Volume):

20090102;16:03:00;3694;3706;3694;3702;44
20090102;16:04:00;3700;3702;3697;3700;85
20090102;16:05:00;3699;3702;3698;3700;22
20090102;16:06:00;3699;3699;3697;3699;26
20090102;16:07:00;3700;3702;3700;3702;10
20090102;16:08:00;3704;3708;3704;3708;14
20090102;16:09:00;3707;3715;3707;3710;48
20090102;16:10:00;3709;3715;3705;3708;86
20090102;16:11:00;3706;3706;3696;3703;207

John W. Vinson said:
Then you either didn't follow John Spencer's advice, or your table is not as
you described it: his subqueries will in fact retrieve the high and the low
for the day.

Please post the exact SQL of the query that you are using, and perhaps a few
rows of example data.
Click to expand...
Click to expand...
 
You guys are brilliant! Your code worked a treat on 700,000 lines of my
database in under 10 seconds. Many thanks, and also to John W V, for your
patience and efforts.

John Spencer said:
Your SQL is retrieving the high for each record in TestTable.

HOW many records do you have? Do you have an index on the Date field? The
query will take a long time with many records because it is running the query
in the where clause ONCE for every record in the table.

If you have a lot of records you may have to use two queries to get the
results you want. If you didn't have a field name Date that requires square
brackets to be used, you could do this all with a subquery in the from clause.

First Query (Save this as qMaxHigh)
SELECT [TestTable].[Date]
, Max([TestTable].[High]) as TheHigh
FROM [TestTable]
GROUP BY [TestTable].[Date]

Now you can use that saved query and your original table.
SELECT [TestTable].[Date]
, [TestTable].[Time]
, [TestTable].[High]
FROM [TestTable] INNER JOIN qMaxHigh
ON [TestTable].[Date] = qMaxHigh.[Date]
AND [TestTable].[High] = qMaxHigh.TheHigh

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Original code would hang but I finally got this to work:

SELECT [Date], [Time], [High]
FROM [TestTable]
WHERE [High] =
(SELECT Max([TestTable].High)
FROM [TestTable] AS T
WHERE T.[Date]=[TestTable].Date);

Some sample raw data (Date,Time,Open,High,Low,Close,Volume):

20090102;16:03:00;3694;3706;3694;3702;44
20090102;16:04:00;3700;3702;3697;3700;85
20090102;16:05:00;3699;3702;3698;3700;22
20090102;16:06:00;3699;3699;3697;3699;26
20090102;16:07:00;3700;3702;3700;3702;10
20090102;16:08:00;3704;3708;3704;3708;14
20090102;16:09:00;3707;3715;3707;3710;48
20090102;16:10:00;3709;3715;3705;3708;86
20090102;16:11:00;3706;3706;3696;3703;207

John W. Vinson said:
Many thanks, John. I managed to get this working, and I feel closer to the
answer, but alas it returns the Max(High) for every minute in the database,
not the highest High for the day. I'm after the highest High in every 24-hour
period and the Time (minute) that occurred. Apologies for not explaining it
properly (see my reply to Karl Dewey for further clarification).
Then you either didn't follow John Spencer's advice, or your table is not as
you described it: his subqueries will in fact retrieve the high and the low
for the day.

Please post the exact SQL of the query that you are using, and perhaps a few
rows of example data.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top