Tom at Neural Market Trends asked:

Nice, I was planning on using Excel to build an interface with their API as well. Was it hard to do? Do I need to learn Visual Basic?

What I want to do is address this question from a trading perspective.

Do I Need a Spreadsheet?

This is a good question because you might be tempted to believe that writing an application would be a better approach. Though the problem with trading is that you are constantly trying out new strategies. Implementing a strategy using a programming language takes too much time. Some people tend to prefer MatLab, and I can understand that, but it is not for me.

Here are the reasons why you want to use a spreadsheet

  1. Quick turnaround on strategies.
  2. Easy way to process large amounts of data.
  3. Easy way to graph large amounts of data.

Here are the reasons why you don’t want to use a spreadsheet

  1. Ask any investment bank on their audit abilities of a spreadsheet and you will get expressions of desperation. Spreadsheets don’t audit well and it drives risk managers bonkers.
  2. It drives software developers bonkers because they don’t have complete control of the application.
  3. Those who like control don’t like Excel because you can twiddle with it.

The main reason why you want to use a spreadsheet is that traders love them. And I know from my own personal experiences I love them as well. I would never think of developing a trading application using anything but a spreadsheet.

Which Spreadsheet Should I Use?

Most folks use Microsoft Excel, but the question is if Microsoft Excel is the best choice. You could use Open Office, but it is not usable. Open office is too slow, too bulky and too difficult to extend. The ability to extend is a royal pain in the butt and stops me from even considering Open Office further.

Some developers might wonder, what about a spreadsheet component that could be integrated into an application. While that approach seems acceptable, it is not because it is too difficult to keep traders happy. Traders like to play around with things and a spreadsheet component does not have that flexibility.

There are other spreadsheets, but at the end of the day, pay the Microsoft Office Tax. Yes it drives me bonkers, but you have to hand to Microsoft, Excel is very very good. I just upgraded to Office 2007.

Which Version of Microsoft Excel is Best?

These days there is Excel 2000, Excel XP, Excel 2003, and Excel 2007. Following is a list of technologies that you can use, and in the next section I will explain the various technologies.

  • Excel 2000: Supports XLL, and is limited to 65536 rows.
  • Excel XP: Supports XLL, COM automation, and is limited to 65536 rows
  • Excel 2003: Supports XLL, COM automation, and is limited to 65536 rows.
  • Excel 2007: Supports Updated XLL, COM automation, Server .NET Managed UDF components, and is unlimited in size.

How to Extend Microsoft Excel?

Extending Excel is a both painful and easy. If you want performance then the only solution are XLL extensions that are written in C. Yes you can write XLL extensions in C++, but you use a C interface. With Excel 2007 XLL extensions have been extended and prepped. There are XLL helpers (1,2) that simplify the code that you need to write, but they cost money. Some of these helpers let you use .NET managed components.

For my own purposes I use ExcelDNA, which is an Open Source toolkit that lets you write your code using .NET. The toolkit is a bit clunky to use, but it works and avoids having me to write C code.

Another way of automating and extending Microsoft Excel is to use COM. Using COM is going to slow you down quite a bit. For live trading purposes COM is not a recommended. Only use COM if the number of COM calls per second are not that big.

In Excel 2007 there is a new approach called UDF and I referenced it in extending link. It would seem that UDF should be faster, but I am skeptical. To get UDF working you need an IIS installation with SharePoint. In essence Excel Services is a very clever Web Service infrastructure, and I am not knocking that. I am knocking the solution as being faster than COM.

You can also extend Excel using Visual Basic for Applications. It is a pretty good solution and with Excel 2007 the development environment is pretty acceptable. Think of it as Visual Basic 6.0, which was by itself pretty good. Though I don’t know if I would develop all of my applications using VBA.

So in the end the only real Excel extension solution you have is XLL. Some people have talked about how XLL is a dead end technology. Though from what I have researched XLL just keeps on chugging due its high speed. XLL is a very specific technology intended for use only in Excel. You might need to learn C, but if you use ExcelDNA you can use .NET.

My end solution is Excel 2007 (which I just upgraded) using a combination of XLL, and COM automation. And finally to address Tom’s other question what about API to the market. I use Interactive Brokers and have written my own bridge between Excel and IB. Though IB delivers a spreadsheet that uses DDE to connect to their trading software.