How to replicate data values for a time series?

bhindir

New member
Joined
Jan 9, 2014
Messages
5
Reaction score
0
Points
0
Hi,

I am trying to replicate a single value for every 30 day calender for fourteen years. For example the time series is from 31/01/1999 to 30/11/2013.

Now I have the following given data keeping it short to give an idea;

Date Value
31/01/1999 0.1553
28/02/1999 0.1792
31/03/1999 0.1712
30/04/1999 0.1398

First of all I need to covert the monthly dates to daily which is not a problem but the corresponding value for each month needs to be repeated for the entire month. Can anyone suggest an formula that can be used to perform the task as I have to do this for upto fourteen years and therefore alot of manual copying. Any help is highly appreciated.

Thanks in advance.

Rups
 
Good afternoon,

I'm not sure if I understand completely, but you can use the Month() function to extra the numerical month (1 - Jan, 2 - Feb, etc...). Also, if by daily dates you mean days since 1/0/1900 you can just take the value + 0.

Hope this helps,
 
Good afternoon,

I'm not sure if I understand completely, but you can use the Month() function to extra the numerical month (1 - Jan, 2 - Feb, etc...). Also, if by daily dates you mean days since 1/0/1900 you can just take the value + 0.

Hope this helps,


thank you for your reply, I do understand how to transform into a daily series but what how do I apply the values e.g for the entire month for February apply a value of 0.1792 for each day. Each month have a different value and therefore rather than typing it once and copy and drag down until end of the month, I was wondering if there is a formula where you can set these parameters.
 
do you use a formula to calculate the value for each month?
if you don't, you could do something like this:

Assuming your dates are in column A, put this in column B
=IF(MONTH(A1)=1,0.1553,IF(MONTH(A1)=2,0.1792,IF(MONTH(A1)=3,0.1712,"")))
 
Or, if these values can change you could set up a table like:

Month Value
Jan .1553
Feb .1792
........

And then call it with a vlookup (assumed that dates start in a1, so in b1) =vlookup(month(a1),---range of month/value table---,2,false)

Best of luck,
 
Thanks for your suggestion.

Just to query on your approach; If each day of January is 0.1553 and each day of February is 0.1792 and each day for March is 0.1738.........would the vlookup still work. If so what how would I formulate. You will below some data and the two columns on the left hand side needs to be presented in the same way as the two columns in the right hand side. Assuming the four columns are A,B,C and D respectively how would I go about transforming the monthly into daily and then applying the values for each month to all the days in the month.



Date PB Date PB
31/01/1999 0.155375 31/01/1999 0.155375
28/02/1999 0.179262 01/02/1999 0.179262
31/03/1999 0.179392 02/02/1999 0.179262
30/04/1999 0.178993 03/02/1999 0.179262
31/05/1999 0.17128 04/02/1999 0.179262
30/06/1999 0.139678 05/02/1999 0.179262
31/07/1999 0.15518 06/02/1999 0.179262
31/08/1999 0.15536 07/02/1999 0.179262
30/09/1999 0.17732 08/02/1999 0.179262
31/10/1999 0.117725 09/02/1999 0.179262
30/11/1999 0.134171 10/02/1999 0.179262
31/12/1999 0.149113 11/02/1999 0.179262
31/01/2000 0.180108 12/02/1999 0.179262
 
Ok I think I understand what you are wanting to do.
However I don't think it can be done with just a formula.

If your given data is just in column A.
31/01/1999
28/02/1999
31/03/1999

this list is total days per month per year.
for example next year would be
31/01/2000
29/02/2000
31/03/2000

column C would then look like
01/01/1999
02/01/1999
....
31/01/1999
01/02/1999
02/02/1999
....

Thus column B would take more rows than column A.

If you are not going to be skipping any months in Column A, then in column C you simply put the first date in C1, then in C2 put =C1+1 and then copy that down as far as you need.
the lookup is a good idea, especially if you ever need to change the month values, and will work for column B and D.
 
ok I just looked at the data you supplied again, and noticed you have a different value for january 2000 than january 1999. if this is the case you would need a lookup table for each month of each year.

How do you calculate what the number should be for column B?
You would need to use that same type of calculation for column D, and not use a lookup or the if statement I provided.
 
PLease assist with time series problem

Hi Guys,

I am still having problems in finding a solution to the following problem:

Looking at the data below The far left show monthly series with corresponding values which needs to be transformed in to a daily series ranging from 31/01/1999 to 30/11/2013. the data on the right had side shows an example of what the left hand table should look like. The problem In am facing is how do I apply each monthly value e.g 0.179262 for 28/02/1999 to all the days of the February 1 to 28 february in 1999. and 0.179392 for 31/03/1999 to all the days of March from 1 march to 31 march 1999 and so on (refering to left hand data) The right hand data is an example of what the left hand data should look like. I have tried some of the solutions like vlookup and if month statement but had no joy. Please help.......Thanks guys. BTW I know how to extend the time series in to daily.


Date PBDatePB
31/01/19990.15537531/01/19990.155375
28/02/19990.17926201/02/19990.179262
31/03/19990.17939202/02/19990.179262
30/04/19990.17899303/02/19990.179262
31/05/19990.1712804/02/19990.179262
30/06/19990.13967805/02/19990.179262
31/07/19990.1551806/02/19990.179262
31/08/19990.1553607/02/19990.179262
30/09/19990.1773208/02/19990.179262
31/10/19990.11772509/02/19990.179262
 
do you use a formula to calculate the PB value?

and please upload a sample worksheet for this.
 
No the pb values are just given values so take them as a constant. There is no formula it is just simple plain data given.

thanks
 
Ok provided the data you have in columns A and B are provided to you.
I have written a macro that will take the dates in column A, being 31/1/1999 and 28/2/1999 and in column C turn them into 1/1/1999, 2/1/1999, 3/1/1999 ... 27/2/1999, 28/2/1999.
I have attached a sample workbook, I have test data in columns A and B. To run the macro hit Alt+F8 the macro is test_me.

You can copy the module to your actual work book, but will need to change some of the variables for what row to start on, if you have headers.
Code:
'set starting values, change these values to fit your sheet.
iRowDest = 1
iRowSourceStartingValue = 1

just change the 1's to the starting row you have.

Simi
View attachment sample-14-1-2014.xlsm
 
Back
Top