How to convert daily returns into weekly returns?

Mirko

New Member
Joined
Apr 18, 2008
Messages
2
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
ABCD
1DateInterest rateweekday
24-Jan-889.871
35-Jan-889.992
46-Jan-8810.053
57-Jan-8810.094
68-Jan-8810.135
711-Jan-8810.051
812-Jan-8810.072
913-Jan-8810.283
1014-Jan-8810.114
1115-Jan-889.985
1218-Jan-889.871
1319-Jan-889.962
1420-Jan-889.913
1521-Jan-889.864
1622-Jan-889.715
1725-Jan-889.71
1826-Jan-889.762
1927-Jan-889.693
2028-Jan-889.564
2129-Jan-889.555
221-Feb-889.741
232-Feb-889.622
243-Feb-889.643
254-Feb-889.684
265-Feb-889.755
278-Feb-889.931
289-Feb-889.852
UK interest daily
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi Mirko

You could use a pivot table and group dates by week,

KR


Dave
 
Upvote 0
Because it's a week you are summarising? 7days fixed regardless of starting on a Monday, Tuesday etc etc. What difference does it make if the OP only has 4 days one week and 6 the next. If the data is grouped by week as requested

Would you care to explain exactly how your reply helped with the response?
 
Upvote 0
Because it's a week you are summarising? 7days fixed regardless of starting on a Monday, Tuesday etc etc. What difference does it make if the OP only has 4 days one week and 6 the next. If the data is grouped by week as requested

Would you care to explain exactly how your reply helped with the response?

Well - would you care to explain how the pivot table mechanism - which the OP may not know very well - should be used to group weeks that have a variable number of days? Do you know how? I'm no expert on pivot tables - heck, I wouldn't even rate myself as a novice - but I suspect that it is not as simple as you didn't even bother to say.

I'm sorry, but the response "use a pivot table" really is not helpful to the typical person who ***uses this board***. If you are asking a question on this board, you are more than likely a beginner. A pivot table is certainly more of an advanced use. Compare the response "use a pivot table" to something like =sumproduct(--(a1:a10),--(b2:b11)). The later can be used. The former - not so much. In this case, well I'm not sure it even can be used. Can it? I would love to be educated. How does a pivot table group weeks with a variable number of days?
 
Upvote 0
Did the OP ask for further information? No,

You wish to know how to group dates by week in a pivot table? You didn't ask very nicely but hey-ho here goes

Create a pivot table by going to Data>>Pivot Table, select your range and then click Finish
Now drag Date column into Row Data and just put your data in the field area.
Now right click the date column and select Group, now select days = 7

Now I think even the most idle brained fool could agree that a week is 7 days long, the fact that the OP has fluctuating 'trading' days does not change the definition of 'week'. The only thing that'd need to be constant is what weekday the 'week' started on, given that the fella appears to be in the UK I'd hazard a guess at Monday, Excel's default.
 
Upvote 0
Surely with any Forum a point in the right direction with anything like a PivotTable should enable a user that wants to learn to do a little googling on PivotTables and learn for themselves. Links to further help are always useful.

I agree that a PivotTable is generally the best option in these cases
 
Upvote 0
Did the OP ask for further information? No,

You wish to know how to group dates by week in a pivot table? You didn't ask very nicely but hey-ho here goes

Create a pivot table by going to Data>>Pivot Table, select your range and then click Finish
Now drag Date column into Row Data and just put your data in the field area.
Now right click the date column and select Group, now select days = 7

Now I think even the most idle brained fool could agree that a week is 7 days long, the fact that the OP has fluctuating 'trading' days does not change the definition of 'week'. The only thing that'd need to be constant is what weekday the 'week' started on, given that the fella appears to be in the UK I'd hazard a guess at Monday, Excel's default.

The Op clearly indicated that, for his problem, a) a week does not have 7 days and b) the number of days in a week is not constant. When I point this out to you, and how it materially and negatively effects your so called solution, you call me and the op "idle brained fool" (the op for having a week that doesn't have 7 days and me for actually reading the op's post)

Continue to post - I won't respond
 
Upvote 0
Surely with any Forum a point in the right direction with anything like a PivotTable should enable a user that wants to learn to do a little googling on PivotTables and learn for themselves. Links to further help are always useful.

I agree that a PivotTable is generally the best option in these cases

