Simple trend calculation

Wendelius

4.97/5 (14 votes)

15 Aug 2017CPOL6 min read

61.4K

2.1K

Simple linear trend calculation with different types for X values implemented in C#, VB, and F#

Introduction

I needed to have a simple linear trend calculation for X values either as double or datetime. So basically the Y value for a single item is always a double but the type of the X varies. Beyond that, I needed basic statistics calculated from the values. These included:

Slope
Y-intercept
Correlation coefficient
R-squared value

Excerpt from the user interface

As an additional step I decided to have my first look at F# so now one implementation of the calculation is done using F#. I have to admit that it isn't yet F#ish and looks more like C# but on the other hand that may help readers to see equivalences (and differences).

Formulas used in calculations

Since the requirement was to do the calculation in a similar way as it would be done in Excel, I used the same variations for formulas as Excel uses. This also made it simple to check the correctness of the calculations. So the formulas are:

Line

$\large y=mx+b$

where

m is slope
x is the horizontal axis value
b is the Y-intercept

Slope calculation

$\large s=\frac{\sum \left ( x - \bar{x} \right ) \left ( y - \bar{y} \right )}{\sum \left ( x - \bar{x} \right )^{2}}$

where

x and y are individual values
accented x and y are averages for the corresponding values

The correlation coefficient

$\large c=\frac{\sum \left ( x - \bar{x} \right ) \left ( y - \bar{y} \right )}{\sqrt{\sum \left ( x - \bar{x} \right )^{2} \left ( y - \bar{y} \right )^{2}}}$

where again

x and y are individual values
accented x and y are averages for the corresponding values

R-squared value

$\large r^{2}=1 - \frac{\sum \left ( y - \hat{y} \right )^{2}}{\sum y^{2} - \frac{\left ( \sum y \right )^{2}}{n}}$

where

y is individual values
accented y (with a hat) is the corresponding calculated trend value
n is the count of values.

Classes for value items

The first thing is to create the classes for the actual value items, both double and datetime. Basically the classes are simple, just properties X and Y. But things get a bit more complicated since the type of the X varies. Instead of using an object property I wanted to have separate classes for the different item types and to be able use double and datetime types instead of object. This approach quickly lead to using an abstract base class with generics.

However, using generics for X introduces a new problem, how to use the same calculation for two different data types. Since I didn’t have any specific requirements concerning the calculation, I decided to convert the X values always to double. In order to use this value in calculation an extra property ConvertedX is defined.

The classes look like following

Abstract (MustInherit) base class for value items

namespace TrendCalculus {
   /// <summary>
   /// Base class for value items
   /// </summary>
   /// <typeparam name="TX">Type definition for X</typeparam>
   public abstract class ValueItem<TX> : IValueItem {

      private double _y;

      /// <summary>
      /// Raised when the data in the item is changed
      /// </summary>
      public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;

      /// <summary>
      /// The actual value for X
      /// </summary>
      public abstract TX X { get; set; }

      /// <summary>
      /// The value for X for calculations
      /// </summary>
      public abstract double ConvertedX { get; set; }

      /// <summary>
      /// Y value of the data item
      /// </summary>
      public double Y {
         get {
            return this._y;
         }
         set {
            if (this._y != value) {
               this._y = value;
               this.NotifyPropertyChanged("Y");
            }
         }
      }

      /// <summary>
      /// This method fires the property changed event
      /// </summary>
      /// <param name="propertyName">Name of the changed property</param>
      protected void NotifyPropertyChanged(string propertyName) {
         System.ComponentModel.PropertyChangedEventHandler handler = this.PropertyChanged;

         if (handler != null) {
            handler(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
         }
      }

      /// <summary>
      /// Creates a copy of the value item
      /// </summary>
      /// <returns>The copy</returns>
      public abstract object CreateCopy();

      /// <summary>
      /// Creates a new trend item
      /// </summary>
      /// <returns>The trend item</returns>
      public abstract object NewTrendItem();
   }
}

VB.NET

''' Base class for value items
Public MustInherit Class ValueItem(Of TX)
    Implements IValueItem

    Private _y As Double

    ''' Raised when the data in the item Is changed
    Public Event PropertyChanged As System.ComponentModel.PropertyChangedEventHandler 
    Implements IValueItem.PropertyChanged

    ''' The actual value for X
    Public MustOverride Property X As TX

    ''' The value for X for calculations
    Public MustOverride Property ConvertedX As Double Implements IValueItem.ConvertedX

    ''' Y value of the data item
    Public Property Y As Double Implements IValueItem.Y
        Get
            Return Me._y
        End Get
        Set
            If (Me._y <> Value) Then
                Me._y = Value
                Me.NotifyPropertyChanged("Y")
            End If
        End Set
    End Property

    ''' This method fires the property changed event
    ''' <param name="propertyName">Name of the changed property</param>
    Protected Sub NotifyPropertyChanged(propertyName As String)
        RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs(propertyName))
    End Sub

    ''' Creates a copy of the value item
    Public MustOverride Function CreateCopy() As Object Implements IValueItem.CreateCopy

