Single Access file vs. 1,000 Excel files

[mdarby]

I have written an Excel VBA application which stores the data for a large number of stocks, using a separate .xls file for each stock. I don't know Access well and am wondering if there would be a large performance improvement in storing all that stock data in Access instead of Excel.

Specifically, if I have a worksheet in Excel which repeatedly reads information from 1000 separate, closed .xls file, would I see a big performance improvement if I stored all that information in Access? I am identifying the cells of interest in the Excel files using the MATCH function. I presume I'd identify the records in Access using some kind of query.

THANKS FOR ANYBODY"S HELP.

 

[dk]

Hi,

If your 1000 files all use a similar structure and their is a definite logic to your data then you may be better off using a database. I'd imagine maintaining 1000 files would be a bloody nightmare unless they are literally 'data dumps' and don't need to be maintained.

If your data is normalised to some degree then I'd imagine it would be a lot quicker than using all of those seperate files. However, it's hard to give a definite answer without knowing a bit more about your situation.

<ul>
[*]How are the 1000 Excel workbooks updated?
[*]Does each workbook have a single or multiple sheets?
[*]Are each of the workbooks in the same format?
[*]What is the format?
[/list]

If you need more advice then I'd suggest posting a bit more info, detailing the inputs, processes and desired outputs from the system.

 

[mdmilner]

In my opinion, working within an active open application is always going to be faster than accessing external files (opening/reading/writing/closing).

SQL, particularly with normalised data (relational database) is in nearly all cases with large amounts of data, going to be faster than using VBA to walk records/fields.

I have a feeling this isn't an issue, but mass importing from your 1,000 data files is relatively easy if they all follow a single format. You'd do something like tell it to import everything in a given directory/folder sequentially and add them to a specified table (or tables)

Mike

 

[mdarby]

Sorry for the earlier duplicative post, my computer locked during its submission and I incorrectly assumed it hadn't been posted.

The files are all in a consistent format. Each row in the files has for security price and volume information, one row per date. The data are static, so except for adding new columns representing additional information I am tracking, they don't change. The files themselves have different number of rows because different stocks started trading at different dates, so some files have more rows than others.

Each security file has all it's information in a single worksheet.

One process I have implemented goes through fives years of data day by day, extracts the price data for each stock for that date to be analyzed. With the current system this is taking prohibitively long, about 5-10 hours for 100 files for 10 years of data. I think if this is to scale I need to go to Access if only for this one operation.