How to convert daily returns into weekly returns?

[Mirko]

Hi everyone,
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
Please, help me
kind regards
Mirko

Look at a part of my spreadsheet below.<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

UK interest daily.xls
	A	B	C	D
1	Date	Interest rate	weekday
2	4-Jan-88	9.87	1
3	5-Jan-88	9.99	2
4	6-Jan-88	10.05	3
5	7-Jan-88	10.09	4
6	8-Jan-88	10.13	5
7	11-Jan-88	10.05	1
8	12-Jan-88	10.07	2
9	13-Jan-88	10.28	3
10	14-Jan-88	10.11	4
11	15-Jan-88	9.98	5
12	18-Jan-88	9.87	1
13	19-Jan-88	9.96	2
14	20-Jan-88	9.91	3
15	21-Jan-88	9.86	4
16	22-Jan-88	9.71	5
17	25-Jan-88	9.7	1
18	26-Jan-88	9.76	2
19	27-Jan-88	9.69	3
20	28-Jan-88	9.56	4
21	29-Jan-88	9.55	5
22	1-Feb-88	9.74	1
23	2-Feb-88	9.62	2
24	3-Feb-88	9.64	3
25	4-Feb-88	9.68	4
26	5-Feb-88	9.75	5
27	8-Feb-88	9.93	1
28	9-Feb-88	9.85	2
UK interest daily

 

[royUK]

I'm sure that Dave is correct, that it doesn't matter how many days are listed in the week, providing that the data falls within a particular week then the data will be included in that week, assuming the OP requires a weekly average you can select average in the data section.

In addition the pivot could be grouped by Months

 

[dave3009]

I'll apologise to mortgageman, I didn't intend you to think I thought you were an idiot. Not really in my charater.

Groups in pivot tables can be very very versatile.

see

http://www.contextures.com/xlPivot07.html

An table could be made without the pivot wizard being opened but with a pivot table you can create tables in seconds without the need for fancy formulas.

I hope the OP was able to search on pivots and gain the info they needed

Cheers


Dave

 

[mortgageman]

I'm sure that Dave is correct, that it doesn't matter how many days are listed in the week, providing that the data falls within a particular week then the data will be included in that week, assuming the OP requires a weekly average you can select average in the data section.

In addition the pivot could be grouped by Months

As you can see from Vog's solution, (or even from just reading the OP's post) it does matter.

Vog - thank you. Your solution is one of those that in hindsight, you just want to say - "Oh yeah". To be honest, I think I would have puzzled over it for days and then given up. Good to know that it can be done and how.

 

[mortgageman]

I'll apologise to mortgageman, I didn't intend you to think I thought you were an idiot. Not really in my charater.

Groups in pivot tables can be very very versatile.

see

http://www.contextures.com/xlPivot07.html

An table could be made without the pivot wizard being opened but with a pivot table you can create tables in seconds without the need for fancy formulas.

I hope the OP was able to search on pivots and gain the info they needed

Cheers


Dave

I'm sure they are versatile, but the link you provided did not answer the OP's issue of variable days in a week. Since Vog provided a solution to the OP's particular issue, s/he didn't have the search.

 

[dave3009]

Gene

VoG II's solution is perfect, it is no different from using groups however.

The link I provided was never intended to provide a solution to anything, purely expand on RoyUK's note that grouping isn't limited to week and month.

And a week is defined as a period of seven successive days. It cannot change, here is a link that will explain it for you

http://dictionary.reference.com/browse/week

Now can I please ask that you back down, a pivot table is a fine solution as you have already agreed.

Many Thanks


Dave

 

[mortgageman]

Gene


And a week is defined as a period of seven successive days. It cannot change, here is a link that will explain it for you

http://dictionary.reference.com/browse/week



Dave

Thanks Dave, I found your dictionary defintion of a week invaluable. I really don't know how I managed to survive almost 1/2 a century and get two advanced degrees without it. Maybe the OP will study it too - and then change the parameters of his needs, since you clearly insist on making him accept a fixed length week.

 

[dave3009]

Gene

VoG II's description uses a fixed length week, or does WEEKNUM provide an option to change an age old fact of life!

Am I to retract my apology?

 

[mortgageman]

Gene

VoG II's description uses a fixed length week, or does WEEKNUM provide an option to change an age old fact of life!

Am I to retract my apology?

I know I said I wouldn't say anything else, but in the interest of making sure other's don't get confused by Dave's post:

Vog's solution maps the days into a week number. The pivot will then be based on this week number. To the extent that some of the OP's days wind up to be less than 5 biz days for a given week (as the OP said could happen), there will be less than 5 of those week nums. Thus when the pivot machinery does an average - for example - it will use a denom other than 5.

I am explaining Vog's solution simply (and hopefully acurately) but that shouldn't be taken to mean that it was a simple solution. Lots of things are simple after someone clever comes up with it.

Dave, I'm not sure what it is about the OP's actual words that you don't get. The OP made it clear that he needed a solution that requires a variable number of days in the week - no matter how much band width you insist on wasting and no matter how many dictionaries you insist on quoting.

It makes no difference to me if you apologise or not. Or if you retact your apology or not.

 

[royUK]

The thing is that creating a PivotTable using the exact same dates & interest rates using Dave's suggestion and without bothering calculating week numbers produces the exact same results!

 

[dave3009]

Gene

Everyone who has offered to add to this solution has done so using pivot tables. Leads me to believe it is the right solution.

I asked before and I'll ask again, can you please back down.

Thank You


Dave