Category Archives: EasyLanguage Indicator

The Day Trading Edition of Easing Into EasyLanguage Now Available!

Learn Pyramiding, Scaling In or Out, Camarilla, Break Out and Clear Out techniques in day trading environment.

Get Your Copy Now!
  • Chapter 1 – Open Range Break Out and other Sundries
  • Chapter 2 – Improving Our ORBO with Pattern Recognition
  • Chapter 3 – Time based Breakout
  • Chapter 4 – Trading After the Lunch Break
  • Chapter 5 – Extending Our Break Out Technology With Pyramiding and Scaling Out
  • Chapter 6 – Scaling Out of Pyramided and multiple Positions
  • Chapter 7 – Pyramania
  • Chapter 8 – Introduction to Zone Trading with the Camarilla
  • Chapter 9 – Day Trading Templates Appendix
A Chart from Chapter 8 – Camarilla Equation

Take a look at this function that spans the entire search space of all the combinations of the days of the week.

inputs: optimizeNumber(numericSimple);
arrays: dowArr[31]("");
vars: currDOWStr(""),daysOfWeek("MTWRF");

//Once
//begin
print(d," ",t," assigning list ");
dowArr[1]="MTWRF";dowArr[2]="MTWR";
dowArr[3]="MTWF";dowArr[4]="MTRF";
dowArr[5]="MWRF";dowArr[6]="TWRF";
dowArr[7]="MTW";dowArr[8]="MTR";
dowArr[9]="MTF";dowArr[10]="MWR";
dowArr[11]="MWF";dowArr[12]="MRF";
dowArr[13]="TWR";dowArr[14]="TWF";
dowArr[15]="TRF";dowArr[16]="WRF";
dowArr[17]="MT";dowArr[18]="MW";
dowArr[19]="MR";dowArr[20]="MF";
dowArr[21]="TW";dowArr[22]="TR";
dowArr[23]="TF";dowArr[24]="WR";
dowArr[25]="WF";dowArr[26]="RF";
dowArr[27]="M";dowArr[28]="T";
dowArr[29]="W";dowArr[30]="R";
dowArr[31]="F";
//end;

OptimizeDaysOfWeek = False;
if optimizeNumber > 0 and optimizeNumber < 32 Then
Begin
currDOWStr = midStr(daysOfWeek,dayOfWeek(d),1);
if inStr(dowArr[optimizeNumber],currDOWStr) <> 0 Then
OptimizeDaysOfWeek = True;
end;
All The Permutations of the Days of the Week

 

Trade after the Lunch Break in Apple

Williams Awesome Oscillator Indicator and Strategy

Awesome Oscillator

This is a very simple yet telling analysis.  Here is the EasyLanguage:

[LegacyColorValue = true]; 



Vars:oscVal(0),mavDiff(0);


mavDiff= Average((h+l)/2,5)-Average((h+l)/2,34);
oscVal = mavDiff - average(mavDiff,5);

Plot3( 0, "ZeroLine" ) ;

if currentbar>=1 then
if oscVal>oscVal[1] then plot1(mavDiff,"+AO")
else plot2(mavDiff,"-AO")
Williams Awesome Oscillator Source Code

And here is what it looks like:

Williams Awesome Oscillator

The code reveals a value that oscillates around 0.  First calculate the difference between the 5-day moving average of the daily midPoint (H+ L)/2 and the 34-day moving average of the midPoint.    A positive value informs us that the market is in a bullish stance whereas a negative represents a bearish tone.  Basically, the positive value is simply stating the shorter-term moving average is above the longer term and vice versa. The second step in the indicator calculation is to subtract the 5-day moving average of the differences from the current difference.  If the second calculation is greater than the prior day’s calculation, then plot the original calculation value as green (AO+).  If it is less (A0-), then paint the first calculation red.  The color signifies the momentum between the current and the five-day smoothed value.

Awesome Indicator Strategy

mavDiff= Average((h+l)/2,5)-Average((h+l)/2,34);
oscVal = mavDiff - average(mavDiff,5);


mp = marketPosition;
mavUp = countIf(mavDiff > 0,30);
mavDn = countIf(mavDiff < 0,30);

value1 = countIf(oscVal > oscVal[1],10);

oscRatio = value1/10*100;
The beginning of an AO based Strategy

Here I am using the very handy countIf function.  This function will tell you how many times a Boolean comparison is true out of the last N days.  Her I use the function twice, but I could have replaced the second function call with mavDn = 30 – mavUp.  So, I am counting the number of occurrences of when the mavDiff is positive and negative over the past 30-days.  I also count the number of times the oscVal is greater than the prior oscVal.  In other words, I am counting the number of green bars.  I create a ratio between green bars and 10.  If there are six green bars, then the ratio equals 60% This indicates that the ratio of red bars would be 40%.  Based on these readings you can create trade entry directives.

if canShort and mavUp > numBarsAbove and mavDiff > minDiffAmt and oscRatio >= obRatio then
sellShort next bar at open;

if canBuy and mavDn > numBarsBelow and mavDiff < -1*minDiffAmt and oscRatio <= osRatio Then
buy next bar at open;
Trade Directives

If the number of readings out of the last 30 days is greater than numBarsAbove and mavDiff is of a certain magnitude and the oscillator ratio is greater than buyOSCRatio, then you can go short on the next open.  Here we are looking for the market to converge.  When these conditions are met then I think the market is overbought.  You can see how I set up the long entries.  As you can see from the chart it does a pretty good job.  Optimizing the parameters on the crude oil futures yielded this equity curve.

Too few trades!

Not bad, but not statistically significant either.  One way to generate more trades is to install some trade management such as protective stop and profit objective.

