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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PS - were you going to also close out https://www.experts-exchange.com/questions/26839152/Dynamically-updating-charts-in-Excel.html
Cheers,
Dave
Cheers,
Dave
ASKER
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.
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
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.
Make sense?
Ping me when you want to review...
Dave
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,'Fu
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
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
ASKER
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
https://www.experts-exchange.com/questions/26873374/Dynamically-updating-ranges-in-Excel-dlmille-please-update-my-spreadsheet-2.html
I will close this q once you have moved over to the other EE q.
Thanks.
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
https://www.experts-exchange.com/questions/26873374/Dynamically-updating-ranges-in-Excel-dlmille-please-update-my-spreadsheet-2.html
I will close this q once you have moved over to the other EE q.
Thanks.
ASKER
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.