Using EXCEL VBA to Combine Equity Curves from TradeStation

A Poor Man’s Equity Curve Merger

Sometimes you just want to create a combined equity curve of several markets and for one reason or another you don’t want to use Maestro.  This post will show you an indicator that you can insert into your TradeStation chart/strategies that will output monthly cumulative equity readings.  After that I refresh my VBA skills a little bit by creating a VBA Macro/Script that will take the output and parse it into a table where the different months are the rows and the different market EOM equities, for those months, will be the columns.  Then all the rows will be summed and then finally a chart will be produced.  I will zip the Exel .xlsm and include it at the end of this post.

Part 1:  Output Monthly Data to Print Log

You can determine the end of the month by comparing the current month of the date and the month of the prior date.  If they are different, then you know you are sitting on the first trading day of the new month.  You can then reach back and access the total equity as of yesterday.  If you want you can also track the change in month equity.  Here is the indicator code in EasyLanguage.

``````// Non plotting indicator that needs to be applied to
// a chart that also has a strategy applied
// one that produces a number of trades

vars: monthlyEqu(0),priorMonthEqu(0);
vars: totalEquity(0);

if month(date) <> month(date[1]) then
begin
monthlyEqu = totalEquity[1] - priorMonthEqu;
priorMonthEqu = totalEquity[1];
print(getSymbolName,",",month(date[1]):2:0,"-",year(date[1])+1900:4:0,",",monthlyEqu,",",totalEquity);
end;
totalEquity = i_ClosedEquity; ``````
Indicator that exports the EOM equity values

The interesting part of this code is the print statement.  You can use the month and year  functions to extract the respective values from the date bar array.  I use a formatted print to export the month and year without decimals.  Remember 2021 in TradeStation is represented by 121 and all you have to do is add 1900 to get a more comfortable value.  The month output is formatted with :2:0 and the year with :4:0.  The first value in the format notation informs  the computer to allow at least 2 or 4 values for the month  and the year respectively.  The second value following the second colon informs the computer that you do not want any decimals values (:0).  Next insert the indicator into all the charts in your workspace.  Here’s a snippet of the output on one market.  I tested this on six markets and this data was the output generated.  Some of the markets were interspersed and that is okay.  You may have a few months of @EC and then a few months of @JY and then @EC again.

``````@EC, 7-2008,-5106.24,-5106.24
@EC, 8-2008,   0.00,-5106.24
@EC, 9-2008,   0.00,-5106.24
@EC,10-2008,   0.00,-5106.24
@EC,11-2008,   0.00,-5106.24
@EC,12-2008,25156.26,20050.02
@EC, 1-2009,   0.00,20050.02
@EC, 2-2009,   0.00,20050.02
@EC, 3-2009,   0.00,20050.02
@EC, 4-2009,   0.00,20050.02
@EC, 5-2009,   0.00,20050.02
@EC, 6-2009,   0.00,20050.02
@EC, 7-2009,   0.00,20050.02
@EC, 8-2009,   0.00,20050.02
@EC, 9-2009,   0.00,20050.02
@EC,10-2009,   0.00,20050.02
@EC,11-2009,   0.00,20050.02
@EC,12-2009,7737.51,27787.53
@EC, 1-2010,   0.00,27787.53
@EC, 2-2010,   0.00,27787.53
@EC, 3-2010,   0.00,27787.53
@EC, 4-2010,   0.00,27787.53
@EC, 5-2010,   0.00,27787.53
@EC, 6-2010,   0.00,27787.53
@EC, 7-2010,   0.00,27787.53
@EC, 8-2010,   0.00,27787.53
@EC, 9-2010,6018.76,33806.29``````
Indicator output

Part 2: Getting the Data into Excel

If you use this post’s EXCEL workbook with the macro you may need to tell EXCEL to forget any formatting that might already be inside the workbook.  Open my workbook and beware that it will inform/warn you that a macro is located inside and that it could be dangerous.  If you want to go ahead and enable the macro go ahead.  On Sheet1 click in A1 and place the letter “G”.  Then goto the Data Menu and then the Data Tools section of the ribbon and click Text to Columns.  A dialog will open and ask you they type of file that best describes your data.  Click the Delimited radio button and then next.  You should see a dialog like this one.

Now copy all of the data from the Print-Log and paste it into Column A.  If all goes right everything will be dumped in the appropriate rows and in a single column.

First select column A and  go back to the Data Menu and the Data Tools on the Ribbon.  Select Delimited and hit next,  Choose comma because we used commas to separate our values.  Click Next again.  Eventually you will get to this dialog.

If you chose Date format for column 2 with MDY, then it will be imported into the appropriate column in a date format.  This makes charting easier.  If all goes well then you will have four columns of data –  a column for Symbol, Date, Delta-EOM and EOM.  Select column B and right click and select Format Cells.

Once you format the B column in the form of  MM-YYYY you will have a date like 9-2007, 10-2007, 11-2007…

Running the VBA Macro/Script

On the Ribbon in EXCEL goto the Developer Tab.  If you don’t see it then you will need to install it.  Just GOOGLE it and follow their instructions.  Once on the Develop Tab goto the Code category and click the Visual Basic Icon.  Your VBA IDE will open and should like very similar to this.

If all goes according to plan after you hit the green Arrow for the Run command your spreadsheet should like similar to this.

The Date column will be needed to be reformatted again as Date with Custom MM-YYYY format.  Now copy the table by highlighting the columns and rows including the headings and then goto to Insert Menu and select the Charts category.

This is what you should get.

This is the output of the SuperTurtle Trading system tested on the currency sector from 2007 to the present.

