Strategies | Advanced Technical Analysis

Quant Strategy Inventor Overview
Quant Strategy Inventor is the powerful Excel based software to create profitable strategy for Stock and Forex market. With Quant Strategy Inventor, you can create the profitable trading strategies. You can analyse and predict financial markets. You can construct your own active and passive portfolio like the fund manager. You can do all this without pain of learning any programming language like C++, C sharp, MQL4, Easy Language, etc. Instead, you can do everything in your Excel Spreadsheet from backtesting to optimization of your trading strategy using simple spreadsheet formula. In this document, we introduce how to create your own trading strategy in 5 minutes in your Excel Spreadsheet. Since this is for first time user, we have put quite a lot of screenshots for better understanding. So get your Excel ready before you go through next steps.

Step 1 – Data Steps
You can use any data you want to build your strategy with our Quant Strategy Inventor including Forex, Stock, Future, etc. In this tutorial, for simplicity we will use two instruments, S&P 500 and Microsoft Corp to build our trading strategy. If you do not have data already, then you will need to collect data to build your strategy. To do so, you need to know the ticker or symbol name for S&P 500 and Microsoft Corp. You can find these tickers in yahoo finance. Make sure that you are using correct ticker name to download data. Below we show the ticker ^GSPC and MSFT for both S&P500 and Microsoft.

Now click on ADD-INS under the Excel Menu to select our Quant Strategy Inventor Graphical User interface. Click on the Basic Module 1.

In the Basic Module 1, you have data download tab and data simulation tab. In the data simulation tab, you can simulate around 11 different mathematical models to generate artificial data for you to study your trading and investment strategies. Here we will just use data download module only for simplicity. You can always play around with different feature of Quant Strategy Inventor.

To download data, click Ticker Range and then select ^GSPC and MSFT on your spreadsheet.

Do the same for Output Range to select where you want to download the historical data on your spreadsheet. In default our starting date is 2011 January 1. If you want, you can always change the starting date to download more data. Here we will just use default. Now click “Download Historical Data” button to download the data.

Now both S&P500 and Microsoft data have been downloaded. Delete Adjusted Close since our open, high, low and close prices are already adjusted automatically.

Data downloading steps are completed. Excel is so flexible tool having interfaces with many financial tools and software. If you wish, you can always use external data to create your trading strategy. Since we are going to use S&P 500 to generate signal for Microsoft, you should swap the data between ^GSPC and MSFT. Just because it is more sensible to explain Microsoft stock in terms of S&P500 although it is just theory.

Step 2 – Signal Steps
Building strategy is like building house. Normally you need a lot of information and careful plan to go ahead with it. In this tutorial, we will create some simple strategy for an example. We have intentionally chosen two instrument to show how flexible to build the trading strategy with our Quant Strategy inventor. For now, we do not know if they are profitable strategy or not yet. We will estimate their profitability in the backtesting and optimization stage later. In this signal construction task, we will use Triple Exponential Moving Average (T3) and Simple Moving Average Cross (SMA) signal. The syntax for T3 indicator is like this: “TA_T3(close, timeperiod=6, vfactor=0.7)” and in the Excel you will put the formula like this “=TA_T3(R10:R1466, 6, 0.7)” as an array formula. When you put array formula, you will select the range from U10 to U1466 first, then click formula bar to type “=TA_T3(R10:R1466, 6, 0.7)”. After that, click Ctrl + Shift + Enter at the same time. I know this Ctrl + Shift + Enter might be strange thingy to the first time user. However, millions of Excel users are using Array formula every day and you will get used to it soon. It is very handy. Once you have entered the array formula for T3 correctly, T3 indicator value will be calculated in U column. If you are new to Excel Array formula, then this link is useful for you: http://www.excel-easy.com/functions/array-formulas.html.

Use the same process to enter Simple Moving average array formula to V column too. The syntax for simple moving average is: “TA_SMA(close, timeperiod=13)”. So you will put “=TA_SMA(R10:R1466, 13)” from V10 to V1466. Now you have created both T3 and Simple Moving Average together. Next we will generate T3 – SMA cross over signal.

Before we generate signal, let us exam T3 and SMA on simple line chart. We can see that T3 is much faster than SMA. Therefore, we can generate buy condition like “if T3 is cross over SMA then buy”. Likewise, you can set sell condition like “if T3 is cross down SMA, then sell”. Here when we buy or sell, we will buy Microsoft and not S&P500. We do this to show how easy to create multi-asset based trading strategy with our Quant Strategy Inventor. If you prefer single asset trading strategy, then you will just need one price data. However, normally index like S&P 500 is quite significant indicator for US Stock market. It might be quite valid idea to use S&P 500 to trade Microsoft. However, in this tutorial, we will be more stick to technical instruction to show the step-by-step guide to build trading strategy without writing coding. In fact, we are not sure whether this strategy is profitable in real world yet.

