PostgreSQL 8.4.0 Setup Complete

The Installation process was very straightforward and provided no surprises. Simply go to www.postgreSQL.org and download release 8.4. Run the installer and it should be very easy. A windows service called "PostgreSQL Server 8.4" will be created and can be verified by checking Control Panel, Administrative Tools, Services. If it does not start automatically, simply right click on it and select Start.



Once the database is running, connect to it and create a 'tradingbot' database.

C:\temp>psql -U postgres
Password for user postgres:
psql (8.4.0)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# create database tradebot;
CREATE DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |         Collation          |           Ctype
           |   Access privileges
-----------+----------+----------+----------------------------+-----------------
-----------+-----------------------
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres

           : postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres

           : postgres=CTc/postgres
 tradebot  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
(4 rows)

postgres=# create user master with password 'blaster';
CREATE ROLE
postgres=# grant all on database tradebot to master;
GRANT
postgres=# \q

Line 1: Connect to default database
Line 9: Create database "tradebot"
Line 27: Create new user called "master"
Line 29: Grant privileges to "master"

Next step is to log into the new database and create a test table.

C:\temp>psql -d tradebot -U master
Password for user master:
psql (8.4.0)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

tradebot=>\d
No relations found.
tradebot=> create table tester (id varchar(10) not null);
CREATE TABLE
tradebot=> \d
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+--------
 public | tester | table | master
(1 row)


tradebot=> \d tester
           Table "public.tester"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | character varying(10) | not null


tradebot=>

Line 1: Connect to tradebot database
Line 9: Get listing of all tables in the database. None returned because database is empty.
Line 11: Create "tester" table
Line 13: Get listing of all tables in the database. "Tester" table appears.
Line 21: Get "tester" table information

Specifications: Watcher

The Watcher Application specifications document is now complete. I have published it using Google docs rather than posting in blog format because it will be easier to make changes over time. As with all software specifications, this is a living document and will be updated often as needed. I will keep a revision history at the bottom of the document to track any major changes.

Proof Of Concept Recap and Game Plan

With Proof Of Concept postings completed, I now have a better understanding of what it will take to develop my trading robot. I have demonstrated that it’s possible to perform the following:


- Use JRuby to interface with TWS API

- Get market quote

- Get historical quote

- Check account information (balance and open position info)

- Check order status

- Submit stock order (market and limit orders)

- Submit options order

- Submit complex options order




Along the way, I have also identified some limitations and glitches within TWS API and JRuby. Most of these can be overcome, but they make development harder. Here are just a few:


- JRuby error messages are not that readable and it can be difficult to determine where the error is coming from. So during development, it’s important to test early and often before too much coding is done.

- TWS API or JRuby upgrades may cause the application to break. Any decision to perform an upgrade should not be taken lightly and complete regression testing will be needed.

- If TWS pops up a dialog window, any API calls will be interrupted until the window is closed. The finished product will need a way to detect or prevent this from happening.

- openOrder() callback method sometimes get triggered more than once for unknown reasons.

- Retrieved data (such as market price) from TWS isn’t perfect. So it’s a good idea to build in some sort of redundant requests to make sure no trading decision is made because of bad pricing data.

- There is a need to be able to recover in the case where order is submitted and TWS crashes. Part of the recovery process would be to detect what orders have been submitted, and executed or not.


It is my intent to develop the program in three phases, with the actual trading robot phase being the last, and most difficult to create. The first phase of development will be a watch list of my investment portfolio that will issue buy/sell signals according to my plan. There is no trading automation in this phase, as these investments not in my Interactive Brokers account. The second phase will be the backbone of the Trading Robot. It will keep track of which positions I have in my Interactive Brokers account and provide a user interface to manage data. The following is a more detailed description of each phase:

Phase 1) Watcher - Ruby on Rails application which allows me to watch my investment portfolio (composed of various ETFs and mutual funds). It will get security prices at regular time intervals or on demand and will issue buy/sell signals according to my rules (mostly based on moving averages). Data will be stored in a database and pricing information will be gathered from various sources on the web. It will provide a web interface from which to edit watch list data.




Phase 2) Account Monitor - Extension of the above Rails application. It will store open positions and trading rules in the database. It will provide a web interface to control application parameters and display activities performed by Trading Robot.




Phase 3) Trading Robot - Separate JRuby application with connection to TWS and database. It will populate the database with data from TWS, apply trading rules, and perform trades when needed. It will run in a batch mode and not have a user interface. All of the Trading Robot's activities will be monitored through the Account Monitor.