Build First VBA Strategy with Quant Strategy Inventor

Below is the Text Only Excerpt automatically extracted From the Full PDF Article for Non PDF Viewer:   

Build First VBA Strategy with Quant Strategy Inventor

Flexible Quantitative Strategy Builder for Forex, Stock and Future Market

Tutorial Overview
Quant Strategy Inventor is the powerful Excel based software to create profitable strategy for Stock, Forex and Future 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, MQL5, Easy Language, MatLab, etc. Instead, you can do everything in your Excel Spreadsheet from backtesting to optimization of your trading strategy using simple spreadsheet formula. Or if you prefer, then you can still use full VBA code to build your trading strategy In this document, we introduce how to create your own trading strategy using VBA formula. 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. There may be quite a lot of similarity with the previous article “Build First VBA Strategy with Quant Strategy Inventor”. To follow these steps, we assumed that you have already installed our Quant Strategy Inventor in your Excel.
To build your trading strategy with VBA, there is two ways of doing the same job. Firstly, you can build your trading strategy in the fresh new workbook. Secondly, you can build your trading strategies in Quant Strategy Inventor_v5.xx.xlsm workbook. Both way will work for you. However when you build hundreds of trading strategies, it is better to use new fresh workbook for each strategy. We already shipped our Quant Strategy Inventor_v5.xx.xlsm file with several strategies. In this tutorial, we will show you how to build our VBA trading strategy using the fresh new workbook.
To do so, firstly open our Quant Strategy Inventor_v5.xx.xlsm file. I hope you have already completed installation and license activation. You can already see there are several strategies in your spreadsheets in the Quant Strategy Inventor_v5.xx.xlsm. Let us ignore them for now because they were built in using Excel Spreadsheet formula. Do not change or alter anything in this workbook since we will create new workbook to build our strategy.

We will create Macro Enabled Workbook since we will use VBA code. To create new blank workbook, just go to File menu and select New. Select Blank workbook.

Then, save it as My Test VBA Strategy.xlsm file in your hard drive. Now you have your fresh blank workbook ready for you. You will proceed to Data Steps as usual.

Even in the new workbook, you should be able to access to our Quant Strategy Inventor Menu. All of them should work.

Sometimes, you may not see our Quant Strategy Inventor menu in your new workbook. It is fine. Simply go to your Developer’s ribbon. Then click on Macros menu.

Now you will see List of macros available from your workbook. Then select “StartQSI” macro and click “Run” button. Assuming that you have your license is installed correctly and your Quant Strategy Inventor_v5.xx.xlsm file is already opened, you should be able to access to Quant Strategy Inventor menu from your new workbook. Now we will proceed to typical strategy building process. The first step is, as usual, data step.

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.  From our Basic Module 1, you can download most of US and non-US stock data. If you want to build Forex trading strategy, then you can just pull historical data from your Meta Trader. In this tutorial, for simplicity we will use stock price of the Microsoft Corp to build our VBA 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 Microsoft Corp. Just type MSFT somewhere in your spreadsheet. Download data using our 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 later.

To download data, click Ticker Range and then select MSFT on your spreadsheet (i.e. only select MSFT since we only need Microsoft data for now.) Once you have downloaded with Microsoft data. We are done for our data step.

Step 2 – Signal Steps
In VBA, you can do anything you want. It is very flexible. You can create your own technical and price action indicator or any mathematical models for your trading. For simplicity and educational purpose, we will use simple Bollinger bands for our trading strategy. We can build our own Bollinger bands in VBA code but we will use easier route. We will just use the built-in Bollinger Bands technical indicators to save time.

So we will just enter the syntax “=TA_BBANDS(I11:I1467, $L$4, $M$4, $M$4)” to our range L11:N1467. Remember that this is array formula too. So you have to enter the formula using “Ctrl+Shift+Enter” key at the same time. We have placed our indicator period and standard deviation level respectively to L4 and M4 cells. We have already structured our input control this way just in case we want to do some optimization later. Once you have entered the formula correctly, you will get upper, middle and lower bands from left to right.

Our sample trading strategy here is that we will buy if the price hit upper Bollinger bands level and likewise we will sell if the price hit lower Bollinger bands. This is typical momentum trading strategy. We buy when there is momentum. We do not know yet whether this will work for Microsoft or now. In fact the whole purpose of this tutorial is to find out the profitability of this strategy.  We just chosen this example for educational purpose for now. Next bit is fun bit, since we are going to do some VBA coding. To start VBA coding, just click on Developer’s Ribbon again. Then click “Visual Basic” menu.

Ok, now you can see the famous visual basic editor used by millions of analyst and traders every day. Soon you will be one of them too.  In this tutorial, we will build what we called User Defined Function to generate our signal with Bollinger Bands.

To do so firstly insert new Module. The Module is where we can put our code. Once module is created, you will just see blank page where you can start to build your code.

Now we will create a user defined function for our signal. I named it as BBandsStrategy. You can name it to anything you want. In the code below, pRange is price range for Micro soft and iRange is indicator range for our Bollinger bands. I have put some variables we need later. For example, I declared r and c variable to loop through our price and indicator later. As you can see, you can just use all general VBA coding style as you need. “buy_sell_sig” variable is where we will hold our buy and sell signal.