Using wide stop and profit objective.

Using a wide protective stop and large profit objective tripled the number of trades.  Don’t know if it is any better, but total performance was not derived from just a couple of trades.  When you are working with a strategy like this and overlay trade management you will often run into this situation.

Exiting while conditions to short are still turned on!

Here we either get stopped out or take a profit and immediately reenter the market.  This occurs when the conditions are still met to short when we exit a trade.  The fix for this is to determine when an exit has occurred and force the entry trigger to toggle off.  But you have to figure out how to turn the trigger back on.  I reset the triggers based on the number of days since the triggers were turned off – a simple fix for this post.  If you want to play with this strategy, you will probably need a better trigger reset.

I am using the setStopLoss and setProfitTarget functionality via their own strategies – Stop Loss and Profit Target.  These functions allow exit on the same as entry, which can be useful.  Since we are executing on the open of the bar, the market could definitely move either in the direction of the stop or the profit.  Since we are using wide values, the probability of both would be minimal.  So how do you determine when you have exited a trade.  You could look the current bar’s marketPosition and compare it with the prior bar’s value, but this doesn’t work 100% of the time.  We could be flat at yesterday’s close, enter long on today’s open and get stopped out during the day and yesterday’s marketPosition would be flat and today’s marketPosition would be flat as well.  It would be as if nothing occurred when in fact it did.

Take a look at this code and see if it makes sense to you.

if mp[1] = 1 and totalTrades > totTrades then
canBuy = False;

if mp[1] = -1 and totalTrades > totTrades then
canShort = False;

if mp[1] = 0 and totalTrades > totTrades then
Begin
if mavDiff[1] < 0 then canBuy = False;
if mavDiff[1] > 0 then canShort = False;
end;

totTrades = totalTrades;
Watch for a change in totalTrades.

If we were long yesterday and totalTrades (builtin keyword/function) increases above my own totTrades, then we know a trade was closed out – a long trade that is.  A closed out short position is handled in the same manner.  What about when yesterday’s position is flat and totalTrades increases.  This means an entry and exit occurred on the current bar.  You have to investigate whether the position was either long or short.  I know I can only go long when mavDiff is less than zero and can only go short when mavDiff is greater than zero.  So, all you need to do is investigate yesterday’s mavDiff  to help you determine what position was entered and exited on the same day.  After you determine if an exit occurred, you need to update totTrades with totalTrades.  Once you determine an exit occurred you turn canBuy or canShort off.  They can only be turned back on after N bars have transpired since they were turned off.   I use my own barsSince function to help determine this.

if  not(canBuy) Then
if barsSince(canBuy=True,100,1,0) = 6 then
canBuy = True;
if not(canShort) Then
if barsSince(canShort=True,100,1,0) = 6 then
canShort = True;

Complete strategy code:

inputs:numBarsAbove(10),numBarsBelow(10),buyOSCRatio(60),shortOSRatio(40),minDiffAmt(2),numBarsTrigReset(6);
Vars:canBuy(True),canShort(True),mp(0),totTrades(0),oscVal(0),mavDiff(0),oscRatio(0),mavUp(0),mavDn(0),stopLoss$(1000);


mavDiff= Average((h+l)/2,5)-Average((h+l)/2,34);
oscVal = mavDiff - average(mavDiff,5);


mp = marketPosition;
mavUp = countIf(mavDiff > 0,30);
mavDn = countIf(mavDiff < 0,30);

value1 = countIf(oscVal > oscVal[1],10);

oscRatio = value1/10*100;



if not(canBuy) Then
if barsSince(canBuy=True,100,1,0) = numBarsTrigReset then
canBuy = True;
if not(canShort) Then
if barsSince(canShort=True,100,1,0) = numBarsTrigReset then
canShort = True;

if mp[1] = 1 and totalTrades > totTrades then
canBuy = False;

if mp[1] = -1 and totalTrades > totTrades then
canShort = False;

if mp[1] = 0 and totalTrades > totTrades then
Begin
if mavDiff[1] < 0 then canBuy = False;
if mavDiff[1] > 0 then canShort = False;
end;


if canShort and mavUp > numBarsAbove and mavDiff > minDiffAmt and oscRatio >= buyOSCRatio then
sellShort next bar at open;

if canBuy and mavDn > numBarsBelow and mavDiff < -1*minDiffAmt and oscRatio <= shortOSRatio Then
buy next bar at open;

totTrades = totalTrades;
Inputs used to generate the equity curve.

Reentry of Long at better price

How to reenter at a better price after a stop loss

A reader of this blog wanted to know how he could reenter a position at a better price if the first attempt turned out to be a loser.  Here I am working with 5 minute bars, but the concepts work for daily bars too.  The daily bar is quite a bit easier to program.

ES.D Day Trade using 30 minute Break Out

Here we are going to wait for the first 30 minutes to develop a channel at the highest high and the lowest low of the first 30 minutes.  After the first 30 minutes and before 12:00 pm eastern, we will place a buy stop at the upper channel.  The initial stop for this initial entry will be the lower channel.  If we get stopped out, then we will reenter long at the midpoint between the upper and lower channel.  The exit for this second entry will be the lower channel minus the width of the upper and lower channel.

Here are some pix.  It kinda, sorta worked here – well the code worked perfectly.   The initial thrust blew through the top channel and then immediately consolidated, distributed and crashed through the bottom channel.  The bulls wanted another go at it, so they pushed it halfway to the midpoint and then immediately the bears came in and played tug of war.  In the end the bulls won out.

Initial Break Out Failed. But 2nd Entry made a little.

