Converting between different frequencies for time series data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to convert between high frequency (for example, daily) and low
frequency (annual) data. Ideally I would like the ability to do this on an
average basis, end of period basis, beginning of period basis, etc. Is this
possible?
 
Hi hello:

It is very easy using auto filter. Let's say your dates are in column A in
A2 thru A355 and you data is in column B in B2 thru B355 with header cells in
A1 and B1. In C2 enter:
=TEXT(A2,"ddd")
In D2 enter:
=TEXT(A2,"d")

copy C2 and D2 down. You will see:

date measure day date
1/1/2006 1 Sun 1
1/2/2006 3 Mon 2
1/3/2006 4 Tue 3
1/4/2006 5 Wed 4
1/5/2006 5 Thu 5
1/6/2006 6 Fri 6
1/7/2006 7 Sat 7
1/8/2006 9 Sun 8
1/9/2006 10 Mon 9
1/10/2006 11 Tue 10
1/11/2006 12 Wed 11
1/12/2006 12 Thu 12
1/13/2006 14 Fri 13
1/14/2006 14 Sat 14
1/15/2006 16 Sun 15
1/16/2006 17 Mon 16
1/17/2006 18 Tue 17
1/18/2006 19 Wed 18
1/19/2006 19 Thu 19
1/20/2006 20 Fri 20
1/21/2006 22 Sat 21
1/22/2006 23 Sun 22
1/23/2006 23 Mon 23
1/24/2006 24 Tue 24
1/25/2006 25 Wed 25
1/26/2006 27 Thu 26
1/27/2006 27 Fri 27
1/28/2006 29 Sat 28
1/29/2006 29 Sun 29
1/30/2006 31 Mon 30
1/31/2006 32 Tue 31
2/1/2006 32 Wed 1
2/2/2006 33 Thu 2
2/3/2006 35 Fri 3
2/4/2006 35 Sat 4
2/5/2006 37 Sun 5
2/6/2006 38 Mon 6
2/7/2006 38 Tue 7
2/8/2006 39 Wed 8
2/9/2006 41 Thu 9
2/10/2006 42 Fri 10
2/11/2006 43 Sat 11
and so on. Next switch on autofilter and for column C select Sun. You
will see:

date measure day date
1/1/2006 1 Sun 1
1/8/2006 9 Sun 8
1/15/2006 16 Sun 15
1/22/2006 23 Sun 22
1/29/2006 29 Sun 29
2/5/2006 37 Sun 5
2/12/2006 44 Sun 12
2/19/2006 50 Sun 19
2/26/2006 57 Sun 26
3/5/2006 64 Sun 5
3/12/2006 71 Sun 12
3/19/2006 78 Sun 19
3/26/2006 86 Sun 26
4/2/2006 93 Sun 2
4/9/2006 100 Sun 9
4/16/2006 107 Sun 16
these are weekly samples

If you use autofilter on column D selecting 1. You will see:

date measure day date
1/1/2006 1 Sun 1
2/1/2006 32 Wed 1
3/1/2006 61 Wed 1
4/1/2006 92 Sat 1
5/1/2006 122 Mon 1
6/1/2006 152 Thu 1
7/1/2006 182 Sat 1
8/1/2006 213 Tue 1
9/1/2006 244 Fri 1
10/1/2006 275 Sun 1
11/1/2006 305 Wed 1
12/1/2006 336 Fri 1
or monthly samples
 
Back
Top