tomfolinsbee
asked on
Using Lookup and Reference functions with named ranges
Note: Edited to add row & column references to make table easier to read
Edited to remove ambiguity about manual calculations
Hello Experts,
I'm trying to use Lookup and Reference functions to dynamically define the ranges used by various statistical calculations like AVERAGE, STDEV, CORREL.
The approach i have in mind is to use a a user defined functions for each calculation, using SecurityA, SecurityB, #daysHistory as required parameters.
My workbook has two sheets:
PairList! (note: this sheet might have up to 700 rows)
user input--------------------- ---------- -- Calculated values-------------------- ---------- -------
SecurityA SecurityB #DaysHistory fnRatioAvg fnRatioStdDev fnCorrelation
1 HK 20 HK 5 HELP! i'll do myself later i'll do myself later
4 HK 20 HK 5 HELP! i'll do myself later i'll do myself later
...
...
Price! (note: this sheet might have up to 65000 rows and 700 securities)
I have currently sorted it ASCENDING, but could change to DESCENDING if the lookups are more efficient.
A B C
1 SecurityID Date Price
2 1 HK Equity 8/10/2006 84.85
3 1 HK Equity 8/11/2006 84.9
4 1 HK Equity 8/14/2006 85.25
5 1 HK Equity 8/15/2006 85.6
6 1 HK Equity 8/16/2006 86.85
7 1 HK Equity 8/17/2006 87.85
8 1 HK Equity 8/18/2006 88.25
9 1 HK Equity 8/21/2006 87.05
10 20 HK Equity 8/10/2006 13.48
11 20 HK Equity 8/11/2006 13.62
12 20 HK Equity 8/14/2006 13.52
13 20 HK Equity 8/15/2006 13.5
14 20 HK Equity 8/16/2006 13.74
15 20 HK Equity 8/17/2006 13.62
16 20 HK Equity 8/18/2006 13.6
17 20 HK Equity 8/21/2006 13.5
18 4 HK Equity 8/10/2006 28.8
19 4 HK Equity 8/11/2006 28.7
20 4 HK Equity 8/14/2006 28.55
21 4 HK Equity 8/15/2006 28.5
22 4 HK Equity 8/16/2006 28.8
23 4 HK Equity 8/17/2006 28.8
24 4 HK Equity 8/18/2006 28.65
25 4 HK Equity 8/21/2006 28.35
...
...
Assuming data for both Prices! and PairList start in cell A1 (with headers), manually the calculations for the first pair (1 HK / 20 HK) would be
={AVERAGE(Prices!C7:C9/Pri ces!C15:C1 7)}
={STDEV(Prices!C7:C9/Price s!C15:C17) }
={CORREL(LN(Prices!C7:C9/P rices!C6:C 8),LN(Pric es!C15:C17 /Prices!C1 4:C16))}
The result table looks like this:
SecurityA SecurityB #DaysHistory fnRatioAverage fnRatioStdDev fnCorrelation
1 HK Equity 20 HK Equity 3 6.4624 0.0230 7%
4 HK Equity 20 HK Equity 3 2.1071 0.0073 -18%
The problem I have is defining the ranges dynamically with reference to SecurityA, SecurityB, #daysHistory in the pair table.
I'm experimenting with various Lookup and Reference functions (eg VLOOKUP, COLUMN, ROW, INDEX, INDIRECT, OFFSET, MATCH).
If someone could help me with fnRatioAverage, I should be able to do fnRatiopStdDev, fnCorrelation on my own.
Any suggestions much appreciated. Thanks.
-Tom
Edited to remove ambiguity about manual calculations
Hello Experts,
I'm trying to use Lookup and Reference functions to dynamically define the ranges used by various statistical calculations like AVERAGE, STDEV, CORREL.
The approach i have in mind is to use a a user defined functions for each calculation, using SecurityA, SecurityB, #daysHistory as required parameters.
My workbook has two sheets:
PairList! (note: this sheet might have up to 700 rows)
user input---------------------
SecurityA SecurityB #DaysHistory fnRatioAvg fnRatioStdDev fnCorrelation
1 HK 20 HK 5 HELP! i'll do myself later i'll do myself later
4 HK 20 HK 5 HELP! i'll do myself later i'll do myself later
...
...
Price! (note: this sheet might have up to 65000 rows and 700 securities)
I have currently sorted it ASCENDING, but could change to DESCENDING if the lookups are more efficient.
A B C
1 SecurityID Date Price
2 1 HK Equity 8/10/2006 84.85
3 1 HK Equity 8/11/2006 84.9
4 1 HK Equity 8/14/2006 85.25
5 1 HK Equity 8/15/2006 85.6
6 1 HK Equity 8/16/2006 86.85
7 1 HK Equity 8/17/2006 87.85
8 1 HK Equity 8/18/2006 88.25
9 1 HK Equity 8/21/2006 87.05
10 20 HK Equity 8/10/2006 13.48
11 20 HK Equity 8/11/2006 13.62
12 20 HK Equity 8/14/2006 13.52
13 20 HK Equity 8/15/2006 13.5
14 20 HK Equity 8/16/2006 13.74
15 20 HK Equity 8/17/2006 13.62
16 20 HK Equity 8/18/2006 13.6
17 20 HK Equity 8/21/2006 13.5
18 4 HK Equity 8/10/2006 28.8
19 4 HK Equity 8/11/2006 28.7
20 4 HK Equity 8/14/2006 28.55
21 4 HK Equity 8/15/2006 28.5
22 4 HK Equity 8/16/2006 28.8
23 4 HK Equity 8/17/2006 28.8
24 4 HK Equity 8/18/2006 28.65
25 4 HK Equity 8/21/2006 28.35
...
...
Assuming data for both Prices! and PairList start in cell A1 (with headers), manually the calculations for the first pair (1 HK / 20 HK) would be
={AVERAGE(Prices!C7:C9/Pri
={STDEV(Prices!C7:C9/Price
={CORREL(LN(Prices!C7:C9/P
The result table looks like this:
SecurityA SecurityB #DaysHistory fnRatioAverage fnRatioStdDev fnCorrelation
1 HK Equity 20 HK Equity 3 6.4624 0.0230 7%
4 HK Equity 20 HK Equity 3 2.1071 0.0073 -18%
The problem I have is defining the ranges dynamically with reference to SecurityA, SecurityB, #daysHistory in the pair table.
I'm experimenting with various Lookup and Reference functions (eg VLOOKUP, COLUMN, ROW, INDEX, INDIRECT, OFFSET, MATCH).
If someone could help me with fnRatioAverage, I should be able to do fnRatiopStdDev, fnCorrelation on my own.
Any suggestions much appreciated. Thanks.
-Tom
So your formula for cell PairList!D2 would be:
={AVERAGE(OFFSET(Price!C$2 ,MATCH(A2, Price!A$2: A$65536,0) +COUNTIF(P rice!A$2:A $65536,A2) -4,0,3,1)/ OFFSET(Pri ce!C$2,MAT CH(B2,Pric e!A$2:A$65 536,0)+COU NTIF(Price !A$2:A$655 36,B2)-4,0 ,3,1))}
Note that I made one tweak to the two function templates...
Use this function to get the range of the last three prices for a security:
OFFSET(Price!C$2,MATCH(Sec urity,Pric e!A$2:A$65 536,0)+COU NTIF(Price !A$2:A$655 36,Securit y)-4,0,3,1 )
and this to find the same but offset by -1 row:
OFFSET(Price!C$2,MATCH(Sec urity,Pric e!A$2:A$65 536,0)+COU NTIF(Price !A$2:A$655 36,Securit y)-5,0,3,1 )
Kevin
={AVERAGE(OFFSET(Price!C$2
Note that I made one tweak to the two function templates...
Use this function to get the range of the last three prices for a security:
OFFSET(Price!C$2,MATCH(Sec
and this to find the same but offset by -1 row:
OFFSET(Price!C$2,MATCH(Sec
Kevin
ASKER
Thanks Kevin!
I used your function templates for the other two calcs, got the same results with the sample data.
I've got a question about why you use -4 or -5 in the 2nd (row) parameter of OFFSET? I see how it fits the sample data(ie, 8 prices for each security minus the 3 days of history =5. But what if the number of days of history to retrieve is 2 or 4 or if the total number of price records for each security is increased?
I haven't tested yet with a large volume of data, will try that now.
I used your function templates for the other two calcs, got the same results with the sample data.
I've got a question about why you use -4 or -5 in the 2nd (row) parameter of OFFSET? I see how it fits the sample data(ie, 8 prices for each security minus the 3 days of history =5. But what if the number of days of history to retrieve is 2 or 4 or if the total number of price records for each security is increased?
I haven't tested yet with a large volume of data, will try that now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kevin - You're doing it again! - Zzzz - Patrick
ASKER
You guys saved my bacon on this project, THANKS!
OFFSET(Price!C2,MATCH(Secu
and this to find the same but offset by -1 row:
OFFSET(Price!C2,MATCH(Secu
where Security is the reference to the security you are looking up.
Kevin