Need help with data feed SPIKE Detection

John117 · March 7, 2009

Hey guys,

I am dealing with real time data and need to be alerted if there is a spike.

I would like the alert to be based both TIME and RANGE.

The data comes from an update loop that runs every hundredth of a second but the data may have not changed by then.

I get about 0-4 changes per second that are in the range of 3-5 spaces past the decimal point.

Ex 1.00023, 1.00024, 1.00041

The example below is shorter just for example and shows what I would like in an alert.

I am currently only storing the most up to data data.

Should I add a second loop around the existing one an store the data to variabe and check it each time the internal loop cycles? How do I include time?

A spike of interest will happen from top to bottom in less than a minute.

Thanks!

1, 1, 1, 0.9, 0.9, 1, 1, 1.1, 1.1, 1, 1, 1 = do nothing but show variance of -.1, +.1

1, 1.1, 1, 1.2, 1.5, 1.8, 1.8, 2, 1.9, 2.2, 2.5, 3 = Alert and show variance of +2

Current Code:

While 1
    Data()
    Sleep(10)
WEnd
    
    
Func Data()
    $DataTable = $Source.FindMainTable("Data") ;Connect to data feed.
    
    Local $DataArray1[$DataTable.RowCount] ;Count rows from data source.
    For $i = 1 To $DataTable.RowCount -1 ;Count rows from data source and subtract one since arrays start with 0 and not 1
        $DataArray1[$i] = $DataTable.CellValue($i, "Info1") & "," & $DataTable.CellValue($i, "Info2") ; Create array for all rows and two columns using "Info1,Info2" format
    Next ;  loop
    
    $Numbers1 = StringSplit($DataArray1[1], ",") ;break the first row and  "Info1,Info2" format into its own array.
    If GUICtrlRead($MyInputBox1) <> $Numbers1[1] Then ;if info in first row, first column is the different than previous, update myinputbox1
        GUICtrlSetData($MyInputBox1, $Numbers1) ;it would update this
    EndIf
    
    $Numbers2 = StringSplit($DataArray1[1], ",") ; same for first row, column 2 . . .
    If GUICtrlRead($MyInputBox2) <> $Numbers2[2] Then ; . . . get info for first row, column 2
        GUICtrlSetData($MyInputBox2, $Numbers2) ; . . .
    EndIf
EndFunc

For now,

I only want to monitor the spikes in $numbers1 (first row, first column - "Info1")

Thanks for any suggestions!!!!

PsaltyDS · March 7, 2009

Hey guys,

I am dealing with real time data and need to be alerted if there is a spike.

I would like the alert to be based both TIME and RANGE.

The data comes from an update loop that runs every hundredth of a second but the data may have not changed by then.

I get about 0-4 changes per second that are in the range of 3-5 spaces past the decimal point.

Ex 1.00023, 1.00024, 1.00041

Then why not sample the data every 200 milliseconds? If you don't expect relevant changes 100 times per second, why sample that often?

The example below is shorter just for example and shows what I would like in an alert.
I am currently only storing the most up to data data.
Should I add a second loop around the existing one an store the data to variabe and check it each time the internal loop cycles? How do I include time?

A spike of interest will happen from top to bottom in less than a minute.

Thanks!

1, 1, 1, 0.9, 0.9, 1, 1, 1.1, 1.1, 1, 1, 1 = do nothing but show variance of -.1, +.1
1, 1.1, 1, 1.2, 1.5, 1.8, 1.8, 2, 1.9, 2.2, 2.5, 3 = Alert and show variance of +2

Current Code:
While 1
    Data()
    Sleep(10)
