[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

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

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.
  • 3
  • 3
1 Solution
hedgeselectAuthor Commented:
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)?

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...

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

hedgeselectAuthor Commented:
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.
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...

hedgeselectAuthor Commented:
Hi Dave

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

Could you please post the latest spreadsheet with the updates on

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


Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now