Access query or more?

[Bob M]

Hi

I have the following 1 hour bar forex data in an access table:-
Time,Open,High,Low,Close,Volume
12.08.2013 00:00:00.000,1.33384,1.33402,1.33250,1.33259,3609. 63
12.08.2013 01:00:00.000,1.33258,1.33320,1.33236,1.33305,3011. 74
12.08.2013 02:00:00.000,1.33305,1.33310,1.33175,1.33239,4379. 66
12.08.2013 03:00:00.000,1.33239,1.33289,1.33214,1.33227,3116. 69
12.08.2013 04:00:00.000,1.33226,1.33279,1.33224,1.33262,1974. 68
12.08.2013 05:00:00.000,1.33261,1.33273,1.33216,1.33256,2764. 00
12.08.2013 06:00:00.000,1.33255,1.33332,1.33160,1.33178,6276. 07
12.08.2013 07:00:00.000,1.33179,1.33259,1.33023,1.33073,12576 .05
12.08.2013 08:00:00.000,1.33073,1.33090,1.32960,1.33079,8872. 98
12.08.2013 09:00:00.000,1.33080,1.33081,1.32870,1.32908,9744. 47
12.08.2013 10:00:00.000,1.32906,1.33024,1.32879,1.32968,9401. 39
12.08.2013 11:00:00.000,1.32969,1.32995,1.32874,1.32876,5922. 18
12.08.2013 12:00:00.000,1.32876,1.32906,1.32770,1.32889,12129 .39

The fields are 1 text, and 5 numeric

I wish to create 6 hour bar data:- such that the first two records would be..............

12.08.2013 05:00:00.000,1.33384,1.33273,1.33402,1.33175,1.332 56,18856.4


12.08.2013 11:00:00.000,1.33255,1.32995,1.33332,1.32870,1.328 76,52793.14

i.e. Time is syncr to 5, 11, 17, 23 each day
Open is Opening Price at the start of the 6 hour period
High is the maximum (of the 6 High values)
Low is the minimum (of the 6 Low Values)
Close is the Closing Price at the end of the 6 hour period
Volume is the sum (of the 6 Volume values)

Can this be achieved using a query or does one need to write a VBA script?

Thank you

Bob M

[June7]

What is 'bar forex'? What is '6 hour bar' data?

Determining the 'low' or 'high' of data from values of same record requires an expression and an expression for more than 2 values is too complicated, so yes need a VBA function.

Everything else can be handled in the query.

[Bob M]

foreign exchange = forex - tracking currency pairs - e.g. EUR/USD
1 bar data - data from 1 hour's duration
6 hour bar data - data from 6 hour's duration

Bob M

p.s. I wish to determine highs and lows and volumes from six consecutive records
Close Price from the current record and Open Price from the 6th record before the current record

[June7]

You show 6 data besides the date/time but only describe 5.

Maybe not as hard as I thought. I was thinking you needed the min/max of the 5 values in each record. However, returning the Max of High and Min of Low is easy with an aggregate query.

The date/time value is in a text field? Is that date in mm/dd/yyyy or dd/mm/yyyy? The decimal seconds also complicate converting the text to a true date value. Might not need to be a true date value for this purpose but I will demo anyway. Need the date part from the string to group the records by days. Need the hour part to calculate group code.

Consider:

SELECT Last([Time]) As DT, First(Open) As O, Last(Close) As C, Max(High) As H, Min(Low) As L, Sum(Volume) As V FROM table GROUP BY CDate(Replace(Left([Time],10),".","/")), Switch(Val(Mid([Time],12,2))<=5,1, Val(Mid([Time],12,2))<=11,2, Val(Mid([Time],12,2))<=17,3, Val(Mid([Time],12,2))<=23,4);

[Bob M]

thank you for your reply..............
I will take some time to try and understand it
Yes there are 6 items of data
the first record is:
12.08.2013 00:00:00.000 - dd.mm.yyyy hh:mm:ss
1.33384 - Opening Price
1.33402 - High
1.33250 - Low
1.33259 - Closing Price
3609.63 - Volume

Many thanks

Bob M

[June7]

The example of 2 records shows 7 data items.

[Bob M]

My mistake

I wish to create 6 hour bar data:- such that the first two records would be..............

12.08.2013 05:00:00.000 [Date & Time],1.33384 [Open],1.33402 [High],1.33175 [Low],1.33256 [Close],18856.4 [Volume]
12.08.2013 11:00:00.000 [Date & Time],1.33255 [Open],1.33332 [High],1.32870 [Low],1.32876 [Close],52793.14 [Volume]

Bob M

[June7]

Did my suggested query work? I just did a minor edit on the query so review that post again.

[Bob M]

Thank you..................
I have been using Excel up till now, Access is rather new to me
Unfortunately the Date & Time are received as I have described but in Excel I have immediately split the field into two fields Date and Time where the Date field is a genuine date field and the Time field is a genuine time field.
I feel very uncomfortable with the original combined 'text' field

Over the weekend, I shall move forward and come back to you
Thank you again for your assistance

Can I presume that "The Great Land" is the USA ?

Bob M

[June7]

More specifically, Alaska.

[Bob M]

I think I understand what the formula is doing..............

BUT, I think CDate(Replace(Left([Time],12),".","/")) should read CDate(Replace(Left([Time],10),".","/"))

Bob M
Dunedin
New Zealand

[June7]

Yes, sorry, I had 10 in there at one point and accidentally edited it when I was fixing the HrGrp expression. Good catch!

I have now edited that post.

If you don't care about have as a true date, can just: Left([Time],10) As DT