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.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:
- Download DataConverter.xls
- Open DataConverter.xls. (Quickstart!: Skip the directions below. Just Open file and press “Convert” to see how it works with default settings.
- 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)
- Enter the Column letter for each type of data (Time/date, Open, High, Low, Close, Volume)
- 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.
- Finally press the “Convert” Button. The converted data will appear in a new workbook. Save as desired.
jezus christ, man… you are a magican with excel. didn´t know all this was possible…
is it also possible to convert tick data (your data file) to let say 5 min?
The Simulator does convert the tick data to OHLC data in time period you specify.
Aha… So coool. Thanx :)
Say I would like 1440 minute bars (Daily)…However, I would like one set to be from say, 9:00am to 9:00am but another set to be from 9:25am to 9:25am. Is this easily accomplished?
Hi CRC. The simulator could to that with a small customization, however the easy workaround would be to remove the unwanted tick data from data.xls before running simulator.xls.
Hello,
How do you convert streaming data in an excel cell to growing columns of OHLC?
Excel 2003
Thank you!
dp
dp, An example is below using the worksheets calculate event.
Private Sub Worksheet_Calculate()
Dim x As Double, y As Double
x = Cells(1, 5) ‘THE LIVE PRICE CELL
y = Cells(1, 3) ‘THE HIGH PRICE CELL
If x > y Then
Cells(1, 3) = x
End If
x = Cells(1, 5) ‘THE LIVE PRICE CELL
y = Cells(1, 4) ‘THE LOW PRICE CELL
If x < y Or y = 0 And x > 0 Then
Cells(1, 4) = x
End If
End Sub
Hi, This is a great tool.
But how to overcome the holidays problem. I have daily data and would like to convert to weekly OHLC. However, the week should be defined as beginning from wednesday to end Tuesday. (This is required for a specific analysis). I can input 5 in the tool but the problem is the daily data has holidays inbetween. Can you help me. many thanks for the help and regards.
Hi there- this program is great. But, what I’d really like to be able to do is use my own tick data for other securities, and also be able to use the program to output the tick data to different time bars e.g. 1 min and 5 min, in ACSII format (for running say in Metastock). Can the program do this?
Regards
Ben
The Data converter currently does not convert tick data only OHLC data to higher timeframe. You can save the resulting data to a comma separated file/text file etc from excel’s file>save as menu.