Calculating returns over variable holding periods

brjohnson · Jun 6, 2011

Hi all,

I'm building a worksheet to analyze the stock market, specifically the performance of a number of symbols between two variable dates in time.

I'm struggling with how to implement a system that allows for a variable start and end date for the backtest period as well as a variable holding period for the stocks.

Specifically, if I set start and end dates as 1/1/2000 and 1/1/2010, a backtest period of 3 months and a holding period of 1 months: I'm taking my 20 ticker symbols, calculating their performance with my system over the 3 months prior to 1/1/2000 (resulting in a percentage gain for each symbol). The sheet then choses the best X number of these. It owns them for the variable holding period, in this example 1 month. It calculates the average return for that holding period and then repeats.

I had no difficulty setting up a static chart with these inputs - but to make the holding period variable seems to have moved beyond my expertise as I am simply unable to make sense of how to capture the % gain for the variable holding period going forward. Using start date + 1 month is easy to establish the first date for calculation but going beyond that... ugh.

Any ideas here would be immensely appreciated!

Thanks,

Brian

Andrew Fergus · Jun 9, 2011

Hi Brian

I suspect your question hasn't been answered because it is difficult to understand and/or to visualise what you want.

Are you able to provide an example of what you currently have, what you want and also how you currently calculate the return for a one month holding period?

Andrew

brjohnson · Jun 11, 2011

Andrew -

Thank you for the follow up and the encouragement to explain this better. I've included below a very small example of what I'm trying to accomplish. You'll see here a 3 stock test of the system over a short time period. In reality I'm using 20 stocks (choosing anywhere from 2-10 best) and an 11 year time span.

In the example below I determine, on a monthly basis, the best 2 stocks of the 3 (ranking by absolute performance the prior month). Once that monthly performance is determined and the best 2 stocks are picked, I need to fill that data into a daily table, entering only the picked stock results into the table.

My problem has been in finding the formula that will serve to fill the data into that daily table (cells B20:D53). I need an if statement which says - if rank = 1 or 2 then fill in the daily performance. The piece of that statement I can't figure out is how to replicate that throughout the month of February and then know to switch to March when the time comes.

I'm not sure if the following info will make a difference in the formulas necessary or not but just in case. In the final version the following inputs all become variable:
Lookback length: Currently monthly in the example - could be 1 month - 6 months
Holding period: currently monthly in the example - could be 15 days - 6 months
Number of stocks held: currently two - could be 1-10
Size of stock universe: currently three - could be 20

Uploaded with ImageShack.us

brjohnson · Jun 11, 2011

Realized I was still a bit confusing in my explanation. As per the second table, you'll see that just GOOG and AAPL are chosen for February, and it switches to GOOG and YHOO for March. It is this mechanic that I need to solve. How to take the chosen stocks and fill them in automatically.

I also realized that the holding period of one month, beginning on the 1st of the month may result in an easier solution than a variable holding period. I really need to be able to execute on a number of holding periods. A 20 day holding period for example could result in holding the best 2 stocks in portions of 2 months...

For those who see this and aren't answering - please let me know what else I need to include to make this easier to understand. Help me, help you, help me

brjohnson · Jun 11, 2011

Here is the html for the excel sheet. Note that I hid some of the Feb daily data so the HTML maker would work.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

1

Stock Price

Performance

Rank

2

GOOG

AAPL

YHOO

GOOG

AAPL

YHOO

GOOG

AAPL

YHOO

3

1/1/2009

307.65

85.35

12.2

4

2/1/2009

338.53

90.13

11.73

10%

6%

-4%

1

2

3

5

3/1/2009

337.99

89.31

13.23

0%

-1%

13%

2

3

1

6

4/1/2009

354.09

108.69

12.75

5%

22%

-4%

2

1

3

7

5/1/2009

393.69

127.24

14.14

11%

17%

11%

2

1

3

8

6/1/2009

426.56

139.35

16.58

8%

10%

17%

3

2

1

9

7/1/2009

418.99

142.83

15.41

-2%

2%

-7%

2

1

3

10

8/1/2009

443.05

163.39

14.32

6%

14%

-7%

2

1

3

11

9/1/2009

455.761

165.298

14.18

3%

1%

-1%

1

2

3

12

10/1/2009

487.2

180.86

17.39

7%

9%