Here the bulls had initial control and then the bears, and then the bulls and finally the bears tipped the canoe over.

We gave it the old college try didn’t we fellas.

This type of logic can be applied to any daytrade or swing trade algorithm.  Here is the code for the strategy.

//working with 5 minute bars here
//should work with any time frame

input:startTradeTime(0930),numOfBarsHHLL(6),stopTradeTime(1200),maxLongEntriesToday(2);
vars: startTime(0),endTime(0),
barCountToday(0),totTrades(0),mp(0),longEntriesToday(0),
periodHigh(-99999999),periodLow(99999999);

startTime = sessionStartTime(0,1);
endTime = sessionStartTime(0,1);


if t = calcTime(startTime,barInterval) Then
begin
periodHigh = -99999999;
periodLow = 99999999;
barCountToday = 1;
totTrades = totalTrades;
end;

if barCountToday <= numOfBarsHHLL Then
Begin
periodHigh = maxList(periodHigh,h);
periodLow = minList(periodLow,l);
end;

barCountToday = barCountToday + 1;

longEntriesToday = totalTrades - totTrades;

mp = marketPosition;
if t <= stopTradeTime and barCountToday > numOfBarsHHLL Then
Begin
if longEntriesToday = 0 then
buy("InitBreakOut") next bar at periodHigh + minMove/priceScale stop;
if longEntriesToday = 1 Then
buy("BetterBuy") next bar at (periodHigh+periodLow)/2 stop;
end;

if mp = 1 then
if longEntriesToday = 0 then sell("InitXit") next bar at periodLow stop;
if longEntriesToday = 1 then sell("2ndXit") next bar at periodLow - (periodHigh - periodLow) stop;

SetExitOnClose;

The key concepts here are the time constraints and how I count bars to calculate the first 30 – minute channel.  I have had problems with EasyLanguages’s entriesToday function, so I like how I did it here much better.  On the first bar of the day, I set my variable totTrades to EasyLanguages built-in totalTrades (notice the difference in the spelling!)  The keyword TotalTrades is immediately updated when a trade is closed out.  So, I simply subtract my totTrades (the total number of trades at the beginning of the day) from totalTrades.  If totalTrades  is incremented (a trade is closed out) then the difference between the two variables is 1.  I assign the difference of these two values to longEntriesToday.  If longEntriesToday = 1, then I know I have entered long and have been stopped out.  I then say, OK let’s get back long at a better price – the midpoint.  I use the longEntriesToday variable again to determine which exit to use.  With any luck the initial momentum that got us long initially will work its way back into the market for another shot.

It’s Easy to Create  a Strategy Based Indicator

Once you develop a strategy, the indicator that plots entry and exit levels is very easy to derive.  You have already done the math – just plot the values.  Check this out.

//working with 5 minute bars here
//should work with any time frame

input:startTradeTime(0930),numOfBarsHHLL(6);
vars: startTime(0),endTime(0),
barCountToday(0),periodHigh(-99999999),periodLow(99999999);

startTime = sessionStartTime(0,1);
endTime = sessionStartTime(0,1);


if t = calcTime(startTime,barInterval) Then
begin
periodHigh = -99999999;
periodLow = 99999999;
barCountToday = 1;
end;

if barCountToday <= numOfBarsHHLL Then
Begin
periodHigh = maxList(periodHigh,h);
periodLow = minList(periodLow,l);
end;

if barCountToday < numOfBarsHHLL Then
begin
noPlot(1);
noPlot(2);
noPlot(3);
noPlot(4);
End
Else
begin
plot1(periodHigh,"top");
plot2((periodHigh+periodLow)/2,"mid");
plot3(periodLow,"bot");
plot4(periodLow - (periodHigh - periodLow),"2bot");
end;

barCountToday = barCountToday + 1;

So, that’s how you do it.  You can use this code as a foundation for any system that wants to try and reenter at a better price.  You can also use this code to develop your own time based break out strategy.  In my new book, Easing Into EasyLanguage – the Daytrade Edition I will discuss topics very similar to this post.

 

 

 

The EasyLanguage Function: A Thing of Beauty

The concept of Scope when it comes to a programming language can get very confusing very quickly. If you are new to programming and you think Scope is a mouthwash, no worries here. I will give a full explanation of Scope in terms of programming with the help of ChatGPT. Its response is italicized.

I recently chatted with ChatGPT about Scope in terms of the Python programing language.

In Python, the scope of a variable refers to the region of the program where that variable can be accessed or referenced. There are two types of scopes in Python: global scope and local scope.

Here’s an example that demonstrates the concept of scope in Python:

# global variable
x = 10

def my_func():
# local variable
y = 5
print("x inside my_func:", x) # x is accessed from the global scope
print("y inside my_func:", y) # y is accessed from the local scope

my_func()

print("x outside my_func:", x) # x is accessed from the global scope
print("y outside my_func:") # y is not defined in the global scope, so this will raise a NameError
Example of Scope as demonstrated by Chat-GPT

In this example, we define a global variable x with a value of 10.

We then define a function my_func that has a local variable y with a value of 5. Inside the function, we print the values of x and y. The variable x is accessed from the global scope, which means it can be referenced from anywhere in the program.

The variable y, on the other hand, is only accessible within the function, which is the local scope. Outside the function, we print the value of x again, which is still accessible from the global scope. However, when we try to print the value of y, we get a NameError because y is not defined in the global scope.

Okay that was pretty formal. In simpler terms, a variable declared inside a function can only be seen inside that function, unless it is declared globally. Another thing about Scope, is that the variables have amnesia. Once you step out of the function the variable forgets what it was, and you can’t refer to its prior value upon return to the function.

