Dynamic Formula Help

[floriandotti]

Hello,

I am trying to make a dynamic chart that will update when I put 2 columns of information in: Portfolio and Benchmark returns. I have everything worked out except one area, and it has to do with a date.

I have maybe 100 different portfolio paired returns, so I made one master worksheet that I can just enter the returns and everything is calculated, but the problem is that the inception dates differ per portfolio.

I can adjust the start day easily, but I am having trouble with some scripting.

=IF($A185>=DATEVALUE("30/04/2011"),STDEV($F174:$F185)*SQRT(12),"")

This is what I've come up with so far, but the issue is that in this column, the $F174 must stay static within each portfolio, but it differs between all portfolios.

My questions is whether I can make the location of cell "$f174" dependent on the FIRST cell that is >=DATEVALUE, and from anything after to remain static?

Thank you for your time.

 

[Gerald Higgins]

So are you saying it might be $F175 instead of $F174, or maybe $F173 ?

If that's the case, then yes, you can do that, you just need a way to determine that it is actually $F173.

Are the values in column F sorted in some way ?

If they are, you can probably use some combination of MATCH and INDEX to find the relevant value, and build the results into what you have now.

 

[floriandotti]

Hi there,

Yes, that's what I'm saying. The end part of the range can be rolling, but the beginning part needs to match the "inception" date, which is may 2010. The only problem with that is for some of my portfolios, the data goes back to 1996, and for others it goes back only to 2000. The return data are monthly observations, so after the start date is decided, it rolls out easily, but it's just linking the starting part of the range to May 2010 which could be F174, or maybe 4 years later at F222, for instance.

The values in F are going to be excess returns, linked to the returns data, but are not sorted in any way... They are by whatever date they occured.

If I could somehow write something like, 'functionX'($C$DATEVALUE(31/05/2010):$C18) everything would work.

my returns start on row 7, so rolling data and anything after 1 year begins on row 18. After that, the end point on the range can roll, but the DATEVALUE point needs to remain static, linked to where May 2010 is found.

thanks for your time again!

 

[floriandotti]

now that I think about it, is there a way to get a cell to show the row number of a value? so could I do something like:
(and i know this doesn't exist, but something similar?)
$Z$10=ROWNUMBER(31/05/2010_Found_In_A:A)

 

[floriandotti]

figured it out:

$Z$10=match("30/05/2010",A:A,0)

So here is my final code, can you check it?

So final code is:

=IF($A#>=DATEVALUE($BJ$8),KURT($F$7:$Z$10),"")

considering that my values start at row 7 for returns

SKEW, STDEV, etc can be substituted for Kurt. I hope this helps anyone else too.

RELIEF!!!

 

[floriandotti]

nevermind!

How would I link a row number to a range search?
so if Z10 = 25 or whatever,
how would I do F7:F(#inZ10)

 

[Gerald Higgins]

Here's one way, there are probably other ways (maybe better)

For example

=sum(Offset(F7,0,0,Z10-6,1))

I've never used KURT, so you might need some variation of this.

In my example, if Z10 contains 25, then the formula will sum everything from F7:F25.

You might need to use $ symbols at various points, if you want to copy this formula to other locations.

 

[floriandotti]

Thanks for your help.

What does the -6 do in Z10-6?

 

[floriandotti]

Hi again,

So I understand the -6 now, but it still isn't working as I want.

Here is more information, but I feel that we are close! or at least you are.

Here are 2 lines of code:

=IF($A133>=DATEVALUE($BJ$8),STDEV($F$122:$F133)*SQRT(12),"")
=IF($A134>=DATEVALUE($BJ$8),STDEV($F$122:$F134)*SQRT(12),"")

Row 122 is the row that contains the inception date, and BJ8 is the inception date. This then works to show blanks until the inception date of the program (there are hundreds of entries before this date, but I want this column to only show STDEV after inception date).BJ9 has the entry of 122, which updates based on inception date, found in BJ8. So for other portfolios, it will automatically update to 50, or 200, or whatever.

I would like to replace what is in STDEV() with a dynamic range:

STDEV(OFFSET($F133,0,0,$BJ$9-6,1)

This doesn't work though... I get $DIV/0, where as the code above gives me a value.

The way it is set up is that from F7:F134 (in this example) I have data. The program began on the date in row 122, so I want to get the STDEV of
122:122, 122:123, ... , 122:134

Does that help explain it? I really appreciate your perserverance and help.

Thanks.

 

[floriandotti]

Along the same lines, is there a way to make a range such as:

=IF($A133>=DATEVALUE($BJ$8),SKEW($F133:OFFSET($BJ$9,0,0)),"")

such that the formula takes the skew from F(x):F133, where x = the value of the offset forumla, which is taken from the date of inception, and then using match to calculate the row number.

This way, SKEW is taken from the first observation which occurs at the inception date, up to whatever row the formula is in?

Thanks