For all of you who want to open multiple text files from within VBA here is the code that will do just that. Here I open a Open File Dialog and enable multiple file selection. I then parse the return string to gather the path and individual file names. The data is then processed – I am looking for a file with a date and a value separated by a comma.  You can basically do anything you want with VBA and you also have the EXCEL functions right at your fingertips.  Plus output to a worksheet is ultra simple.

 Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        Set ffs = .Filters
        With ffs
            .Clear
            .Add "CSV Data", "*.csv; *.txt"
        End With
        .AllowMultiSelect = True
        If .Show = False Then Exit Sub
        fcnt = 0
        maxDate = DateValue("01/01/1900")
        minDate = DateValue("12/31/2200")
        prevSymbol = "**"
        dateNewestToOldest = False
        For Each it In fd.SelectedItems
			fcnt = fcnt + 1
            filePath(fcnt) = it
            fileName(fcnt) = Right(filePath(fcnt), Len(filePath(fcnt)) - InStrRev(filePath(fcnt), "\"))
            symbol(fcnt) = Left(fileName(fcnt), 2)
            Set DataHolder = New DataClass
                        
            If symbol(fcnt) <> prevSymbol Then symbolHolder.Add symbol(fcnt)
            prevSymbol = symbol(fcnt)
            dataCnt = 0
            Open filePath(fcnt) For Input As #1
            rowNumber = 1
			Do Until EOF(1)
				Line Input #1, lineFromFile
                dataCnt = dataCnt + 1
                If dataCnt > skipLines Then
					lineFromFile = Replace(lineFromFile, " ", "")
                    lineItems = Split(lineFromFile, ",")
                    numItems = UBound(lineItems) - LBound(lineItems) + 1
                    isDash1 = InStr(lineItems(0), "-")
                    isSlash1 = InStr(lineItems(0), "/")
                    If isDash1 > 0 Or isSlash1 > 0 Then
                        inputDate = CDate(lineItems(0))
                    Else
                        myYear = Left(lineItems(0), 4)
                        myMonth = Mid(lineItems(0), 5, 2)
                        myDay = Right(lineItems(0), 2)
                        inputDate = DateSerial(Left(lineItems(0), 4), Mid(lineItems(0), 5, 2), Right(lineItems(0), 2))
                    End If
                	If dataCnt > 1 Then
                    	If inputDate < tempDate Then dateNewestToOldest = True
                	End If
                	If inputDate > maxDate Then maxDate = inputDate
                	If inputDate < minDate Then minDate = inputDate
                	Set DataHolder = New DataClass
                	DataHolder.SymbolName = symbol(fcnt)
                	DataHolder.SymbolNum = fcnt
                	DataHolder.EquDate = inputDate
                	DataHolder.EquVal = lineItems(1)
                	myEquityCollection.Add DataHolder
                	tempDate = inputDate              
				End If
            Loop
        Close #1
        Next it
    End With

Since Python doesn’t  allow for a GOTO program flow structure I changed the inline trade entry/exit logic into function calls.  This allows for the call of these functions to be non-sequential.  The original PSB order placement was sequential and would examine the entry/exit logic in TOP-DOWN fashion.  Meaning that if you put the Long Entry Logic first, the program would evaluate that logic first on every bar of data.  This was fine for the majority of trading systems out there.  However, systems that could enter multiple signals on the same bar require the orders to be placed in order of whichever was closest to the current market price.  Let’s say you have a trading algorithm that issues a stop to exit at a loss and a stop to reverse your current position.  If you examine the reversal logic prior to the stop loss and the stop loss is actually closer, then you will execute the wrong signal first.   By encapsulating the entry/exit logic into functions you can use decision constructs to flow through the correct logic in the correct order.  Here are the trade signals programmed as function calls:

As you can see there are six modules – Long Entry, Long Loss, Long Profit, Short Entry, Short Loss, and Short Profit (note non-necessary code was collapsed).  I used the Sublime text editor to collapse the unnecessary lines of code.  You can download a trail version from their website.  I am no longer using the IDLE as my go to IDE – I have fallen deeply in love with PyScripter.  I will delve into this subject later.

Now that you have all your trade signals programmed as functions you can utilize if-thens to determine what order they are called.  Here is the code that calls these functions

If you are flat and you can buy or sell on the same day then you call both functions.  This back tester will allow you to buy and sell on the same day – the only problem is it doesn’t know which occurred first: the buy or the sell.  This can cause a problem because you need to know the correct position by the end of the day.  Since we can peak into the future (be very, very careful) we can look at the close of the day and compare it with the long and short entry prices.  Here is some code that might make your entries more accurate on those occasions where both orders could be filled.

if myHigh[D0] >= buyLevel and myLow[D0] <= sellLevel:
	closeToBuyDiff = myClose[D0] -  buyLevel
	closeToSellDiff = sellLevel - myClose[D0]
    	if closeToBuyDiff < closeToSellDiff:
    		se = sellEntry(sellLevel)
	    	le = longEntry(buyLevel)
      	else:
    		le = longEntry(buyLevel)
    		se = sellEntry(sellLevel)
					

This code will execute the long entry logic after the short entry logic whenever the close is closer to the long entry price than the short entry price.  If both orders are filled on the same bar and the close happens to fall closer to the long entry price, then the software assumes the short entry was entered earlier in the day and the long entry later in the day making the position at end of day long.  Is this 100% accurate?  No but it logically stands to reason that a close near the high would indicate they high was made last.  Without intraday data we simply do not know what happened first.

It was recently brought to my attention that the Excel System Back-Tester(ESB) did not function properly on the MAC OS X.  In other words it bombed when trying to open a comma delimited data file and also when one tried to run an algorithm.  Thanks to a purchaser of the UATSTB I was able to fix the bugs without removing any functionality.  I will post the Macintosh version here as well as have WILEY put it on the book’s website.  Sorry for any inconvenience this may have caused.  Here is the the link:

ESB Macintosh

If you haven’t come across the great data resource Quandl.com I highly suggest in doing so.  A  portion of the data that I used in writing my latest book came from Quandl, specifically the wiki futures or CHRIS database.  Here is the link:

https://www.quandl.com/data/CHRIS

There is a ton of free futures data.  The different contracts are concatenated into large files.  However, the rollover discount is not taken into consideration so the data “as-is” is somewhat un – testable.  I am in the process of scrubbing the data as well as creating  a “Panama” adjustment to the contracts in the large files.  As soon as I complete this task I will provide the data on this website.  Purchasers of my latest book will find 10+ plus years of history of continuous futures data that I pieced together from several different sources, including Quandl.

Here is a quick video that shows how to create a custom symbol list in TradeStation’s Portfolio Maestro.  I describe this in my new book, “The Ultimate Algorithmic Trading System Toolbox.”  The books should be released in May.

Quick Video On Creating Symbol List with Portfolio Maestro

[follow_me]I was speaking with Mike Chalek on the phone this weekend concerning Data Aliasing and he felt this post was a little confusing. After re-reading it I can see where he is coming from. Using the same example let me see if I can clarify: assume the trading day is Wednesday and you want to keep track of the slope of a 19-day weighted moving average of data2 (weekly bars) by using a variable. The following code will give an erroneous result:

wAvg = wAverage(c of data2,19);
mySlope = wAvg – wAvg[1];

If you interrogate mySlope intra-week then it will always be equal to zero. The wAvg is by default tied to data1 which in this case is daily bars. So the value of wAvg is carried over from one day to the next. It only changes when the average of the weekly bar changes and that only occurs on Friday.

There are two possible solutions:

Without the use of data aliasing – inLine function calls
mySlope = wAverage(c of data2,19) – wAverage(c[1] of data2,19) ;

With the use of data aliasing –
vars: wAvg(close of data2,0);

wAvg = wAverage(c of data2,19);
mySlop = wAvg – wAvg[1];

Either examples will work, but if you have several variables tied to a different data stream, then the code will be much cleaner looking using data aliasing – plus it cuts down on multiple function calls.

I have been working on a project where the strategy combined daily and weekly bars.  Keeping track of the two time frames was, at one time, not that easy.  However, with TradeStation’s Data Aliasing it is no problem at all.  We all know that Data 1 is the highest resolution time frame and is the one used for trade execution.   Data 2 can be a different market or a different time from of the same market.  TradeStation allows for multiple data streams.  Take a look at the following output in table 1.  Wavg is a nine period moving average of weekly crude data.  Wavg[1] is the prior value of the moving average.  If you wanted to make a trading decision on a daily bar basis by looking at the slope of the Wavg you couldn’t.  The Wavg and Wavg[1] only changes at the beginning of the next week.  Most traders want to be able to make a trading decision intra-week by examining the current values of the Davg1, Davg2 and the slope of Wavg.  During the week the slope of Wavg is ZERO.

table 1
Date    Davg1 Davg2 Wavg Wavg[1]
1151019 46.94 46.38 46.17 46.17
1151020 47.01 46.54 46.17 46.17
1151021 47.00 46.69 46.17 46.17
1151022 46.95 46.74 46.17 46.17
1151023 46.93 46.70 46.54 46.17<< changed here
1151026 46.83 46.55 46.54 46.54
1151027 46.71 46.47 46.54 46.54
1151028 46.74 46.44 46.54 46.54
1151029 46.74 46.40 46.54 46.54
1151030 46.73 46.39 46.60 46.54
1151102 46.57 46.37 46.60 46.60
1151103 46.55 46.45 46.60 46.60
1151104 46.36 46.44 46.60 46.60

Now look at table 2.   The Wavg is not being updated on a daily  basis but on a weekly basis.  The current Wavg doesn’t become the prior Wavg on each daily bar.  Wavg[1] stays the same until a new weekly bar occurs.  You can now make a trading decision intra-week by examining the slope of the Wavg.  Each time frame update should only occur when a new bar of that same time frame is generated.  This feature is really cool and is easy to implement.  

table2
Date      Davg1 Davg2 Wavg Wavg[1]
1151019 46.94 46.38 46.17 45.75 < notice how the Wavg and Wavg[1] are always different
1151020 47.01 46.54 46.17 45.75
1151021 47.00 46.69 46.17 45.75
1151022 46.95 46.74 46.17 45.75
1151023 46.93 46.70 46.54 46.17
1151026 46.83 46.55 46.54 46.17
1151027 46.71 46.47 46.54 46.17
1151028 46.74 46.44 46.54 46.17
1151029 46.74 46.40 46.54 46.17
1151030 46.73 46.39 46.60 46.54
1151102 46.57 46.37 46.60 46.54
1151103 46.55 46.45 46.60 46.54
1151104 46.36 46.44 46.60 46.54

Here is the code that utilizes Data Aliasing. All I did was declare the weekly avg variable and tied it to data2.

vars: mavShortDaily(0),mavLongDaily(0);
vars: mavWeekly(0,data2);

mavShortDaily = average(c,19);
mavLongDaily = average(c,39);

mavWeekly = average(C of data2, 9);

If mavShortDaily > mavLongDaily and mavWeekly > mavWeekly[1] then buy this bar on close;
If mavShortDaily < mavLongDaily and mavWeekly < mavWeekly[1] then sellshort this bar on close;

print(date," ",mavShortDaily," ",mavLongDaily," ",mavWeekly," ",mavWeekly[1]);

Notice how the variable mavWeekly was tied to data2. When you delcare a variable that is tied to another data other than data1 you can put the data stream right in the variable delcaration : mavWeekly(0,data2).

In the next few weeks I will be providing EasyLanguage code utilizing FSMs to model trading systems.  Cut your teeth on this model of a combination lock.  The EasyLanguage will use the Switch and Case keywords to implement these models.