Re: Rookie Questions: Storing the results of calculations vs. not?

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!