    ''' Creates a New trend item
    Public MustOverride Function NewTrendItem() As Object Implements IValueItem.NewTrendItem
End Class

namespace TrendCalculusFSharp

    // Base class for value items
    [<AbstractClass>]
    type public ValueItem<'TX>() =
            
        // Backing field for Y
        let mutable yValue : double = 0.0

        // Backing field for Converted X
        let mutable convertedXValue : double = 0.0

        let propertyChanged = new Event<System.ComponentModel.PropertyChangedEventHandler, 
                                        System.ComponentModel.PropertyChangedEventArgs>()

        interface IValueItem with
            [<CLIEvent>]
            member this.PropertyChanged : 
                Control.IEvent<System.  ComponentModel.PropertyChangedEventHandler, 
                                System.ComponentModel.PropertyChangedEventArgs> 
                = propertyChanged.Publish

            // The value for X for calculations
            member this.ConvertedX  
                with get() = this.ConvertedX 
                and set(value) = this.ConvertedX <- value
    
            // Creates a copy of the value item
            member this.CreateCopy() = this.CreateCopy()

            // Creates a new trend item
            member this.NewTrendItem() = this.NewTrendItem()

            member this.Y
                with get() = this.Y 
                and set(value) = this.Y <- value

      
        // Y value of the data item
        member this.Y
            with get() = yValue
            and set(value) =
                if yValue <> value then
                    yValue <- value
                    this.NotifyPropertyChanged("Y")

        // Overridded in derived clases
        abstract member NewTrendItem : unit -> obj 
        default __.NewTrendItem() = null

        // Overridded in derived clases
        abstract member CreateCopy : unit -> obj 
        default __.CreateCopy() = null

        // The actual value for X
        abstract X : 'TX with get, set

        // The actual value for X
        abstract ConvertedX : double with get, set
        default __.ConvertedX 
            with get() = convertedXValue
            and set(value) = convertedXValue <-value

        // This method fires the property changed event
        member this.NotifyPropertyChanged(propertyName) =
                propertyChanged.Trigger(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName))

Value item class for double values

namespace TrendCalculus {
   /// <summary>
   /// Class for number items where X is double
   /// </summary>
   public class NumberItem : ValueItem<double> {

      private double _x;

      /// <summary>
      /// X actual value of the data item
      /// </summary>
      public override double X {
         get {
            return this._x;
         }

         set {
            if (this._x != value) {
               this._x = value;
               this.NotifyPropertyChanged("X");
            }
         }
      }

      /// <summary>
      /// The value for X for calculations
      /// </summary>
      public override double ConvertedX {
         get {
            return this.X;
         }
         set {
            if (this.X != value) {
               this.X = value;
            }
         }
      }

      /// <summary>
      /// Creates a new trend item
      /// </summary>
      /// <returns>The trend item</returns>
      public override object NewTrendItem() {
         return new NumberItem();
      }

      /// <summary>
      /// Creates a copy of the value item
      /// </summary>
      /// <returns>The copy</returns>
      public override object CreateCopy() {
         return new NumberItem() {
            X = this.X,
            Y = this.Y
         };
      }
   }
}

VB.NET

''' Class for number items where X Is double
Public Class NumberItem
    Inherits ValueItem(Of Double)

    Private _x As Double

    ''' X actual value of the data item
    Public Overrides Property X As Double
        Get
            Return Me._x
        End Get

        Set(value As Double)
            If (Me._x <> value) Then
                Me._x = value
                Me.NotifyPropertyChanged("X")
            End If
        End Set
    End Property

    ''' The value for X for calculations
    Public Overrides Property ConvertedX As Double
        Get
            Return Me.X
        End Get
        Set(value As Double)
            If (Me.X <> value) Then
                Me.X = value
            End If
        End Set
    End Property

    ''' Creates a New trend item
    Public Overrides Function NewTrendItem() As Object
        Return New NumberItem()
    End Function

    ''' Creates a copy of the value item
    Public Overrides Function CreateCopy() As Object
        Dim newItem As NumberItem = New NumberItem()

        newItem.X = Me.X
        newItem.Y = Me.Y

        Return newItem
    End Function
End Class

namespace TrendCalculusFSharp
    
    // Type for number items where X is double
    type NumberItem() =
        inherit ValueItem<double>() 

        let mutable xValue : double = 0.0

        // X actual value of the data item
        override this.X
            with get() = xValue
            and set(value) =
                if xValue <> value then
                    xValue <- value
                    this.NotifyPropertyChanged("X")

        // The value for X for calculations
        override this.ConvertedX
            with get() = this.X
            and set(value) =
                if this.X <> value then
                    this.X <- value

        // Creates a new trend item
        override this.NewTrendItem() = 
            new NumberItem() :> obj

        // Creates a copy of the value item
        override this.CreateCopy() =
            let copy = new NumberItem()
            copy.X <- this.X
            copy.Y <- this.Y
            copy :> obj

Value item class for datetime values

namespace TrendCalculus {
   /// <summary>
   /// Class for number items where X is datetime
   /// </summary>
   public class DateItem : ValueItem<System.DateTime> {

      private System.DateTime _x;

      /// <summary>
      /// X actual value of the data item
      /// </summary>
      public override System.DateTime X {
         get {
            return this._x;
         }

         set {
            if (this._x != value) {
               this._x = value;
               this.NotifyPropertyChanged("X");
            }
         }
      }

      /// <summary>
      /// The value for X for calculations
      /// </summary>
      public override double ConvertedX {
         get {
            double returnValue = 0;

            if (this.X != null) {
               returnValue = this.X.ToOADate();
            }

            return returnValue;
         }
         set {
            System.DateTime converted = System.DateTime.FromOADate(value);

            if (this.X != converted) {
               this.X = converted;
            }
         }
      }
      /// <summary>
      /// Creates a new trend item
      /// </summary>
      /// <returns>The trend item</returns>
      public override object NewTrendItem() {
         return new DateItem();
      }

