Tuesday, August 22, 2006

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

3 comments:

Anonymous said...

What is the goal of your project exactly? I'm in a similar situation to you and have several half-finished market related projects in python/postgres sql, but i think i should maybe switch to pytables. since i really dont need a relational db for time series data. doing stuff like getting todays open and yesterday's close for all days is harder in sql without some extra trickery.

Anonymous said...

oh by the way, check out
http://rpy.sourceforge.net/

makes it easy to use R from inside python and it meshes well with Numeric arrays and all that.

Another very useful package is matplotlib. It even has some usable stock charting examples for candlesticks.

Mike Taylor said...

epicjohn,

The goal of the project is to take a set of trades from a system and apply dynamic allocation methods against those trades on a weekly basis. I need the ability to determine the current volatility of a stock within the portfolio and compare against the overall volatility limit of the portfolio. If too high...adjust the position (shares) down until the risk is back to acceptable levels.

Of course, I'd also like to test the ability to adjust upward if volatility drops. Many people say adjusting upwards shouldn't be done...but I want to test it regardless.

So, I'll need the capability to read in a set of trades, determine risk of the positions while being held, and cut back or add to as necessary until the system's trailing exit kicks in.

But, if I'm going to go that far with the development...I might as well code the entry and exit portion to output those trades instead of my current method of using Wealth-lab and outputting the results to csv files.

Thanks for the R/python link...that is right up my alley. I use R a lot in examining the system csv files. So, that could prove very helpful in the project. I'll take a look at matplotlib as well. Though, my plans are to keep this a batch-oriented environment.

If you try the pytables out...let me know what you think about it. I hope to try it out a bit this weekend. Maybe write a post comparing the create, insert, fetch, drop of sql versus the pytable equivalent.

Have a great weekend!

MT