WEnd
    
    
Func Data()
    $DataTable = $Source.FindMainTable("Data");Connect to data feed.
    
    Local $DataArray1[$DataTable.RowCount];Count rows from data source.
    For $i = 1 To $DataTable.RowCount -1;Count rows from data source and subtract one since arrays start with 0 and not 1
        $DataArray1[$i] = $DataTable.CellValue($i, "Info1") & "," & $DataTable.CellValue($i, "Info2"); Create array for all rows and two columns using "Info1,Info2" format
    Next;  loop
    
    $Numbers1 = StringSplit($DataArray1[1], ",");break the first row and  "Info1,Info2" format into its own array.
    If GUICtrlRead($MyInputBox1) <> $Numbers1[1] Then;if info in first row, first column is the different than previous, update myinputbox1
        GUICtrlSetData($MyInputBox1, $Numbers1);it would update this
    EndIf
    
    $Numbers2 = StringSplit($DataArray1[1], ","); same for first row, column 2 . . .
    If GUICtrlRead($MyInputBox2) <> $Numbers2[2] Then; . . . get info for first row, column 2
        GUICtrlSetData($MyInputBox2, $Numbers2); . . .
    EndIf
EndFunc
For now,
I only want to monitor the spikes in $numbers1 (first row, first column - "Info1")

Thanks for any suggestions!!!!

Time is easy to include with TimerInit() and TimerDiff(). But you have many terms to define before you have the required specificity to code it. What is a "spike"? What if changes slowly? Is that still a spike? How slowly? You can't just keep the latest and previous values, because that doesn't tell you about changes over time longer than two samples.

I doesn't sound like you have something difficult to code, you just haven't spent the time to define exactly what you want yet.

Vague specs = vague results.

weaponx · March 7, 2009

It sort of makes sense to me, there is some info missing though. Like what is the tolerance for an alert? Is it in absolute or relative value?

John117 · March 8, 2009

Then why not sample the data every 200 milliseconds? If you don't expect relevant changes 100 times per second, why sample that often?

Because I have no way of knowing if it has updated other than to sample it. It may not update for 3 seconds. It may updated 30 times in a second. Being as up-to-date as possible is an absolute must.

What is a "spike"? What if changes slowly? Is that still a spike? How slowly? You can't just keep the latest and previous values, because that doesn't tell you about changes over time longer than two samples.

A spike could be defined as a rapid change in rate within a few seconds. "1-10 sec"

For example if it changed 20 in ten seconds, that would be huge! -it could change 50 or 200. Any change under 10 would not be of much interest. Any change taking 5 min would not be of much use even if it were big. Its a big movement but not a spike. -needs to stay with in a min.

Ex of 20 change:

1.2488 to 1.2508

Below is an example that I made to detect a change.

I needed a rate feed for demo so I just made a simple loop that will set off each group.

The alert levels are just for example to help show how range will cause a different response. I put it in Excel to show it run and slow it a little. The sleep is only for testing.

#include <Excel.au3>
#include <Array.au3>
;#include <GUIConstantsEx.au3>
;#include <WindowsConstants.au3>
;#include <StaticConstants.au3>
;#include <Date.au3>
;#include <string.au3>
Global $RateStore = "", $TimeStore = ""
HotKeySet("{NumPad1}", "Func1")
Global $oExcel = _ExcelBookNew() ;Create new book, make it visible

While 1
    Sleep(10)
WEnd

