Dynamically updating ranges in Excel - dlmille please update my spreadsheet (2)
Hi Dave (dlmille),
Further to my last question, could you please update the latest previous spreadsheet you created with the following:
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
***********
Given that this is a multiple question set, if you are able to post one spreadsheet reflecting all the above, and when verified, I will open 3 new EE questions to reflect questions 2, 3a and 3b as individual solutions.
In answer to your previous questions:
1. Yes, sounds about right, but difficult to verify without the updated spreadsheet
2. Sounds right
3. Again sounds right, but easier for me to verify once I can see the spreadsheet
Thanks again.
Further to my last question, could you please update the latest previous spreadsheet you created with the following:
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
***********
Given that this is a multiple question set, if you are able to post one spreadsheet reflecting all the above, and when verified, I will open 3 new EE questions to reflect questions 2, 3a and 3b as individual solutions.
In answer to your previous questions:
1. Yes, sounds about right, but difficult to verify without the updated spreadsheet
2. Sounds right
3. Again sounds right, but easier for me to verify once I can see the spreadsheet
Thanks again.
ASKER
Sure, will do going forward.
I just wiped all the monthly fund data and replaced it with the following monthly returns which I placed in different time periods in column C, and I get the #REF! error msg.
1.03%
0.89%
0.22%
0.96%
1.36%
2.11%
-0.09%
1.58%
0.33%
-0.20%
0.91%
0.09%
Does that happen to you?
I just wiped all the monthly fund data and replaced it with the following monthly returns which I placed in different time periods in column C, and I get the #REF! error msg.
1.03%
0.89%
0.22%
0.96%
1.36%
2.11%
-0.09%
1.58%
0.33%
-0.20%
0.91%
0.09%
Does that happen to you?
How did you wipe the data? Did you delete rows, or just erase the data with the delete key or CLEAR?
Dave
Dave
Where did you place (what rows) the data?
I just cleared the monthly data and pasted it to the end for the latest dates...
see attached...
Master-Statistics-template-1-v5a.xls
see attached...
Master-Statistics-template-1-v5a.xls
ASKER
I just erased the data with the delete key. Ok I think I have found the issue. It seems to happen when I copy and paste numbers into the cell, but if I paste it as VALUES, its ok.
Use the same new numbers I pasted above, the yearly return column E does not seem to compute? Could you check the yearly return when the data is pasted into 31 Jan 2011. Its showing the yearly return as 0%.
Also just a thought, but where there is only fresh 12 months of data, shouldn't the monthly YTD returns match the fund cumulative performance of $1 ivnested at inception? It shows 1.084 i.e. 8.4% performance for the year v 9.56% for the monthly YTD? Same with annualised return, shouldn't the Dec figure match the monthly YTD return as the final figure for the year should close everything off and make all yearly figures more conclusive if you know what I mean?
Thx.
Use the same new numbers I pasted above, the yearly return column E does not seem to compute? Could you check the yearly return when the data is pasted into 31 Jan 2011. Its showing the yearly return as 0%.
Also just a thought, but where there is only fresh 12 months of data, shouldn't the monthly YTD returns match the fund cumulative performance of $1 ivnested at inception? It shows 1.084 i.e. 8.4% performance for the year v 9.56% for the monthly YTD? Same with annualised return, shouldn't the Dec figure match the monthly YTD return as the final figure for the year should close everything off and make all yearly figures more conclusive if you know what I mean?
Thx.
Ill check later this PM and do some testing so we can make this more bulletproof
Dave
Dave
Ahhh yes - if there are prior returns, I use the previous december as a calibration point to set back to $1 by dividing by that column J value. But, if there are not, I don't need to do that. Corrected... I updated Quarterly returns as well along the same lines...
As far as the column F I think we have a begining of period versus end of period issue with the disconnect. At some point I started thinking end of period.
So the January $1 value is not $1, its $1 at the cumulative return for January (since we're testing on Jan 31st). I'm not sure I ever played with Column F, but reverting back to the original original question, January's $1 value is end of month, not beginning of month figure, so I changed that :)
see attached.
Dave
Master-Statistics-template-1-v5b.xls
As far as the column F I think we have a begining of period versus end of period issue with the disconnect. At some point I started thinking end of period.
So the January $1 value is not $1, its $1 at the cumulative return for January (since we're testing on Jan 31st). I'm not sure I ever played with Column F, but reverting back to the original original question, January's $1 value is end of month, not beginning of month figure, so I changed that :)
see attached.
Dave
Master-Statistics-template-1-v5b.xls
ASKER
Hi
I think we're slowly getting there. Yes all these values at end of month values, not beginning of month. I should made that clear at the beginning.
The first 12-months of rolling returns (column I) need to show the rolling % as the single digit i.e. 1.03% rather than 101.03%.
The formulas have begun to get too complicated for me to follow so I will need to check sample data that I can compare against results from other fund performance templates or magazines etc. This might take a few days to dig out.
I need to remind myself and check whether annualised returns (col F) should be the same as yearly returns (col E) when end of December comes. Could you just confirm whether the annualised returns formula you are using is based on the calculation of "all the monthly returns for that year, (including if there is only a partially completed year), then averaged and then multiplied by 12" to get annualised or is it based on something else? Because if you had all 12-months figures, divided by the number of months of data for that year i.e. 12, and then multiplied it by 12 again this should match the yearly return?
I think col C in the fund summary table (worksheet 3) needs to be checked C21, C22, C25 as col D&E on the fund summary table contains a % return when there is no monthly data for that period yet. In fact, can we show it so that no rows show data until the appropiate condition i.e. monthly data is present?
I've just added some more monthly returns for an example. Thanks again.
Master-Statistics-template-1-v5b.xls
I think we're slowly getting there. Yes all these values at end of month values, not beginning of month. I should made that clear at the beginning.
The first 12-months of rolling returns (column I) need to show the rolling % as the single digit i.e. 1.03% rather than 101.03%.
The formulas have begun to get too complicated for me to follow so I will need to check sample data that I can compare against results from other fund performance templates or magazines etc. This might take a few days to dig out.
I need to remind myself and check whether annualised returns (col F) should be the same as yearly returns (col E) when end of December comes. Could you just confirm whether the annualised returns formula you are using is based on the calculation of "all the monthly returns for that year, (including if there is only a partially completed year), then averaged and then multiplied by 12" to get annualised or is it based on something else? Because if you had all 12-months figures, divided by the number of months of data for that year i.e. 12, and then multiplied it by 12 again this should match the yearly return?
I think col C in the fund summary table (worksheet 3) needs to be checked C21, C22, C25 as col D&E on the fund summary table contains a % return when there is no monthly data for that period yet. In fact, can we show it so that no rows show data until the appropiate condition i.e. monthly data is present?
I've just added some more monthly returns for an example. Thanks again.
Master-Statistics-template-1-v5b.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Dave,
I've posted the question again (to repair the file) on https://www.experts-exchange.com/questions/26878220/Excel-statistics-template-needs-fixing.html
I'll close this one as you've done a fair bit. Please feel free to help correct the various errors including Annualised Return. I just realised the other person who did this on EE has cocked up a few of the columns and I didnt check his work.
I've posted the question again (to repair the file) on https://www.experts-exchange.com/questions/26878220/Excel-statistics-template-needs-fixing.html
I'll close this one as you've done a fair bit. Please feel free to help correct the various errors including Annualised Return. I just realised the other person who did this on EE has cocked up a few of the columns and I didnt check his work.
ASKER
Thanks, please use latest EE question to update anything from this q.
On to the question at hand...
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.
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
Please see attached and advise,
Dave
Master-Statistics-template-1-v5.xls