The spreadsheet details are shown in the following tables—representative data is shown in Table 1 and the cell functions are displayed in Table 2.(15) Only the portion of the spreadsheet relating to stock-A is shown—a similar set of adjacent cells on the same spreadsheet is used for stock-B.
Table 1. The Input (parameter) section of the spreadsheet is shown at the top, and the first nine rows of the Output section are shown in the bottom half of the Table.
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
|
|---|---|---|---|---|---|---|---|---|---|
1 |
Phase-Sensitive Stock-Price Simulator |
||||||||
2 |
12 Mar 99 |
||||||||
|
3 |
Stock A |
Phase 1 |
0 degrees |
||||||
|
4 |
Input: |
2 |
45 degrees |
||||||
|
5 |
Periods/cycle (N) |
52 |
3 |
90 degrees |
|||||
6 |
Phase (choose, 1 to 8) (f) |
5 |
4 |
135 degrees |
|||||
|
7 |
Share price, initial (p0) |
$15.00 |
5 |
180 degrees |
|||||
8 |
Growth rate per period % (g) |
0.50% |
6 |
225 degrees |
|||||
9 |
Saw tooth multiplier % (s) |
120% |
7 |
270 degrees |
|||||
|
10 |
Randomness % (r) |
50% |
8 |
315 degrees |
|||||
|
11 |
|||||||||
12 |
Output: |
||||||||
13 |
Standard deviation-A |
$6.29 |
|||||||
14 |
Correlation coefficient (rA,B) |
-0.552 |
|||||||
15 |
Final |
||||||||
|
16 |
Period |
Offset |
ModNorm |
Sawtooth |
RandNo |
Price |
|||
|
17 |
tn |
Fn |
Mn |
Stn |
Rn |
pgn |
psn |
prn |
A |
|
18 |
0 |
26 |
0.5000 |
0.0000 |
0.0000 |
$15.00 |
$0.00 |
$0.00 |
$15.00 |
|
19 |
1 |
27 |
0.5192 |
-0.0385 |
0.1499 |
$15.08 |
($0.69) |
$1.13 |
$15.52 |
|
20 |
2 |
28 |
0.5385 |
-0.0769 |
0.4607 |
$15.15 |
($1.38) |
$3.49 |
$17.26 |
|
21 |
3 |
29 |
0.5577 |
-0.1154 |
0.4307 |
$15.23 |
($2.08) |
$3.28 |
$16.43 |
|
A |
B |
C |
|
Period |
Offset |
ModNorm |
|
tn |
Fn |
Mn |
|
0 |
=A18+CHOOSE($E$6,0,$E$5/8,$E$5/4,3*$E$5/8, $E$5/2,5*$E$5/8,3*$E$5/4,7*$E$5/8) |
=MOD(B18,$E$5)/$E$5 |
|
1 |
=A19+CHOOSE($E$6,0,$E$5/8,$E$5/4,3*$E$5/8, $E$5/2,5*$E$5/8,3*$E$5/4,7*$E$5/8) |
=MOD(B19,$E$5)/$E$5 |
|
2 |
=A20+CHOOSE($E$6,0,$E$5/8,$E$5/4,3*$E$5/8, $E$5/2,5*$E$5/8,3*$E$5/4,7*$E$5/8) |
=MOD(B20,$E$5)/$E$5 |
|
D |
E |
|
Sawtooth |
RandNo |
|
Stn |
Rn |
|
=2*IF(C18<=0.25,C18,(IF(C18<=0.5,0.5-C18,(IF(C18<=0.75,0.5-C18,(IF(C18<=1,-1+C18))))))) |
0 |
|
=2*IF(C19<=0.25,C19,(IF(C19<=0.5,0.5-C19,(IF(C19<=0.75,0.5-C19,(IF(C19<=1,-1+C19))))))) |
=RAND()-0.5 |
|
=2*IF(C20<=0.25,C20,(IF(C20<=0.5,0.5-C20,(IF(C20<=0.75,0.5-C20,(IF(C20<=1,-1+C20))))))) |
=RAND()-0.5 |
|
F |
G |
H |
I |
|
Price growth |
Price-sawtooth |
Price-random |
Final Price |
|
pgn |
psn |
prn |
A |
|
=$E$7 |
=$E$9*D18*$E$7 |
=$E$10*E18*F18 |
=F18+G18+H18 |
|
=F18*(1+$E$8) |
=$E$9*D19*$E$7 |
=$E$10*E19*F19 |
=F19+G19+H19 |
|
=F19*(1+$E$8) |
=$E$9*D20*$E$7 |
=$E$10*E20*F20 |
=F20+G20+H20 |
(15) Tables 1 and 2 have been edited within the application Word, so that a mix of italics and Greek symbols can be shown. This ensures the data in the Tables correspond more closely to the parameters and functions described in the text.