Tics to minuts

[Rolf Edberg]

What query can make these tics to minutes?



From this:

Date Time Low High

4/1/2004 8:51:40 1.2586 1.2588

4/1/2004 8:51:42 1.2585 1.2587

5/1/2004 8:00:03 1.2685 1.2687

5/1/2004 8:00:24 1.2686 1.2688

5/1/2004 8:01:07 1.2684 1.2686

5/1/2004 8:01:28 1.2685 1.2687



To this:



Date Time Low High
Volume

4/1/2004 8:51 1.2585 1.2588 2

5/1/2004 8:00 1.2685 1.2688 2

5/1/2004 8:01 1.2684 1.2687 2





Thanks

Rolf

 

[John Vinson]

What query can make these tics to minutes?



From this:

Date Time Low High

4/1/2004 8:51:40 1.2586 1.2588

4/1/2004 8:51:42 1.2585 1.2587

What's a "tic", and what's the logic? How do you get from a time
difference of two seconds to two minutes - or is that the difference
betwen Low and High?

John W. Vinson[MVP]

 

[Guest]

Rolf:

Is Volume the count of rows per minute? If so try this:

SELECT
[Date],
FORMAT([Time],"hh:nn") AS MinuteTime,
MIN(Low) As Lowest,
MAX(High) As Highest,
COUNT(*) AS Volume
FROM YourTable
GROUP BY [Date], FORMAT([Time],"hh:nn");

Ken Sheridan
Stafford, England

 

[Rolf Edberg]

Hi John,
I was wrong in my question. Sorry. I'll try to explain.

What I ment was to group trades. All trades that were made in the same
minute should have a lowest Low and a highest High and be counted as Volume.

Regards
Rolf

PS. A tic is the smallest change in the price. DS

 

[Rolf Edberg]

Thanks for the answer!!!

How do I make the question to work?

I got stuck on a ODBC-connection or am I totally off track?

I did this:
Query
New
Design
Close
Query(Meny)
SQL/Direct
Paste 'Your text'
Properties
ODBC; ...
And then I am lost..



Regards

Rolf

-----------------------------------------------

(I was wrong in my question. Sorry.

What I ment was to group trades. All trades that were made in the same
minute should have a lowest Low and a highest High and be counted as Volume.

PS. A tic is the smallest change in the price. The topic shold have been
'seconds to minutes'. DS)

Rolf:

Is Volume the count of rows per minute? If so try this:

SELECT
[Date],
FORMAT([Time],"hh:nn") AS MinuteTime,
MIN(Low) As Lowest,
MAX(High) As Highest,
COUNT(*) AS Volume
FROM YourTable
GROUP BY [Date], FORMAT([Time],"hh:nn");

Ken Sheridan
Stafford, England

What query can make these tics to minutes?



From this:

Date Time Low High

4/1/2004 8:51:40 1.2586 1.2588

4/1/2004 8:51:42 1.2585 1.2587

5/1/2004 8:00:03 1.2685 1.2687

5/1/2004 8:00:24 1.2686 1.2688

5/1/2004 8:01:07 1.2684 1.2686

5/1/2004 8:01:28 1.2685 1.2687



To this:



Date Time Low High
Volume

4/1/2004 8:51 1.2585 1.2588 2

5/1/2004 8:00 1.2685 1.2688 2

5/1/2004 8:01 1.2684 1.2687 2





Thanks

Rolf

 

[John Vinson]

Hi John,
I was wrong in my question. Sorry. I'll try to explain.

What I ment was to group trades. All trades that were made in the same
minute should have a lowest Low and a highest High and be counted as Volume.

Thanks, that makes it much clearer!

You can group records by a calculated field, calculating to the
nearest minute. Date/Time fields are stored as days and fractional
days; there are 1440 minutes in a day, so:

SELECT [Date], CDate(1440 * (CDbl([Time]) \ 1440)) AS TimeMin,
Min([Low]) AS Lowest, Max([High]) AS Highest, Max([High]) - Min([Low])
AS Volume
GROUP BY [Date], CDate(1440 * (CDbl([Time]) \ 1440));

