Category Archives: VBA for Excel

Algorithmic Trading Commentary, Python, TradingSimula18, VBA for Excel

Is your Big Point Value right?

June 14, 2024 George Pruitt Leave a comment

Thinking of using futures data from a vendor such as CSI, Pinnacle, Norgate…?

You would be surprised how many quants still use Excel or they own homegrown back-testing software. Determining trade execution is simple. On a stop order, all you need to do is see if today’s bar is higher than the prior bars calculated trade signal – right. And if it is then a buy stop order is executed and the calculated signal price. Store the entry price and when the exit occurs do the math to calculate the profit or loss. Before you can do this, you must know two very important contract specs on the futures data you are testing:

big point value – I will explain how to calculate this a little later
minimum tick move

That’s it. You don’t need to know anything beyond this to properly calculate trade signals and trade P and L. As long as you store this information for each futures/commodity market you are testing, then you will be good to go. Many testing platforms store more information such as contract size, trading hours, expiration date and several other contract specs. You don’t need to know this information if you all you are interested is proper signal placement and accurate trade accounting. Once you set the big point value and minimum tick move you are good to go, right? Yes, if you stick with the same data vendor. I kid you not. Data vendors often quote the same exact market with different decimal places. If you look up a sugar quote at the CME website, you will see it quoted like 0.1908. If you look it up on TradeStation it is quoted as 19.08. It’s the same size contract 112,000 pounds, but if you use the big point value at the CME, it will not produce accurate calculations on the TradeStation quote. You can’t use the contract size of 112,000 pounds to help with the math either. You have to delve into how each data vendor quotes their data and this will impact the big point move and minimum tick move. Most vendors are gracious enough to provide a futures dictionary with these specs. But it is wise to know how to do this by hand as well.

If you had to take a test, could you calculate the profit or loss from a trade in soybeans, in sugar or in euro currency?

Are you kidding me this is super simple. Just program the strategy in TradeStation or Amibroker or Multicharts or TradingSimula-18. I took the NFA Series 3 test more than two decades ago, and this type of question was on the test. The following information was given:

contract size – 5000 bushels
minimum tick or move – 1/4th of a cent or penny
long entry price 8.32’2 and sell exit price 8.48’6 (per bushel)

The entry and exit price may look a little funny. The entry price is eight dollars and 32 1/4 cents. Many still quote soybeans in 1/8ths. So, 2 X 1/8th = 1/4th. For fun let’s calculate the notional value of the entry and exit prices. The notional value of a soybean contract at the entry price is $8.3225 * 5000 = $41,612.50. As you know futures are highly leveraged and you can control this quantity of soybeans for a small percentage of the notional value (margin.) Now the exit price is $8.4875 (6*1/8th = 3/4th of a cent) and the notional value of the contract at exit is $8.4875 * 5000 = $42,437.5. The result of the trade was $42,437.50 – $41,612.50 = $825. Or you could simply multiple the difference between entry and exit by 5000. This would $0.165 * 5000. This makes perfect sense, but as a broker it was difficult to keep the contract size of a market and its minimum tick move in your memory. Well, if you did it long enough it wasn’t that difficult. You can reduce the math down to one easy to remember value and quickly do the math in your head. The concept of the big point move allows for this. If you download your data from Pinnacle or CSI the price of soybeans is usually quoted like this: 848.75. This would be eight hundred and 48.75 cents. The big point move is the amount of dollars required to lift (or drop) the first digit to the left of the decimal by one. The first digit to the left of the decimal in beans is a penny or one cent. A one cent move in beans is 5000 * $0.01 or $50. Going back to our trade example 848.75 – 832.25 = 16.5 – multiply this by $50 you get $825. You can also derive the minimum move dollar value too. If the minimum move is 1/4th of a cent, then you can multiply 5000 * $.0025 and this yields $12.5.

Why is this important to know?

