I've got some futures contracts of different expiring months in txt format (each contract has its own txt file) and would like to write a code in VBA for splicing the contracts together based on criteria such as: rollover 2 weeks before expiry, rollover when volume in current contract decreases to X amount, etc. I would also like to be able to store the spliced contract into a new txt file for future use. Would appreciate any help on this matter. D

Show us the data...

You can do this three basic ways in VBA, they are 1) use filesytemobject and parse the data into avraint array for processing using the split function 2) use functions like write, read, print etc 3) query the text as if it were a database using adodb.this should allow you to process the data without writing back to a spreahseet. For example you should be able to write a list of ticker codes where you will perform the same function on each contract and write text bask to a nominated directory for each ticker.Code Examples follow, however, some of the filesystemobject code has been wripped straight out of code modules and serves to illsutrate point 1) above. Poine 3) above also illustrated.cheersadam

If it's the actual gettign the data in that's your main question, then adamcox is right on the money. If you're more concerned with the rolling mechanism, that's a little harder. A few months back (at my old firm), I did some work in Java to do this exact sort of thing. It could get pretty complicated, one thing that definitely helped was working using an OO pattern rather than structued.So for example, we'd turn the data into TimeSeries objects - each futures contract would be a TimeSeries object. We then created a GenericTimeSeriesCreator, which would 'sew' all the series together based on another object you provided - a FuturesRollProvider object. The RollProvider simply told the creator which future contract to use when, and what adjustments to make (if we wanted to use continuation contracts). To start with we just had a LastTradeDateRollProvider, but we were able to create several subclasses of the RollProvider with different functionality (eg we would roll n business days before the back 1 contract volume exceeded the front contract, etc etc). In Java, using this approach is pretty striaghtforward, in VBA, it's not as easy, but certainly do-able (you'd have to use interfaces rather than inheritance). It really depends how sophisticated you want to get, and how good your VBA is.

Thanks guys for the replies... Adamcox, will tryout your spreadsheet. DCFC, I seem to have some problems attaching the txt files... if it's of any use, they're obtained freely from www.TurtleTrader.com. They are essentially text files with 7 columns - the first are the dates, the second to fifth are the OHLC prices and the Volume and Open Interest are the sixth and seventh columns respectively. Hope it's clear... Cheers.D

I like the idea of time series objects. Is the sewing of of various objects created by a 'metaverse' approach by which a new time series object is created, or is another time series file created only ?cheersadam

It's a new TimeSeries object, which is sort of a view on several different TimeSeries, adjusted in some manner. You're right in the 'multiverse' approach, all the TimeSeries are held in a TimeSeriesRepository object (just a map that returns a contract given a key really). So when you 'sew' the series, you can create a TimeSeriesFilter on the TimeSeriesRepository using a given RollProvider. A TimeSeriesFilter is a subclass of Repository, so once you've created the Filter, you can just pull out a series in the same way. I'd love to say that I thought all this through, but I inherited the system mainly, but once I got my head around the concepts it is very, very flexible.You could turn it into a text file pretty easily (iterate through the data, cout << it.getDate << it.getValue() etc).

Adam -- are you married to using VBA for this? If you want to do what Doney's describing, there are some peculiarities of the Date/Time libraries in Java vs. VB(A) that make Java a little more convenient for that sort of work. Java also isn't the ideal text processing platform (compared to say perl or python) but personally, I'd probably rather do it with Java.