Friday, March 27, 2009

A reader comments on trading using Excel VBA and Factor Model

Thoughtful comments from a reader John S. from the UK on his experience with trading technology and models:

"I have been developing my own personal automatic trading systems using Excel VBA and based on rules I have developed over the years as an active private trader investor using both technical and fundamental data analysis.

One of the key merits in adopting an automatic trading system approach that has helped me is to avoid the temptation for manual interference and thereby improving profitability by maintaining consistency. I have found the challenge of developing a successful system very rewarding from a personal perspective as I recognise that there are many that have tried and failed. However one problem I have encountered is my ongoing desire to regularly modify and improve the system which I have found can become counter productive as there is a real danger that system development becomes an end in itself! I just can't seem to stop tinkering as soon as I come up with a new idea or feature!

One advantage of using Excel VBA that I have found is that it is inherently flexible as it facilitates the processing of data which can be important especially when using fundamental data as part of the system. In this respect I recognise that every trader is trying to build in an edge that will make the system more profitable. I have noticed that many traders seem to only focus on price by trying to seek an edge by looking at special indicators or combination of indicators etc. Combining price data analysis with a Factor Model approach is a challenge which is ideally suited Excel VBA as it can be easily used to process both fundamental and macroeconomic data into a form that can be integrated with price data analysis.

I recognise from your book that Matlab is more powerful than Excel VBA and may be just as flexible in integrating fundamental and macroeconomic data but I just wanted to draw your attention to benefits I have found using Excel VBA which may suit those who like myself are more comfortable in using Excel VBA and are reluctant to change. Other features that can be exploited that I have found helpful when back testing are automatically producing Price Charts that incorporate Entry and Exit points which provides visual reassurance that the system is working as intended as well as generating automatic Word reports recording key output for future reference.

I am sorry if I sound too much like an advert for Microsoft!"


Excel4Net said...

I reckon the major reasons behind a popularity of Excel/VBA in a quant and quant trading world are:

1. A lot of people already using it - so everyone think that's the way to go.

2. Simplicity of VBA (not sure if it correlates with its flexibility) - that makes it possible to effectively use by anyone - whether it is a trader, quant or a desk developer.

3. VBA and Excel could be easily extended (to improve a performance, integrated a 3rd party software or just to modularize/re-use purposes) by moving the actual model logic into C++, COM or .NET

As for the later (easy way to integrate quant analytics written in .NET with Excel and VBA) - one may have a look at my solution:

Greg said...

I definately think the bandwagon factor is at play, we are often like sheep, and I see no reason why investment strategy or systems would be any different.

merke said...

a little bit of luck will not interfere in the forex

Ricardo Arroja said...

"However one problem I have encountered is my ongoing desire to regularly modify and improve the system which I have found can become counter productive as there is a real danger that system development becomes an end in itself!"

Interesting point. I reckon that the best way to deal with this is to accept (or refuse) the kind of Win/Loss ratio your trading method produces. What I mean is that if you trade counter trend and, psychologically, you only feel confortable with a high level of winning trades, you'll have a hard time coping with a system that generates as many wins or losses. In these circumstances, perhaps you (and the system) will be better off by setting up stricter criteria and sacrificing some setups - but not too many - in order to achieve a higher level of winning trades (even though, in the process, you also might reduce your Avg Win / Avg Loss ratio!).

But I agree: it's tricky issue that burn out event the most seasoned traders.

Yucca said...

"However one problem I have encountered is my ongoing desire to regularly modify and improve the system which I have found can become counter productive as there is a real danger that system development becomes an end in itself!"

Dear John,

I was very surprised by your post. In the world of quantitative analysts (derivatives models design and implementation etc. for top tier investment banks, I have spent 6 years doing that) Excel VBA is believed to be the least flexible and sustainable programming language we have to deal with; systems written using these languages are about to be retired. I understand that now you are still happy with it, but thought you might wish to study the alternatives. At a relatively modest entry price, you can get performance and sustainability that would surprise you.

VBA issues that come to mind are:
* poor performance,
* poor memory management (this can make the performance even worse),
* you cannot use version control system that would allow you to track changes (who-what-why-when) and would facilitate collaboration (see e.g. and; there should be some Microsoft version control tools). Based on my experience, starting from some volume VBA code become unmanageable, one of the reasons for this being that you cannot use version control.
* poor flexibility (compared to the alternatives I will be
** absence of classes (class = structure + methods that can access and modify the contents of the structure)
** virtual absence of abstraction mechanisms (Variant is very error-prone). You might need them if you want to use the same algorithm for a stock and for a yield curve (same action, different objects).

Alternative "easy to use" programming languages would be Matlab and Python. Both languages are SVN-friendly (see the third bullet), Excel-friendly, but have poor performance.

Matlab is quite costly ($1K - $10K, depending on what packages you need and on your location), much nicer and user-friendlier, support team is at the ready,... Matlab performance tip: vectorize your code (operate with vectors and matrices rather than on element-by-element basis, e.g. vector where element i would be a price of stock X on date [observation date + i days] or matrix where element ij would be a yield of currency Y on date i for maturity j). Another way to speed up Matlab is to buy a package that can convert Matlab code code into C++ code, that can be compiled in a DLL you can use in Excel. Such DLL would work much faster (may be a hundred times faster... depends on your task).

Python is a freeware, quite austere, it takes a bit of time to get into it, but it would be worth it. It is more flexible, more of a "proper programming language".

Other languages you might wish to consider are C#, VB.NET and stand-alone VB (all by Microsoft, all reasonably priced). I would position them in between C++ (see below) and Excel VBA; C# would be the most powerful one, VB would be the simplest for you to use - it is almost identical to VBA. Again, there is a trade-off between performance/flexibility and straight-forwardness/similarity to Excel VBA.

Hand-written C++ code is the best from the performance point of view, this is a quite versatile language, but it takes a lot more time to learn it.

Hope you would find it interesting...

Unknown said...

Hi Dr Ernie Chan

I have read your book on Quantitative trading. It is said that MATLAB is good tool to develop complex strategies. But there is no well approved API for that. There is one recently found MATLAB2IB. But is it good enough and well tested?

It is said in your book that Excel/VBA is slow as compared to C++.

I am interested in developing a Automated Trading System. Shall I use this new MATLAB2IB and continue to develop strategies in Matlab? I am good in Matlab and I have used it extensively during my Ph.D and other work. I have not used C++ much and I found it more difficult as compared to MATLAB. Given a choice, I will always do coding in MATLAB.

But is it necessary to develope strategies in C++, if I want to develop a automatic trading system?

Ernie Chan said...

Hi Vinay,
I have used matlab2ibapi for several months, and have found it to be quite useful and reliable for automating my strategies. In fact, I will be publishing an article illustrating how to use it.

Unknown said...

About spreadsheet and quantitative finance:

Since I also find the strategy development on spreadsheet much faster and simpler,
I wondered if there is a better tool than Excel / VBA.

Someone told me about quantrix/Groovy ( Do you know it ?

Ernie Chan said...

Hi Massimo,
It may be faster in the short term, but it cannot handle a massive amount of data.
I have completely given up on using spreadsheets for almost anything.
Matlab or Python is the way to go.