+ Reply to Thread
Results 1 to 11 of 11

Find Sequential Highest High, Lowest Low values

  1. #1
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Find Sequential Highest High, Lowest Low values

    Background:
    Attached excel book shows price Data (Base Excel workbook in 97-2003 format, but using latest Office 2013)
    Cloumn A - Date
    Column B - Open
    Column C - High
    Column D - Low
    Column E - Close
    Column F - sometihng (in current book, empty column)

    From Data range (180 rows, practically it could be any number of rows, last used row range)

    Manually, I've sequentially highlighted Values with date as

    Lowest Low (Column D),

    Lowest Close (Column E),

    Highest High (Column C),

    Highest Close (Cloumn E)

    Adjacement to data, I've marked Peak/Valley on Chart with Arrows to show Highest High, Highest Close and Lowest Low / Lowest Close.

    (In real life work, i dont want chart, which is just here for visual presentation, which value to peak).

    Requirement:
    I need a VBA Macro, that can tabulate the data either under (given in G35:Q47) from any ACTIVE WORKSHEET (As I would be having multiple sheets)

    Scenario 1 - Sequential High, Low with date, Highest High (from Column C), and Lowest Low (from Column D)

    Scenario 2 - Sequential High, Low with date, Highest Close (from Column E), and Lowest Close (from Column E)

    Both of above scenario date/values, I've done manually to explain, what value exactly i want.

    Good if I can get data under both the scenarios with flexibility, which requires just tweaking of column reference number !!!

    After summary table is populated, I dont want to retain original raw data which should be deleted/cleared to reduce file size, and Scenario 1, and/or Scenario 2 Tabulation should only appear as final output.

    Sheet Named Output- is just for illustration, how final output sould look like in main data sheet(DATA), erasing all input data, in format Range Column A:C using either of the scenarios.

    Sample Workbook attached for reference, what i describe above.

    Thanks
    Attached Files Attached Files
    Last edited by analystbank; 09-18-2015 at 07:30 AM.

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

    Re: Find Sequential Highest High, Lowest Low values

    paste into macros,
    run ScanAllCols

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Find Sequential Highest High, Lowest Low values

    @Ranman, Thanks Brother but

    No error, no output.

    I think macro only highlights (put color in cells) to highlight, but output is not same as desired, and also, not all cells r correctly getting highlighted, as is done manually, and illustrated on adjacent chart.

    I only want final output, no color shading , border etc. That was just to facilitate the desired output.
    Last edited by analystbank; 09-19-2015 at 06:31 AM.

  4. #4
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Find Sequential Highest High, Lowest Low values

    Any Help Here, Please.

    If it is too tough, Please highlight, and retain only sequentially High, and Low ROWS values, only from Column E.
    Last edited by analystbank; 09-21-2015 at 12:59 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Find Sequential Highest High, Lowest Low values

    Hi
    Are you trying to get the highest high / lowest low & highest close / lowest close from each month? Or are you after the highest high & lowest low in rank order & whatever date goes with the result & the same for the close figures??

  6. #6
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Find Sequential Highest High, Lowest Low values

    No it should be highlighting sequentially highest high and lowest low, regardless of month or week or days for last high/low.

  7. #7
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Find Sequential Highest High, Lowest Low values

    Your sheet and your explanation of what you want are confusing. In your scenario 1 box you say highest high column C lowest low column D, then in that box you have columns labeled col A, col B, col C. Then you have the worksheet columns with the same letters with data in them. In the box cell J37 under the heading Column C you have the number 10889.55, which appears to come from col D of the worksheet, which is labeled low and your scenario header says Highest High col c, so this is just causing a great deal of confusion. In col A it says lowest low, but there appears to be only one entry in column A of the worksheet for that date, so the terms you are using are confusing also. To me lowest low sounds like there's 20 entries dated with a certain date and I need to find the lowest of those. You have cell D6 marked in red and labeled with a label that says lowest low in col D or lowest close in col E. The other thing with cell D6 is I'm trying to figure out if you marked that as the lowest value in that whole column, because a quick formula tells me the lowest value in that column is in D132. So all this sequential and other stuff is confusing me. Maybe everyone else gets it, I'm just confused.
    Last edited by skywriter; 09-22-2015 at 01:38 AM.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  8. #8
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Find Sequential Highest High, Lowest Low values

    I was going to say something similar. It's as clear as mud. Explain in English & things might happen

  9. #9
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Find Sequential Highest High, Lowest Low values

    Apology for inadvertent drafting or confusion. Thanks for valuable time.

    To put it simply, attached is the same workbook. and query is as under

    Just scan from Column E, and find sequential high, and low regardless of Weekday or Months. To have clear understanding have a look at Chart with arrows showing top, and bottom made, from where direction (up/down) changes.

    The output may remain in same sheet, ( i will be having multiple sheets like this) only with highlighted sequential high low values, and rest of the data could be deleted. Chart is just for illustration, and not required in final work.

    Workbook attached for reference. This time, i've tried to give better explanation and manually linked cells.

    Hope this helps.
    Attached Files Attached Files

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

    Re: Find Sequential Highest High, Lowest Low values

    It looks to me like you are looking for a "signal processing" type algorithm that will identify the "tops of peaks" and the "bottoms of valleys" in a somewhat noisy signal. Unfortunately, I am not familiar with the details of programming these kind of signal processing algorithms, and I don't recall that any of our regular contributors in familiar with these algorithms (if I am wrong, hopefully they will see this post and respond). I know that signal processing is a major field of study in computer science courses. You may do some research into these signal processing algorithms to find what is involved in the programming.

    Part of me also wants to believe that this cannot be the first time that someone has wanted to process stock data for "peaks and troughs". You may do some research among stock analysts to see what algorithms they use to identify peaks and troughs in stock data.

    My signal processing knowledge is pretty much limited to "find the slope/derivative of the signal and identify the points where the slope changes sign". That algorithm is likely to identify a lot of unwanted peaks and troughs in noisy data like this.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Find Sequential Highest High, Lowest Low values

    @MrShorty, yes, that is correct, but i trust, it is possible, atleast if we tell macro that first cell (be it high or low), and from there on find sequential highest high, and lowest low from previous high and so on. Thanks

+ 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. [SOLVED] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  2. [SOLVED] Highest high and lowest low should be plotted.
    By thilag in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-27-2015, 06:30 AM
  3. Replies: 2
    Last Post: 04-27-2015, 04:24 AM
  4. Replies: 1
    Last Post: 01-10-2014, 01:55 PM
  5. [SOLVED] Re: Find lowest 5 numbers in column A with highest values in column B
    By jd16 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 02:08 AM
  6. Find highest and lowest values for a range of dates
    By kersties in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2013, 05:55 AM
  7. Highest High and Lowest Low
    By jimbob in forum Excel General
    Replies: 6
    Last Post: 03-09-2006, 12:11 PM

Tags for this Thread

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