about a spread sheet file

Discussion in 'Trading Software' started by swordman168, May 30, 2009.

  1. dear all ,

    I have bought a file in txt format . It is a intraday 1 minute historical futures data for several years . I hope to open it in spread sheet software so I can do some statistic work to work for . But when I use excel to open it , I found that my excel ( version 2002 ) cannot open such quantity of rolls , more than 65536 . I have spss and open office 3.0 but seems both cannot open it in a spread sheet .

    Can someone tell me how to do it .
    Is other newer version of excel can do it ?
    Thanks a lot in advance .
     
  2. Try excel 2007

    You should also try splitting the txt file into smaller files. Many charting applications will also have such limitations.


    Here's Microsoft's description...

    More rows and columns, and other new limits
    To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003. For those of you who are curious, columns now end at XFD instead of IV.

    Instead of 4 thousand types of formatting, you can now use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory.

    To improve the performance of Excel, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.

    You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports multiple processors and multithreaded chipsets.

    Office Excel 2007 also supports up to 16 million colors.
     
  3. if you have access to any type of data editor or programming knowledge, you could simply cut and paste 65000 rows of data to each column. Simple to do in R, matlab, or unix if you are familiar.

    Very crude approach is to open the file in notepad and find/search for the 65000 value, then shift to select range all the way up, and cut and paste that way. You get a flag in excel, but it allows you to paste anyways. Then cut out those lines in notepad and repeat for the next 65000 characters.

    Heard too many complaints about version 2007 to even try it yet. If it aint broke, don't fix it.