Solved

extract a one minute forex tick into a new table

Posted on 2011-09-18
4
339 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
ID: 36916788
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
ID: 37097641


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
ID: 37118885
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
ID: 37142396
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 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