Now here is important code block which we will actually generate signal from. What we try to do here is that we are collecting price data using pRange and indicator values using iRange. Then we compare close price with upper and lower bands level to generate signal. Make sure that we need to check our price and indicator values are numeric values first. If you don’t, you might get error. So let us be more protective. Since upper and lower bands values are stored in first and third columns, we collect the values using iRange.cells(r, 1) and iRange.cells(r, 3). Likewise, we collect our close price for Microsoft suing pRange.cells(r, 4).  “r” is the row number in our spreadsheet. Buy condition is easy to create. It is simply if close price is greater than upper bands, then we generate signal 1. Likewise, if close price is smaller than lower bands, then we generate signal -1 for sell. Then we will loop through all the close prices in our Microsoft data and store our signal in the “buy_sell_sig”. Then return these signals to end of our function.

Ok, our User Defined Function for Bollinger Bands signal is ready. Now let go back to our Spreadsheet and use this User Defined Function. Since this is array formula again, enter “=BBandsStrategy(F11:I1467, L11:N1467)” to the range  O11:P1467 with “Ctrl+Shift+Enter”. Well you can see that our Function works fine generate signal 1 or -1 according to our trading rules.

Now let us do our order steps. We assume that you have already read our Previous article Build First Trading Strategy with Quant Strategy Inventor. So we will enter this syntax for our buy order:
“=OrderOpenBuy_(O11:O1467, 1000, 0, 0,”na”, 1)”.
This syntax says that we will buy 1000 Microsoft shares when buy signal shows up. We will assign this strategy as ID 1. We will do the same for our buy close, sell open and sell close.

Ok I assume that you have completed all. Now let us run our backtesting to see if this strategy is really making money. So click Analytic Module 1 and open Backtesting Stock page. Fill the price range, Order range, and then run the backtesting.

The balance Growth Curve is not so impressive for our first VBA code strategy. Microsoft is not so working well with our momentum strategy.

So let us try to use mean reversion trading strategy just tweaking our previous strategy. In contrast to our previous trading set up, we will buy if the close price is hit lower bands and likewise, we will sell if the close price is hit upper bands. So we are setting up the trading rules the other way around to our previous trading rules. This is how the code looks like for our second VBA strategy. Literarily we buy when the prices hit the lower bands and sell when the prices hits the upper bands.

Make sure that you are re-entering the formula again just to refresh the values in your worksheet. Let us do the backtesting again. Ok, now our Balance Growth Curve is rather going up. So we might say that Microsoft Stock prefer mean reversion type of trading strategy.

Let us do further optimize for the indicator period and the standard deviation values. With optimization, we can find better settings. Let us select the most profitable setting from this educational strategy and do the backtesting again.

Ok after optimization, the profit have been improved quite a lot. I guess this was good exercise for you to learn how flexibly you can build trading strategy with our Quant Strategy Inventor.

The “My Test VBA Strategy.xlsm” file is shipped together. So you can find the sample VBA strategy file inside your QuantStrategyInventor.zip file. However, just in case, we will list all our code here too. This is simple strategy but you can always expand this simple strategy into more complex and serous one later as your VBA coding skills get better. Just copy and paste this code to your VB editor to start with.

Sample VBA Code for our sample Strategy

Option Explicit
Function BBandsStrategy(pRange As Range, iRange As Range)
Dim buy_sell_sig() As Variant
Dim r As Long ‘r = row counter
Dim c As Long ‘c = column counter

Dim rowSize As Long
Dim colSize As Long
rowSize = iRange.Rows.Count
colSize = iRange.Columns.Count

ReDim buy_sell_sig(1 To rowSize, 1 To 2)

Dim b_upper As Double
Dim b_lower As Double
Dim p_close As Double
Dim curSignal As Integer
curSignal = 0

For r = 1 To rowSize
If IsNumeric(iRange.Cells(r, 1).Value) = True And IsNumeric(pRange.Cells(r, 4).Value) Then
b_upper = CDbl(iRange.Cells(r, 1).Value)
b_lower = CDbl(iRange.Cells(r, 3).Value)
p_close = CDbl(pRange.Cells(r, 4).Value)

‘        ‘Buy Signal Condition
‘        If p_close > b_upper Then
‘            If curSignal = 0 Or curSignal = -1 Then
‘                buy_sell_sig(r, 1) = 1
‘                curSignal = 1
‘            End If
‘
‘        End If
‘
‘        ‘Sell Signal Condition
‘        If p_close < b_lower Then
‘            If curSignal = 0 Or curSignal = 1 Then
‘                buy_sell_sig(r, 2) = -1
‘                curSignal = -1
‘            End If
‘        End If

        ‘Buy Signal Condition
If p_close < b_lower Then
If curSignal = 0 Or curSignal = -1 Then
buy_sell_sig(r, 1) = 1
curSignal = 1
End If
End If

        ‘Sell Signal Condition
If p_close > b_upper Then
If curSignal = 0 Or curSignal = 1 Then
buy_sell_sig(r, 2) = -1
curSignal = -1
End If
End If

    End If
Next r

BBandsStrategy = buy_sell_sig

End Function