Solved

extract a one minute forex tick into a new table

Posted on 2011-09-18
4
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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