"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
