Creating range bars in Excel

A

applejuice

Hi

I figured the most computer savvy forum members would be in here...

I have several hundred excel files:
2011-05-01.xls
2011-05-02.xls
2011-05-03.xls etc...

Each file contains three columns, relating to one particular futures contract.

[Time | Price | Volume]

On average I guess there are around 50,000 rows of data in each file.

Each file contains data for a particular day, and I have named each file as such.

My question is this:

*How can I create range bars?
I understand the concept behind range bars, but while I am fairly Excel proficient, I am incapable of repackaging my raw data in such a way to create these range bars. Perhaps it is in fact impossible with Excel.

I would very much appreciate some advice, if anyone is in a position to give it.

Some secondary questions:

*I would rather pile all of the data in to one large file. I did attempt to merge them all in to a Microsoft Access database, but the loading times were extreme and I encountered numerous crashes, and hit a brick wall with it.

What would you do if you had 200 .xls files that you wished to analyze?

*If Excel is not the appropriate software for working with this data, what alternatives exist?

Thanks in advance.
 
If you data is consistent on each spreadsheet, you can open each data file, copy the range of data into a master file, then close the data file, and so on x's 200.

Assume you're asking about displaying the data in a typical or template chart.

Haven't played with Excel programing in ages, but if the above describes what you need it's certainly do-able. To get started you might play around with the Excel recorder, then look at the code it generates and modify it to get the results you want.
 
Yep,

Although it seems the master file cannot be in excel format:

At 50,000 rows per day, I will hit the 1,000,000 row limit with less than even a single month of data.

The important issue for me is reformulating my data so that it can be used to create range bars - thereby allowing me to more easily identify intraday gyrations, those gyrations being at least XYZ points in magnitude.
 
It will be difficult to do in pure excel, but can be accomplished if you know VBA.
Considering row limits, I might read in each daily file as a worksheet tab and run a loop (through cells) in VBA to create bars based upon your constraints.

Depending on what you are trying to accomplish, you also have columns you can write the rows across for each day/week (up to a limit).
 
Quote from applejuice:

Yep,

Although it seems the master file cannot be in excel format:

At 50,000 rows per day, I will hit the 1,000,000 row limit with less than even a single month of data.

The important issue for me is reformulating my data so that it can be used to create range bars - thereby allowing me to more easily identify intraday gyrations, those gyrations being at least XYZ points in magnitude.
More...

As dtrader98 suggested, I too anticipated that it would be a VBA macro enabled worksheet with separate tabs for each day.

However, you file size would quickly become unmanageable. I think consolidating the data in a single spreadsheet is the wrong approach. I suppose if you could do a file name list and as long as your data ranges are consistent from file to file you could automate the external reference links for the charts.
 
The much larger problem is computing the range bars properly between the prior and current day. You have a boundary problem here since your data is stored by daily activity.
 
Could you post an image of how you want the range bars to look like, and attach some sample XLS files? One solution I'm thinking of is a MySQL database and PHP output to HTML pages.
 
You must log in or register to reply here.
Back
Top