Quantisan.com

Data Scraping the Toronto Stock Exchange: Extracting 3,660 companies’ data

Paul — Thu, 11 Mar 2010 12:29:08 +0000

One of the tasks that I’ve always wanted to make more efficient in my stock trading is the work of scanning for stocks to trade. One look at my trading strategy posts and you’ll see that I have devised many stock scanning systems in the past few years. The most recent system that I’ve used is one that uses options data to filter stocks. However, it is not automated. So it takes a lot of time to gather and analyze the data. Furthermore, the set of tools that I use is limited to U.S. stocks. Now that I have taken an interest in the Canadian stock market, I can’t seem to find any public tool that I like. Thus, I am biting the bullet now and taking my time to develop a custom system once and for all.

Before we can analyze stock data, we need to extract them first. Where better else for that than go straight to the source at TMX.com, the parent company of Toronto Stock Exchange (TSX) and TSX Venture Exchange (TSXV). TMX.com provide a list of publicly traded companies in ten Excel files. The files are divided by sectors. Each contain a number of fundamental company data, such as market capitalization and outstanding shares. So step 1 is to extract those data.

This is where I am at now. I attached the source code for an alpha/developmental release below for anyone interested. It is a working program to scrape the data from TMX.com’s files. But it’s still a work-in-progress. That’s why I am calling it a version 0.1.

The next milestone is to program a Stocks class to hold, organize, and manage all three thousand, six hundred, and sixty companies’ data. This is an easy task to do by extending the built-in dictionary class in Python. However, I haven’t gotten to that chapter yet in my scientific programming with Python learning book. I stopped at chapter 8 to work on this project. Chapter 9 is the inheritence and hierarchical material.

The goal of this project is to build an automated data scraping program for TSX and TSXV data from various sources into my computer. Once I have my data, that’s when the real fun starts.

Regarding the code below, I know that source code is useless for most people. Once the project is complete, I will compile the code into a standalone application and post it on this site. Subscribe to my RSS feed so that you can keep up-to-date with the progress of this project and my other ramblings on trading.

# extractTMX.py
# version: 0.1 alpha release
# revision date: March, 2010
# by Paul, Quantisan.com

"""A data scraping module to extract company listing excel files from TMX.COM"""

import xlrd									# to read Excel file
#import sys
from finClasses import Stock				# custom Stock class

def _verify():
	"""Verification function for a rundown of the module"""
	pass									# copy test block here when finished

def findCol(sheet, key):
	"""Find the column corresponding to header string 'key'"""
	firstRow = sheet.row_values(0)
	for col in range(len(firstRow)):
		if key in firstRow[col]: return col		# return first sighting
	else:										# not found
		raise ValueError("%s is not found!" % key)

def scrapeXLS(book):
	"""Data scraping function for TMX Excel file"""

	listingDict = {}						# dict of ('ticker': market cap)

	for index in range(book.nsheets):
		sh = book.sheet_by_index(index)

		mcCol = findCol(sh, "Market Value")
		assert type(mcCol) is int, "mcCol is a %s" % type(mcCol)
		osCol = findCol(sh, "O/S Shares")
		assert type(osCol) is int, "osCol is a %s" % type(osCol)
		secCol = findCol(sh, "Sector")		# multiple matches but taking first
		assert type(secCol) is int, "secCol is a %s" % type(secCol)
		hqCol = findCol(sh, "HQ\nRegion")
		assert type(hqCol) is int, "hqCol is a %s" % type(hqCol)

		for rx in range(1, sh.nrows):
			sym = str(sh.cell_value(rowx=rx, colx=4))		# symbol

			s = sh.cell_value(rowx=rx, colx=2)	# exchange col.
			if s == "TSX": exch = "T"
			elif s == "TSXV": exch = "V"
			else: raise TypeError("Unknown exchange value")

			mc = sh.cell_value(rowx=rx, colx=mcCol)		# market cap
			# check for empty market cap cell
			mc = int(mc) if type(mc) is float else 0

			os = int(sh.cell_value(rowx=rx, colx=osCol))	# O/S shares
			sec = str(sh.cell_value(rowx=rx, colx=secCol))	# sector
			hq = str(sh.cell_value(rowx=rx, colx=hqCol))	# HQ region

			listingDict[sym] = Stock(symbol=sym,exchange=exch,
				mktCap=mc,osShares=os, sector=sec,hqRegion=hq)
	return listingDict