      /// <summary>
      /// Creates a copy of the value item
      /// </summary>
      /// <returns>The copy</returns>
      public override object CreateCopy() {
         return new DateItem() {
            X = this.X,
            Y = this.Y
         };
      }
   }
}

VB.NET

''' Class for number items where X Is datetime
Public Class DateItem
    Inherits ValueItem(Of System.DateTime)

    Private _x As System.DateTime

    ''' X actual value of the data item
    Public Overrides Property X As System.DateTime
        Get
            Return Me._x
        End Get
        Set(value As System.DateTime)
            If (Me._x <> value) Then
                Me._x = value
                Me.NotifyPropertyChanged("X")
            End If
        End Set
    End Property

    ''' The value for X for calculations
    Public Overrides Property ConvertedX As Double
        Get
            Dim returnValue As Double = 0

            returnValue = Me.X.ToOADate()

            Return returnValue
        End Get
        Set(value As Double)
            Dim converted As System.DateTime = System.DateTime.FromOADate(value)

            If (Me.X <> converted) Then
                Me.X = converted
            End If
        End Set
    End Property

    ''' Creates a New trend item
    Public Overrides Function NewTrendItem() As Object
        Return New DateItem()
    End Function

    ''' Creates a copy of the value item
    Public Overrides Function CreateCopy() As Object
        Dim newItem As DateItem = New DateItem()

        newItem.X = Me.X
        newItem.Y = Me.Y

        Return newItem
    End Function
End Class

namespace TrendCalculusFSharp
    
    // Type for number items where X is date time
    type DateItem() =
        inherit ValueItem<System.DateTime>() 

        let mutable xValue : System.DateTime = System.DateTime.MinValue

        // X actual value of the data item
        override this.X
            with get() = xValue
            and set(value) =
                if xValue <> value then
                    xValue <- value
                    this.NotifyPropertyChanged("X")

        // The value for X for calculations
        override this.ConvertedX
            with get() = this.X.ToOADate()
            and set(value) =
                let converted : System.DateTime = System.DateTime.FromOADate(value)
                if this.X <> converted then
                    this.X <- converted

        // Creates a new trend item
        override this.NewTrendItem() = 
            new NumberItem() :> obj

        // Creates a copy of the value item
        override this.CreateCopy() =
            let copy = new DateItem()
            copy.X <- this.X
            copy.Y <- this.Y
            copy :> obj

As you might notice the abstract class implements IValueItem interface. This interface is used for collections of data items. The interface helps the collection handling since it defines all the necessary methods and properties and eliminates the need to know the actual data type for X, which would be needed if the abstract class definition would be used. So the interface looks like this

namespace TrendCalculus {
   /// <summary>
   /// Interace which each value item type must implement in order to be usable in calculation
   /// </summary>
   public interface IValueItem {
      /// <summary>
      /// Raised when the data in the item is changed
      /// </summary>
      event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;

      /// <summary>
      /// Returns the value for X for calculations
      /// </summary>
      double ConvertedX { get; set; }

      /// <summary>
      /// Y value of the data item
      /// </summary>
      double Y { get; set; }

      /// <summary>
      /// Creates a copy of the value item
      /// </summary>
      /// <returns>The copy</returns>
      object CreateCopy();

      /// <summary>
      /// Creates a new trend item
      /// </summary>
      /// <returns>The trend item</returns>
      object NewTrendItem();

   }
}

VB.NET

''' Interace which each value item type must implement in order to be usable in calculation
Public Interface IValueItem
    ''' Raised when the data in the item Is changed
    Event PropertyChanged As System.ComponentModel.PropertyChangedEventHandler

    ''' Returns the value for X for calculations
    Property ConvertedX As Double

    ''' Y value of the data item
    Property Y As Double

    ''' Creates a copy of the value item
    Function CreateCopy() As Object