23%

3

2

1

13

11/1/2009

536.12

188.5

15.9

10%

4%

-9%

1

2

3

14

12/1/2009

589.87

196.97

15.13

10%

4%

-5%

1

2

3

15

1/1/2010

619.98

210.732

16.78

5%

7%

11%

3

2

1

Sheet1

Excel 2007

Cell Formulas
Range	Formula
B3	=xlqhClose(B$2,$A3)
B4	=xlqhClose(B$2,$A4)
B5	=xlqhClose(B$2,$A5)
B6	=xlqhClose(B$2,$A6)
B7	=xlqhClose(B$2,$A7)
B8	=xlqhClose(B$2,$A8)
B9	=xlqhClose(B$2,$A9)
B10	=xlqhClose(B$2,$A10)
B11	=xlqhClose(B$2,$A11)
B12	=xlqhClose(B$2,$A12)
B13	=xlqhClose(B$2,$A13)
B14	=xlqhClose(B$2,$A14)
B15	=xlqhClose(B$2,$A15)
C3	=xlqhClose(C$2,$A3)
C4	=xlqhClose(C$2,$A4)
C5	=xlqhClose(C$2,$A5)
C6	=xlqhClose(C$2,$A6)
C7	=xlqhClose(C$2,$A7)
C8	=xlqhClose(C$2,$A8)
C9	=xlqhClose(C$2,$A9)
C10	=xlqhClose(C$2,$A10)
C11	=xlqhClose(C$2,$A11)
C12	=xlqhClose(C$2,$A12)
C13	=xlqhClose(C$2,$A13)
C14	=xlqhClose(C$2,$A14)
C15	=xlqhClose(C$2,$A15)
D3	=xlqhClose(D$2,$A3)
D4	=xlqhClose(D$2,$A4)
D5	=xlqhClose(D$2,$A5)
D6	=xlqhClose(D$2,$A6)
D7	=xlqhClose(D$2,$A7)
D8	=xlqhClose(D$2,$A8)
D9	=xlqhClose(D$2,$A9)
D10	=xlqhClose(D$2,$A10)
D11	=xlqhClose(D$2,$A11)
D12	=xlqhClose(D$2,$A12)
D13	=xlqhClose(D$2,$A13)
D14	=xlqhClose(D$2,$A14)
D15	=xlqhClose(D$2,$A15)
F4	=(B4-B3)/B3
F5	=(B5-B4)/B4
F6	=(B6-B5)/B5
F7	=(B7-B6)/B6
F8	=(B8-B7)/B7
F9	=(B9-B8)/B8
F10	=(B10-B9)/B9
F11	=(B11-B10)/B10
F12	=(B12-B11)/B11
F13	=(B13-B12)/B12
F14	=(B14-B13)/B13
F15	=(B15-B14)/B14
G4	=(C4-C3)/C3
G5	=(C5-C4)/C4
G6	=(C6-C5)/C5
G7	=(C7-C6)/C6
G8	=(C8-C7)/C7
G9	=(C9-C8)/C8
G10	=(C10-C9)/C9
G11	=(C11-C10)/C10
G12	=(C12-C11)/C11
G13	=(C13-C12)/C12
G14	=(C14-C13)/C13
G15	=(C15-C14)/C14
H4	=(D4-D3)/D3
H5	=(D5-D4)/D4
H6	=(D6-D5)/D5
H7	=(D7-D6)/D6
H8	=(D8-D7)/D7
H9	=(D9-D8)/D8
H10	=(D10-D9)/D9
H11	=(D11-D10)/D10
H12	=(D12-D11)/D11
H13	=(D13-D12)/D12
H14	=(D14-D13)/D13
H15	=(D15-D14)/D14
J4	=RANK(F4,$F4:$H4)
J5	=RANK(F5,$F5:$H5)
J6	=RANK(F6,$F6:$H6)
J7	=RANK(F7,$F7:$H7)
J8	=RANK(F8,$F8:$H8)
J9	=RANK(F9,$F9:$H9)
J10	=RANK(F10,$F10:$H10)
J11	=RANK(F11,$F11:$H11)
J12	=RANK(F12,$F12:$H12)
J13	=RANK(F13,$F13:$H13)
J14	=RANK(F14,$F14:$H14)
J15	=RANK(F15,$F15:$H15)
K4	=RANK(G4,$F4:$H4)
K5	=RANK(G5,$F5:$H5)
K6	=RANK(G6,$F6:$H6)
K7	=RANK(G7,$F7:$H7)
K8	=RANK(G8,$F8:$H8)
K9	=RANK(G9,$F9:$H9)
K10	=RANK(G10,$F10:$H10)
K11	=RANK(G11,$F11:$H11)
K12	=RANK(G12,$F12:$H12)
K13	=RANK(G13,$F13:$H13)
K14	=RANK(G14,$F14:$H14)
K15	=RANK(G15,$F15:$H15)
L4	=RANK(H4,$F4:$H4)
L5	=RANK(H5,$F5:$H5)
L6	=RANK(H6,$F6:$H6)
L7	=RANK(H7,$F7:$H7)
L8	=RANK(H8,$F8:$H8)
L9	=RANK(H9,$F9:$H9)
L10	=RANK(H10,$F10:$H10)
L11	=RANK(H11,$F11:$H11)
L12	=RANK(H12,$F12:$H12)
L13	=RANK(H13,$F13:$H13)
L14	=RANK(H14,$F14:$H14)
L15	=RANK(H15,$F15:$H15)

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

