Incorporating the first 40 out of 250 rows in column values

[Hans Antlov]

I have a stock ranking system based on Excel, Every week I download 250 daily
quotes from some 300 funds into one long row. I then have simple formulas in
the top column (starting at G2) with the weekly and montly price changes
(this week's divided by last week's price, etc), and then on the remaining
columns use the =INDIRECT(ADDRESS((((ROW()-3)*250)+1),2)) etc to give me
the full spreadsheet with the price performance of one fund on each column. I
then have this linked to a separate spreadsheet where I rank the funds
according to their 1-3-6 and 12 months performance.

I am now trying to incorporate the short-term volatility over 60 days into
my ranking system (given the recent volatitily of the market). I would do
this with the STDEVPA function (and have done so for the monthly volatility
on the price changes columns). For the first fund that would be
=STDEV(B1:B45). I then want to jump to the next fund, which starts at B251
and do the same =STDEV(B251:B295). Since I have more than 300 funds, it would
be extremely time-consuming to enter this manually. Would you guys know of
any function that would help me to jump 250 stocks for each fund column? It
seems I cannot do this with the =INDIRECT(ADDRESS((( function.

Thanks in advance

 

[ShaneDevenshire]

Hi Hans,

Assume the first formula is in M1. Highlight M1:M250 and then drag the fill
handle down as far as necessary.

 

[Hans Antlov]

Thanks for the answer, Shane, but it does not perform what I want. I have
tried the fill handle, but it will only jump 1 row down (thus from B1:B40 to
B2:B41). Let me explain a bit better what I am trying to do.

I import on columns A, B and C an approximatly 50.000 long list of fund
prices and names, with each fund having 250 rows (which is approximately 1
year of trading days). On columns to the right, I then use the
=INDIRECT(ADDRESS function to get the weekly and monthly prices, a total of
18 columns (1WK, 2WK, 3WK, etc up to 12MO). On a separate sheet, I then
divide today price with that of last week, 2WK, 3WK, etc , to get the same 18
columns of percentage change FOR EACH FUND in a separate row, 200 of them. I
have used the fill handle to do this.

What I cannot find is how to for each row (each fund) refer back to the
first 40 prices of the row of 250 prices (which would be approximatly 2
months of performance), and have this autotomacally filled in for the
remaining 200 rows (funds). When I copy or use the fill handle, it only moves
down 1 row, since the next fund indeed is on the next row. But I want to
refer to first 40 prices, jumping 250 rows for each separate fund, not 1.

I would be happy to post or send the Excel file, if that would help.

Hans

 

[ShaneDevenshire]

Hi Hans,

You can send me a sample at (e-mail address removed)