Registered: Aug 2011
08-20-12 10:03 PM
Quote from RPEX:
Hi this is a really interesting thread, i was considering doing something similar with futures options closing prices. But i don't have that much experience with mysql, and was looking for some pointers on table structure for options data. I want to analyse the data both in time series [say a calculation for ATM IV] and cross sectional [vol surface].
Would you go as far as having a separate tables for each date?
How do you treat the underlying. Obviously no option price really means anything without an underlying price. But i wouldn't want to keep a field just for that - surely that's what the relational model seeks to avoid.
Absolutely any ideas / ways of thinking would be great.
This is the way i think about it alot of times... Do i blog about my trades.. And potientially give away my edge.. Or become some chump fuck that only makes money telling other peole how to trade... If these guys really had good great trading methodologies.. They sure as fuck woulldnt be selling it.. Ok ok ok back to the subject at hand
The fact that your asking about table structure in the manner that you are tells me you know nothing about how databases work... Which is no big deal the majority of things in life i feel are made out to be more complex then they are so people feel good about knowing something you don't... Now.. I would sugguest picking up a mysql book.. And just rippping through it cover to cover.. Its stupid simple.. First you learn update delete select etc.. Then you learn about inner joins, joins etc.. Then you learn about indexing quieries optimization triggers. . Etc.. But the whole idea here is to reduce redundent data.. You don't wanna duplicate entries down an entire table... For example puts & calls could be reduced to even a booleon 1 or 0 value if yo uwanted... As the more data you put in there the more exponeitailly inflated your db can get... Think about tick data on every strike on every futures contract every day.... Shit adds up.. Now.. That being said.. If your ripping down prices, time and sales, open interest, volume etc.. There isn't much gonna be duplicated... Thats why i thought basically you would have only one table.. Looking like an excel spreadsheet.. Makes for super easy queries to.. so. . Do you need a seperate table for every strike price? i'm not sure.. I'm working on my ecommerce store right now and i lost track of what i was thinking on this.. I'll obvsiouly have to sit down and start figuring this out.. Cause even if i don't publish it to a website.. I'm definilty gonna start ripping down options data. . Cause you just can't get it once it expires without paying for it.. Especially not in detail.. Obviously the more tables the more complex the quieries. . I bet you someone wrote some stuff about this somewhere.. Or we could post in some db forums and get some advice on it.. Cause i know that it always ends up being alot more data then you think... Then you have to figure if you rip down data from your desktop connection to your IB connection.. If you lose your internet. Or you ccomputer crashes your gonna have a huge gap... I'm sure we could hook into the api from a server that has crazy better uptime and rip it down to a db their.. But you literally would have to have root access to the server so you could install trader work station and hhook into it..