Function CrossOver in VB.net

[Gum]

I would like to create a function that returns true whenever Array1 crosses
over Array2. For example:
Array1 Array2
1.6542 1.6540 False
1.6541 1.6540 True
1.654 1.6540 False
1.6539 1.6540 False
Thus given any pair of numbers (Array1,Array2), whenever Array1 crosses over
Array2 (Array1>Array2) that instance is true, but it is NOT TRUE for any
other instance in which Array1>Array2 (see above table where the last record
is the first - desc order).

 

[Patrice]

It looks like you want just to return true if Array1(i)>Array2(i) and
Array1(i+1)<=Array2(i+1)...

 

[Family Tree Mike]

I would like to create a function that returns true whenever Array1 crosses
over Array2. For example:
Array1 Array2
1.6542 1.6540 False
1.6541 1.6540 True
1.654 1.6540 False
1.6539 1.6540 False
Thus given any pair of numbers (Array1,Array2), whenever Array1 crosses over
Array2 (Array1>Array2) that instance is true, but it is NOT TRUE for any
other instance in which Array1>Array2 (see above table where the last record
is the first - desc order).

Patrice may be right with her answer, but another interpretation is that
you may want is whenever Math.Sign(Array2[idx-1] - Array1[idx-1]) is not
equal to Math.Sign(Array2[idx] - Array1[idx]). This would handle the
"crossover" being in either direction.

That said, looking at your data, I would have said the crossover occurs
at the third line, since they are equal there.

 

[James Hahn]

The description you have provided is inconsistent with the example. The way
I read it, I would have thought you were looking for:
1.6542 1.6540 +
1.6541 1.6540 + No crossover - false
1.654 1.6540 0 Crossover - true
1.6539 1.6540 - No Ccossover - false

Or, maybe you have given us the data backwards (as per your last comment).
So the data really is:

1.6539 1.6540 -
1.654 1.6540 0 (assume - = 0) - false
1.6541 1.6540 + Crossover - true
1.6542 1.6540 + No Crossover

If the last one is the actual case, then the function simply needs to invert
the arrays, scan the number pairs calculating the test condition, and return
whenever the indicator changes from 0 to +

Private Function ArrayTest(ByVal Array1() As Double, ByVal Array2() As
Double) As Boolean()
'Test each array pair and return true on change from 0 to +
Array1.Reverse()
Array2.Reverse()
'Function will fail if Array2 is shorter than Array1
Dim Result(Array1.Length - 1) As Boolean
Dim C As Integer = 0
Dim C1 As Integer
For I As Integer = 1 To Array1.Length - 1
C1 = Math.Sign(Array1(I) - Array2(I))
If C = 0 And C1 = +1 Then
Result(I) = True
Else
Result(I) = False
End If
C = C1
Next I
Result.Reverse()
Return Result
End Function

 

[Gum]

A sample of the data with time is as follows:
09/09/2009 01:41:43 1.6513 1.6509 False
09/09/2009 01:41:42 1.6511 1.6509 False
09/09/2009 01:41:42 1.6510 1.6509 False
09/09/2009 01:41:41 1.6510 1.6509 False
09/09/2009 01:41:40 1.6510 1.6509 False
09/09/2009 01:41:40 1.6510 1.6509 True
09/09/2009 01:41:39 1.6509 1.6509 False
09/09/2009 01:41:38 1.6509 1.6509 False
09/09/2009 01:41:37 1.6509 1.6509 False
09/09/2009 01:41:36 1.6508 1.6509 False

The data could be presented with the last data in the bottom row (instead of
the first row as shown above) as it is captured instantly (at the times
above) and I need to convert it into arrays before the function crossover can
be applied.

For this I have used:
dim price1 as decimal
dim price2 as decimal
dim array1() as string = {"000"} 'to init the array
dim array2() as string = {"000"}
'to populate the arrays both arrays same length
dim i as integer
For i = 0 To array1(i).length-1
array1(i) = Price1.ToString
array2(i) = Price2.ToString
Next