Unless you are an EasyLanguage function. Scope is still constrained by an EasyLanguage function, because it is a separate piece of code held within the library of Analysis Techniques. Most of the time you can’t see what’s in the function unless you open it with the ELEditor. However, the variables that are defined inside the function do not suffer from amnesia. If you need to refer to a prior value of a locally declared variable, you can. This type of function is what EasyLanguage calls a Serial function. The only downside to this function is it slows processing down quite a bit.

Okay. To make a long story short I wanted to show the magic of EasyLanguage function that I have been working with on a project. This project includes some of Ehlers’ cycle analysis functions. The one I am going to discuss today is the HighRoof function – don’t worry I am not going to go into detail of what this function does. If you want to know just GOOGLE it or ask ChatGPT. I developed a strategy that used the function on the last 25 days of closing price data. I then turned around and fed the output of the first pass of the HighRoof function right back into the HighRoof function. Something similar to embedding functions.

doubleSmooth = average(average(c,20),20);

Sort of like a double smoothed moving average. After I did this, I started thinking does the function remember the data from its respective call? The first pass used closing price data, so its variables and their history should be in terms of price data. The second pass used the cyclical movements data that was output by the initial call to the HighRoof function. Everything turned out fine, the function remembered the correct data. Or seemed like it did. This is how you learn about any programming language – pull out your SandBox and do some testing. First off, here is my conversion of Ehlers’ HighRoof function in EasyLanguage.

//Ehlers HiRoof
Inputs: dataSeries(numericseries),cutPeriod(Numeric);

Vars: a1(0), b1(0), c1(0), c2(0), c3(0), Filt(0), Filt2(0),
alpha1(0),oneMinusAlpha1(0), highPass(0),myhp(0),degrees(0);
Vars: numTimesCalled(0);

//Highpass filter cyclic components whose periods are shorter than 48 bars

numTimesCalled = numTimesCalled + 1;

print(d," numTimesCalled ",numTimesCalled," highPass[1] ",highPass[1]," highPass[2] ",highPass[2]," highPass[3] ",highPass[3]);
degrees = .707*360 / CutPeriod;

alpha1 = (Cosine(degrees) + Sine(degrees) - 1) / Cosine(degrees);

oneMinusAlpha1 = 1-alpha1;

highPass = square(oneMinusAlpha1/2)*(dataSeries-2*dataSeries[1]+dataSeries[2]) +
2*(oneMinusAlpha1)*highPass[1]-square(oneMinusAlpha1)*highPass[2];



EhlersHighRoof=highPass;
Ehlers High Roof Function

This function requires just two inputs – the data (with a history) and a simple length or cut period. The first input is of type numericSeries and the second input is of type numericSimple. You will see the following line of code

 print(d," numTimesCalled ",numTimesCalled," highPass[1] ",highPass[1]," highPass[2] ",highPass[2]," highPass[3] ",highPass[3]);

This code prints out the last three historic values of the HighPass variable for each function call. I am calling the function twice for each bar of data in the Crude Oil futures continuous contract.

1230206.00 numTimesCalled  494.00 highPass[1]   -0.78 highPass[2]   -0.51 highPass[3]   -0.60
1230206.00 numTimesCalled 494.00 highPass[1] -0.05 highPass[2] -0.02 highPass[3] -0.06
1230207.00 numTimesCalled 495.00 highPass[1] -0.38 highPass[2] -0.78 highPass[3] -0.51
1230207.00 numTimesCalled 495.00 highPass[1] 0.04 highPass[2] -0.05 highPass[3] -0.02
1230208.00 numTimesCalled 496.00 highPass[1] 0.31 highPass[2] -0.38 highPass[3] -0.78
1230208.00 numTimesCalled 496.00 highPass[1] 0.16 highPass[2] 0.04 highPass[3] -0.05
1230209.00 numTimesCalled 497.00 highPass[1] 0.49 highPass[2] 0.31 highPass[3] -0.38
1230209.00 numTimesCalled 497.00 highPass[1] 0.15 highPass[2] 0.16 highPass[3] 0.04
1230210.00 numTimesCalled 498.00 highPass[1] 0.30 highPass[2] 0.49 highPass[3] 0.31
1230210.00 numTimesCalled 498.00 highPass[1] 0.07 highPass[2] 0.15 highPass[3] 0.16
1230213.00 numTimesCalled 499.00 highPass[1] 0.52 highPass[2] 0.30 highPass[3] 0.49
1230213.00 numTimesCalled 499.00 highPass[1] 0.08 highPass[2] 0.07 highPass[3] 0.15
1230214.00 numTimesCalled 500.00 highPass[1] 0.44 highPass[2] 0.52 highPass[3] 0.30
1230214.00 numTimesCalled 500.00 highPass[1] 0.04 highPass[2] 0.08 highPass[3] 0.07
Output of calling HighRoof twice per bar

Starting at the top of the output you will see that on 1230206 the function was called twice with two different sets of data. As you can see the output of the first two lines is of a different magnitude. The first line is approximately an order or magnitude of 10 of the second line. If you go to lines 3 and 4 you will see the highPass[1] of lines 1 and 2 moves to highPass[2] and then onto highPass[3]. I think what happens internally is for every call on per bar basis, the variables for each function call are pushed into a queue in memory. The queue continues to grow for whatever length is necessary and then either maintained or truncated at some later time.

Why Is This So Cool?

In many languages the encapsulation of data with the function requires additional programming. The EasyLanguage function could be seen as an “object” like in object-oriented programming. You just don’t know you are doing it. EasyLanguage takes care of a lot of the behind-the-scenes data management. To do the same thing in Python you would need to create a class of Ehlers Roof that maintain historic data in class members and the calculations would be accomplished by a class method. In the case of calling the function twice, you would instantiate two classes from the template and each class would act independent of each other.

