If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Incorporating the first 40 out of 250 rows in column values
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 |
#2
|
|||
|
|||
Incorporating the first 40 out of 250 rows in column values
Hi Hans,
Assume the first formula is in M1. Highlight M1:M250 and then drag the fill handle down as far as necessary. -- Cheers, Shane Devenshire "Hans Antlov" wrote: 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 |
#3
|
|||
|
|||
Incorporating the first 40 out of 250 rows in column values
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" wrote: Hi Hans, Assume the first formula is in M1. Highlight M1:M250 and then drag the fill handle down as far as necessary. -- Cheers, Shane Devenshire "Hans Antlov" wrote: 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 |
#4
|
|||
|
|||
Incorporating the first 40 out of 250 rows in column values
|
Thread Tools | |
Display Modes | |
|
|