Development 0.1

"Be careful about using the following code -- I've only proven that it works, I haven't tested it." -- Donald Knuth

I have finally started my dynamic allocation of equity project.  This is something I've stewed about for several weeks...okay...maybe months.  But, after meeting with Jon for lunch this weekend...I finally got the motivation back to begin work on the project.  Thanks Jon!

And seeing as how I hardly ever write anything of significance on this blog...I figure I'd start documenting some of the steps I'm taking to get this project on the road.

First thing was to find a better coding environment than what I was using.  I have been using the PythonWin IDE for my trials and tribulations.  I needed more oomph.  Hopped over to Vim and have hunted and pecked my way around a bit.  No flow joe yet.

Before moving on...does anybody know of a windows or even linux distro of the EVE$EDITOR?  Somebody?  Anybody?  Hello?

Just a week ago, I found out about the new Pydev extension to Eclipse.  Pretty nice.  It's still not perfect...but much closer to what I'm looking for.  So, now that I've found an IDE that allows me to play in the sandbox a bit...on to the database choice.

I downloaded pytables due to their "designed to efficiently and easily cope with extremely large amounts of data" claim to fame.  And then did nothing with it.  It's not the relational type of storage I'm used to...so maybe that's why.  Thought maybe a viewer would help, so downloaded the vitables viewer.  It was nice...but still did nothing with it.

Okay, maybe I'm making this too hard.  One of the python programmers I know mentioned Sqlite.  Downloaded it.  Found the python extension for it here.  Explored documentation for working with it here and here.  Now, I'm getting somewhere.  Wrote a few python modules to test create, insert, drop, and fetch.  Here they are:

Create Table in Python/Sqlite:

******Begin of Code***********************
from pysqlite2 import dbapi2 as sqlite

conn = sqlite.connect("TaylorTree")
cursor = conn.cursor()

SQL = """
    create table MarketDaily
    (
      Symbol    text,
      Bar       SQL_DATE,
      Open      float,
      High      float,
      Low       float,
      Close     float,
      Volume    float,
      AdjClose  float,
      primary key (Symbol, Bar)
    );
      """
cursor.execute(SQL)
******End of Code***********************

Insert into Table:

******Begin of Code***********************
from pysqlite2 import dbapi2 as sqlite

conn = sqlite.connect("TaylorTree")
cursor = conn.cursor()

SQL = """
    insert into MarketDaily
    (Symbol, Bar, Open, High, Low, Close, Volume, AdjClose)
    values
    (
        "YHOO",
        20060801,
        20.00,
        25.00,
        19.00,
        22.00,
        50000,
        22.00
    );
      """
cursor.execute(SQL)

conn.commit()
******End of Code***********************

Fetch from Table:

******Begin of Code***********************
from pysqlite2 import dbapi2 as sqlite

conn = sqlite.connect("TaylorTree")
cursor = conn.cursor()

SQL = "select * from MarketDaily"
cursor.execute(SQL)
# Retrieve all rows as a sequence and print that sequence:
print cursor.fetchall()

cursor.close()
******End of Code***********************

Drop Table:

******Begin of Code***********************
from pysqlite2 import dbapi2 as sqlite

conn = sqlite.connect("TaylorTree")
cursor = conn.cursor()

SQL = "drop table MarketDaily"

cursor.execute(SQL)
******End of Code***********************

Not too bad.  Not too hard.  But, then I figured I'd make a module that would handle all this stuff for me.  Some hard work began...all because I had no idea how to use symbolics in Python/SQL.  Finally discovered the needle in a haystack...'%s'.  Aha!

******Begin of Code***********************
from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect("TaylorTree")
cursor = conn.cursor()

def UpdatePrice(sym, b, o, h, l, c, v, ac):
    SQL = """
          insert into MarketDaily
          (Symbol, Bar, Open, High, Low, Close, Volume, AdjClose)
          values
          (
              '%s',
              %s,
              %s,
              %s,
              %s,
              %s,
              %s,
              %s
          );
          """ % (sym, b, o, h, l, c, v, ac)
    cursor.execute(SQL)
    conn.commit()
******End of Code***********************

After spending a lot of time getting all that going...I then turn back to pytables.  Maybe I need to dig deeper there.  Found some very good documentation here.  But, I'm still sitting here...nothing.  Hey, someone give me some motivation on working with this bad boy!  Anybody have any experience to share in regard to pytables?  If so, bring it on!  I need some mojo!

And that's where I am now.  Oh...and of course, will begin working on spinning through TC2005's databank and load historics into Sqlite.  How do I do that?  That involves working with COM objects and Python makes it very easy for you.  In fact...I'm amazed at how complicated it is to call a COM object from Microsoft's own languages like C#.  In python...all you have to do in order to get to the TC2005 COM object is...

******Begin of Code********
import win32com.client
w=win32com.client.Dispatch("TC2000Dev.cTC2005")
******End of Code**********

2 lines.  Now, I'm sure there is a much easier way to call a COM Object in C# that what I was trying to do.  If anyone out there knows how...please leave a comment.  I'm really interested to see how many lines it takes to connect.

One last thing...if C# is your thing...check out Microsoft's free version of Visual Studio, C#, and even SQL Server via the Express Editions.  C# not your cup of tea?  There is Visual Basic, Visual C++, and even Visual J++.

And that's it from here...where I'm hoping to catch up on some much needed sleep.

Later Trades,

MT