Here is my SandBox for Indicator


Value1 = EhlersHighRoof(close,25);
plot1(Value1,"EhlersHiRoof");
Value2 = EhlersHighRoof(value1,25);
plot2(Value2,"EhlersHiRoof2");
Sandbox Playground for Ehlers Function

 

One last nugget of information. If you are going to be working with trigonometric functions such as Cosine, Sine or Tangent, make sure your arguments are in degrees not radians. In Python, you must use radians.

Can Futures Traders Trust Continuous Contracts? [Part – 2]

Recap from Part -1

I had to wrap up Part -1 rather quickly and probably didn’t get my ideas across, completely.  Here is what we did in Part – 1.

  1. used my function to locate the First Notice Date in crude
  2. used the same function to print out exact EasyLanguage syntax
  3. chose to roll eight days before FND and had the function print out pure EasyLanguage
  4. the output created array assignments and loaded the calculated roll points in YYYMMDD format into the array
  5.  visually inspected non-adjusted continuous contracts that were spliced eight days before FND
  6. appended dates in the array to match roll points, as illustrated by the dip in open interest

Step 6 from above is very important, because you want to make sure you are out of a position on the correct rollover date.  If you are not, then you will absorb the discount between the contracts into your profit/loss when you exit the trade.

Step 2 – Create the code that executes the rollover trades

Here is the code that handles the rollover trades.


...
...
...
...
rollArr[118]=20220314;
rollArr[119]=20220411;
rollArr[120]=20220512;
rollArr[121]=20220613;
rollArr[122]=20220712;
rollArr[123]=20220812;

// If in a position and date + 1900000 (convert TS date format to YYYYMMDD),
// then exit long or short on the current bar's close and then re-enter
// on the next bar's open

if d+19000000 = rollArr[arrCnt] then
begin
condition1 = true;
arrCnt = arrCnt + 1;
if marketPosition = 1 then
begin
sell("LongRollExit") this bar on close;
buy("LongRollEntry") next bar at open;
end;
if marketPosition = -1 then
begin
buyToCover("ShrtRollExit") this bar on close;
sellShort("ShrtRollEntry") next bar at open;
end;

end;
Code to rollover open position

This code gets us out of an open position during the transition from the old contract to the new contract.  Remember our function created and loaded the rollArr for us with the appropriate dates.  This simulation is the best we can do – in reality we would exit/enter at the same time in the two different contracts.  Waiting until the open of the next bar introduces slippage.  However, in the long run this slippage cost may wash out.

Step 3 – Create a trading system with entries and exits

The system will be a simple Donchian where you enter on the close when the bar’s high/low penetrates the highest/lowest low of the past 40 bars.  If you are long, then you will exit on the close of the bar whose low is less than the lowest low of the past 20 bars.  If short, get out on the close of the bar that is greater than the highest high of the past twenty bars.  The first test will show the result of using an adjusted continuous contract rolling 8 days prior to FND

Nice Trade. Around August 2014

This test will use the exact same data to generate the signals, but execution will take place on a non-adjusted continuous contract with rollovers.  Here data2 is the adjusted continuous contract and data1 is the non-adjusted.

Same Trade but with rollovers

Still a very nice trade, but in reality you would have to endure six rollover trades and the associated execution costs.

Conclusion

Here is the mechanism of the rollover trade.

Roll out of old contract and roll into new contract

And now the performance results using $30 for round turn execution costs.

No-Rollovers

No Rollovers?

Now with rollovers

Many more trades with the rollovers!

The results are very close, if you take into consideration the additional execution costs.  Since TradeStation is not built around the concept of rollovers, many of the trade metrics are not accurate.  Metrics such as average trade, percent wins, average win/loss and max Trade Drawdown will not reflect the pure algorithm based entries and exits.  These metrics take into consideration the entries and exits promulgated by the rollovers.  The first trade graphic where the short was held for several months should be considered 1 entry and 1 exit.  The rollovers should be executed in real time, but the performance metrics should ignore these intermediary trades.

I will test these rollovers with different algorithms, and see if we still get similar results, and will post them later.  As you can see, testing on non-adjusted data with rollovers is no simple task.  Email me if you would like to see some of the code I used in this post.

Can Futures Traders Trust Continuous Contracts? [Part – 1]

 Well You Have To, Don’t You?

When I worked at Futures Truth, we tested everything with our Excalibur software.  This software used individual contract data and loaded the entire history (well, the part we maintained) of each contract into memory and executed rollovers at a certain time of the month.  Excalibur had its limitations as certain futures contracts had very short histories and rollover dates had to be predetermined – in other words, they were undynamic.  Over the years, we fixed the short history problem by creating a dynamic continuous contract going back in time for the number of days required for a calculation.  We also fixed the database with more appropriate rollover frequency and dates.  So in the end, the software simulated what I had expected from trading real futures contracts.  This software was originally written in Fortran and for the Macintosh.  It also had limitations on portfolio analysis as it worked its way across the portfolio, one complete market at a time.   Even with all these limitations, I truly thought that the returns more closely mirrored what a trader might see in real time.  Today, there aren’t many, if any, simulation platforms that test on individual contracts.  The main reasons for this are the complexity of the software, and the database management.  However, if you are willing to do the work, you can get close to testing on individual contract data with EasyLanguage.

Step 1 – Get the rollover dates

