Converting A Quarterly Dataset to Weekly Dataset

D

Dan Thompson

I have been trying to write a module in vba that will convert a dataset that
is quarterly data to a dataset that is a weekly data set, (as the data I have
is only available in quarterly data format) but I would like to cross plot it
against some different weekly data I have in a excel chart with a weekly date
basline.

Example...

My quarterly data set looks somthing like this.
YearQuarter Data
194701 100 <--Q1 Data
194702 50 <--Q2 Data
194703 200 <--Q3 Data
194704 1000 <--Q4 Data

This is what it should be after conversion macro is run.
1-Jan-47 100 <-Start of Q1 Data
8-Jan-47 100
15-Jan-47 100
22-Jan-47 100
29-Jan-47 100
5-Feb-47 100
12-Feb-47 100
19-Feb-47 100
26-Feb-47 100
5-Mar-47 100
12-Mar-47 100
19-Mar-47 100
26-Mar-47 100
2-Apr-47 50 <-- Start of Q2 Data
9-Apr-47 50
16-Apr-47 50
23-Apr-47 50
30-Apr-47 50
7-May-47 50
14-May-47 50
21-May-47 50
28-May-47 50
4-Jun-47 50
11-Jun-47 50
18-Jun-47 50
25-Jun-47 50
2-Jul-47 200 <--Start of Q3 Data
9-Jul-47 200
16-Jul-47 200
23-Jul-47 200
30-Jul-47 200
6-Aug-47 200
13-Aug-47 200
20-Aug-47 200
27-Aug-47 200
3-Sep-47 200
10-Sep-47 200
17-Sep-47 200
24-Sep-47 200
1-Oct-47 1000 <-- Start of Q4 Data
8-Oct-47 1000
15-Oct-47 1000
22-Oct-47 1000
29-Oct-47 1000
5-Nov-47 1000
12-Nov-47 1000
19-Nov-47 1000
26-Nov-47 1000
3-Dec-47 1000
10-Dec-47 1000
17-Dec-47 1000
24-Dec-47 1000
31-Dec-47 1000
 
T

Toppers

Dan,

Try this:

Assume your quarterly data is A1:B4.

In (for example) d1, type 01/01/1947 (to use your year) and format as
date;in D2 downwards, type/copy down "=d1+7" until 31/12/1947 reached; this
will give weekly dates.

In E1 put "=indirect("B" & int((Month(D1)-1)/3)+1)" and copy this down to
last non-blank cell of column D. This should give weekly data corresponding
to the quarterly figures.

HTH
 
D

Dan Thompson

Thank you Toppers that was helpfull and did sort of work
Well it did work but threre is still a problem the example I gave you was
only for one year worth of data when I use the same formula and fill down for
10 years it recycles the values for the 4 quarters of the first year over and
over again so I end up with somthing like this example. (Take Note of the
1948 Values using your formula Then Scroll down and see the rest)

Quarterly Data

Date Data
194701 100
194702 50
194703 200
194704 1000
194801 5
194802 10
194803 15
194804 20

Weekly Data After using your formula

Date Data
1-Jan-47 100
8-Jan-47 100
15-Jan-47 100
22-Jan-47 100
29-Jan-47 100
5-Feb-47 100
12-Feb-47 100
19-Feb-47 100
26-Feb-47 100
5-Mar-47 100
12-Mar-47 100
19-Mar-47 100
26-Mar-47 100
2-Apr-47 50
9-Apr-47 50
16-Apr-47 50
23-Apr-47 50
30-Apr-47 50
7-May-47 50
14-May-47 50
21-May-47 50
28-May-47 50
4-Jun-47 50
11-Jun-47 50
18-Jun-47 50
25-Jun-47 50
2-Jul-47 200
9-Jul-47 200
16-Jul-47 200
23-Jul-47 200
30-Jul-47 200
6-Aug-47 200
13-Aug-47 200
20-Aug-47 200
27-Aug-47 200
3-Sep-47 200
10-Sep-47 200
17-Sep-47 200
24-Sep-47 200
1-Oct-47 1000
8-Oct-47 1000
15-Oct-47 1000
22-Oct-47 1000
29-Oct-47 1000
5-Nov-47 1000
12-Nov-47 1000
19-Nov-47 1000
26-Nov-47 1000
3-Dec-47 1000
10-Dec-47 1000
17-Dec-47 1000
24-Dec-47 1000
31-Dec-47 1000
7-Jan-48 100
14-Jan-48 100
21-Jan-48 100
28-Jan-48 100
4-Feb-48 100
11-Feb-48 100
18-Feb-48 100
25-Feb-48 100
3-Mar-48 100
10-Mar-48 100
17-Mar-48 100
24-Mar-48 100
31-Mar-48 100
7-Apr-48 50
14-Apr-48 50
21-Apr-48 50
28-Apr-48 50
5-May-48 50
12-May-48 50
19-May-48 50
26-May-48 50
2-Jun-48 50
9-Jun-48 50
16-Jun-48 50
23-Jun-48 50
30-Jun-48 50
7-Jul-48 200
14-Jul-48 200
21-Jul-48 200
28-Jul-48 200
4-Aug-48 200
11-Aug-48 200
18-Aug-48 200
25-Aug-48 200
1-Sep-48 200
8-Sep-48 200
15-Sep-48 200
22-Sep-48 200
29-Sep-48 200
6-Oct-48 1000
13-Oct-48 1000
20-Oct-48 1000
27-Oct-48 1000
3-Nov-48 1000
10-Nov-48 1000
17-Nov-48 1000
24-Nov-48 1000
1-Dec-48 1000
8-Dec-48 1000
15-Dec-48 1000
22-Dec-48 1000
29-Dec-48 1000