    ''' Creates a New trend item
    Function NewTrendItem() As Object
End Interface

namespace TrendCalculusFSharp

    // Interace which each value item type must implement in order to be usable in calculation
    type public IValueItem =
        interface    
            // Raised when the data in the item is changed
            [<CLIEvent>]
            abstract member PropertyChanged : 
                Control.IEvent<System.ComponentModel.PropertyChangedEventHandler, 
                                System.ComponentModel.PropertyChangedEventArgs>
      
            // Returns the value for X for calculations
            abstract ConvertedX : double with get, set

            // Y value of the data item
            abstract Y : double with get, set

            // Creates a copy of the value item
            abstract member CreateCopy : unit -> obj 

            // Creates a new trend item
            abstract member NewTrendItem : unit -> obj
        end

List of values

The next thing is to create a list for the value items. Of course a simple list could do, but to make things more easy to use I wanted to have a collection which would satisfy following requirements

Changes in the collection are automatically detected by WPF
Only items implementing IValueItem could be added to collection
Any change in the collection would case a data change notification. This would include adding or removing items but also changes in the property values of the items.

Because of these I inherited a new class from ObservableCollection as follows

namespace TrendCalculus {
   /// <summary>
   /// List of item values
   /// </summary>
   public class ValueList<TValueItem> : System.Collections.ObjectModel.ObservableCollection<TValueItem>
      where TValueItem : IValueItem {

      /// <summary>
      /// Raised when items in the value list change or data in existing items change
      /// </summary>
      public event System.EventHandler DataChanged;

      /// <summary>
      /// Type of the items in the list
      /// </summary>
      public ValueListTypes ListType { get; private set; }

      /// <summary>
      /// Default constructor
      /// </summary>
      private ValueList() {
         this.CollectionChanged += ValueList_CollectionChanged;
      }

      /// <summary>
      /// Constructor with the list type information
      /// </summary>
      /// <param name="listType"></param>
      internal ValueList(ValueListTypes listType) : this() {
         this.ListType = listType;
      }

      /// <summary>
      /// Handles collection changed events for data items
      /// </summary>
      /// <param name="sender"></param>
      /// <param name="e"></param>
      private void ValueList_CollectionChanged(object sender, 
      System.Collections.Specialized.NotifyCollectionChangedEventArgs e) {
         // Delete PropertyChanged event handlers from items removed from collection
         if (e.OldItems != null) {
            foreach (IValueItem item in e.OldItems) {
               item.PropertyChanged -= item_PropertyChanged;
            }
         }
         // Add PropertyChanged event handlers to items inserted into collection
         if (e.NewItems != null) {
            foreach (IValueItem item in e.NewItems) {
               item.PropertyChanged += item_PropertyChanged;
            }
         }
         this.NotifyDataChanged(this);
      }

      /// <summary>
      /// Handles Property changed events from individual items in the collection
      /// </summary>
      /// <param name="sender">Item that has changed</param>
      /// <param name="e">Event arguments</param>
      private void item_PropertyChanged(object sender, 
      System.ComponentModel.PropertyChangedEventArgs e) {
         this.NotifyDataChanged(sender);
      }

      /// <summary>
      /// Raises DataChanged event
      /// </summary>
      /// <param name="sender">Item that hsa changed</param>
      private void NotifyDataChanged(object sender) {
         System.EventHandler handler = this.DataChanged;

         if (handler != null) {
            handler(sender, new System.EventArgs());
         }
      }
   }
}

VB.NET

''' List of item values
Public Class ValueList(Of TValueItem As IValueItem)
    Inherits System.Collections.ObjectModel.ObservableCollection(Of TValueItem)

    ''' Raised when items in the value list change Or data in existing items change
    Public Event DataChanged As System.EventHandler

    ''' Type of the items in the list
    Public ListType As ValueListTypes

    ''' Default constructor
    Private Sub New()
        AddHandler Me.CollectionChanged, AddressOf ValueList_CollectionChanged
    End Sub

    ''' Constructor with the list type information
    Friend Sub New(listType As ValueListTypes)
        Me.New()
        Me.ListType = listType
    End Sub

    ''' Handles collection changed events for data items
    Private Sub ValueList_CollectionChanged(sender As Object, 
    e As System.Collections.Specialized.NotifyCollectionChangedEventArgs)
        '' Delete PropertyChanged event handlers from items removed from collection
        If (Not e.OldItems Is Nothing) Then
            For Each Item As IValueItem In e.OldItems
                RemoveHandler Item.PropertyChanged, AddressOf item_PropertyChanged
            Next
        End If
        '' Add PropertyChanged event handlers to items inserted into collection
        If (Not e.NewItems Is Nothing) Then
            For Each item As IValueItem In e.NewItems
                AddHandler item.PropertyChanged, AddressOf item_PropertyChanged
            Next
        End If
        Me.NotifyDataChanged(Me)
    End Sub

    ''' Handles Property changed events from individual items in the collection
    Private Sub item_PropertyChanged(sender As Object, 
    e As System.ComponentModel.PropertyChangedEventArgs)
        Me.NotifyDataChanged(sender)
    End Sub

