Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting A Quarterly Dataset to Weekly Dataset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting A Quarterly Dataset to Weekly Dataset
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 "Dan Thompson" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting A Quarterly Dataset to Weekly Dataset
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. "Toppers" wrote: 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 "Dan Thompson" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting A Quarterly Dataset to Weekly Dataset
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 "Toppers" wrote: 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 "Dan Thompson" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting A Quarterly Dataset to Weekly Dataset
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 "Dan Thompson" wrote: 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 "Toppers" wrote: 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 "Dan Thompson" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting A Quarterly Dataset to Weekly Dataset
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 :) "Toppers" wrote: 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 "Dan Thompson" wrote: 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 "Toppers" wrote: 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 "Dan Thompson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Population Chart with 1 dataset | Excel Discussion (Misc queries) | |||
Sum a dataset with 2 criteria | Excel Programming | |||
How Can I paste the dataset on a spreadsheet | Excel Programming | |||
creating a new dataset | Excel Programming | |||
two dataset comparison | Excel Programming |