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.

Comments

  1. 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?

  2. Exceltrader says:

    The Simulator does convert the tick data to OHLC data in time period you specify.

  3. Aha... So coool. Thanx :)

  4. 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?

  5. Exceltrader says:

    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.

  6. Hello,

    How do you convert streaming data in an excel cell to growing columns of OHLC?

    Excel 2003

    Thank you!
    dp

  7. ExcelTrader says:

    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

  8. 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.

  9. 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

  10. ExcelTrader says:

    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.

  11. Hi

    When i press the convert button on default settings, it opens a blank workbook.
    Do you have any idea what's wrong?

  12. ExcelTrader says:

    Jerry, with default settings it will convert the sample data and place it in the new workbook("Book1.xls" or default name) and you will see the data on Sheet1 of Book1. If you are not seeing any data. I would imagine there is a security setting the should be lowered in Excel (File>Options>Trust Center in Excel 2007/2010).

  13. Hmm, I was using the default settings in trust centre but i did manually enable active x and macro. Still after pressing the convert button, a blank workbook named "Book2" still pops up.
    This occurs similarly on 2 different computers running Excel 2007 with either WinXp and Win7. Too bad, it's a great tool you have got there.

  14. ExcelTrader says:

    Tested to make sure there is no issue and I find no issues with the default settings running the sample data with excel 2003, 2007,2010 (Tested and confirmed working with English(U.S) language and period as decimal separator, comma as thousands separator). What is your decimal separator? What are you Office Language settings? The file has many users and this is the first report of any issue fwiw.

  15. Hello, I'm having the same issues as Jerry. I'm running excel 2007 with vista sp1. I'm using the default settings in trust centre. I also did a test run with anti-virus disabled & internet connecting turned off.

    I'll keep trying different tweeks. Any suggestions would be helpful.

    btw, You have great stuff here .. keep up the good work

  16. ExcelTrader says:

    Sherwood,
    Thanks for reporting the issue. (The newly created workbook should show the converted data on Sheet1). It would help if you could provide a little info. I cannot duplicate this issue. First could you let me know the following?
    What is your decimal separator? What are you Office Language settings? Are you using an unaltered DataConverter.xls"? Has the file been converted to .xlsm?
    Thanks

  17. sheets 1 thru 3 of the newly created wb are blank.
    decimal separator is a period/dot ( . )
    thousands separator is a comma ( , )
    office language setting = english
    DataConverter.xls is unaltered, not *.xlm

    Thanks

  18. ExcelTrader says:

    Sherwood, I have duplicated the issue and all issues are resolved in the current version (see download link in main post). Thanks for your input!

  19. I am trying out the data converter but am experiencing a problem. It works fine with the data supplied, but will only list 1 row of data if I add a sheet with data. Am using excel 2010.
    Any suggestions?
    thanks

  20. ExcelTrader says:

    me yu, Convert file to xlsm when using 2010.

  21. HAve switched to 2007, still not working. ?

  22. ExcelTrader says:

    me yu, could you send me the file with your test data to my email addresss (top right of header) thanks

  23. I have date and time in different colums
    how do you use the converter for that type data

  24. Hello,

    Having same issue...keep receiving blank workbooks. I tried with data in all formats, .xls .xlm .xlsx None of them have worked out for me?

  25. ExcelTrader says:

    The issue of a blank workbook showing instead of converted results has been resolved. (Download above, the old version has been replaced).

  26. Hi,

    great tool, great idea, but there is one problem, when I want convert 1m to 5m bars and there is some missing in 1m, than it will calculate not 5 min bars, but every 5 bars.

    Is there any solution for that?

    thx

    chris

  27. Great work. I never thought that only through excel a trading system could be developed. However I am from India and reached here while searching for conversion utility which could convert 1 minute data to 5 minute or to any other larger time frame. I have downloaded and tested your utility and also gone through the macros(open source). Though I have some understanding of macros yet I am not that much expert to understand what you have programmed.
    Your utility is wonderful but my purpose couldn't be served by it does not take into account the time element. If you can change the codes a little then it would be possible to convert 1 minute data to 5 minute or more.
    Thanks and hope you will make the changes in this wonderful utility.

  28. Hi
    If there is a data like
    name , date , time , Open , high , Low , Close , Adj Vol
    on tick data , Time is not handled

  29. I've created a Forex robot in MS Excel and have been successfully trading with it for the past two years. I have been using Strategy Runner's Pro console as well as their Strategy Automation Station (SAS) to receive information from my Excel and execute them that info with my FXCM account into the market. Due to the sale of Strategy Runner and the new owners restructuring of the company, at the end of Feb. 2012 FXCM will no longer be providing clearing house services for Strategy Runner. Using FXCM's Trade Station 2, which will become my platform by default, is it possible to connect my Excel robot to the market so I can trade from Excel? And if so, what do I need to do or buy?
    Thank you.

  30. Sorry to get back to blank worksheets. I have run the DataConverter on Office XP Pro and it worked fine. On Office 2007 Home I get just blank sheets. The Excel version is 2007 (12.0.6654.5003) SP3 MSO (12.0.6607.1000), Win 2007 Home Premium. Location setup =US.

  31. daily to weekly does not = 5 bars says:

    argh!! holidays!

  32. sunnysmile says:

    wow!!! Ur a genius and just went to the top of my genius's list :-) thanks man

  33. Good job. I have date and time in separated column. Can it be done?

  34. ExcelTrader says:

    One option is to merge your time and date columns. The below code assumes date is in column A, time is in column B, and the sheet containing data is sheet1. Once the code is run, column A would contain the merged data. Change the values for the following 3 variables as needed: ShtName = "Sheet1", Col1 = "A", Col2 = "B".

    Sub Mergecolumn()
    Dim Col1 As String
    Dim Col2 As String
    Dim ShtName As String
    ShtName = "Sheet1" 'change to the sheet name that contains the Data
    Col1 = "A" 'change to column containing Date
    Col2 = "B" 'change to column containing time
    Dim wsET As Worksheet
    Dim lr As Long
    Dim rr As Range
    Set wsET = ThisWorkbook.Worksheets(ShtName)
        With wsET
            lr = .Cells(Rows.Count, Col1).End(xlUp).Row
            For Each rr In .Range(Col1 & "1:" & Col1 & lr)
                rr.Value = rr.Value & " " & .Cells(rr.Row, Col2)
            Next rr
        End With
    End Sub

  35. Hi,
    I am also getting the problem of Blank worksheets in Excel 2007,
    I have done the changes in trust center settings also but to no avail.
    is it possible to change the vba such that it displays the result in a new worksheet in the data converter workbook,
    I guess then the problem of blank workbooks might get sorted out

  36. ExcelTrader says:

    Deepak,
    Verify that each question is correctly answered in column B of the settings sheet. For example if the question "What is the name of the workbook where your Data is located?" has a misspelled workbook name it would result in a blank sheet.

  37. Rodrigo says:

    Right onh-its helped me sort things right out.

Speak Your Mind