This is critical as the dates will be used to roll out of one contract and into another.  In this post, I will test a simple strategy on the crude futures.  I picked crude because it rolls every month.   Some data vendors use a specific date to roll contracts, such as Pinnacle data.  In real time trading, I did this as well.  We had a calendar for each month, and we would mark the rollover dates for all markets traded at the beginning of each month.  Crude was rolled on the 11th or 12th of the prior month to expiration.  So, if we were trading the September 2022 contract, we would roll on August 11th.  A single order (rollover spread) was placed to sell (if long) the September contract and buy the October contract at the market simultaneously.  Sometimes we would leg into the rollover by executing two separate orders – in hopes of getting better execution.  I have never been able to find a historic database of when TradeStation performs its rollovers.  When you use the default @CL symbol, you allow TradeStation to use a formula to determine the best time to perform a rollover.  This was probably based on volume and open interest.  TradeStation does allow you to pick several different rollover triggers when using their continuous data.

You can choose type of trigger – (3) Dynamic or (4) Time based.

I am getting ahead of myself, because we can simply use the default @CL data to derive the rollover dates (almost.)  Crude oil is one of those weird markets where LTD (last trade days) occurs before FND (first notice day.)  Most markets will give you a notice before they back up a huge truck and dump a 1000 barrels of oil at your front door.   With crude you have to be Johnny on the spot!  Rollover is just a headache when trading futures, but it can be very expensive headache if you don’t get out in time.  Some markets are cash settled so rollover isn’t that important, but others result in delivery of the commodity.  Most clearing firms will help you unwind an expired contract for a small fee (well relatively small.)  In the good old days your full service broker would give you heads up.  They would call you and say, “George you have to get out of that Sept. crude pronto!”  Some firms would automatically liquidate the offending contract on your behalf – which sounds nice but it could cost you.  Over my 30 year history of trading futures I was caught a few times in the delivery process.   You can determine these FND and LTD from the CME website.  Here is the expiration description for crude futures.

Trading terminates 3 business day before the 25th calendar day of the month prior to the contract month. If the 25th calendar day is not a business day, trading terminates 4 business days before the 25th calendar day of the month prior to the contract month.

You can look this up on your favorite broker’s website or the handy calendars they send out at Christmas.  Based on this description, the Sept. 2022 Crude contract would expire on August 20th and here’s why

  • August 25 is Tuesday
  • August 24 is Monday- DAY1
  • August 21 is Friday – DAY2
  • August 20 is Thursday – DAY3

This is the beauty of a well oiled machine or exchange.  The FND will occur exactly as described.  All you need to do is get all the calendars for the past ten years and find the 25th of the month and count back three business days.  Or if the 25 falls on a weekend count back four business days.  Boy that would be chore, would it not?  Luckily, we can have the data and an  EasyLanguage script do this for us.  Take a look at this code and see if it makes any sense to you.

Case "@CL":
If dayOfMonth(date) = 25 and firstMonthPrint = false then
begin
print(date[3]+19000000:8:0);
firstMonthPrint = true;
end;
If(dayOfMonth(date[1]) < 25 and dayOfMonth(date) > 25 ) and firstMonthPrint = false then
begin
print(date[4]+19000000:8:0);
firstMonthPrint = true;
end;
Code to printout all the FND of crude oil.

I have created a tool to print out the FND or LTD of any commodity futures by examining the date.  In this example, I am using a Switch-Case to determine what logic is applied to the chart symbol.  If the chart symbol is @CL, I look to see if the 25th of the month exists and if it does, I print the date 3 days prior out.  If today’s day of month is greater than 25 and the prior day’s day of month is less than 25, I know the 25th occurred on a weekend and I must print out the date four bars prior.  These dates are FN dates and cannot be used as is to simulate a rollover. You had best be out before the FND to prevent the delivery process.   Pinnacle Date rolls the crude on the 11th day of the prior month for its crude continuous contracts.  I aimed for this day of the month with my logic.  If the FND normally fell on the 22nd of the month, then I should back up either 9 or 10 business days to get near the 11th of the month.   Also I wanted to use the output directly in an EasyLanguage strategy so I modified my output to be exact EasyLanguage.

Case "@CL":
If dayOfMonth(date) = 25 and firstMonthPrint = false then
begin
value1 = value1 + 1;
print("rollArr[",value1:1:0,"]=",date[9]+19000000:8:0,";");
firstMonthPrint = true;
end;
If(dayOfMonth(date[1]) < 25 and dayOfMonth(date) > 25 ) and firstMonthPrint = false then
begin
value1 = value1 + 1;
print("rollArr[",value1:1:0,"]=",date[10]+19000000:8:0,";");
// print(date[4]+19000000:8:0);
firstMonthPrint = true;
end;


// example of output

rollArr[103]=20210312;
rollArr[104]=20210412;
rollArr[105]=20210512;
rollArr[106]=20210614;
rollArr[107]=20210712;
rollArr[108]=20210812;
rollArr[109]=20210913;
rollArr[110]=20211012;
rollArr[111]=20211111;
rollArr[112]=20211210;
rollArr[113]=20220111;
rollArr[114]=20220211;
rollArr[115]=20220314;
rollArr[116]=20220411;
rollArr[117]=20220512;
rollArr[118]=20220610;
rollArr[119]=20220712;
rollArr[120]=20220812;
Code to print our 9 or 10 bars prior to FND in actual EasyLanguage

Now. that I had the theoretical rollover dates for my analysis I had to make sure the data that I was going to use matched up exactly.  As you saw before, you can pick the rollover date for your chart data.   And you can also determine the discount to add or subtract to all prior data points based on the difference between the closing prices at the rollover point.  I played around with the number of days prior to FND and selected non adjusted for the smoothing of prior data.

Actual data I simulated rollovers with.