    ''' Raises DataChanged event
    Private Sub NotifyDataChanged(sender As Object)
        RaiseEvent DataChanged(sender, New System.EventArgs())
    End Sub
End Class

namespace TrendCalculusFSharp

    // List of item values
    type ValueList<'TValueItem when 'TValueItem :> IValueItem>(listType : ValueListTypes) as this =
        inherit System.Collections.ObjectModel.ObservableCollection<'TValueItem>()

        let dataChanged = new Control.Event<obj>()

        let collectionChangedHandler = new System.Collections.Specialized.NotifyCollectionChangedEventHandler(this.ValueList_CollectionChanged)

        let propertyChangedHandler =  new System.ComponentModel.PropertyChangedEventHandler(this.item_PropertyChanged)
        
        // Constructor code
        do this.CollectionChanged.AddHandler(collectionChangedHandler)

        
        // Raised when items in the value list change or data in existing items change
        [<CLIEvent>]
        member this.DataChanged = dataChanged.Publish

        // Type of the items in the list
        member this.ListType : ValueListTypes = listType

        // Handles collection changed events for data items
        member this.ValueList_CollectionChanged (sender : obj) (e : System.Collections.Specialized.NotifyCollectionChangedEventArgs) =
            // Delete PropertyChanged event handlers from items removed from collection
            if e.OldItems <> null then
                let items = seq { for item in e.OldItems -> (item :?> IValueItem)}
                for valueitem in items do
                    valueitem.PropertyChanged.RemoveHandler(propertyChangedHandler)

            // Add PropertyChanged event handlers to items inserted into collection
            if e.NewItems <> null then
                let items = seq { for item in e.NewItems -> (item :?> IValueItem)}
                for valueitem in items do
                    valueitem.PropertyChanged.AddHandler(propertyChangedHandler)

            this.NotifyDataChanged(this)

        // Handles Property changed events from individual items in the collection
        member private this.item_PropertyChanged(sender : obj) ( e : System.ComponentModel.PropertyChangedEventArgs) =
            this.NotifyDataChanged(sender)
      
        // Raises DataChanged event
        member private this.NotifyDataChanged(sender : obj) =
            dataChanged.Trigger(sender)

As you see the constructor wires the CollectionChanged event so any modification to the collection will be noticed. When the collection is changed the PropertyChanged event for all the items is wired so that if any changes occur in the properties of individual value items, the collection is notified. Both event handlers raise DataChanged event if any change occur.

The calculation

The calculation is done by the LinearTrend class. The usage is that first the DataItems collection is filled with proper value items and when done, Calculate method is called. The calculation fills the following properties

Calculated, the value is true after Calculate has been called. However, the class keeps track of changes in the data item collection by listening DataChanged event so if the source data changes in any way, this property is set to false
Slope contains the calculated slope
Intercept contains the value for Y when Y axis is crossed
Correl contains the correlation coefficient
R2 contains the r-squared value
DataItems contains the source data
TrendItems contains the calculated trend value for each unique X value in the source data
StartPoint returns the calculated trend value for the first X value
EndPoint returns the calculated trend value for the last X value

So the coding part of the calculation looks like this

      /// <summary>
      /// Default constructor
      /// </summary>
      public LinearTrend() {
         this.DataItems = new ValueList<TValueItem>(ValueListTypes.DataItems);
         this.TrendItems = new ValueList<TValueItem>(ValueListTypes.TrendItems);
         this.Calculated = false;
         this.DataItems.DataChanged += DataItems_DataChanged;
      }

      /// <summary>
      /// Handles DataChanged event from the data item collection
      /// </summary>
      /// <param name="sender">Item that has changed</param>
      /// <param name="e"></param>
      private void DataItems_DataChanged(object sender, System.EventArgs e) {
         if (this.Calculated) {
            this.Calculated = false;
            this.Slope = null;
            this.Intercept = null;
            this.Correl = null;
            this.TrendItems.Clear();
         }
      }

      /// <summary>
      /// Calculates the trendline
      /// </summary>
      /// <returns>True if succesful</returns>
      public bool Calculate() {
         double slopeNumerator;
         double slopeDenominator;
         double correlDenominator;
         double r2Numerator;
         double r2Denominator;
         double averageX;
         double averageY;
         TValueItem trendItem;

         if (this.DataItems.Count == 0) {
            return false;
         }

         // Calculate slope
         averageX = this.DataItems.Average(item => item.ConvertedX);
         averageY = this.DataItems.Average(item => item.Y);
         slopeNumerator = this.DataItems.Sum(item => (item.ConvertedX - averageX) 
                                                     * (item.Y - averageY));
         slopeDenominator = this.DataItems.Sum(item => System.Math.Pow(item.ConvertedX - averageX, 2));

         this.Slope = slopeNumerator / slopeDenominator;

         // Calculate Intercept
         this.Intercept = averageY - this.Slope * averageX;

         // Calculate correlation
         correlDenominator = System.Math.Sqrt(
            this.DataItems.Sum(item => System.Math.Pow(item.ConvertedX - averageX, 2)) 
            * this.DataItems.Sum(item => System.Math.Pow(item.Y - averageY, 2)));
         this.Correl = slopeNumerator / correlDenominator;

         // Calculate trend points
         foreach (TValueItem item in this.DataItems.OrderBy(dataItem => dataItem.ConvertedX)) {
            if (this.TrendItems.Where(existingItem 
                => existingItem.ConvertedX == item.ConvertedX).FirstOrDefault() == null) {
               trendItem = (TValueItem)item.NewTrendItem();
               trendItem.ConvertedX = item.ConvertedX;
               trendItem.Y = this.Slope.Value * item.ConvertedX + this.Intercept.Value;
               this.TrendItems.Add(trendItem);
            }
         }

         // Calculate r-squared value
         r2Numerator = this.DataItems.Sum(
            dataItem => System.Math.Pow(dataItem.Y
            - this.TrendItems.Where(
               calcItem => calcItem.ConvertedX == dataItem.ConvertedX).First().Y, 2));

         r2Denominator = this.DataItems.Sum(dataItem => System.Math.Pow(dataItem.Y, 2))
            - (System.Math.Pow(this.DataItems.Sum(dataItem => dataItem.Y), 2) / this.DataItems.Count);

         this.R2 = 1 - (r2Numerator / r2Denominator);

         this.Calculated = true;

         return true;
      }

VB.NET

    ''' Default constructor
    Public Sub New()
        Me.DataItems = New ValueList(Of TValueItem)(ValueListTypes.DataItems)
        Me.TrendItems = New ValueList(Of TValueItem)(ValueListTypes.TrendItems)
        Me.Calculated = False
        AddHandler Me.DataItems.DataChanged, AddressOf DataItems_DataChanged
    End Sub

    ''' Handles DataChanged event from the data item collection
    Private Sub DataItems_DataChanged(sender As Object, e As System.EventArgs)
        If (Me.Calculated) Then
            Me._Calculated = False
            Me._Slope = Nothing
            Me._Intercept = Nothing
            Me._Correl = Nothing
            Me.TrendItems.Clear()
        End If
    End Sub

    ''' Calculates the trendline
    Public Function Calculate() As Boolean
        Dim slopeNumerator As Double
        Dim slopeDenominator As Double
        Dim correlDenominator As Double
        Dim r2Numerator As Double
        Dim r2Denominator As Double
        Dim averageX As Double
        Dim averageY As Double
        Dim trendItem As TValueItem

