Extreme Optimization > QuickStart Samples > Sorting and Filtering QuickStart Sample (VB.NET)

Extreme Optimization QuickStart Samples

Sorting and Filtering QuickStart Sample (VB.NET)

Illustrates how to sort and filter data using the VariableCollection and Variable classes (Extreme.Statistics namespace) in Visual Basic .NET.

C# 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.Mathematics.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

Copyright 2004-2007, Extreme Optimization. All rights reserved.
Extreme Optimization, Complexity made simple, M#, and M Sharp  are trademarks of ExoAnalytics Inc.
Microsoft, Visual C#, Visual Basic, Visual Studio, and Visual Studio.NET are registered trademarks of Microsoft Corporation