Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Dynamically updating ranges in Excel - dlmille please update my spreadsheet (1)

Posted on 2011-03-07
Medium Priority
399 Views
Dave (dlmille),

Further to my last question, could you please update the previous spreadsheet you created with the following:

1.      Two new columns in worksheet 1 (Fund Data worksheet), perhaps between existing columns E and F which look at a) Year To Date (YTD) returns and b) the other column to have 12-month rolling returns (both based on the monthly fund data).

Just to be clear, the YTD returns will always show the cumulative return in that year, so for 2011, it will be Jan 11 and Feb 11.  For complete years the YTD will match the Yearly returns.  The rolling returns will fill every cell in the new column and also look at the cumulative returns for exactly a 12-month period regardless of what the current month is.

As with the rolling 12-month chart from the other question, are you able to include that chart in this spreadsheet here such that it is a second chart in the third worksheet?

Thanks again.
0
Question by:hedgeselect
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

Author Comment

ID: 35066254
BTW, on the last spreadsheet, the new monthly YTD column does correct match the first month's return for Jan of each, but does not march the Yearly Return column (for December) in each year which it should as the cumulative return for the end of each year should be the same as the YTD for December.

I think, when you moved over to my worksheet with the Monthly, Quarterly and Yearly figures, the spreadsheet was based on someone else's calculation method and not yours.  I dont know if this is what is creating the problem?  Do you mind checking against the formulas you created (the one where I split it 400/100 with the other guy)?

Thanks.
0

LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 35066962
I believe I've got it corrected, leveraging the learnings from the first example we did together way back when :)

Monthly YTD was corect, but the formulas for Quarterly and Yearly needed to be recalc'd - can't just sum the monthlies as we well know.

Basically for a period return, you take:

(CURRENT value of \$1 invested MINUS value of \$ invested just before the period starts)
divided by the \$ invested just before the period starts....

For a YTD, the return is merely the cumulated return to that point in the year - Monthly YTD return does that.

And for 12 month rolling return, you take:

CURRENT value of \$1 invested MINUS value of \$ invested just before the period starts.

Let me know if this works for you.

Also this cleaned up the green formula "errors" as they are now consistent, I believe.

See attached...

Dave
Master-Statistics-template-1-v4.xls
0

LVL 42

Expert Comment

ID: 35066968
0

Author Comment

ID: 35072636
Hi Dave,

Other question now closed and I will close this one shortly but open another in a few hours when I get home.  This seems to be better working now after your last update i.e. YTD returns.

For another new EE question (to be posted later this evening) if you want to work on the below?:

1.  New column K in Fund Data worksheet, called Annualised Volatility which is simply the annualised standard deviation of monthly returns which is recorded on a monthly basis for all inclusive monthly data.  For example, in your last spreadsheet this would fill every cell between K152 and K227 (but will work on the whole column when data is added).  See the formula in cell C24 if necessary in worksheet 3.

2. New column L titled "12-month Annualised Volatility" which does the same as (1) above, except that the standard deviation of monthly returns is based on that year's returns only and is calculated and displayed in the December row

3. For the summary table, can we:

a) insert a new row 24 criteria titled "12-month Annualised Volatility for [XXXX]" in which take the latest annual annualised volatility figure the latest year and [XXXX] is dynamically replaced with the last year.  Where it is a new fund or no previous year exists, then a "-" can be added

b) have the last 3-years annual returns dynamically update in the table under the respective cell (where there are 3-years of returns) otherwise "n/a" to be inserted in the cell and also the table automatically updates so that if there was e.g. 8 years of data, only the last 3 years would show

Hope that is clear?  Happy to split this over several EE questions if it helps?  Unfortunately I cannot upload a marked up spreadsheet whilst in the office otherwise I would have done the basic column headers etc.

Thx again.
0

LVL 42

Expert Comment

ID: 35078049
1.  I assume the standard deviation includes all entries that have data - e.g., from when you started tracking fund data to that point in time, correct?  I created the range fundStart to know what row the actual tracking begins on and STDEV(fundStart:currentRow) is the way to go.

2.  12 month annualized volatility is the same, except for partial year at the beginning - and I use fundStart to get that.  If current beginning of year > fundstart, just use last 12 months, otherwise fundstart:current row as with above.

3.  I created a new range called lastFundDEC for the last valid datapoint and lastFundDateDec for the corresponding date (you're really testing my offset function skills - this is great - I need to use this more!) which captures the last valid entry of December.

for example - lastFundDec = =OFFSET('Fund Data'!\$L\$1,MATCH(99^99,'Fund Data'!\$L:\$L)-1,0)

and lastFundDateDec = same formula, but looking at the Column B range.  Its important to use absolute addressing for these types of formulas or things go haywire.  E.g., \$L:\$L or \$L\$1 as opposed to L:L or L1.

Finally, I created a named range called lastMonthlyYTDReturn -to pick that up for partial years.  I check the year label and if its not a number, I'm assuming you're wanting the monthly YTD number for the last return

Make sense?

Ping me when you want to review...
Dave

0

Author Comment

ID: 35079333
Hi Dave

All your assumptions seem correct, although I would need to verify it on the spreadsheet.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26873374.html

I will close this q once you have moved over to the other EE q.

Thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month9 days, 18 hours left to enroll