U.S. Unemployment

I am tired of Bloomberg News quoting single numbers and percentages in their articles on unemployment, it gives me an incomplete picture. So I decided to graph the relevant series myself. I sourced the data from the St.Louis Fed and started graphing.


The gray areas are the recessions as reported by the FED. The current recession has apparently ended, but even the FED has the latest date as a placeholder only.

As you can see Roubini and Bernanke are "right". This recovery is jobless. Never before has the duration of unemployment been so long. Basically, people who lost their jobs are still unemployed. The "official" unemployment is at 10%, but as you can see in the second graph the labour force has decreased in the last year, which is strange since the labour force has a general uptrend. Also the participation rate is down, which means that the FED is hiding unemployment behind their definition of "people who have not looked for a job in the past 4 weeks are not part of the labour force".

I created the graph using Python with matplotlib. For a while now I've been using the two to prototype studies at work. Matplotlib is great, and I can see that it's constantly improving. A year ago it could handle plotting only a day or two of high-frequency data, now I can plot a week. The widgets (sliders, radio buttons, etc) included in the package make prototyping extremely easy. I am also learning more and more about Python and I like what I am seeing, generally.

It was fairly easy to put together the graph, but I found out that another package which I like R with quantmod can do the work as well. Choices, choices... maybe I'll end up combining Python with R :)

Now, on to graphing more of the economy ...

Intraday Data Loading Using Python

I find myself using Python for most of my scripting needs, although sometimes I revert to Bash.

A recent trading idea required intraday data. I found it difficult to find historical intraday data for free; all the news sites offer only historical daily close data. Google and MSN offer real-time quotes, but not historical, creating a page scrapper and running it for a few days seemed like overkill.
I found a small shareware application called "Historical Quotes Downloader" (it uses data from prophet.net) and used it to get 5 days of minute-by-minute intraday data for the S&P 500. That left me with 500 files that needed to be loaded in MySQL...so I wrote a little Python script to do just that. After loading the data, I plan to use quantmod for R to do the research.

Here is the quick Python script for loading files from Historical Quotes Downloader/prophet.net into MySQL, each tick is in a separate table.

# import all of the intra-day data in the table
# create a table for each of the files, using the name before the first period
# and load all of the intraday data
#
# this script could be implemented using fileinput, should try that next time

import sys, datetime, time
import os, fnmatch
import MySQLdb

try:
conn = MySQLdb.connect(host = "localhost",
user = "username",
passwd = "password",
db = "intraday")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)

if len(sys.argv) < 2:
print "Proper Command: "+sys.argv[0]+" ..."
sys.exit(0)

# expand the regular expression to all the matching files
fileList = []
for fileName in os.listdir(os.path.dirname(sys.argv[1])):
if fnmatch.fnmatch(fileName, os.path.basename(sys.argv[1])):
fileList.append(fileName)

# loop though each of the files passed in
for tickFileName in fileList:

tickName = os.path.basename(tickFileName)
tableName = tickName[0:tickName.index(".")]
print "creating table:%s:\n" %(tableName)

cursor = None
# create the table
try:
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE `"""+tableName+"""` (
`datetime` DATETIME NOT NULL DEFAULT 0,
`open` DOUBLE NOT NULL DEFAULT 0,
`high` DOUBLE NOT NULL DEFAULT 0,
`low` DOUBLE NOT NULL DEFAULT 0,
`close` DOUBLE NOT NULL DEFAULT 0,
`volume` INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(`datetime`)
)ENGINE = MYISAM;""")
cursor.close()
conn.commit()
except MySQLdb.Error, e:
print "Error %d: %s" %(e.args[0], e.args[1])
try:
conn.rollback ()
except:
continue

#open the file and load
tickFile = None
try:
tickFile = open(os.path.join(os.path.dirname(sys.argv[1]), tickFileName), "r")
cursor = conn.cursor()

# Symbol (optional), Date (optional), Time (optional), Open, High, Low, Close, Volume
tickFile.readline() # remove the header
tickLines = tickFile.readlines()
for line in tickLines:
# detect the end of the ticks in the file
if line.find("barsofar") != -1:
break
tokens = line.split(",")
try:
sqlDate = time.strptime(tokens[0], "%m/%d/%Y %H:%M:%S")
except (ValueError, IndexError):
print "Unexpected format " + str(sys.exc_info()[0])
break #the file might be empty so proceed with next one

try:
cursor.execute("""INSERT INTO `"""+tableName+"""` (datetime, open, high, low, close, volume) VALUES(%s, %s, %s, %s, %s, %s)""",
(time.strftime("%Y-%m-%d %H:%M:%S", sqlDate),tokens[1],tokens[2],tokens[3],tokens[4],tokens[5]))
except MySQLdb.Error, e:
print "Error %d: %s" %(e.args[0], e.args[1])
break

cursor.close()
conn.commit()
except MySQLdb.Error, e:
print "Error %d: %s" %(e.args[0], e.args[1])
try:
conn.rollback ()
except:
pass
finally:
if tickFile != None:
tickFile.close()

# end of for loop over files

conn.close()

The files I created are in the format MMM.intraday and I call the script "python intraday_db_load.py *.intrday".

If the experiments are a success and a trading pattern is discovered, the plan is to use the Interactive Brokers API for real-time data. I am hoping that opentick.com comes back and I can make use of their API too.

Helper Scripts

At work (FX Trading company) I created a number of scripts to analyze our logs.
I wanted to look at the spreads (ask - bid) and how they change during the day, average price comparison between liquidity providers, as well as graphing the delay of producing a tick in our system.

The first few scripts were written in bash and then gnuplot was used to graph the results. However, recently I discovered SciPy an extension to Python, along with Matplotlib for graphing. I have converted most of my scripts to Python - it's a lot cleaner, and easier to work with. The only problem I've encountered is having too many data points - it crashes Matplotlib, so then I have to fall back to gnuplot.

I am planning on learning more of Python and SciPy - I wonder if finance packages already exist.