A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #11  
Old April 10th, 2010, 11:23 AM posted to microsoft.public.access.gettingstarted
Seano
external usenet poster
 
Posts: 10
Default How to show time for day's stock market high/low

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" wrote:

On Fri, 9 Apr 2010 22:17:01 -0700, Seano
wrote:

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.
--

John W. Vinson [MVP]
.

  #12  
Old April 10th, 2010, 09:12 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How to show time for day's stock market high/low

On Sat, 10 Apr 2010 03:23:01 -0700, Seano
wrote:

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);


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);

--

John W. Vinson [MVP]
  #13  
Old April 10th, 2010, 09:16 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default How to show time for day's stock market high/low

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

Seano wrote:
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" wrote:

On Fri, 9 Apr 2010 22:17:01 -0700, Seano
wrote:

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.
--

John W. Vinson [MVP]
.

  #14  
Old April 12th, 2010, 12:17 PM posted to microsoft.public.access.gettingstarted
Seano
external usenet poster
 
Posts: 10
Default How to show time for day's stock market high/low

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" wrote:

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

Seano wrote:
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" wrote:

On Fri, 9 Apr 2010 22:17:01 -0700, Seano
wrote:

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.
--

John W. Vinson [MVP]
.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.