Func Func1()
    Local $e, $i
    For $e = 20 To 1 Step -1
        Local $RateStore[21]
        For $i = 1 To $e Step 1
            _ExcelWriteCell($oExcel, $i, 1, 1) ;Write to the Cell
            Sleep(100)
            $RateStore[$i] = _ExcelReadCell($oExcel, 1, 1)
            ;$RateStore[$i] = $i
        Next
        If $RateStore[1] <> $RateStore[$e] Then
            ;MsgBox(1, "Changed", "Rate1 <> Rate20")
            If $RateStore[1] < $RateStore[$e] Then
                ;MsgBox(1, "UpSpike", "Rate1 < Rate20")
                If ($RateStore[$e] - $RateStore[1]) >= 20 Then UpSpike20()
                If ($RateStore[$e] - $RateStore[1]) >= 15 And ($RateStore[$e] - $RateStore[1]) < 20 Then UpSpike15()
                If ($RateStore[$e] - $RateStore[1]) >= 10 And ($RateStore[$e] - $RateStore[1]) < 15 Then UpSpike10()
                If ($RateStore[$e] - $RateStore[1]) >= 5 And ($RateStore[$e] - $RateStore[1]) < 10 Then UpSpike5()
                If ($RateStore[$e] - $RateStore[1]) >= 0 And ($RateStore[$e] - $RateStore[1]) < 5 Then UpSpike0()
            EndIf
            If $RateStore[1] > $RateStore[$e] Then
                ;MsgBox(1, "DownSpike", "Rate1 > Rate20")
                If ($RateStore[1] - $RateStore[$e]) >= 20 Then DownSpike20()
                If ($RateStore[1] - $RateStore[$e]) >= 15 And ($RateStore[$e] - $RateStore[1]) < 20 Then DownSpike15()
                If ($RateStore[1] - $RateStore[$e]) >= 10 And ($RateStore[$e] - $RateStore[1]) < 15 Then DownSpike10()
                If ($RateStore[1] - $RateStore[$e]) >= 5 And ($RateStore[$e] - $RateStore[1]) < 10 Then DownSpike5()
                If ($RateStore[1] - $RateStore[$e]) >= 5 And ($RateStore[$e] - $RateStore[1]) < 5 Then DownSpike5()
            EndIf
        EndIf
        ;_ArrayDisplay($RateStore, "Well?")
        ;MsgBox(1, "Rate1", $RateStore[1])
        ;MsgBox(1, "Rate20", $RateStore[$e])
        ;MsgBox(1, "Rate" & $e & " - Rate1", ($RateStore[$e] - $RateStore[1]))
        Global $RateStore = ""
    Next
EndFunc   ;==>Func1

Func UpSpike20()
    ToolTip("UpSpike20", 0, 0)
EndFunc   ;==>UpSpike20

Func UpSpike15()
    ToolTip("UpSpike 15", 0, 0)
EndFunc   ;==>UpSpike15

Func UpSpike10()
    ToolTip("UpSpike 10", 0, 0)
EndFunc   ;==>UpSpike10

Func UpSpike5()
    ToolTip("UpSpike 5", 0, 0)
EndFunc   ;==>UpSpike5

Func UpSpike0()
    ToolTip("UpSpike 0", 0, 0)
EndFunc   ;==>UpSpike0

Func DownSpike20()
    ToolTip("DownSpike20", 0, 0)
EndFunc   ;==>DownSpike20

Func DownSpike15()
    ToolTip("DownSpike15", 0, 0)
EndFunc   ;==>DownSpike15

Func DownSpike10()
    ToolTip("DownSpike10", 0, 0)
EndFunc   ;==>DownSpike10

Func DownSpike5()
    ToolTip("DownSpike5", 0, 0)
EndFunc   ;==>DownSpike5

Func DownSpike0()
    ToolTip("DownSpike0", 0, 0)
EndFunc   ;==>DownSpike0

This includes more than will be used. I have both loops setup just to test all alert indicators.

I am sampling first and last with this. The problem is what if the spike is between 15 on the first loop and 5 on the next loop after the first is dumped ???

PsaltyDS · March 8, 2009

I am sampling first and last with this. The problem is what if the spike is between 15 on the first loop and 5 on the next loop after the first is dumped ???

I think you need to keep more historical values to review for the trends you want to detect.

Some functions that will help with that:

_ArrayPush()

_ArrayPop()

_ArrayMin()

_ArrayMax()

John117 · March 9, 2009

Posted Image

If the image is not working . . .

https://5kmxuq.bay.livefilestore.com/y1pNyj...VHxz8/Chart.jpg

What I am trying to do is detect a spike like the one you see in the middle of the pic above. (Big red line that is going down)

I have written a script to get the data. The code is posted above. I must check the data as often as possible. I am really at a loss as for how to check for the spike.