How did I determine 8 days Prior to First Notice Date?  I plotted different data using a different number of days prior and determined 8 provided a sweet spot between the old and new contract data’s open interest.  Can you see the rollover points in the following chart?  Ignore the trades – these were a beta test.

The Open Interest Valley is the rollover date.

The dates where the open interest creates a valley aligned very closely with the dates I printed out using my FND date finder function.  To be safe, I compared the dates and fixed my array data to match the chart exactly.  Here are two rollover trades – now these are correct.

Using an adjusted continuous contract you would not see these trades.

This post turned out to be a little longer than I thought, so I will post the results of using an adjusted continuous contract with no rollovers, and the results using non-adjusted concatenated contracts with rollovers.  The strategy will be a simple 40/20 bar Donchian entry/exit.  You maybe surprised by the results – stay tuned.

Advanced Topics Edition of Easing Into EasyLanguage – NOW AVAILABLE!

Advanced Edition is now Available

Advanced Topics Cover

The last book in the Easing Into EasyLanguage Series has finally been put to bed.  Unlike the first two books in the series, where the major focus and objective was to introduce basic programming ideas to help get  new EasyLanguages users up to speed, this edition introduces more Advanced topics and the code to develop and program them.

Buy this book to learn how to overcome the obstacles that may be holding you back from developing your ideal Analysis Technique. This book could be thousands of pages long because the number of topics could be infinite. The subjects covered in this edition provide a great cross-section of knowledge that can be used further down the road. The tutorials will cover subjects such as:

  • Arrays – single and multiple dimensions
  • Functions – creation and communicating via Passed by Value and Passed by Reference
  • Finite State Machine – implemented via the Switch-Case programming construct
  • String Manipulation – construction and deconstruction of strings using EasyLanguage functions
  • Hash Table and Hash Index – a data structure(s) that contains unique addresses of bins that can contain N records
  • Using Hash Tables – accessing and storing data related to unique Tokens
  • Token Generation – an individual instance of a type of symbol
  • Seasonality – in depth analysis of the Ruggiero/Barna and Sheldon Knight Universal Seasonal data
  • File Manipulation – creating, deleting and writing to external files
  • Using Projects – organizing Analysis Techniques by grouping support functions and code into a single entity
  • Text Graphic Objects – extracting text from a chart and storing the object information in arrays for later development into a strategy
  • Commitment of Traders Report – TradeStation only (not MultiChart compatible) code. Converting the COT indicator and using the FundValue functionality to develop a trading strategy
  • Multiple Time Frame based indicator – use five discrete time frames and pump the data into a single indicator – “traffic stop light” feel

Once you become a programmer, of any language, you must continually work on honing your craft.  This book shows you how to use your knowledge as building blocks to complete some really cool and advanced topics.

Take a look at this video:

Data Aliasing with Minute Bars

Why It Is Important to Connect Variables with Correct Time Frame

I had a question about data aliasing from a reader of this blog.  Here is the debug code I used in the form of an Indicator.

//vars: myCloseData1(0),myCloseData2(0),
// myRSIData1(0),myRSIData2(0);

vars: myCloseData1(0),myCloseData2(0,data2),
myRSIData1(0),myRSIData2(0,data2);


myCloseData1 = close of data1;
myCloseData2 = close of data2;

myRSIData1 = rsi(close of data1,14);
myRSIData2 = rsi(close of data2,14);


Print(d," ",t," --------------- ");

print(" myCloseData1[0]: ",myCloseData1[0]," myCloseData2[0]: ",myCloseData2[0]);
print(" myCloseData1[1]: ",myCloseData1[1]," myCloseData2[1]: ",myCloseData2[1]);
print(" myCloseData1[2]: ",myCloseData1[2]," myCloseData2[2]: ",myCloseData2[2]);
print(" myCloseData1[3]: ",myCloseData1[3]," myCloseData2[3]: ",myCloseData2[3]);

print(" myRSIData1[0]: ",myRSIData1[0]," myRSIData2[0]: ",myRSIData2[0]);
print(" myRSIData1[1]: ",myRSIData1[1]," myRSIData2[1]: ",myRSIData2[1]);
print(" myRSIData1[2]: ",myRSIData1[2]," myRSIData1[2]: ",myRSIData2[2]);
print(" myRSIData1[3]: ",myRSIData1[3]," myRSIData1[3]: ",myRSIData2[3]);
Illustrating Difference Between Data Aliasing and Not Data Aliasing

If you have a higher resolution as Data 1 (5 minute in this case) than Data 2 (15 minute), then you must use Data Aliasing if you are going to use a variable to represent a price or a function output.  With out Data Aliasing all data references are in terms of Data 1.  When the 15 minute bar closes then the current [0] and one bar back[ 1] will be correct – going back further in time will reflect correct data.  During the 15 minute bar only the last value [0] will show the correct reading of the last completed 15 minute bar.  Once you tie the variable to its correct time frame variableName(0, Data2), you can then reference historic bars in the same fashion as if it were Data1.  This includes price references and function output values.

Check this chart out and see if it makes sense to you.  I dedicate a portion of a Tutorial on Data Aliasing in my latest book due out next week – Easing Into EasyLanguage – Advanced Topics.

Difference between using Data Aliasing and Not using Data Aliasing

Using Gradient to Shade Between Plots

Quickly Analyze Market Metrics with Gradient Based Shading

This is a simple indicator but it does involve some semi-advanced topics.  Just to let you know I am working on the third book in the Easing Into EasyLanguage series.  If you haven’t check out the first two, you might just want to head over to amazon and check those out.  This topic falls in the spectrum of the ideas that I will be covering in the Advanced Topics edition.  Also to let you know I just published the 2nd Edition of Trend Following Systems: A DIY Project – Batteries Included.  Check this out if you want to learn some Python and also see some pretty cool Trend Following algorithms – I include EasyLanguage too!

