Get Highs and Lows from Price list, Version 2
Download: 20110311.GetHighsAndLowsFromPriceList as zip package
;==========================================================================================================================================
;
; Create a list of all the dates,
; which have the highest highs and the lowest lows of prices,
; within a moving day range.
;
; Version 2.
; The "compacted" code from version 1, which does the case decision on each loop,
; now has been "stretched", so that checking the two cases, that means searching for highs or lows,
; will be done a little bit more efficient, hopefully.
; Redundant code, but less comparisons, will lead to less runtime,.
;
;------------------------------------------------------------------------------------------------------------------------------------------
; (c)Detlev Dalitz.20110313.
;==========================================================================================================================================

DirChange (DirScript ())

; Define filenames.
strFileDP_RAW = "DatePrice.raw.txt"
strFileDP_CSV_Tmp = "DatePrice.csv.tmp.txt"
strFileDP_CSV_Out1 = "DatePrice.csv.out.1.txt"
strFileDP_CSV_Out2 = "DatePrice.csv.out.2.txt"
strFileDP_CSV_Out3 = "DatePrice.csv.out.3.txt"

; Define the moving range of days. Must be minimal one item, maximal the total number of items.
; Note: RangeMax = 4 spans up a period of 5 days.
intRangeMax = 4


;------------------------------------------------------------------------------------------------------------------------------------------
; Cleanup and prepare the given input text file to make it readable as CSV file.

strDP = FileGet (strFileDP_RAW)

; Remove leading and trailing blanks.
While StrIndex (strDP, @LF : " ", 1, @FWDSCAN)
   strDP = StrReplace (strDP, @LF : " ", @LF)
EndWhile
While StrIndex (strDP, " " : @CR, 1, @FWDSCAN)
   strDP = StrReplace (strDP, " " : @CR, @CR)
EndWhile
strDP = StrTrim (strDP)

; Make data look CSV alike.
strDP = StrReplace (strDP, " ", ",")
intBytes = FilePut (strFileDP_CSV_Tmp, strDP)

; Get CSV data.
arrDP = ArrayFileGetCSV (strFileDP_CSV_Tmp, 0, ",")
intRowLast = ArrInfo (arrDP, 1) - 1

; Make sure to have float values and valid sortable dates (DT-19 YmdHms datetime string).
For intI = 0 To intRowLast
   arrDT = Arrayize (arrDP[intI, 0], "/")   ; Convert from MDY to YMD.
   arrDP[intI, 0] = TimeAdd (arrDT[2] : ":" : arrDT[0] : ":" : arrDT[1], "0:0:0")
   arrDP[intI, 1] = 1.0 * arrDP[intI, 1]    ; To Float, for sure.
Next
Drop (arrDT)

; Sort on first column DateTime.
ArraySort (arrDP)

; Save array to CSV file.
intBytes = ArrayFilePutCSV (strFileDP_CSV_Tmp, arrDP)


;------------------------------------------------------------------------------------------------------------------------------------------
; Do the search.

; ; Get CSV data.
; arrDP = ArrayFileGetCSV (strFileDP_CSV_Tmp, 0, ",")
; intRowLast = ArrInfo (arrDP, 1) - 1

; Append a helper column to hold the high/low indicator as character "H" or "L".
ArrayInsert (arrDP, 2, 2, "")

; Move through the array.
intRangeMax = Max (Min (intRangeMax, intRowLast), 1)
intPeakRow = 0
blnPeakExist = @FALSE
blnCheckHigh = @TRUE
fltRangeValueMax = arrDP[0, 1]
intRow = 0
While @TRUE
   Switch blnCheckHigh                          ; <==
   Case @TRUE
      For intRange = 1 To intRangeMax
         intRow = intRow + 1
         If intRow > intRowLast Then Break
         If arrDP[intRow, 1] > fltRangeValueMax ; <==
            blnPeakExist = @TRUE
            intPeakRow = intRow
            fltRangeValueMax = arrDP[intRow, 1]
         EndIf
      Next
      If blnPeakExist
         blnPeakExist = @FALSE
         intRow = intPeakRow
         arrDP[intRow, 2] = "H"                 ; <==
      Else
         blnCheckHigh = !blnCheckHigh
         intRow = intRow - intRangeMax
      EndIf
      fltRangeValueMax = arrDP[intRow, 1]
      Break
   Case @FALSE
      For intRange = 1 To intRangeMax
         intRow = intRow + 1
         If intRow > intRowLast Then Break
         If arrDP[intRow, 1] < fltRangeValueMax ; <==
            blnPeakExist = @TRUE
            intPeakRow = intRow
            fltRangeValueMax = arrDP[intRow, 1]
         EndIf
      Next
      If blnPeakExist
         blnPeakExist = @FALSE
         intRow = intPeakRow
         arrDP[intRow, 2] = "L"                 ; <==
      Else
         blnCheckHigh = !blnCheckHigh
         intRow = intRow - intRangeMax
      EndIf
      fltRangeValueMax = arrDP[intRow, 1]
      Break
   EndSwitch

   If intRow >= intRowLast Then Break
   intRangeMax = Max (Min (intRangeMax, intRowLast - intRow), 1)
