+ Reply to Thread
Results 1 to 18 of 18

Extracting Month End data from a daily time series

  1. 05-21-2017, 11:00 AM #1
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Extracting Month End data from a daily time series

    Dear Friends,

    I have a daily data set as below. I want to pick only weekend data (or month end) to another column with the respective date. I would be grateful if you could pls help me on the formula. I have given an example,

    Once my objective is achieved, it should be like this;

    1/31/2000 -1290.66
    2/29/2000 346.44
    3/31/2000 583.54
    4/28/2000 -1303.7
    5/31/2000 -2109.14

    etc so I dont want to do it manually but dragging a formula

    Kindly advise me on the applicable formula, I shall be grateful.

    Regards

    Lalitha


    Date value
    1/3/2000 546.63
    1/4/2000 249.82
    1/5/2000 -976.28
    1/6/2000 -1669.77
    1/7/2000 -1417.37
    1/10/2000 -974.85
    1/11/2000 -960.9
    1/12/2000 -1108.8
    1/13/2000 -1189.04
    1/14/2000 -1280.77
    1/17/2000 -1248.44
    1/18/2000 -1033.8
    1/19/2000 -1547.66
    1/20/2000 -1607.69
    1/21/2000 -1714.59
    1/24/2000 -1655.45
    1/25/2000 -1719.96
    1/26/2000 -1395.28
    1/27/2000 -905.19
    1/28/2000 -637.06
    1/31/2000 -1290.66
    2/1/2000 -1169.14
    2/2/2000 -1033.18
    2/3/2000 -854.88
    2/8/2000 -594.27
    2/9/2000 -3.54
    2/10/2000 22.17
    2/11/2000 557.3
    2/14/2000 365.96
    2/15/2000 -134.84
    2/16/2000 220.39
    2/17/2000 158.23
    2/18/2000 -223.84
    2/21/2000 -500.63
    2/22/2000 -567.83
    2/23/2000 -446.21
    2/24/2000 235.66
    2/25/2000 377.98
    2/28/2000 161.44
    2/29/2000 346.44
    3/1/2000 20.59
    3/2/2000 113.81
    3/3/2000 462.24
    3/6/2000 935.76
    3/7/2000 1042.36
    3/8/2000 1128.43
    3/9/2000 814.03
    3/10/2000 1008.86
    3/13/2000 273.68
    3/14/2000 106.16
    3/15/2000 -75.8
    3/16/2000 -464
    3/17/2000 259.99
    3/20/2000 411.46
    3/21/2000 376.98
    3/22/2000 724.04
    3/23/2000 887.58
    3/24/2000 961.57
    3/27/2000 1469.86
    3/28/2000 1478.69
    3/29/2000 1273.37
    3/30/2000 644.15
    3/31/2000 583.54
    4/3/2000 69.93
    4/5/2000 -504.56
    4/6/2000 -331.61
    4/7/2000 118.68
    4/10/2000 27.74
    4/11/2000 -335.34
    4/12/2000 -245.91
    4/13/2000 -470.44
    4/14/2000 -680.24
    4/17/2000 -2060.63
    4/18/2000 -1544.68
    4/19/2000 -1395.8
    4/20/2000 -1455.86
    4/25/2000 -1442.99
    4/26/2000 -1595.61
    4/27/2000 -1630.13
    4/28/2000 -1303.7
    5/2/2000 -1005.24
    5/3/2000 -1245.53
    5/4/2000 -1508.93
    5/5/2000 -1554.36
    5/8/2000 -1922
    5/9/2000 -2046.1
    5/10/2000 -2330.08
    5/12/2000 -1711.06
    5/15/2000 -1941.7
    5/16/2000 -1662.71
    5/17/2000 -1995.19
    5/18/2000 -2500.4
    5/19/2000 -2344.74
    5/22/2000 -2682.27
    5/23/2000 -2565.82
    5/24/2000 -2889.02
    5/25/2000 -2901.94
    5/26/2000 -3100.3
    5/29/2000 -2847.93
    5/30/2000 -2832.1
    5/31/2000 -2109.14
    6/1/2000 -1881.81
    6/2/2000 -1538.9
    6/5/2000 -961.32
    6/7/2000 -922.94
    6/8/2000 -946.07
    6/9/2000 -702.74
    6/12/2000 -767.95
    6/13/2000 -1130.06
    6/14/2000 -965.93
    6/15/2000 -742.66
    6/16/2000 -388.62
    6/19/2000 -555.53
    6/20/2000 -736.26
    6/21/2000 -584.86
    6/22/2000 -870.64
    6/23/2000 -1084.92
    6/26/2000 -844.09
    6/27/2000 -667.23
    6/28/2000 -384.58
    6/29/2000 -536.2
    6/30/2000 -667.22
    Reply With QuoteRegister To Reply
  2. 05-21-2017, 11:02 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Extracting Month End data from a daily time series

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 05-21-2017, 11:51 AM #3
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Extracting Month End data from a daily time series

    OOPS! Misread the request. I' ll try again.
    Hi L76, welcome to Excel Forum. The following formulas should work:
    in D2:
    =DATE(YEAR(MIN(A:A)),MONTH(MIN(A:A)),1)
    in D3:D14
    =EDATE(D2,1)
    in E2:E13
    =SUMIFS(B:B,A:A,">="&D2,A:A,"<"&D3)

    Mtotals.png

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 05-21-2017 at 12:17 PM.
    Reply With QuoteRegister To Reply
  4. 05-21-2017, 11:56 AM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extracting Month End data from a daily time series

    Enter formula in D2 and copy down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    or if you still using Excel 2003 use this
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    then enter formula in E2 and copy down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    All are array formulas: **Must be entered with Ctrl+Shift+Enter key combination.

    v A B C D E
    1 Date value
    2 1/3/2000 546.63 1/31/2000 -1290.66
    3 1/4/2000 249.82 2/29/2000 346.44
    4 1/5/2000 -976.28 3/31/2000 583.54
    5 1/6/2000 -1669.77 4/28/2000 -1303.70
    6 1/7/2000 -1417.37 5/31/2000 -2109.14
    7 1/10/2000 -974.85 6/30/2000 -667.22
    8 1/11/2000 -960.9
    9 1/12/2000 -1108.8
    10 1/13/2000 -1189.04
    11 1/14/2000 -1280.77
    12 1/17/2000 -1248.44
    13 1/18/2000 -1033.8
    Last edited by AlKey; 05-21-2017 at 12:18 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 05-21-2017, 12:43 PM #5
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Extracting Month End data from a daily time series

    AlKey : very nice!
    Last edited by leelnich; 05-21-2017 at 01:23 PM.
    Reply With QuoteRegister To Reply
  6. 05-22-2017, 06:43 AM #6
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Dear All,

    Thank you very much, I am indebted to you all. This is an awesome forum- very fast

    Thanks once again,

    Lalitha
    Reply With QuoteRegister To Reply
  7. 06-28-2017, 05:52 AM #7
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Dear Friends,

    I need small favour. I have daily returns from which I need to get the monthly average - applicable to the particular month.

    I shall be thankful if you could write the formula on the excel sheet attached.

    A big help

    Kind regards

    Lalitha

    Data Rate
    12-07-96 15.66
    19-07-96 16.04
    26-07-96 16.21
    02-08-96 16.34
    09-08-96 16.41
    16-08-96 16.65
    23-08-96 16.94
    30-08-96 17.07
    06-09-96 17.18
    13-09-96 17.13
    20-09-96 17.13
    27-09-96 17.18
    04-10-96 17.29
    11-10-96 17.4
    18-10-96 17.52
    25-10-96 17.73
    01-11-96 17.91
    08-11-96 17.98


    The way I want
    month average rate for the month
    Jul-96 15.97
    Aug-96 83.41
    Sep-96 68.62


    There are more than 1000 numbers
    Reply With QuoteRegister To Reply
  8. 06-28-2017, 01:55 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Extracting Month End data from a daily time series

    I included sums as well as averages. Your example has both.

    In D2 this formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    To get the counts in E2.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    To get the averages in F2 this.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.




    A
    B
    C
    D
    E
    F
    1
    Data
    Rate*
    Sums
    Counts
    Averages
    2
    7/12/1996
    15.66
    7/31/1996
    47.91
    3
    15.97
    3
    7/19/1996
    16.04
    8/31/1996
    83.41
    5
    16.682
    4
    7/26/1996
    16.21
    9/30/1996
    68.62
    4
    17.155
    5
    8/2/1996
    16.34
    6
    8/9/1996
    16.41
    7
    8/16/1996
    16.65
    8
    8/23/1996
    16.94
    9
    8/30/1996
    17.07
    10
    9/6/1996
    17.18
    11
    9/13/1996
    17.13
    12
    9/20/1996
    17.13
    13
    9/27/1996
    17.18
    14
    10/4/1996
    17.29
    15
    10/11/1996
    17.40
    16
    10/18/1996
    17.52
    17
    10/25/1996
    17.73
    18
    11/1/1996
    17.91
    19
    11/8/1996
    17.98
    Dave
    Reply With QuoteRegister To Reply
  9. 07-12-2017, 02:58 AM #9
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Dear Friend,

    I shall be thankful if you could please attached a small excel sheet with above data. I tried to upload it for your convenience but it failed.

    Data Rate* Sums Counts Averages
    07-12-96 15.66 7/31/1996 #VALUE! 0 15.97
    7/19/1996 16.04 8/31/1996 #VALUE! 0 16.682
    7/26/1996 16.21 9/30/1996 #VALUE! 0 17.155

    Kind regards

    Lalitha
    Reply With QuoteRegister To Reply
  10. 07-12-2017, 03:02 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,448

    Re: Extracting Month End data from a daily time series

    Lalitha - you need to supply YOUR workbook, to which someone can apply the formula.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  11. 07-12-2017, 04:36 AM #11
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Dear Friend,

    I have attached the excel. Kindly provide the formula if possible. Sorry to trouble you.

    So many thanks

    Regards

    Lalitha
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 07-12-2017, 04:58 AM #12
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Extracting Month End data from a daily time series

    E4=SUMPRODUCT((TEXT($A$3:$A$47,"MMYY")=TEXT($D4,"MMYY"))*($C$3:$C$47))
    F4=SUMPRODUCT((TEXT($A$3:$A$47,"MMYY")=TEXT($D4,"MMYY"))*($C$3:$C$47)*($B$3:$B$47))/SUMPRODUCT((TEXT($A$3:$A$47,"MMYY")=TEXT($D4,"MMYY"))*($B$3:$B$47))
    G4=SUMPRODUCT((TEXT($A$3:$A$47,"MMYY")=TEXT($D4,"MMYY"))*($C$3:$C$47))/SUMPRODUCT((TEXT($A$3:$A$47,"MMYY")=TEXT($D4,"MMYY"))*($B$3:$B$47>0))
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  13. 07-12-2017, 05:01 AM #13
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Extracting Month End data from a daily time series

    or
    e4=SUMIFS($C$3:$C$47,$A$3:$A$47,">="&EOMONTH($D4,-1)+1,$A$3:$A$47,"<="&EOMONTH($D4,0))
    g4=AVERAGEIFS($C$3:$C$47,$A$3:$A$47,">="&EOMONTH($D4,-1)+1,$A$3:$A$47,"<="&EOMONTH($D4,0))
    Reply With QuoteRegister To Reply
  14. 07-12-2017, 06:02 AM #14
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Thank you dear. Excellent I owe you guys a lot for all these support

    Kind regards

    Lalitha
    Reply With QuoteRegister To Reply
  15. 07-12-2017, 06:03 AM #15
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,448

    Re: Extracting Month End data from a daily time series

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  16. 07-12-2017, 06:52 AM #16
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Dear Friends,

    I have a small issue. When I am dragging the formula down, it becomes zero. Let me try again

    Kind regards

    Lalitha
    Reply With QuoteRegister To Reply
  17. 07-12-2017, 06:55 AM #17
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Extracting Month End data from a daily time series

    attach the file where you are getting zero
    Reply With QuoteRegister To Reply
  18. 07-12-2017, 07:05 AM #18
    lalithaamara76
    lalithaamara76 is offline
    Registered User
    Join Date
    05-21-2017
    Location
    Colombo
    MS-Off Ver
    2003
    Posts
    8

    Re: Extracting Month End data from a daily time series

    Dear friends,

    I have a small issue when copying the formula down. let me try again. All becomes zero

    Kind regards

    Lalitha
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do I split daily time series data into seasonality grid by years
    By Oilking in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2018, 04:40 AM
  2. [SOLVED] Extracting Hourly data from a time series given in seconds
    By stewartdix in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-19-2016, 07:54 PM
  3. BREAKING down a month series into a daily series, quickly
    By skooby in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-13-2014, 02:09 AM
  4. Daily Time series data set
    By Belete in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2013, 04:09 PM
  5. Extracting seasonal data from non-ordered time series
    By zhl in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2013, 04:30 PM
  6. Extracting daily data from a 7 day rolling report that is delievered daily
    By eumeus in forum Excel General
    Replies: 2
    Last Post: 11-19-2010, 07:49 AM
  7. [SOLVED] How do I extract the month end value from a daily time series?
    By MTro in forum Excel General
    Replies: 2
    Last Post: 12-24-2005, 07:15 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1