def fetchFiles(fname):
	infile = open(fname, 'r')				# text file of XLS file names
	listing = {}
	for line in infile:						# 1 file name per line
		if line[0] == '#': continue			# skip commented lines

		line = line.strip()					# strip trailing \n
		print "Reading '%s' ..." % line

		xlsFile = "TMX/" + line				# in TMX directory
		book = xlrd.open_workbook(xlsFile)	# import Excel file
		listing.update(scrapeXLS(book))		# append scraped the data to dict
	return listing

#if __name__ == '__main__':					# verify block
#	if len(sys.argv) == 2 and sys.argv[1] == 'verify':
#		_verify()

if __name__ == '__main__':					# test block
	listing = fetchFiles('TMX/TMXfiles.txt')

Forex Trading: Income or Capital Gain Tax in Canada?

Paul — Mon, 08 Mar 2010 15:29:02 +0000

I’ve always known that foreign exchange trading is treated as capital gain tax in Canada. But just to be sure before filing my taxes soon, I’ve decided to double check the facts from Canada Revenue Agency. As you know, the difference between income tax and capital gain tax is substantial. Income tax is taxed at your marginal tax rate. Whereas capital gain tax is a generous half of your marginal tax rate. That works out to a 10% to 20% difference.

Taxes in Canada is generally simple to do. The problem though, is sifting through the cacophony of information within the Canada Revenue Agency to find out the applicable rules. I’ve copy and pasted a couple of relevant excerpts from the 2010 CRA Income Tax Interpretation Bulletin for the record.

Basically, forex trading can be treated as either income or capital gain tax in Canada (surprise). According to IT-95R Foreign exchange gains and losses.

2. Where it can be determined that a gain or loss on foreign exchange arose as a direct consequence of the purchase or sale of goods abroad, or the rendering of services abroad, and such goods or services are used in the business operations of the taxpayer, such gain or loss is brought into income account. If, on the other hand, it can be determined that a gain or loss on foreign exchange arose as a direct consequence of the purchase or sale of capital assets, this gain or loss is either a capital gain or capital loss, as the case may be. Generally, the nature of a foreign exchange gain or loss is not affected by the length of time between the date the property is acquired (or disposed of) and the date upon which payment (or receipt) is effected.

As you can see, it is very vague. That’s why forex trading can be considered income or capital gain tax. It is up to you and your accountant to figure out which works for you.

A noteworthy point in the above excerpt is that the holding period is not taken into account. So there’s no 30-day rule like in the states whereby frequent trading would miss out the capital loss credit if they re-purchase the same asset within 30-day of disposal.

Further down the page in IT-95R, we have the following bullet.

6. A taxpayer who has transactions in foreign currency or foreign currency futures that do not form part of business operations, or are merely the result of sundry dispositions of foreign currency by an individual, will be accorded by the Department the same treatment as that of a “speculator” in commodity futures see 7 and 8 or IT-346R. However, if such a taxpayer has special “Inside” information concerning foreign exchange, he will be required to report his gains and losses on income account.

IT-346R Commodity Futures and Certain Commodities explains the tax treatment of speculation in the commodity markets.

7. As a general rule, it is acceptable for speculators to report all their gains and losses from transactions in commodity futures or in commodities as capital gains and losses with the result that only one-half the gain is taxable, and one-half the loss is allowable subject to certain restrictions, (hereinafter called “capital treatment”) provided such reporting is followed consistently from year to year.

So there, we have it. Amateur forex traders, such as myself, can report our forex trading gain/loss as capital gains and losses. The reason being that forex trading isn’t part of my business operation because I have another primary source of income (e.g. salary from another job).

Twitter Weekly Summary for 2010-03-07

Paul — Mon, 08 Mar 2010 04:50:00 +0000

just bought some exercise resistance bands online, will start P90X in a couple weeks when they arrive #
with the #market being squeezed at an imp junction, I find strangles to offer the best odds atm for a breakout either way $$ #
good intro resources on Complex Event Processing http://ow.ly/1f3j9 #
spent the morning at the vet with my pet, quickest $1k bill that I signed… those vets can really rake the $$$ in! #
Thx 4 #FF #forex #traders @fx_13 @Ancient_Warrior @JS_Trader @FXDIRK @LisaCNolen #

Another reason against stocking into a registered trading account this year

Paul — Fri, 05 Mar 2010 12:32:10 +0000

There’s another reason why I am so reluctant to put money into a TFSA trading account to invest in Canadian stocks. As you know, in a registered trading account, such as a RRSP self-directed trading account or a Questrade TFSA trading account (aff), you cannot short shares and can only buy because you can’t trade on margin. As such, a registered trading account is an account for buy-only trading.

It doesn’t take a genius to figure out that a buy-only trading strategy is best used … in a bull market. As trading wisdom goes, trade with the trend. So the question is, are we in a bull market in 2010?

