Thread: Rookie Questions: Storing the results of calculations vs. not?
PG: 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? 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 have rudimentary python skills, and I'm getting the hang of psycopg2. After reading the postgresql manual, what should I read next? Thanks in advance, CJL
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!
On Tue, 29 May 2007, Ron Johnson wrote: >> 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. Years ago I was taught that calculation results should not be stored, but re-calculated as needed. If you change the underlying formulae you immediately see the new results without having to update every table. You can use views, and you can return the results by specifying the SELECT statement with a column name for the calculation. For example, SELECT original_cost - (selling_price + commission) AS net_profit FROM ... >> I have rudimentary python skills, and I'm getting the hang of psycopg2. >> After reading the postgresql manual, what should I read next? Three suggestions: "wxPython In Action" by Robin Dunn to write the GUI for your application. "Introduction to SQL, 4th Edition" by Rich F. van der Laans to learn the power of the various SQL data manipulation clauses. "SQL for Smarties, 3rd Edition" by Joe Celko to get better insight into effectively written advanced queries. Don't forget the PostgreSQL docs for specifics of the language and implementation. Rich -- Richard B. Shepard, Ph.D. | The Environmental Permitting Applied Ecosystem Services, Inc. | Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
Hi All, I have set up a jdbc connection using jdeveloper(on my windows machine). I am connecting to a postgres database on a linux machine. I used the Database connections are shown in the Navigator, in the Connections | Database folder. I tested the connection and it said "Success!" in the database connection navigation window. However when I expand the connection to show the database's schemas, I cannot see any tables. I have created tables and relationships in the PgSQL database on the linux machine, and I know they do exist. I run a query using psql client on the linux box, I can view all the contents of my tables. Do I have to be setting up any special settings in my jdeveloper to be able to inspect the tables? -B