Questions:

1. should I only store the rate if it has not changed? -could help limit the array

2. Should I store both the rate and time together? Such as "$rate & "," & now()"

3. How can I reference the time then against the current time in a efficient manor? -when each rate has a stored time? Can you compare time?

4. How can I detect the begining of the spike against the end ? "average value against max value"? -this would require me to know how to reference time and to have stored it. I can see how the arraymin() and arrayMax() could work here - I am thinking that average would be bad because it may factor in the spike and boost the average.

can anyone help me with a demo to see this in action?

Thanks!

PsaltyDS · March 9, 2009

If the image is not working . . .
https://5kmxuq.bay.livefilestore.com/y1pNyj...VHxz8/Chart.jpg
What I am trying to do is detect a spike like the one you see in the middle of the pic above. (Big red line that is going down)
I have written a script to get the data. The code is posted above. I must check the data as often as possible. I am really at a loss as for how to check for the spike.
Questions:
1. should I only store the rate if it has not changed? -could help limit the array
2. Should I store both the rate and time together? Such as "$rate & "," & now()"
3. How can I reference the time then against the current time in a efficient manor? -when each rate has a stored time? Can you compare time?
4. How can I detect the begining of the spike against the end ? "average value against max value"? -this would require me to know how to reference time and to have stored it. I can see how the arraymin() and arrayMax() could work here - I am thinking that average would be bad because it may factor in the spike and boost the average.
can anyone help me with a demo to see this in action?
Thanks!

Did you look at the _ArrayPush() and other functions I mentioned? Your array does not change in size as data is collected. So if you declared an array with 100 elements and use _ArrayPush() to capture the data at each update, you will always have the latest 100 values.

Once the array is filled (it is important not to start testing values until it has filled up at least once with usable data) you can compare _ArrayMin() and _ArrayMax() for the range of values over that many sample times.

You have three values to change in defining a "spike":

1) The array size (number of values used) = width of data window

2) The sampling frequency

3) The delta value (absolute value difference) that indicates a spike

John117 · March 9, 2009

P, Thanks for the reply!

Based on what you supplied, here is a demo. It does will not be dumped and after initial run will only be updated by new data.

Right now it is loaded with a 100 point spike. Now if that were under a minute, that would be huge. But if it took a day, it would just be a direction.

How can I account for time?

Thanks!

#include <Excel.au3>   
 #include <Array.au3>
Global $RateStore[100]
HotKeySet("{NumPad1}", "Func1")
HotKeySet("{NumPad2}", "Func2")
Global $oExcel = _ExcelBookNew() ;Create new book, make it visible
While 1
    Sleep(10)
WEnd 

Func Func1()
    For $e = 1 To 100 Step 1
    _ArrayPush($RateStore, $e)
Next
_ArrayDisplay($RateStore, "Are there 100?")
EndFunc

Func Func2() ;this is set up to manually input a change (in excel - other than "100") to verify that it will only update with a rate change. 
    If $RateStore[99] <> _ExcelReadCell($oExcel, 1, 1) Then
        _ArrayPush($RateStore, _ExcelReadCell($oExcel, 1, 1))
    EndIf
        _ArrayDisplay($RateStore, "Did it update?")
EndFunc

PsaltyDS · March 9, 2009

P, Thanks for the reply!
Based on what you supplied, here is a demo. It does will not be dumped and after initial run will only be updated by new data.

Right now it is loaded with a 100 point spike. Now if that were under a minute, that would be huge. But if it took a day, it would just be a direction.

How can I account for time?

Thanks!
#include <Excel.au3>   
 #include <Array.au3>
Global $RateStore[100]
HotKeySet("{NumPad1}", "Func1")
HotKeySet("{NumPad2}", "Func2")
Global $oExcel = _ExcelBookNew();Create new book, make it visible
While 1
    Sleep(10)
WEnd 

Func Func1()
    For $e = 1 To 100 Step 1
    _ArrayPush($RateStore, $e)
