Calculating daily compounding percentages into weekly and monthly percentages

bensittingdown

New member
Joined
Oct 3, 2014
Messages
5
Reaction score
0
Points
0
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
 
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
 
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/20120.164872

regards,
Ben
 
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 DateDaily earning
Rate
WeekWeekly earning rateMonthMonthly earning rate
FRI1/07/20111July
SAT2/07/2011-0.0011
SUN3/07/2011-0.0011
MON4/07/20112
TUE5/07/2011-0.1477
WED6/07/20110.1474
THU7/07/20110.1039
FRI8/07/20110.0575
SAT9/07/2011-0.0011
SUN10/07/2011-0.0011
MON11/07/2011-0.02443
TUE12/07/20110.0758
WED13/07/20110.3606
THU14/07/20110.3895
FRI15/07/2011-0.0348
SAT16/07/2011-0.0011
SUN17/07/2011-0.0011
MON18/07/20110.05034
TUE19/07/20110.0975
WED20/07/20110.0732
THU21/07/20110.0275
FRI22/07/2011-0.1733
 
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.
 
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.
 
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")
 
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"
 
Attitude check! Replies are given by volunteers.:yell:

"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.
 
Back
Top