Handeling large data

[Lumy]

Hello everyone. I need some advice in handeling a large amount of data in excel. I shall describe this quickly: I have a list of 420 stocks. For each of these 420 stocks, I have prices, currency, volume and several other types of data, for each day drom 01/01/2009 to 31/12/2013.

That is a lot of data.

My purpose is to summarise all this data to be used for some useful calculations. I'm not talking anything too complicated, just for example, getting the average price every stock on the list for those 4 yearsof trading. Or, another example, making a colum in which currency and volume are multiplied. Again, for each stock, for each day.

So the problem here is how to actually make these small, but numerous computations, and how to best organise the data so that it's easier to make those calculations.

So far, I have two files: the first has all the dates and types of data in colums, for each stock, and there is a 1 blank colum gap between each stock, and this carries on to the right side of the sheet. Everything is in one sheet only.

The second file has each stock's data seperated by tabs, with the name of each stock on the tabs (although, I can just have the tabs be sheet1,sheet2,etc).

Question 1: Considering the type of calculations I need to preform (mostly basic maths), which of the two file arrangements should I use so it is easier to programme macros?

Question 2: Given the fact that I have no idea how to actually code VBA or make macros that would fit this dimension of data, what would you suggest as an automatization system? For example, I was thinking if it was possible to mirror the actions done in one tab os data to ALL the other tabs in the file. That way, I could work only on one tab and then have all the actions already replicated in all the other tabs.

Thank you very much for all the help!!

[mikeTRON]

PowerPivot.

[Lumy]

Oh, I see. It looks very handy.
Could you suggest some learning material for begginers?
Also, with powerpivot, so I still have the need to make and run macros, or will it be able to organise all the data at once?

Oh, and still, which type of file is better to work with the pivot? The one with data in one tab or the wone with data seperated by tabs?

Thank you so much for the quick answer!

[mikeTRON]

http://msdn.microsoft.com/en-us/libr...=sql.110).aspx

http://www.powerpivotpro.com/categor...ivot-training/

https://www.youtube.com/watch?v=URy_uQYS49s

I cant understand what you are trying to accomplish but I build models off 20+MM records and it seems to work just fine for simple to complex calculations.

The add-in is free too btw.

[Lumy]

In the end, I need so summarize the sata so I can use it in a model. I will, for each stock, the sum of volume and prices, etc, so that I can insert it in a model, for example Sum(price)/sum(vol)+sum(returns)/sum(trades)=X

I will then put all the X values for all the stocks in one single tab and then I can calculate summary statistics, like mean, median, etc.

[mikeTRON]

How much data are you takling about?
How many rows? How many columns?

Do you only have excel 2003?
Are you familiar with pivot tables? they aggregate and allow for calculations as well.

[Lumy]

Around 1260 lines and 5992 collums. It's a lot of data.
Like I said, in one file all of the data is in one single sheet, with each stock seperated by a blank collum. The other has each stock in its own sheet.

Will the pivots tables work in this case?

[Lumy]

Ok, update. I've been looking around and apperently I can't install powerpivot. I only have excel 2007 on this PC and my other PC has office 2013 home edition.

So, I'll need to find another solution, it seems.