Hello,
I’m trying to create a simple excel trading system that shows equity evolution, drawdown, etc but I dont know how to start.
For example i want to analyse the SP daily closes that the system gives e a Buy signal if “X” happens and after that calculate sharpe, cortino etc.
Does anybody have a simple excel trading system as a sample?
Thanks!
If you are just getting practice in calculating these things, why not use some kind of momentum strategy or moving average strategy. Buy when the system is above some moving average or if the last X points of data are sufficiently positive, cash out or sell in the opposite case. That is not too hard to code up. Basically you look at returns for each period and keep a running product of returns for all periods where you are exposed. Or you can build an equity curve based on each period’s return and convert it to your net returns.
In my experience, the tricky part of building back tests is in the position weighting side of things. Optimization (if you do that) requires solver if you have more than two assets, and you’d need to do that for every period where you might trade. Also the rebalancing decision is an extra layer of complexity if you are not going to rebalance at every trading period. It’s even trickier if you are going to try to figure out if rebalancing benefits outweigh expected transaction costs (frankly, I often skip that step). By this time, you are beyond what anyone sensible would try to manage in excel, and it’s better to move on to a more capable platform like R or something.
By contrast, if you are just going long or short something like S&P, or changing how much leverage you are using, it’s a lot easier.
The other thing to look out for is look-ahead bias. It is an obvious thing to avoid in theory, but it is disturbingly easy to sneak in to your system. Whenever I have a system that looks too good to be true, the first thing I do is inspect it for possible look-ahead bias.
Ibazer, what exactly do you want to gain from the excel sample?
Do you want to see how someone else programmed excel to crunch the data obtained from the results of using a trading signal on past data? That is something apart from knowledge of trading systems. That is just knowing how to program excel.
Are you more interested in getting ideas of signals to use in a trading system and how to develop/ test trading systems in general. THAT is a much more complex can of worms your are opening and a simple example of one persons trading system will not be that helpfull to you. As bchad started to touch on, systems trading is all in the execution and leverage. The signal is only part of it.
I have been working with an experienced trader in the development and testing of algorithmic trading systems for the last few months. One of the first things I learned was … the first rule of algorithmic trading is you don’t talk about algorithmic trading! Really though, if someone has a good system that works, they aren’t telling you about it. I remember I naively posted a screen shot of a backtest of one of our systems on FB and my partner got pretty mad and threatened to stop working with me!
Anyway, if you are intersted in systems trading, I recommend these sources:
The Evaluation and Optimization of Trading Strategies by Robert Pardo
Systematic Trading by Robert Carver
The forum on Elite Trader under Automated Trading is good for asking questions and learning more.
well, aren’t you cute.
For the record, sharing discretionary trades after the fact is not eroding anyone’s edge.
There’s some links to pages that implement technical trading rules in Excel here : http://breakingdownfinance.com/trading-strategies/technical-analysis/
if (close [t+1] > close [t]) then buy close [t] else sell close [t]