17

Daily Performance Total

Daily Performance Individual (Price)

Daily Performance Individual (% Gain)

18

GOOG

AAPL

YHOO

GOOG

AAPL

YHOO

Average

GOOG

AAPL

YHOO

19

1/31/2009

338.53

90.13

11.73

20

2/1/2009

0.00%

338.53

90.13

11.73

0.00%

21

2/2/2009

0.60%

1.53%

340.57

91.51

12.15

0.60%

1.53%

3.58%

22

2/3/2009

-0.04%

1.61%

340.45

92.98

12.68

-0.04%

1.61%

4.36%

23

2/4/2009

0.75%

0.61%

343

93.55

13

0.75%

0.61%

2.52%

24

2/5/2009

3.13%

3.11%

353.72

96.46

13.51

3.13%

3.11%

3.92%

25

2/6/2009

4.96%

3.38%

371.28

99.72

13.63

4.96%

3.38%

0.89%

26

2/7/2009

0.00%

371.28

99.72

13.63

0.00%

27

2/8/2009

0.00%

371.28

99.72

13.63

0.00%

28

2/9/2009

2.02%

2.80%

378.77

102.51

13.9

2.02%

2.80%

1.98%

41

2/22/2009

0.00%

346.45

91.2

12.14

0.00%

42

2/23/2009

-4.73%

-4.66%

330.06

86.95

11.97

-4.73%

-4.66%

-1.40%

43

2/24/2009

4.66%

3.80%

345.45

90.25

12.75

4.66%

3.80%

6.52%

44

2/25/2009

-1.10%

1.01%

341.64

91.16

12.48

-1.10%

1.01%

-2.12%

45

2/26/2009

-1.31%

-2.16%

337.18

89.19

12.98

-1.31%

-2.16%

4.01%

46

2/27/2009

0.24%

0.13%

337.99

89.31

13.23

0.24%

0.13%

1.93%

47

2/28/2009

0.00%

337.99

89.31

13.23

0.00%

48

3/1/2009

0.00%

337.99

89.31

13.23

0.00%

49

3/2/2009

-3.20%

-4.91%

327.16

87.94

12.58

-3.20%

-1.53%

-4.91%

50

3/3/2009

-0.51%

-0.64%

325.48

88.37

12.5

-0.51%

0.49%

-0.64%

51

3/4/2009

-2.02%

5.28%

318.92

91.17

13.16

-2.02%

3.17%

5.28%

52

3/5/2009

-4.16%

-4.79%

305.64

88.84

12.53

-4.16%

-2.56%

-4.79%

53

3/6/2009

0.96%

4.15%

308.57

85.3

13.05

0.96%

-3.98%

4.15%

Sheet1

Excel 2007

