Algorithmic Trading

I made an Excel Add-in this week to run a macro embedded in the add-in to sort a database of all our vendor information (address, banking information, etc...) from a text file to a tabular spreasheet. The text file wouldn't fit on an Excel 2003 spreadsheet (66,000 rows), so I had to upgrade to 2007 to get 'er done. I was very proud of myself.

Got me thinking. How difficult is it for an individual investor to set up algorithmic trading? I am curious to see what options are available to incorporate technology into analysis and trading strategies, and what kind of software is currently available to do this. It sounds a little dangerous, but I realize my gut has shit for brains (High Fidelity quote), so I have to take the subjectivity out of trading as much as possible.

I started looking at RSS news feeds and got inspired to put some automation into my stock research. But how?

MSN offers an Excel Add-In that will allow you to download stock prices by formula. Just reference the cell with the ticker symbol and it will return the appropriate value in the specified cell. Excellent! However, the add-in only provides a few fields of data (last price, high, low, etc..., wih maybe 20 or so data points in total). I wanted more....

So after searching around for a while, I got sidetracked by another Excel Add-In called Windows Azure Data Market. This is a really impressive research tool that essentially links to a bunch of different databases (some you have to pay for, but most are free) on all sorts of different information. The data can be directly imported into excel as a table. The interfaces will take some getting used to in order to understand exactly what information is available, and also I would complain that there is no explanations of how the data was calculated. Still, many, many massive databases of economic, and social data with easy import into excel is well worth any hassle.

After that discovery, I continued my search for a way to import additional stock information into Excel, and hit the jackpot with a site called Old School Value. This guy built an Excel add-in with a series of Macros that provide several thousand pieces of stock data and can even look up on past dates. The macros reference many different source websites. While the macros are quite large, they don't seem all that complicated. I am going to launch a project to deconstruct the macros in order to figure out how to reference the websites to obtain the stock data. The I will build my own version of the macros to suit my needs. Excellent stuff that should keep me busy for quite some time.

I have developed a couple of Macros now at work for converting text files to a database, and think I will learn a lot by taking on this challenge.

Also received my certified treasury professional course package, 2010 tax prep software, several library books on treasury, banking, and investments. Going to be busy for a while.....

OK, breakthrough today. Basic Input/Output Macro solved. This simple piece of code will download a comma delimited HTML file, and paste it into Excel. I haven't finished with the parsing formulas yet, so it will come all in one string, and need a manual text to columns for the moment.

Most of this was pieced together from a number of different examples found online. These sparse lines of code put in a pretty package are earning some of these guys a lot of cash selling it as software.

Sub Quote()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate "http://download.finance.yahoo.com/d/quotes.csv?s=CCME&f=snl1a2b2b3b4a5bc1ee8j3ghvt8xt1opr"
Do Until Not ie.Busy
DoEvents
Loop
Range("A1") = ie.document.body.innertext
ie.Quit
Set ie = Nothing
End Sub

