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]

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

 

[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?