        If (Me.DataItems.Count = 0) Then
            Return False
        End If

        ' Calculate slope
        averageX = Me.DataItems.Average(Function(item) item.ConvertedX)
        averageY = Me.DataItems.Average(Function(item) item.Y)
        slopeNumerator = Me.DataItems.Sum(Function(item) (item.ConvertedX - averageX) * _
        (item.Y - averageY))
        slopeDenominator = Me.DataItems.Sum(Function(item) _
        System.Math.Pow(item.ConvertedX - averageX, 2))

        Me._Slope = slopeNumerator / slopeDenominator

        ' Calculate Intercept
        Me._Intercept = averageY - Me.Slope * averageX

        ' Calculate correlation
        correlDenominator = System.Math.Sqrt(Me.DataItems.Sum( Function(item) _
           System.Math.Pow(item.ConvertedX - averageX, 2)) * Me.DataItems.Sum(Function(item) _
           System.Math.Pow(item.Y - averageY, 2)))
        Me._Correl = slopeNumerator / correlDenominator

        ' Calculate trend points
        For Each item As TValueItem In Me.DataItems.OrderBy(Function(dataItem) dataItem.ConvertedX)
            If (Me.TrendItems.Where(Function(existingItem) existingItem.ConvertedX = _
            item.ConvertedX).FirstOrDefault() Is Nothing) Then
                trendItem = CType(item.NewTrendItem(), TValueItem)
                trendItem.ConvertedX = item.ConvertedX
                trendItem.Y = Me.Slope.Value * item.ConvertedX + Me.Intercept.Value
                Me.TrendItems.Add(trendItem)
            End If
        Next

        ' Calculate r-squared value
        r2Numerator = Me.DataItems.Sum(
            Function(dataItem) System.Math.Pow(dataItem.Y _
            - Me.TrendItems.Where(
               Function(calcItem) calcItem.ConvertedX = dataItem.ConvertedX).First().Y, 2))

        r2Denominator = Me.DataItems.Sum(Function(dataItem) System.Math.Pow(dataItem.Y, 2)) _
            - (System.Math.Pow(Me.DataItems.Sum(Function(dataItem) dataItem.Y), 2) / Me.DataItems.Count)

        Me._R2 = 1 - (r2Numerator / r2Denominator)

        Me._Calculated = True

        Return True
    End Function

namespace TrendCalculusFSharp
open System.Linq

    // Linear trend calculation
    type LinearTrend<'TValueItem when 'TValueItem :> IValueItem>() as this =

        let mutable calculatedValue : bool = false
        let mutable slopeValue : System.Nullable<double> = System.Nullable<double>()
        let mutable interceptValue : System.Nullable<double> = System.Nullable<double>()
        let mutable correlValue : System.Nullable<double> = System.Nullable<double>()
        let mutable r2Value : System.Nullable<double> = System.Nullable<double>()
        let dataItemsValue : ValueList<'TValueItem> = ValueList<'TValueItem>(ValueListTypes.DataItems)
        let trendItemsValue : ValueList<'TValueItem> = ValueList<'TValueItem>(ValueListTypes.TrendItems)

        //let dataChangedHandler = new System.EventHandler<obj>(fun sender ->  this.DataItems_DataChanged(sender))
        let dataChangedHandler = this.DataItems_DataChanged
              
        // Constructor code
        do this.DataItems.DataChanged.Add(dataChangedHandler)

        // Has the trend been calculated
        member this.Calculated = calculatedValue

        // Slope
        member this.Slope : System.Nullable<double> = slopeValue

        // Intercept
        member this.Intercept : System.Nullable<double> = interceptValue

        // Correlation coefficient
        member this.Correl : System.Nullable<double> = correlValue

        // R-squared value
        member this.R2 : System.Nullable<double> = r2Value

        // Data items
        member this.DataItems : ValueList<'TValueItem> = dataItemsValue

        // Trend items
        member this.TrendItems : ValueList<'TValueItem> = trendItemsValue

        // Value for the first trend point on X axis
        member this.StartPoint 
            with get() =
                match this.Calculated with
                | false -> Unchecked.defaultof<'TValueItem>
                | true -> this.TrendItems.OrderBy(fun item -> item.ConvertedX).FirstOrDefault()

        // Value for the last trend point on X axis
        member this.EndPoint 
            with get() =
                match this.Calculated with
                | false -> Unchecked.defaultof<'TValueItem>
                | true -> this.TrendItems.OrderByDescending(fun item -> item.ConvertedX).FirstOrDefault()

        // Handles DataChanged event from the data item collection
        member private this.DataItems_DataChanged(sender : obj) =
            if this.Calculated = true then
                calculatedValue <- false
                slopeValue  <- System.Nullable<double>()
                interceptValue <- System.Nullable<double>()
                correlValue <- System.Nullable<double>()
                this.TrendItems.Clear()

        member this.AverageX : double = 
            Seq.averageBy(fun item -> (item :> IValueItem).ConvertedX) (this.DataItems)

        member this.AverageY : double = 
            Seq.averageBy(fun item -> (item :> IValueItem).Y) (this.DataItems)

