Hello all. I'm working on a project to analyze financial data (income statements, balance sheets, cash flow statements) for many companies (>100).



I currently have the the data in a very inefficient format in Excel. I have three worksheets, one for each of the three financial statements above. In each of these worksheets, the data is broken down as follows:

Company Year Data1 Data2 Data3
Company1 Year1 x y z
Company1 Year2 x y z
Company1 Year3 x y z
Company2 Year1 x y z
Company2 Year2 x y z
Company3 Year3 x y z
...etc

The size of the file is around 100MB, and as you can imagine, Excel takes way too long to do any useful activity.

Can anyone help me think of a more efficient way of organizing the data? I need to be able to apply statistical formulas to the raw data and to calculated data between the worksheets. For example, I may want to look at the trend of Income_Statement.Company1.YearX.Data1 / Balance_Sheet.Company1.YearX.Data2. I may also want to look at the trend of Income_Statement.Company1.YearX.Data1 over the past 5 years versus the trend of Income_Statement.Company1.YearX.Data2 over the same period.

It seems like Access would be a better choice than Excel, but I don't know if it can handle the data analysis I need to do.

Thanks in advance for your help.

In general, it is much better to place all of the data in a single range on a single worksheet when you can (whenever the structure is the same.)

I don't know what calculations you are doing, or what Data1, Data2, etc are, but by placing the data on a single sheet, you can easily take advantage of features like filtering and pivot tables. And of course, simple division is a bit easier too.

I don't know what Data1 Data2 Data3 are, and they are probably fine, but since I don't know what they are, I will mention that it is easy to fall into using for column labels what should be data in a column. For example, if I sold vegetables on the side of the highway, I would want a field for Item and another for Quantity. Not a field for Corn and a different field for Beans, etc. Again, I dont knowyour data and I really doubt you need to change this, but I thought I should mention it.