To generate buy condition, if T3 is cross over SMA, we can simply enter formula: “=IF(AND(U11>V11, U10<V10), 1, “”)”. Note that U11 and U10 is T3 values at date 2011-01-04 and 2011-01-03 respectively. Likewise, V11 and V10 is SMA values at 2011-01-04 and 2011-01-03. This formula tells Excel to generate signal 1 if T3 value is greater than SMA value at date 2011-01-04 and if T3 value is smaller than SMA value at date 2011-01-03. So this is the mechanical way of telling Excel our cross over strategy in spreadsheet formula. Drag the formula down to cell W1466. Now your buy condition is ready.

In fact, in stock market, most of time, you will be profitable from buying the shares and not from selling the shares. In fact, you can still create sell condition if you wish. Whether you want to see the trading statistics for sell or not, it is your choice. In forex, you can make money when you are selling the instrument too. So forex traders, you should create sell signal too. To create sell condition, simply you can do the opposite for sell condition. For example, you can put the formula like this: =IF(AND(U11<V11, U10>V10), -1, “”). It is not too difficult if you think about what value each U11, U10, V11 and V10 represents. Also drag the formula to X1466. Now our sell signal is ready too. In our next step, we will generate actual order for buy and sell backtesting simulation.

Step 3 – Order Steps
To generate backtesting simulation, we will create nice heading for our order Instruction for the range Y8:AB9, which is coloured in yellow. You can use any colour or font you want. We used yellow colour for order for now. To make the virtual order which our backtesting and simulation machine will use, you will use the Order Open Buy syntax for buy like this:
OrderOpenBuy_(signal range, volume, take Profit, stop Loss, instruction, id).
Signal range are where the signal is located. For example, since we have put our buy signal at range W10:W1466, our signal range is W10:W1466. For the volume, we will put 1000. This means that we will buy 1000 shares of Microsoft, when we get the buy signal. Take Profit and Stop Loss can be empty. If stop loss and take profit is empty then in our backtesting simulation, we will assume that the order will be closed at certain condition. For example, it is common that closing buy position when the sell signal shows up. Therefore, in this tutorial, we will close buy condition when the sell signal shows up. If you wish to use take profit and stop loss, then you can use numeric values like 5 or 10. For example, when you use syntax like this: “=OrderOpenBuy_(W10:W1466, 1000, 5, 5, “”, 1)”. This means that if the price are rising by 5 dollar, the buy condition will be closed with profit and likewise, the price are dropping by 5 dollar, the buy condition will be closed with loss. Some broker may not allow you to use take profit and stop loss order, therefore, you should use this condition after you have checked with your brokers. Since we are not going to use take profit and stop loss condition, we will just use the buy syntax like this: “=OrderOpenBuy_(W10:W1466, 1000, 0, 0, “”, 1)”. Note that this is also array formula, So put this formula across the range Y10:Y1466 with Ctrl + Shift + Enter keys.

If you see the text like “Open, Buy, 1000, 0, 0, na, 1” in the same row as the buy signal row, then you have done everything correctly. This means that we will buy 1000 Microsoft shares when T3 moving average is cross over simple moving average of S&P 500. Now we will enter formula for buy close condition. We will assign ID: 1 for the signal generated. This ID is useful later when you want to combine multiple strategies in the same spreadsheet. As you imagine for second and third strategies you can give ID: 2 and ID: 3 respectively. The syntax for Buy close is: OrderCloseBuy_(signal Range, instruction, id). Like before, signal range are where the buy close signal is located. Normally this is when the sell signal shows up. Instruction is how you want to close the buy positions. Normally you should type “all” for the instruction. This means that you will close all buy positions. When you assign ID here, then we will only close order with the same ID. Otherwise, we will close all order regardless of the ID.

This is the syntax we will enter for our buy close order “=OrderCloseBuy_(X10:X1466, “all”, 1)”. This means that when sell signal (-1) shows up, we will close buy position for Microsoft. Since for standard brokerage practice, you can make profits from buy order only for stock market, we have already completed our task of building order management. In fact, we are ready for our backtesting simulation. For Forex traders, make sure that you are completing for both buy and sell orders.

Step 4 – Backtesting Simulation
Now to do backtesting simulation, we will call our backtesting simulation for Stock Data. To do so just go to ADD-Ins again and click Analytics Module 1.

Now you will see our backtesting simulator. In our backtesting simulator, you can perform backtesting, optimization and cost simulations. There are some inputs, which you have to fill in before backtesting. Data Range is the range where your price data are located. Order Range is the range where your order instruction are located. Input Range is where your input are located for your optimization. For backtesting, you do not need input range to be filled (For some Excel version you might be prompted to fill input range too.). Therefore, to start backtesting, you need to fill data range and order range for now. You can change backtesting inputs and commission settings later. But we will just use default setting for now since this is just simple demonstration tutorial.

For Data Range, make sure that you are including Date, Open, High, Low, Close, Volume columns as shown below for Microsoft as shown in the screenshots below. Most of time do not include the headings.

For Order Range, you only need to include Buy Open (Y) and Buy Close (Z) columns since we have not create sell open and sell close instruction.

Once you have filled Data Range and Order Range. You are now ready to do backtesting for your strategy. Simple click “Backtest” button.

After we have clicked the backtest button, our backtesting simulator will perform all the calculation and print trading results in the separate worksheet. Here we have found some interesting results. Can you see that the balance growth curve is rather going up? This is not bad for this simple strategy.