VBA Code

I have become very spoiled using Python as it does many things for you by simply calling a function.  This code took my longer to develop than I thought it would, because I had to back to the really old school of programming (really wanted to see if I could do this from scratch) to get this done.  You could of course eliminate some of my code by calling spread sheet functions from within EXCEL.  I went ahead and did it the brute force way just to see if I could do it.

``````Sub combineEquityFromTS()

'symbol, date, monthlyEquity, cumulativeEquity - format
'create arrays to hold each column of data
'use nested loops to do all the work
'brute force coding, no objects - just like we did in the 80s

Dim symbol(1250) As String
Dim myDate(1250) As Long
Dim monthlyEquity(1250) As Double
Dim cumulativeEquity(1250) As Double

'read the data from the cells
dataCnt = 1
Do While Cells(dataCnt, 1) <> ""
symbol(dataCnt) = Cells(dataCnt, 1)
myDate(dataCnt) = Cells(dataCnt, 2)
monthlyEquity(dataCnt) = Cells(dataCnt, 3)
cumulativeEquity(dataCnt) = Cells(dataCnt, 4)
dataCnt = dataCnt + 1
Loop
dataCnt = dataCnt - 1

'get distinct symbolNames and use as headers
For i = 2 To dataCnt - 1
If symbol(i) <> symbol(i + 1) Then
newSymbol = True
For j = 1 To numSymbolheadings
If symbol(i + 1) = symbolHeadings(j) Then
newSymbol = False
End If
Next j
If newSymbol = True Then
End If
End If
Next i

'Remove duplicate months in date array
'have just one column of month end dates

Cells(2, 7) = myDate(1)
dispRow = 2
numMonths = 1
i = 1
Do While i <= dataCnt
foundDate = False
For j = 1 To numMonths
If myDate(i) = Cells(j + 1, 7) Then
foundDate = True
End If
Next j
If foundDate = False Then
numMonths = numMonths + 1
Cells(numMonths + 1, 7) = myDate(i)
End If
i = i + 1
Loop
'put symbols across top of table
'put "date" and "cumulative" column headings in proper
'locations too

Cells(1, 7) = "Date"
For i = 1 To numSymbolheadings
Cells(1, 7 + i) = symbolHeadings(i)
Next i

Cells(1, 7 + numSymbols + 1) = "Cumulative"
'now distribute the monthly returns in their proper
'slots in the table
dispRow = 2
dispCol = 7
For i = 1 To numSymbols
For j = 2 To numMonths + 1
For k = 1 To dataCnt
foundDate = False
If symbol(k) = symbolHeadings(i) And myDate(k) = Cells(j, 7) Then
Cells(dispRow, dispCol + i) = cumulativeEquity(k)
dispRow = dispRow + 1
Exit For
End If
'for later use
'If j > 1 Then
'    If symbol(k) = symbolHeadings(i) And myDate(k) < Cells(j, 7) And myDate(k) > Cells(j - 1, 7) Then
'        dispRow = dispRow + 1
'    End If
'End If
Next k
Next j
dispRow = 2
Next i
'now accumulate across table and then down
For i = 1 To numMonths
cumulative = 0
For j = 1 To numSymbols
cumulative = cumulative + Cells(i + 1, j + 7)
Next j
Cells(i + 1, 7 + numSymbols + 1) = cumulative
Next i
End Sub``````

This a throwback to the 80s BASIC on many of the family computers of that era.  If you are new to VBA you can access cell values by using the keyword Cells and the row and column that points to the data.   The first thing you do is create a Module named combineEquityFromTS and in doing so it will create a Sub combineEquityFromTS() header and a End Sub footer.  All of your code will be squeezed between these two statements.  This code is ad hoc because I just sat down and started coding without much forethought.

Use DIM to Dimension an Arrays

I like to read the date from the cells into arrays so I can manipulate the data internally.  Here I create five arrays and then loop through column one until there is no longer any data.  The appropriate arrays are filled with their respective data from each row.

Once the data is in arrays we can start do parse it.  The first thing I want is to get the symbolHeadings or markets.  I know the first row has a symbol name so I go ahead and put that into the symbolHeadings array.  I kept track of the number of rows in the data with the variable dataCnt.  Here I use  nested loops to work my way down the data and keep track of new symbols as I encounter them.  If the symbol changes values, I then check my list of stored symbolHeadings and if the new symbol is not in the list I add it.

Since all the currencies will have the same month values I wanted to compress all the months into a single discrete month list.  This isn’t really all that necessary since we could have just prefilled the worksheet with monthly date values going back to 2007.  This algorithm is similar to the one that is used to extract the different symbols.  Except this time, just for giggles, I used a Do While Loop.

As well as getting values from cells you can also put values into them.  Here I run through the list of markets and put them into Cells(1, 7+i).  When working with cells you need to make sure you get the offset correct.  Here I wanted to put the market names in Row A and Columns: H, I, J, K, L, M.

• Column H = 8
• Column I = 9
• Column J = 10
• Column K =11
• Column L = 12
• Column M = 13

Cells are two dimensional arrays.  However if you are going to use what is on the worksheet make sure you are referencing the correct data.  Here I introduce dispRow and dispCol as the anchor points where the data I need to reference starts out.

Here I first parse through each symbol and extract the EOM value that matches the symbol name and month-year value.  So if I am working on @EC and I need the EOM for 07-2010, I fist loop through the month date values and compare the symbol AND myDate (looped through with another for-loop) with the month date values.  If they are the same then I dump the value in the array on to the spreadsheet.  And yes I should have used this:

For j = dispRow to numMonths-1