Then in the interest of my education, perhaps you can answer how a pivot table can be used in **this** case - a question that dave seems to think I am a fool for even asking (and one that Dave clearly couldn't answer). How can you use a pivot table when the number of days in the week **varies** from week to week. I'm not saying it can't be done mind you - I don't know enough to have an opionion. I do think it won't be simple, and deserves more than "use a pivot table" as a response to a OP.

Is there a MVP who would like to put this to rest one way or the other? Can it be done?
 
Upvote 0
I'm certainly no expert on Pivots but by calculating week numbers:

<b>Raw Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84px;" /><col style="width:84px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Date</td><td >Interest rate</td><td >weekday</td><td >Week Num</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">04-Jan-88</td><td style="text-align:right; ">9.87</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">05-Jan-88</td><td style="text-align:right; ">9.99</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">06-Jan-88</td><td style="text-align:right; ">10.05</td><td style="text-align:right; ">3</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">07-Jan-88</td><td style="text-align:right; ">10.09</td><td style="text-align:right; ">4</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">08-Jan-88</td><td style="text-align:right; ">10.13</td><td style="text-align:right; ">5</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">11-Jan-88</td><td style="text-align:right; ">10.05</td><td style="text-align:right; ">1</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">12-Jan-88</td><td style="text-align:right; ">10.07</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">13-Jan-88</td><td style="text-align:right; ">10.28</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">14-Jan-88</td><td style="text-align:right; ">10.11</td><td style="text-align:right; ">4</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">15-Jan-88</td><td style="text-align:right; ">9.98</td><td style="text-align:right; ">5</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">18-Jan-88</td><td style="text-align:right; ">9.87</td><td style="text-align:right; ">1</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">19-Jan-88</td><td style="text-align:right; ">9.96</td><td style="text-align:right; ">2</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">20-Jan-88</td><td style="text-align:right; ">9.91</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">21-Jan-88</td><td style="text-align:right; ">9.86</td><td style="text-align:right; ">4</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">22-Jan-88</td><td style="text-align:right; ">9.71</td><td style="text-align:right; ">5</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">25-Jan-88</td><td style="text-align:right; ">9.7</td><td style="text-align:right; ">1</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">26-Jan-88</td><td style="text-align:right; ">9.76</td><td style="text-align:right; ">2</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">27-Jan-88</td><td style="text-align:right; ">9.69</td><td style="text-align:right; ">3</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">28-Jan-88</td><td style="text-align:right; ">9.56</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">29-Jan-88</td><td style="text-align:right; ">9.55</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">01-Feb-88</td><td style="text-align:right; ">9.74</td><td style="text-align:right; ">1</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">02-Feb-88</td><td style="text-align:right; ">9.62</td><td style="text-align:right; ">2</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">03-Feb-88</td><td style="text-align:right; ">9.64</td><td style="text-align:right; ">3</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">04-Feb-88</td><td style="text-align:right; ">9.68</td><td style="text-align:right; ">4</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">05-Feb-88</td><td style="text-align:right; ">9.75</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">08-Feb-88</td><td style="text-align:right; ">9.93</td><td style="text-align:right; ">1</td><td style="text-align:right; ">7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="text-align:right; ">09-Feb-88</td><td style="text-align:right; ">9.85</td><td style="text-align:right; ">2</td><td style="text-align:right; ">7</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=WEEKNUM(A2)</td></tr><tr><td >D3</td><td >=WEEKNUM(A3)</td></tr><tr><td >D4</td><td >=WEEKNUM(A4)</td></tr><tr><td >D5</td><td >=WEEKNUM(A5)</td></tr><tr><td >D6</td><td >=WEEKNUM(A6)</td></tr><tr><td >D7</td><td >=WEEKNUM(A7)</td></tr><tr><td >D8</td><td >=WEEKNUM(A8)</td></tr><tr><td >D9</td><td >=WEEKNUM(A9)</td></tr><tr><td >D10</td><td >=WEEKNUM(A10)</td></tr><tr><td >D11</td><td >=WEEKNUM(A11)</td></tr><tr><td >D12</td><td >=WEEKNUM(A12)</td></tr><tr><td >D13</td><td >=WEEKNUM(A13)</td></tr><tr><td >D14</td><td >=WEEKNUM(A14)</td></tr><tr><td >D15</td><td >=WEEKNUM(A15)</td></tr><tr><td >D16</td><td >=WEEKNUM(A16)</td></tr><tr><td >D17</td><td >=WEEKNUM(A17)</td></tr><tr><td >D18</td><td >=WEEKNUM(A18)</td></tr><tr><td >D19</td><td >=WEEKNUM(A19)</td></tr><tr><td >D20</td><td >=WEEKNUM(A20)</td></tr><tr><td >D21</td><td >=WEEKNUM(A21)</td></tr><tr><td >D22</td><td >=WEEKNUM(A22)</td></tr><tr><td >D23</td><td >=WEEKNUM(A23)</td></tr><tr><td >D24</td><td >=WEEKNUM(A24)</td></tr><tr><td >D25</td><td >=WEEKNUM(A25)</td></tr><tr><td >D26</td><td >=WEEKNUM(A26)</td></tr><tr><td >D27</td><td >=WEEKNUM(A27)</td></tr><tr><td >D28</td><td >=WEEKNUM(A28)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4



and make a pivot

<b>Pivot</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:92px;" /><col style="width:157px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Row Labels</td><td >Average of Interest rate</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; ">2</td><td style="text-align:right; ">10.03</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; ">3</td><td style="text-align:right; ">10.10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; ">4</td><td style="text-align:right; ">9.86</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; ">5</td><td style="text-align:right; ">9.65</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; ">6</td><td style="text-align:right; ">9.69</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:left; ">7</td><td style="text-align:right; ">9.89</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:left; ">Grand Total</td><td style="text-align:right; ">9.87</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top