Calculating returns over variable holding periods

• Jun 6th, 2011, 12:18 PM
brjohnson
Calculating returns over variable holding periods
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
• Jun 9th, 2011, 10:40 PM
Andrew Fergus
Re: Calculating returns over variable holding periods
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
• Jun 11th, 2011, 11:49 AM
brjohnson
Re: Calculating returns over variable holding periods
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

http://img194.imageshack.us/img194/4830/mrexcel1.png

http://img199.imageshack.us/img199/8397/mrexcel2.png

• Jun 11th, 2011, 07:34 PM
brjohnson
Re: Calculating returns over variable holding periods
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 :)
• Jun 11th, 2011, 08:13 PM
brjohnson
Re: Calculating returns over variable holding periods
Here is the html for the excel sheet. Note that I hid some of the Feb daily data so the HTML maker would work.

<b>Sheet1</b><table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0"><thead><tr><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><th>1</th><td style=";">Stock Price</td><td></td><td></td><td></td><td></td><td style=";">Performance</td><td></td><td></td><td></td><td style=";">Rank</td><td></td><td></td></tr><tr ><th>2</th><td></td><td style=";">GOOG</td><td style=";">AAPL</td><td style=";">YHOO</td><td></td><td style=";">GOOG</td><td style=";">AAPL</td><td style=";">YHOO</td><td></td><td style=";">GOOG</td><td style=";">AAPL</td><td style=";">YHOO</td></tr><tr ><th>3</th><td style="text-align: right;;">1/1/2009</td><td style="text-align: right;;">307.65</td><td style="text-align: right;;">85.35</td><td style="text-align: right;;">12.2</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr ><th>4</th><td style="text-align: right;;">2/1/2009</td><td style="text-align: right;;">338.53</td><td style="text-align: right;;">90.13</td><td style="text-align: right;;">11.73</td><td></td><td style="text-align: right;;">10%</td><td style="text-align: right;;">6%</td><td style="text-align: right;;">-4%</td><td></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><th>5</th><td style="text-align: right;;">3/1/2009</td><td style="text-align: right;;">337.99</td><td style="text-align: right;;">89.31</td><td style="text-align: right;;">13.23</td><td></td><td style="text-align: right;;">0%</td><td style="text-align: right;;">-1%</td><td style="text-align: right;;">13%</td><td></td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><th>6</th><td style="text-align: right;;">4/1/2009</td><td style="text-align: right;;">354.09</td><td style="text-align: right;;">108.69</td><td style="text-align: right;;">12.75</td><td></td><td style="text-align: right;;">5%</td><td style="text-align: right;;">22%</td><td style="text-align: right;;">-4%</td><td></td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td></tr><tr ><th>7</th><td style="text-align: right;;">5/1/2009</td><td style="text-align: right;;">393.69</td><td style="text-align: right;;">127.24</td><td style="text-align: right;;">14.14</td><td></td><td style="text-align: right;;">11%</td><td style="text-align: right;;">17%</td><td style="text-align: right;;">11%</td><td></td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td></tr><tr ><th>8</th><td style="text-align: right;;">6/1/2009</td><td style="text-align: right;;">426.56</td><td style="text-align: right;;">139.35</td><td style="text-align: right;;">16.58</td><td></td><td style="text-align: right;;">8%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">17%</td><td></td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><th>9</th><td style="text-align: right;;">7/1/2009</td><td style="text-align: right;;">418.99</td><td style="text-align: right;;">142.83</td><td style="text-align: right;;">15.41</td><td></td><td style="text-align: right;;">-2%</td><td style="text-align: right;;">2%</td><td style="text-align: right;;">-7%</td><td></td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td></tr><tr ><th>10</th><td style="text-align: right;;">8/1/2009</td><td style="text-align: right;;">443.05</td><td style="text-align: right;;">163.39</td><td style="text-align: right;;">14.32</td><td></td><td style="text-align: right;;">6%</td><td style="text-align: right;;">14%</td><td style="text-align: right;;">-7%</td><td></td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td></tr><tr ><th>11</th><td style="text-align: right;;">9/1/2009</td><td style="text-align: right;;">455.761</td><td style="text-align: right;;">165.298</td><td style="text-align: right;;">14.18</td><td></td><td style="text-align: right;;">3%</td><td style="text-align: right;;">1%</td><td style="text-align: right;;">-1%</td><td></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><th>12</th><td style="text-align: right;;">10/1/2009</td><td style="text-align: right;;">487.2</td><td style="text-align: right;;">180.86</td><td style="text-align: right;;">17.39</td><td></td><td style="text-align: right;;">7%</td><td style="text-align: right;;">9%</td><td style="text-align: right;;">23%</td><td></td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><th>13</th><td style="text-align: right;;">11/1/2009</td><td style="text-align: right;;">536.12</td><td style="text-align: right;;">188.5</td><td style="text-align: right;;">15.9</td><td></td><td style="text-align: right;;">10%</td><td style="text-align: right;;">4%</td><td style="text-align: right;;">-9%</td><td></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><th>14</th><td style="text-align: right;;">12/1/2009</td><td style="text-align: right;;">589.87</td><td style="text-align: right;;">196.97</td><td style="text-align: right;;">15.13</td><td></td><td style="text-align: right;;">10%</td><td style="text-align: right;;">4%</td><td style="text-align: right;;">-5%</td><td></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><th>15</th><td style="text-align: right;;">1/1/2010</td><td style="text-align: right;;">619.98</td><td style="text-align: right;;">210.732</td><td style="text-align: right;;">16.78</td><td></td><td style="text-align: right;;">5%</td><td style="text-align: right;;">7%</td><td style="text-align: right;;">11%</td><td></td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr></tbody></table><b>Excel 2007</b><br /><br /><table ><tr><td style="padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;"><b>Worksheet Formulas</b><table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0" ><thead><tr><th >Cell</th><th>Formula</th></tr></thead><tbody><tr><th >B3</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A3</font>)</td></tr><tr><th >C3</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A3</font>)</td></tr><tr><th >D3</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A3</font>)</td></tr><tr><th >B4</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A4</font>)</td></tr><tr><th >C4</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A4</font>)</td></tr><tr><th >D4</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A4</font>)</td></tr><tr><th >B5</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A5</font>)</td></tr><tr><th >C5</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A5</font>)</td></tr><tr><th >D5</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A5</font>)</td></tr><tr><th >B6</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A6</font>)</td></tr><tr><th >C6</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A6</font>)</td></tr><tr><th >D6</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A6</font>)</td></tr><tr><th >B7</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A7</font>)</td></tr><tr><th >C7</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A7</font>)</td></tr><tr><th >D7</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A7</font>)</td></tr><tr><th >B8</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A8</font>)</td></tr><tr><th >C8</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A8</font>)</td></tr><tr><th >D8</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A8</font>)</td></tr><tr><th >B9</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A9</font>)</td></tr><tr><th >C9</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A9</font>)</td></tr><tr><th >D9</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A9</font>)</td></tr><tr><th >B10</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A10</font>)</td></tr><tr><th >C10</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A10</font>)</td></tr><tr><th >D10</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A10</font>)</td></tr><tr><th >B11</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A11</font>)</td></tr><tr><th >C11</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A11</font>)</td></tr><tr><th >D11</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A11</font>)</td></tr><tr><th >B12</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A12</font>)</td></tr><tr><th >C12</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A12</font>)</td></tr><tr><th >D12</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A12</font>)</td></tr><tr><th >B13</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A13</font>)</td></tr><tr><th >C13</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A13</font>)</td></tr><tr><th >D13</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A13</font>)</td></tr><tr><th >B14</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A14</font>)</td></tr><tr><th >C14</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A14</font>)</td></tr><tr><th >D14</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A14</font>)</td></tr><tr><th >B15</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">B\$2,\$A15</font>)</td></tr><tr><th >C15</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">C\$2,\$A15</font>)</td></tr><tr><th >D15</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">D\$2,\$A15</font>)</td></tr><tr><th >F4</th><td style="word-wrap: break-word">=(<font color="Blue">B4-B3</font>)/B3</td></tr><tr><th >G4</th><td style="word-wrap: break-word">=(<font color="Blue">C4-C3</font>)/C3</td></tr><tr><th >H4</th><td style="word-wrap: break-word">=(<font color="Blue">D4-D3</font>)/D3</td></tr><tr><th >F5</th><td style="word-wrap: break-word">=(<font color="Blue">B5-B4</font>)/B4</td></tr><tr><th >G5</th><td style="word-wrap: break-word">=(<font color="Blue">C5-C4</font>)/C4</td></tr><tr><th >H5</th><td style="word-wrap: break-word">=(<font color="Blue">D5-D4</font>)/D4</td></tr><tr><th >F6</th><td style="word-wrap: break-word">=(<font color="Blue">B6-B5</font>)/B5</td></tr><tr><th >G6</th><td style="word-wrap: break-word">=(<font color="Blue">C6-C5</font>)/C5</td></tr><tr><th >H6</th><td style="word-wrap: break-word">=(<font color="Blue">D6-D5</font>)/D5</td></tr><tr><th >F7</th><td style="word-wrap: break-word">=(<font color="Blue">B7-B6</font>)/B6</td></tr><tr><th >G7</th><td style="word-wrap: break-word">=(<font color="Blue">C7-C6</font>)/C6</td></tr><tr><th >H7</th><td style="word-wrap: break-word">=(<font color="Blue">D7-D6</font>)/D6</td></tr><tr><th >F8</th><td style="word-wrap: break-word">=(<font color="Blue">B8-B7</font>)/B7</td></tr><tr><th >G8</th><td style="word-wrap: break-word">=(<font color="Blue">C8-C7</font>)/C7</td></tr><tr><th >H8</th><td style="word-wrap: break-word">=(<font color="Blue">D8-D7</font>)/D7</td></tr><tr><th >F9</th><td style="word-wrap: break-word">=(<font color="Blue">B9-B8</font>)/B8</td></tr><tr><th >G9</th><td style="word-wrap: break-word">=(<font color="Blue">C9-C8</font>)/C8</td></tr><tr><th >H9</th><td style="word-wrap: break-word">=(<font color="Blue">D9-D8</font>)/D8</td></tr><tr><th >F10</th><td style="word-wrap: break-word">=(<font color="Blue">B10-B9</font>)/B9</td></tr><tr><th >G10</th><td style="word-wrap: break-word">=(<font color="Blue">C10-C9</font>)/C9</td></tr><tr><th >H10</th><td style="word-wrap: break-word">=(<font color="Blue">D10-D9</font>)/D9</td></tr><tr><th >F11</th><td style="word-wrap: break-word">=(<font color="Blue">B11-B10</font>)/B10</td></tr><tr><th >G11</th><td style="word-wrap: break-word">=(<font color="Blue">C11-C10</font>)/C10</td></tr><tr><th >H11</th><td style="word-wrap: break-word">=(<font color="Blue">D11-D10</font>)/D10</td></tr><tr><th >F12</th><td style="word-wrap: break-word">=(<font color="Blue">B12-B11</font>)/B11</td></tr><tr><th >G12</th><td style="word-wrap: break-word">=(<font color="Blue">C12-C11</font>)/C11</td></tr><tr><th >H12</th><td style="word-wrap: break-word">=(<font color="Blue">D12-D11</font>)/D11</td></tr><tr><th >F13</th><td style="word-wrap: break-word">=(<font color="Blue">B13-B12</font>)/B12</td></tr><tr><th >G13</th><td style="word-wrap: break-word">=(<font color="Blue">C13-C12</font>)/C12</td></tr><tr><th >H13</th><td style="word-wrap: break-word">=(<font color="Blue">D13-D12</font>)/D12</td></tr><tr><th >F14</th><td style="word-wrap: break-word">=(<font color="Blue">B14-B13</font>)/B13</td></tr><tr><th >G14</th><td style="word-wrap: break-word">=(<font color="Blue">C14-C13</font>)/C13</td></tr><tr><th >H14</th><td style="word-wrap: break-word">=(<font color="Blue">D14-D13</font>)/D13</td></tr><tr><th >F15</th><td style="word-wrap: break-word">=(<font color="Blue">B15-B14</font>)/B14</td></tr><tr><th >G15</th><td style="word-wrap: break-word">=(<font color="Blue">C15-C14</font>)/C14</td></tr><tr><th >H15</th><td style="word-wrap: break-word">=(<font color="Blue">D15-D14</font>)/D14</td></tr><tr><th >J4</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F4,\$F4:\$H4</font>)</td></tr><tr><th >K4</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G4,\$F4:\$H4</font>)</td></tr><tr><th >L4</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H4,\$F4:\$H4</font>)</td></tr><tr><th >J5</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F5,\$F5:\$H5</font>)</td></tr><tr><th >K5</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G5,\$F5:\$H5</font>)</td></tr><tr><th >L5</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H5,\$F5:\$H5</font>)</td></tr><tr><th >J6</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F6,\$F6:\$H6</font>)</td></tr><tr><th >K6</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G6,\$F6:\$H6</font>)</td></tr><tr><th >L6</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H6,\$F6:\$H6</font>)</td></tr><tr><th >J7</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F7,\$F7:\$H7</font>)</td></tr><tr><th >K7</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G7,\$F7:\$H7</font>)</td></tr><tr><th >L7</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H7,\$F7:\$H7</font>)</td></tr><tr><th >J8</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F8,\$F8:\$H8</font>)</td></tr><tr><th >K8</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G8,\$F8:\$H8</font>)</td></tr><tr><th >L8</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H8,\$F8:\$H8</font>)</td></tr><tr><th >J9</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F9,\$F9:\$H9</font>)</td></tr><tr><th >K9</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G9,\$F9:\$H9</font>)</td></tr><tr><th >L9</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H9,\$F9:\$H9</font>)</td></tr><tr><th >J10</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F10,\$F10:\$H10</font>)</td></tr><tr><th >K10</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G10,\$F10:\$H10</font>)</td></tr><tr><th >L10</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H10,\$F10:\$H10</font>)</td></tr><tr><th >J11</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F11,\$F11:\$H11</font>)</td></tr><tr><th >K11</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G11,\$F11:\$H11</font>)</td></tr><tr><th >L11</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H11,\$F11:\$H11</font>)</td></tr><tr><th >J12</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F12,\$F12:\$H12</font>)</td></tr><tr><th >K12</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G12,\$F12:\$H12</font>)</td></tr><tr><th >L12</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H12,\$F12:\$H12</font>)</td></tr><tr><th >J13</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F13,\$F13:\$H13</font>)</td></tr><tr><th >K13</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G13,\$F13:\$H13</font>)</td></tr><tr><th >L13</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H13,\$F13:\$H13</font>)</td></tr><tr><th >J14</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F14,\$F14:\$H14</font>)</td></tr><tr><th >K14</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G14,\$F14:\$H14</font>)</td></tr><tr><th >L14</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H14,\$F14:\$H14</font>)</td></tr><tr><th >J15</th><td style="word-wrap: break-word">=RANK(<font color="Blue">F15,\$F15:\$H15</font>)</td></tr><tr><th >K15</th><td style="word-wrap: break-word">=RANK(<font color="Blue">G15,\$F15:\$H15</font>)</td></tr><tr><th >L15</th><td style="word-wrap: break-word">=RANK(<font color="Blue">H15,\$F15:\$H15</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Sheet1</b><table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0"><thead><tr><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><th>17</th><td style=";">Daily Performance Total</td><td></td><td></td><td></td><td></td><td style=";">Daily Performance Individual (Price)</td><td></td><td></td><td></td><td></td><td style=";">Daily Performance Individual (% Gain)</td><td></td><td></td></tr><tr ><th>18</th><td></td><td style=";">GOOG</td><td style=";">AAPL</td><td style=";">YHOO</td><td></td><td style=";">GOOG</td><td style=";">AAPL</td><td style=";">YHOO</td><td style=";">Average</td><td></td><td style=";">GOOG</td><td style=";">AAPL</td><td style=";">YHOO</td></tr><tr ><th>19</th><td style="text-align: right;;">1/31/2009</td><td></td><td></td><td></td><td></td><td style="text-align: right;;">338.53</td><td style="text-align: right;;">90.13</td><td style="text-align: right;;">11.73</td><td></td><td></td><td></td><td></td><td></td></tr><tr ><th>20</th><td style="text-align: right;;">2/1/2009</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td></td><td></td><td style="text-align: right;;">338.53</td><td style="text-align: right;;">90.13</td><td style="text-align: right;;">11.73</td><td></td><td></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td></tr><tr ><th>21</th><td style="text-align: right;;">2/2/2009</td><td style="text-align: right;;">0.60%</td><td style="text-align: right;;">1.53%</td><td></td><td></td><td style="text-align: right;;">340.57</td><td style="text-align: right;;">91.51</td><td style="text-align: right;;">12.15</td><td></td><td></td><td style="text-align: right;;">0.60%</td><td style="text-align: right;;">1.53%</td><td style="text-align: right;;">3.58%</td></tr><tr ><th>22</th><td style="text-align: right;;">2/3/2009</td><td style="text-align: right;;">-0.04%</td><td style="text-align: right;;">1.61%</td><td></td><td></td><td style="text-align: right;;">340.45</td><td style="text-align: right;;">92.98</td><td style="text-align: right;;">12.68</td><td></td><td></td><td style="text-align: right;;">-0.04%</td><td style="text-align: right;;">1.61%</td><td style="text-align: right;;">4.36%</td></tr><tr ><th>23</th><td style="text-align: right;;">2/4/2009</td><td style="text-align: right;;">0.75%</td><td style="text-align: right;;">0.61%</td><td></td><td></td><td style="text-align: right;;">343</td><td style="text-align: right;;">93.55</td><td style="text-align: right;;">13</td><td></td><td></td><td style="text-align: right;;">0.75%</td><td style="text-align: right;;">0.61%</td><td style="text-align: right;;">2.52%</td></tr><tr ><th>24</th><td style="text-align: right;;">2/5/2009</td><td style="text-align: right;;">3.13%</td><td style="text-align: right;;">3.11%</td><td></td><td></td><td style="text-align: right;;">353.72</td><td style="text-align: right;;">96.46</td><td style="text-align: right;;">13.51</td><td></td><td></td><td style="text-align: right;;">3.13%</td><td style="text-align: right;;">3.11%</td><td style="text-align: right;;">3.92%</td></tr><tr ><th>25</th><td style="text-align: right;;">2/6/2009</td><td style="text-align: right;;">4.96%</td><td style="text-align: right;;">3.38%</td><td></td><td></td><td style="text-align: right;;">371.28</td><td style="text-align: right;;">99.72</td><td style="text-align: right;;">13.63</td><td></td><td></td><td style="text-align: right;;">4.96%</td><td style="text-align: right;;">3.38%</td><td style="text-align: right;;">0.89%</td></tr><tr ><th>26</th><td style="text-align: right;;">2/7/2009</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td></td><td></td><td style="text-align: right;;">371.28</td><td style="text-align: right;;">99.72</td><td style="text-align: right;;">13.63</td><td></td><td></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td></tr><tr ><th>27</th><td style="text-align: right;;">2/8/2009</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td></td><td></td><td style="text-align: right;;">371.28</td><td style="text-align: right;;">99.72</td><td style="text-align: right;;">13.63</td><td></td><td></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td></tr><tr ><th>28</th><td style="text-align: right;;">2/9/2009</td><td style="text-align: right;;">2.02%</td><td style="text-align: right;;">2.80%</td><td></td><td></td><td style="text-align: right;;">378.77</td><td style="text-align: right;;">102.51</td><td style="text-align: right;;">13.9</td><td></td><td></td><td style="text-align: right;;">2.02%</td><td style="text-align: right;;">2.80%</td><td style="text-align: right;;">1.98%</td></tr><tr ><th>41</th><td style="text-align: right;;">2/22/2009</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td></td><td></td><td style="text-align: right;;">346.45</td><td style="text-align: right;;">91.2</td><td style="text-align: right;;">12.14</td><td></td><td></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td></tr><tr ><th>42</th><td style="text-align: right;;">2/23/2009</td><td style="text-align: right;;">-4.73%</td><td style="text-align: right;;">-4.66%</td><td></td><td></td><td style="text-align: right;;">330.06</td><td style="text-align: right;;">86.95</td><td style="text-align: right;;">11.97</td><td></td><td></td><td style="text-align: right;;">-4.73%</td><td style="text-align: right;;">-4.66%</td><td style="text-align: right;;">-1.40%</td></tr><tr ><th>43</th><td style="text-align: right;;">2/24/2009</td><td style="text-align: right;;">4.66%</td><td style="text-align: right;;">3.80%</td><td></td><td></td><td style="text-align: right;;">345.45</td><td style="text-align: right;;">90.25</td><td style="text-align: right;;">12.75</td><td></td><td></td><td style="text-align: right;;">4.66%</td><td style="text-align: right;;">3.80%</td><td style="text-align: right;;">6.52%</td></tr><tr ><th>44</th><td style="text-align: right;;">2/25/2009</td><td style="text-align: right;;">-1.10%</td><td style="text-align: right;;">1.01%</td><td></td><td></td><td style="text-align: right;;">341.64</td><td style="text-align: right;;">91.16</td><td style="text-align: right;;">12.48</td><td></td><td></td><td style="text-align: right;;">-1.10%</td><td style="text-align: right;;">1.01%</td><td style="text-align: right;;">-2.12%</td></tr><tr ><th>45</th><td style="text-align: right;;">2/26/2009</td><td style="text-align: right;;">-1.31%</td><td style="text-align: right;;">-2.16%</td><td></td><td></td><td style="text-align: right;;">337.18</td><td style="text-align: right;;">89.19</td><td style="text-align: right;;">12.98</td><td></td><td></td><td style="text-align: right;;">-1.31%</td><td style="text-align: right;;">-2.16%</td><td style="text-align: right;;">4.01%</td></tr><tr ><th>46</th><td style="text-align: right;;">2/27/2009</td><td style="text-align: right;;">0.24%</td><td style="text-align: right;;">0.13%</td><td></td><td></td><td style="text-align: right;;">337.99</td><td style="text-align: right;;">89.31</td><td style="text-align: right;;">13.23</td><td></td><td></td><td style="text-align: right;;">0.24%</td><td style="text-align: right;;">0.13%</td><td style="text-align: right;;">1.93%</td></tr><tr ><th>47</th><td style="text-align: right;;">2/28/2009</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td></td><td></td><td style="text-align: right;;">337.99</td><td style="text-align: right;;">89.31</td><td style="text-align: right;;">13.23</td><td></td><td></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td></tr><tr ><th>48</th><td style="text-align: right;;">3/1/2009</td><td style="text-align: right;;">0.00%</td><td></td><td style="text-align: right;;">0.00%</td><td></td><td style="text-align: right;;">337.99</td><td style="text-align: right;;">89.31</td><td style="text-align: right;;">13.23</td><td></td><td></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">0.00%</td></tr><tr ><th>49</th><td style="text-align: right;;">3/2/2009</td><td style="text-align: right;;">-3.20%</td><td></td><td style="text-align: right;;">-4.91%</td><td></td><td style="text-align: right;;">327.16</td><td style="text-align: right;;">87.94</td><td style="text-align: right;;">12.58</td><td></td><td></td><td style="text-align: right;;">-3.20%</td><td style="text-align: right;;">-1.53%</td><td style="text-align: right;;">-4.91%</td></tr><tr ><th>50</th><td style="text-align: right;;">3/3/2009</td><td style="text-align: right;;">-0.51%</td><td></td><td style="text-align: right;;">-0.64%</td><td></td><td style="text-align: right;;">325.48</td><td style="text-align: right;;">88.37</td><td style="text-align: right;;">12.5</td><td></td><td></td><td style="text-align: right;;">-0.51%</td><td style="text-align: right;;">0.49%</td><td style="text-align: right;;">-0.64%</td></tr><tr ><th>51</th><td style="text-align: right;;">3/4/2009</td><td style="text-align: right;;">-2.02%</td><td></td><td style="text-align: right;;">5.28%</td><td></td><td style="text-align: right;;">318.92</td><td style="text-align: right;;">91.17</td><td style="text-align: right;;">13.16</td><td></td><td></td><td style="text-align: right;;">-2.02%</td><td style="text-align: right;;">3.17%</td><td style="text-align: right;;">5.28%</td></tr><tr ><th>52</th><td style="text-align: right;;">3/5/2009</td><td style="text-align: right;;">-4.16%</td><td></td><td style="text-align: right;;">-4.79%</td><td></td><td style="text-align: right;;">305.64</td><td style="text-align: right;;">88.84</td><td style="text-align: right;;">12.53</td><td></td><td></td><td style="text-align: right;;">-4.16%</td><td style="text-align: right;;">-2.56%</td><td style="text-align: right;;">-4.79%</td></tr><tr ><th>53</th><td style="text-align: right;;">3/6/2009</td><td style="text-align: right;;">0.96%</td><td></td><td style="text-align: right;;">4.15%</td><td></td><td style="text-align: right;;">308.57</td><td style="text-align: right;;">85.3</td><td style="text-align: right;;">13.05</td><td></td><td></td><td style="text-align: right;;">0.96%</td><td style="text-align: right;;">-3.98%</td><td style="text-align: right;;">4.15%</td></tr></tbody></table><b>Excel 2007</b><br /><br /><table ><tr><td style="padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;"><b>Worksheet Formulas</b><table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0" ><thead><tr><th >Cell</th><th>Formula</th></tr></thead><tbody><tr><th >F19</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A19,"yahoo"</font>)</td></tr><tr><th >G19</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A19,"yahoo"</font>)</td></tr><tr><th >H19</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A19,"yahoo"</font>)</td></tr><tr><th >F20</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A20,"yahoo"</font>)</td></tr><tr><th >G20</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A20,"yahoo"</font>)</td></tr><tr><th >H20</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A20,"yahoo"</font>)</td></tr><tr><th >F21</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A21,"yahoo"</font>)</td></tr><tr><th >G21</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A21,"yahoo"</font>)</td></tr><tr><th >H21</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A21,"yahoo"</font>)</td></tr><tr><th >F22</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A22,"yahoo"</font>)</td></tr><tr><th >G22</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A22,"yahoo"</font>)</td></tr><tr><th >H22</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A22,"yahoo"</font>)</td></tr><tr><th >F23</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A23,"yahoo"</font>)</td></tr><tr><th >G23</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A23,"yahoo"</font>)</td></tr><tr><th >H23</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A23,"yahoo"</font>)</td></tr><tr><th >F24</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A24,"yahoo"</font>)</td></tr><tr><th >G24</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A24,"yahoo"</font>)</td></tr><tr><th >H24</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A24,"yahoo"</font>)</td></tr><tr><th >F25</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A25,"yahoo"</font>)</td></tr><tr><th >G25</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A25,"yahoo"</font>)</td></tr><tr><th >H25</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A25,"yahoo"</font>)</td></tr><tr><th >F26</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A26,"yahoo"</font>)</td></tr><tr><th >G26</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A26,"yahoo"</font>)</td></tr><tr><th >H26</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A26,"yahoo"</font>)</td></tr><tr><th >F27</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A27,"yahoo"</font>)</td></tr><tr><th >G27</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A27,"yahoo"</font>)</td></tr><tr><th >H27</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A27,"yahoo"</font>)</td></tr><tr><th >F28</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A28,"yahoo"</font>)</td></tr><tr><th >G28</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A28,"yahoo"</font>)</td></tr><tr><th >H28</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A28,"yahoo"</font>)</td></tr><tr><th >B20</th><td style="word-wrap: break-word">=K20</td></tr><tr><th >C20</th><td style="word-wrap: break-word">=L20</td></tr><tr><th >B21</th><td style="word-wrap: break-word">=K21</td></tr><tr><th >C21</th><td style="word-wrap: break-word">=L21</td></tr><tr><th >B22</th><td style="word-wrap: break-word">=K22</td></tr><tr><th >C22</th><td style="word-wrap: break-word">=L22</td></tr><tr><th >B23</th><td style="word-wrap: break-word">=K23</td></tr><tr><th >C23</th><td style="word-wrap: break-word">=L23</td></tr><tr><th >B24</th><td style="word-wrap: break-word">=K24</td></tr><tr><th >C24</th><td style="word-wrap: break-word">=L24</td></tr><tr><th >B25</th><td style="word-wrap: break-word">=K25</td></tr><tr><th >C25</th><td style="word-wrap: break-word">=L25</td></tr><tr><th >B26</th><td style="word-wrap: break-word">=K26</td></tr><tr><th >C26</th><td style="word-wrap: break-word">=L26</td></tr><tr><th >B27</th><td style="word-wrap: break-word">=K27</td></tr><tr><th >C27</th><td style="word-wrap: break-word">=L27</td></tr><tr><th >B28</th><td style="word-wrap: break-word">=K28</td></tr><tr><th >C28</th><td style="word-wrap: break-word">=L28</td></tr><tr><th >K20</th><td style="word-wrap: break-word">=(<font color="Blue">F20-F19</font>)/F19</td></tr><tr><th >L20</th><td style="word-wrap: break-word">=(<font color="Blue">G20-G19</font>)/G19</td></tr><tr><th >M20</th><td style="word-wrap: break-word">=(<font color="Blue">H20-H19</font>)/H19</td></tr><tr><th >K21</th><td style="word-wrap: break-word">=(<font color="Blue">F21-F20</font>)/F20</td></tr><tr><th >L21</th><td style="word-wrap: break-word">=(<font color="Blue">G21-G20</font>)/G20</td></tr><tr><th >M21</th><td style="word-wrap: break-word">=(<font color="Blue">H21-H20</font>)/H20</td></tr><tr><th >K22</th><td style="word-wrap: break-word">=(<font color="Blue">F22-F21</font>)/F21</td></tr><tr><th >L22</th><td style="word-wrap: break-word">=(<font color="Blue">G22-G21</font>)/G21</td></tr><tr><th >M22</th><td style="word-wrap: break-word">=(<font color="Blue">H22-H21</font>)/H21</td></tr><tr><th >K23</th><td style="word-wrap: break-word">=(<font color="Blue">F23-F22</font>)/F22</td></tr><tr><th >L23</th><td style="word-wrap: break-word">=(<font color="Blue">G23-G22</font>)/G22</td></tr><tr><th >M23</th><td style="word-wrap: break-word">=(<font color="Blue">H23-H22</font>)/H22</td></tr><tr><th >K24</th><td style="word-wrap: break-word">=(<font color="Blue">F24-F23</font>)/F23</td></tr><tr><th >L24</th><td style="word-wrap: break-word">=(<font color="Blue">G24-G23</font>)/G23</td></tr><tr><th >M24</th><td style="word-wrap: break-word">=(<font color="Blue">H24-H23</font>)/H23</td></tr><tr><th >K25</th><td style="word-wrap: break-word">=(<font color="Blue">F25-F24</font>)/F24</td></tr><tr><th >L25</th><td style="word-wrap: break-word">=(<font color="Blue">G25-G24</font>)/G24</td></tr><tr><th >M25</th><td style="word-wrap: break-word">=(<font color="Blue">H25-H24</font>)/H24</td></tr><tr><th >K26</th><td style="word-wrap: break-word">=(<font color="Blue">F26-F25</font>)/F25</td></tr><tr><th >L26</th><td style="word-wrap: break-word">=(<font color="Blue">G26-G25</font>)/G25</td></tr><tr><th >M26</th><td style="word-wrap: break-word">=(<font color="Blue">H26-H25</font>)/H25</td></tr><tr><th >K27</th><td style="word-wrap: break-word">=(<font color="Blue">F27-F26</font>)/F26</td></tr><tr><th >L27</th><td style="word-wrap: break-word">=(<font color="Blue">G27-G26</font>)/G26</td></tr><tr><th >M27</th><td style="word-wrap: break-word">=(<font color="Blue">H27-H26</font>)/H26</td></tr><tr><th >K28</th><td style="word-wrap: break-word">=(<font color="Blue">F28-F27</font>)/F27</td></tr><tr><th >L28</th><td style="word-wrap: break-word">=(<font color="Blue">G28-G27</font>)/G27</td></tr><tr><th >M28</th><td style="word-wrap: break-word">=(<font color="Blue">H28-H27</font>)/H27</td></tr><tr><th >F41</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A41,"yahoo"</font>)</td></tr><tr><th >G41</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A41,"yahoo"</font>)</td></tr><tr><th >H41</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A41,"yahoo"</font>)</td></tr><tr><th >F42</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A42,"yahoo"</font>)</td></tr><tr><th >G42</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A42,"yahoo"</font>)</td></tr><tr><th >H42</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A42,"yahoo"</font>)</td></tr><tr><th >F43</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A43,"yahoo"</font>)</td></tr><tr><th >G43</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A43,"yahoo"</font>)</td></tr><tr><th >H43</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A43,"yahoo"</font>)</td></tr><tr><th >F44</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A44,"yahoo"</font>)</td></tr><tr><th >G44</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A44,"yahoo"</font>)</td></tr><tr><th >H44</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A44,"yahoo"</font>)</td></tr><tr><th >F45</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A45,"yahoo"</font>)</td></tr><tr><th >G45</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A45,"yahoo"</font>)</td></tr><tr><th >H45</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A45,"yahoo"</font>)</td></tr><tr><th >F46</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A46,"yahoo"</font>)</td></tr><tr><th >G46</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A46,"yahoo"</font>)</td></tr><tr><th >H46</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A46,"yahoo"</font>)</td></tr><tr><th >F47</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A47,"yahoo"</font>)</td></tr><tr><th >G47</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A47,"yahoo"</font>)</td></tr><tr><th >H47</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A47,"yahoo"</font>)</td></tr><tr><th >F48</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A48,"yahoo"</font>)</td></tr><tr><th >G48</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A48,"yahoo"</font>)</td></tr><tr><th >H48</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A48,"yahoo"</font>)</td></tr><tr><th >F49</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A49,"yahoo"</font>)</td></tr><tr><th >G49</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A49,"yahoo"</font>)</td></tr><tr><th >H49</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A49,"yahoo"</font>)</td></tr><tr><th >F50</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A50,"yahoo"</font>)</td></tr><tr><th >G50</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A50,"yahoo"</font>)</td></tr><tr><th >H50</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A50,"yahoo"</font>)</td></tr><tr><th >F51</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A51,"yahoo"</font>)</td></tr><tr><th >G51</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A51,"yahoo"</font>)</td></tr><tr><th >H51</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A51,"yahoo"</font>)</td></tr><tr><th >F52</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A52,"yahoo"</font>)</td></tr><tr><th >G52</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A52,"yahoo"</font>)</td></tr><tr><th >H52</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A52,"yahoo"</font>)</td></tr><tr><th >F53</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">F\$18,\$A53,"yahoo"</font>)</td></tr><tr><th >G53</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">G\$18,\$A53,"yahoo"</font>)</td></tr><tr><th >H53</th><td style="word-wrap: break-word">=xlqhClose(<font color="Blue">H\$18,\$A53,"yahoo"</font>)</td></tr><tr><th >K41</th><td style="word-wrap: break-word">=(<font color="Blue">F41-F40</font>)/F40</td></tr><tr><th >L41</th><td style="word-wrap: break-word">=(<font color="Blue">G41-G40</font>)/G40</td></tr><tr><th >M41</th><td style="word-wrap: break-word">=(<font color="Blue">H41-H40</font>)/H40</td></tr><tr><th >K42</th><td style="word-wrap: break-word">=(<font color="Blue">F42-F41</font>)/F41</td></tr><tr><th >L42</th><td style="word-wrap: break-word">=(<font color="Blue">G42-G41</font>)/G41</td></tr><tr><th >M42</th><td style="word-wrap: break-word">=(<font color="Blue">H42-H41</font>)/H41</td></tr><tr><th >K43</th><td style="word-wrap: break-word">=(<font color="Blue">F43-F42</font>)/F42</td></tr><tr><th >L43</th><td style="word-wrap: break-word">=(<font color="Blue">G43-G42</font>)/G42</td></tr><tr><th >M43</th><td style="word-wrap: break-word">=(<font color="Blue">H43-H42</font>)/H42</td></tr><tr><th >K44</th><td style="word-wrap: break-word">=(<font color="Blue">F44-F43</font>)/F43</td></tr><tr><th >L44</th><td style="word-wrap: break-word">=(<font color="Blue">G44-G43</font>)/G43</td></tr><tr><th >M44</th><td style="word-wrap: break-word">=(<font color="Blue">H44-H43</font>)/H43</td></tr><tr><th >K45</th><td style="word-wrap: break-word">=(<font color="Blue">F45-F44</font>)/F44</td></tr><tr><th >L45</th><td style="word-wrap: break-word">=(<font color="Blue">G45-G44</font>)/G44</td></tr><tr><th >M45</th><td style="word-wrap: break-word">=(<font color="Blue">H45-H44</font>)/H44</td></tr><tr><th >K46</th><td style="word-wrap: break-word">=(<font color="Blue">F46-F45</font>)/F45</td></tr><tr><th >L46</th><td style="word-wrap: break-word">=(<font color="Blue">G46-G45</font>)/G45</td></tr><tr><th >M46</th><td style="word-wrap: break-word">=(<font color="Blue">H46-H45</font>)/H45</td></tr><tr><th >K47</th><td style="word-wrap: break-word">=(<font color="Blue">F47-F46</font>)/F46</td></tr><tr><th >L47</th><td style="word-wrap: break-word">=(<font color="Blue">G47-G46</font>)/G46</td></tr><tr><th >M47</th><td style="word-wrap: break-word">=(<font color="Blue">H47-H46</font>)/H46</td></tr><tr><th >K48</th><td style="word-wrap: break-word">=(<font color="Blue">F48-F47</font>)/F47</td></tr><tr><th >L48</th><td style="word-wrap: break-word">=(<font color="Blue">G48-G47</font>)/G47</td></tr><tr><th >M48</th><td style="word-wrap: break-word">=(<font color="Blue">H48-H47</font>)/H47</td></tr><tr><th >K49</th><td style="word-wrap: break-word">=(<font color="Blue">F49-F48</font>)/F48</td></tr><tr><th >L49</th><td style="word-wrap: break-word">=(<font color="Blue">G49-G48</font>)/G48</td></tr><tr><th >M49</th><td style="word-wrap: break-word">=(<font color="Blue">H49-H48</font>)/H48</td></tr><tr><th >K50</th><td style="word-wrap: break-word">=(<font color="Blue">F50-F49</font>)/F49</td></tr><tr><th >L50</th><td style="word-wrap: break-word">=(<font color="Blue">G50-G49</font>)/G49</td></tr><tr><th >M50</th><td style="word-wrap: break-word">=(<font color="Blue">H50-H49</font>)/H49</td></tr><tr><th >K51</th><td style="word-wrap: break-word">=(<font color="Blue">F51-F50</font>)/F50</td></tr><tr><th >L51</th><td style="word-wrap: break-word">=(<font color="Blue">G51-G50</font>)/G50</td></tr><tr><th >M51</th><td style="word-wrap: break-word">=(<font color="Blue">H51-H50</font>)/H50</td></tr><tr><th >K52</th><td style="word-wrap: break-word">=(<font color="Blue">F52-F51</font>)/F51</td></tr><tr><th >L52</th><td style="word-wrap: break-word">=(<font color="Blue">G52-G51</font>)/G51</td></tr><tr><th >M52</th><td style="word-wrap: break-word">=(<font color="Blue">H52-H51</font>)/H51</td></tr><tr><th >K53</th><td style="word-wrap: break-word">=(<font color="Blue">F53-F52</font>)/F52</td></tr><tr><th >L53</th><td style="word-wrap: break-word">=(<font color="Blue">G53-G52</font>)/G52</td></tr><tr><th >M53</th><td style="word-wrap: break-word">=(<font color="Blue">H53-H52</font>)/H52</td></tr><tr><th >B41</th><td style="word-wrap: break-word">=K41</td></tr><tr><th >C41</th><td style="word-wrap: break-word">=L41</td></tr><tr><th >B42</th><td style="word-wrap: break-word">=K42</td></tr><tr><th >C42</th><td style="word-wrap: break-word">=L42</td></tr><tr><th >B43</th><td style="word-wrap: break-word">=K43</td></tr><tr><th >C43</th><td style="word-wrap: break-word">=L43</td></tr><tr><th >B44</th><td style="word-wrap: break-word">=K44</td></tr><tr><th >C44</th><td style="word-wrap: break-word">=L44</td></tr><tr><th >B45</th><td style="word-wrap: break-word">=K45</td></tr><tr><th >C45</th><td style="word-wrap: break-word">=L45</td></tr><tr><th >B46</th><td style="word-wrap: break-word">=K46</td></tr><tr><th >C46</th><td style="word-wrap: break-word">=L46</td></tr><tr><th >B47</th><td style="word-wrap: break-word">=K47</td></tr><tr><th >C47</th><td style="word-wrap: break-word">=L47</td></tr><tr><th >B48</th><td style="word-wrap: break-word">=K48</td></tr><tr><th >B49</th><td style="word-wrap: break-word">=K49</td></tr><tr><th >B50</th><td style="word-wrap: break-word">=K50</td></tr><tr><th >B51</th><td style="word-wrap: break-word">=K51</td></tr><tr><th >B52</th><td style="word-wrap: break-word">=K52</td></tr><tr><th >B53</th><td style="word-wrap: break-word">=K53</td></tr><tr><th >D48</th><td style="word-wrap: break-word">=M48</td></tr><tr><th >D49</th><td style="word-wrap: break-word">=M49</td></tr><tr><th >D50</th><td style="word-wrap: break-word">=M50</td></tr><tr><th >D51</th><td style="word-wrap: break-word">=M51</td></tr><tr><th >D52</th><td style="word-wrap: break-word">=M52</td></tr><tr><th >D53</th><td style="word-wrap: break-word">=M53</td></tr></tbody></table></td></tr></table><br />
• Jun 12th, 2011, 05:37 PM
Andrew Fergus
Re: Calculating returns over variable holding periods
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
• Jun 23rd, 2011, 05:11 PM
brjohnson
Re: Calculating returns over variable holding periods
Andrew - Thanks for the follow up. I was finally able to figure this out so no more issues with this problem any longer.