John W. Vinson[MVP]

 

[John Vinson]

SELECT [Date], CDate(1440 * (CDbl([Time]) \ 1440)) AS TimeMin,
Min([Low]) AS Lowest, Max([High]) AS Highest, Max([High]) - Min([Low])
AS Volume
GROUP BY [Date], CDate(1440 * (CDbl([Time]) \ 1440));

Oops! Sorry! Reversed the order of operations:

SELECT [Date], CDate(Fix(1440*CDbl([Time]))/1440) AS TimeMin,
Min([Low]) AS Lowest, Max([High]) AS Highest, Max([High]) - Min([Low])
AS Volume
GROUP BY [Date], CDate(Fix(1440*CDbl([Time]))/1440);

John W. Vinson[MVP]

 

[Rolf Edberg]

Thanks!!!!!!!!!

SELECT [Date], CDate(1440 * (CDbl([Time]) \ 1440)) AS TimeMin,
Min([Low]) AS Lowest, Max([High]) AS Highest, Max([High]) - Min([Low])
AS Volume
GROUP BY [Date], CDate(1440 * (CDbl([Time]) \ 1440));

Oops! Sorry! Reversed the order of operations:

SELECT [Date], CDate(Fix(1440*CDbl([Time]))/1440) AS TimeMin,
Min([Low]) AS Lowest, Max([High]) AS Highest, Max([High]) - Min([Low])
AS Volume
GROUP BY [Date], CDate(Fix(1440*CDbl([Time]))/1440);

John W. Vinson[MVP]

 

[Rolf Edberg]

Thanks!!
Rolf

Rolf:

If you are using a local or linked native Access table then you don't need
to create a pass-through query, so the steps are:

Query
New
Design
Close
View(Menu)
SQL View
Paste in the text
View(Menu)
Datsheet View
The query should now open. If its OK then..
File(menu)
Save
Save with a suitable name.

If you really do want to create a pass-through query you'll need to enter
the ODBC connect string in the query's properties sheet. For more
information take a look at the Help topic on Queries | SQL Queries |
Create
an SQL-specific query.

Ken Sheridan
Stafford, England

Thanks for the answer!!!

How do I make the question to work?

I got stuck on a ODBC-connection or am I totally off track?

I did this:
Query
New
Design
Close
Query(Meny)
SQL/Direct
Paste 'Your text'
Properties
ODBC; ...
And then I am lost..



Regards

Rolf

-----------------------------------------------

(I was wrong in my question. Sorry.

What I ment was to group trades. All trades that were made in the same
minute should have a lowest Low and a highest High and be counted as
Volume.

PS. A tic is the smallest change in the price. The topic shold have been
'seconds to minutes'. DS)

"Ken Sheridan" <[email protected]> skrev i
meddelandet

Rolf:

Is Volume the count of rows per minute? If so try this:

SELECT
[Date],
FORMAT([Time],"hh:nn") AS MinuteTime,
MIN(Low) As Lowest,
MAX(High) As Highest,
COUNT(*) AS Volume
FROM YourTable
GROUP BY [Date], FORMAT([Time],"hh:nn");

Ken Sheridan
Stafford, England

:

What query can make these tics to minutes?



From this:

Date Time Low High

4/1/2004 8:51:40 1.2586 1.2588

4/1/2004 8:51:42 1.2585 1.2587

5/1/2004 8:00:03 1.2685 1.2687

5/1/2004 8:00:24 1.2686 1.2688

5/1/2004 8:01:07 1.2684 1.2686

5/1/2004 8:01:28 1.2685 1.2687



To this:



Date Time Low High
Volume

4/1/2004 8:51 1.2585 1.2588 2

5/1/2004 8:00 1.2685 1.2688 2

5/1/2004 8:01 1.2684 1.2687 2





Thanks

Rolf