Compiling intraday statistics and researching/backtesting intraday data - What platform or solution?

Discussion in 'Strategy Building' started by Howard, Jul 20, 2015.

  1. Howard

    Howard

    Hello all,

    Brett Steenbarger is the one who first taught me how to do simple statistical explorations using OHLC data on an EOD basis in Excel. He teaches this in his latest (I think) book. However, my interest lies in intraday trading and here's a quote from him:

    "Should you become serious about investigating such intraday patterns, I strongly recommend obtaining a clean database from a vendor such as Tick data. You can use their data mangement software to create data points at any periodicity and download these easily into Excel. Serious, longer-term investigations of historical intraday data need tools far stronger than Excel. Limits to the size of spreadsheets and the ease of maneuvering them make it impossible to use Excel for long-term investigations of high frequency data."

    Does anyone know what type of tools or software Brett is talking about here?

    At this point, I've been using 1-minute data. Both due to cost and size of the data, I was hoping to avoid tick data for now, although I certainly think I would want to use tick data later on if I should successfully proceed with my discoveries. 1-minute data seems fine during normal market activity, but shows limitations during periods of heightened volatility. Perhaps that could be helped simply by going down to 30-second data for now.

    What I'm doing at this point, is merely collecting useful statistics and metrics for every single trading day. I've been doing it manually in Excel, but would like to automate it eventually. I have a programmer friend who may help me do this.

    PS: This is not intented to be part of an automated HFT strategy or similar. It's meant to aid discretionary (hoping to reduce the element of discretion) day trading.

    Any ideas?

    Thanks!
     
  2. IAS_LLC

    IAS_LLC

    Check out a software package called "Octave".
     
    Howard likes this.
  3. Howard

    Howard

    This is similar to MATLAB, right?

    Incidentally, I already own MATLAB and have learned some VERY basic MATLAB skills as a minor part of the curriculum in a course I took in calculus. Specifically, numerical methods such as Euler's Method, Newton's Method and numerical derivation and integration.

    However, to me, it seems like there's a long way from my current skill set to actually being able to implement what I want in MATLAB. Perhaps there are some books or resources which teaches MATLAB within a trading context?

    I already have a demanding curriculum and spend time doing trading research on the side. If I were to apply myself to learning programming, that will probably leave little time left for actual trading research. But perhaps there's no way around that and I should rather simply do it.

    I guess what I was hoping was some out-of-the-box solution that could be learned rather quickly.

    My local university has an intro class in Python this fall. Perhaps I should enroll there? Or do some of the online courses mentioned in another thread I made? Coursera?

    Thanks,

    Howard
     
  4. IAS_LLC

    IAS_LLC

    Yes, Octave is poor man's Matlab. If you have Matlab, even better. Python is "harder" to learn than Matlab in my opinion (not hard). Matlab probably has all of the statistical functionality you need already built it. What are you try to calculate?
     
    Howard likes this.
  5. Howard

    Howard

    Thank you.

    I'd rather not share too much, but basically, I want to build on the type of sorting exercise I'm already doing in Excel, where I have data in tables.

    With Excel, you can set up OHLC data, but also add derived columns from the OHLC input such as % change, if it's a gap up/down open, day of week and so on. With some intelligence, you could sort based on these variables and PERHAPS get an idea of the trading day ahead.

    I'm interested in doing the same with MATLAB or some other software, but I want to add even more metrics to work with. Some of these metrics I'm currently putting in manually in my Excel sheet, but I'd like to automate the process with 1-minute data as input. Then I'd like to be able to sort this data and see what I can find.

    Excel seems to have limitations when you start working with larger datasets and add a lot of columns/variables in your sheets. But perhaps I can get it to work. As mentioned, I may see myself working with tick data later on and I suppose Excel is completely out of the question then.

    Basically, I have a lot of ideas and I believe some of them are good, but I have troubles implementing it due to my lack of technical understanding/needs. The ideal would be some existing software that would be easy to use, but perhaps there's no way around learning programming to accomplish what I want. :)
     
  6. IAS_LLC

    IAS_LLC

    Well, I assure you Matlab can do everything you are doing in excel (and much much more), more efficiently and probably with less "code". Hell, Matlab can even make your powerpoint presentations and spreadsheets for you (I do this all of the time). Unless you provide more detail on what you're trying to accomplish, that is about all I can give you.
     
    Howard likes this.
  7. Howard

    Howard

    I do not doubt it, I just have hard time visualizing it and I'm not sure how I could go on implementing it. Do you have a book or some resources to recommend?

    Actually, I have this:

    http://www.amazon.com/gp/product/01...d_t=201&pf_rd_p=1944687442&pf_rd_i=1439828628

    Second edition.

    I started working through it from start to finish, but suddenly were short on time and had to get back to my actual studies and not risk getting poor grades. I'm much more motivated now though, but I'm wondering if working through a book like this is a bad approach. Not sure really. Maybe it would be better for me to simply hire a programmer for the job.

    Would you mind sharing some of your spreadsheets or work in MATLAB? If it's not proprietary information?

    But say for instance that I have two years of 1-minute OHLC data. I then want to compile various metrics for all the trading days for this year. I suppose this could be done by loading the data and then using a script to calculate the metrics I want for every single day.

    I'm not interested in sharing all the details since this is a private project, but say for instance that I in Excel have a column which says Open and that I have two possibillities: flat or gap.

    Another column says Gap Close and again, two possibillities: yes/no.

    In Excel, and I already do this with daily OHLC data, I can then use the sorting function and choose all the days in my sample that has a gap. Further, I can sort out all the days with a gap close. Then I can see how many days have a gap close on the same trading day.

    I'm wishing to experiment with similar analysis, but add some more detail.
     
  8. Let us say you create them. What are you going to do with them? Are you going to look at every single day? Or maybe are you looking for a scanner of some conditions? In that case you could use Amibroker software, an excellent solution at low price.
     
  9. IAS_LLC

    IAS_LLC

    All of my trading stuff is in C++ (not going to share that), not matlab or spreadsheets. But if you have a specific question on how to do something (specific), I can prototype some matlab code for you (unless its something complicated...in which case I will have to charge you). My use of matlab is for aerospace applications.
     
  10. rb7

    rb7

    To the original question of the OP, Tick Data provide their own software to manipulate their data, but it's pretty basic and the only thing you can do is to create time series of various length.
     
    #10     Jul 30, 2015