Thus based on the table above at an instance, i would be top row:
09/09/2009 01:41:43 1.6513 1.6509 False
and i-9 would be the bottom:
09/09/2009 01:41:36 1.6508 1.6509 False

Then I apply the function and get the following error:
"Value of type '1-dimensional array of String' cannot be converted to
'1-dimensional array of Decimal' because 'String' is not derived from
'Decimal'."

Am I populating the arrays properly? Would this method of indeterminate
population of the array lead to decreased speed due to the size of the array
(over 25,000)? Any alternatives?

The conversion .. ToString appeared acceptable except when applied to your
function: ArrayTest

 

[James Hahn]

I can't see how you are getting Price1 and Price2 out of the file. I can't
see why you need to have the arrays as type String, and I can't see why you
need to use arrays when a List(Of would be much more effective, but assuming
that Double will do as well as string and that Price1 and Price2 are the
third and fourth elements of each line of the file, then it becomes
something like:

Dim Prices() as string = Split(File.ReadAllLines(Path), vbCrLf) 'Or however
you are getting at that file data.

Dim Array1(Prices.Count-1) as Double
Dim Array2(Prices.Count-1) as Double

For I as Integer = 0 to Prices.Count-1
Dim A() as String = Prices(I).Split()
Array1(I) = Val(A(2))
Array2(I) = Val(A(3))
Next I

Then you can use the code I provided earlier (assuming that my assumption
there about what you are trying to do is correct).

Notice that there is no need to initialise the arrays. Also, Decimal has
some advantages, but Double is better unless you specifically need some
feature that Decimal provides. Since you are not doing any calculations
other than compares, Decimal is not needed.

If you used a List(Of structures instead of two arrays, you could add the
result field as a third element of the structure (and even keep the date and
time in the structure, if needed). It would probably be much faster, as
well.

 

[Gum]

I have been looking closely at your remarks: "..can't see how.." and your
suggestions regarding the data types. This is how my code looks:
Module Module1

Sub Main()
Dim myDataReader As SqlDataReader
Dim mySqlConnection As SqlConnection
Dim mySqlCommand As SqlCommand
Dim _date As DateTime
Dim Price1 As String
Dim Price2 As String
Dim CommandString As String
Try


mySqlConnection = New SqlConnection("server=myserver;Integrated
Security=SSPI;database=TestMe")
CommandString = "select * from testData"

mySqlCommand = New SqlCommand(CommandString, mySqlConnection)
mySqlConnection.Open()
myDataReader =
mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
Do While (myDataReader.Read())
_date = myDataReader.GetDateTime(0).ToString
Price1 = myDataReader.GetValue(1)
Price2 = myDataReader.GetValue(2)
Loop
mySqlConnection.Close()

Dim Array1(Price1.Count - 1) As Double
Dim Array2(Price2.Count - 1) As Double

For I As Integer = 0 To Price1.Count - 1
Array1(I) = Val(Price1)
Array2(I) = Val(Price2)
Next I
'ERROR in line below:Value of type '1-dimensional array of Boolean' cannot
be 'converted to 'Boolean'

If ArrayTest(Array1, Array2) Then
'do this
End If

Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try
End Sub
End Module

The testdata structure is:
date datetime,
Price1 float,
Price2 numeric(18,4),
iRowID int,
Xross bit

Function ArrayTest is same as yours.

 

[James Hahn]

I have defined the result array " As Boolean() " or an array of booleans.
This array contains one result (True or False) for each price pair. The two
arguments are both arrays, so the result is an array where each array
element describes whether or not a crossover occurred at that point in the
array.

So the equivalent to your calling code would be something like:
Dim Result() as Boolean = ArrayTest(Array1, Arrray2)

You could then examine each element of Result() to see the points at which a
crossover occurred.

However, you code to populate the source arrays won't work:
Dim Price1 As String
Dim Price2 As String
Do While (myDataReader.Read())
_date = myDataReader.GetDateTime(0).ToString
Price1 = myDataReader.GetValue(1)
Price2 = myDataReader.GetValue(2)
Loop
That code is putting each successive pair of prices into the same two string
variables.

It should actually be:
Dim Array1(PriceCount - 1) As Double
Dim Array2(PriceCount - 1) As Double
Dim I as Integer = 0
Do While (myDataReader.Read())
'_date = myDataReader.GetDateTime(0).ToString
Array1(I) = myDataReader.GetValue(1)
Array2(I) = myDataReader.GetValue(2)
I += 1
Loop

Whether or not you want to process _date depends on what you need to do with
the results, but you could put the dates into a third matching array of
datetimes.

I have had to assume that you know how many price data pairs there will be
(PriceCount). If you can't know this then you can:
1. Assume a large number and hope it is never exceeded,
2. Read the data in two passes - one to find out how many there are and the
second one to put them into the arrays,
3. Redim the arrays as you add each new price pair, or
4. Use a generic List(of instead of an array.

 

[Gum]

I got your points. It would not be possible to know before hand the array
length. Price1 has a variable arrival time, so that in order to get a pair,
whenever Price1 is updated then I can match with the existing Price2 and that
would create sufficient conditions for the function. To do this, I am
looking at creating a datatable, say:

Dim dt As DataTable
Dim Dte As DataColumn = New DataColumn("Date")
Dte.DataType = System.Type.GetType("System.datetime")
Dim Price1 As DataColumn = New DataColumn("Price1")
Price1.DataType = System.Type.GetType("System.decimal")
Dim Price2 As DataColumn = New DataColumn("Price2")
Price2.DataType = System.Type.GetType("System.decimal")

and then I would need to dynamically enter the values into each row whenever
Price1 is updated. (I am not sure how this can be done).

I would only need at maximum 3 rows to compute the function, so is it
possible that I can restrict this to ensure reduced overheads?
The rows of the datatable can be assessed via - " dt.Rows.count " - (if not
fixed to 3 or 4 as above).
If the Price1 and Price2 are in a datatable then I can access them at t and
t-1 where t would be the row number.

Is this a feasible approach? If so, how to set up the datatable?

 

[James Hahn]

What are the three rows that you need? I would have thought two is enough
(my code only requires two).

You need to determine what event will invoke the calculation. This could be
simply a decision to run a process that updates the database, or it could be
an event signalled by the arrival of a new item of data, or it could be a
timer event that causes you to go look at the database and see if something
new has happened.

Whatever the event is, the procedure is essentially the same.

When you need to update the database with the result of the calculation,
simply get the two lines of data from the database (T and T-1), extract the
prices you need, call the calculation routine, and put the result back into
the database at row T. A form of my original function rewritten to be
called
once for each row would look something like this:

Private Function CrossOverTest(PriceA1 As Double, ByVal PriceA2 As Double, _
PriceB1 As Double, ByVal PriceB2 As Double) _
As Boolean
'Test these two price pairs and return true on change from previous

value of 0 to +
Dim C0 As Integer = Math.Sign(PriceA1 - PriceA2)
Dim C1 As Integer = Math.Sign(PriceB1 - PriceB2)
If C0 = 0 And C1 = +1 Then
Result = True
Else
Result = False
End If
Return Result
End Function

You would call the function using code like this:
'Get crossover value (true/false) for last (most recent) row of table
Dim T as integer = dt.rows.count-1

Dim R1 as DataRow = dt.Rows(T-1)
Dim R2 as DataRow = dt.Rows(T)

Dim PA1 As Double = R1.Columns("Price1")
Dim PA2 As Double = R1.Columns("Price2")
Dim PB1 As Double = R2.Columns("Price1")
Dim PB2 As Double = R2.Columns("Price2")
R2.Columns("Crossed") = CrossOverTest(PA1, PA2, PB1, PB2)

 

[Gum]

You are right... I need only 2 rows. However in the code R1 and R2 belong to
DataRow which 'Columns' is not a member of, so whenever they are used as
'R1.Columns(' ... or... 'R2.Columns(', errors result.

 

[James Hahn]

I'm writing the code from memory, so there could be any sort of error, but
if you can't understand the meaning of the error it is important to quote
the exact error message and the code that created it. I think you are
saying that the R1 and R2 property references aren't working. Try
R1("Price1") etc instead.