You should know this if you are trading the market, period. Also, if you are a quant and are using some back testing software that requires you to set up the database outside the purview of the back-tester, then these values must be known, and must also be accurate. Since TradeStation integrates its own data, you don’t have to worry about this. But if you want to use a database from Pinnacle, BarChart, CSI or Norgate, then you have to take the time to set this up, right off the bat. I feel like the leading data vendors, provide accurate data. But there are differences from one vendor to another. Not all data vendors use the same number of decimal places in their market quotes, so you must be able to determine the big point value and minimum move from the data. You can do all the math to determine profit and loss from the big point value. Here is a snapshot of a few markets in the TS-18 dataMasterPinnacle and the TS-18 dataMasterCSI text files. Like I said, all vendors will provide this information for you, so you can set your database properly. Some back-testing platforms require more contract specs, but TS-18 just needs this information to carry out accurate calculations. Here TS-18 wants to know the symbol, big point value, minimum tick move, and market name.

Pinnacle Data
AN,1000,0.01,Aus.Dol
ZL,600,0.01,BeanOil
BN,625,0.01,B.Pound
ZU,1000,0.01,Crude
ZC,50,0.25,Corn
CC,10,1,Cocoa
CL,1000,0.01,Crude
CN,1000,0.01,Can.Dol
CT,500,0.01,Cotton
FN,1250,0.005,EuroCur


CSI Data
AD,100000,0.0001,Aus.Dol
BO,600,0.01,BeanOil
BP,62500,0.0001,B.Pound
CL,1000,0.01,Crude
C2,50,0.25,Corn
C_,50,0.25,Corn
CC,10,1,Cocoa
CD,100000,0.0001,Can.Dol
CL,1000,0.01,Crude
CT,500,0.01,Cotton
CU,125000,0.00005,EuroCur
EC,125000,0.00005,EuroCur

Other than the symbol names the values for each symbol are very similar. Most data vendors, including CSI quote the Euro currency like this:

1.08735 and a move to 1.08740 = 0.00005 *$125,000 = $6.25

But Pinnacle data quotes it like this:

108.735 and a move to 108.740 = 0.005 * $1,250 = $6.25

The size of the contract isn’t different here. It is the quote that is different. The big point value is tied to the contract size and the format of the quote. This is why knowing the big point value is so important. If you don’t set up the contract specifications correctly, then you will receive inaccurate results.

One more test in sugar – from the CME website

contract size – 112,000 pounds
minimum tick or move – $0.0001
long entry price $0.1910 and sell exit price $0.1980 (per pound)
big point value = $112,000
$0.0070 * 112,000 = $784

From TradeStation

contract size – 112,000 pounds
minimum tick or move – $0.01
long entry price $19.10 and sell exit price $19.80 (per pound??)
big point value = $1,120
$0.70 * 1,120 = $784

Most fractional quotes are delivered in decimal format and the also very important minimum tick move.

The interest rate futures have minimum tick moves ranging from 1/256ths to 1/32nds. Most vendors will give you a quote like 120.03125 for the bonds. If you test in TradersStudio or Excel or any other platform where the data is not integrated and are using CSI (or any other vendor), then you must force your calculated prices to fall on an even tick. Assume you do a calculation to buy a bond future at 120.022365895. If you don’t take the minimum tick move into considerations, you might be filled at this theoretical price. In reality you should be filled on an even tick at 120.03125. This is worse but realistic fill. You could create what you think is a really awesome strategy where you are shaving off the price on every trade – getting a better fill on every trade.

Thinking of purchasing and using back-testing software or Excel and data from a data vendor?

Become your own quant and do this. You will learn a lot about algorithm testing and development. But first things first. Get your database set up according to your data vendor. Once this chore is complete, testing becomes smooth sailing. I provide the databases for Pinnacle and CSI with TS-18. Other software provides these as well, and a way to create your own databases.

EasyLanguage, EasyLanguage Indicator, EasyLanguage Snippets, EasyLanguage Tutorial, EasyLanguage Tutorial, Free EasyLanguage, Must Know, Must Know EasyLanguage, VBA for Excel

Using EXCEL VBA to Combine Equity Curves from TradeStation

April 5, 2022 George Pruitt Leave a comment

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.

The fields will be dumped into 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.

Make sure you use Date [MDY] for the second column of data.

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.

Clean Up column B by formatting cells and customizing the date format

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.

VBA IDE [integrated development environment]

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

EOM table with Months as Rows and Individual Market EOMs as columns

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.

Across and then Down to sum accumulated monthly returns

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.