        // Calculates the trendline
        member this.Calculate() : bool = 
            let mutable slopeNumerator : double = 0.0
            let mutable correlDenominator : double = 0.0
            let mutable r2Numerator : double = 0.0
            let mutable r2Denominator : double = 0.0

            calculatedValue <- false

            if this.DataItems.Count <> 0 then
                slopeNumerator <-
                    Seq.sumBy(fun item-> ((item :> IValueItem).ConvertedX - this.AverageX) * ((item :> IValueItem).Y - this.AverageY)) (this.DataItems)

                correlDenominator <-
                    sqrt (
                        Seq.sumBy(fun item-> pown ((item :> IValueItem).ConvertedX - this.AverageX) 2) (this.DataItems)
                        * 
                        Seq.sumBy(fun item-> pown ((item :> IValueItem).Y - this.AverageY) 2) (this.DataItems)
                        )

                slopeValue <- System.Nullable(
                    slopeNumerator
                    /
                    Seq.sumBy(fun item-> pown ((item :> IValueItem).ConvertedX - this.AverageX) 2) (this.DataItems)
                    )

                interceptValue <- System.Nullable(
                    this.AverageY - this.Slope.Value * this.AverageX
                    )

                correlValue <-  System.Nullable(
                    slopeNumerator / correlDenominator
                    )

                // Calculate trend points
                for item in this.DataItems.OrderBy(fun dataItem -> dataItem.ConvertedX) do
                    if this.TrendItems.Where(fun existingItem -> existingItem.ConvertedX = item.ConvertedX).Count() = 0 then
                        let trendItem : 'TValueItem = item.NewTrendItem() :?> 'TValueItem
                        trendItem.ConvertedX <- item.ConvertedX
                        trendItem.Y <- this.Slope.Value * item.ConvertedX + this.Intercept.Value
                        this.TrendItems.Add(trendItem);

                // Calculate r-squared value
                r2Numerator <-
                    this.DataItems.Sum(fun dataItem -> 
                        pown (dataItem.Y - this.TrendItems.Where(fun calcItem -> 
                            calcItem.ConvertedX = dataItem.ConvertedX).First().Y) 2) 

                r2Denominator <-
                    this.DataItems.Sum(fun dataItem -> pown dataItem.Y 2)
                    - ((pown (this.DataItems.Sum(fun dataItem -> dataItem.Y)) 2) / (float this.DataItems.Count))

                r2Value <- System.Nullable(1.0 - (r2Numerator / r2Denominator))

                calculatedValue <- true

            calculatedValue

As you can see I have used LINQ in calculations. It would have been possible to condense the calculation even more, but in order to help debugging I calculated numerators and denominators separately. But as a side-note, using LINQ here simplifies the code a lot.

The test application

Now in order to test the functionality let’s create a small test application. The application should be able to generate both double and datetime values as test material and also to show the results of the calculation. The window looks like this with double values

And an example with datetime values

The code is quite simple. The "Generate values" -button creates the test data with Random object and when the test material is created one can press the "Calculate" -button to show the results

namespace TrendTest {
   /// <summary>
   /// Interaction logic for MainWindow.xaml
   /// </summary>
   public partial class TestWindow : System.Windows.Window {

      TrendCalculus.LinearTrend<TrendCalculus.IValueItem> linearTrend
      = new TrendCalculus.LinearTrend<TrendCalculus.IValueItem>();

      public TestWindow() {
         InitializeComponent();

         this.UseDouble.IsChecked = true;
         this.Values.ItemsSource = linearTrend.DataItems;
         this.TrendItems.ItemsSource = this.linearTrend.TrendItems;
      }

      private void GenerateValues_Click(object sender, System.Windows.RoutedEventArgs e) {
         System.Random random = new System.Random();

         linearTrend.DataItems.Clear();

         for (int counter = 0; counter < 10; counter++) {
            if (this.UseDouble.IsChecked.Value) {
               linearTrend.DataItems.Add(new TrendCalculus.NumberItem() {
                  X = System.Math.Round(random.NextDouble() * 100),
                  Y = System.Math.Round(random.NextDouble() * 100)
               });
            } else {
               linearTrend.DataItems.Add(new TrendCalculus.DateItem() {
                  X = System.DateTime.Now.AddDays(System.Math.Round(random.NextDouble() * -100)).Date,
                  Y = System.Math.Round(random.NextDouble() * 100)
               });
            }
         }

      }

      private void Calculate_Click(object sender, System.Windows.RoutedEventArgs e) {
         if (this.linearTrend.Calculate()) {
            this.TrendItems.ItemsSource = this.linearTrend.TrendItems;
            this.Slope.Text = this.linearTrend.Slope.ToString();
            this.Intercept.Text = this.linearTrend.Intercept.ToString();
            this.Correl.Text = this.linearTrend.Correl.ToString();
            this.R2.Text = this.linearTrend.R2.ToString();
            this.StartX.Text = this.linearTrend.StartPoint.ConvertedX.ToString();
            this.StartY.Text = this.linearTrend.StartPoint.Y.ToString();
            this.EndX.Text = this.linearTrend.EndPoint.ConvertedX.ToString();
            this.EndY.Text = this.linearTrend.EndPoint.Y.ToString();
         }
      }

      private void UseDouble_Checked(object sender, System.Windows.RoutedEventArgs e) {
         this.linearTrend.DataItems.Clear();
      }

