Results 1 to 4 of 4

Thread: How to Better the below formula without much Manual intervention?

  1. #1

    How to Better the below formula without much Manual intervention?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have excel sheet which every month in Calculation Sheet I had new row (Below Row-59) and adjust the formula in Historicals to match for 1Month,3months,1Yrs,3 Yrs and so on.

    My Questions is there a way to better the formula to i just need to add the data in "Calculation Sheet" and sheet picks up the data accordingly with out any manual intervention.

    I have attached my excel sheet example.

    Thank you
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    This solution has been developed in Excel 2003 since you supplied a pre-Excel 2007 .xls file. It made it a bit more difficult than later versions of Excel.
    First you should cearly separate that data in B4:G50 on the Calculation Sheet from the cells below which have some calculations in. Insert some blank rows, say 3 rows.
    Then you need to convert the data in cells B4:G50 on the Calculation Sheet to a List (a Table in more recent versions of Excel):Select B4:G50, then dropdown menus Data|List|Create List, click OK (headers should be included).
    The reason for this is that Excel automatically adjusts the size of the List to accommodate addition and removal of rows.
    To add a new row of data, you click on that bottommost row of the List and a new blank row will appear below, click in that and start entering/pasting your new data.

    In the attached, on the Historicals sheet, you'll find some formulae, for example in cell D4 you have:
    =FVSCHEDULE(1,TrackingDiffYTD)-1
    which replaces the original formula:
    =FVSCHEDULE(1,'Calculation Sheet'!F45:F50)-1
    Both formulae refer to the same cells and give the same results.
    Likewise with cell D9 which now contains:
    =(((FVSCHEDULE(1,PortfolioYTD)^(12/7)-1))-((FVSCHEDULE(1,OneMoRiskFreeYTD))^(12/7)-1))/(STDEV(PortfolioYTD)*SQRT(12))
    replacing:
    =(((FVSCHEDULE(1,'Calculation Sheet'!D45 : D50)^(12/7)-1))-((FVSCHEDULE(1,'Calculation Sheet'!G45:G50))^(12/7)-1))/(STDEV('Calculation Sheet'!D45: D50)*SQRT(12))

    I've added some dynamic named ranges to the workbook, and although the names I've given these ranges are longish, it should be fairly obvious what they refer to:

    BenchmarkLast12Rows
    BenchmarkLast3Rows
    BenchmarkYTD

    OneMoRiskFreeLast12Rows
    OneMoRiskFreeLast3Rows
    OneMoRiskFreeYTD

    PortFolioLast12Rows
    PortfolioLast3Rows
    PortfolioYTD

    TrackingDiffLast12Rows
    TrackingDiffLast3Rows
    TrackingDiffYTD

    I've not altered any formulae on the Calculation Sheet, and they should look after themselves.
    On the Historicals sheet, I altered cells C4:E5 and C9:E11 to include these new names.

    Now, the only cells which give the same resuts as your original table are in the YTD column! That's because I suspect your formulae for the last 3 months and the last 12 months might not refer to the correct ranges, especially C9, the Sharpe ratio for the last 3 months, seems to inlude far too many cells. Other formulae for the last three months only seen to include 2 month's data, and formulae for the last year seem to include only 11 month's data. If I'm wrong, it's easy enough to change just 1 or 2 names (not one of the ones above) on which other names rely.
    The YTD named ranges will adjust automatically to the last year in the source data as new data moves from one year to the next. (I'm assuming YTD means always data from the 1st Jan of the last year?)
    Attached Files Attached Files
    Last edited by p45cal; 2015-08-22 at 11:00 AM.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    I wonder if prveen even saw this response about 3 weeks ago?

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by p45cal View Post
    I wonder if prveen even saw this response about 3 weeks ago?
    Many times have I tried, worked and lost my time, and at the end, initiator question is never answered.
    This is the price we pay if we want to help
    Deal with it
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •