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
|
|||
|
|||
How to show time for day's stock market high/low
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 |
#2
|
|||
|
|||
How to show time for day's stock market high/low
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 |
#3
|
|||
|
|||
How to show time for day's stock market high/low
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. "Richard" wrote: 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 |
#4
|
|||
|
|||
How to show time for day's stock market high/low
On Thu, 8 Apr 2010 18:20:01 -0700, Seano
wrote: 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? -- John W. Vinson [MVP] |
#5
|
|||
|
|||
How to show time for day's stock market high/low
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" wrote: On Thu, 8 Apr 2010 18:20:01 -0700, Seano wrote: 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? -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
How to show time for day's stock market high/low
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" wrote: 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" wrote: On Thu, 8 Apr 2010 18:20:01 -0700, Seano wrote: 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? -- John W. Vinson [MVP] . |
#7
|
|||
|
|||
How to show time for day's stock market high/low
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 Seano wrote: 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" wrote: On Thu, 8 Apr 2010 18:20:01 -0700, Seano wrote: 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? -- John W. Vinson [MVP] . |
#8
|
|||
|
|||
How to show time for day's stock market high/low
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" wrote: 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" wrote: 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" wrote: On Thu, 8 Apr 2010 18:20:01 -0700, Seano wrote: 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? -- John W. Vinson [MVP] . |
#9
|
|||
|
|||
How to show time for day's stock market high/low
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" wrote: 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 Seano wrote: 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" wrote: On Thu, 8 Apr 2010 18:20:01 -0700, Seano wrote: 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? -- John W. Vinson [MVP] . . |
#10
|
|||
|
|||
How to show time for day's stock market high/low
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 | |
|
|