Automatic array determination upon criterea

[Danielvk93]

Hi,

I have been looking ages for this solution in order to complete my thesis. I have a dataset with daily stock prices of many firms. The firms however are listed under each other, and therefore I need to determine the array upon a critera.

For my first BETA calculation, i have used the following formula:

=COVAR.S(F3:F2267;H3:H2267)/VAR.S(H3:H2267)

Now I need a formula, which automatically can let excel put =COVAR.S(F2268:XXX;H2267:XXX)/VAR.S(H2267:XXX) till my last observation. I have empty cells splitting the different firms which could be linked to.

I really need help with this, hopefully someone can help me out.


Thanks

 

[Aladin Akyurek]

Do you mean that the ranges grow and the formula should adjust itself to the changing ranges?

 

[Danielvk93]

Yes, the ranges are adjust, not every company has equal data points. The only thing that seperates the firms between rows are the empty cells i created, so i coud put a 1 for every new observation in the column.

 

[Aladin Akyurek]

Attachments lists a few methods for posting sample exhibits which do not require retyping.

 

[Danielvk93]

PERMNO	Date	Name	Price	Return	Benchmark	Return Bench	Beta
1	3-1-2007	XXX	50.62		1126.21		3.30487575
1	4-1-2007	XXX	49.68	-1.86%	1132.17	0.53%
1	5-1-2007	XXX	49.2	-0.97%	1141.81	0.85%
1	8-1-2007	XXX	48.9	-0.61%	1150.57	0.77%
2	2-2-2007	AAA	46.83		1148.16
2	5-2-2007	AAA	47.05	0.47%	1140.53	-0.66%	-0.954285894
2	6-2-2007	AAA	46.32	-1.55%	1139.32	-0.11%
2	7-2-2007	AAA	47.49	2.53%	1139.32	0.00%
2	8-2-2007	AAA	45.11	-5.01%	1145.2	0.52%
2	2-2-2007	AAA	44.85	-0.58%	1129.44	-1.38%
2	5-2-2007	AAA	44.57	-0.62%	1128.17	-0.11%
2	6-2-2007	AAA	45.9	2.98%	1128.84	0.06%
3	22-1-1997	BBB	45.55		1134.61
3	23-1-1997	BBB	45.8	0.55%	1135.82	0.11%
3	24-1-1997	BBB	46.01	0.46%	1118.15	-1.56%
4	17-5-2004	CCC	46.63		1124.09
4	18-5-2004	CCC	45.5	-2.42%	1139.93	1.41%
4	19-5-2004	CCC	46.07	1.25%	1140.6	0.06%
4	20-5-2004	CCC	45.41	-1.43%	1135.53	-0.44%
4	21-5-2004	CCC	44.48	-2.05%	1138.11	0.23%
4	22-5-2004	CCC	45.71	2.77%	1122.41	-1.38%
4	23-5-2004	CCC	46.9	2.60%	1113.89	-0.76%
4	24-5-2004	CCC	45.71	-2.54%	1107.3	-0.59%
4	25-5-2004	CCC	46.795	2.37%	1117.49	0.92%

<colgroup><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[Danielvk93]

I have to calculate the column Beta with the formula posted above. As you can see the array varies whenever the next company starts.

 

[Aladin Akyurek]

I have to calculate the column Beta with the formula posted above. As you can see the array varies whenever the next company starts.

Assuming that the exhibit you posted is in A:H, how does

3.30487575

obtain?

 

[Danielvk93]

=covariantie.s(e4:e6;g4:g6)/var.s(g4:g6)

 

[Aladin Akyurek]

=covariantie.s(e4:e6;g4:g6)/var.s(g4:g6)

With:

=COVARIANCE.S(E4:E6,G4:G6)/VAR.S(G4:G6)

I get:

3.34134615384615

for the sample you posted.

Why is that?