Summarize daily data into weekly average

Hey Everyone

First time poster after hours of searching, be gentle please!

I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).

I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this?

Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used? Many many thanks in advance,

AR


Hi,

Below I have a table showing various prices reached during the trading day of the Ftse 100 share index.A new row will be added to the DAILY table at 10pm after the end of each days trading and not before.

I would like to convert the daily data into a weekly time frame, showing the same fields but referring to the levels(Open,High,Low,Close) during the previous completed trading weeks only.The date in the WEEKLY table is the first working day of each week.

DAILY OPEN HIGH LOW CLOSE
06/10/2006 6,005 6,014 5,978 6,001
05/10/2006 5,967 6,017 5,967 6,005
04/10/2006 5,937 5,969 5,922 5,967
03/10/2006 5,958 5,958 5,897 5,937
02/10/2006 5,961 5,986 5,951 5,958
29/09/2006 5,971 6,003 5,950 5,961
28/09/2006 5,930 5,979 5,930 5,971
27/09/2006 5,874 5,942 5,873 5,930
26/09/2006 5,798 5,879 5,798 5,874
25/09/2006 5,822 5,847 5,775 5,798
22/09/2006 5,897 5,897 5,820 5,822
21/09/2006 5,866 5,898 5,849 5,897
20/09/2006 5,832 5,881 5,821 5,866

WEEKLY OPEN HIGH LOW CLOSE
02/10/2006 5,961 6,017 5,897 6,001
25/09/2006 5,822 6,003 5,775 5,961
18/09/2006 5,877 5,912 5,820 5,822
11/09/2006 5,879 5,944 5,820 5,877

So when the coming Monday the 9/10/2006 has completed trading it will be added as a row to the DAILY table but NOT to the WEEKLY table because the trading week has not finshed,so a new row will not be added to the weekly table until after 10 pm on the Friday of each week when all markets have closed for the week.

I'm not very good with time and date formulas, so can anybody advise me as how best to approach this problem and the type of formulas I will need.

I need this to run without manual intervention if that is possible

Really appreciate the help.

Ade

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.

******** ******************** src="*********>*********>Microsoft Excel - UK interest daily.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCD1DateInterest 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
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi everyone and congrats for this very helpful forum

I have an excel spreadsheet with 2 columns
1st column are working/trading days date from jan, 04 1985 until feb, 16 2009
2nd column are values for each day

I would like to transform the daily data in monthly average data?

Can anyone pls help?

tks in advance

I have a data set of daily interest returns, but need weekly returns. As a matter of fact, there are public holidays, which means some weeks consist of only four or even less trading days. Luckly I have the date to each return. How can I turn it into weekly returns.

greetings

Mirko

Is there a way that I can convert daily time series data into weekly data in
Excel? I'd like to take 2 columns of data where column 1 is the date (M-F)
and column 2 is the value associated with that date and create a new column
that has only the Fri dates and the values associated with the Fri dates.
I'd like to similarly further convert the end-of-week data to end-of-month
data. Thank you.

Hi their folks, first time visit to this site and I'm Hoping you can help me. Firstly I havent checked any other threads to see if they are the same as this one, if you know that there is thread that answers my question please post a link to it and i apologise for posting a duplicate thread.
Right, my problem is: I have a spreadsheet which lists in the first coloumn the date, and in the second the rainfall for that date. It covers from 1996 - 2006. It has the rainfall for every day in that selected time period. I want to make a line graph showing this data, so i can see any cycles etc in the data. Unfortunatly you can only have 255 seris in a chart, and obivously this is not enough. So i decided to split the data into weekly rainfall, and make one chart for each year. I began by using the formula =Sum(B2-B8) and draged it down for the whole chart, then I deleted all the infromation in the c colum which wasn't on a sunday, obviously this was going to take time and effort, and there is lots of room for error. I wondered if there was a easier way to do this or any other way of making a chart which will show my data?
all help greatly appreciated and sorry for any grammer and spelling errors
thank you very much Tocheb73

I am new to arrays and found something to convert my daily data to weekly but now can't find it. I want to create a new series ending on Friday.

can anyone help out?

I have a lot of spreadsheets in which I summarize daily data into weekly, monthly, and YTD key indicators. I usually set up all of my data sheets to keep data in columns and set up for a Fiscal Year.