EndWhile


;------------------------------------------------------------------------------------------------------------------------------------------
; Remove 'empty rows', which are no highs or no lows.

intRow = ArrInfo (arrDP, 1) - 1
While intRow > -1
   If arrDP[intRow, 2] == "" Then ArrayRemove (arrDP, intRow, 1)
   intRow = intRow - 1
EndWhile

;   ;------------------------------------------------------------------------------------------------------------------------------------------
;   ; Alternative code. Remove 'empty rows', which are no highs or no lows.
;
;   ArraySort (arrDP, @DESCENDING, 2) ; Sort empty cells to the end of the array.
;   intRow = ArrInfo (arrDP, 1) - 1
;   While arrDP[intRow, 2] == ""
;      ArrayRemove (arrDP, intRow, 1)
;      intRow = intRow - 1
;   EndWhile


;------------------------------------------------------------------------------------------------------------------------------------------
; Save array to CSV file.

intBytes = ArrayFilePutCSV (strFileDP_CSV_Out1, arrDP)

;------------------------------------------------------------------------------------------------------------------------------------------
; View result.

Run (strFileDP_CSV_Out1, "")


;------------------------------------------------------------------------------------------------------------------------------------------
; Alternative output format. Sort High/Low indicator.
; Note: Does not preserve the existing order of the date column.

ArraySort (arrDP, @ASCENDING, 2)
intBytes = ArrayFilePutCSV (strFileDP_CSV_Out2, arrDP)
Run (strFileDP_CSV_Out2, "")


;------------------------------------------------------------------------------------------------------------------------------------------
; Alternative output format. Sort High/Low indicator by date.

ArrayInsert (arrDP, 3, 2, "")    ; Append a helper column to hold the combined values from column 2 and column 0.
intRow = ArrInfo (arrDP, 1) - 1
While intRow > -1
   arrDP[intRow, 3] = arrDP[intRow, 2] : arrDP[intRow, 0]
   intRow = intRow - 1
EndWhile
ArraySort (arrDP, @ASCENDING, 3) ; Sort the helper column.
ArrayRemove (arrDP, 3, 2)        ; Remove helper column.

intBytes = ArrayFilePutCSV (strFileDP_CSV_Out3, arrDP)
Run (strFileDP_CSV_Out3, "")


:CANCEL
Exit