Next
_ArrayDisplay($RateStore, "Are there 100?")
EndFunc

Func Func2();this is set up to manually input a change (in excel - other than "100") to verify that it will only update with a rate change. 
    If $RateStore[99] <> _ExcelReadCell($oExcel, 1, 1) Then
        _ArrayPush($RateStore, _ExcelReadCell($oExcel, 1, 1))
    EndIf
        _ArrayDisplay($RateStore, "Did it update?")
EndFunc

You don't want to fill all 100 data elements at an unknown rate in a tight loop. You need an accurate AdLibEnable() set up to get ONE _ArrayPush() every known sample time. If you collect a single data point every 10 milliseconds, for example, then you know that the 100 element array represents exactly 1 full second of data.

If you want to detect a "spike" over a max of 2 seconds, then you have the option of taking data point every 20 milliseconds, or make the array 200 elements. Whatever combination of sample rate and window size gets your required detection parameters.

You are not free to choose just any value for these numbers. How often is the source data updated? If you sample every 50 milliseconds, for example, is that enough time for a "spike" to occur and then return to normal without having been detected? That depends on the details of your source.

This is why I said earlier you needed more thinking on your specs before you could code it.

weaponx · March 9, 2009

Using a method like this will eliminate any confusion about the sample size and intervals. In this case our sample size is 60 seconds and the interval (rate) is 500 ms, netting an array with 120 elements (60*1000/500). If the sample size is 60 seconds and the rate is 250 ms our array would have 240 elements (60*1000/250).

So you will use $iSampleRate to determine when to retrieve the next value and use _ArrayPush to add it to the sample set. This is essentially a queue (First in first out).

Const $iSampleSize = 60*1000 ;Milliseconds - Array will only contain enough elements to cover this time period
Const $iSampleRate = 500 ;Milliseconds - Each array element will represent this time period
Const $iNumElements = $iSampleSize / $iSampleRate ;The number of elements in the sample set
Const $fTolerance = 50 ;Percent - Any change in the sample set exceeding this amount will be flagged

Dim $aData[$iNumElements]

;Fill array with fake data
For $X = 0 to $iNumElements-1
    $aData[$X] = Random(1,2)
Next

;Insert spike at random location
$aData[Random(0,$iNumElements-1,1)] = 3

;Dump array to console
;For $X = 0 to $iNumElements-1
;    ConsoleWrite($aData[$X] & @CRLF)
;Next

;Analyze array
Dim $fLowest = $aData[0]
Dim $iLowestElement = 0

Dim $fHighest = 0
Dim $iHighestElement = 0

For $X = 0 to $iNumElements-1
    ;Find lowest value
    If $aData[$X] < $fLowest Then
        $fLowest = $aData[$X]
        $iLowestElement = $X
    EndIf
    
    ;Find highest value
    If $aData[$X] > $fHighest Then
        $fHighest = $aData[$X]
        $iHighestElement = $X
    EndIf    
Next

$fDifference = Abs(($fLowest-$fHighest)/$fHighest) * 100 ;Percent - Difference between lowest and highest value in the sample set

ConsoleWrite("Lowest value: " & $fLowest & " @ element: " & $iLowestElement & @CRLF)
ConsoleWrite("Highest value: " & $fHighest & " @ element: " & $iHighestElement & @CRLF)
ConsoleWrite("Difference: " & $fDifference & " %" & @CRLF)

If $fDifference > $fTolerance Then
    MsgBox(0,"","Spike detected")
EndIf

Edited March 9, 2009 by weaponx

John117 · March 11, 2009

PsaltyDS - Thanks!

I was just using te demo to test the process to see it kick something out . . . the AdLibEnable() was the key! - I forgot it existed :-)

WeaponX - Thanks!

I was able to use most of your stucture with the AdLibEnable() function. It works perfectly to get me to my next section. (how to react based on direction)

Thanks guys!!!

Need help with data feed SPIKE Detection

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members