Hello Handle123, Very good post. What do you mean " 3000 sample size"? Are you stating backtesting a strategy with atleast 3000 historical trades? Thank you
You can use a circular reference and iterative calculations: http://access-excel.tips/excel-iterative-calculation/
For just looping, you can create new rows with cell formulas refering to the rows above and do conditionals, calculations and whatnot. You can use many excel functions over areas of cells and arrays, to aggregate, lookup values, translate etc. However, soon, especially for something like Monte Carlo, you'll need VBA or a more general-purpose language since you're hitting the limits what is practical and sane. So Excel got you quicker up and crawling, but to run, you may need to investigate other options, unless Excel serves good enough. It's important to learn more and not think the learning as "wasted time" I believe. We tend to think everything as a straight line, when they are anything but for hard-to-achieve goals.
Just ran a first backtest and got a systematic positive return. Not exactly over the top but it's a start. Unless I messed up something, will see that after I review the algorithms again and run several more backtests.
Depends, if system puts out many trades, you will want to that spot areas like the last 15 year if you can . Like have a system that only takes longs would be sweeping up, but times of bear market, you have enough rules in place for system to stop taking trades.
This my 20th year trading options. Here is a few lessons I have learned in the last 20 years: [Lesson 1: Advisories and Newsletters] In the first several years I tried every option advisory and newsletter there was at the time. Only a few intermittantly made money but those eventually disappeared. Conclusion: You can't count on anybody for trading advice, because they will eventually disappear and leave you starting over at ground zero. ------------------------------------------------------------------------------------- [Lesson 2: Back Testing] Back Testing only semi-works but is flawed. Back Test Example 1: Did the Low Price of the Day Fill? Lets say you have a math based system that requires the following historical option data prices: Open, High, Low, Close. Now lets say your trading system said to Buy the SPY JAN 272 CALL at 1.56 at Sell it at 1.96. Your historical data says: OPEN=1.82 HIGH=2.56 LOW=1.56 CLOSE=2.40 You assume your trade filled at 1.56 because that was the low of the day and then the Close was 2.40 so your trade obviously won, right? Not necessarily. Without a Time and Sales Report you won't know if 1.56 went off at the bid or ask (bought or sold). Time and Sales reports historically only go back about a month on options, so its purely guess work as to if the trade filled and won or didn't fill because 1.56 was never available at the asking price. Back Test Example 2: What Really Happened after the Fill? Lets say you have a trade that says Buy at 1.50 and Sell at 1.90 and the historical option data days OPEN=1.80 HIGH=2.00 LOW=1.40 CLOSE=1.75 You would assume the LOW=1.40 occurred after the HIGH=2.00 and then the option price never moved higher than 1.75 after you were filled at 1.50, right? Not necessarily true. 20 years of Testing, Option Charting, reading Time and Sales Reports, and actually Trading options has a historical value of 50% correct in your assumption. In other words, 50% of the time the option price Opened at 1.80 and then tanked to 1.40 and then went to the High of 2.00 and then declined near the end of the day to 1.75. Back Test Example 3: Did the Stop Price hit before or after the profitable sell? Your Buy Limit was filled at 1.60 and your Sell limit was 2.00. You see a high of 2.20? Your thinking this back-test won. But did you notice the Low of the day was 1.10 and your Stop was 1.20? So did the trade win or stop? Without accurate option charting which only goes back a month you will never know. Conclusion: Because of the 3 flaws noted above in back-testing, when I develope a back-tested theoretical system that has a Win/Loss Record of 85-90% over a minimum time frame of 12 months, that trading system doing the real time trades over a period of the following year has a actual Win/Loss Record of 70% to 75%. (I did some of these real time in my old threads Purely Mechanical Option Trading and Purely Mechanical Option Trading 2.) ---------------------------------------------------------------------------------------------------- [Lesson 3: Total Automation] It it Always Possible on Every Trade? Not Always. To have total automation means your computer program interfaces with something like Interactive Brokers Application Programming Interface (API). So you set it up for the following parameters on your option choice: Buy Limit: 1.50 Sell Limit: 1.90 Stop: 1.10 So this order is active before the market opens (otherwise its not total automation) and the option opens with a Gap Down Opening of 1.20. Your order fills at approx. 1.20 (give or take due to the volatility Gap Down) and immediately drops lower to 1.00 and you are Stopped out! Total Automation doesn't work in cases of Severe Gap Down Openings. I use semi-automation: I have a semi-automated I.B. Bracket Order ready (but not transmitted) with my Buy Limit: 1.50 Sell Limit: 1.90 Stop: 1.10, "BUT" I wait to see the opening price before I transmit the order. If the option opens below my Buy Limit, I simply re-adjust the Bracket Parameters accordingly. Example: Opens at 1.20. Adjust Bracket to Buy Limit: 1.20 Sell Limit: 1.60 Stop: 0.80 Conclusion: Semi-Automation, not total. Too many wild gap downs when market volatility picks up. (And yes, someday Volatility will come back into these markets.)
Before you even begin looking to "backtest" anything, get a grasp of he second arc sine law, and how that might affect the results you see with any trading system.
Hmm? I don't think understanding the nature of random processes per-se is as valuable as understanding the limitations of your data and the various biases that you introduce in the backtesting process. Once that is internalized, next step is to understand how various ways you tweak the strategy produce a fit (i.e. that any empirical modification introduces a bias too). Plus, for anything that deals with asymmetry, understand the limitations of conventional analysis tools.
Very nice post! Can you or anyone (@sle?) comment on something that stops me from diving deeper into options? How do you manage overall exposure? Most online advise I follow says to sell premium. Then they say invest only about 50% of total capital. They say try to diversify, but I don't think it's as easy to do with options as with futures/equities. So I have an impression that option trading is for hobbyists, retired people trading with play money, or ex traders who just trade part of previously earned pot. Are there any traders making living trading options ONLY portfolios (not OPM)? How do they mitigate risk? How do they position size and diversify when, essentially, they are either long volatility or short volatility? Thank you.