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; 

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

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.

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.

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()

    'read data from columns
    '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 symbolHeadings(20) 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
    symbolHeadings(1) = symbol(1)
    numSymbolheadings = 1
    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
                numSymbolheadings = numSymbolheadings + 1
                symbolHeadings(numSymbolheadings) = symbol(i + 1)
            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
       
    numSymbols = numSymbolheadings
    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

Instead of –

For j = 2 to numMonths-1

Keeping arrays in alignment with Cells can be difficult.  I have a hybrid approach here.  I will clean this up later and stick just with arrays and only use Cells to extract and place data.  The last thing you need to do is sum each row up and store that value in the Cumulative column.

Conclusion

This was another post where we relied on another application to help us achieve our objective.   If you are new to EXCEL VBA (working with algorithms that generate trades) you can find out more in my Ultimate Algorithmic Trading System Toolbox book.  Even if you don’t get the book this post should get you started on the right track.

Excel Workbooks – One Empty with Macro and one Filled With this Post Data.