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

http://www.vbforums.com/attachment.php?attachmentid=33753&stc=1

and

http://www.vbforums.com/attachment.php?attachmentid=33754&stc=1

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. :wave:

[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. :rolleyes:

[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!!! :afrog:

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 :p

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. :D

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! :mad:

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.