Data Converter to Convert OHLC Data to a larger timeframe.

If you have ever wanted to take your existing Open High Low Close historical trading data and convert it into larger time frame, the following Excel VBA based data converter I wrote may do the trick.

DataConverter

DataConverter.xls allows you to configure the Number of OHLC bars that should be condensed down into a single bar. You also can list the file name, sheet, and columns where your data is located.  The data will almost instantly be transformed into the higher time frame you have requested.

Instructions:

  1. Download DataConverter.xls (*last updated 1/1/2011)
  2. Open DataConverter.xls. (Quickstart!: Skip the directions below. Just Open file and press "Convert" to see how it works with default settings.
  3. If you have OHLC Data in a workbook, open that file (you can also just use the default sample data that is contained on the sample data sheet in DataConverter.xls)
  4. Enter the Column letter for each type of data (Time/date, Open, High, Low, Close, Volume)
  5. Enter the Name of the WorkBook and worksheet that contain the data. For example, to convert 1 Minute bars to 4 minute bars, simply enter 4. To convert 3 minute bars to 15 minute bars enter 5 (5*3=15) etc etc.
  6. Finally press the "Convert" Button.  The converted data will appear in a new workbook. Save as desired.

Constant Volume Charts added

I have added the option of constant volume  bar charts (as alternative to time based) for the Excel/VBA based automated trading platform

The constant volume bar option, updates a new bar after a certain amount of volume has completed. This amount should be entered into D3 of the “Live” sheet. Checks are in place to prompt if the values are not entered and the constant volume is set to override the default time based bars.

Below is an example of Constant volume bar charting. You will notice that each bar contains roughly the same amount of volume.

CV

Excel Books I recommend

Best Beginner books

1. Excel 2007 Bible – It is important to have one book that just covers excel (not Visual Basic and Macros) so that you don’t unnecessarily use VBA for performing calculations better handled by formulas. The channels and trend lines in my videos of sim trading the ES are formula based not VBA based like most people seem to think. I probably could not have figured it all out without this book.

2. Excel 2007 VBA Programming For Dummies

3. Excel VBA in easy steps by Ed Robinson – By far the best beginner book but often out of stock. It’s only 188 pages but covers a lot of material in a very clear and concise manner. The examples are easy to understand even with no prior programming experience. If you have no prior VBA experience this is a great first book. Barnes and Noble is the publisher. The price on the cover is 9.95, but since it is out of print people are currently selling it used for $50 to $100 :(

4. Excel VBA Macro Programming – If Excel VBA in easy steps is out of stock, this may be a good alternative judging from the reviews. I have not personally used this one though.

Best Intermediate and Advanced books

1. Excel Hacks is filled with original content and examples. I have a lot of Excel books and thought I knew about pretty much everything until I bought this book.

2. Excel 2007 VBA Programmer’s Reference This enormous book should be used for reference. It has a great index so you don’t have to waste time trying to figure out how to do some simple task. If you have used my earnings.xls workbook that automatically downloads stock’s earnings dates, then you have already benefited from this book. I used an example from the 2003 version of this book to learn from!

*Be careful about buying multiple books by the same author because there is a lot of repetition and they often put out practically the same book but with a different co author, book name and Cover!