Trend Analysis Question

[mendhak]

I have the following data:

    100 	Feb. 4, 2000
    200 	Aug. 31, 2000
    300 	Mar. 25, 2001
    400 	Feb. 26, 2002
    500 	Aug. 14, 2002
    600 	Apr. 29, 2003
    700 	Dec. 2, 2003
    800 	Jun. 11, 2004
    900 	Jan. 15, 2005

Column 1 is "discovery number" and Column 2 is the date on which that discovery was made. So, discovery #200 was made on August 31, 2000.

What I want to do is figure out when Discovery #1000 will be made.

How would you go about this?

[mendhak]

I should also show you a little of what I did:

If you remember from stats class, for a given X (In this case, X = 1000), you can get Y by the formula:

Y = a + bX

Where



and



The only confusion I have is regarding the date column.

Should I take the first date as a base and take the difference of each, or should I be taking the difference between that date and the date before it?

[sql_lall]

I think your first option is right...
if you convert each date to a 'Days After First Date' number, then you can predict how many days after the first date the next should be.

However, it would be interesting to see if the answer is exactly the same if you convert each date to a 'Days Since Previous Date' number, and predict it that way.

If they are not the same, then it's up to the question...it may be worded to indicate one way, but if not, then with appropriate reasoning, either should be fine.

[mendhak]

Alright. Let me try this out, I'll be back with something useful.

[mendhak]

Here's what I wrote. I took care of both conditions. If you have VB.NET, you can copy-paste this into the form's code. I can't upload any EXEs.

The two dates I got were:

September 5, 2005 --> Days since previous date method.
January 10, 2006 --> Days since first date method.

Public Class Form1
    Inherits System.Windows.Forms.Form
 
#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub
 
    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents CheckBox1 As System.Windows.Forms.CheckBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.CheckBox1 = New System.Windows.Forms.CheckBox
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(56, 112)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(184, 32)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'CheckBox1
        '
        Me.CheckBox1.Location = New System.Drawing.Point(88, 56)
        Me.CheckBox1.Name = "CheckBox1"
        Me.CheckBox1.TabIndex = 1
        Me.CheckBox1.Text = "Pairs Method"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.CheckBox1)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)
 
    End Sub
 
#End Region
 
    Dim nmArr(8) As Integer
    Dim dtArr(8) As Date
    Dim dtDiffs(8) As Integer
    Dim blMethod As Boolean
    Dim A As Decimal
    Dim B As Decimal
    Dim n As Integer = 9
    Dim YFinal As Decimal
    Dim XFinal As Decimal = 1000
    Dim DtFinal As Date
 
 
 
    '    100    Feb. 4, 2000
    '200    Aug. 31, 2000
    '300    Mar. 25, 2001
    '400    Feb. 26, 2002
    '500    Aug. 14, 2002
    '600    Apr. 29, 2003
    '700    Dec. 2, 2003
    '800    Jun. 11, 2004
    '900    Jan. 15, 2005
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    
 
 
        'Initialize Array
        dtArr(0) = Date.Parse("02/04/2000")
        dtArr(1) = Date.Parse("08/31/2000")
        dtArr(2) = Date.Parse("03/25/2001")
        dtArr(3) = Date.Parse("02/26/2002")
        dtArr(4) = Date.Parse("08/14/2002")
        dtArr(5) = Date.Parse("04/29/2003")
        dtArr(6) = Date.Parse("12/02/2004")
        dtArr(7) = Date.Parse("06/11/2004")
        dtArr(8) = Date.Parse("01/15/2005")
 
        Dim i As Integer
        For i = 0 To 8
            nmArr(i) = (i + 1) * 100
        Next
 
 
 
    End Sub
 
 
   
 
 
 
    Private Function Exy() As Integer
 
        Dim i As Integer
        Dim intSum As Integer = 0
 
        For i = 0 To 8
            intSum = intSum + (nmArr(i) * dtDiffs(i))
        Next
 
        Return intSum
 
 
    End Function
 
    Private Function Ex() As Integer
 
        Dim i As Integer
        Dim intSum As Integer = 0
 
        For i = 0 To 8
            intSum = intSum + nmArr(i)
        Next
 
        Return intSum
 
 
    End Function
 
    Private Function Ey() As Integer
 
        Dim i As Integer
        Dim intSum As Integer = 0
 
        For i = 0 To 8
            intSum = intSum + dtDiffs(i)
        Next
 
        Return intSum
 
    End Function
 
    Private Function Ex2() As Integer
 
        Dim i As Integer
        Dim intSum As Integer = 0
 
        For i = 0 To 8
            intSum = intSum + (nmArr(i) * nmArr(i))
        Next
 
        Return intSum
 
    End Function
 
    Private Function YBar() As Decimal
 
        Dim i As Integer
        Dim intSum As Integer = 0
 
        For i = 0 To 8
            intSum = intSum + dtDiffs(i)
        Next
 
        Return (intSum / 9)
 
    End Function
 
    Private Function XBar() As Decimal
 
 
        Dim i As Integer
        Dim intSum As Integer = 0
 
        For i = 0 To 8
            intSum = intSum + nmArr(i)
        Next
 
        Return (intSum / 9)
    End Function
 
 
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
 
        blMethod = CheckBox1.Checked
 
        GetDateDiffs()
 
 
 
        B = ((n * Exy()) - ((Ex()) * (Ey()))) / ((n * Ex2()) - (Ex() * Ex()))
 
        A = YBar() - (B * XBar())
 
        YFinal = A + (B * XFinal)
 
        If blMethod Then
            DtFinal = DateAdd(DateInterval.Day, YFinal, dtArr(8))
        Else
            DtFinal = DateAdd(DateInterval.Day, YFinal, dtArr(0))
        End If
 
 
        MessageBox.Show(DtFinal.ToLongDateString)
    End Sub
 
 
    Private Sub GetDateDiffs()
 
        Dim intCount As Integer
 
        For intCount = 0 To 8
 
            If blMethod Then
                dtDiffs(intCount) = DateDiff(DateInterval.Day, dtArr(IIf(intCount = 0, 0, intCount - 1)), dtArr(intCount))
            Else
                dtDiffs(intCount) = DateDiff(DateInterval.Day, dtArr(0), dtArr(intCount))
            End If
 
        Next
 
    End Sub
 
   