      private void UseDatetime_Checked(object sender, System.Windows.RoutedEventArgs e) {
         this.linearTrend.DataItems.Clear();
      }

      private void DataItemsToClipboard_Click(object sender, System.Windows.RoutedEventArgs e) {
         System.Text.StringBuilder clipboardData = new System.Text.StringBuilder();

         clipboardData.AppendFormat("{0}\t{1}\t{2}", "Actual X", "Converted X", "Y").AppendLine();
         foreach (TrendCalculus.IValueItem item in linearTrend.DataItems) {
            if (item is TrendCalculus.DateItem) {
               clipboardData.AppendFormat("{0}\t{1}\t{2}",
                  ((TrendCalculus.DateItem)item).X.ToShortDateString(), item.ConvertedX, item.Y);
            } else {
               clipboardData.AppendFormat("{0}\t{1}\t{2}",
                  ((TrendCalculus.NumberItem)item).X.ToString(), item.ConvertedX, item.Y);
            }
            clipboardData.AppendLine();
         }
         System.Windows.Clipboard.SetText(clipboardData.ToString());
      }
   }
}

VB.NET

Class TestWindow

    Dim linearTrend As TrendCalculusVB.LinearTrend(Of TrendCalculusVB.IValueItem) = _
    New TrendCalculusVB.LinearTrend(Of TrendCalculusVB.IValueItem)()

    Private Sub Window_Loaded(sender As Object, e As RoutedEventArgs)

        Me.UseDouble.IsChecked = True
        Me.Values.ItemsSource = Me.linearTrend.DataItems
        Me.TrendItems.ItemsSource = Me.linearTrend.TrendItems
    End Sub
    Private Sub GenerateValues_Click(sender As Object, e As System.Windows.RoutedEventArgs)
        Dim random As System.Random = New System.Random()

        linearTrend.DataItems.Clear()

        For counter As Int32 = 0 To 9
            If (Me.UseDouble.IsChecked.Value) Then
                linearTrend.DataItems.Add(New TrendCalculusVB.NumberItem() With {
                  .X = System.Math.Round(random.NextDouble() * 100),
                  .Y = System.Math.Round(random.NextDouble() * 100)
               })
            Else
                linearTrend.DataItems.Add(New TrendCalculusVB.DateItem() With {
                  .X = System.DateTime.Now.AddDays(System.Math.Round(random.NextDouble() * -100)).Date,
                  .Y = System.Math.Round(random.NextDouble() * 100)
               })
            End If
        Next counter
    End Sub

    Private Sub Calculate_Click(sender As Object, e As System.Windows.RoutedEventArgs)
        If (Me.linearTrend.Calculate()) Then
            Me.TrendItems.ItemsSource = Me.linearTrend.TrendItems
            Me.Slope.Text = Me.linearTrend.Slope.ToString()
            Me.Intercept.Text = Me.linearTrend.Intercept.ToString()
            Me.Correl.Text = Me.linearTrend.Correl.ToString()
            Me.R2.Text = Me.linearTrend.R2.ToString()
            Me.StartX.Text = Me.linearTrend.StartPoint.ConvertedX.ToString()
            Me.StartY.Text = Me.linearTrend.StartPoint.Y.ToString()
            Me.EndX.Text = Me.linearTrend.EndPoint.ConvertedX.ToString()
            Me.EndY.Text = Me.linearTrend.EndPoint.Y.ToString()
        End If
    End Sub

    Private Sub UseDouble_Checked(sender As Object, e As System.Windows.RoutedEventArgs)
        Me.linearTrend.DataItems.Clear()
    End Sub

    Private Sub UseDatetime_Checked(sender As Object, e As System.Windows.RoutedEventArgs)
        Me.linearTrend.DataItems.Clear()
    End Sub

    Private Sub DataItemsToClipboard_Click(sender As Object, e As System.Windows.RoutedEventArgs)
        Dim clipboardData As System.Text.StringBuilder = New System.Text.StringBuilder()

        clipboardData.AppendFormat("{0}{1}{2}{3}{4}", "Actual X", vbTab, "Converted X", _
        vbTab, "Y").AppendLine()
        For Each item As TrendCalculusVB.IValueItem In linearTrend.DataItems
            If (TypeOf (item) Is TrendCalculusVB.DateItem) Then
                clipboardData.AppendFormat("{0}{1}{2}{3}{4}", (CType(item, _
                TrendCalculusVB.DateItem)).X.ToShortDateString(), vbTab, item.ConvertedX, vbTab, item.Y)
            Else
                clipboardData.AppendFormat("{0}{1}{2}{3}{4}", (CType(item, _
                TrendCalculusVB.NumberItem)).X.ToString(), vbTab, item.ConvertedX, vbTab, item.Y)
            End If
            clipboardData.AppendLine()
        Next
        System.Windows.Clipboard.SetText(clipboardData.ToString())
    End Sub
End Class

In order to easily test the calculations a "Copy to clipboard" -button is included that copies the source data to clipboard with tabulators as delimiters so that the data can easily be pasted into Excel.

Remarks

As this was the first trial on F# I understand that changing the mindset and learning to produce good F# is going to be a rocky road. However, having used procedural and OO languages for ages F# seems like a fresh breath so far :)

References

The references concerning the corresponding Excel functions can be found at:

History

22^nd May, 2016: Article created
28^th May, 2016: VB.Net version added
6^th June, 2016: F# version added
15^th August, 2017: Replaced pictures of formulas with LaTeX equations

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)