Shading Between Keltner Channels with RSI Intensity

The code that follows demonstrates how to shade between plots and adjust gradient in terms of the RSI reading.  I compiled this with MultiCharts, so I assume it will work there too – just let me know if it doesnt.  I found this code somewhere on the web when researching shading.  If I knew the original author I would definitely give full credit.   The code is rather simple, setting up the chart is just slightly more difficult.  The Keltner Channel was used to define the shading boundaries.  You could have just as easily used Bollinger Bands or anything that provided a range around the market.  Here’s the code.

inputs:  KeltnerLength( 90 ), KeltnerWid( 5 ), RSILength( 14 ), overbought( 70 ), oversold( 30 ); 
var: Avg( 0 ), Shift( 0 ), LowerBand( 0 ), UpperBand( 0 ), MyRSI( 0 ) ;

// Keltner

Avg = AverageFC( c, KeltnerLength ) ;
Shift = KeltnerWid * AvgTrueRange( Keltnerlength ) ;
UpperBand = Avg + Shift ;
LowerBand = Avg - Shift ;

Plot11( UpperBand, "UpperBand" ) ;
Plot12( LowerBand, "LowerBand" ) ;
Plot13( Avg, "MidLine" ) ;

// RSI

MyRSI = xaverage(RSI( c, RSILength ), 7) ;

var: projrsi(0);

// Get projected RSI in terms of the Upper and Lower Bands

projrsi = Avg + .01 * (UpperBand - LowerBand) * (MyRSI - 50) * 2.5;
if false then plot14( projrsi, "RSI" );

// Gradient background

var: barspacing( getappinfo( aibarspacing ) );
var: gradcolr(0);
// Remember how to use the IFF function?
gradcolr = iff( MyRSI > 50, GradientColor( projrsi, Avg, UpperBand, black, red),
GradientColor(projrsi, LowerBand, Avg, green, black) );

plot91( UpperBand, "ugrad", gradcolr, default, barspacing);
plot92( LowerBand, "lgrad");

// Show Bar - increase transparency of data to 100% so
// shading does not overlap the bar charts

plot4( c, "c");
plot5( h, "h");
plot6( l, "l");
Code to Shade with Gradient Based on a RSI Reading

That is a little bit of code that does a lot of work.  Here are the key lines and their explanations.

projrsi = Avg + .01 * (UpperBand – LowerBand) * (MyRSI – 50) * 2.5;

Remember the RSI outputs values between 0 and 100 – oscillates.  Assume RSI is in oversold territory at 24.

UpperBand = 16273 and LowerBand = 15023 and Avg = 15648

Let’s do the math:

  1. projrsi = 15468 + 0.01 * (16273 – 15023) * (24 – 50) * 2.5
  2. projrsi = 15468 + 0.01 * 1250  * – 26 * 2.5
  3. projrsi = 15468 + 12.5 * -65
  4. projrsi = 15468 – 165
  5. projrsi = 15308

Basically all this math is doing is keeping the RSI reading within the bounds of the Keltner Upper and Lower Channels.  You want a high RSI reading to be near the Upper Channel and a low RSI reading to be near the Lower Channel.   You can change up the formula to make more sense.

projrsi = Avg + (MyRSI – 50)/100 * (UpperBand – LowerBand) * 2.5

I have worked with computer graphics for many years and this is really a very neat formula.  The generic formula to constrain a value within a boundary is;

projrsi = LowerBand + (MyRSI / 100) * (UpperBand – LowerBand)

Here you take the LowerBand and add the percentage of the MyRSI/100 times the range.  This works too.  But the original formula scales or intensifies the RSI reading so you get much wider gradient spectrum.  The AVG is used as the center of gravity and the RSI is converted in terms of the middle 50 line.  A positive number, anything > 50, is then scaled higher in the range and a negative number, anything < 50 is scaled lower in the range.  In other words it makes a prettier and more informative picture.

The other important line in the code is

gradcolr = iff( MyRSI > 50, GradientColor( projrsi, Avg, UpperBand, black, red),
GradientColor(projrsi, LowerBand, Avg, green, black) );

This code uses the IFF function which basically replicates this

If MyRSI > 50 then

     gradColor = GradientColor(projrsi, Avg, UpperBand, black, red)

else

gradColor = GradientColor(projrsi,Avg,LowerBand,green,black);

GradientColor Function

GradientColor( dValue, dMin, dMax, nFromColor, nToColor )

Return

Returns a specific color from a user defined gradient color range, such as Blue to White

Inputs:

  • dValue = value being passed-in that is within the specified Min/Max range of values
  • dMin = Starting value for the gradient range, where the nFromColor is displayed
  • dMax = Ending value for the gradient range, where the nToColor is displayed
  •  nFromColor = Starting color of the gradient
  • nToColor = Ending color of the gradient

Since the gradient shading will cover up your bars you will need to plot the bars as well.

Chart SetUp

The close should be POINT and the other inputs LINES.

Don’t Forget To Fade Out Your Data Chart

That’s it.  Like I stated earlier – I will be including things like this in the Advanced Topics edition.  I should have it wrapped sometime in July or August.

 

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.

Clear Any Left Over Formatting

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.

Merged Equity on Closed Trade Basis

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.

Dimension and Load Arrays

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.

Parse different symbols from all data

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.

Squash Monthly List Down

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

Put Markets as Column Headers

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.

Three nested loops – Whopee.

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.