You can also check the detailed trading results. Check the net profit and annual compounding growth rate (ACGR). Our net profit is around 14435 US dollar with ACGR of 4.48%. We have just beat that useless interest rate. Anyway, this is only the first step with our Quant Strategy Inventor.

Step 5 – Optimization
Optimization is a powerful technique to improve your profitability. You can say this is some sort of brutal force search meaning that you are keep running your algorithm with different inputs until you search some profitable settings for your strategies. On the other hands, optimization takes quite lengthy computation. So you should use them carefully. Luckily, the optimizer in Quant Strategy Inventor is very flexible. You can define how many trials you want to do before optimization. You can also force the optimizer to quit in the middle of optimization task. So feel free to play around with our optimizer and try to find mega profitable trading strategy. When you want to quit during the optimization just press “X” key.
So now, we will introduce how you can start the optimization with our previous strategy. To do optimization, you need to create input control areas (range T1:V6) in your spreadsheet as we have shown below (coloured as green). In this tutorial, we will use time period of T3 moving average and time period of simple moving average as our sample input 1 and input 2. In the next step, we have to refer to our signal formula to this input cell U3 and V3. Therefore, our array formula for T3 column will become like this: “=TA_T3(R10:R1466, U3, 0.7)”. Our array formula for SMA will become like this: “=TA_SMA(R10:R1466, V3)”. Also we need to define start, stop and steps for each inputs. Since we are using T3 cross over SMA strategy. For optimization, you must have 4 rows including inputs, start, stop and step rows. You can have 1 or 2 inputs and you can even have 10 inputs if you like.

To choose our inputs sensibly, it is not wise to have large time period for T3 because if T3 is more lagging than SMA, we won’t get any signals. So we will start time period of T3 from 5 to 13 in the step of 1. So we will test 5, 6, 7, 8, 9, 10, 11, 12 and 13 for our T3 time period. For simple moving average, we can have greater period but not too great because we might not get many trades. So we will start from 13 to 50 only in step of 1. In theory, we will test all the combination of T3 period and SMA period in this range. This will be around 350 combination of two inputs. Let us see how our Quant Strategy Inventor is handling this 350 repetitive backtesting.

When you do optimization, you must fill the input range and you should never leave it empty.

Once you are ready with optimization, then click optimization button.

In our computer, 342 backtesting took little bit more than 1 minute. I think this is not too bad comparing to what we are rewarded with the profitable trading strategy. Once the optimization is done. Quant Strategy inventor will print the optimization results in separate worksheet. Let us exam the profitability. To do so, simply use Excel’s custom sort functions.

We will sort our backtesting results in terms of net profit for now. If you wish, you can use other trading statistics you like. Some people might prefer Sharpe ratio or Calmar ratio.

You can see that at some backtesting we have more than doubled up our profits from 14,435 US dollar to 37,942 US dollar.

Also our Sharpe ratio and Calmar ratio have improved quite a lot with our optimization. If you wish to drill down particular backtesting, then you can. Just simply copy and paste the new input 1 and input 2 into our strategy worksheet. Then do backtesting with new inputs. In our case, we will use 10 and 14 as our new inputs since they have shown great profits in our optimization.

Make sure that you enter new inputs 10 and 14 in our strategy worksheet cell U3 and V3.

You can see more sharp increase in our balance curve too in the balance growth curve below.

Conclusion
This tutorial covers the strategy building, backtesting and optimization feature of Quant Strategy Inventor. Note that this is only part of the functionality you can access from our Quant Strategy Inventor. As you have seen in this tutorial, most of time, you can use native spreadsheet formula to build your trading strategy. Even though we have spent quite a lot of time to explain these basics steps, in fact, it might take less than 5 minutes to test this trading strategy to some experienced hands. Whichever strategy you are building, please make sure that you go through data steps, signal steps and order instruction steps. If you want to use VBA to test your trading strategy, then most of time, your VBA formula will be working inside signal steps.
We believe that you can create any trading strategy you can imagine. You can combine different strategies too in one single backtesting. When you want to combine multiple strategies, you need to assign different ID to different strategies. The ID should be from 1 to 100. The ID can not be smaller than 0 and greater 100 for now meaning that you can combine up to 100 trading strategies. Cost simulation is much like optimization. You might perform the cost simulation after you have found profitable trading strategies.

Instruction (Manual) Document

This part should be filled by author before your submission.

1. Information about Author
Your Surname ATI
Your First Name ATI
Your Country ATI
Your Email Address ATI
Your ID on our website ATI

2. Information for the submitted materials
Title of the submitted instruction or manual Build First Trading Strategy with Quant Strategy Inventor
Language of Instruction English
Key words (at least 3) Forex, Stock, Investment, Trading, optimization, simulation, backtesting, technical analysis, economic analysis, Quantitative Trading
Date of Completion 17 October 2016
Version of this Document 1.0

3. If it is about any trading platform or any of our products (leave empty if you don’t use)
Name of Trading Platform Quant Strategy Inventor
Trading Platform version Version 5.03
Name of Product Quant Strategy Inventor
Product version 5.03