;==========================================================================================================================================
;   Raw data input file.
;------------------------------------------------------------------------------------------------------------------------------------------
;   6/30/2010 35.27
;   6/29/2010 36.11
;   6/28/2010 37.22
;   6/25/2010 37.59
;   6/24/2010 36.71
;   6/23/2010 38.06
;   6/22/2010 37.7
;   6/21/2010 38.78
;   6/18/2010 37.96
;   6/17/2010 38.22
;   6/16/2010 37.82
;   6/15/2010 38.14
;   6/14/2010 36.77
;   6/11/2010 36.21
;   6/10/2010 36.42
;   6/9/2010 35.01
;   6/8/2010 35.24
;   6/7/2010 34.54
;   6/4/2010 34.48
;   6/3/2010 37.13
;   6/2/2010 36.91
;   6/1/2010 35.02
;   5/28/2010 36
;   5/27/2010 36.73
;   5/26/2010 34.66
;   5/25/2010 34.76
;==========================================================================================================================================
;   Intermediate CSV data file.
;------------------------------------------------------------------------------------------------------------------------------------------
;   "2010:05:25:00:00:00","34.76"
;   "2010:05:26:00:00:00","34.66"
;   "2010:05:27:00:00:00","36.73"
;   "2010:05:28:00:00:00","36.0"
;   "2010:06:01:00:00:00","35.02"
;   "2010:06:02:00:00:00","36.91"
;   "2010:06:03:00:00:00","37.13"
;   "2010:06:04:00:00:00","34.48"
;   "2010:06:07:00:00:00","34.54"
;   "2010:06:08:00:00:00","35.24"
;   "2010:06:09:00:00:00","35.01"
;   "2010:06:10:00:00:00","36.42"
;   "2010:06:11:00:00:00","36.21"
;   "2010:06:14:00:00:00","36.77"
;   "2010:06:15:00:00:00","38.14"
;   "2010:06:16:00:00:00","37.82"
;   "2010:06:17:00:00:00","38.22"
;   "2010:06:18:00:00:00","37.96"
;   "2010:06:21:00:00:00","38.78"
;   "2010:06:22:00:00:00","37.7"
;   "2010:06:23:00:00:00","38.06"
;   "2010:06:24:00:00:00","36.71"
;   "2010:06:25:00:00:00","37.59"
;   "2010:06:28:00:00:00","37.22"
;   "2010:06:29:00:00:00","36.11"
;   "2010:06:30:00:00:00","35.27"
;==========================================================================================================================================
;   Result CSV data output file (sorted on first column).
;------------------------------------------------------------------------------------------------------------------------------------------
;   "2010:05:27:00:00:00","36.73","H"
;   "2010:06:03:00:00:00","37.13","H"
;   "2010:06:04:00:00:00","34.48","L"
;   "2010:06:10:00:00:00","36.42","H"
;   "2010:06:15:00:00:00","38.14","H"
;   "2010:06:21:00:00:00","38.78","H"
;   "2010:06:24:00:00:00","36.71","L"
;   "2010:06:30:00:00:00","35.27","L"
;==========================================================================================================================================
;   Result CSV data output file (sorted on third column, not preserving the order of the date column).
;------------------------------------------------------------------------------------------------------------------------------------------
;   "2010:06:03:00:00:00","37.13","H"
;   "2010:05:27:00:00:00","36.73","H"
;   "2010:06:10:00:00:00","36.42","H"
;   "2010:06:21:00:00:00","38.78","H"
;   "2010:06:15:00:00:00","38.14","H"
;   "2010:06:24:00:00:00","36.71","L"
;   "2010:06:30:00:00:00","35.27","L"
;   "2010:06:04:00:00:00","34.48","L"
;==========================================================================================================================================
;   Result CSV data output file (sorted on third + first column).
;------------------------------------------------------------------------------------------------------------------------------------------
;   "2010:05:27:00:00:00","36.73","H"
;   "2010:06:03:00:00:00","37.13","H"
;   "2010:06:10:00:00:00","36.42","H"
;   "2010:06:15:00:00:00","38.14","H"
;   "2010:06:21:00:00:00","38.78","H"
;   "2010:06:04:00:00:00","34.48","L"
;   "2010:06:24:00:00:00","36.71","L"
;   "2010:06:30:00:00:00","35.27","L"
;==========================================================================================================================================
;
;   Topic:  Find the highest high and lowest lows
;   Conf:  WinBatch
;   From:  supersoup rutledmj@gmail.com
;   Date:  Friday, March 11, 2011 09:58 AM
;
;   I have an excel document with 2 columns, date and price,
;   i would like to find the Max price value of the first 5 prices,
;   save the date of the max value,
;   then find the max price of the next 5 prices starting with the last highest.
;   If the next 5 doesnt have a higher high, then it looks for the lowest low.
;   Once it finds the low, it looks at the new 5 and looks for a lower low,
;   if it doesnt find one, it looks for the high.
;
;   The high of the first five is 36.73 on 5/27 and becomes the extreme high.
;   Starting on 5/27 the next high is 37.13 on 6/3, since it is higher it becomes the new extreme high.
;   Starting on 6/3 the next high is still on 6/3 so we look for the low of the range which is 34.48 on 6/4.
;   Starting with 6/4 there is nothing lower than 34.48 so 36.42 becomes the high.
;
;   In the end I want a list of all the dates that are the highest highs and all the dates that are the lowest lows.
;
;==========================================================================================================================================