Excel statistics template needs fixing

You've put in some PRETTY BIG NUMBERS in monthly returns  column C from row 128.  Aren't those supposed to be percentages?

Dave

Correcting for that - changing to % - things fall back in line.

Exactly how do you want to handle months with no data - I assumed you didn't want to plot that and you'd want to ignore all return data, if there was no monthly data entered.  For example from Feb-11 onward, while the calcs are there, they aren't really valid without valid monthly data input.  You will want to determine how you want the logic to proceed based on this question, as your graphs assume last data entry.

Dave
Master-Statistics-template-1-v6.xls

Whoops - I meant to post this...

Dave
Master-Statistics-template-1-v7.xls

aghhh.  stupid.  Good spot.  It's been a very long week.  I thought it was automatically formatted to convert to percentages when I type 1.1, -0.4 etc etc.

Could you please look at the other issues from previous EE question which I will shortly close.
Could you please correct the annualised returns and also the summary table, C21 22 etc?

Anything you can do to help tidy this up for today would be great, I'm hoping to use the template for a report for Friday close if possible.  Latest version attached.

Thanks.






Master-Statistics-template-1-v6-.xls

I did the last post before refreshing the screen so didnt see your last 2 posts.  

Yes you right.  The empty cells after Feb 11 make no difference as the graph is based on the data that's there.  I guess I'm being fussy and this is more a cosmetic thing than anything else.  Seeing the red 100% in column D and E just looks odd.  Cant there be an IF statement or something like that which says if there is no data to calculate (yet), then show zero because column H, YTD returns show what I need.  Col E is really for the end of the year when the last Dec number is known.

Ok - I cleaned up quite a bit and checked across and things appear to add up properly.

I didn't catch your "show zero" comment - I made it blank instead.  Will that suffice?

Please advise.

Dave
Master-Statistics-template-1-v8.xls

This is now becoming a work of art :)

Some minor tidying up for this question before closing please:

1. Statistics summary table: Cell B25 - could we please remove reference to the month but keep the last complete dynamic year as it does now I think?

2. Cell B26 - After the Sharpe ratio has been computed, could we please add the text string "/1" i.e. slash 1, so if the ratio computed as 1.5, cell C26 would show as 1.5/1  

3. In the charts on worksheet 3, could you please scale the $1growth  Y axis to 2 decimal places instead of 4?

I think I'm getting to where I want to be with this spreadsheet, at least for the next week or so.  

Some general questions for you:

a) As I have had to anonomise the names and numbers in this spreadsheet, I will need to rename some of the benchmark indices or even the fund name.  By replacing the benchmark names in row 3 on worksheet 2, will this create any problems.

b) Worksheet 2 - what is cell L176?

c) Worksheet 2 - What is in cell  M&N 168&169?  If I need to add more historical benchmark data prior to Feb 08 or later than the last row, do I need to update this figure or something?


Some major questions (some from a few previous questions ago but think you might have forgotten).

1. Can we also dynamically label the last 4 rows of the summary table such that cell B33 shows YTD YYYY i.e. automatically updates the year in there and cells 30-32 dynamically show the last 3 years of "yearly returns" .  This can be tested by adding 3 more years of monthly returns to see that subsequent years get updated automatically and YTD picks up the correct YTD from the fund summary worksheet.

2. Any chance of making the second chart look like the first format wise etc including axis scaling

3. Benchmark indices (worksheet 2): Could you please work your magic on those rows such that you copy the automatic line zero'ing out, formats and anything else you think worthwhile.  I DO NOT need a copy of all the other columns i.e rolling, annualised etc

If you can please have 2 spreadsheets saved to this EE question, that will be great.  The first spreadsheet to address minor points 1-3 and then another spreadsheet including minor changes + major changes 1-3.  This way I will create a new question to award points for the new questions but at least, if you have time today (big IF) to work on it, then I can use the spreadsheets tomorrow morning when I go to work.

Thanks again.

PS What do you do professionally? Programmer or maths!?

I did the first 3 - easy.

General Questions:

A) - should be no problem changing labels on the worksheets
B) - can be erased - I was testing calcs - I'll erase all my gibble
C) - that was leftover gibble, too.  the ranges are dynamic so no need to worry with updating any figures to that effect

Major Questions...
1.  The last 4 rows of summary table were already updated with formulas. Yes, the formulas are still there.
2.  Done
3.  not sure what you mean...  Please provide more input.

Here's all the changes:

dave

PS - my profile has my background :)
Master-Statistics-template-1-v9.xls

Hi

I think you misunderstood me for the last major q1.  Im referring to the text string in cells B30-B33, not the columns with calculation next to it.  If you add more monthly fund data in worksheet 1, all the way to say June 2013, what you should find is that in cell B33, it will say YTD 2013 with calculation in C33 will have the data upto June 13.  Cells B33 to C32 should now show the text string for the full complete years, i.e. 2010, 2011 and 2012 respectively.

Also what is all the black blocks at the top of worksheet 1?  If I start to add monthly fund data from Sept 2005, I start get error cell reference msgs again?

Thx, off to sleep.

point 3 - basically I meant that I could be lazy and not bother dragging down associated columns with the benchmark because I thought that I add each monthly figure, the data column and cum return column will automatically come down a row and compute automatically and also apply the formatting?

The black blocks just imply that it won't calculate in that range - can't get a quarterly return with < quarter of data, yearly return with < year of data, etc.  Its either blocking them out or adding more convolution to the formulas.

If you start adding data from Sept 95, all will work correctly, lol.

ok.  Will you be able to update to a new spreadsheet the other changes here or do you want me to open a new question and post link first?

Hi

Major question 3 now posted on https://www.experts-exchange.com/questions/26882761/Excel-statistics-template-needs-fixing.html

Hope you get a chance to update it.

Thanks.

Hi Dave

I'm currently missing you on the follow on questions.  Would really prefer for you to work on my files, more efficient and I think you know what you are doing.

I want to open a new question as the latest  spreadsheet on the following link as the spreadsheet does not now seem to dynamically update the benchmark indicies - https://www.experts-exchange.com/questions/26882761/Excel-statistics-template-needs-fixing.html

If you get a chance, could you please take a look at then post here.  I will then open a new question for you to post the final version template there.

Thanks.

Will try to look tonite.  Under the gun at work for a few things these next few days.

Cheers,

Dave

I just got to this and looks like TommySz... helped you out. I'm glad - really under the gun at work this week, so glad you're getting what you need with other experts as well.

Cheers,

Dave

Hi Dave,

https://www.experts-exchange.com/questions/26931284/Excel-template-needs-fixing-3.html

Could you please help me with the above link.  The benchmark indicies dont seem to be updating for some reason on the 3rd worksheet.

Would be grateful for your assistance.

Thanks.

Will take a look - but might be tonite.
Dave

Thx, I appreciate it.

FYI - The problem seems to occur when I overwrite the fund monthly returns in worksheet one, with more earlier data.  The benchmark indicies in worksheet 2, should then just update on the chart by starting whenever the earliest data of the benchmark is and not try to match the new fund data unless there is new earlier data for that too.

I basically starting analysing a new fund and overwrote the data, but the benchmarks went funny.  They just need to stay as they are or I enter earlier data so the benchmark graphs can extend out to the same time period as the fund (where the benchmark has that time period of data).

Thx.