Solved

Excel statistics template needs fixing

Posted on 2011-03-10
21
248 Views
Last Modified: 2012-05-11
I need the attached spreadsheet formulas corrected please.  Columns, D, E, I and K show wrong numbers, dont know why.  Maybe col L too.

Thanks.


Master-Statistics-template-1-v6-.xls
0
Comment
Question by:hedgeselect
  • 11
  • 10
21 Comments
 
LVL 41

Expert Comment

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

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35098269
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35098300
Whoops - I meant to post this...

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

Author Comment

by:hedgeselect
ID: 35098394
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
0
 

Author Comment

by:hedgeselect
ID: 35098472
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.

0
 
LVL 41

Expert Comment

by:dlmille
ID: 35099364
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
0
 

Author Comment

by:hedgeselect
ID: 35101777
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!?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35102016
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
0
 

Author Comment

by:hedgeselect
ID: 35102336
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.
0
 

Author Comment

by:hedgeselect
ID: 35102363
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?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 41

Expert Comment

by:dlmille
ID: 35110339
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.
0
 

Author Comment

by:hedgeselect
ID: 35111028
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?
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35116962
Attached with summary table updated.  Maybe you can make the question with the autofill a new one as I'd like to learn how that works as well :)

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

Author Comment

by:hedgeselect
ID: 35118558
Hi

Major question 3 now posted on http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26882761.html

Hope you get a chance to update it.

Thanks.

0
 

Author Comment

by:hedgeselect
ID: 35133744
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 - http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26882761.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.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35133756
Will try to look tonite.  Under the gun at work for a few things these next few days.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35136047
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
0
 

Author Comment

by:hedgeselect
ID: 35312903
Hi Dave,

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26931284.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.

0
 
LVL 41

Expert Comment

by:dlmille
ID: 35317475
Will take a look - but might be tonite.
Dave
0
 

Author Comment

by:hedgeselect
ID: 35319427
Thx, I appreciate it.
0
 

Author Comment

by:hedgeselect
ID: 35320944
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now