If there are 15 data numbers, this would equal 16 columns, the first being the date. I then keep a rolling week for each indicator (that's another 15 columns,) a MTD for each indicator (15 more,) and YTD (another 15.)

I have historically made heave use of VLOOKUP. But that requires me to actually have to open every file when I update the summary reports, as VLOOKUP and HLOOKUP are not suppose to refresh data unless the linked workbook is also open.

I recently came across the SUMPRODUCT function that is suppose to be able to refresh data without actually opening the linked workbook.

This last alone does save me some personal time in the updating.

With dozens of sheets feeding the summary report, and all of the individual cells having all of the LOOKUP or SUMPRODUCT functions, as well as all of the calculations done to get the rolling week, MTD, and YTD my individual file sizes have really grown since I started this process.

Does anyone have any advice as to how to minimize file size and speed up Excel when opening and updating?

Does any one know an easy way to convert a series of weekly data into their
respective monthly averages.

I have a large range of data in weekly format:
1/2/99 3.54
8/2/99 5.41
15/2/99 2.10

through to...
25/2/05 4.10

How can i get that data into a table which looks like:
1999 2000 ....... 2005
Jan 5.12 1.24 ...... 5.28
Feb 2.14 3.54 ....... 1.79
Mar etc etc
.....
Dec 2.45 4.8 ........ #NA

The monthly average table needs to look up the respective months in the
weekly data and average them into the corresponding cell (ie Jan-1999 equals
5.12 from the 4 or so weeks of data).

I am thinking vlookup function, but am unsure how to add average when
looking up a series of dates in a lookup function.
I already have a similar table which looks up the week number of the date
and puts the corresponding weekly data into the table against its week number.

The weekly data may also have returned #na for graphing purposes, is there
anyway i can accommodate this in the formula.

I would appreciate some help.

Could someone please show me a formula to average weekly data into monthly.

I have a spreadhseet:
Date Data
2/1/99 1.15
9/1/99 2.42
16/1/99 1.24
through to.....
25/2/05 3.54

How can i calculate the average of every month between 1999 and 2005?
In other words, i need something which looks up all the weeks in a
particular month (ie Jan 2001) and averages them out into a table:

1999 2000 ......... 2005
Jan 2.45 6.42
Feb 3.64 etc etc.

I was thinking vlookup, but don't know how to average in a vlookup.

I would appreciate some help.

Hi Guys,

Please check out my sample.xls
This is a ongoing workbook in that data will be added daily and summed up weekly and monthly -
Is there a way to calculate this without adding more rows or columns? each calculation will be in a different worksheet but same workbook.

Any help/sample code would be wonderful
Thanks
Av

Kindly tell me a simple WAY to do it.
Actually i am having daily stock market prices, i just want a simple way to
transfer them into weekly prices.
Say row A has dates and Row B has values.

Not sure if this should be posted here but trying!

I am new to arrays and found something to convert my daily data to weekly but now can't find it. I want to create a new series where it looks at my daily dates and gives me the date of every Friday(or another day I know I can change by using a different number).

I can then vlookup to match the data to the new date.

can anyone help out?

I have an Access database and I'm going to have the user select a month from a dropdown on a form in the database, have a query create a new table based on that criteria, then a macro will call on a spreadsheet, which will have a database query to the newly created table... the table/master sheet will have a list of order numbers and dates and other data....

what I don't know how to do and need some direction is I need that master sheet to then be broken into 5 other sheets when the workbook is opened... sorting the data into Week 1, Week 2, Week 3, Week 4 and Week 5.

Can someone help me out or point me in the right direction?

Thank you in advance to anyone who replies :D

Im short for time, and have a massive data set that i need to seperate. The data is in this format:

Cell A Cell B
01/01/1977 4.5
02/01/1977 7.6
03/01/1977 7.7
04/01/1977 9.0
05/01/1977 3.4
06/01/1977 12
07/01/1977 7.2
08/01/1977 4.6
09/01/1977 7.9
10/01/1977 4.5
11/01/1977 5.5
12/01/1977 6.6

It is a daily data set, which goes on for 30 years, so approx about 15 000 cells long. I need to seperate it into weeks, and then average out the weeks. Is there a formula to do this?

Thanks

Omar

I am working on a project that my boss wants and I would like to know if there is an function or formula I can use to help.

Currently I collect daily proudction data for multiple tasks that an employee does thru out the day. All employees have their own tab on a excel workbook (see attached - weekends are in gray). I can easily get the monthly average for.

The problem is I now have to produce a weekly average as well as the monthly average that will then be linked into charts on a seperate spread sheet. Is there a way to use any funtion or forumula instead of going thru each employees tasks by week and summing it manually?

sample.xlsx

hello all,

any help would be grand I haattached a file that needs a monthly and weekly

I have daily data in column D and daily dates in column A

I have created week ENDING column for weekly DATA in column N

and in Column O I have the formula:
=SUMIF(A:A,"

I am collecting and charting a machine run time on a daily basis. I have been asked to compile the data into weekly buckets and chart that data point instead (of daily). Is there a way to do this without changing the data collection worksheet?

colum a1.a30 contains dates and b1.b30 contains daily counts:

1/1/08 3
1/2/08 5
1/3/08 0
1/4/08 3
....and so on...

What I would like to do with these data is set up a separate adjacent column
like this:
col. d col. e
week 1 avg of 1/1 - 1/7 (or 1/1 - 1/5 -- some range for the week)
week 2 avg of 1/8-1/15
week 3 avg 1/16 - 1/23
.....and so on....

Any way to have these weekly averages caculate (copy the formula down)
without having to adjust the ranges each time?

Thanks

hi

Excel 2007
I am entering information into an excel sheet one day/column at a time. So 2/08/08 is sitting in column c 1/08/07 is sitting in column d and so on rolling back along the sheet. This allows me masses of day that I can store.

What I wish to do is to take the information that is sitting per day in “sheet 1” put it into “sheet 2” in weekly a weekly layout, with the information average or added together.

Then take the information in the weekly, do the same paste it in “sheet 3” per calendar month, “sheet 4” per 3 months “ sheet 5” per 6 months and “sheet 6” per year.

This would have to all update daily as new information is entered. And I would like the Macro to be as efficient as possible doing so.

I have struggled to do this, and have been doing it manually typing in the formulas going back 7 and so on days at a time. And it is really slow when the macro copies what I have done.

Can any one show me a more efficient easier way of doing this?

I am new to excel formulas. My sheet is show below.

ID WeekEndDate StaffLastName StaffHours ServiceCode ClientCode WorkPackage 1 3/28/2012 Anit 8 N1 1212 2 3/29/2012 Anit 8 N2 1212 3 3/30/2012 Anit 8 N2 1214 4 3/31/2012 Anit 8 N4 1212 5 4/1/2012 Anit 5 N1 1212 6 4/1/2012 Anit 3 N2 1212 7 4/3/2012 Anit 8 N2 1222 8 4/4/2012 Anit 8 N2 1212 9 4/5/2012 Anit 8 A1 1212 10 4/6/2012 Anit 8 A1 1212
I want to accumulate this daily data into a weekly (On fridays) based on serviceCode and workPackage.Client code is a empty coloum. For a particular serviceCode and workpackage add the staffHours for that week. The final data should be something like this

ID WeekEndDate StaffLastName StaffHours ServiceCode ClientCode WorkPackage 1 3/30/2012 Anit 8 N1 1212 2 3/30/2012 Anit 8 N2 1212 3 3/30/2012 Anit 8 N2 1214 4 4/6/2012 Anit 16 A1 1212 5 4/6/2012 Anit 5 N1 1212 6 4/6/2012 Anit 19 N2 1212
Looking forward for replies and Thanks in advance

Hi Guys,

Have not been in here for a long time.

Need some help.

I have a webpage where I want to download the daily data from,

http://www.dbs.com/ratesonline/shut.html

and I want it to be inputted into Excel so that I can call up its graph

X Axis - Date
Y Axis - Price

base on the name of the Unit Trust.

How can I do it? Can the data be downloaded using "Import External Data" function?

How can it be inputted as a daily data?
How can it be plotted as a graph?

Thank you guys in advance.

Could some please help me? I have a spreadsheet for attendance that creates individual spreadsheets through a macro. I am trying to add a 6-week average column for each individual worksheet. I have 70 some employees so I didn't attach the whole workbook. But here is what the macro looks like:

Sub UpdateSheets()
Dim myrng As Range, ThisWeek As Date, EmpName As String, c As Range

Dim reg, hol, vac, sic

Sheets("Week End").Select
ThisWeek = Range("B2").Value
Set myrng = Worksheets("Week End").Range("a7", Range("A65536").End(xlUp))

For Each cell In myrng
EmpName = cell.Value
reg = cell.Offset(0, 1).Value
vac = cell.Offset(0, 2).Value
sic = cell.Offset(0, 3).Value
ove = cell.Offset(0, 4).Value
oth = cell.Offset(0, 5).Value
hol = cell.Offset(0, 6).Value

On Error GoTo NewSheet
Sheets(EmpName).Select

With Range("A:A")
Set c = .Find(ThisWeek, LookIn:=xlFormulas)
If Not c Is Nothing Then
MsgBox ("Weekly Data for " & ThisWeek & " has already been updated.")
GoTo Finish
End If
End With

Range("A65536").End(xlUp).Select

FillInData:
ActiveCell.Offset(1, 0).Value = ThisWeek
ActiveCell.Offset(1, 0).NumberFormat = "mm/dd/yy"
ActiveCell.Offset(1, 1).Value = reg
ActiveCell.Offset(1, 2).Value = vac
ActiveCell.Offset(1, 3).Value = sic
ActiveCell.Offset(1, 4).Value = ove
ActiveCell.Offset(1, 5).Value = oth
ActiveCell.Offset(1, 6).Value = hol
ActiveCell.Offset(1, 7).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Sheets("Week End").Select
Next cell
GoTo Finish

NewSheet:
Set NewSheet = Worksheets.Add
NewSheet.Name = EmpName
Sheets(EmpName).Select
Range("A1").Value = EmpName
Range("A1").Font.Size = 18
Range("A3").Value = "Week Ending"
Columns("A:A").ColumnWidth = 14
Range("b3").Value = "Regular"
Range("g3").Value = "Holiday"
Range("c3").Value = "Vacation"
Range("d3").Value = "Sick"
Range("e3").Value = "Overtime"
Range("f3").Value = "Other"
Range("h3").Value = "Total"
Columns("B:H").HorizontalAlignment = xlCenter
Range("A3:H3").Font.Bold = True
Range("A4").Select
Resume FillInData

Finish:
Sheets("Week End").Select
Range("A1").Select

End Sub

I am not sure if I can add this function into this macro or create 70+ macros (1 for each employee) on the individual worksheets?

Any ideas or suggestions or help would be greatly appreciated.

Thanks

Hi,
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")

Can anyone please help me with that? Thanks