Removing weekends and holidays from trading price data set
Results 1 to 2 of 2

Thread: Removing weekends and holidays from trading price data set
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Removing weekends and holidays from trading price data set

    I have the table below - I would like to create a new table that references this table but ignores days with zeros (those are non trading days) So in essence it would look exactly like the table below but 4/14/2017 - 4/16/2017 (for example) would be deleted and the third date would be 4/13/2017. Obviously i want this to be automated so deleting the rows by hand aint gonna cut it. Any ideas??

    Date Company A S&P 500
    4/18/2017 77.39 20523.281
    4/17/2017 77.08 20636.922
    4/16/2017 0 0
    4/15/2017 0 0
    4/14/2017 0 0
    4/13/2017 75.4 20453.25
    4/12/2017 77.31 20591.86
    4/11/2017 79.18 20651.3
    4/10/2017 77.76 20658.02
    4/9/2017 0 0
    4/8/2017 0 0
    4/7/2017 78.39 20656.1
    4/6/2017 77.87 20662.95
    4/5/2017 76.41 20648.15
    4/4/2017 77.36 20689.24
    4/3/2017 77.19 20650.21
    4/2/2017 0 0
    4/1/2017 0 0
    3/31/2017 78.81 20663.22
    3/30/2017 78.24 20728.49
    3/29/2017 77.6 20659.32
    3/28/2017 78.6 20701.5
    3/27/2017 77.7 20550.98
    3/26/2017 0 0
    3/25/2017 0 0
    3/24/2017 78.35 20596.72

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Removing weekends and holidays from trading price data set

    Welcome to the forum.

    Easiest way is to just filter the table. Select one of the columns with the 0s, from the Home tab click Sort & Filter > Filter. Then click the down arrow at the top of the column and uncheck the 0. You can then copy those columns and paste them to another sheet, and you'll only get the visible rows.

    Other than that, you could create an array formula that copies over the rows you want to another location. But if there are enough, that will slow down your sheet.

    Another option would be to write a macro to perform the function in an "on-demand" manner, but it wouldn't save you much over the filter method.


    Hope this helps!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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