Cell Formulas
Range	Formula
F19	=xlqhClose(F$18,$A19,"yahoo")
F20	=xlqhClose(F$18,$A20,"yahoo")
F21	=xlqhClose(F$18,$A21,"yahoo")
F22	=xlqhClose(F$18,$A22,"yahoo")
F23	=xlqhClose(F$18,$A23,"yahoo")
F24	=xlqhClose(F$18,$A24,"yahoo")
F25	=xlqhClose(F$18,$A25,"yahoo")
F26	=xlqhClose(F$18,$A26,"yahoo")
F27	=xlqhClose(F$18,$A27,"yahoo")
F28	=xlqhClose(F$18,$A28,"yahoo")
F41	=xlqhClose(F$18,$A41,"yahoo")
F42	=xlqhClose(F$18,$A42,"yahoo")
F43	=xlqhClose(F$18,$A43,"yahoo")
F44	=xlqhClose(F$18,$A44,"yahoo")
F45	=xlqhClose(F$18,$A45,"yahoo")
F46	=xlqhClose(F$18,$A46,"yahoo")
F47	=xlqhClose(F$18,$A47,"yahoo")
F48	=xlqhClose(F$18,$A48,"yahoo")
F49	=xlqhClose(F$18,$A49,"yahoo")
F50	=xlqhClose(F$18,$A50,"yahoo")
F51	=xlqhClose(F$18,$A51,"yahoo")
F52	=xlqhClose(F$18,$A52,"yahoo")
F53	=xlqhClose(F$18,$A53,"yahoo")
G19	=xlqhClose(G$18,$A19,"yahoo")
G20	=xlqhClose(G$18,$A20,"yahoo")
G21	=xlqhClose(G$18,$A21,"yahoo")
G22	=xlqhClose(G$18,$A22,"yahoo")
G23	=xlqhClose(G$18,$A23,"yahoo")
G24	=xlqhClose(G$18,$A24,"yahoo")
G25	=xlqhClose(G$18,$A25,"yahoo")
G26	=xlqhClose(G$18,$A26,"yahoo")
G27	=xlqhClose(G$18,$A27,"yahoo")
G28	=xlqhClose(G$18,$A28,"yahoo")
G41	=xlqhClose(G$18,$A41,"yahoo")
G42	=xlqhClose(G$18,$A42,"yahoo")
G43	=xlqhClose(G$18,$A43,"yahoo")
G44	=xlqhClose(G$18,$A44,"yahoo")
G45	=xlqhClose(G$18,$A45,"yahoo")
G46	=xlqhClose(G$18,$A46,"yahoo")
G47	=xlqhClose(G$18,$A47,"yahoo")
G48	=xlqhClose(G$18,$A48,"yahoo")
G49	=xlqhClose(G$18,$A49,"yahoo")
G50	=xlqhClose(G$18,$A50,"yahoo")
G51	=xlqhClose(G$18,$A51,"yahoo")
G52	=xlqhClose(G$18,$A52,"yahoo")
G53	=xlqhClose(G$18,$A53,"yahoo")
H19	=xlqhClose(H$18,$A19,"yahoo")
H20	=xlqhClose(H$18,$A20,"yahoo")
H21	=xlqhClose(H$18,$A21,"yahoo")
H22	=xlqhClose(H$18,$A22,"yahoo")
H23	=xlqhClose(H$18,$A23,"yahoo")
H24	=xlqhClose(H$18,$A24,"yahoo")
H25	=xlqhClose(H$18,$A25,"yahoo")
H26	=xlqhClose(H$18,$A26,"yahoo")
H27	=xlqhClose(H$18,$A27,"yahoo")
H28	=xlqhClose(H$18,$A28,"yahoo")
H41	=xlqhClose(H$18,$A41,"yahoo")
H42	=xlqhClose(H$18,$A42,"yahoo")
H43	=xlqhClose(H$18,$A43,"yahoo")
H44	=xlqhClose(H$18,$A44,"yahoo")
H45	=xlqhClose(H$18,$A45,"yahoo")
H46	=xlqhClose(H$18,$A46,"yahoo")
H47	=xlqhClose(H$18,$A47,"yahoo")
H48	=xlqhClose(H$18,$A48,"yahoo")
H49	=xlqhClose(H$18,$A49,"yahoo")
H50	=xlqhClose(H$18,$A50,"yahoo")
H51	=xlqhClose(H$18,$A51,"yahoo")
H52	=xlqhClose(H$18,$A52,"yahoo")
H53	=xlqhClose(H$18,$A53,"yahoo")
B20	=K20
B21	=K21
B22	=K22
B23	=K23
B24	=K24
B25	=K25
B26	=K26
B27	=K27
B28	=K28
B41	=K41
B42	=K42
B43	=K43
B44	=K44
B45	=K45
B46	=K46
B47	=K47
B48	=K48
B49	=K49
B50	=K50
B51	=K51
B52	=K52
B53	=K53
C20	=L20
C21	=L21
C22	=L22
C23	=L23
C24	=L24
C25	=L25
C26	=L26
C27	=L27
C28	=L28
C41	=L41
C42	=L42
C43	=L43
C44	=L44
C45	=L45
C46	=L46
C47	=L47
K20	=(F20-F19)/F19
K21	=(F21-F20)/F20
K22	=(F22-F21)/F21
K23	=(F23-F22)/F22
K24	=(F24-F23)/F23
K25	=(F25-F24)/F24
K26	=(F26-F25)/F25
K27	=(F27-F26)/F26
K28	=(F28-F27)/F27
K41	=(F41-F40)/F40
K42	=(F42-F41)/F41
K43	=(F43-F42)/F42
K44	=(F44-F43)/F43
K45	=(F45-F44)/F44
K46	=(F46-F45)/F45
K47	=(F47-F46)/F46
K48	=(F48-F47)/F47
K49	=(F49-F48)/F48
K50	=(F50-F49)/F49
K51	=(F51-F50)/F50
K52	=(F52-F51)/F51
K53	=(F53-F52)/F52
L20	=(G20-G19)/G19
L21	=(G21-G20)/G20
L22	=(G22-G21)/G21
L23	=(G23-G22)/G22
L24	=(G24-G23)/G23
L25	=(G25-G24)/G24
L26	=(G26-G25)/G25
L27	=(G27-G26)/G26
L28	=(G28-G27)/G27
L41	=(G41-G40)/G40
L42	=(G42-G41)/G41
L43	=(G43-G42)/G42
L44	=(G44-G43)/G43
L45	=(G45-G44)/G44
L46	=(G46-G45)/G45
L47	=(G47-G46)/G46
L48	=(G48-G47)/G47
L49	=(G49-G48)/G48
L50	=(G50-G49)/G49
L51	=(G51-G50)/G50
L52	=(G52-G51)/G51
L53	=(G53-G52)/G52
M20	=(H20-H19)/H19
M21	=(H21-H20)/H20
M22	=(H22-H21)/H21
M23	=(H23-H22)/H22
M24	=(H24-H23)/H23
M25	=(H25-H24)/H24
M26	=(H26-H25)/H25
M27	=(H27-H26)/H26
M28	=(H28-H27)/H27
M41	=(H41-H40)/H40
M42	=(H42-H41)/H41
M43	=(H43-H42)/H42
M44	=(H44-H43)/H43
M45	=(H45-H44)/H44
M46	=(H46-H45)/H45
M47	=(H47-H46)/H46
M48	=(H48-H47)/H47
M49	=(H49-H48)/H48
M50	=(H50-H49)/H49
M51	=(H51-H50)/H50
M52	=(H52-H51)/H51
M53	=(H53-H52)/H52
D48	=M48
D49	=M49
D50	=M50
D51	=M51
D52	=M52
D53	=M53

Andrew Fergus · Jun 12, 2011

If I am understanding this correctly, given the data is extracted automatically using your existing formulas, you just want to be able to have the values in cells F18:H18 set automatically. Is that correct? And the top stocks are based on the values in rows 3 to 15 on the same sheet?

And once those values have been set, you only want to show the top 2 items in columns B:D? And which two are shown will change each month.....you might have a period of time that spans at least one month end......is that right?

Andrew

brjohnson · Jun 23, 2011

Andrew - Thanks for the follow up. I was finally able to figure this out so no more issues with this problem any longer.

Calculating returns over variable holding periods

brjohnson

New Member

Excel Facts

Andrew Fergus

MrExcel MVP

brjohnson

New Member

brjohnson

New Member

brjohnson

New Member

Andrew Fergus

MrExcel MVP

brjohnson

New Member

Similar threads

Forum statistics

Share this page

Calculating returns over variable holding periods

brjohnson

New Member

Excel Facts

Andrew Fergus

MrExcel MVP

brjohnson

New Member

brjohnson

New Member

brjohnson

New Member

Andrew Fergus

MrExcel MVP

brjohnson

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock