Sorting and Filtering QuickStart Sample (Visual Basic)

Illustrates how to sort and filter data used for statistical analysis in Visual Basic.

C# code F# code IronPython code Back to QuickStart Samples

Imports System
Imports System.Data
Imports System.Data.OleDb
' Variable classes reside in the Extreme.Statistics namespace.
imports Extreme.Statistics

Namespace Extreme.Numerics.QuickStart.VB
    ' Illustrates sorting and filtering of data sets and variables.
    Module SortingAndFiltering

        Sub Main()
            Dim table As DataTable = LoadTimeSeriesData()
            Dim timeSeries As VariableCollection = New VariableCollection(table)

            ' Next, we create some helper variables:
            Dim timeStamp As DateTimeVariable = CType(timeSeries("Date"), DateTimeVariable)
            Dim high As NumericalVariable = CType(timeSeries("High"), NumericalVariable)
            Dim low As NumericalVariable = CType(timeSeries("Low"), NumericalVariable)
            Dim open As NumericalVariable = CType(timeSeries("Open"), NumericalVariable)
            Dim close As NumericalVariable = CType(timeSeries("Close"), NumericalVariable)
            Dim volume As NumericalVariable = CType(timeSeries("Volume"), NumericalVariable)

            ' Let's print some basic statistics for the full data set:
            Console.WriteLine("Total # observations: {0}", timeSeries.Observations.Count)
            Console.WriteLine("Average volume: {0:F0}", volume.Mean)
            Console.WriteLine("Total volume: {0:F0}", volume.Sum)

            '
            ' Filtering
            '

            ' Next, we create a filter, selecting observations where the close price
            ' was greater than the open price:
            Dim myFilter As Filter = close.Filters.GreaterThan(open)
            ' and set the VariableCollection's Filter property.
            timeSeries.Filter = myFilter

            ' Data is now filtered:
            Console.WriteLine("Filtered # observations: {0}", timeSeries.Observations.Count)
            ' Summary statistics apply only to the filtered data:
            Console.WriteLine("Average volume: {0:F0}", volume.Mean)
            Console.WriteLine("Total volume: {0:F0}", volume.Sum)

            ' Filters can be combined using set operations.
            Dim volumeFilter As Filter = volume.Filters.Between(200000000.0, 300000000.0)
            Console.WriteLine("Volume filtered #: {0}", volumeFilter.FilteredLength)
            Dim combinedFilter As Filter = Filter.Intersection(volumeFilter, myFilter)
            ' Alternatively: combinedFilter = volumeFilter & filter
            Console.WriteLine("Combined filtered #: {0}", combinedFilter.FilteredLength)
            timeSeries.Filter = combinedFilter

            '
            ' Sorting
            '

            ' The simplest way to sort data is calling the Sort method 
            ' with the name of the variable to sort on:
            timeSeries.Sort("High")
            For i As Integer = 0 To timeSeries.Observations.Count - 1
                Console.Write("{0,8:F2}", high(i))
            Next
            Console.WriteLine()

            ' We can also create a CollectionSortOrder object that
            ' defines the sort order over multiple fields/variables.
            ' We first create an order with one variable:
            Dim ordering As CollectionSortOrder = New CollectionSortOrder(timeSeries, _
                "High", SortOrder.Ascending)
            ' We then use the Then method repeatedly to add more sort fields:
            ordering = ordering.Then("Date", SortOrder.Descending)

            ' And call the Sort method with the CollectionSortOrder object:
            timeSeries.Filter = high.Filters.Between(25.11, 25.15)
            ' Sort orders are automatically combined with filters.
            timeSeries.Sort(ordering)

            Console.WriteLine("Sorted on High, Date (desc.)")
            For i As Integer = 0 To timeSeries.Observations.Count - 1
                Console.WriteLine("{0,8:F2} {1,11}", high(i), timeStamp(i).ToShortDateString())
            Next
            Console.WriteLine()

            Console.WriteLine("Sorted on High, Date")
            timeSeries.Sort(New CollectionSortOrder(high, SortOrder.Ascending).Then _
               (timeStamp, SortOrder.Ascending))
            For i As Integer = 0 To timeSeries.Observations.Count - 1
                Console.WriteLine("{0,8:F2} {1,11}", high(i), timeStamp(i).ToShortDateString())
            Next
            Console.WriteLine()

            ' The sort order remains, even if we change the filter:
            timeSeries.Filter = high.Filters.Between(25.21, 25.25)
            Console.WriteLine("Same sort order, different data")
            For i As Integer = 0 To timeSeries.Observations.Count - 1
                Console.WriteLine("{0,8:F2} {1,11}", high(i), timeStamp(i).ToShortDateString())
            Next
            Console.WriteLine()

            Console.Write("Press Enter key to exit...")
            Console.ReadLine()
        End Sub

        Private Function LoadTimeSeriesData() As DataTable
            Dim filename As String = "..\..\..\..\Data\MicrosoftStock.xls"
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
            Dim cnn As OleDbConnection = Nothing
            Dim ds As DataSet = New DataSet
            Try
                cnn = New OleDbConnection(connectionString)
                cnn.Open()
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("Select * from [MicrosoftStock$]", cnn)
                adapter.Fill(ds)
            Catch ex As OleDbException
                Console.WriteLine(ex.InnerException)

            Finally
                If Not (cnn Is Nothing) Then
                    cnn.Close()
                End If
            End Try
            Return ds.Tables(0)
        End Function

    End Module

End Namespace