Results 1 to 9 of 9

Thread: Calculating daily compounding percentages into weekly and monthly percentages

  1. #1

    Calculating daily compounding percentages into weekly and monthly percentages



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

    Hi,


    I am only a spreadsheet newbie. Am trying to tackle a financial drama. I have a column of dates going back in time
    over a thousand days and an adjacent column of figures that shows historic daily percentage movements in the value
    of an investment for each of those days.



    I need to be able to calculate what the percentage return has been on a weekly basis, as well as on a monthly basis.

    About half the daily returns are positive eg 0.121561% and about half are negative eg -0.115487. Some days however
    show zero change.



    I need to be accurate as the outcome will form basis of a complaint which they will certainly shoot down if not correct.

    To be accurate the calculation must occur on a day by day basis, in that figures for one week canít be merely added
    together then divided to get an average. Also the figures for most weeks are a mix of positive and negative percentage movements.



    So maybe there is a need to start each calculation on a base figure of say 100 then multiply by first
    percentage movement then multiply the answer by the next percentage movement, and repeat until end of week gives the weekly percentage movement. How to get the program to distinguish and calculate the negative and positive percentage returms sequentially on a day by day basis is something way beyond me unfortunately.



    As my hard drive failed recently, I purchased a new drive but no longer have excel so have downloaded freeware
    spreadsheets, gnumeric and kingsoft to do this. I think the excel process and formula might be identical to excel or close enough for me to adjust, hopefully. Thanks greatly if anyone can help.




    Ben Davis

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    The first question is on what day of the week do you start counting presumably on a Monday?
    Secondly I presume weekends and public holidays have NO data
    Also to be considered how are you treating data which don't have five trading days.
    Given the implications I would suggest using multiple columns and simple formula. You don't want to get into court defending a complicated array formula.

    So I would use your start on 100, except your return has 6 significant figures so I suggest rather start on 1,000,000. This is Day 0
    Col A: Date
    Col B: Return
    Col C: Indexed value, Day 1 would then be Day0 x (1 + daily return) (/100 if return in percent)
    Col D to E :Year, Month, WeekNum using said function

    Then Identify if this is a new week/month
    Col F: if(F3<>F2,"New Month","")
    Col G: if(G3<>G2,"New Week","")

    Then use a pivot table (or sumif) to pick up the new weeks/ months rows (i.e. week/Month's closing prices)

    The week/month return is then (new - previous)/previous then you can use averages on the Week/Months moves

  3. #3
    Thanks Wizard for your reply.

    Your advice I think is definitely pointing me in the right direction.


    It sucks that Iím not experienced at spreadsheets and that what I need to do requires a level of knowledge and understanding that canít be picked up overnight. I will have to study up on some of the terminology you referred to.

    I will post a cut sample from the start of the list, as follows, that may help partly answer your initial questions.

    As you can see it started on 1.7.2011 (and runs to present day.) As the product invested locally and overseas the earning rate figures were applied 7 days a week, though in recent months they adopted a practice of not applying earning rates to Saturdays or Sundays.

    For the exercise all I can do is start on each Monday and calculate each daily earning rate that appears up to and including the following Sunday, then start again on the next Monday. This would be the process for calculating each weekly earning rate only.

    For monthly earning rate calculations I would have to start at the start date of each month as shown on the list with the calculation to progress one day at a time (whether negative or positive earning rate) from the first day of the month until the last day of the month inclusive.

    It is important that the calculation proceeds one day at a time to be accurate. For example using a starting value of 100.....if a -50% return occurred one day and then a +50% return occurred on the next, the value would not recover to 100 but to 75, so the return at end of second day is -25%. So I know I need a formulae that calculates one day at a time and can distinguish negative from positive returns in the process. I'm not cluey enough at present to know whether the formulae you kindly provided does these things.


    There is another variation in the data.... from 11.02.2012 until 01.05.2014 inclusive they calculated and published daily returns to six decimal places, rather than four. It may not alter the process, not sure. Please see second cut piece from the list showing commencement of this change.



    1/07/2011
    0
    2/07/2011
    -0.0011
    3/07/2011
    -0.0011
    4/07/2011
    0
    5/07/2011
    -0.1477
    6/07/2011
    0.1474
    7/07/2011
    0.1039
    8/07/2011
    0.0575
    9/07/2011
    -0.0011
    10/07/2011
    -0.0011
    11/07/2011
    -0.0244
    12/07/2011
    0.0758
    13/07/2011
    0.3606
    14/07/2011
    0.3895


    <
    9/02/2012 -0.2505
    10/02/2012 -0.0324
    11/02/2012 -0.001121
    12/02/2012 -0.001121
    13/02/2012 -0.120729
    14/02/2012 0.164872

    regards,
    Ben

  4. #4
    Wizard,
    This is layout idea I had, not sure if it is adequate from what you said. It does basically show what I'm needing to achieve though may need to run cursor across the data to highlight the col's.
    Was done in Kingsoft 2013 spreadsheet though Iíve read the software does not do formula (unless that was a previous version). If so, I will have to learn how to use gnumeric for the actual calculations, as it looks like a superior program.

    Apologies for any nuisance. Ben.
    <

    Day Effective Date Daily earning
    Rate
    Week Weekly earning rate Month Monthly earning rate
    FRI 1/07/2011 1 July
    SAT 2/07/2011 -0.0011
    SUN 3/07/2011 -0.0011
    MON 4/07/2011 2
    TUE 5/07/2011 -0.1477
    WED 6/07/2011 0.1474
    THU 7/07/2011 0.1039
    FRI 8/07/2011 0.0575
    SAT 9/07/2011 -0.0011
    SUN 10/07/2011 -0.0011
    MON 11/07/2011 -0.0244 3
    TUE 12/07/2011 0.0758
    WED 13/07/2011 0.3606
    THU 14/07/2011 0.3895
    FRI 15/07/2011 -0.0348
    SAT 16/07/2011 -0.0011
    SUN 17/07/2011 -0.0011
    MON 18/07/2011 0.0503 4
    TUE 19/07/2011 0.0975
    WED 20/07/2011 0.0732
    THU 21/07/2011 0.0275
    FRI 22/07/2011 -0.1733

  5. #5
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Have you thought about using Google Sheets if you don't have Excel. Alternatively look up Open office. I will see if they have pivot table functionality, otherwise it's a bit more work to set-up the averaging.

    The reason I pointed out the number of trading days a week is that there could be a higher variance in the averages on the shorter weeks.

  6. #6
    I've made some slow progress using Kingsoft spreadsheets freeware. The software can read excel and operate using excel formula from the looks of things, from using an excel formula a friend kindly supplied. I just apply it to seven days each week, and it skips over any zero days. A sample of the formula is ....
    =IF(WEEKDAY(B68)=2,((C68/100+1)*(C69/100+1)*(C70/100+1)*(C71/100+1)*(C72/100+1)*(C73/100+1)*(C74/100+1)-1)*100,"")

    I have to manually change the row numbers to match those of the days to calculate each new weekly calculation. The pasted piece of code as you can see works out the weekly return for days 68 to 74 on the list. Unfortunately there are well over a thousand days/rows. Would be grateful if anyone could advise of anything looking wrong in my approach and if there is any short cut way to do what I'm doing? Thanks heaps.

  7. #7
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    The problem is that you need to ensure that there is a single date row for every date and that there are no missed days, skips etc. Need to run a check add a column = previous day + 1 then another column = if (Date (in A) = date in check,"","ERROR MISSING")

  8. #8
    If you see the post above you can see I already have "a single date row for every date and that there are no missed days, skips etc"

  9. #9
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Attitude check! Replies are given by volunteers.

    "Would be grateful if anyone could advise of anything looking wrong in my approach and if there is any short cut way to do what I'm doing? "

    Asked and answered.

Posting Permissions

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