jfreechart convert time series to ohlc?

Question

I am trying to create real-time candlestick chart using JFreeChart.

How do i convert a time series into OHLC to use in Jfreechart?

I'm taking a price and time snapshot of data and storing into a mysql database. I am trying to java client and jfreechart to generate candlestick charts of the time series, but I also want to be able to select the bar size (5 min, 15 min, etc) from the java client.. (i am pulling data into java client using java hibernate) so how do I take a continious series and convert to X min ohlc?

Answer 1

Preferably you won't want to pull all data from the database to the client. You would want to try and perform calculations on the database to prevent unnecessary data transfer. Or I would recommend maintaining a separate table to cache all the results.

To go from continuous series to OHLC ->

1. Open - the earliest row for that time grouping
2. Close - the latest row for that time grouping
3. Low/Close are just the max min.

For open and Close, you'll need something like this: SQL: How To Select Earliest Row In standard SQL you'll need a partition by and it's kind of clunky.

Low/High - is much easier

select max(price) AS HIGH from table GROUP BY Date

Bar size - is just a matter of changing the group by from Date, to HOUR(datetime) etc..

If you want to graph directly from the database we provide sqlchart, which allows graphing directly from command line sql calls. You can see a candlestick at the bottom of that page.