This is What it should look like ..

1-Jan-47 100
8-Jan-47 100
15-Jan-47 100
22-Jan-47 100
29-Jan-47 100
5-Feb-47 100
12-Feb-47 100
19-Feb-47 100
26-Feb-47 100
5-Mar-47 100
12-Mar-47 100
19-Mar-47 100
26-Mar-47 100
2-Apr-47 50
9-Apr-47 50
16-Apr-47 50
23-Apr-47 50
30-Apr-47 50
7-May-47 50
14-May-47 50
21-May-47 50
28-May-47 50
4-Jun-47 50
11-Jun-47 50
18-Jun-47 50
25-Jun-47 50
2-Jul-47 200
9-Jul-47 200
16-Jul-47 200
23-Jul-47 200
30-Jul-47 200
6-Aug-47 200
13-Aug-47 200
20-Aug-47 200
27-Aug-47 200
3-Sep-47 200
10-Sep-47 200
17-Sep-47 200
24-Sep-47 200
1-Oct-47 1000
8-Oct-47 1000
15-Oct-47 1000
22-Oct-47 1000
29-Oct-47 1000
5-Nov-47 1000
12-Nov-47 1000
19-Nov-47 1000
26-Nov-47 1000
3-Dec-47 1000
10-Dec-47 1000
17-Dec-47 1000
24-Dec-47 1000
31-Dec-47 1000
7-Jan-48 5
14-Jan-48 5
21-Jan-48 5
28-Jan-48 5
4-Feb-48 5
11-Feb-48 5
18-Feb-48 5
25-Feb-48 5
3-Mar-48 5
10-Mar-48 5
17-Mar-48 5
24-Mar-48 5
31-Mar-48 5
7-Apr-48 10
14-Apr-48 10
21-Apr-48 10
28-Apr-48 10
5-May-48 10
12-May-48 10
19-May-48 10
26-May-48 10
2-Jun-48 10
9-Jun-48 10
16-Jun-48 10
23-Jun-48 10
30-Jun-48 10
7-Jul-48 15
14-Jul-48 15
21-Jul-48 15
28-Jul-48 15
4-Aug-48 15
11-Aug-48 15
18-Aug-48 15
25-Aug-48 15
1-Sep-48 15
8-Sep-48 15
15-Sep-48 15
22-Sep-48 15
29-Sep-48 15
6-Oct-48 20
13-Oct-48 20
20-Oct-48 20
27-Oct-48 20
3-Nov-48 20
10-Nov-48 20
17-Nov-48 20
24-Nov-48 20
1-Dec-48 20
8-Dec-48 20
15-Dec-48 20
22-Dec-48 20
29-Dec-48 20

Perhaps the same formula just needs to be a little different I have to do
this for all years from 1947 to current date. But would be nice to have
somthing dynamic I can use for no matter what length of data set I apply it
to.

Any more thoughts ??

Dan Thompson.
 
D

Dan Thompson

Im not sure if you got my reply to your reply but your formula worked
unfortunatly my data set is more than just one year I need to be able to
convert
the data for several years and when I use your formula for more than one year
I end up with the same data for each year. in other words the values for
194701 100
194702 50
194703 200
194704 1000

end up being the same for 1948 1949 ect..

do you know how to fix this ?

Thanks in Advance
Dan Thompson
 
T

Toppers

Dan,

try:

=INDIRECT("B"&INT((MONTH($D1)-1)/3)+1+(YEAR($D1)-1947)*4)

Sorry about delay in reply .... time difference ... I have to sleep sometime!

HTH
 
D

Dan Thompson

I don't know if you will recive this since I am reply sooooo late and I
apologize
as I haven't had a chance to get back to you till now but thank you very much
your new formula works perfect :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to getting text labels to show up in scatter chart. 0
Converting CSV text 2
How do I combine 2 data sets using date? 2
Match/Vlookup/Hlookup ? 3
Consolidate, Lookup and Sum from dates to months 3
Combine 2 Charts 2007 Excel 1
How to avergage colums 7
2 Lists but different time frames. 3

Top