Solved

extract a one minute forex tick into a new table

Posted on 2011-09-18
4
333 Views
Last Modified: 2012-06-27
Hi,

I have a table with a raw set of forex data. I am trying to change the granularity to 1 minute ticks, instead of variable ticks, sometimes multiple ticks per second. I want this data in a new table. I want the query to be robust enough that it will work (or fail to work gracefully) so it can be cronjobbed or similiar, and reliably produce a clean/no result even if the source table returns null values on a particular select statement (when markets are closed, there is no source data, but the cronjob will probably still run)

I have made some headway into reducing the granularity but am finding that as this is my very first go at sql, reading the manual and searching for examples takes too long. My complete statement is below, after my question. I am using Navicat.

This is the first of a bunch of probably simple questions, with 500 points each, so it would be great - if you like this stuff keep an eye for more posts from me over the next few hours.

Question: This sql query works (unreliably), but takes time to run, probably because first extracts a data set to a temp table, then sorts the entire set using orderby to just extract a few items of data for each minute period. Is there a better overall approach that is any faster getting the 'open' 'high' 'low' 'close' (or o,h,l,c) for each minute, than the multiple select statements I have used?

Example results data showing table structure:-

tickid      ticker      tickdt      bidprice      askprice      lastprice      o      h      l      c      status      volume
8764058      XAUUSD      9/19/2011 10:08:00      1824.9      1826.06      1824.95      1825.27      1825.27      1824.95      1824.95      2      100000
8764059      XAUUSD      9/19/2011 10:08:00      1824.9      1826.06      1824.9      1825.27      1825.27      1824.9      1824.9      2      100000
8764060      XAUUSD      9/19/2011 10:08:01      1824.9      1825.98      1824.9      1825.27      1825.27      1824.9      1824.9      2      100000
8764061      XAUUSD      9/19/2011 10:08:02      1825      1825.85      1824.9      1825.27      1825.27      1824.9      1824.9      2      100000
8764062      XAUUSD      9/19/2011 10:08:02      1825      1825.85      1825      1825.27      1825.27      1824.9      1825      2      100000
8764063      XAUUSD      9/19/2011 10:08:03      1825.03      1825.85      1825.03      1825.27      1825.27      1824.9      1825.03      2      100000
8764064      XAUUSD      9/19/2011 10:08:03      1824.98      1825.85      1824.98      1825.27      1825.27      1824.9      1824.98      2      100000
8764065      XAUUSD      9/19/2011 10:08:06      1824.98      1825.93      1824.98      1825.27      1825.27      1824.9      1824.98      2      100000

SQL Query:-

CREATE TEMPORARY TABLE tempticks (

    tickid INT(11) NOT NULL
    , ticker VARCHAR (10)
    , tickdt DATETIME
    , bidprice FLOAT
    , askprice FLOAT
    , lastprice FLOAT
    , o FLOAT
    , h FLOAT
    , l FLOAT
    , c FLOAT
    , status TINYINT (4)
    , volume VARCHAR (20)
) ENGINE=MEMORY;

/* xxx Select statements to create the data */

INSERT INTO tempticks
select * from xauusd
where tickdt <date_format(date_add(now()-120,INTERVAL 1 minute),'%y%m%d%h%i00')
and
tickdt >=date_format(now()-120,'%y%m%d%h%i00');

/* Just output the full 1minute table for */
SELECT * FROM tempticks;

/* XXX output just the data we want */

SELECT o As "open"
from tempticks
ORDER BY tickid ASC
limit 1;

SELECT c AS "close"
from tempticks
ORDER BY tickid DESC
limit 1;

SELECT h AS "maximum"
FROM tempticks
ORDER BY h DESC
LIMIT 1;

select l as "minimum"
from tempticks
ORDER BY l ASC
LIMIT 1;

select volume
from tempticks
ORDER BY tickid DESC
LIMIT 1;

/* Explicitly destroy the table */

DROP TABLE tempticks;
0
Comment
Question by:XenekStoehr
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:mankowitz
Comment Utility
you want to use an aggregate function but, I am not 100% sure what you are looking for. Does this do what you want?

SELECT tickid, ticker, tickdt, bidprice,      askprice,      lastprice,      o, h, l, c, status, volume
FROM table
WHERE <whatever time constraints you want>
GROUP BY ticker,tickdt


if this is not what you are looking for, can you give us the description of the original table?
0
 
LVL 1

Author Comment

by:XenekStoehr
Comment Utility


tickid      ticker      tickdt      bidprice      askprice      lastprice      o      h      l      c      status      volume
8764058      XAUUSD      9/19/2011 10:08:00      1824.9      1826.06      1824.95      1825.27      1825.27      1824.95      1824.95      2      100000

We are trying to reduce the volume of data from as many as a few ticks a second (variable), to one tick a minute (consistent), with an accurate open High Low Close calcuated from parts of the orginal data within the first minute period.

We want to feed the data into a new table.

And - we want to do it all in SQL :) :) :)

I think I need to become familar with using subquery statements, and perhaps do away with the temporary table?  
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
Comment Utility
you could use something like this:

SELECT ticker, 
       Date_format(tickdt, '%m/%d/%y %h:%i:00') tick_minute, 
       bidprice, 
       askprice, 
       lastprice, 
       (SELECT o 
        FROM   ticks 
        WHERE  Date_format(tickdt, '%m/%d/%y %h:%i:00') = 
               Date_format(t1.tickdt, '%m/%d/%y %h:%i:00') 
        ORDER  BY tickid ASC 
        LIMIT  1)                               o, 
       Max(h)                                   h, 
       Min(l)                                   l, 
       (SELECT c 
        FROM   ticks 
        WHERE  Date_format(tickdt, '%m/%d/%y %h:%i:00') = 
               Date_format(t1.tickdt, '%m/%d/%y %h:%i:00') 
        ORDER  BY tickid DESC 
        LIMIT  1)                               o, 
       status, 
       SUM(volume) 
FROM   ticks t1 
GROUP  BY tick_minute 

Open in new window

0
 
LVL 1

Author Closing Comment

by:XenekStoehr
Comment Utility
This gives me substantial amounts of assistance, and will almost certainly help resolve the issue. I will reopen another question regarding specific parts of your script if I have problems or fail to understand how it works.

Thanks Mankowitz!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now