End Class

I guess I'll go with 2006.

[wossname]

Roboplot would eat this for breakfast with weetabix.

It's a shame I can't be bothered finishing it yet though.

[mendhak]

You might be interested in the "source/reason" for this:

The SOHO satellite recently found comet #900. They announced a contest: Whoever can guess the date+time for discovery of comet #1000 will win... are you ready... a DVD and a pair of solar glases and even a T-Shirt!!!

http://soho.nascom.nasa.gov/comet1000/

Curiosity got the better of me, had to find out.

Now, I'll be using the January date and working with other factors like

a) Increased webcam hits due to news of the contest
b) More people "logging on" to the internet every day, a small percentage of them interested in things other than cybersex.
c) Political events which may divert attention from this.
d) Mercury explodes

[Phenix]

The MS Excel FORECAST function places #1000 on Sep. 30, 2005. The data you gave also does look pretty linear (converting the date to the MS serial number format and then back again), which should fit your original Y = a + bX assumption. The MS Excel TREND function gives exactly the same answer.

[mendhak]

I see. I was wondering why I had gone through this trouble when an existing solution was in place, but since our answers are different, it makes me wonder if Excel knows what it's doing

Perhaps their function has different logic?

[wossname]

a small percentage of them interested in things other than cybersex.

May be difficult to prove.

[Rassis]

Here´s what I got using the Add-Trendline of MS-EXCEL and two buit-in functions: Intercept() and Slope(). It was fun and easy. See the attached MS-EXCEL file.

Hope you get your T-shirt

[mendhak]

Thanks Ras. It's interesting to see how no two answers are alike.

And to speculate on whether I had known about Excel's functions, whether I'd still have written that code.

*bites fingers*

[wossname]

Why not use Tarot cards to predict the future? Or goat entrails, or sandpaper.

[Rassis]

Why not use Tarot cards to predict the future? Or goat entrails, or sandpaper.

Math methods are rational and work just fine in case the near future proceeds with the same pattern as the recent past. In case you predict some change (sudden or continuous) math can still be of use by applying proper algorithms (with damping effects when a sudden change is foreseen or a trend is foreseen). If data doesn’t exist at all, then you can still use stat methods to predict the future in corporations such as the Delphi method.

Tarot cards, goat entrails or sandpaper…NEVER!

Be happy.

[Phenix]

Rassis,
Nice color scheme on the spreadsheet.

[yrwyddfa]

Don't let Wossy confuse you:

Here's the source that will generate a trend curve, and the function you need in order to predict further into the future.

http://www.vbforums.com/showthread.php?t=311225

[iqkh]

Here´s what I got using the Add-Trendline of MS-EXCEL and two buit-in functions: Intercept() and Slope(). It was fun and easy. See the attached MS-EXCEL file.

Hope you get your T-shirt

Nice Rassis!!

I was also working on the Forecasting for incoming calls I have taken data for last 2 years but the y=a+b.x does not give me data approx to any of the past data. Do u have any suggestion for this sort of forecasting.

[Rassis]

I was also working on the Forecasting for incoming calls I have taken data for last 2 years but the y=a+b.x does not give me data approx to any of the past data. Do u have any suggestion for this sort of forecasting.

You can try the "add trend line" tool to fit any of the functions prebuilt in Excel to your data or use Crystal Ball, BestFit, Stat::Fit, ExpertFit or any other similar software.

Alternatively, I attach an example in Excel for the power, linear and the exponential that you can modify and expand at your will.

Hope this helps.

[yrwyddfa]

If you want to get really technical you can find the trend (a reasonably low polynomial) and then get the moving average (the simple one will do)

Where the lines intersect you can expect a reverse change in the gradient of the line in the near future in say about 80% of cases.