Figures 1 and 2 shows the weekly chart of TSX Index and the TSX Venture Index, respectively.

Referring to Fig. 1, TSX is bumping up against some headwind at a 12,000 resistance. Unless TSX can stay above 12,000 cleanly (a weekly low above that level at least), I am still bearish in the market because that financial crisis back in 2008 (remember those days?) is still overshadowing long term prices and trader sentiments.

TSX, weekly

TSX Venture is depressed even more as seen in Figure 2 below. Just look at the huge gap from current price to the 200 week moving average (red line). This isn’t a good sign as lack of interest in high-risk stocks means traders are generally cautious.

Yes, it’s true that the equity market has been running up, up, and up for a good year. However, the fact that TSX is sitting at the same level as in 2001 proves that the long term, multi-year bull market has been cleanly broken.

We might very well see pockets of multi-month rallies materialize (like we’re seeing now), but unless proven otherwise, the long term trend is not looking green for the markets. It will not be easy money in the stock market like back in the pre-tech bubble 1990’s.

As opined by Bill Cara, the time for buy-and-hold is gone. It is well known in the community that the coming years will be volatile with big ups and downs. That is my little secret for moving to trade forex last year and the main reason why I am reluctant to set aside money on a one-sided stock trading account.

TSX Venture, weekly

First look at Google App Engine for automated trading and quant analysis on the cloud

Paul — Thu, 04 Mar 2010 02:19:31 +0000

I just spent the last few hours looking into Google App Engine to use it for trading. Google App Engine (GAE) is a cloud computing development and hosting platform for web applications. GAE is similar to the well-known Amazon EC2 service but it is also very different. The main difference between GAE and EC2 is that GAE is not as flexible as EC2 but it is a lot easier to develop on. Think of it like using C++ (EC2) to write your own trading platform to using EasyLanguage on TradeStation or MQL on Metatrader (GAE).

Another advantage for using Google App Engine is that it is free! Well, to a certain extent. It is free for a limited monthly usage. However, Google’s free quota is known to be very generous (think Gmail) and this is no exception. Based on the numbers, it looks comparable to a typical $20/month virtual private server (VPS) hosting package. My guess is that you can run a decent personal quant program on it if you keep the resources for yourself (private use). For more information on the free quota, you can read the current GAE quota web page or the Wikipedia page for GAE.

You might be wondering how did I spend a few hours just reading on GAE? The answer is that I’ve done a brief research, planning, and assessment to see how GAE can be put to use in terms of quantitative analysis for automated and discretionary trading. Without going through all of my notes and logic, here’s the gist of my conclusion.

For Automated Trading

Since GAE is also a SDK, it has a restricted programming API. You can’t just plug in any custom library of your own (i.e. your broker’s API) into your program. That is its advantage and its disadvantage. Furthermore, GAE only support HTTP and HTTPS. So you can’t open a socket for your connection either. These limitations won’t be changing anytime soon as GAE is designed to be like that for security reasons. Basically, there’s no way for your web application on GAE to connect with your broker for trading. So much for that idea.

For Discretionary Trading

Things look brighter for discretionary trading though. A big bonus with using Google App Engine is that it supports the Google Finance API. Google Finance offers free real-time stock quotes. Combine it with the programming capability of the Google App Engine and you have yourself a free number cruncher in the cloud. However, GAE is definitely no match for your personal computer, so why would you go through all that trouble?

One possible scenario in which this could be useful is for traders that is often on the road or traders such as myself in which we have a day job. In that case, you can set you quant analytics on GAE as a private web application and then you can access it as a web page anywhere on your mobile phone.

One of the obvious limitation with using GAE is that it has limited features for scientific computing. For example, it doesn’t support Numpy in the Python SDK. As such, I can’t imagine using GAE for anything more than simple technical analysis for now. I certainly hope that GAE can include more powerful scientific computing capabilities in the near future.

Until then, I really can’t see Google App Engine offering much help aside from performing algorithmic tasks to help with your trading analysis. Their open issues log does announce that Numpy is being integrated into GAE though. So that’s a great sign for things to come! I am very hopeful of GAE’s potential as a free cloud server to host my quant analytics on. We’ll just have to wait for now.

February 2010 Trading Performance Review

Paul — Tue, 02 Mar 2010 13:14:06 +0000

I barely traded this month. The total number of trades in February for my forex demo account is ten. That’s far from my regular 150 – 250 trades a month. Ever since my trading system was broken because of technical difficulties, I’ve changed my priorities in trading. I spent most of my time in February learning a new programming language to ramp up my quant and automated trading projects.

