Vba array

[pong]

I have a conceptual question about array. When do we use array in excel and why? Cux I am doing doing a project that requires me doing some calculation and generates the report on the other sheet. Basically, I got the purchases/ sales of securities at a certain price. Then i need to find out their total share , total value. With these information, i could calculate the cost of a sale ( cost of a sale = the average price of previous share purchases)

Then, i need to generate a report on the other sheet and give out the cost of sale of the securities.
Cux the report doesnt require total value of shares and total shares. I dont need to print them on the excel. So I was thinking it may be better to store them in an array. But it looks more complicated to this data and retrieve them. So I just put them somewhere in the report. But I still think there are mor efficient way to do this.

My question is
when should i use array to store data?

 

[pong]

Date          Name  Transaction Price 
31/12/2010	a	1000	10
3/1/2011	a	-200	11
20/2/2011	a	300	9.8
6/4/2011	a	-400	10.5
5/5/2011	a	500	9.5
31/12/2010	b	2000	9
5/1/2011	b	1000	8.9
30/4/2011	b	3000	8.8
6/5/2011	b	-2000	9.5
31/5/2011	b	-1000	9.9
31/12/2010	c	1000	10
19/1/2011	c	1000	9.75
3/3/2011	c	1000	9.5
8/4/2011	c	-1000	9.9
18/5/2011	c	-1000	10
31/12/2010	d	3000	6
28/1/2011	d	5000	5.9
15/3/2011	d	1000	5.8
10/4/2011	d	-2000	6.5
30/6/2011	d	-8000	6
20/5/2011	e	1000	5.5
1/6/2011	f	2000	10
28/6/2011	f	300	9.4

 

[pong]

Date          Name  Transaction Price 
31/12/2010	A	1100	10
3/1/2011	A	-210	11
20/2/2011	A	200	9.8
6/4/2011	A	-300	10.5
5/5/2011	A	500	9.5
31/12/2010	B	2000	9
5/1/2011	B	1000	8.9
30/4/2011	B	3000	8.8
6/5/2011	B	-2000	9.5
31/5/2011	B	-1000	9.9
31/12/2010	C	1000	10
19/1/2011	C	1000	9.75
3/3/2011	C	1000	9.5
8/4/2011	C	-1000	9.9
18/5/2011	C	-1000	10
31/12/2010	D	3000	6
28/1/2011	D	5000	5.9
15/3/2011	D	1000	5.8
10/4/2011	D	-2000	6.5
30/6/2011	D	-8000	6
20/5/2011	E	1000	5.5
1/6/2011	F	2000	10
28/6/2011	F	300	9.4

Date         Name        Shares Price    Accumulation cost             cost sold       total shares 
31/12/2010	A	1100	   10	        11000		                      1100
3/1/2011	A	-210	   11	         8900	              -2100            	890
20/2/2011	A	200	   9.8	        10860		                       1090
6/4/2011	A	-300	   10.5        7871          	-2988          	      790
5/5/2011	A	500	  9.5	       12621              		               1290

So this is the information for the the project. My job is to get the the cold sold for each category. For example , for a , there are two sales (two negative numbers). I need to get them for the report. For the first cost sold at A, the cost is 210/1100*11000=2100( the accumulation cost ) for the next one .After the sale , the accumulation cost =8900(11000-2100).

 

[pong]

for the third transaction the accumulation cost would be 10860(8900+1960)
so for the second sale (forth transaction ) 300/1090 * 10860 and the cost sold would be 2988.

so my task is to run a report to get all the cost sold
in this case for a, it is 2988+2100 =5088

Like this
Cost
A 5088

So i am not required to put the total shares, accumulation cost data on the excel
So where should i store these data cux i need them to get the cost sold data.
Should i put them into array or should I just put them next the price data and delete them with my code ?