Managing Options and Volatility Historical Data

Strange · Post by **Strange** » Sun Nov 25, 2012 12:11 am

I was never involved with the data management side of the volatility trading business and now that I have to deal with it, it's proving to be a real challenge. I kind-off thought that I have figured out solutions to all of these, but having some doubts now.

For starters, how do people store options quotes? It's a fair number of quotes, even if you just look at all optionable stocks daily - over ten years you get billions of quotes and I hate to image what it would look like for any sort of intra-day quotes (e.g. 1 or 10 minute frequency "bars" which is where we are planning to go soon). So, this brings up a bunch of questions:

Is it reasonable to store it in a RDBMS like MySQL or Oracle? Assuming the data has to be split into multiple tables, is it a better idea to split the data by asset or is it better to split the data by time?

Or maybe the right thing to do is to use a file-system-based structure instead and dump each quote-time option chain into separate intelligently-named file (e.g. SPX-201211061125.csv)?

Once you convert all these into vol surfaces, it becomes a little easier to deal with, since you maybe have a few thousand surfaces per day. However, it's still unclear how to store and manipulate it - does it make sense to store a quote for each "slice" of vol surface in a database? Or is still better to go with some file-system and text-file based solution?

silverside · Post by **silverside** » Sun Nov 25, 2012 10:59 am

Hi strange,

I would think a RDBMS would be a better solution if stability and ease of initial setup are your criteria. You can optimize for speed by good database design, but if you're looking to get the last ounce of performance you could hand craft something maybe in C++.

To be honest I leave these details to the DBA, then again I'm not trying to capture the whole investable universe of volatility.

I would, however, start by defining some requirements (speed, scalability, uptime, analysis capability) and work backwards from there rather than immediately opting for a particular technology.

chiral3 · Post by **chiral3** » Sun Nov 25, 2012 2:27 pm

I got to the point where I simply got a dedicated IT team to manage it. Of course there are pluses and minuses associated with this but the benefits were 1) I didn't want to morph my team into an IT shop (which it invariably is), 2) I got the benefits of disaster recovery, redundancy, read/write optimization, etc., 3) unlimited capacity, and 4) the ability to house business logic that migrates mindless calculations away from the team and automates them within the IT infrastructure. I also get the benefit of IT for trading people but more often than not my guys write their code for them and tell them how to implement. Guess it makes sense if you think about the personality types and the economics. We maintain our own databases, but as they start to reach critical mass, I like to migrate them. To date this has included my tick dbase an my equity and rate option dbases.

Strange · Post by **Strange** » Sun Nov 25, 2012 6:24 pm

So did you outsource it somehow or are we talking about another team in a large shop? I am not in position to hire a lot of people yet and need the solution to this ASAP, since this is critical to the business.

dgn2 · Post by **dgn2** » Mon Nov 26, 2012 3:10 am

I am looking at going the file system route using pytables + pandas for this sort of thing for futures options at the moment. If it gets far enough along I will let you know how it goes, but it sounds like you need an immediate solution. I don't think you want to do this in MySQL (which is what I am doing right now)

Strange · Post by **Strange** » Mon Nov 26, 2012 4:01 am

It definitely feels to me like some sort of file-based solution is the way to go, especially given that there are only two or three types of queries/aggregations that we are going to use. Or, it should be some sort of hybrid solution.

TonyC · Post by **TonyC** » Mon Nov 26, 2012 8:21 pm

That J database that's mentioned in the APL thread is 80% of kdb/q, is free, and while the db is written in J you dont't need to know J to use it.

the APL thread also points to some hi performance time series db R addins

and oh yeah "dude, we should talk"

jslade · Post by **jslade** » Tue Nov 27, 2012 1:17 am

I was going to mention JDB/J again. It's pretty damn good. You probably do need some J to get the ETL loads done. When I get some spares, I plan on making it talk to R more effectively.

Custom mmaped flat files might not be a bad idea either.

Strange · Post by **Strange** » Tue Nov 27, 2012 5:38 am

Could you give me a pointer to the site, pls? So far searching for JDB/J turned "jason DB" which does not sound right

PS. found it

jslade · Post by **jslade** » Tue Nov 27, 2012 10:16 am

Before you go too far with it, the question you should probably answer for yourself is, what does it have to talk to? J can easily call R as a shared object, and I think it talks to Java. Going the other direction calling J from R is unfinished business. You could use a socket, and I'm working on code to call it as a shared object, but, well, it might be easier to use files if you're in a hurry.

http://www.jsoftware.com/jwiki/Interfaces

That said, it seems to work well enough I'm planning on investing some cycles into it for my own infrastructure. If it doesn't work out, someone told me CME stores its options ticks in Cassandra.