I also tried a few longer term trades this month. The results haven’t been uplifting. I am about breakeven by the end of the month with a net gain of …. $2 in a $5550 demo account. As you can see, there’s really not much point in me continuing paper trading at this stage. It is taking valuable time away from me to fix what’s broken so that I can use my previously profitable trading system again. As such, I’m going to put near 100% of my effort to program a semi-automated trading system for the coming weeks, or probably months.

On another note. Seeing that it is tax season here in Canada. I thought about my tax situation and considered opening a Questrade TFSA trading account (affiliate link) to legitimately avoid income taxes from trading. But after considering the cost and benefit of such a move, I concluded that a TFSA trading account isn’t cost effective for me at the moment.

As I’ve spent most of the month doing backend work, there isn’t very much to review for my trading this month. So let’s just dive right into my statistics for the record.

Performance Statistics

Monthly Metric February (January)

Sarting Fund $5,550.02
End of Month Fund $5,551.99

Total Net Profit   $1.97    (-$67.42)
Gross Profit   $37    ($375)
Gross Loss   -$35    (-$442)
Profit Factor   1.06    (0.85)
Net Profit %   0.04%    (-1.20%)
Max. Drawdown %   0.50%    (3.95%)

Sterling Ratio   0.00    (-0.15)
Excess Return   -0.02%    (-1.26%)
SDev Return per Trade   0.15%    (0.11%)
Kurtosis   -1.36    (1.85)
Skewness   0.79    (1.01)

With only ten trades this month, I don’t have enough samples to make meaningful calculations for the above statistics.

95th Percentile P&L   $12.55    ($12.23)
5th Percentile P&L   -$6.60    (-$8.43)
Net Profit Mid 90% Only   -$4.75    (-$117.84)

Interestingly, I noticed that my typical top and bottom 5th percentile profit/loss are quite consistent.

Total # of Trades   10    (186)
Percent Profitable   40.00%    (41.45%)
# of Winning Trades   4    (63)
# of Losing Trades   6    (89)
# of Break-even Trades   0    (34)

My percent profitable trades seem to be near 40% month to month. That’s good to know as it’s useful to calculate my statistical return expectancy as I’ve done in my TFSA trading cost-benefit analysis.

Average Trade Profit %   0.00%    (-0.01%)
Average Winning %   0.17%    (0.11%)
Average Losing %   -0.11%    (-0.09%)
Ratio Win% / Loss%   1.59    (1.20)

By being more discriminating in my trades, I’ve effectively increased my win/loss ratio. That’s good.

Max. Conseq. Winners   3    (8)
Max. Conseq. Losers   5    (20)
Largest Winning Trade   $13.81    ($22.66)
Largest Losing Trade   -$7.08    (-$14.30)

Avg. Time Held [hrs] 1.67 (0.73)
Avg. max. unrealized profit/ Realized profit (11.59)

Goal for March

My goal from January is to learn MQL4. I changed my plan since then due my lack of faith with using a proprietary scripting language. I learned Python programming this month and is now learning to use specialized libraries to serve my trading needs.

My goal for March is to write a rudimentary data analysis and visualization program for historical forex data.

40 monitors, 6 computers, 1 day trader

Paul — Mon, 01 Mar 2010 15:06:50 +0000

I’m still recuperating from my food poisoning from last week. Meanwhile, I came across this unbelievable trading workspace showcase from LifeHacker.

40 monitors trading workspace

I have no idea how this trader can juggle 40 monitors during his day trading. I am satisfied with just one 24″ monitor on my desk. However, I’m neither a day trader or a professional trader, so perhaps I’m missing something?

What do you think of this 40 monitors setup? A dream come true or a charts nightmare?

No related posts.

Twitter Weekly Summary for 2010-02-28

Paul — Mon, 01 Mar 2010 04:50:00 +0000

Happiness ain't all it's cracked up to be – life – 26 February 2010 – New Scientist: http://bit.ly/bfXdQ1 via @addthis #
RT @fx_13: Trade Like a Dealer: (And Avoid Death by a Thousand Stops) http://bit.ly/c1qTfM #
RT @TechCrunch John Doerr On Bloom Energy Launch: “This Is Like The Google IPO” http://tcrn.ch/9Pda1Q #
RT @inegoveritas: How do you say "Chainsaw Massacre" in Russian? @mbusigin #canadavsrussia #
Wired: How Google’s Algorithm Rules the Web http://shar.es/mmEqR via @sharethis #
I am really resisting temptation to trade more these days. But I need to focus on programming to crunch some price data… #