Excel System Development

N

naifwonder

Several people have told me that Microsoft Excel makes for very a powerful trading system development platform. Admittedly, my knowledge of utilizing excel for trading system development and back-testing is less then what I would like it to be. I have looked through the web for tutorials but the information is very scattered. As such, for those of you that are familiar with this matter, I am hoping that you could share some information.

Below are some questions which would help to clarify things. I am not expecting anyone to answer all of them (unless you want to :) ) but a few would be nice.

1. Do you currently use excel in your system development process or have you done so in the past? If so, how would you rate your experience with it?

2. How difficult do you find the learning curve for this program relative to a platform such as TradeStation? (I am aware that the answer to this questions is highly contingent upon the person's programming experience. Just for the record, I would rate myself as an intermediate level programmer.)

3. What language do you develop in?

4. Do you use any third-party applications in conjunction with excel for system development? If so, which and for what function?

5. Do you find the program to be fast and reliable in both back-testing and real-time environments?

6. When analyzing large sets of data, how CPU intensive is it relative to a program such as TradeStation?

If you have any other information which you feel would be useful to know regarding this topic, please feel free to share.

BTW - My primary desire is to be able to analyze data on a tick by tick basis across multiple markets on multiple time frames simultaneously. I would also like to be able to include other number sets induced by myself to be analyzed along with the tick data. These secondary numbers sets may or may not be induced in real-time. If you feel that Excel is not the solution for this and are aware of something else, I would be glad to know.
 
Quote from naifwonder:


BTW - My primary desire is to be able to analyze data on a tick by tick basis across multiple markets on multiple time frames simultaneously. I would also like to be able to include other number sets induced by myself to be analyzed along with the tick data. These secondary numbers sets may or may not be induced in real-time. If you feel that Excel is not the solution for this and are aware of something else, I would be glad to know.
More...

I would suggest that Excel is not the tool for this task. You need a proper event driven framework and quite possibly a "real" programming language such as Java, C++ etc.
 
I'm also interested in this thread, but my questions are more on automating with excel than testing.

Does someone use excel to trade multiple systems( not one single system on a portfolio )? How reliable it is compared to TS?
What are the limitations? Can you handle second intervals?Can you automate anything including DOM? Do you access all IB's orders type( futures spread, peg... )?

Thanks a lot.
 
Quote from TraDaToR:

Can you handle second intervals?
More...

I am aware that it may seem awkward to provide answers on my own question thread but why not if I can right? I would not imagine second intervals to pose a problem. You could get data on a second interval and import it into excel or get tick data and aggregate it into a second interval data set.
 
I did a lot of work using Excel to backtest trading systems at the portfiolo level and the truth was it was painfully too slow. Even if you put the backtest engine in C++ and call the DLL, the out of process communications makes the process too slow.
I even wrote a series of articles on using Excel as a backtesting platform including one on using Excel for money management.

My conclusion is backtesting in Excel is just a toy.

This was the reason I got involved developing TradersStudio in 2001. If you like Excel, TradersStudio can export reports in XLS and also has complete OLE automation with Excel. It can even load excel and start running excel macros.

Please take a look at this product. I think it will solve your problems. In addition our language is a lot like Excel VBA and comes with a source level debugger. You can also write addins in VB 6.0 as active x dll's.

http://www.tradersstudio.com/Overview/tabid/68/Default.aspx
 
Quote from naifwonder:

Several people have told me that Microsoft Excel makes for very a powerful trading system development platform. Admittedly, my knowledge of utilizing excel for trading system development and back-testing is less then what I would like it to be. I have looked through the web for tutorials but the information is very scattered. As such, for those of you that are familiar with this matter, I am hoping that you could share some information.

Below are some questions which would help to clarify things. I am not expecting anyone to answer all of them (unless you want to :) ) but a few would be nice.

1. Do you currently use excel in your system development process or have you done so in the past? If so, how would you rate your experience with it?

2. How difficult do you find the learning curve for this program relative to a platform such as TradeStation? (I am aware that the answer to this questions is highly contingent upon the person's programming experience. Just for the record, I would rate myself as an intermediate level programmer.)

3. What language do you develop in?

4. Do you use any third-party applications in conjunction with excel for system development? If so, which and for what function?

5. Do you find the program to be fast and reliable in both back-testing and real-time environments?

6. When analyzing large sets of data, how CPU intensive is it relative to a program such as TradeStation?

If you have any other information which you feel would be useful to know regarding this topic, please feel free to share.

BTW - My primary desire is to be able to analyze data on a tick by tick basis across multiple markets on multiple time frames simultaneously. I would also like to be able to include other number sets induced by myself to be analyzed along with the tick data. These secondary numbers sets may or may not be induced in real-time. If you feel that Excel is not the solution for this and are aware of something else, I would be glad to know.
More...

1. yes. excellent for what I use it for
2. very easy
3. VBA, although there is a strong case for trying to use all native excel functions, because they are compiled and run much faster.
4. Yes, you will almost always want to use a DDE/RTD link from somewhere, like Bloomberg, IB, Qcharts, whatever...
5. Would not use it for backtesting, ever. Real time (RTD) is fast. DDE fairly slow, but i bet not many people notice.
6. Very, would not use it for that type of thing. Real time data is fine, and can handle A LOT of data. Back testing, monte carlo's, anything of that nature will take a very long time....

TNG
 
It all depends on what you are doing. I built my system in Excel and it is automated, but I wouldn't recommend it for everybody. Here's why I use Excel:

- Trade end of week. Speed of execution is not tremendously important. My trades get done in a second or two. But if you are scalping, you need something faster.

- Fundamental data. If you have building a technical system, there are many programs out there for you. But not many incorporate fundamental data.

Using Excel is very labor intensive. And if my broker (IB) changes something in the API, I may have to waste time getting my system working again. If you can use a commercial system/trading platform, I'd highly recommend it. And if you need lightning fast executions, you probably want to code. But Excel is very powerful if it fits your need.
 
Excel is a must tool to learn, specially if you do quantitative model development. In the hands of an experienced Excel operator, it can be as useful a research tool as something like Matlab, but at a fraction of the cost.

That said, imo no one in their right mind would run a model of any complexity over a non-trivial number of symbols live in Excel. You can do your research in Excel, but you don't use it when implementing the model live.

C#, C++ [ok, VB or Java if you must].

nitro
 
Quote from nitro:

Excel is a must tool to learn, specially if you do quantitative model development. In the hands of an experienced Excel operator, it can be as useful a research tool as something like Matlab, but at a fraction of the cost.

That said, imo no one in their right mind would run a model of any complexity over a non-trivial number of symbols live in Excel. You can do your research in Excel, but you don't use it when implementing the model live.

C#, C++ [ok, VB or Java if you must].

nitro
More...

Nitro... I'm surprised. For someone who doesn't know how to program, what would you recommend for real time data analysis?

I know pro ATS guys aren't using excel, obviously, but for most retail and pro traders who don't want to learn to program, it's a goddsend.

TNG
 
You must log in or register to reply here.
Back
Top