On 05/24/07 15:20, cjl wrote:
PG:
Sorry it's taken so long for anyone to answer you, but it appears that some emails were hung up for a while.
I am playing around with some historical stock option data, and have decided to use a database to make my life easier. The data is "end-of- day" for all equitable options, so something like 17 columns and approximately 200,000 rows a day. I have several months of data in "csv" format, one file for each trading day. After some simple experiments, I found it was easier to import this data directly into postgresql than into mysql, because of the expiration date format being un-friendly to mysql. I'm using the COPY command to load the data. I realize I have a lot of reading to do, but I would like to ask a few questions to help guide my reading. 1) The data contains the price of the underlying stock, the strike price of the option, and the option premium. From this I can calculate the "cost basis" and the "maximum potential profit", which are elements I would like to be able to SELECT and ORDER. Should I store the results of these calculation with the data, or is this "business logic" which doesn't belong in the database. Is this what views are for?
I'd say "business logic", and yes, views are good for that.
2) For each underlying stock there are lots of options, each having unique strike prices and expirations. For example, AAPL (apple computer) have stock options (calls and puts) that expire in June, at various strike prices. Lets say that apple stock is trading at $112. I would like to be able to select the options with strikes just above and below this price, for example $110 and $115. The data contains options with strikes from $60 through $125, every $5. Is this something I need to do programatically, or can I create a complex SQL query to extract this information?
I'd have a table with one row per option. Then make this kind of query: SELECT * FROM T_OPTION WHERE TICKER = 'AAPL' AND EXPIRE_DT BETWEEN '2007-06-01' AND 2007-06-30' AND PRICE = 112.0 ORDER BY PRICE DESC LIMIT 1 UNION SELECT * FROM T_OPTION WHERE TICKER = 'AAPL' AND EXPIRE_DT BETWEEN '2007-06-01' AND 2007-06-30' AND PRICE = 112.0 ORDER BY PRICE ASC LIMIT 1 ;
I have rudimentary python skills, and I'm getting the hang of psycopg2. After reading the postgresql manual, what should I read next?
-- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