Information on how to create the website reference (http://www.gummy-stuff.org/Yahoo-data.htm)

Now I just need to work on parsing the ticker in from a cell in Excel into the website address and parsing the output cleanly into excel from the comma delimited file. Easy stuff. Then it is on to creating a robust version that will allow modification of the fields downloaded from Yahoo, and a table listing their definitions.

Then I have to decide the proper format to aid in analysis, add my excel formulas for validation. Streamline the download in the most efficient way possible so it doesn't take an hour to download 1000 quotes at a time, and add some macro buttons and things to simplify the data entry (and ensure data integrity).

I am wondering if perhaps MS Access is the more appropriate program for this kind of thing, since it would manage multi-dimensional data (quarterly/annual financial statements, historical price data) much better than excel. I don't have much skill in access, so for now I am sticking with excel.

Then, I would have to see if I can add data from other sites as the guy from Old School Value has done, which creates a whole new set of challenges.

Success! Are you excited? I am excited!

The following Subroutine "Quote" will download a website address (entered in cell A1) and output to Excel. The function "QuoteURL" will return the website address by inputing a range of cells for stock tickers (US stocks entered normally, Canadian stocks must add ".TO", ".V", ... for the exchange) and another range of cells for the Yahoo fields (listing in the link to Gummy Site provided in previous posting).

I think the subroutine should not update continuously, since it is refreshing from the internet and will consume more resources. Therefore, you need to re-run it every time you want to update the data. The function will update continuously the same way as Excel ranges can have items inserted and formulas based on those ranges will update in real time to expand the range, so it allows easy modification. The Function can be eliminated by coding the subroutine to search for the necessary data, but I thought the function would be an easier and more robust way to do it.

You can modify where you want the output to appear on the spreadsheet as you see fit, and you have your very own customizable stock importation macro, which will allow you to analyze many stocks in excel quickly and easily. Unfortunately, this is just the code to download the basic data. There are other links in Yahoo that can provide historical price and volume data (I think on a more than daily basis), and also will provide historical financial statement data. Adding these lookups will really make this thing useful. Then it just comes down to formatting and presentation.

I have kept the coding as simple as possible and am not worried about exception handling and things like that. Maybe later.


Sub Quote()

Application.ScreenUpdating = False

Dim ouputArray() As String
Dim ouputFields() As Variant
Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")
ie.navigate Cells(1, 1).Value
Do Until Not ie.Busy
DoEvents
Loop

ouputArray = Split(ie.document.body.innertext, vbCrLf)

ie.Quit
Set ie = Nothing

ReDim ouputFields(0 To UBound(ouputArray))

For i = 0 To UBound(ouputArray)
ouputFields(i) = Split(ouputArray(i), ",")
Next i

For x = 0 To UBound(ouputFields)
For y = 0 To UBound(ouputFields(x))
Cells(x + 10, y + 2) = ouputFields(x)(y)
Next y
Next x

Application.ScreenUpdating = True

End Sub

Function QuoteURL(quoteTickers As Variant, dataFields As Variant) As String

Application.ScreenUpdating = False

Dim tickers As String
Dim fields As String
Dim url As String

For x = 1 To quoteTickers.Count
If x = 1 Then
tickers = quoteTickers(x)
Else
tickers = tickers & "+" & quoteTickers(x)
End If
Next x

For y = 1 To dataFields.Count
fields = fields & dataFields(y)
Next y

url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & tickers & "&f=" & fields

Application.ScreenUpdating = True

QuoteURL = url

End Function






Just cut and paste code into Visual Basic Editor (can be reached by pressing Alt-F11 from any Excel spreadsheet). First you will want to use the macro recorder to record a macro (any Macro), and save it to the "Personal Macro Workbook". This way when you enter the VB Editor, you will have a module set up for you already that will allow your code to be used on any workbook that exists now or is created in the future. When in the editor, press Ctrl-R to view the project explorer and expand VBA PROJECT (Personal.xlsb). Open the subfolder called Modules and double click on Module1. Paste the above code into the blank screen that comes up and then click save. Your code can now be accessed by searching for a User Defined function in Excel for the function, and running the Macro for the Subroutine.


OK, its not very polished, and still has work to be done to get it working perfectly, but it opens up some pretty good possibilities for analysis.

Talked with Alberto for a bit, and he suggested that I treat the project as a stock screener. Instead of just downloading a couple of stocks at a time, I should download the entire universe of stocks and then sort/rank them using Excel features to provide candidates for further analysis. Imagine having the data for the entire stock market on one screen and being able to use a pivot table to pull the data you want!

This makes a lot of sense to me, so I probably can replace the custom function and provide a static variable of all the stock tickers. Maybe I could add some code to download this from a website somewhere. The problem is that there are physical limitations to the size of a website address, so I would have to break down the list of stock tickers and loop the macro through several iterations. Hopefully this will not add much to the processing time.

I have learned a little bit about what is possible in referencing elements of a web page and extracting data from it, so I will see if I can add more data based on less accessible sources than I have been using so far.

I finally completed the modifications to make the macro work with more than 200 tickers (now it is unlimited). I spent some time finding tables of all the stocks for Amex, NYSE, Nasdaq, TSX, and TSX - Venture exchanges, and have input these into the spreadsheet.

I now have the entire North American stock market universe downloaded in about 2 minutes, and ranked according to my custom formulas.

Look out for more picks coming soon......

OK, Yahoo has added password access to the link I was using to download the quotes. Looks like I am going to have to re-code for Google, since even if I added some way to enter the password, it would slow down the speed to the point of impracticality.

Too bad, because the Yahoo portal was very efficient.

Figured out that internet explorer 9 is the problem. downgraded to IE8 and everything is back to working order again. However, I took a look, and almost all of the best value companies from the screener turn out not so good when looking closer.

I will keep looking....

Max, I'm an excel nut too. maybe not as much as you though . I would love to try this out and give you my feedback. just switched jobs so time is a factor lately but rest assured i'm following this thread!

Figured out how to add a signature to Macros, so this should reduce the packaging issues. I already sent you the demo and will send you the full version when I get a chance.

Worked on the coding a little and got a huge increase in efficiency. Can now download the entire stock market on one spreadsheet in seconds instead of minutes.

Was originally taking the block of text (quotes) from yahoo, parsing the file into lines, then parsing the lines into columns, and feeding each cell one by one into excel. Decided just to feed each whole line into the first column in excel, and then do a text to columns on the whole column after.