# Thread: How to replicate data values for a time series?

1. ## How to replicate data values for a time series?

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.

Rups

2. 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,

3. Originally Posted by bgoree09
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.

4. 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,"")))

5. 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,

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

7. 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.

8. 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.

9. ## 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 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

10. do you use a formula to calculate the PB value?

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•