+ Reply to Thread
Results 1 to 3 of 3

Identifying short-term highs and lows on time series data

  1. #1
    Registered User
    Join Date
    12-24-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    2

    Identifying short-term highs and lows on time series data

    Dear all,

    I am examining the patterns on short-term highs and lows on financial time series data. May I know I can identify those short-term highs and lows on a data metric with daily_high and daily_low with excel (below table). Thanks!!!

    ST_high ST_low

    Date Daily_High Daily_Low
    2018/11/19 26,397 26,146
    2018/11/20 26,132 25,776
    2018/11/21 25,998 25,495
    2018/11/22 26,139 25,873
    2018/11/23 25,983 25,807
    2018/11/26 26,414 26,032
    2018/11/27 26,418 26,159
    2018/11/28 26,705 26,333
    2018/11/29 26,923 26,400
    2018/11/30 26,683 26,397
    2018/12/03 27,259 27,007
    2018/12/04 27,260 26,985
    2018/12/05 26,915 26,746
    2018/12/06 26,239 26,013
    2018/12/07 26,281 26,056
    2018/12/10 25,845 25,571
    2018/12/11 25,862 25,623
    2018/12/12 26,239 26,013
    2018/12/13 26,582 26,315
    2018/12/14 26,219 26,061
    2018/12/17 26,186 26,002
    2018/12/18 26,153 25,714
    2018/12/19 25,926 25,784
    2018/12/20 25,860 25,417
    2018/12/21 25,806 25,314
    2018/12/24 25,609 25,421
    Last edited by alphageneration; 12-24-2018 at 06:04 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,317

    Re: Identifying short-term highs and lows on time series data

    I often find that the most difficult part of this kind of "signal processing" is trying to clearly state in my own mind exactly what I mean by "short term high". It isn't clear to me exactly how you are identifying those 'highs' you have identified. I note that you have chosen not to highlight the small "peaks" on 22 Nov and 7 Dec., so there must be more to your criteria than just "higher than the day before and after".

    I think this thread will have some helpful ideas: https://www.excelforum.com/excel-gen...al-points.html

    The overall process I would expect to follow:
    1) Identify my criteria for "short term high".
    2) Add helper column(s) to identify those criteria.
    3) A final column to combine all the criteria together into a single column that identifies the short term highs
    4) Conditional formatting to highlight the identified rows.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    12-24-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Identifying short-term highs and lows on time series data

    Thanks MrShorty for the advice!

    True that I haven't specified too clearly on my definition on high and low.

    The listed table was just my own findings based on graphical presentation, which didn't have clear criteria.

    Before I simply tried finding out the local max and min by comparing the highs (lows) at +/- 1 day as well as the max (min) +/- 3 days. But the results aren't so meaningful, because it may create two or more successive max (min), which fail to meet the purpose of identifying the short-term trends.

    But it may also be just how financial data behave, as they are often fractal and not following simple arithmetic patterns.

    Let me think once again how to define the criteria, as I regard there may be a need to compute the last local max and min at each new rows, or it may even need to correct the last local max and min when new data override the last values.

    Again, thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. vlookup help to find weekly highs and lows
    By kenil gala in forum Excel General
    Replies: 3
    Last Post: 12-13-2018, 11:30 AM
  2. Replies: 4
    Last Post: 01-25-2017, 02:38 PM
  3. Stock price 52 week highs and lows
    By kalozo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-14-2016, 11:36 AM
  4. Calculating short term and long term moving average in VBA
    By ixthus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-10-2013, 02:40 AM
  5. Finding highs lows for a stock price data (weekly)
    By jayanthkrovi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2012, 03:11 PM
  6. How to find a "big jump" or highs/lows in excel?
    By velocio in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-15-2012, 06:57 PM
  7. [SOLVED] Plot Highs and lows?
    By John in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-16-2006, 05: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1