Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1

    How to replicate data values for a time series?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #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. #3
    Quote Originally Posted by bgoree09 View Post
    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. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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. #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,

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

  7. #7
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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. #8
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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. #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. #10
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    do you use a formula to calculate the PB value?

    and please upload a sample worksheet for this.

Page 1 of 2 1 2 LastLast

Posting Permissions

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