troubleshooting Question

Generate OHLC Bars from Tick Data

Avatar of Paccc
Paccc asked on
MySQL Server
8 Comments1 Solution4850 ViewsLast Modified:
I have a database full of tick data from stocks.  Tick data is basically a timestamped record of all transactions that take place on a certain stock.

Here's a sample from one of the tables:

+-------+------------+---------+------+
| ID    | Timestamp  | Price   | Size |
+-------+------------+---------+------+
| 40001 | 1157722663 |    1308 |  100 |
| 40002 | 1157722663 |    1308 |    2 |
| 40003 | 1157722665 |    1308 |   11 |
| 40004 | 1157722666 | 1307.75 |    1 |
| 40005 | 1157722666 |    1308 |    7 |
| 40006 | 1157722667 | 1308.25 |    1 |
| 40007 | 1157722667 | 1308.25 |    6 |
| 40008 | 1157722668 |    1308 |   10 |
| 40009 | 1157722668 |    1308 |    7 |
| 40010 | 1157722668 |    1308 |    7 |
+-------+------------+---------+------+

What I am trying to do is generate a condensed form of this data known as OHLC bars.  This should be familiar for anyone who has looked at stock charts, but I'll explain it for those who haven't.  If I were generating 1 minute bars, I would take the first price from that minute and call it Open, the last price would be the Close, and the High and Low are the max and min for that range.  There is also a fifth parameter, Volume, that is simply the sum of all of the sizes in that range.  I have written a query that partially works but could not optimize it:

SELECT
 FLOOR(a.Timestamp / 60) * 60 AS Timestamp,
 a.Price AS Open,
 MAX(a.Price) AS High,
 MIN(a.Price) AS Low,
 (SELECT b.Price FROM AAPL_ticks b WHERE FLOOR(b.Timestamp / 60) * 60 = FLOOR(a.Timestamp / 60) * 60 LIMIT 1) AS Close,
 SUM(a.Size) AS Volume FROM AAPL_ticks a GROUP BY FLOOR(a.Timestamp / 60)

This query groups it in 60 second increments.  When I run this query, I get the correct output, but it takes forever (somewhere in the range of 1000 seconds for 390 bars).  I have narrowed down the problem to the subquery that calculates the Close. When I take this statement out the query processes thousands of bars in a few seconds.  I've tried writing a stored procedure for this but failed at that as well.  I've been working on this for a while and have finally given up, so any assistance would be very helpful.  Thanks in advance!

- Paccc
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros